python +excel 根据品类求涨出和亏损的商品的前三和后三

import pandas as pd

# 读取原始数据
df = pd.read_excel('D:\\work\\2\\配料原始表.xlsx', sheet_name='Sheet1')

# 按品类分组并处理数据
grouped = df.groupby('品类名称')
result_dfs = []
for category, group in grouped:
    # 筛选亏损数据并整理格式
    loss_df = group[group['差异金额'] > 0].sort_values(by='差异金额', ascending=False).head(3)
    loss_df['亏涨类别'] = '亏损'
    loss_df['序号'] = range(1, len(loss_df) + 1)
    loss_df = loss_df[['品类名称', '亏涨类别', '序号', '商品名称', '差异金额']]

    # 筛选涨出数据并整理格式
    gain_df = group[group['差异金额'] < 0].sort_values(by='差异金额').head(3)
    gain_df['亏涨类别'] = '涨出'
    gain_df['序号'] = range(1, len(gain_df) + 1)
    gain_df = gain_df[['品类名称', '亏涨类别', '序号', '商品名称', '差异金额']]

    # 合并该品类的亏损和涨出数据
    category_result_df = pd.concat([loss_df, gain_df])
    result_dfs.append(category_result_df)

# 合并所有品类的数据
result_df = pd.concat(result_dfs)

# 将结果写入新的Excel文件
with pd.ExcelWriter('D:\\work\\2\\配料统计表2.xlsx', engine='openpyxl') as writer:
    result_df.to_excel(writer, sheet_name='Sheet1', index=False)

运行代码后获得一张新的excel表

下面是原始表