Based on whichever string/category appears first in a column, retain that row and drop other rows in pandas

user_12883297 0
Viewed 33

I have a dataframe

df = pd.DataFrame([["A","X",1], ["B","W",0.9], ["B","X",0.8],
                   ["A","W",0.7], ["B","Z",8], ["B","Y",48],
                   ["A","Y",98],["A","Z",56]], columns=["id","key","val"])

After groupby on id column, in column key, among W and X whichever appears first retain that row and drop the other, similarly among Y and Z retain the row based on whichever appears first and drop the other after groupby/common id.

Expected output:

df_out = pd.DataFrame([["A","X",1], ["B","W",0.9], ["B","Z",8],
                       ["A","Y",98]], columns=["id","key","val"])

How to do it?

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

Use DataFrame.replace for same 'group's and pass to DataFrame.duplicated for get first duplciates by both columns (so per id groups):

df = df[~df.replace({'key':{'X':'W', 'Y':'Z'}}).duplicated(['id','key'])]
print (df)
  id key   val
0  A   X   1.0
1  B   W   0.9
4  B   Z   8.0
6  A   Y  98.0
user_29010020
Related