对具有重复索引的表进行透视

Question

我想透视这个表:

Year    County  Sex     rate
0   2006    Alameda Male    45.80
1   2006    Alameda Female  54.20
2   2006    Alpine  Male    52.81
3   2006    Alpine  Female  47.19
4   2006    Amador  Male    49.97
5   2006    Amador  female  50.30

我想要的输出是:

Year    County  Male  Female
2006    Alameda 45.80 54.20
2006    Alameda 52.81 47.19
2006    Alpine  49.97 50.30

I tried doing this:

sex_rate=g.pivot(index="County",columns='Year',values='rate')

但是我一直得到这个错误:

ValueError: Index contains duplicate entries, cannot reshape

请帮助。I am new to python

Answer 1

我认为你想要index=['Year', 'County']，而不仅仅是index='County'。由于您将两列传递给index，您可能希望使用pivot_table而不是pivot:

df.pivot_table(index=['Year','County'], 
columns='Sex', values='rate'
).reset_index()

输出:

Sex  Year   County  Female   Male
0    2006  Alameda   54.20  45.80
1    2006   Alpine   47.19  52.81
2    2006   Amador   50.30  49.97