Pandas - use pivot_table to convert each unique element in 1 column into a unique column

Question

I am trying to expand a dataframe such that for all the unique elements in the rows of one column, each value becomes a column in its own right.

I start with a dataframe that looks like this.

     Colour Age    Type  Count
0    Black  11yrs  Cats  22
1    Black  12yrs  Cats  2
2    White  8yrs   Dogs  10
3    Brown  11yrs  Dogs  2
4    White  12yrs  Cats  14

I would like to change the dataframe, such that the columns are the unique elements of Colour column, Black, White, Brown - so that it looks like this:

    Age    Type   Black  White Brown 
0   11yrs  Cats   22     0     0   
1   12yrs  Cats   2      14     0
2   8yrs  Dogs    0      10    0
3   11yrs  Dogs   0      0     2

I've tried a few approaches but clearly I am missing something.

Any help appreciated.

score 2 · Accepted Answer · answered Sep 18 '19 at 23:00

You may use pivot_table as follows:

(df.pivot_table(index=['Age', 'Type'], 
                columns='Colour', 
                values='Count', 
                fill_value=0).reset_index())

Out[22]:
Colour    Age  Type  Black  Brown  White
0       11yrs  Cats     22      0      0
1       11yrs  Dogs      0      2      0
2       12yrs  Cats      2      0     14
3        8yrs  Dogs      0      0     10

Or set_index and unstack

(df.set_index(['Age', 'Type', 'Colour']).Count.unstack(fill_value=0)
   .reset_index())

Out[23]:
Colour    Age  Type  Black  Brown  White
0       11yrs  Cats     22      0      0
1       11yrs  Dogs      0      2      0
2       12yrs  Cats      2      0     14
3        8yrs  Dogs      0      0     10

Pandas - use pivot_table to convert each unique element in 1 column into a unique column

1 Answers1