pandas datetime set Sunday as first day of the week

Question

I've got a dataframe of pandas with a series of dates (all Sundays) like this:

Date        Year    Week
2011-01-02  2011    52
2011-01-23  2011    3
2011-01-23  2011    3
2011-01-30  2011    4
2011-01-30  2011    4

The week is given by df['Date'].dt.week, and what I want is set Sundays as the first day of the week, so I can get:

Date        Year    Week
2011-01-02  2011    1
2011-01-23  2011    4
2011-01-23  2011    4
2011-01-30  2011    5
2011-01-30  2011    5

How can I do that in the simplest way?

P.S. I have failed to mention that there're multiple years in this dataset. So for rare occasions there'll be the last day of the year is Sunday, I would like to get The 53rd week of this year other than The 1st week of next year.

Answer 1

You can use dayofweek to get that.

date = pd.DatetimeIndex(start='2011-01-02', end='2011-12-31',freq='D')
df = pd.DataFrame([date,date.year,date.week,date.dayofweek])
df = df.T
df.columns=['date','year','week','dayofweek']
df['newweek'] = 0
df.loc[df['dayofweek']==6, 'newweek'] = 1
df['newweek'] = df['newweek'].cumsum()

If you have multiple years, than do a rolling operation on the datetimeindex.

Answer 2

A simple and quick answer would be the following:

df['Week'] = df['Date'].apply(lambda x: (x + dt.timedelta(days=1)).week)

df
        Date  Year  Week
0 2011-01-02  2011     1
1 2011-01-23  2011     4
2 2011-01-23  2011     4
3 2011-01-30  2011     5
4 2011-01-30  2011     5

Basically the first day is the Monday so applying a timedelta shifts your datetime (Sundays) to the following day (Mondays)