格式上 期望的效果

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:
    # 筛选涨出数据并整理格式
    gain_df = group[group['差异金额'] < 0].sort_values(by='差异金额').head(3)
    gain_df['亏涨类别'] = '涨出'
    gain_df['序号'] = range(1, len(gain_df)+1)
    gain_df = gain_df[['品类名称', '亏涨类别', '序号', '商品名称', '差异金额']]
    # 筛选亏损数据并整理格式
    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[['品类名称', '亏涨类别', '序号', '商品名称', '差异金额']]
    # 合并该品类的亏损和涨出数据
    category_result_df = pd.concat([loss_df, gain_df])
    result_dfs.append(category_result_df)

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

# 将结果转换为符合需求的格式
new_result = []
categories = result_df['品类名称'].unique()
for category in categories:
    category_data = result_df[result_df['品类名称'] == category]
    gain_data = category_data[category_data['亏涨类别'] == '涨出']
    loss_data = category_data[category_data['亏涨类别'] == '亏损']
    new_row = [category]
    for i in range(1, 4):
        item = gain_data[gain_data['序号'] == i]
        new_row.append(item['商品名称'].values[0] + '\n' + str(item['差异金额'].values[0]) if len(item) > 0 else '')
    for i in range(1, 4):
        item = loss_data[loss_data['序号'] == i]
        new_row.append(item['商品名称'].values[0] + '\n' + str(item['差异金额'].values[0]) if len(item) > 0 else '')
    new_result.append(new_row)

# 创建新的DataFrame
new_df = pd.DataFrame(new_result, columns=['品类名称', '涨出最多商品名称及金额', '涨出第二多商品名称及金额', '涨出第三多商品名称及金额', '亏损第一多商品名称及金额', '亏损第二多商品名称及金额', '亏损第三多商品名称及金额'])

# 将新表保存为Excel文件
new_df.to_excel('D:\\work\\2\\配料统计表新表优化.xlsx', index=False)