如何用Pandas同时按列索引和列头提取多Excel文件数据？

Absolutely, you can pull off this mix of column selection! The usecols parameter does only accept a single type (either all indices or all names), but we can work around that with two straightforward approaches depending on your file size and performance needs.

Method 1: Efficient Column Name Pre-fetch (Best for Large Files) #

This approach first grabs the name of the first column (since its index is fixed at 0 but name varies), then combines it with your fixed column names to use in usecols. This avoids loading unnecessary data into memory.

import pandas as pd

def load_target_columns(file_path):
    # Step 1: Read only the header row to get column names (no data rows)
    header_only = pd.read_excel(file_path, nrows=0)
    
    # Step 2: Get the name of the first column (index 0)
    first_col_name = header_only.columns[0]
    
    # Step 3: Combine with your fixed column names
    desired_columns = [first_col_name, 'TOTAL', 'CLEAR', 'NON-CLEAR', 'SYSTEM']
    
    # Optional: Validate all fixed columns exist in the file
    fixed_cols = ['TOTAL', 'CLEAR', 'NON-CLEAR', 'SYSTEM']
    missing_cols = [col for col in fixed_cols if col not in header_only.columns]
    if missing_cols:
        print(f"Warning: File {file_path} is missing columns: {', '.join(missing_cols)}")
        # Add handling here (e.g., skip file, proceed with available columns)
    
    # Step 4: Load only the desired columns
    df = pd.read_excel(file_path, usecols=desired_columns)
    return df

# Example usage
your_file = "sample_data.xlsx"
result_df = load_target_columns(your_file)

Method 2: Full Load + Column Filter (Simpler for Small Files) #

If your Excel files are small enough that loading all columns isn't an issue, you can read the entire file first, then filter down to the columns you need—combining index-based selection for the first column and name-based selection for the rest.

import pandas as pd

def load_target_columns_simple(file_path):
    # Step 1: Read the entire file
    full_df = pd.read_excel(file_path)
    
    # Step 2: Combine the first column (by index) and fixed columns (by name)
    target_df = full_df[[full_df.columns[0], 'TOTAL', 'CLEAR', 'NON-CLEAR', 'SYSTEM']]
    
    return target_df

# Example usage
your_file = "sample_data.xlsx"
result_df = load_target_columns_simple(your_file)

This method is more readable for quick scripts, just keep in mind it uses more memory for large datasets.

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