Pandas中分组聚合后透视报错KeyError: 'SubMarkets'的解决方法

The Root Cause #

When you run groupby(["Year", "SubMarkets"]).mean(), the resulting Series has a MultiIndex (hierarchical index) where Year and SubMarkets are index levels—not regular columns in the DataFrame. Even after converting to a DataFrame with df.to_frame(), these two values stay part of the index, not accessible as column names. That’s exactly why pivot(index='SubMarkets', ...) throws a KeyError—it can’t find SubMarkets in the DataFrame’s column list.

Solution 1: Use unstack() Directly (Most Efficient) #

Instead of converting to a DataFrame first and then pivoting, you can use unstack() on the grouped Series to reshape the data exactly how you want. This method is purpose-built for rearranging MultiIndex data:

# Group, calculate mean, then reshape with unstack
df_pivoted = county_rental_df.groupby(["Year", "SubMarkets"])["1_Bed_Effective_Rent_Per_Unit_Modified"].mean().unstack(level="Year").reset_index()

# Optional: Clean up the column name for a cleaner output
df_pivoted.columns.name = None

This will directly produce your desired output:

Solution 2: Reset Index First (If You Want to Keep Your Original Workflow) #

If you prefer to stick with your initial to_frame() step, you need to convert the MultiIndex into regular columns first using reset_index():

# Step 1: Group and get average values
df = county_rental_df.groupby(["Year", "SubMarkets"])["1_Bed_Effective_Rent_Per_Unit_Modified"].mean()

# Step 2: Convert to DataFrame and reset index (turns index levels into columns)
df_1 = df.to_frame().reset_index()

# Step 3: Now pivot works because SubMarkets and Year are regular columns
df_1_2 = df_1.pivot(index='SubMarkets', columns='Year', values='1_Bed_Effective_Rent_Per_Unit_Modified').reset_index()

# Optional: Clean up column name
df_1_2.columns.name = None

Quick Explanation #

• unstack(level="Year") takes the Year level from the index and turns it into columns, aligning each year’s values with the correct SubMarket.
• reset_index() moves all index levels into the DataFrame as standard columns, making them available for the pivot() function to reference.

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