CODEWITHSUNDEEP
pythonpandasdatetime
rcwilkin1993
rcwilkin1993

Reputation: 87

How to subtract only the time (excluding date) in 2 Pandas columns?

Having a terrible time finding information on this. I am tracking several completion times every single day to measure them against goal completion time.

I am reading the completion date and time into a pandas dataframe and using df.map to map a dictionary of completion times to create a "goal time" column in a dataframe.

Sample Data:

Date                  Process
1/2/2020 10:20:00 AM  Test 1
1/2/2020 10:25:00 AM  Test 2
1/3/2020 10:15:00 AM  Test 1
1/3/2020 10:00:00 AM  Test 2

Using df.map() to create a column with the goal time:

goalmap={
    'Test 1':dt.datetime.strptime('10:15', '%H:%M'),
    'Test 2':dt.datetime.strptime('10:30', '%H:%M')}
df['Goal Time']=df['Process'].map(goalmap)

I am then trying to create a new column of "Delta" that calculates the time difference between the two in minutes. Most of the issues I am running into relate to the data types. I got it to calculate an time difference by converting column one (Date) using pd.to_datetime but because my 'Goal Time' column does not store a date, it calculates a delta that is massive (back to 1900). I've also tried parsing the time out of the Date Time column to no avail.

Any best way to calculate the difference between time stamps only?

Upvotes: 2

Views: 117

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150805

I recommend timedelta over datetime:

goalmap={
    'Test 1': pd.to_timedelta('10:15:00'),
    'Test 2': pd.to_timedelta('10:30:00') }
df['Goal Time']=df['Process'].map(goalmap)

df['Goal_Timestamp'] = df['Date'].dt.normalize() + df['Goal Time']

df['Meet_Goal'] = df['Date'] <= df['Goal_Timestamp']

Output:

                 Date Process Goal Time      Goal_Timestamp  Meet_Goal
0 2020-01-02 10:20:00  Test 1  10:15:00 2020-01-02 10:15:00      False
1 2020-01-02 10:25:00  Test 2  10:30:00 2020-01-02 10:30:00       True
2 2020-01-03 10:15:00  Test 1  10:15:00 2020-01-03 10:15:00       True
3 2020-01-03 10:00:00  Test 2  10:30:00 2020-01-03 10:30:00       True

Upvotes: 2

Related Questions