How would I load a database with multiple inconsistent headers? I have a database with multiple headers like so:

site_no,datetime,00060_00003    
11481500,2019-10-05,7.54
[...]
site_no,datetime,00010_00001,00010_00002,00010_00003,00060_00003
11523000,2019-10-05,15.0,14.1,14.6,1920


I need to merge these into something that looks like this:

site_no,datetime,00010_00001,00010_00002,00060_00003    
11481500,2019-10-05,-1,-1,7.54,-1
11523000,2019-10-05,15.0,14.1,14.6,1920


The code that I currently have to do this is:

    df = pd.read_csv(outputFileName, false_values = ["***"], header=headerlist[0] )
    i = 1
    while i+1 &lt; len(headerlist):
        newdf = pd.read_csv(outputFileName, false_values=["***"], skiprows=headerlist[i]-1, header = headerlist[i]).head(n=(headerlist[i+1]-headerlist[i]),)
        df.join(newdf, on = "datetime", how="outer")
        i = i + 1
    df.to_csv(outputFileName)


I get an error:

KeyError: 'datetime'
$ cat one.csv
    site_no,datetime,00060_00003
    11481500,2019-10-05,7.54
$ cat two.csv
    site_no,datetime,00010_00001,00010_00002,00010_00003,00060_00003
   11523000,2019-10-05,15.0,14.1,14.6,1920


code:

df2 = pd.read_csv('two.csv')
df = pd.read_csv('one.csv')

pd.concat([df,df2], join='outer', join_axes=[df2.columns])


output:

   00010_00001  00010_00002  00010_00003  00060_00003  00060_00003        datetime   site_no
0          NaN          NaN          NaN          NaN             7.54  2019-10-05  11481500
0         15.0         14.1         14.6       1920.0              NaN  2019-10-05  11523000


the pd.join or pd.concat works on entire data frame, you don't need to loop through the variables.

using merge: (from the documentation)

t = pd.DataFrame({'key':['foo','foo'], 'lval':[1,2]})
&gt;&gt;&gt; right = pd.DataFrame({'key':['foo', 'foo'], 'rval':[4,5]})
&gt;&gt;&gt; left
   key  lval
0  foo     1
1  foo     2
&gt;&gt;&gt; right
   key  rval
0  foo     4
1  foo     5
&gt;&gt;&gt; pd.merge(left, right, on='key')
   key  lval  rval
0  foo     1     4
1  foo     1     5
2  foo     2     4
3  foo     2     5