What I am trying to do produce the average "1_Bed_Effective_Rent_Per_Unit_Modified" for each "SubMarket" within a "Year". I took the average of four quarters in a year for each "SubMarket" with this line
df = county_rental_df.groupby(["Year", "SubMarkets"])["1_Bed_Effective_Rent_Per_Unit_Modified"].mean() and got this output as a series
| Year | SubMarkets | 1_Bed_Effective_Rent_Per_Unit_Modified |
|---|---|---|
| 2000 | 1 Ward | 1148.50 |
| 2 Ward | 2683.00 | |
| 2001 | 1 Ward | 896.00 |
| 2 Ward | 2107.50 |
I then made the series into a data frame using this df_1 = df.to_frame()
The year column only has the year printed once for each year like shown in the above table.
What I want I want the final product to look like is
| SubMarkets | 2000 | 2001 |
|---|---|---|
| 1 Ward | 1148.50 | 896.00 |
| 2 Ward | 2683 | 2107.50 |
so I used this line df_1_2 = df_1.pivot(index='SubMarkets', columns='Year', values='1_Bed_Effective_Rent_Per_Unit_Modified')\.reset_index() and I get an error "KeyError: 'SubMarkets'". How can I fix this to get my desired output?
CodePudding user response：
You need reset the index of df_1 before pivot
df_1 = df_1.reset_index()
df_1_2 = df_1.pivot(index='SubMarkets', columns='Year', values='1_Bed_Effective_Rent_Per_Unit_Modified')