DateOffset Panda subtraction
I have a dataFrame
[in] MyDates
[out]
2017-04-04 -5.0
2017-04-03 -5.0
2017-03-31 -4.0
2017-03-30 -6.0
2017-03-29 -5.0
2017-03-28 -5.0
Each number corresponds to how many days I have to add or remove from the corresponding date. I want to create a new column with the index date minus the number of days that are in column 1. I know I can do this using DateOffset but cannot figure out how ...
Thank!
source to share
You can convert the column to TimedeltaIndex
or to_timedelta
and add
( +
) or subtract the (-)
values:
df['new'] = df.index - pd.TimedeltaIndex(df['col'], unit='d')
print (df)
col new
2017-04-04 -5.0 2017-04-09
2017-04-03 -5.0 2017-04-08
2017-03-31 -4.0 2017-04-04
2017-03-30 -6.0 2017-04-05
2017-03-29 -5.0 2017-04-03
2017-03-28 -5.0 2017-04-02
Or:
df['new'] = df.index + pd.to_timedelta(df['col'], unit='d')
print (df)
col new
2017-04-04 -5.0 2017-03-30
2017-04-03 -5.0 2017-03-29
2017-03-31 -4.0 2017-03-27
2017-03-30 -6.0 2017-03-24
2017-03-29 -5.0 2017-03-24
2017-03-28 -5.0 2017-03-23
If Series
how to input
add to_frame
:
df = s.to_frame('date')
df['new'] = df.index - pd.TimedeltaIndex(df['date'], unit='d')
print (df)
date new
2017-04-04 -5.0 2017-04-09
2017-04-03 -5.0 2017-04-08
2017-03-31 -4.0 2017-04-04
2017-03-30 -6.0 2017-04-05
2017-03-29 -5.0 2017-04-03
2017-03-28 -5.0 2017-04-02
source to share
IIUC you want to build TimedeltaIndex
and add this:
In [173]:
df.index + pd.TimedeltaIndex(df['days'], unit='d')
Out[173]:
DatetimeIndex(['2017-03-30', '2017-03-29', '2017-03-27', '2017-03-24',
'2017-03-24', '2017-03-23'],
dtype='datetime64[ns]', freq=None)
If it is a column you just do df['Dates'] + pd.TimedeltaIndex(df['days'], unit='d')
In [176]:
df['offset_date'] = df['Dates'] + pd.TimedeltaIndex(df['days'], unit='d')
df
Out[176]:
Dates days offset_date
0 2017-04-04 -5.0 2017-03-30
1 2017-04-03 -5.0 2017-03-29
2 2017-03-31 -4.0 2017-03-27
3 2017-03-30 -6.0 2017-03-24
4 2017-03-29 -5.0 2017-03-24
5 2017-03-28 -5.0 2017-03-23
If it's an index and you want to add as a column, it's almost the same op:
In [180]:
df['offset_date'] = df.index + pd.TimedeltaIndex(df['days'], unit='d')
df
Out[180]:
days offset_date
Dates
2017-04-04 -5.0 2017-03-30
2017-04-03 -5.0 2017-03-29
2017-03-31 -4.0 2017-03-27
2017-03-30 -6.0 2017-03-24
2017-03-29 -5.0 2017-03-24
2017-03-28 -5.0 2017-03-23
source to share