0
$\begingroup$

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 < 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'
| improve this question | | | | |
$\endgroup$
0
$\begingroup$
$ 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]})
>>> right = pd.DataFrame({'key':['foo', 'foo'], 'rval':[4,5]})
>>> left
   key  lval
0  foo     1
1  foo     2
>>> right
   key  rval
0  foo     4
1  foo     5
>>> 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
| improve this answer | | | | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.