0

My data Frame 1 looks like:

   Col1 Col2 Col3
1   A    4    ab
2   A    5    de
3   A    2    ah
4   B    1    ac
5   B    3    jd
6   B    2    am

data frame 2:

  col1 col2
1  A    4
2  B    3

How do i delete all the rows in Data Frame 1 which do not match the combination of rows of dataframe 2?

Output Expected:

   Col1 Col2 Col3
1   A    4    ab
2   B    3    jd
Akshay Bharadwaj
  • 85
  • 1
  • 12
  • 1
    inner merge on `col1` and `col2` using `df1.merge(df2,on=['col1','col2'])` ? – anky Jan 28 '20 at 07:34
  • Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) – ansev Jan 28 '20 at 07:58

2 Answers2

1

Use DataFrame.merge with inner join, only necessary rename columns:

df = df2.rename(columns={'col1':'Col1','col2':'Col2'}).merge(df1, on=['Col1','Col2'])
#on should be omited, then merge by intersection of columns of df1, df2
#df = df2.rename(columns={'col1':'Col1','col2':'Col2'}).merge(df1)

print (df)
  Col1  Col2 Col3
0    A     4   ab
1    B     3   jd

Another idea is use left_on and right_on parameter and then remove columns with names by df2.columns:

df = (df2.merge(df1, left_on=['col1','col2'], 
                     right_on=['Col1','Col2']).drop(df2.columns, axis=1))
print (df)
  Col1  Col2 Col3
0    A     4   ab
1    B     3   jd

If columns names are same:

print (df2)
  Col1  Col2
1    A     4
2    B     3

df = df2.merge(df1, on=['Col1','Col2'])
#df = df2.merge(df1)
print (df)
  Col1  Col2 Col3
0    A     4   ab
1    B     3   jd
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
0

You can also use join, to do an inner join

dfR = df1.join( df ,on=['Col1','Col2'] ,how='inner',rsuffix='_x')
dfR[['Col1','Col2','Col3']]

This will also give you the same result

   Col1 Col2 Col3
1   A    4    ab
2   B    3    jd

For more details check these links Join Documentation and examples

Andy_101
  • 1,246
  • 10
  • 20