如何用Pandas批量处理规则命名Excel并导出CSV？含三类疑问

Hey there! Let's break down how to solve both of your Pandas Excel processing needs clearly, with practical code snippets and solutions to common pitfalls.

First, we'll use pandas for data handling and glob to easily match all your similarly-named Excel files (like Design__Tolerance_1.xlsx, Design_Tolerance_2.xlsx, etc.). Here's a step-by-step implementation:

Step 1: Import Required Libraries #

import pandas as pd
import glob

Step 2: Target All Relevant Excel Files #

Use glob to fetch all file paths that match your naming pattern. Replace "/path/to/your/excel/folder/" with the actual folder path where your files are stored (if your script is in the same folder, you can omit the path and just use the filename pattern):

# Match any Excel file starting with "Design" and ending with "_*.xlsx"
file_paths = glob.glob("/path/to/your/excel/folder/Design*Tolerance_*.xlsx")

Step 3: Combine Files into One DataFrame #

We'll read only the columns you specified (['Time', 'Test 1', 'Test 2']) for each file, then append them to a combined DataFrame. I've added an optional column to track which source file each row came from (super helpful for debugging):

# Define the columns we need to extract
fields = ['Time', 'Test 1', 'Test 2']
combined_df = pd.DataFrame()

for file in file_paths:
    # Read only the specified columns, skipping any leading spaces in column names
    df = pd.read_excel(file, skipinitialspace=True, usecols=fields)
    # Optional: Add a source file column to trace data origins
    df['Source_File'] = file.split("/")[-1]  # Grabs just the filename (not full path)
    # Append the current file's data to the combined DataFrame
    combined_df = pd.concat([combined_df, df], ignore_index=True)

Step 4: Export to CSV #

Finally, save the combined DataFrame as a single CSV file:

# Export without the default Pandas index column for cleaner output
combined_df.to_csv("Combined_Tolerance_Test_Data.csv", index=False)

Let's address the real-world hiccups you might run into with this workflow:

• What if some files are missing one or more of the specified columns?
  Add error handling to skip problematic files (or log warnings) instead of crashing your script:

  for file in file_paths:
      try:
          df = pd.read_excel(file, skipinitialspace=True, usecols=fields)
          df['Source_File'] = file.split("/")[-1]
          combined_df = pd.concat([combined_df, df], ignore_index=True)
      except ValueError as e:
          print(f"⚠️ Warning: Could not process {file} - {e}")
  

• What if column names have minor variations (e.g., 'test 1' instead of 'Test 1', or extra spaces)?
  Normalize column names to handle inconsistencies. This code will match columns regardless of case or leading/trailing spaces:

  for file in file_paths:
      # First, load the Excel file to inspect column names
      xl = pd.ExcelFile(file)
      temp_df = xl.parse(skipinitialspace=True)
      
      # Clean column names (strip spaces, lowercase) for matching
      cleaned_cols = [col.strip().lower() for col in temp_df.columns]
      
      # Map our target fields to the actual column names in the file
      field_mapping = {}
      for target_field in fields:
          match = next((col for col, clean_col in zip(temp_df.columns, cleaned_cols) if clean_col == target_field.lower()), None)
          if match:
              field_mapping[target_field] = match
      
      # Skip the file if no matching columns are found
      if not field_mapping:
          print(f"❌ No matching columns found in {file}")
          continue
      
      # Read only the matched columns, then rename them to our target names
      df = xl.parse(usecols=list(field_mapping.values()))
      df.rename(columns={v: k for k, v in field_mapping.items()}, inplace=True)
      
      df['Source_File'] = file.split("/")[-1]
      combined_df = pd.concat([combined_df, df], ignore_index=True)
  

• What if my Excel files use different sheet names?
  Specify the sheet_name parameter in pd.read_excel() or xl.parse(). If sheet names vary across files, you can loop through all sheets in each file (but that's a rare edge case—most batch workflows use consistent sheet names):

  # Example for a specific sheet name
  df = pd.read_excel(file, sheet_name="Test_Data", skipinitialspace=True, usecols=fields)
  

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