importpandasaspd
df=pd.read_excel(r'D:\lhy\data\车辆行驶里程表-1.xlsx',engine='openpyxl')# 查看数据print(df.head())
车辆ID 启动时间 停止时间 启动时剩余电量 停止时剩余电量 启动时电池温度 \ 0 1.0 2020-02-20 11:31:27 2020-02-20 11:59:45 53 45 11 1 1.0 2020-02-20 07:52:51 2020-02-20 08:19:25 62 53 8 2 1.0 2020-02-13 12:44:46 2020-02-13 13:13:30 87 79 13 3 1.0 2020-02-13 07:30:16 2020-02-13 07:56:58 94 87 11 4 1.0 2020-02-10 17:21:11 2020-02-10 17:57:23 49 41 13 峰值速度 平均速度 0 85.219 31.80 1 82.797 31.62 2 76.453 35.50 3 67.234 29.21 4 82.984 24.86
df_car100=df.query("车辆ID==100").reset_index(drop=True)df_car100
| 车辆ID | 启动时间 | 停止时间 | 启动时剩余电量 | 停止时剩余电量 | 启动时电池温度 | 峰值速度 | 平均速度 |
|---|
| 0 | 100.0 | 2020-02-20 16:04:11 | 2020-02-20 16:41:27 | 58 | 37 | 12 | 88.93800 | 30.59 |
|---|
| 1 | 100.0 | 2020-02-19 15:56:41 | 2020-02-19 16:31:11 | 73 | 59 | 15 | 70.31300 | 20.87 |
|---|
| 2 | 100.0 | 2020-02-19 11:48:33 | 2020-02-19 12:32:51 | 86 | 67 | 11 | 68.07800 | 23.02 |
|---|
| 3 | 100.0 | 2020-02-19 10:46:44 | 2020-02-19 11:28:10 | 92 | 76 | 10 | 58.76600 | 26.07 |
|---|
| 4 | 100.0 | 2020-02-17 16:20:48 | 2020-02-17 17:14:24 | 33 | 11 | 14 | 78.60900 | 27.99 |
|---|
| 5 | 100.0 | 2020-02-16 16:43:55 | 2020-02-16 17:04:51 | 54 | 37 | 10 | 76.00000 | 37.26 |
|---|
| 6 | 100.0 | 2020-02-16 13:32:33 | 2020-02-16 13:58:07 | 64 | 44 | 6 | 65.75000 | 28.16 |
|---|
| 7 | 100.0 | 2020-02-10 17:49:09 | 2020-02-10 18:11:19 | 78 | 62 | 17 | 73.46900 | 35.19 |
|---|
| 8 | 100.0 | 2020-01-09 19:13:23 | 2020-01-09 19:40:59 | 49 | 38 | 7 | 70.96900 | 28.26 |
|---|
| 9 | 100.0 | 2020-01-05 19:47:54 | 2020-01-05 20:29:54 | 68 | 54 | 7 | 65.84400 | 28.57 |
|---|
| 10 | 100.0 | 2019-12-20 16:46:34 | 2019-12-20 17:14:10 | 20 | 16 | 16 | 44.04700 | 28.26 |
|---|
| 11 | 100.0 | 2019-12-20 15:02:59 | 2019-12-20 15:40:17 | 32 | 20 | 12 | 72.64100 | 32.17 |
|---|
| 12 | 100.0 | 2019-12-06 15:21:08 | 2019-12-06 15:47:10 | 46 | 42 | 11 | 51.87500 | 29.96 |
|---|
| 13 | 100.0 | 2019-12-06 14:43:13 | 2019-12-06 15:01:43 | 53 | 46 | 9 | 66.57800 | 38.92 |
|---|
| 14 | 100.0 | 2019-11-13 12:37:45 | 2019-11-13 13:00:53 | 62 | 56 | 15 | 62.01563 | 28.53 |
|---|
| 15 | 100.0 | 2019-11-07 13:37:07 | 2019-11-07 14:11:33 | 93 | 87 | 18 | 49.07813 | 26.14 |
|---|
| 16 | 100.0 | 2019-09-17 12:51:05 | 2019-09-17 14:12:49 | 97 | 53 | 23 | 80.92188 | 62.40 |
|---|
time=pd.Timestamp(2020,1,1)print(time)df_car100_before2020=df_car100.query('停止时间 < @time').reset_index(drop=True)df_car100_before2020
2020-01-01 00:00:00
| 车辆ID | 启动时间 | 停止时间 | 启动时剩余电量 | 停止时剩余电量 | 启动时电池温度 | 峰值速度 | 平均速度 |
|---|
| 0 | 100.0 | 2019-12-20 16:46:34 | 2019-12-20 17:14:10 | 20 | 16 | 16 | 44.04700 | 28.26 |
|---|
| 1 | 100.0 | 2019-12-20 15:02:59 | 2019-12-20 15:40:17 | 32 | 20 | 12 | 72.64100 | 32.17 |
|---|
| 2 | 100.0 | 2019-12-06 15:21:08 | 2019-12-06 15:47:10 | 46 | 42 | 11 | 51.87500 | 29.96 |
|---|
| 3 | 100.0 | 2019-12-06 14:43:13 | 2019-12-06 15:01:43 | 53 | 46 | 9 | 66.57800 | 38.92 |
|---|
| 4 | 100.0 | 2019-11-13 12:37:45 | 2019-11-13 13:00:53 | 62 | 56 | 15 | 62.01563 | 28.53 |
|---|
| 5 | 100.0 | 2019-11-07 13:37:07 | 2019-11-07 14:11:33 | 93 | 87 | 18 | 49.07813 | 26.14 |
|---|
| 6 | 100.0 | 2019-09-17 12:51:05 | 2019-09-17 14:12:49 | 97 | 53 | 23 | 80.92188 | 62.40 |
|---|
df_car100_after2020=df_car100.loc[df_car100['停止时间']>time,:].reset_index(drop=True)df_car100_after2020
| 车辆ID | 启动时间 | 停止时间 | 启动时剩余电量 | 停止时剩余电量 | 启动时电池温度 | 峰值速度 | 平均速度 |
|---|
| 0 | 100.0 | 2020-02-20 16:04:11 | 2020-02-20 16:41:27 | 58 | 37 | 12 | 88.938 | 30.59 |
|---|
| 1 | 100.0 | 2020-02-19 15:56:41 | 2020-02-19 16:31:11 | 73 | 59 | 15 | 70.313 | 20.87 |
|---|
| 2 | 100.0 | 2020-02-19 11:48:33 | 2020-02-19 12:32:51 | 86 | 67 | 11 | 68.078 | 23.02 |
|---|
| 3 | 100.0 | 2020-02-19 10:46:44 | 2020-02-19 11:28:10 | 92 | 76 | 10 | 58.766 | 26.07 |
|---|
| 4 | 100.0 | 2020-02-17 16:20:48 | 2020-02-17 17:14:24 | 33 | 11 | 14 | 78.609 | 27.99 |
|---|
| 5 | 100.0 | 2020-02-16 16:43:55 | 2020-02-16 17:04:51 | 54 | 37 | 10 | 76.000 | 37.26 |
|---|
| 6 | 100.0 | 2020-02-16 13:32:33 | 2020-02-16 13:58:07 | 64 | 44 | 6 | 65.750 | 28.16 |
|---|
| 7 | 100.0 | 2020-02-10 17:49:09 | 2020-02-10 18:11:19 | 78 | 62 | 17 | 73.469 | 35.19 |
|---|
| 8 | 100.0 | 2020-01-09 19:13:23 | 2020-01-09 19:40:59 | 49 | 38 | 7 | 70.969 | 28.26 |
|---|
| 9 | 100.0 | 2020-01-05 19:47:54 | 2020-01-05 20:29:54 | 68 | 54 | 7 | 65.844 | 28.57 |
|---|
# 计算电量消耗并新增列df_car100_before2020['电量消耗']=df_car100_before2020['启动时剩余电量']-df_car100_before2020['停止时剩余电量']# 查看计算结果df_car100_before2020
| 车辆ID | 启动时间 | 停止时间 | 启动时剩余电量 | 停止时剩余电量 | 启动时电池温度 | 峰值速度 | 平均速度 | 电量消耗 |
|---|
| 0 | 100.0 | 2019-12-20 16:46:34 | 2019-12-20 17:14:10 | 20 | 16 | 16 | 44.04700 | 28.26 | 4 |
|---|
| 1 | 100.0 | 2019-12-20 15:02:59 | 2019-12-20 15:40:17 | 32 | 20 | 12 | 72.64100 | 32.17 | 12 |
|---|
| 2 | 100.0 | 2019-12-06 15:21:08 | 2019-12-06 15:47:10 | 46 | 42 | 11 | 51.87500 | 29.96 | 4 |
|---|
| 3 | 100.0 | 2019-12-06 14:43:13 | 2019-12-06 15:01:43 | 53 | 46 | 9 | 66.57800 | 38.92 | 7 |
|---|
| 4 | 100.0 | 2019-11-13 12:37:45 | 2019-11-13 13:00:53 | 62 | 56 | 15 | 62.01563 | 28.53 | 6 |
|---|
| 5 | 100.0 | 2019-11-07 13:37:07 | 2019-11-07 14:11:33 | 93 | 87 | 18 | 49.07813 | 26.14 | 6 |
|---|
| 6 | 100.0 | 2019-09-17 12:51:05 | 2019-09-17 14:12:49 | 97 | 53 | 23 | 80.92188 | 62.40 | 44 |
|---|
# 计算电量消耗并新增列df_car100_before2020['电量消耗']=df_car100_before2020['启动时剩余电量']-df_car100_before2020['停止时剩余电量']# 查看计算结果df_car100_before2020
| 车辆ID | 启动时间 | 停止时间 | 启动时剩余电量 | 停止时剩余电量 | 启动时电池温度 | 峰值速度 | 平均速度 | 电量消耗 |
|---|
| 0 | 100.0 | 2019-12-20 16:46:34 | 2019-12-20 17:14:10 | 20 | 16 | 16 | 44.04700 | 28.26 | 4 |
|---|
| 1 | 100.0 | 2019-12-20 15:02:59 | 2019-12-20 15:40:17 | 32 | 20 | 12 | 72.64100 | 32.17 | 12 |
|---|
| 2 | 100.0 | 2019-12-06 15:21:08 | 2019-12-06 15:47:10 | 46 | 42 | 11 | 51.87500 | 29.96 | 4 |
|---|
| 3 | 100.0 | 2019-12-06 14:43:13 | 2019-12-06 15:01:43 | 53 | 46 | 9 | 66.57800 | 38.92 | 7 |
|---|
| 4 | 100.0 | 2019-11-13 12:37:45 | 2019-11-13 13:00:53 | 62 | 56 | 15 | 62.01563 | 28.53 | 6 |
|---|
| 5 | 100.0 | 2019-11-07 13:37:07 | 2019-11-07 14:11:33 | 93 | 87 | 18 | 49.07813 | 26.14 | 6 |
|---|
| 6 | 100.0 | 2019-09-17 12:51:05 | 2019-09-17 14:12:49 | 97 | 53 | 23 | 80.92188 | 62.40 | 44 |
|---|
# 1. 计算行驶时长（时间差）df_car100_before2020['行驶时长delta']=df_car100_before2020['停止时间']-df_car100_before2020['启动时间']# 2. 将时间差转换为秒数df_car100_before2020['行驶时长s']=df_car100_before2020['行驶时长delta'].dt.seconds# 3. 计算行驶里程（秒转小时 × 平均速度），并四舍五入保留整数df_car100_before2020['行驶里程']=(df_car100_before2020['行驶时长s']/60/60*df_car100_before2020['平均速度']).round(0)# 4. 查看最终结果df_car100_before2020
| 车辆ID | 启动时间 | 停止时间 | 启动时剩余电量 | 停止时剩余电量 | 启动时电池温度 | 峰值速度 | 平均速度 | 电量消耗 | 行驶时长delta | 行驶时长s | 行驶里程 |
|---|
| 0 | 100.0 | 2019-12-20 16:46:34 | 2019-12-20 17:14:10 | 20 | 16 | 16 | 44.04700 | 28.26 | 4 | 0 days 00:27:36 | 1656 | 13.0 |
|---|
| 1 | 100.0 | 2019-12-20 15:02:59 | 2019-12-20 15:40:17 | 32 | 20 | 12 | 72.64100 | 32.17 | 12 | 0 days 00:37:18 | 2238 | 20.0 |
|---|
| 2 | 100.0 | 2019-12-06 15:21:08 | 2019-12-06 15:47:10 | 46 | 42 | 11 | 51.87500 | 29.96 | 4 | 0 days 00:26:02 | 1562 | 13.0 |
|---|
| 3 | 100.0 | 2019-12-06 14:43:13 | 2019-12-06 15:01:43 | 53 | 46 | 9 | 66.57800 | 38.92 | 7 | 0 days 00:18:30 | 1110 | 12.0 |
|---|
| 4 | 100.0 | 2019-11-13 12:37:45 | 2019-11-13 13:00:53 | 62 | 56 | 15 | 62.01563 | 28.53 | 6 | 0 days 00:23:08 | 1388 | 11.0 |
|---|
| 5 | 100.0 | 2019-11-07 13:37:07 | 2019-11-07 14:11:33 | 93 | 87 | 18 | 49.07813 | 26.14 | 6 | 0 days 00:34:26 | 2066 | 15.0 |
|---|
| 6 | 100.0 | 2019-09-17 12:51:05 | 2019-09-17 14:12:49 | 97 | 53 | 23 | 80.92188 | 62.40 | 44 | 0 days 01:21:44 | 4904 | 85.0 |
|---|
# 计算总电量消耗 / 总行驶里程，结果保存到变量soc_div_odo_before2020=df_car100_before2020['电量消耗'].sum()/df_car100_before2020['行驶里程'].sum()# 打印变量查看结果soc_div_odo_before2020
0.4911242603550296
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
