Data drop duplicates data complex

user_13765644 0
Viewed 121
import pandas as pd

search = pd.DataFrame({
  "Code": ["BB", "BB", "CC", "CC", "CC", "DD", "DD"],
  "N1": [22, 20, 33, 30, 31, 44, 40]
})

confirmed = pd.DataFrame({'N2': [30, 40, 50]})

Hi dear, I have a question while learning Python/pandas for data analysis. I have two dataframes above, I wish to search the data value in the comfirmed df, and if the value matches/appears in the first df (search), then we keep that value and its corresponding "Code" and delete the other same Code value. For example, 30 in the "confirmed" df, and it also appears in the "search" dataframe, so we keep value 30 and its code "CC", meanwhile we delete the other "CC"s and values (33, 31). Same for the others.

In the end, the "search" dataframe should look like this:

  Code  N1
0   BB  22
1   BB  20
2   CC  30
3   DD  40

It is quite complex question for me as a Python data rookie, so if anyone has any ideas, please help. Great thanks.

pandas dataframe duplicates python
2 Answers

Do with transform after isin check 

s=search.N1.isin(confirmed.N2)
m=(~s).groupby(search['Code']).transform('all') | s
out=search[m]
out
  Code  N1
0   BB  22
1   BB  20
3   CC  30
6   DD  40
user_79645270

Similar to Yorben_s' answer, but with np.where as well:

df = search.copy()
df['flag'] = np.where((df['N1'].isin(comfirmed['N2'])), 1, 0)
df['flag'] = np.where((df.groupby('Code')['flag'].transform(sum) == 0), 1, df['flag'])
df = df.loc[df['flag'] == 1].drop('flag', axis=1)
df
user_63667700
Related