Converting date to Monday date of each week

user_8913983 0
Viewed 47

I have a df:

date
2021-06-28
2021-06-29
2021-06-30
2021-07-02
2021-07-04
2021-07-07
2021-08-06
2021-08-07

I am trying to convert this to week, I know I can use df.date.dt.isocalendar().week but this returns the week number with the default start date, whereas my start date is 2021-06-28. I can achieve the dates in df using pd.Grouper(key = 'date', freq = 'W-MON', label = 'left', closed = 'left') in a groupby but I would like to have the same output without using groupby - simply applying a function to date column.

My goal is to have:

date
2021-06-28
2021-06-28
2021-06-28
2021-06-28
2021-06-28
2021-07-05
2021-08-02
2021-08-02

Because these dates represent the Monday of each week that the date falls in.

pandas date python
1 Answers

You can convert to 'W-SUN' period and get the first day:

df['date'] = pd.to_datetime(df['date'])

df['Monday'] = df['date'].dt.to_period('W-SUN').dt.start_time

NB. why 'W-SUN'? This indicates the weeks ending in SUN(day), so starting on Mondays. See anchored offsets.

output:

        date     Monday
0 2021-06-28 2021-06-28
1 2021-06-29 2021-06-28
2 2021-06-30 2021-06-28
3 2021-07-02 2021-06-28
4 2021-07-04 2021-06-28
5 2021-07-07 2021-07-05
6 2021-08-06 2021-08-02
7 2021-08-07 2021-08-02
user_163434640
Related