pandas get a sum column for next 7 days

Question

I want to get the sum of values for next 7 days of a column

my dataframe :

         date  value
0  2021-04-29      1
1  2021-05-03      2
2  2021-05-06      1
3  2021-05-15      1
4  2021-05-17      2
5  2021-05-18      1
6  2021-05-21      2
7  2021-05-22      5
8  2021-05-24      4

i tried to make a new column that contains date 7 days from current date

df['temp'] = df['date'] + timedelta(days=7)

then calculate value between date range :

df['next_7days'] = df[(df.date > df.date) & (df.date <= df.temp)].value.sum()

But this gives me answer as all 0.

intended result:

         date  value  next_7days
0  2021-04-29      1           3
1  2021-05-03      2           1
2  2021-05-06      1           0
3  2021-05-15      1          10
4  2021-05-17      2          12
5  2021-05-18      1          11
6  2021-05-21      2           9
7  2021-05-22      5           4
8  2021-05-24      4           0

The method iam using currently is quite tedious, are their any better methods to get the intended result.

Wouldn't for any column `x`, the expression `x > x` always be False? You can use `>=` — stochastic13, May 24 '21 at 11:26
How big is the dataframe. Would a loop over all entries be feasible? — stochastic13, May 24 '21 at 11:47
The solution here - https://stackoverflow.com/questions/41642320/efficient-pandas-rolling-aggregation-over-date-range-by-group-python-2-7-windo seems like a good way — user88484, Nov 16 '22 at 08:33

score 1 · Accepted Answer · answered May 24 '21 at 11:46

With a list comprehension:

tomorrow_dates = df.date + pd.Timedelta("1 day")
next_week_dates = df.date + pd.Timedelta("7 days")

df["next_7days"] = [df.value[df.date.between(tomorrow, next_week)].sum()
                    for tomorrow, next_week in zip(tomorrow_dates, next_week_dates)]

where we first define tomorrow and next week's dates and store them. Then zip them together and use between of pd.Series to get a boolean series if the date is indeed between the desired range. Then using boolean indexing to get the actual values and sum them. Do this for each date pair.

to get

        date  value  next_7days
0 2021-04-29      1           3
1 2021-05-03      2           1
2 2021-05-06      1           0
3 2021-05-15      1          10
4 2021-05-17      2          12
5 2021-05-18      1          11
6 2021-05-21      2           9
7 2021-05-22      5           4
8 2021-05-24      4           0

pandas get a sum column for next 7 days

1 Answers1