How to subtract date and time in Pandas?

Question

I have data from Pandas which was the contents of a CSV file:

Date                Inbound     Outbound
17/10/2019 12:35    5.49E+02    3.95E+03 
17/10/2019 12:40    2.06E+02    3.17E+03 
17/10/2019 12:45    2.06E+02    3.17E+03 
17/10/2019 12:50    2.06E+02    3.17E+03 
17/10/2019 12:55    2.06E+02    3.17E+03 
17/10/2019 13:00    2.06E+02    3.17E+03
....

I aim to convert the column Date from timestamps to time periods in units of minutes, which should result in something like the following:

    Date        Inbound     Outbound
    0           5.49E+02    3.95E+03 
    5           2.06E+02    3.17E+03 
    10          2.06E+02    3.17E+03 
    15          2.06E+02    3.17E+03 
    20          2.06E+02    3.17E+03 
    25          2.06E+02    3.17E+03

You can follow this https://stackoverflow.com/questions/22132525/add-column-with-number-of-days-between-dates-in-dataframe-pandas — Ghost, Oct 17 '19 at 17:39

score 3 · Accepted Answer · edited Oct 18 '19 at 08:28

3

You can use subtract the first timestampe to calculate the difference, then get total_seconds() and convert to minutes:

df['Date'] = pd.to_datetime(df['Date'])

df['Date'] = (df.Date.sub(df.Date.iloc[0])
   .dt.total_seconds().div(60).astype(int)
)

Output:

   Date  Inbound  Outbound
0     0    549.0    3950.0
1     5    206.0    3170.0
2    10    206.0    3170.0
3    15    206.0    3170.0
4    20    206.0    3170.0
5    25    206.0    3170.0

edited Oct 18 '19 at 08:28

jezrael

822,522
95
1,334
1,252

answered Oct 17 '19 at 17:46

Quang Hoang

146,074
10
56
74

Something wrong with the implementation, it cannot be ran! – Tim Luka Oct 18 '19 at 07:45
1

@TimLuka - I think is necessary `df['Date'] = pd.to_datetime(df['Date'])` first – jezrael Oct 18 '19 at 08:28

RightmireM · Answer 2 · 2019-10-18T08:26:56.980

import pandas as pd

data = [
"17/10/2019 12:35",  
"17/10/2019 12:40",  
"17/10/2019 12:45", 
"17/10/2019 12:50",  
"17/10/2019 12:55",  
"17/10/2019 13:00",
    ]

columns = ["Date"]

df = pd.DataFrame(data, columns = columns)
# Because the values came in frm a CSV, they are not datetime objects. 
# Convert the column to datetime ojects
df['Date'] = pd.to_datetime(df['Date'])
subtractdate = df["Date"].iloc[0]
df["Diff"] = df["Date"].apply(lambda x: x - subtractdate)    
print(df)

EDIT:

To get the minutes, do the following...

df["Diff"] = df["Date"].apply(lambda x: (x - subtractdate).seconds/60)

(x - subtractdate) returns a timedelta object. It doesn't have a "minutes" option, but does have a seconds. This will return in a float format (I.e. 5.0, 10.0)

To get it as a string, do the following:

df["Diff"] = df["Date"].apply(lambda x: str( (x - subtractdate).seconds/60  ) )

And to get it as an int:

df["Diff"] = df["Date"].apply(lambda x: int( (x - subtractdate).seconds/60  ) )

The int will be the "5, 10, 15" format.

Thanks! actually it does show like `00:00:00 00:05:00 00:10:00 ... ` I only want to show the difference without the timing thing. `0, 5, 10, 15 ...` — Tim Luka, Oct 18 '19 at 07:36

How to subtract date and time in Pandas?

2 Answers2