python - Pandas - 将时间戳四舍五入到最接近的秒

Question

我正在努力使用 pandas 来四舍五入时间戳。

时间戳是这样的:

datetime.datetime(2017,06,25,00,31,53,993000)
datetime.datetime(2017,06,25,00,32,31,224000)
datetime.datetime(2017,06,25,00,33,11,223000)
datetime.datetime(2017,06,25,00,33,53,876000)
datetime.datetime(2017,06,25,00,34,31,219000)
datetime.datetime(2017,06,25,00,35,12,634000)

如何四舍五入到最接近的秒数？

之前 iv 尝试了这篇文章中的建议，但没有奏效: Rounding time off to the nearest second - Python

到目前为止，我的代码如下所示:

import pandas as pd
filename = 'data.csv'
readcsv = pd.read_csv(filename)

根据文件头信息导入数据

log_date = readcsv.date
log_time = readcsv.time
log_lon = readcsv.lon
log_lat = readcsv.lat
log_heading = readcsv.heading

readcsv['date'] = pd.to_datetime(readcsv['date']).dt.date
readcsv['time'] = pd.to_datetime(readcsv['time']).dt.time

将日期和时间组合成一个变量

timestamp = [datetime.datetime.combine(log_date[i],log_time[i]) for i in range(len(log_date))]

创建数据框

data = {'timestamp':timestamp,'log_lon':log_lon,'log_lat':log_lat,'log_heading':log_heading}
log_data = pd.DataFrame(data,columns=['timestamp','log_lon','log_lat','log_heading'])
log_data.index = log_data['timestamp']

我对python还是很陌生所以请原谅我的无知

Answer 1

可以先用read_csv使用参数 parse_dates 从 date 和 time 列创建 datetime，然后是 dt.round对于回合 datetime:

import pandas as pd

temp=u"""date,time,lon,lat,heading
2017-06-25,00:31:53.993000,48.1254,17.1458,a
2017-06-25,00:32:31.224000,48.1254,17.1458,a
2017-06-25,00:33:11.223000,48.1254,17.1458,a
2017-06-25,00:33:53.876000,48.1254,17.1458,a
2017-06-25,00:34:31.219000,48.1254,17.1458,a
2017-06-25,00:35:12.634000,48.1254,17.1458,a"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), parse_dates={'timestamp':['date','time']})

print (df)
                timestamp      lon      lat heading
0 2017-06-25 00:31:53.993  48.1254  17.1458       a
1 2017-06-25 00:32:31.224  48.1254  17.1458       a
2 2017-06-25 00:33:11.223  48.1254  17.1458       a
3 2017-06-25 00:33:53.876  48.1254  17.1458       a
4 2017-06-25 00:34:31.219  48.1254  17.1458       a
5 2017-06-25 00:35:12.634  48.1254  17.1458       a

print (df.dtypes)
timestamp    datetime64[ns]
lon                 float64
lat                 float64
heading              object
dtype: object

---

df['timestamp'] = df['timestamp'].dt.round('1s')

print (df)
            timestamp      lon      lat heading
0 2017-06-25 00:31:54  48.1254  17.1458       a
1 2017-06-25 00:32:31  48.1254  17.1458       a
2 2017-06-25 00:33:11  48.1254  17.1458       a
3 2017-06-25 00:33:54  48.1254  17.1458       a
4 2017-06-25 00:34:31  48.1254  17.1458       a
5 2017-06-25 00:35:13  48.1254  17.1458       a

编辑:

如果您还想将带有日期时间的列设置为 index:

import pandas as pd

temp=u"""date,time,lon,lat,heading
2017-06-25,00:31:53.993000,48.1254,17.1458,a
2017-06-25,00:32:31.224000,48.1254,17.1458,a
2017-06-25,00:33:11.223000,48.1254,17.1458,a
2017-06-25,00:33:53.876000,48.1254,17.1458,a
2017-06-25,00:34:31.219000,48.1254,17.1458,a
2017-06-25,00:35:12.634000,48.1254,17.1458,a"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), parse_dates={'timestamp':['date','time']}, index_col=['timestamp'])
print (df)
                             lon      lat heading
timestamp                                        
2017-06-25 00:31:53.993  48.1254  17.1458       a
2017-06-25 00:32:31.224  48.1254  17.1458       a
2017-06-25 00:33:11.223  48.1254  17.1458       a
2017-06-25 00:33:53.876  48.1254  17.1458       a
2017-06-25 00:34:31.219  48.1254  17.1458       a
2017-06-25 00:35:12.634  48.1254  17.1458       a

---

print (df.index)
DatetimeIndex(['2017-06-25 00:31:53.993000', '2017-06-25 00:32:31.224000',
               '2017-06-25 00:33:11.223000', '2017-06-25 00:33:53.876000',
               '2017-06-25 00:34:31.219000', '2017-06-25 00:35:12.634000'],
              dtype='datetime64[ns]', name='timestamp', freq=None)


df.index = df.index.round('1s')
print (df)
                         lon      lat heading
timestamp                                    
2017-06-25 00:31:54  48.1254  17.1458       a
2017-06-25 00:32:31  48.1254  17.1458       a
2017-06-25 00:33:11  48.1254  17.1458       a
2017-06-25 00:33:54  48.1254  17.1458       a
2017-06-25 00:34:31  48.1254  17.1458       a
2017-06-25 00:35:13  48.1254  17.1458       a