CODEWITHSUNDEEP
pythonpandasdataframe
Prachi
Prachi

Reputation: 75

How to convert values of one column into column headers and other column values into rows?

This is how my df looks like.

    A      B     C      D      E      F
    xyz   abc    aa    100    qq     brc,pqr,lmn
    xyz   abc    bb    150    qq     lmn,brc,ppq
    xyz   abc    cc    80     qq     lmn,pqr
    abc   pqr    cc    99     qq     pqr,brc,lmn
    abc   pqr    aa    180    qq     brc,lmn,pqr
    abc   pqr    bb    200    qq     lmn,pqr,brc

this is what i want. how can i achieve the same?

A      B     aa     bb    cc      E      F
xyz   abc    100   150    80      qq     brc,pqr,lmn,ppq
abc   pqr    180   200    99      qq     brc,lmn,pqr

Upvotes: 1

Views: 58

Answers (1)

jezrael
jezrael

Reputation: 863781

Use DataFrame.pivot_table with DataFrame.join for another DataFrame created by GroupBy.agg:

f = lambda x: ','.join(set(z for y in x for z in y.split(',')))
df1 = df.groupby(['A','B']).agg({'E':'first', 'F': f})

df = df.pivot_table(index=['A','B'],
                    columns='C',
                    values='D',
                    aggfunc='mean').join(df1).reset_index()
print (df)
     A    B   aa   bb  cc   E                F
0  abc  pqr  180  200  99  qq      lmn,brc,pqr
1  xyz  abc  100  150  80  qq  lmn,ppq,brc,pqr

Upvotes: 1

Related Questions