1

[Sample Code]

d = {
   'country': ['IN', 'USA', 'USA', 'IN'],
   'username': ['abi.g', 'pugal.g', 'jan.g', 'jacob.h'],
   'email': ['abi@gmail.com', 'pugal.g@yahoo.in', 'jan232@gmail.com', 'jacob@hoi.com'],
   'ClusterID': ['', '4', '5', '9']
}
df1 = pd.DataFrame(d)

data = [
   ['USA', 3490.89, 'qcx_taskid85_duns250437449', '3'], 
   ['JA', 1211, 'Pugal Gandi', '4'], 
   ['USA', 3455.00, 'Janani Khannan', '6']
]
df2 = pd.DataFrame(data, columns=['country', 'salary', 'name', 'ClusterID'])

df1.reset_index(inplace=True, drop=True)
df2.reset_index(inplace=True, drop=True)
df1.loc[df1['ClusterID'] == df2['ClusterID']]

enter image description here enter image description here

Qns: How to compare/filter the two columns using .loc, while the no. of records are different in pandas?

Thanks,

Jai K
  • 375
  • 1
  • 4
  • 12
  • are you looking for `df1[df1['ClusterID'].isin(df2['ClusterID'])]` or `df1.merge(df2,on='ClusterID',how='outer',indicator=True,suffixes=('','_y')).query("_merge=='both'")[df1.columns]` but the first one is better and readable – anky Jan 09 '21 at 08:51
  • @anky Thanks for the solution. Here, I have two other doubts.. A). `.isin()` only works for an `exact match`.. Is there any way that we can do for `partial search` (e.g partial string match, instead of number?)? B). The direct condition on `.loc(df1==df2)` will works only if the total no. of rows on both datasets are equal? – Jai K Jan 09 '21 at 09:14
  • check this https://stackoverflow.com/questions/56521625/quicker-way-to-perform-fuzzy-string-match-in-pandas#56521804 – anky Jan 09 '21 at 09:18

1 Answers1

1

Try this >>

df1.loc[df1['ClusterID'].isin(df2['ClusterID'])]

Both df1['ClusterID'] & df2['ClusterID'] are series. Equating two series within loc method will throw the exception. Instead, calling isin method will give bool outputs and hence for all True values you will get the corresponding output with loc method.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Sarang
  • 13
  • 3