将以分钟格式的时间列转换为以HH:MM:SS格式的时间(以 pandas 为单位) [英] Convert columns of time in minutes format to time in HH:MM:SS format in pandas

问题描述

我正在使用脚本将HH:MM:SS格式的停止时间插值为分钟的int值.脚本如下.

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<hour>\d+):(?P<minute>\d+):(?P<second>\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

我现在想要的是将这些值转换回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.

这是从我使用的较大的stop_times.csv文件派生的示例CSV. 到达时间和出发时间列是重点:

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

此处是从以分钟为单位的时间值中得出HH:MM:SS值的参考:

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!

推荐答案

根据之前的问题 最好的办法是保留原始的HH:MM:SS字符串:

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

所以不是

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

使用

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

然后，您无需进行任何新的计算即可恢复HH:MM:SS字符串. reindexed['arrival_time']仍将是原始的HH:MM:SS字符串，并且 reindexed['arrival_time_minutes']将是持续时间(以分钟为单位).

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.

基于李建勋的解决方案， 要减少微秒，您可以将分钟数乘以60，然后调用astype(int):

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')

产生

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

请注意，df['HH:MM:SS']列包含pd.Timedelta s:

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

但是，如果您尝试将此数据存储在csv中

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

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

您得到:

,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

如果分钟值太大，您还将在时间增量字符串表示形式中看到days:

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')

收益

       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')

每个您可以使用的菲利普云解决方案

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

结果看起来相同，但是现在df['HH:MM:SS']列包含 strings

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'

请注意，这会砍掉(忽略)整天和微秒. 将DataFrame写入CSV

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')

现在产生

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

这篇关于将以分钟格式的时间列转换为以HH:MM:SS格式的时间(以 pandas 为单位)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！