How to merge multiple csv files on common columns and keep the non common ones as separate columns?

user_5550284 0
Viewed 771

I have three csv files which contains data about covid 19. The first csv has information about number of confirmed cases, the second one has information about number of deaths and the third one has information about number of recovery.

So this is how the dataframes looks like

import pandas as pd

df1 = pd.read_csv('/Users/sr/covid_csvs/confirmed.csv')

df2 = pd.read_csv('/Users/sr/covid_csvs/deaths.csv')

df3 = pd.read_csv('/Users/sr/covid_csvs/recovery.csv')

print(df1.head(5))

  Province/State Country/Region      Lat     Long     Date  Confirmed
0            NaN    Afghanistan  33.0000  65.0000  1/22/20          0
1            NaN        Albania  41.1533  20.1683  1/22/20          0
2            NaN        Algeria  28.0339   1.6596  1/22/20          0
3            NaN        Andorra  42.5063   1.5218  1/22/20          0
4            NaN         Angola -11.2027  17.8739  1/22/20          0


print(df2.head(5))

  Province/State Country/Region      Lat     Long     Date     Deaths
0            NaN    Afghanistan  33.0000  65.0000  1/22/20          0
1            NaN        Albania  41.1533  20.1683  1/22/20          0
2            NaN        Algeria  28.0339   1.6596  1/22/20          0
3            NaN        Andorra  42.5063   1.5218  1/22/20          0
4            NaN         Angola -11.2027  17.8739  1/22/20          0


print(df3.head(5))

  Province/State Country/Region      Lat     Long     Date  Recovery
0            NaN    Afghanistan  33.0000  65.0000  1/22/20         0
1            NaN        Albania  41.1533  20.1683  1/22/20         0
2            NaN        Algeria  28.0339   1.6596  1/22/20         0
3            NaN        Andorra  42.5063   1.5218  1/22/20         0
4            NaN         Angola -11.2027  17.8739  1/22/20         0

Now I want to merge all the three dataframes such that I get the below result

  Province/State Country/Region      Lat     Long     Date  Confirmed  Deaths Recovery
0            NaN    Afghanistan  33.0000  65.0000  1/22/20          0       0        0
1            NaN        Albania  41.1533  20.1683  1/22/20          0       0        0
2            NaN        Algeria  28.0339   1.6596  1/22/20          0       0        0
3            NaN        Andorra  42.5063   1.5218  1/22/20          0       0        0
4            NaN         Angola -11.2027  17.8739  1/22/20          0       0        0

So I tried doing something like below

df_merged = pd.concat([df1, df2, df3])    
df_merged.to_csv('merged.csv', sep=',', encoding='utf-8', index=False)

But I do not get the required csv. How do I do this?

pandas csv python
1 Answers

Idea is create MultiIndex by DataFrame.set_index for each DataFrame, then concat with axis=1 and last remove index=False in to_csv:

cols = ['Province/State', 'Country/Region','Lat','Long','Date']

dfs = [df1, df2, df3]
df_merged = pd.concat([x.set_index(cols) for x in dfs], axis=1)    
df_merged.to_csv('merged.csv', sep=',', encoding='utf-8')

Or convert MultiIndex to column and then use index=False in to_csv:

cols = ['Province/State', 'Country/Region','Lat','Long','Date']

dfs = [df1, df2, df3]
df_merged = pd.concat([x.set_index(cols) for x in dfs], axis=1).reset_index()  
df_merged.to_csv('merged.csv', sep=',', encoding='utf-8', index=False)
user_29010020
Related