分析车辆电耗变化情况

导入数据importpandasaspd dfpd.read_excel(rD:\jwq\4.9 作业\车辆行驶里程表-2.xlsx)df_car100df.query(车辆ID100).reset_index(dropTrue)df_car100车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度0100.02020-02-20 16:04:112020-02-20 16:41:2758371288.9380030.591100.02020-02-19 15:56:412020-02-19 16:31:1173591570.3130020.872100.02020-02-19 11:48:332020-02-19 12:32:5186671168.0780023.023100.02020-02-19 10:46:442020-02-19 11:28:1092761058.7660026.074100.02020-02-17 16:20:482020-02-17 17:14:2433111478.6090027.995100.02020-02-16 16:43:552020-02-16 17:04:5154371076.0000037.266100.02020-02-16 13:32:332020-02-16 13:58:076444665.7500028.167100.02020-02-10 17:49:092020-02-10 18:11:1978621773.4690035.198100.02020-01-09 19:13:232020-01-09 19:40:594938770.9690028.269100.02020-01-05 19:47:542020-01-05 20:29:546854765.8440028.5710100.02019-12-20 16:46:342019-12-20 17:14:1020161644.0470028.2611100.02019-12-20 15:02:592019-12-20 15:40:1732201272.6410032.1712100.02019-12-06 15:21:082019-12-06 15:47:1046421151.8750029.9613100.02019-12-06 14:43:132019-12-06 15:01:435346966.5780038.9214100.02019-11-13 12:37:452019-11-13 13:00:5362561562.0156328.5315100.02019-11-07 13:37:072019-11-07 14:11:3393871849.0781326.1416100.02019-09-17 12:51:052019-09-17 14:12:4997532380.9218862.40获取 100 号车辆 2020 年前后的两个 DataFramedf_car100_before2020、df_car100_after2020timepd.Timestamp(2020,1,1)print(time)df_car100_before2020df_car100.query(停止时间time).reset_index(dropTrue)df_car100_before20202020-01-01 00:00:00车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度0100.02019-12-20 16:46:342019-12-20 17:14:1020161644.0470028.261100.02019-12-20 15:02:592019-12-20 15:40:1732201272.6410032.172100.02019-12-06 15:21:082019-12-06 15:47:1046421151.8750029.963100.02019-12-06 14:43:132019-12-06 15:01:435346966.5780038.924100.02019-11-13 12:37:452019-11-13 13:00:5362561562.0156328.535100.02019-11-07 13:37:072019-11-07 14:11:3393871849.0781326.146100.02019-09-17 12:51:052019-09-17 14:12:4997532380.9218862.40df_car100_after2020df_car100.loc[df_car100[停止时间]time,:].reset_index(dropTrue)df_car100_after2020车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度0100.02020-02-20 16:04:112020-02-20 16:41:2758371288.93830.591100.02020-02-19 15:56:412020-02-19 16:31:1173591570.31320.872100.02020-02-19 11:48:332020-02-19 12:32:5186671168.07823.023100.02020-02-19 10:46:442020-02-19 11:28:1092761058.76626.074100.02020-02-17 16:20:482020-02-17 17:14:2433111478.60927.995100.02020-02-16 16:43:552020-02-16 17:04:5154371076.00037.266100.02020-02-16 13:32:332020-02-16 13:58:076444665.75028.167100.02020-02-10 17:49:092020-02-10 18:11:1978621773.46935.198100.02020-01-09 19:13:232020-01-09 19:40:594938770.96928.269100.02020-01-05 19:47:542020-01-05 20:29:546854765.84428.57电量消耗计算1.在第 100 号车辆 2020 年前的行车数据【df_car100_before2020】中进行每次行程的电量消耗计算。2.使用【启动时剩余电量】序列减去【停止时剩余电量】序列来计算单次行程的电量消耗量。3.使用表格对象中列数据新增方法将计算结果保存到新的列【电量消耗】中。df_car100_before2020[电量消耗]df_car100_before2020[启动时剩余电量]-df_car100_before2020[停止时剩余电量]df_car100_before2020车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度电量消耗0100.02019-12-20 16:46:342019-12-20 17:14:1020161644.0470028.2641100.02019-12-20 15:02:592019-12-20 15:40:1732201272.6410032.17122100.02019-12-06 15:21:082019-12-06 15:47:1046421151.8750029.9643100.02019-12-06 14:43:132019-12-06 15:01:435346966.5780038.9274100.02019-11-13 12:37:452019-11-13 13:00:5362561562.0156328.5365100.02019-11-07 13:37:072019-11-07 14:11:3393871849.0781326.1466100.02019-09-17 12:51:052019-09-17 14:12:4997532380.9218862.4044计算车辆每次行程的行驶里程1.用停止时间 - 启动时间生成时间差列行驶时长delta2.用.dt.seconds将时间差转为秒数生成行驶时长s列3.按公式行驶里程(公里) (行驶时长s/3600) × 平均速度(公里/小时)计算里程用round取整4.将结果存入新列行驶里程。# 计算行驶时长delta时间差对象df_car100_before2020[行驶时长delta]df_car100_before2020[停止时间]-df_car100_before2020[启动时间]# 将时间差转换为秒数df_car100_before2020[行驶时长s]df_car100_before2020[行驶时长delta].dt.seconds# 计算行驶里程秒转小时 × 平均速度四舍五入取整df_car100_before2020[行驶里程](df_car100_before2020[行驶时长s]/60/60*df_car100_before2020[平均速度]).round(0)# 查看计算结果df_car100_before2020车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度电量消耗行驶时长delta行驶时长s行驶里程0100.02019-12-20 16:46:342019-12-20 17:14:1020161644.0470028.2640 days 00:27:36165613.01100.02019-12-20 15:02:592019-12-20 15:40:1732201272.6410032.17120 days 00:37:18223820.02100.02019-12-06 15:21:082019-12-06 15:47:1046421151.8750029.9640 days 00:26:02156213.03100.02019-12-06 14:43:132019-12-06 15:01:435346966.5780038.9270 days 00:18:30111012.04100.02019-11-13 12:37:452019-11-13 13:00:5362561562.0156328.5360 days 00:23:08138811.05100.02019-11-07 13:37:072019-11-07 14:11:3393871849.0781326.1460 days 00:34:26206615.06100.02019-09-17 12:51:052019-09-17 14:12:4997532380.9218862.40440 days 01:21:44490485.0计算车辆在2020年之前的单位行驶里程电量消耗按公式 单位行驶里程电量消耗 总电量消耗 ÷ 总行驶里程 计算将结果存入变量soc_div_odo_before2020并打印。# 计算单位行驶里程电量消耗soc_div_odo_before2020df_car100_before2020[电量消耗].sum()/df_car100_before2020[行驶里程].sum()# 打印结果soc_div_odo_before20200.4911242603550296定义函数来计算车辆2020年后单位行驶里程电量消耗把电量消耗、行驶时长、行驶里程、单位电耗计算封装成函数get_soc_div_odo(df)参数为车辆行驶数据表返回单位行驶里程电量消耗。用函数计算 100 号车 2020 年前数据df_car100_before2020验证结果与之前一致用同一函数计算 2020 年后数据df_car100_after2020对比 2020 年前后单位电耗差异defget_soc_div_odo(df): df: 需要计算【总电量消耗/总行驶里程】的表格 Return: 总电量消耗/总行驶里程 df[电量消耗]df[启动时剩余电量]-df[停止时剩余电量]df[行驶时长delta]df[停止时间]-df[启动时间]df[行驶时长s]df[行驶时长delta].dt.seconds df[行驶里程](df[行驶时长s]/60/60*df[平均速度]).round(0)returndf[电量消耗].sum()/df[行驶里程].sum()# 测试2020年前数据get_soc_div_odo(df_car100_before2020)# 运行结果0.4911242603550296# 计算2020年后数据get_soc_div_odo(df_car100_after2020)1.0493827160493827