我正在使用脚本将停止时间从 HH:MM:SS 格式插入分钟整数值。脚本如下。
# 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 格式，但我无法弄清楚。我有一种预感，该方法隐藏在 timeseries documentation 中的某处。 , 但我没有得到结果。
这是从我正在使用的较大的 stop_times.csv 文件派生的示例 CSV。 arrival_time 和 departure_time 列是重点:
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 值的引用:
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
非常感谢任何帮助。提前致谢!
请您参考如下方法:
Per the previous question ， 也许 最好的办法是保留原始的 HH:MM:SS 字符串:
所以代替
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'] 将是以分钟为单位的持续时间。
基于 Jianxun Li's solution , 要去掉微秒，可以将分钟乘以 60，然后调用 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.Timedeltas:
In [240]: df['HH:MM:SS'].iloc[0]
Out[240]: Timedelta('0 days 09:08:48')
但是，如果您尝试将此数据存储在 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 作为 timedelta 字符串表示的一部分:
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
这可能不是您想要的。在那种情况下，而不是
df['HH:MM:SS'] = pd.to_timedelta((60*df['minutes']).astype('int'), unit='s')
根据 Phillip Cloud's solution你可以使用
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'] 列包含字符串
In [244]: df['HH:MM:SS'].iloc[0]
Out[244]: '09:08:48'
请注意，这会切断(省略)整日和微秒。 将 DataFrame 写入 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
