2

What is the most efficient way to convert a pandas dataframe date column to a 'week beginning' column? In my case I want to convert to Sunday of that week. For example, I want 2016-04-01 to convert to 2016-03-27.

Mateyobi
  • 123
  • 2
  • 13

3 Answers3

2

Use to_period("W-SAT") to convert to period series and then use start_time to get the start time of the period:

import pandas as pd

di = pd.date_range("2016/01/01", "2016/04/10").to_series()
di.dt.to_period("W-SAT").dt.start_time
HYRY
  • 94,853
  • 25
  • 187
  • 187
1

You could use to_period('W') conversion on index and the offset it previous week

In [56]: ts
Out[56]:
2016-04-01   -1.174966
2016-04-02   -0.518799
2016-04-03   -0.598929
2016-04-04    0.085304
2016-04-05   -0.648838
2016-04-06   -0.427322
2016-04-07    0.146146
2016-04-08   -1.957471
2016-04-09   -0.302514
2016-04-10    1.249215
Freq: D, dtype: float64

In [57]: ts.index.to_period('W').to_timestamp('W') + pd.offsets.DateOffset(-7)
Out[57]:
DatetimeIndex(['2016-03-27', '2016-03-27', '2016-03-27', '2016-04-03',
               '2016-04-03', '2016-04-03', '2016-04-03', '2016-04-03',
               '2016-04-03', '2016-04-03'],
              dtype='datetime64[ns]', freq=None)

If you had date column like

In [90]: ds
Out[90]:
        date       val
0 2016-04-01  0.087695
1 2016-04-02 -0.163535
2 2016-04-03 -1.069274
3 2016-04-04  1.415452
4 2016-04-05 -1.100268
5 2016-04-06  0.239589
6 2016-04-07 -1.045833
7 2016-04-08 -0.325026
8 2016-04-09 -0.423831
9 2016-04-10 -1.320371

You can work with dt - accessor object for datetimelike properties of the Series values. 

In [91]: ds['date'].dt.to_period('W').dt.to_timestamp('W') + pd.offsets.DateOffset(-7)
Out[91]:
DatetimeIndex(['2016-03-27', '2016-03-27', '2016-03-27', '2016-04-03',
               '2016-04-03', '2016-04-03', '2016-04-03', '2016-04-03',
               '2016-04-03', '2016-04-03'],
              dtype='datetime64[ns]', freq=None)
Zero
  • 74,117
  • 18
  • 147
  • 154
  • this works well when the date column is an index. how do I make it work when the date column is not an index? – Mateyobi Apr 02 '16 at 18:40
  • im doing `data['Sunday'] = data['Date'].dt.to_period('W').dt.to_timestamp('W') + pd.offsets.DateOffset(-7)` and it says `AttributeError: 'PeriodProperties' object has no attribute 'to_timestamp''` – Mateyobi Apr 02 '16 at 19:43
  • Pandas version, `pd.__version__` and dtype is `ds['date'].dtype`? – Zero Apr 02 '16 at 19:50
  • `version: 0.17.1 Date dtype: datetime64[ns]` – Mateyobi Apr 02 '16 at 21:53
1

You can check out this post and apply it here: Find the Friday of previous/last week in python

from dateutil.relativedelta import relativedelta, SU
from datetime import datetime
import pandas as pd

df = pd.DataFrame({'date': [datetime(2016, 4, 1), datetime(2016, 1, 1)]})
relative_delta = relativedelta(weekday=SU(-1))
df['date'] = df['date'].apply(lambda x: x+relative_delta)
Community
  • 1
  • 1
Quang Nguyen
  • 147
  • 1
  • 9