0

I have two data frames as below:

df1
  City   State   Year
Modesto    CA    2000
Modesto    CA    2001
Anchorage  AK    1999
Anchorage  AK    2000
Greensboro NC    1998
Greensboro NC    1999
Las Vegas  NV    2001
Fort Worth TX    2002
df2
              Agency               State  Year  
Pasadena Police Dept                CA    1999
Pasadena Police Dept                CA    2000
Las Vegas Metropolitan Police Dpt   NV    2001
Modesto Police                      CA    2000
Modesto Police                      CA    2001
City of Fort Worth Police Dept      TX    2002

I need to merge them on all 3 columns, the issue is searching through the Agency column for matches to the city column.

For this example this is what my output would look like:

Output
              Agency                City      State  Year  
Modesto Police                     Modesto     CA    2000
Modesto Police                     Modesto     CA    2001
Las Vegas Metropolitan Police Dpt  Las Vegas   NV    2001
City of Fort Worth Police Dept     Fort Worth  TX    2002

As you can see the verbiage of the Agency column is not consistent with how the departments are titled as well as the position of the city name in the string.

I've tried using variations of grep, but have been unsuccessful in applying it when also matching the state and year columns. There are also other columns that I'd be pulling in the merge, but these are the relevant ones to be joined on.

0 Answers0