Python读取Excel双表格列表头异常问题求助

问题描述 #

编写的Python代码用于读取AWS S3存储中的Excel文件并做数据处理，该Excel包含两个表头完全相同的表格，但代码无法正确识别表头，误将表头当作数据处理，执行时出现报错。

完整代码 #

import pandas as pd
import string
import random
import logging
import io
import boto3
import base64


if len(logging.getLogger().handlers) > 0:
    # The Lambda environment pre-configures a handler logging to stderr.
    # If a handler is already configured,
    # `.basicConfig` does not execute. Thus we set the level directly.
    logging.getLogger().setLevel(logging.INFO)
else:
    logging.basicConfig(level=logging.INFO)


if len(logging.getLogger().handlers) > 0:
    # The Lambda environment pre-configures a handler logging to stderr.
    # If a handler is already configured,
    # `.basicConfig` does not execute. Thus we set the level directly.
    logging.getLogger().setLevel(logging.INFO)
else:
    logging.basicConfig(level=logging.INFO)


def read_data_between_headers(df, target_headers):
    repeat_indices = df[df.apply(lambda row: all(cell in row.values for cell in target_headers), axis=1)].index

    if len(repeat_indices) > 1:
        data_between_headers = df.iloc[repeat_indices[0] + 1:repeat_indices[-1]]
        data_between_headers = data_between_headers.dropna(how='all').iloc[:-1]
        data_between_headers = data_between_headers.dropna(subset=target_headers, how='all')

        data_after_last_occurrence = df.iloc[repeat_indices[-1] + 1:]
        data_after_last_occurrence = data_after_last_occurrence.dropna(how='all')
        data_after_last_occurrence = data_after_last_occurrence.dropna(subset=target_headers, how='all')

        logging.info("read data between headers")
        logging.info("Columns in data_between_headers: %s", data_between_headers.columns)
        print("******************", data_between_headers, data_after_last_occurrence)
        return data_between_headers, data_after_last_occurrence

    else:
        logging.info("no data between headers")
        return pd.DataFrame(), pd.DataFrame()


def random_alphanumeric(length, hyphen_interval=4):
    characters = string.ascii_letters + string.digits
    random_value = ''.join(random.choice(characters) for _ in range(length))
    return '-'.join(random_value[i:i + hyphen_interval] for i in range(0, len(random_value), hyphen_interval))

def process_excel(df, target_headers):
    resulting_data = read_data_between_headers(df, target_headers)
    data_between_headers, data_after_last_occurrence = resulting_data
    print(data_between_headers, data_after_last_occurrence)
    print("Before conversion:", data_between_headers['Value'])
    data_between_headers['Val'] = pd.to_numeric(data_between_headers['Val'], errors='coerce')
    
    print("After conversion:", data_between_headers['Val'])
    data_between_headers['Type'] = 'GLA'
    data_after_last_occurrence['Val'] = pd.to_numeric(data_after_last_occurrence['Val'], errors='coerce')
    data_after_last_occurrence['Type'] = 'GLA'
    logging.info("process excel worked")

    updated_rows = []

    for index, row in data_between_headers.iterrows():
        updated_rows.append(dict(row))
        if row['Value'] != 0:
            new_row = dict(row)
            new_row['Val'] = -row['Val']
            new_row['ID'] = random_alphanumeric(16, hyphen_interval=4)
            new_row['GLA'] = '2100'

            for col in data_between_headers.columns:
                if col not in ['ID', 'Comp', 'Type', 'Jou', 'Val', 'GLA']:
                    new_row[col] = ''
            updated_rows.append(new_row)
            print(updated_rows)

    updated_rows.extend([{}, {}])
    updated_rows.append({key: key for key in data_between_headers.columns})

    for index, row in data_after_last_occurrence.iterrows():
        updated_rows.append(dict(row))
        if row['Value'] != 0:
            new_row = dict(row)
            new_row['Val'] = -row['Val']
            new_row['ID'] = random_alphanumeric(16, hyphen_interval=4)
            new_row['GLA'] = '2100'

            for col in data_after_last_occurrence.columns:
                if col not in ['ID', 'Comp', 'Type', 'Jou', 'Val', 'GLA']:
                    new_row[col] = ''
            updated_rows.append(new_row)

    updated_df = pd.DataFrame(updated_rows, columns=data_between_headers.columns)
    logging.info("processed data successfully")

    return updated_df

def read_excel_from_s3(file_path):
    logging.info("Reading cheatsheet from S3.")
    try:
        bucket_name = 'kloo-approved-invoice-integration'
        object_key = file_path
        s3 = boto3.client('s3')
        logging.info(f"Bucket={bucket_name}, Key={object_key}")
        obj = s3.get_object(Bucket=bucket_name, Key=object_key)
        data = obj['Body'].read()
        print(data)
        df = pd.read_excel(io.BytesIO(data))
    except Exception as error:
        logging.error(f"Something went wrong with reading Excel file from S3: {file_path}")
        df = pd.DataFrame()  # Return an empty DataFrame in case of an error
    return df

def write_excel_with_specific_columns(df, output_file_path):
    logging.info(f"Writing DataFrame to {output_file_path} with specific columns.")
    try:
        with io.BytesIO() as output:
            with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
                df.to_excel(writer, index=False)  # Set index=False to exclude the index column
            data = output.getvalue()

        s3 = boto3.resource('s3')
        s3.Bucket('kloo-approved-invoice-integration').put_object(Key='stage/test2.xlsx', Body=data)
        logging.info(f"DataFrame successfully written to {output_file_path}.")
    except Exception as error:
        logging.error(f"Something went wrong with writing DataFrame to Excel file: {output_file_path}")

def lambda_handler(event, context):
    try:
        query_string_parameters = event['queryStringParameters']
        xl_input_file = query_string_parameters.get('xl_input_file')
        # xl_input_file = event.get('xl_input_file') or os.environ.get("XL_INPUT_FILE")

        if xl_input_file:
            print(f"Excel file to process: {xl_input_file}")

            target_headers = ['ID', 'Comp', 'Type', 'Jou', 'Val', 'GLA']

            # Read the Excel file from S3
            excel_df = read_excel_from_s3(xl_input_file)
            # logging.info(excel_df)

            # result_df = process_excel(xl_input_file, target_headers)
            result_df = process_excel(excel_df, target_headers)
            # logging.info(result_df)

            # Specify the output file path
            # output_file_path = 'kloo-approved-invoice-integration/stage/test2.xlsx'
            encoded_data = base64.b64encode(result_df.to_csv().encode(), validate=True)
            logging.info(encoded_data)

            # Write the processed DataFrame to a new Excel file with specific columns
            write_excel_with_specific_columns(result_df, 'stage/test2.xlsx')

            # # Write DataFrame to a new Excel file with specific columns
            # write_excel_with_specific_columns(excel_df, 'kloo-approved-invoice-integration/stage/test2.xlsx', target_headers)

            return {
                'statusCode': 200,
                'body': 'Process completed successfully',
                'result_df': encoded_data.decode() # Convert DataFrame to a JSON-serializable format
            }
        else:
            print("No Excel file provided.")
            return {
                'statusCode': 400,
                'body': 'No Excel file provided'
            }

    except Exception as e:
        print(f"Error: {str(e)}")
        return {
            'statusCode': 500,
            'body': f"Error: {str(e)}"
        }

报错信息 #

\x1b\x0b\xf8\x0e\xb9\x00\x00\x00\xc9\x00\x00\x00\x18\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00sL\x00\x00customXml/itemProps3.xmlPK\x01\x02-\x00\x14\x00\x06\x00\x08\x00\x00\x00!\x00t?9z\xc2\x00\x00\x00(\x01\x00\x00\x1e\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x8aM\x00\x00customXml/_rels/item1.xml.relsPK\x01\x02-\x00\x14\x00\x06\x00\x08\x00\x00\x00!\x00\\\x96'"\xc3\x00\x00\x00(\x01\x00\x00\x1e\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x90O\x00\x00customXml/_rels/item2.xml.relsPK\x01\x02-\x00\x14\x00\x06\x00\x08\x00\x00\x00!\x00{\xf3\x02\xa3\xc3\x00\x00\x00(\x01\x00\x00\x1e\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x97Q\x00\x00customXml/_rels/item3.xml.relsPK\x05\x06\x00\x00\x00\x00\x17\x00\x17\x00\x0f\x06\x00\x00\x9eS\x00\x00\x00\x00'

Error: ['ID', 'Comp', 'Type', 'Jou', 'Val', 'GLA']

问题分析与预期行为 #

• 现有问题：代码将列名当作数据处理，未正确识别表头；同时process_excel中存在列名错误（使用Value而非目标表头中的Val），引发KeyError。
• 预期行为：读取两个表格的表头和数据，忽略空行并完成指定数据处理。

解决方案 #

1. 修正Excel读取逻辑，读取无表头数据 #

修改read_excel_from_s3，先读取所有内容为无表头格式，避免默认将第一行设为表头导致后续表头匹配混乱，同时移除无效的二进制内容打印：

def read_excel_from_s3(file_path):
    logging.info("Reading cheatsheet from S3.")
    try:
        bucket_name = 'kloo-approved-invoice-integration'
        object_key = file_path
        s3 = boto3.client('s3')
        logging.info(f"Bucket={bucket_name}, Key={object_key}")
        obj = s3.get_object(Bucket=bucket_name, Key=object_key)
        data = obj['Body'].read()
        # 读取无表头的Excel数据，用默认列名（0,1,2...）
        df = pd.read_excel(io.BytesIO(data), header=None)
    except Exception as error:
        logging.error(f"Something went wrong with reading Excel file from S3: {file_path}")
        df = pd.DataFrame()  # Return an empty DataFrame in case of an error
    return df

2. 修复表头匹配逻辑，精准定位表头行 #

修改read_data_between_headers，将表头匹配逻辑改为严格对比行内容与目标表头（顺序一致），同时为提取到的表格数据设置正确的表头：

def read_data_between_headers(df, target_headers):
    # 严格匹配整行内容等于目标表头（忽略空值，保证顺序一致）
    repeat_indices = df[df.apply(lambda row: list(row.dropna().values) == target_headers, axis=1)].index

    if len(repeat_indices) > 1:
        # 处理第一个表格（两个表头之间的数据）
        table1_data = df.iloc[repeat_indices[0] + 1:repeat_indices[-1]]
        table1_data = table1_data.dropna(how='all')
        # 设置正确的表头
        table1_data.columns = target_headers
        # 过滤全空的有效行
        table1_data = table1_data.dropna(subset=target_headers, how='all')

        # 处理第二个表格（最后一个表头之后的数据）
        table2_data = df.iloc[repeat_indices[-1] + 1:]
        table2_data = table2_data.dropna(how='all')
        # 设置正确的表头
        table2_data.columns = target_headers
        # 过滤全空的有效行
        table2_data = table2_data.dropna(subset=target_headers, how='all')

        logging.info("read data between headers")
        logging.info("Columns in table1: %s", table1_data.columns)
        print("******************", table1_data, table2_data)
        return table1_data, table2_data

    else:
        logging.info("no data between headers")
        return pd.DataFrame(columns=target_headers), pd.DataFrame(columns=target_headers)

3. 修正process_excel中的列名错误 #

将代码中错误的data_between_headers['Value']替换为data_between_headers['Val']：

def process_excel(df, target_headers):
    resulting_data = read_data_between_headers(df, target_headers)
    data_between_headers, data_after_last_occurrence = resulting_data
    print(data_between_headers, data_after_last_occurrence)
    # 修正列名：Value → Val
    print("Before conversion:", data_between_headers['Val'])
    data_between_headers['Val'] = pd.to_numeric(data_between_headers['Val'], errors='coerce')
    
    print("After conversion:", data_between_headers['Val'])
    data_between_headers['Type'] = 'GLA'
    data_after_last_occurrence['Val'] = pd.to_numeric(data_after_last_occurrence['Val'], errors='coerce')
    data_after_last_occurrence['Type'] = 'GLA'
    logging.info("process excel worked")

    updated_rows = []

    for index, row in data_between_headers.iterrows():
        updated_rows.append(dict(row))
        # 修正列名：Value → Val
        if row['Val'] != 0:
            new_row = dict(row)
            new_row['Val'] = -row['Val']
            new_row['ID'] = random_alphanumeric(16, hyphen_interval=4)
            new_row['GLA'] = '2100'

            for col in data_between_headers.columns:
                if col not in ['ID', 'Comp', 'Type', 'Jou', 'Val', 'GLA']:
                    new_row[col] = ''
            updated_rows.append(new_row)
            print(updated_rows)

    updated_rows.extend([{}, {}])
    updated_rows.append({key: key for key in data_between_headers.columns})

    for index, row in data_after_last_occurrence.iterrows():
        updated_rows.append(dict(row))
        # 修正列名：Value → Val
        if row['Val'] != 0:
            new_row = dict(row)
            new_row['Val'] = -row['Val']
            new_row['ID'] = random_alphanumeric(16, hyphen_interval=4)
            new_row['GLA'] = '2100'

            for col in data_after_last_occurrence.columns:
                if col not in ['ID', 'Comp', 'Type', 'Jou', 'Val', 'GLA']:
                    new_row[col] = ''
            updated_rows.append(new_row)

    updated_df = pd.DataFrame(updated_rows, columns=data_between_headers.columns)
    logging.info("processed data successfully")

    return updated_df

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