基于pandas根据起止时间计算各充电桩站点平均充电时长的实现问题

基于两个DataFrame的数据计算每个充电桩站点的平均充电时长，两张数据源分别为evc_locations.csv和evc_logs.csv，具体数据如下：

evc_locations.csv 数据 #

location_id,charger_id,electricity_procurement_cost_per_kwh,electricity_dispensing_cost_per_kwh,zip_cd,state 
loc1001,charge11001,3,4.2,60611,illinois 
loc1001,charge11002,3,4.2,60611,illinois 
oc1001,charge11003,3,4.2,60611,illinois 
loc1002,charge11004,3,3.4,60611,illinois 
loc1002,charge11005,3,3.4,60611,illinois 
loc1003,charge11006,3,4.1,60611,illinois 
loc1004,charge11007,3,4.3,60612,illinois 
loc1004,charge11008,3,4.3,60612,illinois 
loc1005,charge11009,3,3.6,60612,illinois 
loc1005,charge11010,3,3.6,60612,illinois 
loc1005,charge11011,3,3.6,60612,illinois 
loc1006,charge11012,4,4.1,60613,florida 
loc1006,charge11013,4,4.1,60613,florida 
loc1006,charge11014,4,4.1,60613,florida 
loc1006,charge11015,4,4.1,60613,florida 
loc1007,charge11016,4,4.6,60613,florida 
loc1008,charge11017,4,4.25,60614,florida 
loc1008,charge11018,4,4.25,60614,florida 
loc1009,charge11019,4,4.33,60614,florida 
loc1010,charge11020,3,3.2,60615,california 
loc1010,charge11021,4,4.33,60615,california 
loc1010,charge11022,4,4.33,60615,california 
loc1010,charge11023,4,4.33,60615,california 
loc1010,charge11024,4,4.33,60615,california 
loc1011,charge11025,4,4.55,60615,california 
loc1011,charge11026,4,4.55,60615,california 
loc1011,charge11027,4,4.55,60615,california

evc_logs.csv 数据 #

charger_id,cust_id,start_capacity,end_capacity,start_time,end_time 
charge11010,cust202196,09,433,01/01/2020 06:32 AM,01/01/2020 07:42 AM 
charge11003,cust202262,02,402,01/01/2020 06:31 AM,01/01/2020 07:41 AM 
charge11032,cust202018,33,416,01/01/2020 06:30 AM,01/01/2020 07:41 AM 
charge11027,cust202096,07,437,01/01/2020 06:32 AM,01/01/2020 07:40 AM 
charge11026,cust202043,40,444,01/01/2020 06:30 AM,01/01/2020 07:43 AM 
charge11022,cust202447,00,405,01/01/2020 06:30 AM,01/01/2020 07:43 AM 
charge11023,cust202407,34,447,01/01/2020 06:34 AM,01/01/2020 07:43 AM 
charge11006,cust202166,25,405,01/01/2020 06:32 AM,01/01/2020 07:41 AM 
charge11034,cust202381,48,431,01/01/2020 06:30 AM,01/01/2020 07:41 AM 
charge11011,cust202499,22,448,01/01/2020 06:32 AM,01/01/2020 07:42 AM 
charge11009,cust202057,03,403,01/01/2020 06:34 AM,01/01/2020 07:40 AM 
charge11018,cust202211,00,403,01/01/2020 06:34 AM,01/01/2020 07:42 AM 
charge11031,cust202419,36,418,01/01/2020 06:34 AM,01/01/2020 07:40 AM 
charge11002,cust202075,06,404,01/01/2020 06:33 AM,01/01/2020 07:43 AM 
charge11004,cust202272,17,405,01/01/2020 06:34 AM,01/01/2020 07:43 AM 
charge11008,cust202397,28,446,01/01/2020 06:34 AM,01/01/2020 07:42 AM 
charge11016,cust202071,36,421,01/01/2020 06:33 AM,01/01/2020 07:42 AM 
charge11028,cust202454,38,441,01/01/2020 06:34 AM,01/01/2020 07:41 AM 
charge11030,cust202489,00,440,01/01/2020 06:34 AM,01/01/2020 07:42 AM 
charge11015,cust202305,29,416,01/01/2020 06:34 AM,01/01/2020 07:42 AM 
charge11013,cust202044,02,437,01/01/2020 06:34 AM,01/01/2020 07:42 AM 
charge11017,cust202451,38,425,01/01/2020 06:34 AM,01/01/2020 07:43 AM 
charge11020,cust202217,15,449,01/01/2020 06:34 AM,01/01/2020 07:42 AM 
charge11014,cust202391,03,417,01/01/2020 06:33 AM,01/01/2020 07:44 AM

初始实现代码 #

import pandas as pd

locations = pd.read_csv("evc_locations.csv")
logs = pd.read_csv("evc_logs.csv")
location_logs = pd.merge(locations, logs, 
                   on='charger_id', 
                   how='inner')
location_logs[['start_time','end_time']]=location_logs[['start_time','end_time']].apply(pd.to_datetime,1)

output = location_logs.groupby('location_id').apply(lambda x : (x['start_time']-x['end_time'].shift()).dt.total_seconds().mean()/60)
print(output)

错误输出结果 #

location_id
loc1001     NaN
loc1002     NaN
loc1003     NaN
loc1004     NaN
loc1005   -69.0
loc1006   -69.5
loc1007     NaN
loc1008   -69.0
loc1010   -70.5
loc1011   -71.0
oc1001      NaN

• 计算逻辑错误：原代码统计的是同一站点下当前订单开始时间减去上一条订单结束时间，属于充电空闲间隔的计算逻辑，并非单条订单的充电时长。同时shift()偏移操作会导致每个分组第一条记录计算结果为NaN，仅存在1条充电记录的站点最终平均结果就会为NaN。
• 时间顺序错误：充电时长应为结束时间减去开始时间，原代码用开始时间减结束时间，自然会出现负数结果。

正确逻辑为先计算单条充电记录的时长，再按站点分组求平均，代码如下：

import pandas as pd

locations = pd.read_csv("evc_locations.csv")
logs = pd.read_csv("evc_logs.csv")
# 关联站点信息和充电日志
location_logs = pd.merge(locations, logs, 
                   on='charger_id', 
                   how='inner')
# 转换时间格式
location_logs[['start_time','end_time']] = location_logs[['start_time','end_time']].apply(pd.to_datetime, axis=1)
# 计算单条充电记录时长（单位：分钟）
location_logs['charge_duration'] = (location_logs['end_time'] - location_logs['start_time']).dt.total_seconds() / 60
# 按站点分组求平均时长
output = location_logs.groupby('location_id')['charge_duration'].mean()
print(output)

正确输出结果 #

location_id
loc1001    70.0
loc1002    69.0
loc1003    69.0
loc1004    68.0
loc1005    69.0
loc1006    69.5
loc1007    69.0
loc1008    69.5
loc1010    69.5
loc1011    70.0
oc1001     70.0
Name: charge_duration, dtype: float64

内容的提问来源于stack exchange，提问作者systemdebt