Guillaume Thomas Guillaume Thomas - 5 months ago 41
Python Question

Truncate date to last monday or any day of the week in python

Given a

pandas.Series
storing timestamps such as :

In [14]: x
Out[14]:
0 2015-11-03
1 2015-11-17
2 2015-12-08
3 2015-12-22
4 2016-01-05
dtype: datetime64[ns]


I want to truncate the date so that i have only last monday of each timestamp

In [14]: x
Out[14]:
0 2015-11-02
1 2015-11-16
2 2015-12-07
3 2015-12-21
4 2016-01-04
dtype: datetime64[ns]

Answer

You can construct a TimedeltaIndex using the dt.dayofweek attribute as Monday is day 0. Any day of week greater than this will result in timedeltas equivalent to the number of days to subtract:

In [49]:
s = s - pd.TimedeltaIndex(s.dt.dayofweek, unit='D')
s

Out[49]:
index
0   2015-11-02
1   2015-11-16
2   2015-12-07
3   2015-12-21
4   2016-01-04
Name: date, dtype: datetime64[ns]

E.g. for 2015-11-05, 2015-11-04, 2015-11-03, 2015-11-02 the output of pd.TimedeltaIndex(s.dt.dayofweek, unit='D') is 3 days, 2 days, 1 days, 0 days, respectively.