pandas calculate date difference in a new column referencing previous cell-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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