将带MultiIndex的Pandas透视表转换为嵌套字典

Hey there! I totally get the frustration of scouring for solutions that fit your exact use case—converting a Pandas MultiIndex pivot table into a nested dictionary for easy, intuitive access is a common need but not always straightforward. Let's break this down with practical examples and flexible methods.

Step 1: Example MultiIndex Pivot Table #

First, let's create a sample pivot table with a MultiIndex to mirror the structure you're likely working with:

import pandas as pd

# Sample dataset
data = {
    'Category': ['Electronics', 'Electronics', 'Clothing', 'Clothing', 'Electronics'],
    'Subcategory': ['Phones', 'Laptops', 'Shirts', 'Pants', 'Phones'],
    'Brand': ['Apple', 'Dell', 'Nike', 'Levi\'s', 'Samsung'],
    'Monthly Sales': [1200, 900, 600, 750, 800]
}

df = pd.DataFrame(data)
# Create pivot table with 3-level MultiIndex: Category -> Subcategory -> Brand
pivot_table = df.pivot_table(index=['Category', 'Subcategory', 'Brand'], values='Monthly Sales')

Method 1: Recursive Function (Works for Any Number of Index Levels) #

This is the most flexible approach—it handles MultiIndexes with 2, 3, or more levels automatically. The function recursively groups by each index level and builds out the nested structure:

def multiindex_to_nested(df):
    # Base case: if only one index level left, convert to dict
    if df.index.nlevels == 1:
        return df.to_dict('index')
    # Recursive case: group by the first index level, then process the rest
    nested = {}
    for top_level_key, subgroup in df.groupby(level=0):
        # Drop the top index level and recurse
        nested[top_level_key] = multiindex_to_nested(subgroup.droplevel(0))
    return nested

# Convert the pivot table
result_dict = multiindex_to_nested(pivot_table)

What the Output Looks Like #

You’ll get a clean nested structure that lets you access data exactly how you’d expect:

# Access Samsung's phone sales
print(result_dict['Electronics']['Phones']['Samsung']['Monthly Sales'])
# Output: 800

Method 2: Simplified Approach for 2-Level MultiIndexes #

If your pivot table only has two index levels, you can use Pandas built-in methods for a quicker solution:

# For 2-level indexes: unstack and convert to dict
two_level_dict = pivot_table['Monthly Sales'].unstack(level=0).to_dict('series')
# Or reverse the level order if needed
two_level_dict_reversed = pivot_table['Monthly Sales'].unstack(level=1).to_dict('series')

Key Notes #

• The recursive method is future-proof: if you later add more index levels to your pivot table, you won’t need to modify the code.
• If your pivot table has multiple value columns, the recursive method will include all of them in the innermost dictionaries, which is perfect for accessing multiple metrics at once.

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