why row differences by column in time series data goes wrong?

user_14126595 0
Viewed 96

I tried to understand how covid cases are changing since pandemic begun, and NYT provides daily covid cases data by county level. I used the data and want to see how new casea are changing historically. To do so, I used pandas builtin function diff() to do get difference, but I got wrong output, somewhere in the output, it got negative value, which I don't understand why. Can anyone point me out what goes wrong by using pandas.diff()? Any idea?

current attempt

Here is the NYT covid data statistics repos, and I attempted following:

import pandas as pd
url = "https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties.csv"
df= pd.read_csv(url, parse_dates=['date'])
df.drop(columns=['Unnamed: 0'], inplace=True)
df['new_cases'] = df.groupby(['county'])['cases'].diff()
df['new_deaths'] = df.groupby(['county'])['deaths'].diff()
df= df.fillna(0)

if we run this code, new cases for some counties got negative values which doesn't make sense. what I want to do is, how many cases is increasing, or remain same or decreasing respect to previous days. I don't understand why pandas.diff() goes wrong. What's the more accurate and secure way to do this in pandas? Any thoughts?

new update

if above attempt goes right, this is my next step that I want to make plot, and I did like this by continuing above attempt:

  df['new_cases_rol7'] = df[['new_cases']].rolling(7).mean()

but problem is new_cases calculation goes wrong.

I spotted negative new_cases value doing df.to_csv("file.csv") and opened it on ms excel. I spot this issue because when I did plot, some plots looks so off, I think pandas.diff() goes wrong. any idea what goes wrong?

pandas time-series python
2 Answers

Here's an instance of either questionable data, or a missing "recovery" column, or some other explanation. I subselected 1 country from Washington, then looked at a 10 day period within that subset. As you can see, on 2020-03-24 to the following day the cases decreased by 1. However there was no death in the "deaths" column. It seems like the diff is working correctly in this case, just that the "cases" column actually decreases between these 2 observations. This was just the first case I found, but if there's one I'm sure there's more.

import pandas as pd
url = "https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties.csv"
df= pd.read_csv(url, parse_dates=['date'])

washington_df = (df.query("state == 'Washington' & county == 'Grant'")
                 .assign(cases_diff=lambda df: df["cases"].diff())
                 .set_index("date")
                )

print(washington_df.loc["2020-03-20": "2020-03-30"])
           county       state     fips  cases  deaths  cases_diff
date                                                             
2020-03-20  Grant  Washington  53025.0     11       0         3.0
2020-03-21  Grant  Washington  53025.0     11       0         0.0
2020-03-22  Grant  Washington  53025.0     18       0         7.0
2020-03-23  Grant  Washington  53025.0     23       0         5.0
2020-03-24  Grant  Washington  53025.0     27       0         4.0
2020-03-25  Grant  Washington  53025.0     26       0        -1.0
2020-03-26  Grant  Washington  53025.0     33       1         7.0
2020-03-27  Grant  Washington  53025.0     42       1         9.0
2020-03-28  Grant  Washington  53025.0     44       1         2.0
2020-03-29  Grant  Washington  53025.0     58       1        14.0
2020-03-30  Grant  Washington  53025.0     62       0         4.0

Acutally, just poking around on the github data repo. Check this link: https://github.com/nytimes/covid-19-data#user-content-methodology-and-definitions and scroll down to "declining counts" this explains it.

This chunk of code will enforce cases to be changing by >= 0.

groupby = ["state", "county"]
df["new_cases"] = df.groupby(groupby)["cases"].diff()

(df["new_cases"] < 0).any() # we have negatives in our `new_cases`
>>> True

We've detected we have negative increments in our "new_cases" column. So let's perform some data cleaning to ensure that df["new_cases"] >= 0. To my knowledge there's no super fast or efficient way of doing this (aside from dropping observations with negative new_cases). This will take a while to run. It'll probably be more worth your time to just drop the observations that have a negative increment.

def force_incremental(df, groupby, incremental_col):
    df = df.copy()
    
    while True:
        cases_diff = df.groupby(groupby)[incremental_col].diff()
        decreasing_mask = cases_diff < 0
        if not decreasing_mask.any():
            break

        decreasing_cases = df.where(decreasing_mask)
        df.update(decreasing_cases.groupby(groupby).shift(-1)["cases"])
        
    return df

new_df = (df.query("county != 'Unknown'")
        .set_index(["state", "county", "date"])
        .pipe(force_incremental, ["state", "county"], "cases")
        .assign(cases_diff=lambda df: df.groupby(["state", "county"])["cases"].diff())
        .reset_index()
       )
user_142784480

I assume there is a logical error. You didn't aggregate by date. It might well be, that on one day the new_cases go up and on another day it goes down. This is why diff gets negative numbers.

you can do:

df['new_cases'] = df.groupby(['date','county']).sum() # to aggregate on county per day

additionally, you can do a .cumsum() to see how the number aggregate over date.

It that is not what you want, I might help to rephrase the question.

user_50083800
Related