Pandas frame rotation when deduplicating additional columns

Question

As in the example, I want to collapse the following data framework:

  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

Is it possible to do this shorter than

• turn off an extra column before turning
• deduplication separately
• merging it with rotary data?

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

Here is the code for the data file:

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]})

Answer 1

Use set_indexandunstack

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

Answer 2

You can use it pivot_table, it pivottakes only one column as an index, column and value, and it pivot_tablecan take several 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