任务1.2

importpandasaspd# 导入pandas库df=pd.read_excel(r'C:\Users\Administrator\Downloads\车辆行驶里程表-2 (1).xlsx')# 读取excel文件df

59931 rows × 8 columns

df_car100=df.query('车辆ID==100').reset_index(drop=True)# 筛选出车辆ID为100的行，重置索引df_car100# 查看筛选结果

time=pd.Timestamp(2020,1,1)# 创建一个时间戳对象，表示2020年1月1日print(time)# 打印时间戳对象df_car100_before2020=df_car100.query('停止时间<@time').reset_index(drop=True)# 筛选出车辆ID为100的行，停止时间早于2020年1月1日，重置索引df_car100_before2020# 查看筛选结果

2020-01-01 00:00:00

df_car100_before2020=df_car100.loc[df_car100['停止时间']>time,:].reset_index(drop=True)# 筛选出车辆ID为100的行，停止时间晚于2020年1月1日，重置索引df_car100_before2020# 查看筛选结果

df_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['停止时剩余电量']

df_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_before2020

C:\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)

soc_div_odo_before2020=df_car100_before2020['电量消耗'].sum()/df_car100_before2020['行驶里程'].sum()# 计算车辆ID为100的行，电量消耗的总和除以行驶里程的总和，得到电量消耗与行驶里程的比例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()

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.4911242603550296

df_car100_after2020=df_car100[df_car100['启动时间']>='2020-01-01']# 筛选出车辆ID为100的行，启动时间晚于2020年1月1日print(f"2020年后数据量：{len(df_car100_after2020)}条")# 查看筛选结果的行数df_car100_after2020.head()# 查看筛选结果的前五行

2020年后数据量：10 条

get_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