use grouper to aggregate by week from the exact day-CodePudding

I would like to aggregate my data on week, using pandas grouper, where the week ends at the exact same day of my last date, and not the end of the week.

This is the code I wrote:

fp.groupby(pd.Grouper(key='date',freq='w')).collectionName.nunique().tail(10)

And these are the results:

date
2021-10-03     644
2021-10-10     698
2021-10-17     756
2021-10-24     839
2021-10-31     883
2021-11-07     905
2021-11-14     961
2021-11-21    1028
2021-11-28     990
2021-12-05     726
Freq: W-SUN, Name: collectionName, dtype: int64

The last date I have is 2021-12-02, so I would like that to be the last day of the week aggregate, and it goes back every 7 days, to the end (in this case beginning of the dataset).

I need help with this.

CodePudding user response：

Use pd.DataFrame.resample with rule='1w', on='date' and origin='end_day'

CodePudding user response：

This assumes you can find the last date prior to grouping. See references here: https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#offset-aliases and here: https://www.programiz.com/python-programming/datetime/strftime

df = pd.DataFrame({'date': pd.date_range(start='2021-01-01 01:04:16', periods=250), 'val':range(1,251)})

                   date  val
0   2021-01-01 01:04:16    1
1   2021-01-02 01:04:16    2
2   2021-01-03 01:04:16    3
3   2021-01-04 01:04:16    4
4   2021-01-05 01:04:16    5
..                  ...  ...
245 2021-09-03 01:04:16  246
246 2021-09-04 01:04:16  247
247 2021-09-05 01:04:16  248
248 2021-09-06 01:04:16  249
249 2021-09-07 01:04:16  250

[250 rows x 2 columns]

# locate last date and get day of week in correct format
anchor = df['date'].iat[-1].strftime("%a")

df.groupby(pd.Grouper(key='date',freq='w-'   anchor)).nunique().tail(5)

# week ends on the same day as the original dataset
            val
date
2021-08-10    7
2021-08-17    7
2021-08-24    7
2021-08-31    7
2021-09-07    7