Python - Drop Duplicate Based On Max Value Of A Column

I am not really good with pandas, and I think pandas should solve my problem: I have a text file, that contains data (id1;id2;value1;value2;value3) 1;2;30;40;20.3; 1;2;30;42;26.2;

Solution 1:

You need DataFrameGroupBy.idxmax for indexes of max value of value3 and thes select DataFrame by loc:

print (df.groupby(['id1','id2','value1']).value3.idxmax())
id1  id2  value1
1    2    30        1
3    5    12        4
24   12   1         6
Name: value3, dtype: int64

df = df.loc[df.groupby(['id1','id2','value1']).value3.idxmax()]
print (df)
   id1  id2  value1  value2  value3   a
1    1    2      30      42    26.2 NaN
4    3    5      12      33    11.2 NaN
6   24   12       1      23     1.9 NaN

Another possible solution is sort_values by column value3 and then groupby with GroupBy.first:

df = df.sort_values('value3', ascending=False)
       .groupby(['id1','id2','value1'], sort=False)
       .first()
       .reset_index()
print (df)
   id1  id2  value1  value2  value3   a
0    1    2      30      42    26.2 NaN
1    3    5      12      33    11.2 NaN
2   24   12       1      23     1.9 NaN