Pandas extract week of year and year from date

Question

I caught up with this scenario and don't know how can I solve this. I have the data frame where I am trying to add "week_of_year" and "year" column based in the "date" column of the pandas' data frame which is working fine.

import pandas as pd
df = pd.DataFrame({'date': ['2018-12-31', '2019-01-01', '2019-12-31', '2020-01-01']})
df['date'] = pd.to_datetime(df['date'])
df['week_of_year'] = df['date'].apply(lambda x: x.weekofyear)
df['year'] = df['date'].apply(lambda x: x.year)
print(df)

Current Output

       date       week_of_year    year
0    2018-12-31      1            2018
1    2019-01-01      1            2019
2    2019-12-31      1            2019
3    2020-01-01      1            2020

Expected Output

So here what I am expecting is for 2018 and 2019 the last date was the first week of the new year which is 2019 and 2020 respectively so I want to add logic in the year, where the week is 1 but the date belongs for the previous year so the year column would track that as in the expected output.

           date       week_of_year    year
    0    2018-12-31      1            2019
    1    2019-01-01      1            2019
    2    2019-12-31      1            2020
    3    2020-01-01      1            2020

Grzegorz Skibinski Grzegorz Skibinski · Accepted Answer · 2020-05-06T13:58:32

Try:

df['date'] = pd.to_datetime(df['date'])
df['week_of_year'] = df['date'].dt.weekofyear
df['year']=(df['date']+pd.to_timedelta(6-df['date'].dt.weekday, unit='d')).dt.year

Outputs:

        date  week_of_year  year
0 2018-12-31             1  2019
1 2019-01-01             1  2019
2 2019-12-31             1  2020
3 2020-01-01             1  2020

Few things - generally avoid .apply(..).

For datetime columns you can just interact with the date through df[col].dt variable.

Then to get the last day of the week just add to date 6-weekday where weekday is between 0 (Monday) and 6 to the date

Pandas extract week of year and year from date

1 Answers