Joining on columns with duplicate values - clean before merging or after merging?

Question

I am joining on two data sets on a column which has duplicated values in both datasets. Is it better practice to remove the duplicates and make the values I am joining on a primary key in both datasets before joining the two, or is it okay to first merge the two data sets, then make the joined column the primary key using something like .groupby()?

E.g:

A = pd.DataFrame({'KEY' : ['abc', 'abc', '123', 'wyz'],
'WEIGHT' : [5, 7, 13, 10]
})

B = pd.DataFrame({'KEY': ['abc', '123', '123', 'def'],
'TITLE' : ['cat', 'dog', 'dog', 'elephant'] 
})

# join first then clean
C = pd.merge(A,B, how='inner', on='KEY')
C = C.groupby('KEY', as_index=False).agg(funcs) # mean for VALUE, first for TITLE
# versus clean then joining
A = A.groupby('KEY', as_index=False).mean()
B = B.groupby('KEY', as_index=False).first()
C = pd.merge(A,B, how='inner', on='KEY')
```

Answer 1

With small datasets it doesn't matter, but for large datasets it is always better to remove duplicates before joining, just for efficiency. There is usually an increase in CPU time when you are joining larger datasets with duplicates. This is magnified for very large datasets. But, in an opposite sense, sometimes joining without first removing the duplicates also helps with identifying join problems if the resulting output does NOT contain EXACT duplicate. e.g Sometimes a row contains a column you may not be interested in, which is revealed AFTER you do the join and can thus generate additional rows. I have discovered hidden variables in some of my data which i didn't realize changed by seeing duplicates in the output. That can help with refining your join by including (or eliminating) the column, and can help your model.

in practice we usually join 1 or 2 keys. So it is always a good idea to do a count of primary keys in the input and output data to make sure you are getting what you want.

Answer 2

This is similar to the discussion here. So, it depends. You just need to aim for readability. I would argue for the cleaning-first approach however, so that the merging operation is done on smaller datasets.