5

I have a pandas Series called 'df' as follows

                       value    
date_time_index         
2015-10-28 01:54:00     1.0 
2015-10-28 01:55:00     1.0 
2015-10-28 01:56:00     1.0 
2015-10-28 01:57:00     1.0 
2015-10-28 01:58:00     1.0

and I just want a new column with the difference in seconds between consecutive rows, how can I do this?

Note: The type is

 type(df.index[1])

given as

 pandas.tslib.Timestamp
Runner Bean
  • 4,895
  • 12
  • 40
  • 60
  • 1
    did you try df.diff() http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.diff.html – George C Aug 10 '16 at 02:09
  • yes, ive been trying that for 1 hour now, but I get a difference like '0 days 00:00:01'. I cant seem to get this into seconds. There must be some simple way get what Im looking for. – Runner Bean Aug 10 '16 at 02:12
  • 2
    http://stackoverflow.com/q/26456825/3765319 After you subtract using `.diff`, you are left with a TimeDelta column. That answer should help you. – Kartik Aug 10 '16 at 02:19
  • @Kartik, yes just used that in a posted answer below at the exact same time you made your comment, but thank you – Runner Bean Aug 10 '16 at 02:21

2 Answers2

9

I'd do it like this:

df.index.to_series().diff().dt.total_seconds().fillna(0)

date_time_index
2015-10-28 01:54:00     0.0
2015-10-28 01:55:00    60.0
2015-10-28 01:56:00    60.0
2015-10-28 01:57:00    60.0
2015-10-28 01:58:00    60.0
Name: date_time_index, dtype: float64
piRSquared
  • 285,575
  • 57
  • 475
  • 624
3

I think Ive worked it out using 

df['temp_index'] = df.index
df['Delta'] = df['temp_index'].diff().astype('timedelta64[m]')

in minutes rather than seconds (change m to s for seconds)

Runner Bean
  • 4,895
  • 12
  • 40
  • 60