Subtract last and first elements in a column using Python Pandas

Question

I have a dataframe like this:

A B C Date
2 3 6 03/19/2012 2:33:34.270103 PM
4 8 2 03/19/2012 2:33:34.270308 PM
...
6 2 0 03/19/2012 2:34:04.611729 PM

So I need to calculate the time difference between the last element and first element in the column 'Date'. I need the time difference in seconds for

(10/21/2014 2:34:04.611729 PM) - (03/19/2012 2:33:34.270103 PM) =  ANSWER to print out

Also, the date format I have is exactly as shown above. Any ideas?

Answer 1

Convert the Date series to datetime first using the to_datetime method, then it's just a simple computation from there.

In [24]: df = pd.read_csv("test.csv")

In [25]: df['Date'] = pd.to_datetime(df['Date'])

In [26]: delta = df['Date'].iat[-1] - df['Date'].iat[0]

In [27]: delta
Out[27]: numpy.timedelta64(30300000000,'ns')

In [28]: delta / np.timedelta64(1, 's')
Out[28]: 30.300000000000001

Line 28 is to "standardize" the result to seconds, as the initial result was in nanoseconds.

EDIT:

Basing it on the CSV you uploaded, the issue here is that your dates are of a format that is not readily convertible to datetime. You need to truncate the first two and the last characters before converting. Following code works and provides the correct target value.

In [1]: import numpy as np

In [2]: import pandas as pd

In [3]: df = pd.read_csv("dummy.csv")

In [4]: str_ = lambda x:x[2:-1]

In [5]: df["Timestamp"] = df["Timestamp"].map(str_)

In [6]: df["Timestamp"] = pd.to_datetime(df["Timestamp"])

In [7]: delta_ = df["Timestamp"].iat[-1] - df["Timestamp"].iat[0]

In [8]: delta_
Out[8]: numpy.timedelta64(36981327000,'ns')

In [9]: delta_ / np.timedelta64(1, "s")
Out[9]: 36.981327

Kindly let us know if this works.