logo标签列表

在Python中计算逐行的时间差

pythonpandasnumpydatetimetime
7
我希望能够根据乘客上下车的时间差计算数据框中每位乘客的旅行时间。以下是数据框：
my_df = pd.DataFrame({
    'id': ['a', 'b', 'b', 'b', 'b', 'b', 'c','d'],
    'date': ['2020/02/03', '2020/04/05', '2020/04/05', '2020/04/05','2020/04/06', '2020/04/06', '2020/12/15', '2020/06/23'],
    'arriving_time': ['14:36:06', '08:52:02', '08:53:02', '08:55:24', '18:58:03', '19:03:05', '17:04:28', '21:31:23'],
    'leaving_time': ['14:40:05', '08:52:41', '08:54:33', '08:57:14', '19:01:07', '19:04:08', '17:09:48', '21:50:12']
})
print(my_df)

output:

    id  date    arriving_time   leaving_time
0   a   2020/02/03  14:36:06    14:40:05
1   b   2020/04/05  08:52:02    08:52:41
2   b   2020/04/05  08:53:02    08:54:33
3   b   2020/04/05  08:55:24    08:57:14
4   b   2020/04/06  18:58:03    19:01:07
5   b   2020/04/06  19:03:05    19:04:08
6   c   2020/12/15  17:04:28    17:09:48
7   d   2020/06/23  21:31:23    21:50:12

然而，有两个问题（我自己无法解决）：

  • 乘客通过手机信号检测到，但信号经常不稳定，这就是为什么对于同一个人，我们可能有许多行（例如上面数据集中的乘客 b）。"arriving_time" 是检测到信号的时间，"leaving_time" 是信号丢失的时间。
  • 要计算旅行时间，我需要为每个唯一的 ID 和每次旅行减去最近到达时间和最近离开时间之间的时间。

这是我想要获得的结果。

id  date    arriving_time   leaving_time    travelTime
0   a   2020/02/03  14:36:06    14:40:05    00:03:59
1   b   2020/04/05  08:52:02    08:52:41    00:05:12
2   b   2020/04/05  08:53:02    08:54:33    00:05:12
3   b   2020/04/05  08:55:24    08:57:14    00:05:12
4   b   2020/04/06  18:58:03    19:01:07    00:06:05
5   b   2020/04/06  19:03:05    19:04:08    00:06:05
6   c   2020/12/15  17:04:28    17:09:48    00:05:20
7   d   2020/06/23  21:31:23    21:50:12    00:18:49

如您所见，乘客B在同一天进行了两次不同的旅行，我想知道每次旅行持续的时间。我已经尝试了以下代码，它似乎可以工作，但速度非常慢（我认为这是由于我的df中有大量行）。
for user_id in set(my_df.id):
    for day in set(my_df.loc[my_df.id == user_id, 'date']):
        my_df.loc[(my_df.id == user_id) & (my_df.date == day), 'travelTime'] = max(my_df.loc[(my_df.id == user_id) & (my_df.date == day), 'leaving_time'].apply(pd.to_datetime)) - min(my_df.loc[(my_df.id == user_id) & (my_df.date == day), 'arriving_time'].apply(pd.to_datetime))
3个回答
7

我认为为了得到正确的最大值和最小值，应该将列转换为日期时间格式，然后用由GroupBy.transform创建的 Series 进行减法计算:

my_df['s'] = pd.to_datetime(my_df['date'] + ' ' + my_df['arriving_time'])
my_df['e'] = pd.to_datetime(my_df['date'] + ' ' + my_df['leaving_time'])

g = my_df.groupby(['id', 'date'])
my_df['travelTime'] = g['e'].transform('max').sub(g['s'].transform('min'))
print (my_df)
  id        date arriving_time leaving_time                   s  \
0  a  2020/02/03      14:36:06     14:40:05 2020-02-03 14:36:06   
1  b  2020/04/05      08:52:02     08:52:41 2020-04-05 08:52:02   
2  b  2020/04/05      08:53:02     08:54:33 2020-04-05 08:53:02   
3  b  2020/04/05      08:55:24     08:57:14 2020-04-05 08:55:24   
4  b  2020/04/06      18:58:03     19:01:07 2020-04-06 18:58:03   
5  b  2020/04/06      19:03:05     19:04:08 2020-04-06 19:03:05   
6  c  2020/12/15      17:04:28     17:09:48 2020-12-15 17:04:28   
7  d  2020/06/23      21:31:23     21:50:12 2020-06-23 21:31:23   

                    e travelTime  
0 2020-02-03 14:40:05   00:03:59  
1 2020-04-05 08:52:41   00:05:12  
2 2020-04-05 08:54:33   00:05:12  
3 2020-04-05 08:57:14   00:05:12  
4 2020-04-06 19:01:07   00:06:05  
5 2020-04-06 19:04:08   00:06:05  
6 2020-12-15 17:09:48   00:05:20  
7 2020-06-23 21:50:12   00:18:49

为了避免新的列，可以使用DataFrame.assign和包含datetimes的Series：

s = pd.to_datetime(my_df['date'] + ' ' + my_df['arriving_time'])
e = pd.to_datetime(my_df['date'] + ' ' + my_df['leaving_time'])

g = my_df.assign(s=s, e=e).groupby(['id', 'date'])
my_df['travelTime'] = g['e'].transform('max').sub(g['s'].transform('min'))
print (my_df)
  id        date arriving_time leaving_time travelTime
0  a  2020/02/03      14:36:06     14:40:05   00:03:59
1  b  2020/04/05      08:52:02     08:52:41   00:05:12
2  b  2020/04/05      08:53:02     08:54:33   00:05:12
3  b  2020/04/05      08:55:24     08:57:14   00:05:12
4  b  2020/04/06      18:58:03     19:01:07   00:06:05
5  b  2020/04/06      19:03:05     19:04:08   00:06:05
6  c  2020/12/15      17:04:28     17:09:48   00:05:20
7  d  2020/06/23      21:31:23     21:50:12   00:18:49
2

如果我理解正确，我们首先按照 id 和 date 进行分组，以获取最大和最小的离开时间和到达时间。

然后进行简单的减法运算。

df2 = df.groupby(['id','date']).agg(min_arrival=('arriving_time','min'),
                             max_leave=('leaving_time','max'))


df2['travelTime'] =  pd.to_datetime(df2['max_leave']) - pd.to_datetime(df2['min_arrival']) 


print(df2)

              min_arrival max_leave travelTime
id date                                       
a  2020-02-03    14:36:06  14:40:05   00:03:59
b  2020-04-05    08:52:02  08:57:14   00:05:12
   2020-04-06    18:58:03  19:04:08   00:06:05
c  2020-12-15    17:04:28  17:09:48   00:05:20
d  2020-06-23    21:31:23  21:50:12   00:18:49

如果你想在原始数据框中恢复此操作，可以使用 transform 或将新的增量值合并到原始数据框中：

df_new = (pd.merge(df,df2[['travelTime']],on=['date','id'],how='left')

  id       date arriving_time leaving_time   travelTime
0  a 2020-02-03      14:36:06     14:40:05     00:03:59
1  b 2020-04-05      08:52:02     08:52:41     00:05:12
2  b 2020-04-05      08:53:02     08:54:33     00:05:12
3  b 2020-04-05      08:55:24     08:57:14     00:05:12
4  b 2020-04-06      18:58:03     19:01:07     00:06:05
5  b 2020-04-06      19:03:05     19:04:08     00:06:05
6  c 2020-12-15      17:04:28     17:09:48     00:05:20
7  d 2020-06-23      21:31:23     21:50:12     00:18:49
2
你可以尝试这个 -
my_df['arriving_time'] = pd.to_datetime(my_df['arriving_time'])
my_df['leaving_time'] = pd.to_datetime(my_df['leaving_time'])
my_df['travel_time'] = my_df.groupby(['id', 'date'])['leaving_time'].transform('max') - my_df.groupby(['id', 'date'])['arriving_time'].transform('min')
my_df
    id        date       arriving_time        leaving_time travel_time
0  a  2020/02/03 2020-03-19 14:36:06 2020-03-19 14:40:05    00:03:59
1  b  2020/04/05 2020-03-19 08:52:02 2020-03-19 08:52:41    00:05:12
2  b  2020/04/05 2020-03-19 08:53:02 2020-03-19 08:54:33    00:05:12
3  b  2020/04/05 2020-03-19 08:55:24 2020-03-19 08:57:14    00:05:12
4  b  2020/04/06 2020-03-19 18:58:03 2020-03-19 19:01:07    00:06:05
5  b  2020/04/06 2020-03-19 19:03:05 2020-03-19 19:04:08    00:06:05
6  c  2020/12/15 2020-03-19 17:04:28 2020-03-19 17:09:48    00:05:20
7  d  2020/06/23 2020-03-19 21:31:23 2020-03-19 21:50:12    00:18:49
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接