Calculate time difference between two dates in the same column in Pandas-CodePudding

I have a column (DATE) with multiple data times and I want to find the difference in minutes from date to date and store it into a new column (time_interval).

This is what I have tried:

df['time_interval'] = (df['DATE'],axis=0 - df['DATE'],axis=1) * 24 * 60

CodePudding user response：

Depending on how you'd care to store the differences, either

df = pd.DataFrame(data=['01-01-2006 00:53:00',
                        '01-01-2006 01:53:00',
                        '01-01-2006 02:53:00',
                        '01-01-2006 03:53:00',
                        '01-01-2006 04:53:00'],
                  columns=['DATE'])
df['DATE'] = pd.to_datetime(df['DATE'])
df['time_interval'] = df['DATE'].diff().fillna(timedelta(0)).apply(lambda x: x.total_seconds() / 60)

to get

                 DATE  time_interval
0 2006-01-01 00:53:00            0.0
1 2006-01-01 01:53:00           60.0
2 2006-01-01 02:53:00           60.0
3 2006-01-01 03:53:00           60.0
4 2006-01-01 04:53:00           60.0

or alternatively

df['time_interval'] = df['DATE'].diff().shift(-1).fillna(timedelta(0)).apply(lambda x: x.total_seconds() / 60)

to get

                 DATE  time_interval
0 2006-01-01 00:53:00           60.0
1 2006-01-01 01:53:00           60.0
2 2006-01-01 02:53:00           60.0
3 2006-01-01 03:53:00           60.0
4 2006-01-01 04:53:00            0.0

CodePudding user response：

pandas.Series.dt.minute

You can shift the TimeSeries by 1, and then subtract. TimeDelta is useful for TimeSeries math.

ts = pd.to_datetime(["2000-01-01 00:34:00", "2000-01-01 02:27:00", "2000-01-01 03:04:00", "2000-01-01 04:05:05"])
datetime_series = pd.DataFrame(ts, columns=['Date'])
datetime_series['ts_Delta'] = datetime_series.Date - datetime_series.shift(1).Date # shift by 1
datetime_series['ts_Delta_Minute'] = (datetime_series.Date - datetime_series.shift(1).Date).dt.total_seconds() / 60 # select DateTime and convert to total seconds, then divide by 60 for minutes
datetime_series['Time_Delta'] = pd.to_timedelta(datetime_series['ts_Delta_Minute']) # useful for TimeSeries math
datetime_series