利用Python Pandas处理Excel非标准格式数据：跨行单元格合并技巧

本文详细介绍了如何使用python pandas库自动化处理excel电子表格中的非标准数据格式，特别是针对那些数据内容跨越多个连续行但逻辑上属于同一单元格的情况。通过迭代处理行对并有条件地合并指定列的数据，将原本分散在两行中的信息整合成单个单元格内的列表，从而将非标准格式的数据转换为规范的表格结构，便于后续分析和处理。

数据格式化挑战

在日常数据处理中，我们经常会遇到从各种系统导出的Excel文件，其数据格式可能并非标准的二维表格。例如，某些数据项为了排版或历史原因，会跨越两行甚至多行显示，但从逻辑上讲，它们应该属于同一条记录的同一列。

以一个常见的场景为例：原始Excel数据中，Data B1 和 Data D1 的信息分别占据了连续的两行。如果直接将这样的数据导入到表格中，会导致数据错位，形成如下两行不完整的记录：

1:[Data A1, Data B1(第一行), Data C1, Data D1(第一行), Data E1],
2:[' ', Data B1(第二行), ' ', Data D1(第二行), ' ']

然而，我们期望的理想格式是将属于同一逻辑单元格的数据合并到一行中，例如：

1:[Data A1, [Data B1(第一行), Data B1(第二行)], Data C1, [Data D1(第一行), Data D1(第二行)], Data E1]

这种格式化需求对于后续的数据分析、数据库导入或报表生成至关重要。手动处理耗时且易出错，因此需要一个自动化的解决方案。

立即学习“Python免费学习笔记（深入）”；

巧文书

巧文书是一款AI写标书、AI写方案的产品。通过自研的先进AI大模型，精准解析招标文件，智能生成投标内容。

61

查看详情

Pandas解决方案概述

Python的Pandas库是处理表格数据（DataFrame）的强大工具。针对上述跨行单元格合并的问题，我们可以设计一个策略：

1. 逐对读取行： 遍历DataFrame，每次读取两行数据。
2. 识别目标列： 确定需要合并内容的列（例如，示例中的 'Data B1' 和 'Data D1' 对应的列）。
3. 条件合并： 对于目标列，将两行中的数据合并成一个列表；对于其他列，则取第一行的数据。
4. 构建新DataFrame： 将处理后的新行添加到结果DataFrame中。

代码实现与解析

以下是使用Pandas实现上述数据转换的完整代码：

import pandas as pd

# 假设Excel文件名为 'data.xlsx'，数据位于 'Sheet1'
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}' 未找到。请确保文件存在且路径正确。")
    exit()
except Exception as e:
    print(f"读取Excel文件时发生错误：{e}")
    exit()

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

# 3. 遍历原始DataFrame，每次处理两行
# range(0, len(df), 2) 表示从索引0开始，步长为2，确保每次处理一对行
for i in range(0, len(df), 2):
    # 获取当前行 (第一行)
    row1 = df.iloc[i]

    # 安全地获取下一行 (第二行)
    # 如果当前是最后一行且总行数为奇数，则第二行为None
    row2 = df.iloc[i + 1] if i + 1 < len(df) else None

    # 4. 构建合并后的新行
    combined_row = {}
    for col in df.columns:
        # 确定需要合并的列名。请根据实际Excel文件的列标题进行修改。
        # 例如，如果你的列名是 'B列数据' 和 'D列数据'，则修改为 ['B列数据', 'D列数据']
        if col in ['Data B1', 'Data D1']:  
            # 将两行对应列的数据合并成一个列表
            # 如果row2不存在（即原始数据行数为奇数），则第二部分为None
            combined_row[col] = [row1[col], row2[col] if row2 is not None else None]
        else:
            # 对于不需要合并的列，直接取第一行的数据
            combined_row[col] = row1[col]

    # 5. 将合并后的行添加到结果DataFrame
    # 使用 pd.concat 代替 df.append 以提高性能（对于大型数据集更明显）
    # 但此处为保持与原始答案逻辑一致性，仍使用append，但建议在实际项目中考虑concat
    formatted_df = pd.concat([formatted_df, pd.DataFrame([combined_row])], ignore_index=True)

# 6. 将格式化后的DataFrame保存到新的Excel文件
output_file = 'formatted_output.xlsx'
try:
    formatted_df.to_excel(output_file, index=False)
    print(f"数据已成功格式化并保存到 '{output_file}'")
except Exception as e:
    print(f"保存Excel文件时发生错误：{e}")

代码解析：

1. 文件读取与错误处理: pd.read_excel() 用于加载Excel文件。添加了 try-except 块来处理文件未找到或其他读取错误，提高脚本的健壮性。
2. formatted_df 初始化: 创建一个空的DataFrame formatted_df，它的列名与原始DataFrame df 保持一致，确保最终输出的结构正确。
3. for 循环与 range(0, len(df), 2): 这是核心的遍历逻辑。range(start, stop, step) 函数以步长 2 迭代，确保每次循环 i 都是一对行的起始索引。
4. df.iloc[i]: iloc 是Pandas中基于整数位置进行索引的方法。df.iloc[i] 获取索引为 i 的行，即当前对的第一行。
5. 安全获取 row2: row2 = df.iloc[i + 1] if i + 1 < len(df) else None 这行代码非常关键。它尝试获取当前对的第二行 (i + 1)。if i + 1 < len(df) 条件判断确保不会在原始DataFrame的末尾越界。如果原始DataFrame的总行数为奇数，则最后一次循环时 row2 会被赋值为 None，从而避免程序崩溃。
6. 条件合并逻辑:
   • if col in ['Data B1', 'Data D1']: 这里是指定需要合并的列。请务必将 'Data B1' 和 'Data D1' 替换为你的Excel文件中实际的列标题。
   • combined_row[col] = [row1[col], row2[col] if row2 is not None else None]: 对于指定的列，它将 row1 和 row2 中对应列的数据打包成一个Python列表。如果 row2 为 None，则列表的第二个元素也将是 None。
   • else: combined_row[col] = row1[col]: 对于其他不需要合并的列，直接取 row1 中的数据。
7. pd.concat() 添加行: pd.concat([formatted_df, pd.DataFrame([combined_row])], ignore_index=True) 将新生成的 combined_row 转换为一个单行DataFrame，并与 formatted_df 进行拼接。虽然原始答案使用了 append，但 pd.concat 在处理大量数据时通常更高效，因为它避免了反复创建新的DataFrame对象。ignore_index=True 确保新DataFrame的索引是连续的。
8. 保存结果: formatted_df.to_excel(output_file, index=False) 将处理后的DataFrame保存到一个新的Excel文件。index=False 避免将DataFrame的索引写入Excel文件。

关键点与注意事项

• 列名匹配: 代码中的 ['Data B1', 'Data D1'] 必须与你的Excel文件中的实际列标题完全匹配。如果列名有变化，请相应修改代码。
• 数据结构假设: 本教程假设数据是两行一组进行合并的。如果你的数据是三行一组或更复杂的模式，你需要调整 range() 的步长和 row 的获取逻辑。
• 奇数行处理: 提供的解决方案能够优雅地处理原始数据行数为奇数的情况。在这种情况下，最后一对的第二行将被视为 None，合并后的列表中对应的元素也将是 None。你可以根据实际需求，选择如何处理这些 None 值（例如，在保存前进行清理或填充）。
• 性能优化: 对于非常大的Excel文件（数十万行以上），频繁地使用 pd.concat 可能会影响性能。更优的策略是先将所有 combined_row 收集到一个列表中，然后在循环结束后一次性通过 pd.DataFrame(list_of_combined_rows) 创建最终的DataFrame。
• 数据类型: 合并后的单元格内容会变成Python列表。如果后续需要进一步处理这些列表（例如，提取特定元素或将其转换为字符串），请注意数据类型的变化。

总结

通过Python Pandas，我们可以高效地自动化处理Excel电子表格中的非标准数据格式。本文介绍的方法通过迭代行对和有条件地合并指定列的数据，成功地将跨行显示的逻辑单元格内容整合到单个单元格的列表中，从而将半结构化数据转换为标准的表格格式。掌握这一技巧，将大大提高数据清洗和预处理的效率，为后续的数据分析工作打下坚实基础。

以上就是利用Python Pandas处理Excel非标准格式数据：跨行单元格合并技巧的详细内容，更多请关注php中文网其它相关文章！