How to round dates to week starts in Pandas

Question

I'm having a DataFrame with a date column. How can I map each date d to the start day of the week containing d?

Solution 1

import pandas as pd
df['Date'] - pd.to_timedelta(df['Date'].dt.dayofweek, unit='d')

Solution 2

Here is an alternative approach for calculating beginning of the week series by using convenience method pd.DateOffset(weekday=0,weeks=1):

import pandas as pd, numpy as np
df=pd.DataFrame({'date':pd.date_range('2016-10-01','2016-10-31')})
df['BeginWeek']=np.where(df.date.dt.weekday==0, # offset on Non Mondays only
                         df['date'], 
                         df['date']-np.timedelta64(1,'W')), 
                         )

Thanks to ribitskyib np.where was added to pick current Monday when date is already Monday. Confirmation that the above works now:

Some additional ideas provided by others:

Here is a quick list of days of the week:

df['BeginWeek'].dt.strftime("%a").unique() 
array(['Mon'], dtype=object)

and the days in the original column are:

df['date'].dt.strftime("%a").unique()
array(['Sat', 'Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri'], dtype=object)

Solution 3

If you don't have dates, the accepted answer won't work (at least for me). If you have pandas._libs.tslibs.timestamps.Timestamp objects use this instead:

df["week_start"] = pd.to_datetime(["timestamp_col"]).dt.to_period('W-SUN').dt.start_time

GH discussion on it here

Comments

[ribitskiyb]

It gives incorrect (in the context of the question) result if the date is already Monday -- it shifts it back to Monday of the previous week pd.Timestamp('2019-10-07') - pd.DateOffset(weekday=0, weeks=1) gives Timestamp('2019-09-30 00:00:00')