CODEWITHSUNDEEP
pythonpandasdatedataframe
Imsa
Imsa

Reputation: 1125

Pandas Start Week on Sunday when Using Group By for Dates and counting events in the period

I have some data and a date column. By running the command below, it goes through the DF and counts all the events happened during that week. 

df['date'].groupby(df.date.dt.to_period("W")).agg('count')

The result is something like:

2018-04-16/2018-04-22    40
2018-04-23/2018-04-29    18

The weeks starts on Monday and end Sunday.

I want the week to start on Sunday and end on Saturday. So, the data should be

2018-04-15/2018-04-21    40
2018-04-22/2018-04-28    18

Upvotes: 2

Views: 3017

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

Use:

df = pd.DataFrame({'Date':np.random.choice(pd.date_range('2018-04-10',periods=365, freq='D'),1000)})

df.groupby(df['Date'].dt.to_period('W-SAT')).agg('count')

Output:

                       Date
Date                       
2018-04-08/2018-04-14    12
2018-04-15/2018-04-21    19
2018-04-22/2018-04-28    21
2018-04-29/2018-05-05    16
2018-05-06/2018-05-12    21

Upvotes: 6

sushain97
sushain97

Reputation: 2802

Use an anchored offset. Excerpt from the linked table:

W-SUN   weekly frequency (Sundays). Same as ‘W’
W-MON   weekly frequency (Mondays)
W-TUE   weekly frequency (Tuesdays)
W-WED   weekly frequency (Wednesdays)
W-THU   weekly frequency (Thursdays)
W-FRI   weekly frequency (Fridays)
W-SAT   weekly frequency (Saturdays)

Since you want the week to end on Saturday, W-SAT should suffice.

Upvotes: 2

Related Questions