Create a rolling sum of a column which is grouped by weeks on a rolling basis in python pandas

user_11693768 0
Viewed 267

I have the following pandas dataframe,

   status1  status2 location1   datetime1           grouping    service capacity 
0   xx      xx      xx          01-01-2020 11:50:00 xx          xx       150
1   xx      xx      xx          01-01-2020 11:57:00 xx          xx       200
2   xx      xx      xx          01-01-2020 11:59:00 xx          xx       200
3   xx      xx      xx          01-01-2020 13:59:00 xx          xx       200
...
x   xx      xx      xx          01-02-2020 13:59:00 xx          xx       300
x   xx      xx      xx          01-03-2020 13:04:00 xx          xx       300
...
x   xx      xx      xx          07-03-2021 13:04:00 xx          xx       400
x   xx      xx      xx          07-03-2021 13:04:00 xx          xx       300
x   xx      xx      xx          07-03-2021 13:04:00 xx          xx       300

I want to sum up the capacities for each week on a rolling basis.

For for example I want

   
   WeekStartingSunday   countofstatus1      sumofcapacity
0                   1               50               3000
1                   2               30               2000
2                   3              ...                ...
3                   4              ...                ...
...

So week 1 contains the sum of all the dates within the first week of 2020. The week would be startingSunday. I also want to create tables for other days like Monday Tuesday etc.

I tried df.groupby('capacity').rolling(7).sum() but it just sums up every 7 rows i think.

I also tried,

group = pd.pivot_table(df,columns='capacity', index='datetime1')
group2 = group.resample('D').sum().rolling(7).sum()
group2.sort_index().head(15)

But it looks like this,

capacity    1.0   2.0   2.25 2.40 3.0....
datetime1
2020-01-01  NaN   NaN   NaN  NaN NaN ...
2021-01-02  NaN   NaN   NaN  NaN NaN ...
...
2021-01-07  322.1 326.5 117  0.0 275.2 ...
...

Can this be done in pandas ?

rolling-computation pandas dataframe python
1 Answers

You can try:

  1. Convert date strings to datetime format. Assume dates in dd-mm-YYYY
# Use dayfirst=True for dates in dd-mm-YYYY
df['datetimea1'] = pd.to_datetime(df['datetimea1'], dayfirst=True)
  1. Define column with iso calendar week by .dt.isocalendar().week :
df['WeekStartingMonday'] = df['datetimea1'].dt.isocalendar().week
  1. Group on new column WeekStartingMonday by .groupby and aggregate entries for a week by .agg(), as follows:
df_out = (df.groupby('WeekStartingMonday', as_index=False)
            .agg(countofstatus1=('status1', 'count'), sumofcapacity=('capacity', 'sum'))
         )

Input:

  status1 status2 location1           datetimea1 grouping service  capacity
0      xx      xx        xx  01-01-2020 11:50:00       xx      xx       150
1      xx      xx        xx  01-01-2020 11:57:00       xx      xx       200
2      xx      xx        xx  01-01-2020 11:59:00       xx      xx       200
3      xx      xx        xx  01-01-2020 13:59:00       xx      xx       200
4      xx      xx        xx  01-02-2020 13:59:00       xx      xx       300
5      xx      xx        xx  01-03-2020 13:04:00       xx      xx       300
6      xx      xx        xx  07-03-2021 13:04:00       xx      xx       400
7      xx      xx        xx  07-03-2021 13:04:00       xx      xx       300
8      xx      xx        xx  07-03-2021 13:04:00       xx      xx       300

Output:

print(df_out)

   WeekStartingMonday  countofstatus1  sumofcapacity
0                   1               4            750
1                   5               1            300
2                   9               4           1300

Edit

1) Other options of Sunday/Monday as the first day of the week:

You can use .dt.strftime() with various format strings to get the week number as follows:

%U week number of year, with Sunday as first day of week (00..53).

%V ISO week number, with Monday as first day of week (01..53).

%W week number of year, with Monday as first day of week (00..53).

df['WeekStartingSunday'] = df['datetimea1'].dt.strftime('%U')

or:

df['WeekStartingMonday'] = df['datetimea1'].dt.strftime('%V')

or:

df['WeekStartingMonday'] = df['datetimea1'].dt.strftime('%W')

For more information, you can refer to the official document of strftime() format codes.

There is no corresponding options to start a week with Tuesday to Saturday. If you have absolute need to get such custom week, you can do it by some workaround solution below.

2) To define any day of a week as the first day of the week:

You can convert your dates to Period representing weeks that end on a particular day of the week. For example: to get distinct week numbers for a week starting on Tuesday (ending on Monday), you can use:

df['Period'] = df['datetimea1'].dt.to_period('W-MON')     # W-MON is the for a custom week ending on MON (i.e. starting on TUE)
df['WeekStartingTuesday'] = df['Period'].dt.week

Note that in this way, the first week of the first day of the year e.g. 2020-01-01 may have a week number of 1 or 2. Anyway, it should still serve well for your use case which requires distinct week numbers for consecutive 7 days.

There is no direct method for setting bi-weekly frequency to start/end on a particular day of week. One further workaround could be e.g.

df['Period'] = df['datetimea1'].dt.to_period('W-MON')       # W-MON is the for a custom week ending on MON (i.e. starting on TUE)
df['BiWeeklyStartingTuesday'] = df['Period'].dt.week // 2   # Get bi-weekly number
user_150706970
Related