CODEWITHSUNDEEP
pythonpandasdatetimeweek-number
Lucas Dresl
Lucas Dresl

Reputation: 1170

Transforming dates: from year and week number into datetime

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

Upvotes: 0

Views: 3129

Answers (2)

mohd4482
mohd4482

Reputation: 1918

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.

Upvotes: 0

benvc
benvc

Reputation: 15130

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

Upvotes: 5

Related Questions