title: The fastest way pandas can compile numerous dataframes
body: <p>I am compiling some dataframes with pandas at the moment, currently 99 workbooks with around 200,000 rows of data and a few columns. The exercise is filtering one of these columns and getting a combined workbook of all of the filtered data.</p>
  
  <p>Initially I used a list to append all the filtered data to one dataframe and then write out a the resultant csv, below:</p>
  
  <pre><code>appended_data=[]
  for fname in os.listdir(readpath):
      if 'xlsx' in fname:
          ex_wbk = os.path.join(readpath + fname)
          try:
              fName = os.path.join(readpath + "\\" + fname)
              fBase = os.path.basename(fName)
              df = pd.read_excel(fName, index = False, index_col=None, na_values='')
              df.drop(df.iloc[:, 2:4], inplace = True, axis = 1) 
              df.dropna(subset=['Data Line'], inplace=True)
              appended_data.append(df)
              errorbook = ex_wbk
          except OSError:
              print(str(errorbook) + " Workbook in use")
              pass
  
  appended_data = pd.concat(appended_data, sort=True)
  appended_data.to_excel(home, header=False, index=False)
  </code></pre>
  
  <p><strong><em>This took 789 seconds to run</em></strong></p>
  
  <hr>
  
  <p>Then, I thought about making a csv file of each workbook with only the filtered data and compiling all of those. As below,</p>
  
  <pre><code>for filename in os.listdir(homedir):
      if filename.endswith(".xlsx"):
          fName = os.path.join(homedir + "\\" + filename)
          fBase = os.path.basename(fName)
          fRaw = os.path.splitext(fBase)[0]
          df = pd.read_excel(fName, index = False)
          df.drop(df.iloc[:, 2:4], inplace = True, axis = 1) 
          df.dropna(subset=['Data Line'], inplace=True)
          df.to_csv(path_or_buf=home, header=True, index=False)
  
  all_files = glob.glob(homedir + "/*.csv")
  df = pd.concat((pd.read_csv(f) for f in all_files))
  os.path.join(path, "*.csv")
  df.to_csv ("D:\\Users\\cdoyle\\Desktop\\short\\combined_csv.csv", index=False, encoding='utf-8-sig')
  </code></pre>
  
  <p><strong><em>This took 610 seconds to run</em></strong></p>
  
  <hr>
  
  <p>Is there a faster way than the above?</p>