Untitled

importpandasaspddfpd.read_excel(./车辆行驶里程表-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[启动时间]pd.to_datetime(df[启动时间])df[停止时间]pd.to_datetime(df[停止时间])df_car100_before2020df[(df[车辆ID]100)(df[启动时间].dt.year2020)]df_car100_after2020df[(df[车辆ID]100)(df[启动时间].dt.year2020)]print(df_car100_before2020)print(-*100)print(df_car100_after2020)车辆ID 启动时间 停止时间 启动时剩余电量 停止时剩余电量 \ 3135 100.0 2019-12-20 16:46:34 2019-12-20 17:14:10 20 16 3136 100.0 2019-12-20 15:02:59 2019-12-20 15:40:17 32 20 3137 100.0 2019-12-06 15:21:08 2019-12-06 15:47:10 46 42 3138 100.0 2019-12-06 14:43:13 2019-12-06 15:01:43 53 46 3139 100.0 2019-11-13 12:37:45 2019-11-13 13:00:53 62 56 3140 100.0 2019-11-07 13:37:07 2019-11-07 14:11:33 93 87 34332 100.0 2019-09-17 12:51:05 2019-09-17 14:12:49 97 53 启动时电池温度 峰值速度 平均速度 3135 16 44.04700 28.26 3136 12 72.64100 32.17 3137 11 51.87500 29.96 3138 9 66.57800 38.92 3139 15 62.01563 28.53 3140 18 49.07813 26.14 34332 23 80.92188 62.40 ---------------------------------------------------------------------------------------------------- 车辆ID 启动时间 停止时间 启动时剩余电量 停止时剩余电量 \ 3125 100.0 2020-02-20 16:04:11 2020-02-20 16:41:27 58 37 3126 100.0 2020-02-19 15:56:41 2020-02-19 16:31:11 73 59 3127 100.0 2020-02-19 11:48:33 2020-02-19 12:32:51 86 67 3128 100.0 2020-02-19 10:46:44 2020-02-19 11:28:10 92 76 3129 100.0 2020-02-17 16:20:48 2020-02-17 17:14:24 33 11 3130 100.0 2020-02-16 16:43:55 2020-02-16 17:04:51 54 37 3131 100.0 2020-02-16 13:32:33 2020-02-16 13:58:07 64 44 3132 100.0 2020-02-10 17:49:09 2020-02-10 18:11:19 78 62 3133 100.0 2020-01-09 19:13:23 2020-01-09 19:40:59 49 38 3134 100.0 2020-01-05 19:47:54 2020-01-05 20:29:54 68 54 启动时电池温度 峰值速度 平均速度 3125 12 88.938 30.59 3126 15 70.313 20.87 3127 11 68.078 23.02 3128 10 58.766 26.07 3129 14 78.609 27.99 3130 10 76.000 37.26 3131 6 65.750 28.16 3132 17 73.469 35.19 3133 7 70.969 28.26 3134 7 65.844 28.57df_car100_before2020[电量消耗]df_car100_before2020[启动时剩余电量]-df_car100_before2020[停止时剩余电量]df_car100_before2020车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度电量消耗3135100.02019-12-20 16:46:342019-12-20 17:14:1020161644.0470028.2643136100.02019-12-20 15:02:592019-12-20 15:40:1732201272.6410032.17123137100.02019-12-06 15:21:082019-12-06 15:47:1046421151.8750029.9643138100.02019-12-06 14:43:132019-12-06 15:01:435346966.5780038.9273139100.02019-11-13 12:37:452019-11-13 13:00:5362561562.0156328.5363140100.02019-11-07 13:37:072019-11-07 14:11:3393871849.0781326.14634332100.02019-09-17 12:51:052019-09-17 14:12:4997532380.9218862.4044df_car100_before2020[行驶时长]df_car100_before2020[停止时间]-df_car100_before2020[启动时间]df_car100_before2020[行驶时长s]df_car100_before2020[行驶时长].dt.secondsdf_car100_before2020[行驶里程]round((df_car100_before2020[行驶时长s]/3600)*df_car100_before2020[平均速度])df_car100_before2020车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度电量消耗行驶时长行驶时长s行驶里程3135100.02019-12-20 16:46:342019-12-20 17:14:1020161644.0470028.2640 days 00:27:36165613.03136100.02019-12-20 15:02:592019-12-20 15:40:1732201272.6410032.17120 days 00:37:18223820.03137100.02019-12-06 15:21:082019-12-06 15:47:1046421151.8750029.9640 days 00:26:02156213.03138100.02019-12-06 14:43:132019-12-06 15:01:435346966.5780038.9270 days 00:18:30111012.03139100.02019-11-13 12:37:452019-11-13 13:00:5362561562.0156328.5360 days 00:23:08138811.03140100.02019-11-07 13:37:072019-11-07 14:11:3393871849.0781326.1460 days 00:34:26206615.034332100.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_before2020np.float64(0.4911242603550296)defget_soc_div_odo(df): df: 计算表格 df[电量消耗]df[启动时剩余电量]-df[停止时剩余电量]df[行驶时长]df[停止时间]-df[启动时间]df[行驶时长s]df[行驶时长].dt.seconds df[行驶里程]round((df[行驶时长s]/3600)*df[平均速度])returndf[电量消耗].sum()/df[行驶里程].sum()get_soc_div_odo(df_car100_before2020)np.float64(0.4911242603550296)get_soc_div_odo(df_car100_after2020)np.float64(1.0493827160493827)