如何用Python Pandas自动清理格式不规则的Excel冗余数据？

核心思路是逐行读取文件，基于固定表头特征定位有效数据的起始位置，无需手动指定跳过行数，实现全自动化处理。

实现步骤 #

• 遍历文件内容，通过识别表头中的固定关键词（如Deal Id、Present Value）找到表头所在行；
• 若存在分隔线（全短横线组成的行），则跳过表头和分隔线，从数据行开始导入；
• 最后用Pandas读取文件，清理空行和尾部无关内容。

处理CSV文件的代码示例 #

import pandas as pd
import csv

def find_header_row(csv_path, target_columns=["Deal Id", "Deal Date", "Present Value"]):
    with open(csv_path, 'r', encoding='utf-8') as f:
        reader = csv.reader(f)
        for row_num, row in enumerate(reader):
            # 清理当前行的空内容，保留有效文本
            cleaned_row = [col.strip() for col in row if col.strip()]
            # 检查是否包含所有目标表头关键词
            if all(col in cleaned_row for col in target_columns):
                # 检查下一行是否是分隔线（连续短横线）
                next_row = next(reader, [])
                if any('-'*5 in cell for cell in next_row):
                    # 跳过表头行和分隔线行，返回数据起始行号
                    return row_num + 2
                # 无分隔线则返回表头行号
                return row_num
    return None

# 调用函数定位起始行
csv_path = "your_valuation.csv"
header_row = find_header_row(csv_path)

if header_row:
    # 导入数据，跳过起始行之前的所有行
    df = pd.read_csv(csv_path, skiprows=header_row)
    # 清理全空行和尾部说明文本
    df = df.dropna(how='all').reset_index(drop=True)
    print("数据导入成功：")
    print(df.head())
else:
    print("未识别到有效表头，请检查目标关键词是否匹配")

处理Excel文件的代码示例 #

import pandas as pd
from openpyxl import load_workbook

def find_excel_header_row(excel_path, sheet_name=0, target_columns=["Deal Id", "Deal Date", "Present Value"]):
    wb = load_workbook(excel_path, read_only=True)
    ws = wb[sheet_name] if isinstance(sheet_name, str) else wb.worksheets[sheet_name]
    
    # 逐行遍历工作表内容
    for row_num, row in enumerate(ws.iter_rows(values_only=True), start=1):
        cleaned_row = [str(cell).strip() for cell in row if str(cell).strip()]
        if all(col in cleaned_row for col in target_columns):
            # 检查下一行是否为分隔线
            next_row = next(ws.iter_rows(min_row=row_num+1, max_row=row_num+1, values_only=True), [])[0]
            if any('-'*5 in str(cell) for cell in next_row if str(cell)):
                return row_num + 1  # 跳过分隔线，返回数据起始行
            return row_num
    return None

# 调用函数定位起始行
excel_path = "your_valuation.xlsx"
header_row = find_excel_header_row(excel_path)

if header_row:
    df = pd.read_excel(excel_path, skiprows=header_row-1)
    df = df.dropna(how='all').reset_index(drop=True)
    print("数据导入成功：")
    print(df.head())
else:
    print("未识别到有效表头，请检查目标关键词是否匹配")

注意事项 #

• 可根据实际表格的表头特征调整target_columns中的关键词，确保识别准确性；
• 对于多行合并的表头（如示例中跨列的Mark to Market），优先选取最下方的具体列名作为识别关键词；
• 若表格尾部有说明文本，dropna(how='all')可自动去除全空行，若仍有非空的说明行，可进一步通过判断行内容是否包含特定关键词（如"Notice"）来过滤。

内容的提问来源于stack exchange，提问作者DBGGHOST