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

Question

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

jezrael · Accepted Answer · 2020-08-31 11:14:30Z

1

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

edited Aug 31, 2020 at 11:14

answered Aug 31, 2020 at 11:00

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

6 Comments

Prachi Over a year ago

Thank you for responding but I am getting the error as 'No numeric types to aggregate' as the data type is string for values. what can i do about it?

jezrael Over a year ago

@Prachi - You can use df.D = df.D.astype('int') or some another solumn from this like pd.to_numeric

Prachi Over a year ago

ok, i did that. but my output doesnt look like how i want it. i actually got the column 'cc' twice and the values seperated too. i tried df.groupby(df.columns,axis=1).first() but it didnt work too.

jezrael Over a year ago

@Prachi - There are not some space like columns are 'cc' and ' cc' ? What is print (df.columns.tolist()) after my solution?

jezrael Over a year ago

@Prachi - Thank you, so problem is cc has space, so need before my solution df.C = df.C.str.strip() for convert ' cc' to 'cc'

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related