CODEWITHSUNDEEP
pythonpivotpandas-groupby
Vijay
Vijay

Reputation: 11

python pandas: pivot rows to columns based on duplicate index columns

I have a pandas dataframe with repeated column values as shown below

id,  key, qual, metric, value

abc  xyz  TRK    a      5528
abc  xyz  TRK    b      1234
abc  xyz  TRK    c      Error
abc  xyz  TRK    d      OK

I need the output as given below

id   key  qual  a     b     c     d

abc  xyz  TRK   5528  1234  Error  OK

I have tried using pivot but the result was dropping key and qual columns.

measures = measures.pivot(index='id',columns='metric',values='value')

Upvotes: 1

Views: 688

Answers (1)

Scott Boston
Scott Boston

Reputation: 153500

You can use some dataframe reshaping... However, I am a bit confuse by your values of b, c, and d in your expected output.

Try this using set_index and unstack:

df.set_index(df.columns[:-1].tolist())['value'].unstack().reset_index().rename_axis(None, axis=1)

Output:

    id  key qual     a     b     c     d
0  abc  xyz  TRK  5528  1234  3453  4544

Another way is to use pivot_table(will not work with string in value column):

df.pivot_table(columns='metric',index=['id','key','qual'])['value']

Upvotes: 1

Related Questions