consolidate multiple pandas dataframes with unpredictable column headers/order into single frame or list
<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>