How to restructure daily time series by distinct column values in pandas?

user_14126595 0
Viewed 210

I have daily time series data by different counties in US, which is covid cases daily time series, I want to restructure/reshape it in order to use it with other time series data that I have. So I tried groupby operation to regroup the time series, but I got value error as follow:

ValueError: No axis named county_state for object type DataFrame

I am not sure using groupby is the right move to take. Can anyone suggest possible way of doing this right in pandas? Any idea?

current attempt

Here is the reproducible data on gist. Here is the my current attempt:

import pandas as pd

df = pd.read_csv("df.csv")
df['date'] = pd.to_datetime(df['date'])
df.groupby('date', 'county_state')['cases', 'deaths'].unstack().reset_index()

but above attempt is not working, which cause ValueError instead. Can anyone suggest how to make this right?

desired output

Here is structure of output of my expected dataframe, no need to use aggregate by cases or deaths.

    date    fips    cases   deaths  county_state
1/26/2020   4013    1   0   Maricopa_Arizona
1/27/2020   4013    5    0  Maricopa_Arizona
1/28/2020   4013    7    0  Maricopa_Arizona
...         
9/02/202    4013    2333  100     Maricopa_Arizona
1/26/2020   6037    1   0   Los Angeles_California
1/27/2020   6037    15    2  Los Angeles_California
1/28/2020   6037    20    4  Los Angeles_California
...
9/02/202    6037    10001  200     Los Angeles_California

How to achieve my expected output above? Any way to get this right in pandas?

pandas dataframe python
3 Answers

You need to do this to import the CSV correctly, the CSV has some issues with space at the end of each line and your separator is ' '. Probably best to split the county and state into separate columns, sort them and then drop them:

df = pd.read_csv('df.csv', sep='    ')
df.drop(columns='Unnamed: 5', inplace=True)
df['date'] = pd.to_datetime(df['date'])
df['state'] = df['county_state'].str.split('_').str[1]
df['county'] = df['county_state'].str.split('_').str[0]
df.sort_values(by=['state', 'county', 'date'], inplace=True)
df.drop(columns=['state', 'county'], inplace=True)
print(df)

            date   fips  cases  deaths      county_state
0    2020-01-26   4013      1       0  Maricopa_Arizona
2    2020-01-27   4013      1       0  Maricopa_Arizona
4    2020-01-28   4013      1       0  Maricopa_Arizona
6    2020-01-29   4013      1       0  Maricopa_Arizona
8    2020-01-30   4013      1       0  Maricopa_Arizona
...         ...    ...    ...     ...               ...
7280 2020-08-29  55081    280       2  Monroe_Wisconsin
7325 2020-08-30  55081    281       2  Monroe_Wisconsin
7370 2020-08-31  55081    282       2  Monroe_Wisconsin
7415 2020-09-01  55081    286       2  Monroe_Wisconsin
7460 2020-09-02  55081    290       2  Monroe_Wisconsin

[7461 rows x 5 columns]
user_61683230

I think this gives you the output you wanted:

df.groupby(['fips', 'county_state', 'date']).sum().reset_index()[["date", "fips", "cases", "deaths", "county_state"]]

EDIT: The data frame was read in like this:

df = pd.read_csv("https://gist.github.com/jerry-shad/f372d7a05e1a63732665fad0c7c754d0/raw/425b91083519adae00056aac3b25dcb3cfda42bc/df.csv", sep="\t").drop(columns=["Unnamed: 5"])

Results:

date    fips    cases   deaths  county_state
0   1/26/2020   4013    1   0   Maricopa_Arizona
1   1/27/2020   4013    1   0   Maricopa_Arizona
2   1/28/2020   4013    1   0   Maricopa_Arizona
3   1/29/2020   4013    1   0   Maricopa_Arizona
4   1/30/2020   4013    1   0   Maricopa_Arizona
... ... ... ... ... ...
7456    8/7/2020    55081   242 2   Monroe_Wisconsin
7457    8/8/2020    55081   242 2   Monroe_Wisconsin
7458    8/9/2020    55081   243 2   Monroe_Wisconsin
7459    9/1/2020    55081   286 2   Monroe_Wisconsin
7460    9/2/2020    55081   290 2   Monroe_Wisconsin
user_102930680

Here is a potential solution:

  • Read the csv through pd.read_csv and make date the index of df directly and drop the unnecessary column.
df = pd.read_csv('df.csv', parse_dates=['date'],index_col=['date']) 
df.drop(columns=['Unnamed: 5'], inplace=True)
  • Use groupby together with resample.
df.groupby(['fips','county_state']).resample('1D').sum()

Output (Only the head of the dataframe):

                                 fips  cases  deaths
fips county_state     date                           
4013 Maricopa_Arizona 2020-01-26  4013      1       0
                      2020-01-27  4013      1       0
                      2020-01-28  4013      1       0
                      2020-01-29  4013      1       0
                      2020-01-30  4013      1       0
user_56045620
Related