Python - Converting a column with weekly data to a datetime object

user_19551721 0
Viewed 44

I have the following date column that I would like to transform to a pandas datetime object. Is it possible to do this with weekly data? For example, 1-2018 stands for week 1 in 2018 and so on. I tried the following conversion but I get an error message: Cannot use '%W' or '%U' without day and year

import pandas as pd

df1 = pd.DataFrame(columns=["date"])
df1['date'] = ["1-2018", "1-2018", "2-2018", "2-2018", "3-2018", "4-2018", "4-2018", "4-2018"]

df1["date"] = pd.to_datetime(df1["date"], format = "%W-%Y")
pandas datetime python
2 Answers

You need to add a day to the datetime format

df1["date"] = pd.to_datetime('0' + df1["date"], format='%w%W-%Y')
print(df1)

Output

        date
0 2018-01-07
1 2018-01-07
2 2018-01-14
3 2018-01-14
4 2018-01-21
5 2018-01-28
6 2018-01-28
7 2018-01-28
user_51680110

As the error message says, you need to specify the day of the week by adding %w :

df1["date"] = pd.to_datetime( '0'+df1.date, format='%w%W-%Y')
user_190414370
Related