任务1.2

importpandasaspd# 导入pandas库dfpd.read_excel(rC:\Users\Administrator\Downloads\车辆行驶里程表-2 (1).xlsx)# 读取excel文件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)# 筛选出车辆ID为100的行重置索引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)# 创建一个时间戳对象表示2020年1月1日print(time)# 打印时间戳对象df_car100_before2020df_car100.query(停止时间time).reset_index(dropTrue)# 筛选出车辆ID为100的行停止时间早于2020年1月1日重置索引df_car100_before2020# 查看筛选结果2020-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)# 筛选出车辆ID为100的行停止时间晚于2020年1月1日重置索引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[停止时剩余电量]# 计算车辆ID为100的行启动时剩余电量减去停止时剩余电量得到电量消耗df_car100_before2020# 查看筛选结果C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\2176885489.py:1: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df_car100_before2020[电量消耗] df_car100_before2020[启动时剩余电量] - df_car100_before2020[停止时剩余电量]车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度电量消耗行驶时长delta行驶时长s行驶里程10100.02019-12-20 16:46:342019-12-20 17:14:1020161644.0470028.2640 days 00:27:36165613.011100.02019-12-20 15:02:592019-12-20 15:40:1732201272.6410032.17120 days 00:37:18223820.012100.02019-12-06 15:21:082019-12-06 15:47:1046421151.8750029.9640 days 00:26:02156213.013100.02019-12-06 14:43:132019-12-06 15:01:435346966.5780038.9270 days 00:18:30111012.014100.02019-11-13 12:37:452019-11-13 13:00:5362561562.0156328.5360 days 00:23:08138811.015100.02019-11-07 13:37:072019-11-07 14:11:3393871849.0781326.1460 days 00:34:26206615.016100.02019-09-17 12:51:052019-09-17 14:12:4997532380.9218862.40440 days 01:21:44490485.0df_car100_before2020[行驶时长delta]df_car100_before2020[停止时间]-df_car100_before2020[启动时间]# 计算车辆ID为100的行停止时间减去启动时间得到行驶时长df_car100_before2020[行驶时长s]df_car100_before2020[行驶时长delta].dt.seconds# 将行驶时长转换为秒df_car100_before2020[行驶里程](df_car100_before2020[行驶时长s]/60/60*df_car100_before2020[平均速度]).round(0)# 计算车辆ID为100的行行驶时长除以60再除以60得到行驶里程乘以平均速度得到行驶里程保留0位小数df_car100_before2020C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\4107842984.py:1: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df_car100_before2020[行驶时长delta] df_car100_before2020[停止时间] - df_car100_before2020[启动时间] C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\4107842984.py:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df_car100_before2020[行驶时长s] df_car100_before2020[行驶时长delta].dt.seconds C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\4107842984.py:3: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df_car100_before2020[行驶里程] (df_car100_before2020[行驶时长s]/60/60 * df_car100_before2020[平均速度]).round(0)车辆ID启动时间停止时间启动时剩余电量停止时剩余电量启动时电池温度峰值速度平均速度电量消耗行驶时长delta行驶时长s行驶里程10100.02019-12-20 16:46:342019-12-20 17:14:1020161644.0470028.2640 days 00:27:36165613.011100.02019-12-20 15:02:592019-12-20 15:40:1732201272.6410032.17120 days 00:37:18223820.012100.02019-12-06 15:21:082019-12-06 15:47:1046421151.8750029.9640 days 00:26:02156213.013100.02019-12-06 14:43:132019-12-06 15:01:435346966.5780038.9270 days 00:18:30111012.014100.02019-11-13 12:37:452019-11-13 13:00:5362561562.0156328.5360 days 00:23:08138811.015100.02019-11-07 13:37:072019-11-07 14:11:3393871849.0781326.1460 days 00:34:26206615.016100.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()# 计算车辆ID为100的行电量消耗的总和除以行驶里程的总和得到电量消耗与行驶里程的比例soc_div_odo_before2020# 查看结果0.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)# 调用函数get_soc_div_odo计算车辆ID为100的行电量消耗与行驶里程的比例C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\1796169913.py:6: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df[电量消耗] df[启动时剩余电量] - df[停止时剩余电量] C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\1796169913.py:7: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df[行驶时长delta] df[停止时间] - df[启动时间] C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\1796169913.py:8: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df[行驶时长s] df[行驶时长delta].dt.seconds C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\1796169913.py:9: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df[行驶里程] (df[行驶时长s]/60/60 * df[平均速度]).round(0) 0.4911242603550296df_car100_after2020df_car100[df_car100[启动时间]2020-01-01]# 筛选出车辆ID为100的行启动时间晚于2020年1月1日print(f2020年后数据量{len(df_car100_after2020)}条)# 查看筛选结果的行数df_car100_after2020.head()# 查看筛选结果的前五行2020年后数据量10 条车辆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.99get_soc_div_odo(df_car100_after2020)# 调用函数get_soc_div_odo计算车辆ID为100的行电量消耗与行驶里程的比例C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\1796169913.py:6: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df[电量消耗] df[启动时剩余电量] - df[停止时剩余电量] C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\1796169913.py:7: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df[行驶时长delta] df[停止时间] - df[启动时间] C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\1796169913.py:8: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df[行驶时长s] df[行驶时长delta].dt.seconds C:\Users\Administrator\AppData\Local\Temp\ipykernel_8068\1796169913.py:9: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df[行驶里程] (df[行驶时长s]/60/60 * df[平均速度]).round(0) 1.0493827160493827