Identify duplicate entries in pandas Dataframe using group by and (multiple columns comparison)

user_13866248 0
Viewed 64

I have the following data frame:

dct = { 'inc_num' :[101,102,103,104,105,106,107,108,109,110],
        'store' : ['A','A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
        'part1' : ['oil','oil', 'filter', 'window', 'mirror', 'filter', 'oil', 'coolant', 'wiper', 'oil'],
        'part2' : ['light','shaft', np.nan, 'grease', 'oil', 'gas', np.nan, 'wiper', 'oil', 'filter'],
        'part3' : ['filter',np.nan, np.nan, np.nan, 'filter', np.nan, np.nan, 'filter', np.nan, np.nan], 
        'part4' : [np.nan,np.nan, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan, 'light']
       }


df = pd.DataFrame(dct)


print (df)

inc_num     store  part1   part2   part3     part4
101          A      oil     light   filter   nan  
102          A      oil     shaft   nan      nan  
103          A      filter  nan     nan      nan  
104          A      window  grease  nan      nan  
105          B      mirror  oil     filter   nan  
106          B      filter  gas     nan      nan  
107          B      oil    nan      nan      nan  
108          C      coolant wiper   filter   nan 
109          C      wiper   oil     nan      nan 
110          C      oil     filter  nan      light

I have to identify duplicate 'parts' entry group by Store, irrespective of order, and if at least one entry also found, mark latest as "True" and all other as False.

Also, the Reference number is expected, but NOT must at this point.

and my expected Dataframe is

inc_num     store  part1   part2   part3     part4   duplicate  reference
101          A      oil     light   filter   nan     True       102 (or) 103      // oil is duplicated in 102; filter in 103
102          A      oil     shaft   nan      nan     True       
103          A      filter  nan     nan      nan     False       
104          A      window  grease  nan      nan     False
105          B      mirror  oil     filter   nan     True       106        // filter duplicated in 106
106          B      filter  gas     nan      nan     False      
107          B      oil    nan      nan      nan     False      
108          C      coolant wiper   filter   nan     True       109(or)110 //wiper in 109, filter in 110
109          C      wiper   oil     nan      nan     True       110        //oil is duplicated in 110
110          C      oil     filter  nan      light   False

I have also provided inline comment for the clarification for a single column duplicate, I was using Dataframe.duplicated(sunset=[colX,colY], keep='last') But not sure how to handle multiple column comparison.

To add original ticket incident I have been using below code:

s = df.duplicated(['colX','colY'],keep=False)
df.loc[s,'reference'] = df.groupby([s,'store','colX'])['inc_num'].transform('max')

Your help is greatly appreciated.

pandas dataframe duplicates numpy python
1 Answers

You can use pd.melt to bring the three part columns into one column and then look for duplicates by store and part:

df = df.melt(id_vars=['inc_num', 'store'], value_vars=['part1', 'part2', 'part3'])

df['duplicated'] = df.duplicated(['store','value'],keep=False)

This will get you all the (store,part) duplicates.

user_43374020
Related