Finding string with multiple condition between two data frame in python-CodePudding

I have two dataframe df1 and df2. df1 has 4 columns.

>df1
Neighborhood  Street  Begin Street  End Street
8th Ave       6th St  Church St     Mlk blvd
.....

>df2
Intersection  Roadway
Mlk blvd      Hue St.

I want to add a new column Count in df2 in such a way that for every row in df2 if any string from Intersection or Roadway column exists in overall df1 data frame even once or more, the count column will have a value of 1. For example for this sample dataframe df2 as Mlk blvd is found in df1 under End Street column : the df2 will look like :

>df2
Intersection  Roadway   Count
Mlk blvd      Hue St.    1

I also wanted to strip the string and make it case neutral to match it. However, I am not sure how would I set this matching logic using .iloc . How could I solve this?

CodePudding user response：

Flatten the values in df1 and map to lower case, then convert the values in df2 to lower case and use isin any to test for the match

vals = map(str.lower, df1.values.ravel())
df2['count'] = df2.applymap(str.lower).isin(vals).any(1).astype(int)

  Intersection  Roadway  count
0     Mlk blvd  Hue St.      1