1
$\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'
$\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
$\endgroup$
0
$\begingroup$

In this case concat is what you want

You can read the data into a dataframe using normal pd.read_csv

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

Then apply pd.concat with axis=0 and ignore_index=True. By passing axis=0 here you are stacking the df's on top of each other. The columns that do not match will result in a NaN. You can do a fillna(-1) to change that to -1 as per your requirement.

df1 = pd.concat([df1,df2], axis=0, ignore_index=True).fillna(-1)

The output of this will be:

one.csv will be df1:

    site_no    datetime  00060_00003
0  11481500  2019-10-05         7.54

two.csv will be df2:

    site_no    datetime  00010_00001  00010_00002  00010_00003  00060_00003
0  11523000  2019-10-05         15.0         14.1         14.6         1920

concatenated df1 will be:

    site_no    datetime  00060_00003  00010_00001  00010_00002  00010_00003
0  11481500  2019-10-05         7.54         -1.0         -1.0         -1.0
1  11523000  2019-10-05      1920.00         15.0         14.1         14.6
$\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.