Python无循环处理含标题与数据块的Excel文件方案

Awesome question—this kind of messy Excel layout is way too common, and ditching loops for pandas' vectorized operations will make your code faster, cleaner, and easier to maintain. Here's a streamlined, loop-free approach to get your desired DataFrame:

Step 1: Read the Raw Data #

First, read the Excel file without specifying a header—since headers repeat under each category, we’ll handle them manually later:

import pandas as pd

# Replace with your actual file path
df = pd.read_excel("your_unstructured_file.xlsx", header=None)

Step 2: Flag Key Row Types #

We need to identify three critical row types to process the data correctly:

• Empty rows (separate different categories)
• Header rows (the Article Name/Quantity labels)
• Category title rows (the standalone category names)

# Flag rows where all columns are empty
is_empty = df.isna().all(axis=1)

# Flag rows that contain the data headers
is_header = df[0] == "Article Name"

# Create a Category column, only populating it with category title rows
df["Category"] = df[0].where(~is_empty & ~is_header)

Step 3: Propagate Categories & Clean the Data #

Use pandas' ffill() (forward fill) to carry category values down to all associated data rows, then filter out unwanted rows and fix column names:

# Forward-fill category names to every row in their group
df["Category"] = df["Category"].ffill()

# Keep only actual data rows (exclude empty rows and headers)
final_df = df[~is_empty & ~is_header].copy()

# Rename columns to their proper labels
final_df = final_df.rename(columns={0: "Article Name", 1: "Quantity"})

# Reset index for a clean, sequential output
final_df = final_df.reset_index(drop=True)

Test with Sample Data #

To verify this works, use this sample dataset that mimics your Excel structure:

# Simulate the unstructured Excel layout
sample_data = [
    ["Electronics", None],
    ["Article Name", "Quantity"],
    ["Laptop", 10],
    ["Phone", 20],
    [None, None],
    ["Clothing", None],
    ["Article Name", "Quantity"],
    ["Shirt", 50],
    ["Pants", 30],
]
sample_df = pd.DataFrame(sample_data)

# Run the steps above on sample_df to see the final categorized DataFrame

Edge Case Adjustments #

• If your Excel has inconsistent header formatting (e.g., extra spaces, capitalization), tweak the is_header check:

  is_header = df[0].str.strip().str.lower() == "article name"
  

• If category rows have values in other columns (unlikely per your description), adjust the df[0].where(...) condition to match your actual layout.

This approach leverages pandas' optimized vectorized operations instead of slow loops, making it far more efficient for large datasets while keeping the code concise and readable.

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