车辆电耗变化情况

importpandasaspddfpd.read_excel(D:\qi\车辆行驶里程表-2.xlsx)df:1: SyntaxWarning: invalid escape sequence \q :1: SyntaxWarning: invalid escape sequence \q C:\Users\琪-\AppData\Local\Temp\ipykernel_42068\927100219.py:1: SyntaxWarning: invalid escape sequence \q dfpd.read_excel(D:\qi\车辆行驶里程表-2.xlsx)车辆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_before2020车辆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.40importpandasaspddfpd.read_excel(D:\qi\车辆行驶里程表-2.xlsx)df:1: SyntaxWarning: invalid escape sequence \q :1: SyntaxWarning: invalid escape sequence \q C:\Users\琪-\AppData\Local\Temp\ipykernel_42068\1507251596.py:1: SyntaxWarning: invalid escape sequence \q df pd.read_excel(D:\qi\车辆行驶里程表-2.xlsx)车辆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_before2020df_car100.loc[df_car100[停止时间]time,:].reset_index(dropTrue)df_car100_before2020车辆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.02020-02-20 16:04:112020-02-20 16:41:2737371288.93830.59371100.02020-02-19 15:56:412020-02-19 16:31:1159591570.31320.87592100.02020-02-19 11:48:332020-02-19 12:32:5167671168.07823.02673100.02020-02-19 10:46:442020-02-19 11:28:1076761058.76626.07764100.02020-02-17 16:20:482020-02-17 17:14:2411111478.60927.99115100.02020-02-16 16:43:552020-02-16 17:04:5137371076.00037.26376100.02020-02-16 13:32:332020-02-16 13:58:074444665.75028.16447100.02020-02-10 17:49:092020-02-10 18:11:1962621773.46935.19628100.02020-01-09 19:13:232020-01-09 19:40:593838770.96928.26389100.02020-01-05 19:47:542020-01-05 20:29:545454765.84428.5754# 计算行驶时df_car100_before2020[行驶时长delta]df_car100_before2020[停止时间]-df_car100_before2020[启动时间]df_car100_before2020[行驶时长s]df_car100_before2020[行驶时长delta].dt.seconds# 计算行驶里程并使用round函数保留整数df_car100_before2020[行驶里程]round((df_car100_before2020[行驶时长s]/3600)*df_car100_before2020[平均速度])df_car100_before2020车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度电量消耗行驶时长delta行驶时长s行驶里程0100.02020-02-20 16:04:112020-02-20 16:41:2737371288.93830.59370 days 00:37:16223619.01100.02020-02-19 15:56:412020-02-19 16:31:1159591570.31320.87590 days 00:34:30207012.02100.02020-02-19 11:48:332020-02-19 12:32:5167671168.07823.02670 days 00:44:18265817.03100.02020-02-19 10:46:442020-02-19 11:28:1076761058.76626.07760 days 00:41:26248618.04100.02020-02-17 16:20:482020-02-17 17:14:2411111478.60927.99110 days 00:53:36321625.05100.02020-02-16 16:43:552020-02-16 17:04:5137371076.00037.26370 days 00:20:56125613.06100.02020-02-16 13:32:332020-02-16 13:58:074444665.75028.16440 days 00:25:34153412.07100.02020-02-10 17:49:092020-02-10 18:11:1962621773.46935.19620 days 00:22:10133013.08100.02020-01-09 19:13:232020-01-09 19:40:593838770.96928.26380 days 00:27:36165613.09100.02020-01-05 19:47:542020-01-05 20:29:545454765.84428.57540 days 00:42:00252020.0soc_div_odo_before2020df_car100_before2020[电量消耗].sum()/df_car100_before2020[行驶里程].sum()soc_div_odo_before2020np.float64(2.993827160493827)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()get_soc_div_odo(df_car100_before2020)np.float64(0.0)get_soc_div_odo(df_car100_after2020)np.float64(1.0493827160493827)