I have the following dataframe:
ID KEY TYPE FIELD VALUE
0 MYID 112233 1 Height 117
1 MYID 112233 1 Weight 17
2 MYID 445566 1 Height 95
3 MYID 445566 1 Weight 39
4 MYID 112233 2 Height 118
5 MYID 112233 2 Weight 17
This has duplicate KEY
values, and I need to keep the ones with the highest TYPE
. The intended result is:
KEY TYPE Height Weight
0 112233 2 118 17
1 445566 1 95 39
I tried accomplishing this using pd.pivot(index='KEY', columns='FIELD')['VALUE']
but it complains because I have duplicate values in KEY
. I also tried using pd.pivot_table()
but this is inadequate for this problem as I don't have to aggregate data, just show them.
How could I solve this?