CODEWITHSUNDEEP
pythonpandas
Robert Pollak
Robert Pollak

Reputation: 4165

Pivoting a Pandas dataframe while deduplicating additional columns

Similar to the documentation example, I want to pivot the following dataframe:

  foo extra bar  baz
0 one     x   A    1
1 one     x   B    2
2 one     x   C    3
3 two     y   A    4
4 two     y   B    5
5 two     y   C    6

The result should be

     extra A  B  C

one      x 1  2  3
two      y 4  5  6

Can this be done in a shorter way than

(I expected the pivot command to be able to do this, but my tries failed.)

Here's the code for the dataframe to play with it:

df = pd.DataFrame({'foo': ['one','one','one','two','two','two'],
                   'extra': ['x','x','x','y','y','y'],
                   'bar': ['A', 'B', 'C', 'A', 'B', 'C'],
                   'baz': [1, 2, 3, 4, 5, 6]})

Upvotes: 4

Views: 45

Answers (2)

Zero
Zero

Reputation: 76917

Use set_index and unstack

In [2087]: df.set_index(['foo', 'extra', 'bar'])['baz'].unstack().reset_index()
Out[2087]:
bar  foo extra  A  B  C
0    one     x  1  2  3
1    two     y  4  5  6

Upvotes: 1

akuiper
akuiper

Reputation: 214957

You can use pivot_table, pivot only accepts one column as index, column and value while pivot_table can accept multiple columns:

df.pivot_table('baz', ['foo', 'extra'], 'bar').reset_index()

#bar    foo extra   A   B   C
#  0    one     x   1   2   3
#  1    two     y   4   5   6

Upvotes: 3

Related Questions