6000+独立格式Excel表格的数据与结构清洗技术咨询

Wow, dealing with 6000+ messy Excel files sounds like a real headache—especially when they’re supposed to have the same core data but are all over the place with column order, typos, and multi-level headers. Your initial approach of reading cells in a fixed order makes sense, but it’s no surprise that the structural inconsistencies are throwing you off. Let’s break down a systematic strategy to tackle this:

First, you need to formalize exactly what your standardized data should look like. List out all 9-13 core columns with their correct names, data types, and descriptions. This becomes your single source of truth to map every messy file against. For example:

• CustomerID (integer, unique customer identifier)
• TransactionDate (datetime, format YYYY-MM-DD)
• TotalAmount (float, currency value)
• ProductCategory (string, e.g., "Electronics", "Apparel")

Misspelled or inconsistent column names are likely your biggest hurdle. Here’s how to fix that:

• For each file, extract all column headers (flatten multi-level headers first if needed—more on that later).
• Use fuzzy matching to map these messy headers to your golden schema. Libraries like rapidfuzz or fuzzywuzzy work great for this, as they account for typos, abbreviations, and minor wording differences. Example code snippet:

  from rapidfuzz import process, fuzz
  
  # Your golden standard column names
  golden_columns = ["CustomerID", "TransactionDate", "TotalAmount", "ProductCategory"]
  # Messy columns from a sample file
  messy_columns = ["CustID", "Trans Date", "Total $", "Prod Cat"]
  
  mapped_columns = {}
  for col in messy_columns:
      # Find the best match in the golden schema
      match, score, _ = process.extractOne(col, golden_columns, scorer=fuzz.WRatio)
      # Only keep matches with a high enough confidence (adjust threshold as needed)
      if score > 70:
          mapped_columns[col] = match
  

• For multi-level headers (e.g., a top header "Sales" with a sub-header "Total"), concatenate the levels into a single string first (like Sales_Total) before running fuzzy matching.

Once you’ve mapped the messy columns to your golden schema, you can force consistency:

• After reading the Excel file into a pandas DataFrame, rename the columns using your mapped_columns dictionary.
• Use df.reindex(columns=golden_columns) to reorder the columns to match your schema. This will automatically create NaN values for any missing columns—you can either flag those files for manual review or fill in defaults if you know what the missing data should be.

If some files have 2nd or 3rd level headers, you need to flatten them before processing:

• When reading the file with pandas.read_excel(), specify which rows contain headers using the header parameter. For example, if headers are in rows 0 and 1:

  import pandas as pd
  df = pd.read_excel("messy_file.xlsx", header=[0, 1])
  

• Then flatten the multi-index columns into a single string:

  df.columns = ['_'.join(col).strip() for col in df.columns.values]
  

• Now you can proceed with the fuzzy matching step as usual.

To handle this volume efficiently, wrap the above steps into a reusable function and iterate over all your files:

from pathlib import Path

def process_excel_file(file_path):
    # Read the file (adjust header parameter if needed for multi-level headers)
    df = pd.read_excel(file_path)
    
    # Extract messy columns and map to golden schema
    messy_columns = df.columns.tolist()
    mapped_columns = {}
    for col in messy_columns:
        match, score, _ = process.extractOne(col, golden_columns, scorer=fuzz.WRatio)
        if score > 70:
            mapped_columns[col] = match
    
    # Rename columns and reorder to golden schema
    df = df.rename(columns=mapped_columns)
    df = df.reindex(columns=golden_columns)
    
    return df

# Get all Excel files in your target directory
all_files = list(Path("your_excel_directory").glob("*.xlsx"))

# Combine all processed files into a single DataFrame
combined_standardized_data = pd.concat([process_excel_file(f) for f in all_files], ignore_index=True)

# Save the final standardized data
combined_standardized_data.to_excel("all_standardized_data.xlsx", index=False)

• Low confidence matches: If a column name doesn’t hit your fuzzy match threshold (e.g., <70), flag that file for manual review—don’t guess, as that can introduce hard-to-catch errors.
• Data type inconsistencies: After standardizing columns, enforce the correct data types from your golden schema using df.astype() or pd.to_datetime() (for date columns).
• Garbage rows: Add a step to drop rows that are mostly empty or contain irrelevant data, like df = df.dropna(thresh=5) to keep rows with at least 5 non-null values.

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