I am trying to transform the following dataframe
id year week
1 2018 43
1 2019 1
2 2019 4
3 2018 51
into a dataframe containing the following column
id year week year_week
1 2018 43 2018-43
1 2019 1 2019-1
2 2019 4 2019-4
3 2018 51 2018-51
where "year_week" is a datetime type
You will need to pick a day of the week in order to create your timestamp from that data. Assuming these are ISO weeks, I picked "1" for the Monday that begins the ISO week (also added a column to convert to the string format shown in your question).
If you really want the column data to be datetime objects instead of pandas.Timestamp, see Converting between datetime and Timestamp objects for another step you will need to include.
from datetime import datetime
import pandas as pd
def year_week(y, w):
return datetime.strptime(f'{y} {w} 1', '%G %V %u')
df = pd.DataFrame([(2018, 43), (2019, 1), (2019, 4), (2018, 51)], columns=['year', 'week'])
df['year_week_ts'] = df.apply(lambda row: year_week(row.year, row.week), axis=1)
df['year_week_str'] = df.apply(lambda row: row.year_week_ts.strftime('%G-%V'), axis=1)
print(df)
# year week year_week_ts year_week_str
# 0 2018 43 2018-10-22 2018-43
# 1 2019 1 2018-12-31 2019-01
# 2 2019 4 2019-01-21 2019-04
# 3 2018 51 2018-12-17 2018-51
# for python 3 versions pre-3.6 use '{} {} 1'.format(y, w) instead of the f string above
You may want to have a look over the epiweeks package!
Given the data you provided and assuming the ISO week numbering system, let's say that you want to create a column with the week ending date:
from epiweeks import Week
df['ending'] = df.apply(
lambda row: pd.to_datetime(Week(row.year, row.week, 'iso').enddate()),
axis=1
)
That results in a new column of datetime type.
Hopefully that what you are looking for.
