Calculations in a dataframe based on datetime values (with missing dates), without filling the dataframe with empty dates

user_11957996 0
Viewed 98

I have a dataframe similar to the one below and I want to calculate the sum of the value column for the last seven days. The problem is that there isn't necessarily a row for each day.

df = pd.DataFrame({
    'value': [2,3,7,14],
    'date': ['10/20/2005','10/22/2005','10/25/2005','10/27/2005']
})

df['date'] = pd.to_datetime(df['date'])
df
value   date
    2   2005-10-20
    3   2005-10-22
    7   2005-10-25
    14  2005-10-27

What I would like to is something like

 df['value'].sum('Last 7 days')
 26

The solutions to the problem that I found were always about filling the df with the missing dates, using .asfreq() or .reindex(). Unfortunately, that is not an option for me since I have way too many classes that are each represented in a df like the above one. So filling the df up with the missing dates would create thousands and thousands of extra rows.

Is there a way to use pd.Timedelta() (or similar), where I can treat the missing days as zeros?

missing-data pandas datetime python
1 Answers

Rolling has this intelligently built into the function for datetime based columns:

df.rolling('7d', on='date').sum()

Note that 10/27 and 10/20 are 8 days apart not 7 :)

And if you want to put it into another column:

df['sum'] = df.rolling('7d', on='date').sum()['value']

If you just want the final value:

df.rolling('7d', on='date').sum()['value'].iloc[-1]
user_82171120
Related