Untitled2

importpandasaspddfpd.read_excel(D:\cxdownload\python大一下\车辆行驶里程表-2.xlsx)df车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度01.02020-02-20 11:31:272020-02-20 11:59:4553451185.21931.8011.02020-02-20 07:52:512020-02-20 08:19:256253882.79731.6221.02020-02-13 12:44:462020-02-13 13:13:3087791376.45335.5031.02020-02-13 07:30:162020-02-13 07:56:5894871167.23429.2141.02020-02-10 17:21:112020-02-10 17:57:2349411382.98424.86...........................5992638350.02020-02-20 08:02:362020-02-20 08:21:341008823102.81363.275992750477.02020-02-20 17:52:272020-02-20 18:24:29968120101.98463.685992812070.02020-02-20 10:52:482020-02-20 11:04:369990987.35961.025992923950.02020-02-20 14:48:172020-02-20 15:53:09946015102.35960.125993067818.02020-02-20 10:44:502020-02-20 12:43:02961519102.53175.6359931 rows × 8 columnsdf_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.40timepd.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.57df_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.4044df_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.0soc_div_odo_before2020df_car100_before2020[电量消耗].sum()/df_car100_before2020[行驶里程].sum()soc_div_odo_before20200.4911242603550296defget_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()get_soc_div_odo(df_car100_before2020)0.4911242603550296get_soc_div_odo(df_car100_after2020)1.0493827160493827