使用 Python 总结 excel 工作簿

我有一个 excel 工作簿，其中有许多选项卡。每个选项卡都有合并单元格。这是我需要做的，也是我目前所掌握的： 1- 遍历工作表 2- 读取工作表数据 3- 取消合并单元格，将第一个值复制到下面未合并的空单元格中 4- 按列组合分组，并求和某些列的值 5- 输出最下面几行的值，这些值是上面几行值的总和 5- 保持相同的数据结构 6- 输出一个新工作簿，其中包含多张生成数据的工作表

下面是一个工作表的输入数据示例：

在此数据中，"总计"列中每行的值 = Jan+Feb+Mar+Apr+May+Jun 中的值

我需要的输出是这样的：

每个月份是组合 (BU+Category+Name+Act+CM+M) 中月份值的总和。 例如，SP+SS 的第一个组合为 1 月 = 5 + -

BV 应进行计算。

BV 应单独求和，就好像它们是另一个 M。

这是我目前尝试过的：

## 取消合并列和行

wb = load_workbook(filename)
sheet = wb[sheet1]
unmerged_path = "Unmerged\unmerged_br1.xlsx"；

## 取消合并单元格并赋予顶层第一个值
merge_list = []
for merge in sheet.merged_cells.ranges：
    merge_list.append(merge)

for group in merge_list：
    print("Changing merged cells " + str(group.coord))
    min_col, min_row, max_col, max_row = range_boundaries(group.coord)
    top_left_cell_value = sheet.cell(row=min_row, column=min_col).value
    sheet.unmerge_cells(str(group))
    for row in sheet.iter_rows(min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row)：
        for cell in row：
            cell.value = top_left_cell_value

if not os.path.exists('Unmerged')：
   os.makedirs('Unmerged')

wb.save(unmerged_path)

这将为我在中间工作簿中保存未合并文件，我将再次读取该工作簿以输出摘要文件，如下所示：

unmerged = pd.read_excel(unmerged_path, sheet_name=sheet)

final = unmerged.groupby(['BU','Category', 'Name', 'Act', 'CM', 'M'], as_index=False).agg({'Jan':'sum','Feb':'sum'、
                      三月':'总和', 四月':'总和', 五月':'总和', 六月':'总和'})

## 每个 BU、Category、Name、Act、CM、M 的总计列（1 月至 6 月的数值总和
final['Total'] = final[final.columns[6:]].sum(axis=1)

final.to_excel("Output\file.xlsx", sheet_name = Name1, index=False)

我使用上述代码得到的输出并没有返回 PP 和 Time 列。此外，它也没有输出我需要的底部列

PP 列是 Med1 的 SP/SS。时间列应该是 6，也就是 SP 的值。 另一个例子是，Med4 的 PP 列为 Soc，时间列变成 N/A。

了解了！正在尝试通过取消合并单元格、执行一些计算并在保留特定数据结构的同时创建汇总报告来转换 Excel 数据。我将帮助解决这个问题。

以下是基于提供的代码和解释的分步 Python 解决方案，其中考虑了“PP”和“Time”列，以及所需的其他计算：

import pandas as pd
import openpyxl

# 读取数据
unmerged_path = "Unmerged\unmerged_br1.xlsx"
unmerged = pd.read_excel(unmerged_path, sheet_name='Sheet1')  # 将“Sheet1”替换为实际的sheet名称

# ----- 步骤 1：准备数据 -----
# 1. 填充 PP 和 Time 列
# 假设“PP”列依赖于“Category”列，而“Time”列依赖于“Act”列
unmerged['PP'] = unmerged['Category'].apply(lambda x: 'SP/SS' if x == 'Med1' else ('Soc' if x == 'Med4' else ''))
unmerged['Time'] = unmerged['Act'].apply(lambda x: 6 if x == 'SP' else ('N/A' if x == 'N/A' else ''))

# 2. 将月份列转换为数字（如果尚未转换）
month_cols = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
unmerged[month_cols] = unmerged[month_cols].apply(pd.to_numeric, errors='coerce')

# ----- 步骤 2：执行计算 -----

def calculate_aggregations(df):
    """对 DataFrame 的每个组执行计算。"""
    df['Total'] = df[month_cols].sum(axis=1)
    return df

final = unmerged.groupby(['BU', 'Category', 'Name', 'PP', 'Act', 'Time', 'CM', 'M'], as_index=False).apply(calculate_aggregations)

# ----- 步骤 3：创建汇总行 -----

def create_summary_rows(df):
    """创建每个组的汇总行。"""
    # 1. 计算“总计”行
    total_row = df[month_cols + ['Total']].sum().to_frame().transpose()
    total_row['BU'] = df['BU'].iloc[0]
    total_row['Category'] = df['Category'].iloc[0]
    total_row['Name'] = df['Name'].iloc[0]
    total_row['PP'] = df['PP'].iloc[0]
    total_row['Act'] = 'Total'
    total_row['Time'] = ''
    total_row['CM'] = df['CM'].iloc[0]
    total_row['M'] = df['M'].iloc[0]

    # 2. 计算“BV 总计”行
    bv_total_row = df[df['M'] == 'BV'][month_cols + ['Total']].sum().to_frame().transpose()
    bv_total_row['BU'] = df['BU'].iloc[0]
    bv_total_row['Category'] = df['Category'].iloc[0]
    bv_total_row['Name'] = df['Name'].iloc[0]
    bv_total_row['PP'] = df['PP'].iloc[0]
    bv_total_row['Act'] = 'BV Total'
    bv_total_row['Time'] = ''
    bv_total_row['CM'] = df['CM'].iloc[0]
    bv_total_row['M'] = 'BV'

    # 将汇总行附加到 DataFrame
    return pd.concat([df, total_row, bv_total_row], ignore_index=True)

final = final.groupby(['BU', 'Category', 'Name', 'PP'], as_index=False).apply(create_summary_rows).reset_index(drop=True)

# ----- 步骤 4：输出到 Excel -----
final.to_excel("Output\file.xlsx", sheet_name='Sheet1', index=False)  # 将“Sheet1”替换为实际的sheet名称

代码说明：

1. 数据准备：

   • 填充“PP”和“Time”： 代码使用 .apply 和 lambda 函数根据提供的逻辑填充这些列。
   • 将月份转换为数字： 确保月份列包含数字以进行求和。

2. 执行计算：

   • calculate_aggregations 函数：此函数现在计算每月总计（“总计”列）。
3. 创建汇总行：
   • create_summary_rows 函数：此函数为每个组计算两行：
     • “总计”行：对所有“Act”值求和。
     • “BV 总计”行：仅对“M”列中的“BV”值求和。
4. 输出到 Excel：将结果保存到新的 Excel 文件。

请注意：

• 用输入和输出文件的实际文件名替换占位符文件名。
• 根据的数据集调整“PP”和“Time”列的逻辑。

此代码应生成一个 Excel 文件，其中包含在所需格式中描述的聚合数据，包括“PP”、“Time”、“Total”、“Total Total”和“BV Total”行。如有任何其他问题，请告诉我！