title: Loop through excels and extract values in excel using pandas
body: <p>I am learning python while doing small automation and I need help here. I have excel files in folder and I need to read all excel files and look for specific text in excel and get value from a specific column if required text is found. I was able to read excel and get values but I think I am getting those values in separate dataframe for each excel. After having dataframe, I am trying to combine but I get only one row in output.
  I need help in getting rows for all excels in one dataframe. Below is my code</p>
  <pre><code>import pandas as pd
  import numpy as np
  from pathlib import Path
  
  p = Path(path to excel files)
  filtered = [x for x in p.glob(&quot;**/*.xlsx&quot;) if not x.name.__contains__(&quot;R4&quot;)] #doing filter to 
  exclude some excel 
  ls = list(filtered)
  L = len(ls)
  for x in ls[0:L]:
      data = pd.read_excel(x, sheet_name=[1], header=1)
      df = data[1]
      a = np.where(df.values == 'Deductible Individual')
      b = [x[0] for x in a]
      val = b[0]
      d1 = np.where(df.values == 'Deductible Family')
      b = [x[0] for x in d1]
      val3 = b[0]
      INdata1 = df.loc[val, 'In-Network\nVALUE']
      INDDEDIN = pd.DataFrame({&quot;INDIVIDUAL DEDUCTIBLE INN&quot;: [INdata1]})
      INdata3 = df.loc[val3, 'In-Network\nVALUE']
      FAMDEDIN = pd.DataFrame({&quot;FAMILY DEDUCTIBLE INN&quot;: [INdata3]})
  
  result = pd.concat([INDDEDIN, FAMDEDIN], axis=1)
  
  print(INDDEDIN)
  INDIVIDUAL DEDUCTIBLE INN
  0                     1400.0
  INDIVIDUAL DEDUCTIBLE INN
  0                     1500.0
  
  print(FAMDEDIN)
  FAMILY DEDUCTIBLE INN
  0                 4200.0
  FAMILY DEDUCTIBLE INN
  0                 2400.0
  
  print(result)
  INDIVIDUAL DEDUCTIBLE INN  FAMILY DEDUCTIBLE INN
  0              1500.0                 2400.0
                
  </code></pre>
  <p>But I am expecting result to print like below:</p>
  <pre><code> INDIVIDUAL DEDUCTIBLE INN  FAMILY DEDUCTIBLE INN
    0              1500.0                 2400.0
                   1400.0                 4200.0
  </code></pre>
  <p>Thank you ewz93. I tried below code and it is much easier and simple. But result is printed as below:</p>
  <pre><code>                              0         1
  INDIVIDUAL DEDUCTIBLE INN  [1400.0]  [1500.0]
  FAMILY DEDUCTIBLE INN      [4200.0]  [2400.0]
  </code></pre>
  <p>I need result as below, how can I do that?</p>
  <pre><code>INDIVIDUAL DEDUCTIBLE INN      FAMILY DEDUCTIBLE INN
    1400.0                         4200.0
    1500.0                         2400.0
  </code></pre>