Reshape rows to columns in pandas dataframe

Question

In pandas how to go from a:

a = pd.DataFrame({'foo': ['m', 'm', 'm', 's', 's', 's'],
                    'bar': [1, 2, 3, 4, 5, 6]})
>>> a
   bar foo
0    1   m
1    2   m
2    3   m
3    4   s
4    5   s
5    6   s

to b:

b = pd.DataFrame({'m': [1, 2, 3],
                    's': [4, 5, 6]})
>>> b
   m  s
0  1  4
1  2  5
2  3  6

I tried solutions in other answers, e.g. here and here but none seemed to do what I want.

Basically, I want to swap rows with columns and drop the index, but how to do it?

piRSquared · Accepted Answer · 2017-06-17 22:43:30Z

6

a.set_index(
    [a.groupby('foo').cumcount(), 'foo']
).bar.unstack()

answered Jun 17, 2017 at 22:43

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PedroA Over a year ago

can you detail a bit what's going on? I looked at the GroupBy.cumcount() documentation, but it's somewhat cryptic.

piRSquared Over a year ago

I apologize for the lack of detail. I'm on my phone. The problem with the information you have is that you need to differentiate between the different values with the same foo value. Cumcount does exactly that by creating a 0, 1, and 2 for the first 3 and the same again for the second three. What's more is that it will work if they weren't both of size 3. Given the positions I set the index at, it is laid out perfectly for an unstack.

BENY · Accepted Answer · 2017-06-18 00:03:07Z

4

This is my solution

a = pd.DataFrame({'foo': ['m', 'm', 'm', 's', 's', 's'],
                    'bar': [1, 2, 3, 4, 5, 6]})
a.pivot(columns='foo', values='bar').apply(lambda x: pd.Series(x.dropna().values))

foo    m    s
0    1.0  4.0
1    2.0  5.0
2    3.0  6.0

answered Jun 18, 2017 at 0:03

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Reshape rows to columns in pandas dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related