pandas calculate date difference in a new column referencing previous cell

Rajesh Swarnkar 0
Viewed 31

I have pandas dataframe that contains dates in column Date. I need to add another column Days which contains the date difference from previous cell. So date in ith cell should difference from i-1th. And for the first difference consider it to be 0.

Date    Days
08-01-1997  0
09-01-1997  1
10-01-1997  1
13-01-1997  3
14-01-1997  1
15-01-1997  1
01-03-1997  45
03-03-1997  2
04-03-1997  1
05-03-1997  1
13-06-1997  100

I tried this but not useful.

pandas python
2 Answers

First convert the Date column to pandas DateTime object, then calculate the difference which is timedelta object, from there, take the days from Series.dt and assign 0 to first value

>>> df['Date']=pd.to_datetime(df['Date'], dayfirst=True)
>>> df['Days']=(df['Date']-df['Date'].shift()).dt.days.fillna(0).astype(int)

OUTPUT

df
         Date  Days
0  1997-01-08     0
1  1997-01-09     1
2  1997-01-10     1
3  1997-01-13     3
4  1997-01-14     1
5  1997-01-15     1
6  1997-03-01    45
7  1997-03-03     2
8  1997-03-04     1
9  1997-03-05     1
10 1997-06-13   100
ThePyGuy0

you can use diff as well

df['date_up'] = pd.to_datetime(df['Date'],dayfirst=True)
df['date_diff'] = df['date_up'].diff()
df['date_diff_num_days'] = df['date_diff'].dt.days.fillna(0).astype(int)
df.head()

Date    Days    date_up date_diff   date_diff_num_days
0   08-01-1997  0   1997-01-08  NaT 0
1   09-01-1997  1   1997-01-09  1 days  1
2   10-01-1997  1   1997-01-10  1 days  1
3   13-01-1997  3   1997-01-13  3 days  3
4   14-01-1997  1   1997-01-14  1 days  1
sayan dasgupta0
Related