使用Pandas处理Excel数据：合并跨行单元格以优化表格结构

本教程旨在指导如何使用python pandas库处理非标准格式的excel数据。当数据逻辑上属于同一记录但物理上分散在两行时，我们将学习一种迭代方法，将特定列的跨行数据合并到单个单元格（列表形式）中。此过程有助于将原始的非规范化数据转换为更适合分析和表格展示的结构，提高数据可用性。

在日常数据处理工作中，我们经常会遇到从各种系统导出的Excel文件，其数据格式可能并不总是符合标准的表格结构。一个常见的情况是，一个逻辑上的数据记录被拆分到两行中，例如，某个属性的值在第一行，而其补充信息在紧邻的第二行。这种格式使得直接将数据转换为标准表格或进行进一步分析变得困难。

例如，原始数据可能呈现如下结构：

而我们期望将其转换为更规整的单行记录，其中Column B和Column D的值能够合并：

本文将详细介绍如何使用Python的Pandas库自动化这一数据重构过程。

核心思路：逐行合并策略

由于我们需要合并的是相邻两行的数据，传统的基于列或单行操作的Pandas方法（如apply）可能难以直接实现这种跨行逻辑。因此，一种有效的方法是采用迭代策略：

NatAgent

AI数据情报监测与分析平台

下载

1. 逐对读取行： 以步长为2的方式遍历DataFrame的行，每次获取一对相邻的行。
2. 构建新行： 为这对行构建一个新的字典，代表合并后的单行数据。
3. 条件合并： 对于需要合并的特定列（如示例中的Column B和Column D），将两行中的值组合成一个列表。
4. 直接复制： 对于不需要合并的列，直接取第一行的值。
5. 追加到结果DataFrame： 将构建好的新行追加到一个新的DataFrame中。

示例代码与详细解析

以下是实现上述逻辑的Python代码：

import pandas as pd

# 定义Excel文件路径和工作表名称
excel_file = 'data.xlsx'
sheet_name = 'Sheet1'

# 1. 读取Excel文件到Pandas DataFrame
try:
    df = pd.read_excel(excel_file, sheet_name=sheet_name)
except FileNotFoundError:
    print(f"错误：文件 '{excel_file}' 或工作表 '{sheet_name}' 不存在。请检查路径和名称。")
    # 创建一个示例DataFrame用于演示，如果文件不存在
    data = {
        'Data A': ['Data A1', '', 'Data A2', ''],
        'Data B': ['Data B1', 'Data B1\'', 'Data B2', 'Data B2\''],
        'Data C': ['Data C1', '', 'Data C2', ''],
        'Data D': ['Data D1', 'Data D1\'', 'Data D2', 'Data D2\''],
        'Data E': ['Data E1', '', 'Data E2', '']
    }
    df = pd.DataFrame(data)
    print("已使用示例数据继续。")

print("原始数据：")
print(df)

# 2. 初始化一个空的DataFrame用于存储格式化后的数据
# 确保新DataFrame的列与原始DataFrame一致
formatted_df = pd.DataFrame(columns=df.columns)

# 3. 遍历DataFrame，每次处理两行
# range(0, len(df), 2) 表示从索引0开始，每次跳过2个索引，即每次处理 i 和 i+1
for i in range(0, len(df), 2):
    # 获取当前行的第一行数据
    row1 = df.iloc[i]

    # 尝试获取下一行数据，如果已是最后一行，则row2为None
    row2 = df.iloc[i + 1] if i + 1 < len(df) else None

    combined_row = {} # 用于存储合并后新行的数据

    # 遍历所有列
    for col in df.columns:
        # 指定需要合并的列名列表。请根据您的实际情况修改此列表！
        # 例如，如果您的列名是 'Col B' 和 'Col D'，则改为 ['Col B', 'Col D']
        columns_to_combine = ['Data B', 'Data D'] 

        if col in columns_to_combine:
            # 如果是需要合并的列，则将row1和row2的值放入列表中
            # 注意处理row2可能为None的情况
            combined_row[col] = [row1[col], row2[col] if row2 is not None else None]
        else:
            # 对于不需要合并的列，直接取row1的值
            combined_row[col] = row1[col]

    # 将构建好的combined_row追加到formatted_df
    # ignore_index=True 确保新行有独立的索引
    formatted_df = formatted_df.append(combined_row, ignore_index=True)

print("\n格式化后的数据：")
print(formatted_df)

# 4. 将格式化后的DataFrame保存到新的Excel文件
output_excel_file = 'formatted_output.xlsx'
formatted_df.to_excel(output_excel_file, index=False)
print(f"\n格式化后的数据已保存到 '{output_excel_file}'")

代码解析：

1. 导入Pandas并读取数据：
   • import pandas as pd：导入Pandas库。
   • pd.read_excel(excel_file, sheet_name=sheet_name)：从指定的Excel文件和工作表读取数据，并将其存储在一个Pandas DataFrame df 中。代码中加入了try-except块，以便在文件不存在时提供一个示例DataFrame，方便测试。
2. 初始化结果DataFrame：
   • formatted_df = pd.DataFrame(columns=df.columns)：创建一个空的DataFrame formatted_df，它的列结构与原始DataFrame df 相同。这将用于存储合并后的数据。
3. 循环处理行：
   • for i in range(0, len(df), 2):：这是核心循环。range(start, stop, step) 函数生成一个序列。在这里，它会从0开始，以2为步长，直到DataFrame的长度。这意味着i将依次为0, 2, 4, ...，每次迭代都代表一对行的起始索引。
   • row1 = df.iloc[i]：使用iloc按位置获取当前迭代的第一行数据。
   • row2 = df.iloc[i + 1] if i + 1
   • combined_row = {}：在每次循环开始时，创建一个空字典，用于构建当前合并后的新行。
   • for col in df.columns:：遍历原始DataFrame的所有列。
   • columns_to_combine = ['Data B', 'Data D']：这是您需要根据实际数据进行修改的关键部分！ 将需要合并的列名添加到此列表中。
   • if col in columns_to_combine:：如果当前列在需要合并的列表中，则执行合并逻辑。
     • combined_row[col] = [row1[col], row2[col] if row2 is not None else None]：将row1和row2中该列的值放入一个列表中。如果row2为None，则第二部分的值也为None。
   • else:：如果当前列不需要合并，则直接取row1的值。
     • combined_row[col] = row1[col]：将row1中该列的值赋给combined_row。
   • formatted_df = formatted_df.append(combined_row, ignore_index=True)：将构建好的combined_row（字典形式）作为新行追加到formatted_df中。ignore_index=True 确保新行获得一个新的、连续的索引，而不是尝试使用combined_row的字典键作为索引。
4. 保存结果：
   • formatted_df.to_excel(output_excel_file, index=False)：将最终格式化好的DataFrame保存到一个新的Excel文件。index=False 防止Pandas将DataFrame的索引也写入Excel文件。

注意事项

1. 自定义合并列： 代码中的columns_to_combine列表是您需要根据实际数据进行调整的核心。请确保将其替换为您的Excel文件中实际需要合并的列名。
2. 数据类型： 合并后的单元格内容将是一个Python列表（例如 ['Data B1', 'Data B1\'']）。这可能会影响后续的数据分析操作，您可能需要进一步处理这些列表（例如，将它们连接成字符串，或提取特定元素）。
3. 数据完整性与对齐： 此方法假设数据是严格按对（两行一组）排列的。如果原始数据中存在不规则的跨行（例如，有些记录只占一行，有些占三行），则此代码需要进行更复杂的修改以适应这些情况。在执行前，最好对原始数据进行初步检查。
4. 性能考虑： 对于非常庞大的数据集（例如，数十万行甚至更多），在循环中反复使用DataFrame.append()可能会导致性能问题，因为它每次都会创建一个新的DataFrame。对于这类场景，更高效的做法是先将所有combined_row收集到一个列表中，最后使用pd.concat()一次性创建formatted_df。然而，对于大多数中等规模的数据集，当前方法足够清晰和高效。
5. 空值处理： 如果原始数据中存在空值（NaN），它们也会被包含在合并后的列表中。您可能需要在合并前或合并后对这些空值进行清理或替换。

总结

通过上述Pandas迭代方法，我们可以有效地解决Excel数据中逻辑记录跨越物理行的问题。这种方法提供了一个灵活的框架，允许我们根据特定列的需求进行数据合并，从而将非规范化的原始数据转换为更易于管理和分析的标准表格格式。掌握这种数据清洗技巧对于任何需要处理复杂Excel数据的数据分析师或开发者来说都至关重要。