我面临的这个问题很简单,但很奇怪,一直困扰着我。
我有一个数据框如下:
df['datetime'] = df['datetime'].dt.tz_convert('US/Pacific')
df.head()
vehicle_id trip_id datetime
6760612 1000500 4f874888ce404720a203e36f1cf5b716 2017-01-01 10:00:00-08:00
6760613 1000500 4f874888ce404720a203e36f1cf5b716 2017-01-01 10:00:01-08:00
6760614 1000500 4f874888ce404720a203e36f1cf5b716 2017-01-01 10:00:02-08:00
6760615 1000500 4f874888ce404720a203e36f1cf5b716 2017-01-01 10:00:03-08:00
6760616 1000500 4f874888ce404720a203e36f1cf5b716 2017-01-01 10:00:04-08:00
我试图找出数据时间差异如下(以两种不同的方式):
df['datetime_diff'] = df['datetime'].diff()
df['time_diff'] = (df['datetime'] - df['datetime'].shift(1)).astype('timedelta64[s]')
对于特定的trip_id,我的结果如下:
df[trip_frame['trip_id'] == '4f874888ce404720a203e36f1cf5b716'][['datetime','datetime_diff','time_diff']].head()
datetime datetime_diff time_diff
6760612 2017-01-01 10:00:00-08:00 NaT NaN
6760613 2017-01-01 10:00:01-08:00 00:00:01 1.0
6760614 2017-01-01 10:00:02-08:00 00:00:01 1.0
6760615 2017-01-01 10:00:03-08:00 00:00:01 1.0
6760616 2017-01-01 10:00:04-08:00 00:00:01 1.0
但是对于像下面这样的其他一些trip_id,您可以观察到我的日期时间差为零(对于两列),而实际上不是。时间差以秒为单位。
df[trip_frame['trip_id'] == '01b8a24510cd4e4684d67b96369286e0'][['datetime','datetime_diff','time_diff']].head(4)
datetime datetime_diff time_diff
3236107 2017-01-28 03:00:00-08:00 0 days 0.0
3236108 2017-01-28 03:00:01-08:00 0 days 0.0
3236109 2017-01-28 03:00:02-08:00 0 days 0.0
3236110 2017-01-28 03:00:03-08:00 0 days 0.0
df[df['trip_id'] == '01c2a70c25e5428bb33811ca5eb19270'][['datetime','datetime_diff','time_diff']].head(4)
datetime datetime_diff time_diff
8915474 2017-01-21 10:00:00-08:00 0 days 0.0
8915475 2017-01-21 10:00:01-08:00 0 days 0.0
8915476 2017-01-21 10:00:02-08:00 0 days 0.0
8915477 2017-01-21 10:00:03-08:00 0 days 0.0
关于实际问题的任何线索?我将不胜感激。
更新 - 我尝试了@n1k31t4 的方法,并为那些有问题的行得到了以下结果。甚至没有做时区转换。它仍然是那么奇怪和令人惊讶。
datetime timestamps timestamp_diffs date_diffs
3236107 2017-01-28 11:00:00+00:00 1485601200000000 0.0 0 days
3236108 2017-01-28 11:00:01+00:00 1485601201000000 0.0 0 days
3236109 2017-01-28 11:00:02+00:00 1485601202000000 0.0 0 days
3236110 2017-01-28 11:00:03+00:00 1485601203000000 0.0 0 days
3236111 2017-01-28 11:00:04+00:00 1485601204000000 0.0 0 days
