Pandas Dataframes: Combining Columns from Two Global Datasets when the rows hold different Countries

user_12275901 0
Viewed 75

My Problem is that these two CSV files have different countries at different rows, so I can't just append the column in question to the other data frame.

https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv

https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv

I'm trying to think of some way to use a for loop, checking every row, and add the recovered cases to the correct row where the country name is the same in both data frames, but I don't know how to put that idea in to code. Help?

pandas python
1 Answers

You can do this a couple of ways:

Option 1: use pd.concat with set_index

pd.concat([df_confirmed.set_index(['Province/State', 'Country/Region']),
          df_recovered.set_index(['Province/State', 'Country/Region'])],
         axis=1, keys=['Confirmed', 'Recovered'])

Option 2: use pd.DataFrame.merge with an left join or outer join using how parameter

df_confirmed.merge(df_recovered, on=['Province/State', 'Country/Region'], how='left', 
                   suffixes=('_confirmed','_recovered'))

Using pd.read_csv from github raw format:

df_recovered = pd.read_csv('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv')

df_confirmed = pd.read_csv('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv')
user_63615310
Related