Pandas能否读取Excel分组结构并转换为MultiIndex？

Absolutely! You can absolutely build that 4-level MultiIndex (Family → Individual → Child (optional) → Investment) from your structured Excel file using Pandas' read_excel method—with just a bit of straightforward post-processing to clean up the hierarchical gaps. Let me break this down with a concrete example matching your simulated structure.

Step 1: Understand the Simulated Excel Structure #

First, let’s assume your Excel file has a grouped structure like this (where empty cells inherit the value from the nearest non-empty cell above them):

Step 2: Read the Excel File #

Start by reading the file into a DataFrame. If your file has a header row, use header=0; adjust skiprows if needed to skip any title rows above the actual data:

import pandas as pd

# Read the Excel file - adjust parameters based on your actual file
df = pd.read_excel("your_grouped_data.xlsx", header=0)

Step 3: Fill Hierarchical Gaps #

The empty cells in the Family, Individual, and Child columns are meant to inherit the value from the row above. Use ffill() (forward fill) to populate these gaps:

# Fill missing values in the first three hierarchy columns
df[["Family", "Individual", "Child"]] = df[["Family", "Individual", "Child"]].ffill()

Step 4: Remove Subtotal Rows #

Since you mentioned subtotals can be rebuilt later, filter out any rows marked as subtotals (adjust the condition to match how subtotals are labeled in your file):

# Filter out subtotal rows - modify the condition to fit your actual subtotal labels
df = df[df["Individual"] != "Subtotal"]

Step 5: Set the MultiIndex #

Finally, set the first four columns as your 4-level MultiIndex, and rename the index levels for clarity:

# Set the MultiIndex with the four hierarchy columns
df = df.set_index(["Family", "Individual", "Child", "Investment"])

# Optional: Rename index levels to match your requirements
df.index.names = ["Family", "Individual", "Child", "Investment"]

Final Result #

Your DataFrame will now have the exact MultiIndex structure you wanted, with the optional Child level included (even for rows where there’s no child, the index will just have an empty string or NaN, which is perfectly valid). Here’s what the resulting index will look like for the Smith family entries:

MultiIndex([('Smith Family', 'John Smith', '', 'Stocks'),
            ('Smith Family', 'John Smith', '', 'Bonds'),
            ('Smith Family', 'John Smith', 'Jane S.', 'Stocks'),
            ('Smith Family', 'John Smith', 'Jane S.', 'Cash'),
            ('Smith Family', 'Jane Smith', '', 'Real Estate'),
            ('Smith Family', 'Jane Smith', '', 'Cash')],
           names=['Family', 'Individual', 'Child', 'Investment'])

Notes #

• If your Excel uses merged cells for the hierarchy headers, you might need to adjust the initial read step (e.g., using skiprows to skip merged header rows, or manually defining column names).
• For rows where there’s no Child value, the index will retain the filled empty value—you can replace these with None if preferred using df.index = df.index.set_levels(df.index.levels[2].str.replace('', None), level=2).

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