Transform multiple rows into a single row using Pandas

Question

I'm sure this is fairly easy to do but I can't figure it out. I'm coding in python and using pandas to work on dataframes. My dataframe looks like this:

And I would like to transform it like that:

a b1 c1 b2 c2 b3 c3 b4 c4
1 10 20 30 40 60 70 80 100
2 10 20 60 70 80 100 NA NA

Basically for every rows where the value in a is the same, take values in b and c and transform them into new columns, so that I end up with only one row for every value in a.

I hope I'm clear enough, if not don't hesitate to tell me. Thanks in advance for any help provided !

Florian.

BENY · Accepted Answer · 2018-04-29 15:16:56Z

2

We need create a help key here, by using cumcount

s=df.assign(key=df.groupby('a').cumcount()+1).set_index(['a','key']).stack().unstack([1,2])
s.columns=s.columns.map('{0[1]}{0[0]}'.format)
s
Out[396]: 
     b1    c1    b2    c2    b3     c3    b4     c4
a                                                  
1  10.0  20.0  30.0  40.0  60.0   70.0  80.0  100.0
2  10.0  20.0  60.0  70.0  80.0  100.0   NaN    NaN

answered Apr 29, 2018 at 15:16

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

Transform multiple rows into a single row using Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related