Pandas: Retain the a particular row based on weightage given on a particular column, if duplicates are present based on other columns after groupby

user_12883297 0
Viewed 66

I have a data frame df

df = pd.DataFrame([["A","X",98,56,61], ["B","E",79,54,36], ["A","Y",98,56,61],["B","F",79,54,36], ["A","Z",98,56,61], ["A","W",48,51,85],["B","G",44,57,86],["B","H",79,54,36]], columns=["id","class","c1","c2","c3"])

when we do groupby on id, if duplicate values(rows) are present based on multiple columns like c1,c2,c3, retain the row based on weighatge given on column class.

For example here when we do groupby on id A, c1,c2,c3 are duplicates for class X,Y,Z, among X,Y,Z weighatge given to X so retain X and delete other rows, similarly among E,F,H weightage given to F, so retain F and delete other rows.

Expected Output:

output = pd.DataFrame([["A","X",98,56,61],["B","F",79,54,36],["A","W",48,51,85],["B","G",44,57,86]], columns=["id","class","c1","c2","c3"])

How to do it?

pandas python-2.7 dataframe python-3.x python
1 Answers

Based on your explanation, you can create a dictionary of the weightage and then create 2 conditions and then do:

#add classes for weightage incase of duplicates
cls = ['X','F']
c = df.duplicated(['id','c1','c2','c3'],keep=False) 
out = df[(c&df['class'].isin(cls))|~c]

print(out)

  id class  c1  c2  c3
0  A     X  98  56  61
3  B     F  79  54  36
5  A     W  48  51  85
6  B     G  44  57  86
user_98406370
Related