如何用Python Pandas基于父ID拆解分类嵌套表格为扁平表格

Scenario 1: Row-Span Nested Categories #

This applies when parent category labels span multiple rows of subcategories, leaving empty/NaN values in the parent column for subcategory rows.

Sample Nested DataFrame #

First, create a representative nested table:

import pandas as pd
import numpy as np

# Build nested table structure
nested_df = pd.DataFrame({
    "Category": ["Electronics", np.nan, np.nan, "Apparel", np.nan, np.nan],
    "Subcategory": [np.nan, "Phones", "Laptops", np.nan, "Shirts", "Pants"],
    "Value": [np.nan, 100, 200, np.nan, 150, 120]
})

print("Nested Table:")
print(nested_df)

Flattening Code #

# Copy original data to avoid modifying the source
flat_df = nested_df.copy()

# Fill missing parent category values with the last valid category
flat_df["Category"] = flat_df["Category"].ffill()

# Remove rows that are just category headers (no subcategory/value data)
flat_df = flat_df.dropna(subset=["Subcategory", "Value"])

# Reset index for clean output
flat_df = flat_df.reset_index(drop=True)

print("\nFlat Table:")
print(flat_df)

Output #

The resulting flat table has complete category-subcategory pairs for every row:

Category Subcategory  Value
0  Electronics      Phones  100.0
1  Electronics     Laptops  200.0
2     Apparel      Shirts  150.0
3     Apparel       Pants  120.0

Scenario 2: Multi-Level Column Headers #

If your nested table uses multi-row column headers (e.g., top-level headers grouping sub-columns), use this approach:

Sample Multi-Header Table #

# Define multi-level column headers
headers = [["Category", "Subcategory", "Sales", "Sales"],
           ["", "", "Q1", "Q2"]]
multi_col_df = pd.DataFrame(
    [["Electronics", "Phones", 100, 150],
     ["Electronics", "Laptops", 200, 250],
     ["Apparel", "Shirts", 150, 180],
     ["Apparel", "Pants", 120, 140]],
    columns=pd.MultiIndex.from_tuples(zip(*headers))
)

print("Multi-Header Nested Table:")
print(multi_col_df)

Flattening Code #

# Combine multi-level headers into single, readable column names
flat_col_df = multi_col_df.copy()
flat_col_df.columns = ["_".join(col).strip("_") for col in flat_col_df.columns.values]

print("\nFlat Column Table:")
print(flat_col_df)

Output #

Columns are flattened to unique, descriptive names:

Category Subcategory  Sales_Q1  Sales_Q2
0  Electronics      Phones       100       150
1  Electronics     Laptops       200       250
2     Apparel      Shirts       150       180
3     Apparel       Pants       120       140

Key Notes #

• Use ffill() to propagate parent category values down rows efficiently.
• For multi-level columns, adjust the separator (like _) based on your naming preferences.
• Modify dropna() parameters if your data allows missing values in certain columns.

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