Dataframe as follows
date state Deaths Cases Day Avg day of case
2020-03-01 AK 0 15.5000 1.0 1.0
2020-03-02 AK 0 28.4048 2.0 0.0
2020-03-03 AK 0 43.8333 3.0 0.0
2020-03-04 AK 0 60.8905 4.0 0.0
2020-03-05 AK 0 81.3548 5.0 0.0
The dataframe is grouped by state and date. The "Avg Day of case" at each state grouping is the first day in the "Day" column. The first day for each state is "03-01-2020". If the state started recording later (ex: Alabama started recording on 03-13-2020" this would be the 13th day in the "Day" column. I did this because 03-01-2020 is when most states started having cases. To achieve the above, I did the following code:
df['date'] = pd.to_datetime(df['date'])
df = df.dropna()
df = df[df['date'] >= '03-01-2020']
# getting the day column
df['Day'] = df.groupby('state')['date'].rank(ascending=True)
#creating the avg day of case
df['Avg day of case'] = 0
#getting the first day to be the first day of the Avg day of case
df.loc[df.groupby('state')['date'].head(1).index, 'Avg day of case'] = df.loc[:,'Day']
This was all fine. Now here's the hard part. To finish the "Avg day of case", I wanted to apply the following formula that takes in the day and the cases, to figure out when the average day of the cases was.
for i in range(1, len(df)):
x = (((df.loc[i, 'Cases'] - df.loc[i-1, 'Cases']) * (df.loc[i, 'Day']- df.loc[i-1, 'Avg day of
case'])) / (df.loc[i, 'Cases'])) + (df.loc[i-1, 'Avg day of case'])
df.loc[i, 'Avg day of case'] = round(x, 2)
However when I do this, I always get
KeyError: 0
The above exception was the direct cause of the following exception:
I am not really understanding why though. I ordered dates and grouped by state. After, I applied code which would make the first value of each state grouping the first day of cases. This should have gotten rid of the problem of dividing by zero. What am I doing wrong?
Here is an example excel sheet where the formula did work. Thanks in advance.
