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?
Advertisement
Answer
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