Convert columns of time in minutes format to time in HH:MM:SS format in pandas

Question

I am using a script to interpolate stop times from the format HH:MM:SS into minute int values. The script is as follows.

# read in new csv file
reindexed = pd.read_csv('output/stop_times.csv')

for col in ('arrival_time', 'departure_time'):
    # extract hh:mm:ss values
    df = reindexed[col].str.extract(
        r'(?P\d+):(?P\d+):(?P\d+)').astype('float')
    # convert to int value
    reindexed[col] = df['hour'] * 60 + df['minute']
    # interpolate
    reindexed[col] = reindexed[col].interpolate()
    reindexed[col] = np.round(reindexed[col], decimals=2)
    reindexed.to_csv('output/stop_times.csv', index=False)

# convert minutes back to HH:MM:SS

What I would now like is to convert those values back into a HH:MM:SS format, but I am having trouble figuring that out. I have a hunch that the method is hidden somewhere in the timeseries documentation, but I have come up short of a result.

Here is a sample CSV derived from the larger stop_times.csv file that I am using. The arrival_time and departure_time columns are of focus:

stop_id,stop_code,stop_name,stop_desc,stop_lat,stop_lon,location_type,parent_station,trip_id,arrival_time,departure_time,stop_sequence,pickup_type,drop_off_type,stop_headsign
02303,02303,LCC Station Bay C,lcc_c,44.00981229999999,-123.0351463,,99994.0,1475360,707.0,707.0,1,0,0,82 EUGENE STATION
01092,01092,N/S of 30th E of University,,44.0242826,-123.07484540000002,,,1475360,709.67,709.67,2,0,0,82 EUGENE STATION
01089,01089,N/S of 30th W of Alder,,44.0242545,-123.08092409999999,,,1475360,712.33,712.33,3,0,0,82 EUGENE STATION
01409,01409,"Amazon Station, Bay A",amz_a,44.026660799999995,-123.08448870000001,,99993.0,1475360,715.0,715.0,4,0,0,82 EUGENE STATION
01222,01222,E/S of Amazon Prkwy N of 24th,,44.0339371,-123.0887632,,,1475360,715.75,715.75,5,0,0,82 EUGENE STATION
01548,01548,E/S of Amazon Pkwy S of 19th,,44.038014700000005,-123.0896553,,,1475360,716.5,716.5,6,0,0,82 EUGENE STATION

Here is a reference for deriving HH:MM:SS values from a time value in minutes:

78.6 minutes can be converted to hours by dividing 78.6 minutes / 60 minutes/hour = 1.31 hours
1.31 hours can be broken down to 1 hour plus 0.31 hours - 1 hour
0.31 hours * 60 minutes/hour = 18.6 minutes - 18 minutes
0.6 minutes * 60 seconds/minute = 36 seconds - 36 seconds

Any help is much appreciated. Thanks in advance!

unutbu · Accepted Answer

Per the previous question, perhaps the best thing to do would be to keep the original HH:MM:SS strings:

So instead of

for col in ('arrival_time', 'departure_time'):
    df = reindexed[col].str.extract(
        r'(?P\d+):(?P\d+):(?P\d+)').astype('float')
    reindexed[col] = df['hour'] * 60 + df['minute']

use

for col in ('arrival_time', 'departure_time'):
    newcol = '{}_minutes'.format(col)
    df = reindexed[col].str.extract(
        r'(?P\d+):(?P\d+):(?P\d+)').astype('float')
    reindexed[newcol] = df['hour'] * 60 + df['minute']

Then you don't have to do any new calculations to recover the HH:MM:SS strings. reindexed['arrival_time'] will still be the original HH:MM:SS strings, and reindexed['arrival_time_minutes'] would be the time duration in minutes.

Building on Jianxun Li's solution, to chop off the microseconds, you could multiply the minutes by 60 and then call astype(int):

import numpy as np
import pandas as pd

np.random.seed(0)
df = pd.DataFrame(np.random.rand(3) * 1000, columns=['minutes'])
df['HH:MM:SS'] = pd.to_timedelta((60*df['minutes']).astype('int'), unit='s')

which yields

      minutes  HH:MM:SS
0  548.813504  09:08:48
1  715.189366  11:55:11
2  602.763376  10:02:45

Note that the df['HH:MM:SS'] column contains pd.Timedeltas:

In [240]: df['HH:MM:SS'].iloc[0]
Out[240]: Timedelta('0 days 09:08:48')

However, if you try to store this data in a csv

In [223]: df.to_csv('/tmp/out', date_format='%H:%M:%S')

you get:

,minutes,HH:MM:SS
0,548.813503927,0 days 09:08:48.000000000
1,715.189366372,0 days 11:55:11.000000000
2,602.763376072,0 days 10:02:45.000000000

If the minute values are too big, you would also see days as part of the timedelta string representation:

np.random.seed(0)
df = pd.DataFrame(np.random.rand(3) * 10000, columns=['minutes'])
df['HH:MM:SS'] = pd.to_timedelta((60*df['minutes']).astype('int'), unit='s')

yields

       minutes        HH:MM:SS
0  5488.135039 3 days 19:28:08
1  7151.893664 4 days 23:11:53
2  6027.633761 4 days 04:27:38

That might not be what you want. In that case, instead of

df['HH:MM:SS'] = pd.to_timedelta((60*df['minutes']).astype('int'), unit='s')

per Phillip Cloud's solution you could use

import operator
fmt = operator.methodcaller('strftime', '%H:%M:%S')
df['HH:MM:SS'] = pd.to_datetime(df['minutes'], unit='m').map(fmt)

The result looks the same, but now the df['HH:MM:SS'] column contains strings

In [244]: df['HH:MM:SS'].iloc[0]
Out[244]: '09:08:48'

Note that this chops off (omits) both the whole days and the microseconds. Writing the DataFrame to a CSV

In [229]: df.to_csv('/tmp/out', date_format='%H:%M:%S')

now yields

,minutes,HH:MM:SS
0,548.813503927,09:08:48
1,715.189366372,11:55:11
2,602.763376072,10:02:45

Convert columns of time in minutes format to time in HH:MM:SS format in pandas

Answers (2)

Related Questions