Group by and drop duplicates in pandas dataframe

user_8340881 0
Viewed 583

I have a pandas dataframe as below. I want to group by based on all the three columns and retain the group with the max of Col1.

import pandas as pd
df = pd.DataFrame({'col1':['A', 'A', 'A', 'A', 'B', 'B'], 'col2':['1', '1', '1', '1', '2', '3'], 'col3':['5', '5', '2', '2', '2', '3']})
df


  col1 col2 col3
0   A   1    5
1   A   1    5
2   A   1    2
3   A   1    2
4   B   2    2
5   B   3    3

My expected output

  col1 col2 col3
0   A   1    5
1   A   1    5
4   B   2    2
5   B   3    3

I tried below code, but it return me the last row of each group, instead I want to sort by col3 and keep the group with max col3

df.drop_duplicates(keep='last', subset=['col1','col2','col3'])


  col1  col2 col3
1   A   1     5
3   A   1     2
4   B   2     2
5   B   3     3

For Example: Here I want to drop 1st group because 2 < 5, so I want to keep the group with col3 as 5

df.sort_values(by=['col1', 'col2', 'col3'], ascending=False)
a_group = df.groupby(['col1', 'col2', 'col3'])
for name, group in a_group:
  group = group.reset_index(drop=True)
  print(group)

  col1 col2 col3
0    A    1    2
1    A    1    2
  col1 col2 col3
0    A    1    5
1    A    1    5
  col1 col2 col3
0    B    2    2
  col1 col2 col3
0    B    3    3
pandas python-3.x
4 Answers

You cant group on all columns since the col you wish to retain max for has different values. Instead dont include that column in the group and consider others:

col_to_max = 'col3'
i = df.columns ^ [col_to_max]
out = df[df[col_to_max] == df.groupby(list(i))[col_to_max].transform('max')]

print(out)

  col1 col2 col3
0    A    1    5
1    A    1    5
4    B    2    2
5    B    3    3
user_98406370

So we can do

out = df[df.col3==df.groupby(['col1','col2'])['col3'].transform('max')]
  col1 col2 col3
0    A    1    5
1    A    1    5
4    B    2    2
5    B    3    3
user_79645270

I believe you can use groupby with nlargest(2). Also make sure that your 'col3' is a numerical one.

>>> df['col3'] = df['col3'].astype(int)
>>> df.groupby(['col1','col2'])['col3'].nlargest(2).reset_index().drop('level_2',axis=1)

  col1 col2  col3
0    A    1     5
1    A    1     5
2    B    2     2
3    B    3     3
user_91673820

You can get index which doesn't has col3 max value and duplicated index and drop the intersection

ind = df.assign(max = df.groupby("col1")["col3"].transform("max")).query("max != col3").index
ind2 = df[df.duplicated(keep=False)].index

df.drop(set(ind).intersection(ind2))

  col1 col2 col3
0    A    1    5
1    A    1    5
4    B    2    2
5    B    3    3
user_112802890
Related