I am working with Covid-19 dataset from European CDC. I have pivoted the data frame, but it seems that the values are bing aggregated

user_13967100 0
Viewed 84

I am working with Covid-19 dataset and have used pivot function below:

url = 'https://opendata.ecdc.europa.eu/covid19/casedistribution/csv'
data = pd.read_csv(url,parse_dates=['dateRep'],index_col=['dateRep'])
data.head()

data.index.name = "date"
data = data.drop(['day', 'month', 'year','geoId','countryterritoryCode','continentExp'], axis = 1)
data = data.rename(columns={'countriesAndTerritories': 'country', 'popData2019':'population', 'continentExp' : 'continent' })

df_pivot = data.pivot(index = 'date', columns = 'country', values = 'cases').fillna(0)
df_pivot`

When I look at US cases on 2020-01-04 the number in pivoted data frame is "24998.0" which is incorrect (it should be 0). I will appreciate any suggestions.Thanks.

pandas pivot python
1 Answers

pivot cannot aggregate, it only reshapes.

The issue is with pandas automatically parsing the index and it gets confused with months and days (so it parsed April 1st as January 4th). The simplest method is to parse the dates manually with a format after reading.

import pandas as pd
df = pd.read_csv('https://opendata.ecdc.europa.eu/covid19/casedistribution/csv')

df['dateRep'] = pd.to_datetime(df['dateRep'], format='%d/%m/%Y')
df = df.set_index('dateRep')

And now we can see it will be fine:

df[df['countriesAndTerritories'] == 'United_States_of_America'].sort_index().plot(y='cases')

enter image description here

We can check to see that the parsing with your method was getting confused. Automatic parsing mixed up April 1st with January 4th.

df[(df.index == '2020-04-01') & df['countriesAndTerritories'].eq('United_States_of_America')].cases
#dateRep
#2020-04-01    24998     # <-- That's your number for Jan 4th.
#Name: cases, dtype: int64
user_43333590
Related