Can I specify data to match in joins?

Question

I have students working on projects in ArcGIS Online, and they are looking to join two tables together: a Public High Schools table, and a State Championships table. The problem is that in the Public High Schools table, schools have official names [i.e. Johnson County Senior High School], whereas the State Championships table has common names [i.e. Johnson County].

Is there a way to specify which schools in Table A match which schools in Table B [i.e. telling ArcGIS that Johnson County Senior High School = Johnson County]?

I'm trying to have the student avoid the painstaking data cleaning of correcting the names to perfectly match. Normally I'd be good to have them experience the unfun process of tirelessly working in spreadsheets, but we're running out of time in the semester.

AmandaRing · Answer

Unfortunately, the join requires the field values to be an exact match in order for them to be joined.

Therefore, as previously shared, your best options are to either create a new ID field for each of them, or to make one of them match the others.

If all the records share the correct start to the school name, sorting alphabetically and then autopopulating an ID column with 1 to n could work.
Another option might be to use a find and replace with a wildcard search in Table B to replace all the extra bits with nothing
Ex. Find "Senior*" replace with "" to leave you with "Johnson County"

Good luck!

LauraGosnold · Answer

You can create a new column in both tables and give the matching schools a number or code.  This would give you the common denominator you need to merge the files.

If it were me personally, I would change the name  of the schools in one of the tables to match the other, simply because it would drive me nuts otherwise.  However, I understand the semester is closing in!

MarieDawes · Answer

People more experienced than me may have a better way. But one thing to consider is creating a new field to ID each school (i.e. SchoolID). You could populate the SchoolID field with the field calculator, assigning a number (or text code) to each school. Thus in the High Schools table Johnson County Senior High SchoolID = JCSH and in the State Championship table Johnson County also has a SchoolID = JCSH. It's not effortless, but shouldn't take too long (unless you have thousands of schools).

On the other hand, there may be some Python code you could write to convert the names in one table to match those in the other. I'm curious what others here think.