Elegant way to add years as timedelta units to shift dates - Pandas

user_10829044 0
Viewed 1256

I have a dataframe like as shown below

df1 = pd.DataFrame({'person_id': [11,11,11,21,21],
                        'admit_dates': ['03/21/2015', '01/21/2016', '7/20/2018','01/11/2017','12/31/2011'],
                        'discharge_dates': ['05/09/2015', '01/29/2016', '7/27/2018','01/12/2017','01/31/2016'],
                        'drug_start_dates': ['05/29/1967', '01/21/1957', '7/27/1959','01/01/1961','12/31/1961'],
                        'offset':[223,223,223,310,310]})

What I would like to do is add offset which is in years to the dates columns.

So, I was trying to convert the offset to timedelta object with unit=y or unit=Y and then shift admit_dates

df1['offset'] = pd.to_timedelta(df1['offset'],unit='Y') #also tried with `y` (small y)
df1['shifted_date'] = df1['admit_dates'] + df1['offset']

However, I get the below error

ValueError: Units 'M' and 'Y' are no longer supported, as they do not represent unambiguous timedelta values durations.

Is there any other elegant way to shift dates by years?

python-datetime pandas timedelta dataframe python
3 Answers

One thing you can do is extract the year out of the date, and add it to the offset:

df1 = pd.DataFrame({'person_id': [11,11,11,21,21],
                        'admit_dates': ['03/21/2015', '01/21/2016', '7/20/2018','01/11/2017','12/31/2011'],
                        'discharge_dates': ['05/09/2015', '01/29/2016', '7/27/2018','01/12/2017','01/31/2016'],
                        'drug_start_dates': ['05/29/1967', '01/21/1957', '7/27/1959','01/01/1961','12/31/1961'],
                        'offset':[10,20,2,31,12]})
df1.admit_dates = pd.to_datetime(df1.admit_dates)

df1["new_year"] = df1.admit_dates.dt.year + df1.offset
df1["date_with_offset"] = pd.to_datetime(pd.DataFrame({"year": df1.new_year, 
                                                  "month": df1.admit_dates.dt.month, 
                                                  "day":df1.admit_dates.dt.day}))

The catch - with your original offsets, some of the dates cause the following error:

OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 2328-01-11 00:00:00

According to the documentation, the maximum date in pandas is Apr. 11th, 2262 (at about quarter to midnight, to be specific). It's probably because they keep time in nanoseconds, and that's when the out of bounds error occurs for this representation.

user_11055600

The max Timestamp supported in pandas is Timestamp('2262-04-11 23:47:16.854775807') so you could not be able to add 310 years to date 12/31/2011, one possible way is to use python's datetime objects which support a max year upto 9999 so you should be able to add 310 years to that.

from dateutil.relativedelta import relativedelta

df['admit_dates'] = pd.to_datetime(df['admit_dates'])
df['admit_dates'] = df['admit_dates'].dt.date.add(
    df['offset'].apply(lambda y: relativedelta(years=y)))

Result:

df
   person_id admit_dates discharge_dates drug_start_dates  offset
0         11  2238-03-21      05/09/2015       05/29/1967     223
1         11  2239-01-21      01/29/2016       01/21/1957     223
2         11  2241-07-20       7/27/2018        7/27/1959     223
3         21  2327-01-11      01/12/2017       01/01/1961     310
4         21  2321-12-31      01/31/2016       12/31/1961     310
user_128331660

Units 'Y' and 'M' becomes deprecated since pandas 0.25.0 But thanks to numpy timedelta64 through which we can use these units in the pandas Timedelta

import pandas as pd
import numpy as np

# Builds your dataframe
df1 = pd.DataFrame({'person_id': [11,11,11,21,21],
                    'admit_dates': ['03/21/2015', '01/21/2016', '7/20/2018','01/11/2017','12/31/2011'],
                    'discharge_dates': ['05/09/2015', '01/29/2016', '7/27/2018','01/12/2017','01/31/2016'],
                    'drug_start_dates': ['05/29/1967', '01/21/1957', '7/27/1959','01/01/1961','12/31/1961'],
                    'offset':[223,223,223,310,310]})

>>> df1
   person_id admit_dates discharge_dates drug_start_dates  offset
0         11  03/21/2015      05/09/2015       05/29/1967     223
1         11  01/21/2016      01/29/2016       01/21/1957     223
2         11   7/20/2018       7/27/2018        7/27/1959     223
3         21  01/11/2017      01/12/2017       01/01/1961     310
4         21  12/31/2011      01/31/2016       12/31/1961     310

>>> df1['shifted_date'] = df1.apply(lambda r: pd.Timedelta(np.timedelta64(r['offset'], 'Y'))+ pd.to_datetime(r['admit_dates']), axis=1)
>>> df1['shifted_date'] = df1['shifted_date'].dt.date
>>> df1
   person_id admit_dates discharge_dates drug_start_dates  offset shifted_date
0         11  03/21/2015      05/09/2015       05/29/1967     223   2238-03-21
1         11  01/21/2016      01/29/2016       01/21/1957     223   2239-01-21
2         11   7/20/2018       7/27/2018        7/27/1959     223   2241-07-20
....
user_107046220
Related