title: consolidate multiple pandas dataframes with unpredictable column headers/order into single frame or list
body: <p>I have searched a lot on this topic and been unable to find any existing question which addresses this <em>specific</em> problem. Nb I am very new to coding and python.  </p>
  
  <p>I'm reading multiple excel source data files which are similar in structure, but the column headers are unpredictable in that the order can change and certain column headers are missing from some files but not others. </p>
  
  <pre><code>import pandas as pd
  import glob
  import csv
  import os
  
  outfile = open('./output/output.csv','w')
  big_df = pd.DataFrame()
  dataList = []
  path = "./files/"
  
  #load xlsx files from directory
  allFiles = glob.glob(os.path.join(path, "*.xlsx"))
  # loop through each file, finding the right sheet
  for f in allFiles:
      try:
          #read sheet as dataframe and append to list
          df = pd.read_excel(f, index_col=None, header=0, sheetname='sheetToRead')
          dataList.append(df)
      except:
          [handle error]
  #concatenate the list into a dataframe  
  big_df = pd.concat(dataList)
  #output the dataframe to csv
  big_df.to_csv(outfile)
  </code></pre>
  
  <p>this code works fine when all the excel sheets have the same set of columns - but i have a large number of source files to iterate through and a lot of them have unpredictable column headers and ordering of the column headers, e.g.: </p>
  
  <p>file example 1:</p>
  
  <pre><code>col1  col2   col3   col4  
  apple orange banana tangerine
  </code></pre>
  
  <p>file example 2:</p>
  
  <pre><code>col1   col3   col4 col5
  green  violet red  azul
  </code></pre>
  
  <p>file example 3: </p>
  
  <pre><code>col2    col4    col5  col6
  cactus  bonsai  oak   sycamore
  </code></pre>
  
  <p>there are so many column combinations that I want to automatically read the column headers and sync them up in the output file, using the column header string as the key, so the output has all the columns but just prints blank rows/Nan where there was no column/data e.g.</p>
  
  <p>output file:</p>
  
  <pre><code>col1   col2    col3   col4      col5  col6
  apple  orange  banana tangerine Nan   Nan
  green  Nan     violet red       azul  Nan
  Nan    cactus  Nan    bonsai    oak   sycamore
  </code></pre>
  
  <p>finally, here is the error I get when I try to run the code on these types of files: </p>
  
  <pre><code>Traceback (most recent call last):
    File "\script.py", line 27, in &lt;module&gt;
      big_df = pd.concat(dataList)
    File "C:\Python35-32\lib\site-packages\pandas\tools\merge.py", line 845, in concat
      copy=copy)
    File "C:\Python35-32\lib\site-packages\pandas\tools\merge.py", line 878, in __init__
      raise ValueError('No objects to concatenate')
  ValueError: No objects to concatenate
  </code></pre>
  
  <p>any suggestions welcomed, doesn't necessarily have to be using pandas.  </p>