要使用Python将Excel数据从竖向转为横向（即数据透视或长表转宽表），核心是利用`pandas`库的`pivot`或`pivot_table`函数。下面将详细介绍步骤、代码示例以及如何处理常见场景。 ### 一、核心方法：使用pandas的pivot/pivot_table `pandas`库提供了两种主要函数来实现此转换，其区别如下： | 函数 | 适用场景 | 特点 | 索引唯一性要求 | | :--- | :--- | :--- | :--- | | **`df.pivot(index, columns, values)`** | **简单的一对一转换**，即`(index, columns)`组合在原始数据中**唯一**。 | 直接重塑，速度快。 | 严格要求`(index, columns)`组合唯一，否则报错。 | | **`df.pivot_table(values, index, columns, aggfunc)`** | **复杂的一对多转换**，即`(index, columns)`组合有**重复值**，需要聚合（如求和、平均）。 | 功能强大，支持数据聚合，是Excel数据透视表的直接对应。 | 不要求唯一，通过`aggfunc`（如`'sum'`, `'mean'`）处理重复项。 | #### **场景1：简单唯一值转换 (使用 `pivot`)** 假设您有一份销售记录，每位员工每个季度的数据是唯一的。 **原始数据 (`data_vertical.xlsx`)**: | 姓名 | 季度 | 销售额 | | :--- | :--- | :--- | | 张三 | Q1 | 100 | | 张三 | Q2 | 150 | | 李四 | Q1 | 200 | | 李四 | Q2 | 180 | **目标**：将“姓名”作为行，“季度”作为列，“销售额”作为值，转换为横向表格。 ```python import pandas as pd # 1. 读取竖向数据Excel文件 [ref_3] df = pd.read_excel('data_vertical.xlsx') print("原始竖向数据:") print(df) # 2. 使用pivot进行转换 (假设(姓名, 季度)组合唯一) [ref_3] try: # index: 转为行标签的列 # columns: 转为列标签的列 # values: 作为填充值的列 df_pivot = df.pivot(index='姓名', columns='季度', values='销售额') print("\n转换后的横向数据 (pivot):") print(df_pivot) except ValueError as e: print(f"pivot失败，可能存在重复值，错误信息: {e}") print("请尝试使用pivot_table并指定聚合函数。") # 3. 保存结果到新的Excel文件 [ref_3] df_pivot.to_excel('data_horizontal_pivot.xlsx', sheet_name='结果') print("\n结果已保存至 'data_horizontal_pivot.xlsx'") ``` **输出结果**: ``` 季度 Q1 Q2 姓名 张三 100 150 李四 200 180 ``` #### **场景2：含重复值需聚合的转换 (使用 `pivot_table`)** 这是更常见的业务场景，例如同一员工同一季度有多条销售记录，需要汇总。 **原始数据 (`data_vertical_duplicates.xlsx`)**: | 姓名 | 季度 | 销售额 | | :--- | :--- | :--- | | 张三 | Q1 | 100 | | 张三 | Q1 | 50 | # 张三在Q1有第二笔记录 | 张三 | Q2 | 150 | | 李四 | Q1 | 200 | | 李四 | Q2 | 180 | **目标**：将数据转为横向，并对同一员工同一季度的销售额进行**求和**。 ```python import pandas as pd # 1. 读取数据 df = pd.read_excel('data_vertical_duplicates.xlsx') print("原始数据 (含重复项):") print(df) # 2. 使用pivot_table进行转换和聚合 [ref_6] # aggfunc: 聚合函数，默认为'numpy.mean'（平均值），常用有'sum', 'count', 'max', 'min' df_pivot_table = df.pivot_table( index='姓名', # 行索引 columns='季度', # 列索引 values='销售额', # 要聚合的值 aggfunc='sum', # 聚合方式：求和 fill_value=0, # 缺失值填充为0（如李四没有Q3数据） margins=True, # (可选) 添加总计行/列，类似Excel透视表的“总计” margins_name='总计' # (可选) 总计行/列的名称 ) print("\n转换并聚合后的横向数据 (pivot_table):") print(df_pivot_table) # 3. 重置索引，使“姓名”变回普通列，表格更规整 df_result = df_pivot_table.reset_index() df_result.columns.name = None # 移除列索引的名称（‘季度’） print("\n重置索引后的规整表格:") print(df_result) # 4. 保存结果 df_result.to_excel('data_horizontal_pivot_table.xlsx', index=False) print("\n结果已保存至 'data_horizontal_pivot_table.xlsx'") ``` **输出结果**: ``` 季度 姓名 Q1 Q2 总计 0 张三 150 150 300 1 李四 200 180 380 ``` *注：`pivot_table`功能非常强大，`aggfunc`参数也支持传入字典或列表，对不同的值列进行不同的聚合操作[ref_6]。* ### 二、处理合并单元格等复杂情况 如果原始Excel中存在合并单元格，直接读取会导致部分单元格值为`NaN`。最佳实践是**先预处理，取消合并并填充空白格**，再进行转换。 ```python import pandas as pd import openpyxl from openpyxl.utils import range_boundaries def preprocess_merged_cells(file_path, sheet_name=0): """ 预处理Excel文件：填充合并单元格的空白值。 参数: file_path: Excel文件路径。 sheet_name: 工作表名或索引。 返回: 处理后的pandas DataFrame。 """ # 使用openpyxl引擎加载工作簿以操作单元格 [ref_6] wb = openpyxl.load_workbook(file_path, data_only=True) if isinstance(sheet_name, int): ws = wb.worksheets[sheet_name] else: ws = wb[sheet_name] # 遍历所有合并单元格区域，进行填充 for merged_range in list(ws.merged_cells.ranges): min_col, min_row, max_col, max_row = range_boundaries(str(merged_range)) top_left_value = ws.cell(row=min_row, column=min_col).value # 将合并区域左上角的值填充到该区域所有单元格 for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col): for cell in row: cell.value = top_left_value # 取消合并 [ref_6] ws.unmerge_cells(str(merged_range)) # 将处理后的工作表数据转换为DataFrame data = ws.values columns = next(data) # 第一行为标题 df = pd.DataFrame(data, columns=columns) wb.close() return df # 使用示例：先预处理，再转换 df_cleaned = preprocess_merged_cells('原始数据_含合并单元格.xlsx', 'Sheet1') print("预处理后的规整数据:") print(df_cleaned) # 然后使用pivot或pivot_table进行转换 # 例如，假设‘部门’列原先有合并单元格，现在已填充 result_df = df_cleaned.pivot_table(index='部门', columns='月份', values='业绩', aggfunc='sum', fill_value=0) result_df = result_df.reset_index() result_df.to_excel('处理结果_横向.xlsx', index=False) ``` ### 三、综合应用示例与对比 假设您有一份更复杂的销售数据，包含部门、销售员、产品类别和销售额，且存在同一销售员同类产品的多笔记录。 ```python import pandas as pd # 模拟创建一份复杂的竖向数据 data = { '部门': ['销售部', '销售部', '销售部', '销售部', '技术部', '技术部', '销售部'], '销售员': ['张三', '张三', '李四', '李四', '王五', '王五', '张三'], '产品类别': ['A', 'A', 'B', 'A', 'A', 'B', 'B'], '季度': ['Q1', 'Q2', 'Q1', 'Q2', 'Q1', 'Q2', 'Q1'], '销售额': [100, 150, 200, 120, 300, 250, 180] } df_complex = pd.DataFrame(data) print("复杂原始数据:") print(df_complex) # 目标1：查看每个部门、每个销售员在不同季度的总销售额（多维透视） pivot_multi = df_complex.pivot_table( index=['部门', '销售员'], # 多级行索引 columns='季度', values='销售额', aggfunc='sum', fill_value=0 ) print("\n【多维透视】部门-销售员在各季度的销售额:") print(pivot_multi) # 目标2：查看每个部门不同产品类别的销售额总计 pivot_product = df_complex.pivot_table( index='部门', columns='产品类别', values='销售额', aggfunc='sum', fill_value=0 ) print("\n【分类汇总】各部门产品类别销售额:") print(pivot_product) # 保存多个结果到同一个Excel的不同工作表 with pd.ExcelWriter('综合透视结果.xlsx') as writer: pivot_multi.to_excel(writer, sheet_name='部门-销售员季度汇总') pivot_product.to_excel(writer, sheet_name='部门-产品类别汇总') # 也可以保存规整后的原始数据 df_complex.to_excel(writer, sheet_name='原始数据', index=False) print("\n所有结果已保存至 '综合透视结果.xlsx'") ``` ### 四、方法选择与关键要点总结 1. **`pivot` vs `pivot_table`**：这是最核心的选择。如果数据中用作行和列的组合键是唯一的，用`pivot`；如果存在重复需要汇总（求和、平均等），则必须用`pivot_table`。 2. **数据清洗先行**：如同在Excel中操作一样，规整的数据源是成功的前提。务必先处理合并单元格、空值、格式不一致等问题。`pandas`的`fillna()`, `drop_duplicates()`等函数是清洗利器。 3. **聚合函数 (`aggfunc`)**：`pivot_table`的`aggfunc`参数非常灵活。除了常用的`'sum'`、`'mean'`、`'count'`，还可以传入`'first'`（取第一个）、`'last'`（取最后一个），甚至自定义函数。 4. **处理多层索引**：`pivot_table`结果可能产生多层行索引或列索引。使用`reset_index()`可以将索引变回列，使用`df.columns = df.columns.map('_'.join)`可以展平多层列索引。 5. **性能与大数据**：对于非常大的数据集（数十万行以上），`pivot_table`可能会消耗较多内存。确保`index`和`columns`指定的列是分类明确、值数量有限的列，这有助于提升性能。 通过上述方法和代码示例，您可以灵活运用Python的`pandas`库，高效、准确地将任何结构的竖向Excel数据转换为所需的横向报表格式，无论是简单的转置还是复杂的多维度聚合分析，都能轻松应对。