CODEWITHSUNDEEP
pythonpandasdataframerows
Florian Bernard
Florian Bernard

Reputation: 323

Transform multiple rows into a single row using Pandas

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:

a b  c
1 10 20
1 30 40
1 60 70
1 80 100
2 10 20
2 60 70
2 80 100

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.

Upvotes: 1

Views: 1087

Answers (1)

BENY
BENY

Reputation: 323226

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

Upvotes: 2

Related Questions