如何自动识别多结构Excel表头位置并导入为Pandas DataFrame？

Great question! Dealing with messy, inconsistent Excel files where headers start at random positions (A1, B2, etc.) is super frustrating—your current workaround works, but we can streamline this to avoid post-import cleanup entirely. Here's a cleaner approach that directly targets the header row using your "setting" identifier:

Core Idea #

Instead of importing the entire sheet first and then trimming it, we'll:

• Do a quick scan of the sheet to locate the cell containing your target string ("setting")
• Use that cell's position to define exactly where the header row starts and which columns to import
• Read the data directly with the correct parameters in pd.read_excel()

Implementation Code #

import pandas as pd
import numpy as np

def read_excel_with_dynamic_header(file_path, sheet_name, target_header="setting"):
    # Step 1: Read the entire sheet without treating any row as header
    full_sheet = pd.read_excel(file_path, sheet_name=sheet_name, header=None)
    
    # Step 2: Locate the position of the target header string
    row_indices, col_indices = np.where(full_sheet == target_header)
    
    # Validate there's exactly one occurrence of the target (per your use case)
    if len(row_indices) != 1 or len(col_indices) != 1:
        raise ValueError(f"Expected exactly one '{target_header}' in the sheet, found {len(row_indices)}")
    
    header_row = row_indices[0]
    start_col = col_indices[0]
    
    # Step 3: Read only the relevant data with the correct header
    df = pd.read_excel(
        file_path,
        sheet_name=sheet_name,
        header=header_row,
        usecols=lambda x: x >= start_col  # Keep columns from start_col onwards
    )
    
    return df

# Usage example
Test = read_excel_with_dynamic_header("excelfile.xlsx", "sheetname", "setting")

How This Works #

• Step 1: Reading with header=None ensures we get a raw DataFrame where every cell is treated as data, making it easy to search for our target string.
• Step 2: np.where() quickly finds all cells matching "setting"—we validate there's exactly one match (per your requirement that the rightmost header is always "setting").
• Step 3: We pass header=header_row to tell Pandas which row is the header, and usecols to skip any columns before the start of our header. This imports only the data we need, no extra cleanup required.

Why This Is Better Than Your Temporary Solution #

• Efficiency: For large Excel files, this avoids loading unnecessary rows/columns twice (the scan is lightweight since it's just raw data).
• Cleaner Code: No need for manual dropping of rows/columns or renaming headers post-import.
• Maintainability: You can easily adjust the target header string if needed, and the function handles all logic in one reusable place.

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