Pandas - Date Difference Using Column as Offset

Question

I have a df with two columns: date and offset(integer value). I want to subtract the number of months (based on the column value) from the date and get the month-end date. I've tried:

df['NewDate'] = df['Date'] - relativedelta(months=df['Offset'])

and:

df['NewDate'] = df.apply(lambda x: x['Date']-pd.offsets.MonthEnd(months=x['Offset']), axis=1)

but couldn't get either to work (the second runs very slow due to df.apply anyways).

Orig df
Date      Offset
12/31/17  0  
12/31/17  1
12/31/17  2
12/31/17  3

New df
Date      Offset  NewDate
12/31/17  0       12/31/17
12/31/17  1       1/31/18   
12/31/17  2       2/28/18
12/31/17  3       3/31/18

score 3 · Accepted Answer · answered Jan 11 '18 at 15:53

3

You can use numpy for faster solution:

df['NewDate'] = df['Date'].values.astype('datetime64[M]') + 
                df['Offset'].add(1).values.astype('timedelta64[M]') - 
                np.array([1], dtype='timedelta64[D]')
print (df)
        Date  Offset    NewDate
0 2017-12-31       0 2017-12-31
1 2017-12-31       1 2018-01-31
2 2017-12-31       2 2018-02-28
3 2017-12-31       3 2018-03-31

answered Jan 11 '18 at 15:53

jezrael

822,522
95
1,334
1,252

Perfect. Thank you! Can you explain the 2nd and 3rd parts of the solution? – Walt Reed Jan 11 '18 at 16:05
1

Yes. You can add `1` to offset for add next month, so e.g. for 1 is added 2 months and then is subtract 1 day for last month day. – jezrael Jan 11 '18 at 16:33
1

Thank you, this helped so much! – Walt Reed Jan 11 '18 at 17:08

Pandas - Date Difference Using Column as Offset

1 Answers1