Converting long format to wide dataframe

Question

I have data in this format:

Age            Gender Period  Unemployed
16 to 19 years Men   2005-01-01   91000
20 to 24 years Men   2005-01-01   175000
16 to 19 years Women 2005-01-01   88888
20 to 24 years Women 2005-01-01   175000
16 to 19 years Men   2005-02-01   92000
20 to 24 years Men   2005-02-01   175000

Now I want to convert it into wide format such as age is index:

                2005-01-01     2005-02-01
               Men     Women   Men   Women
Age
16 to 19 years
20 to 24 years

I tried using pivot:

df.pivot(index="Age", columns=["Period", "Gender"], value="Unemployed")

However I get error about duplicate values. But I dont know why should there be limitation for duplicate values here as I dont aggregate anything just reshape?

score 1 · Accepted Answer · answered Mar 24 '20 at 14:20

1

Use pivot_table setting both Period and Gender as columns:

df.pivot_table(index='Age', columns=['Period' ,'Gender'], values='Unemployed')

Period             2005-01-01         2005-02-01
Gender             Men   Women        Men
Age                                      
16 to 19 years      91000   88888      92000
20 to 24 years     175000  175000     175000

answered Mar 24 '20 at 14:20

yatu

86,083
12
84
139

Why is pivot not working for this case? – Alex T Mar 24 '20 at 14:22
1

Pivot does not accept multiple columns @AlexT – yatu Mar 24 '20 at 14:22
See [here](https://stackoverflow.com/questions/30960338/pandas-difference-between-pivot-and-pivot-table-why-is-only-pivot-table-workin) @alex (second answer) – yatu Mar 24 '20 at 14:23

Converting long format to wide dataframe

1 Answers1