3

I've run into a bit of an issue. I recently pulled data from the CDC website concerning Suicide rates by county. However, the csv file does not have locational data. Luckily, my project is about the correlation between suicide rate and % of abandoned homes in the nation (by county).

The abandoned homes shapefile does have locational data and I wanted to do a table join based on county name or county code; however, for both instances, the data within the field is just a little different.

For instance, Autauga County, AL versus Autauga County, Alabama. As you can see, the names almost align. The default method of joining the fields based on county names brings up a lot of "Null" values.

|improve this question
  • 2
    Joining will only occur between exact matches. You must make the fields match exactly, which will take some work. Depending on the database the join may also be case sensitive. Perhaps it would be sufficient to load the table data in Excel, sort by location then fill with the value that's in the vector data; if the data is consistently wrong then it may be better to change the value in the vector once rather than trying to manipulate many rows in the table. If it's all the same left of the comma then there may be a quicker way, is this the case? – Michael Stimson Feb 20 '15 at 1:56
  • Thanks everyone for the quick responses! All of them are great! I'll just pop into excel and get down to business! Michael, yes, everything left of the comma. I thought I might see if there was a short cut or perhaps work-around for future references. – George Feb 20 '15 at 2:15
2

I would recommend creating a two field lookup table that maps the county names from the shapefile to those in the table which will hopefully be a one to one match.

Then you can join that onto your shapefile so that it has an additional field with the county names from the table that you can use to join that on.

If there are just a few rules that you can use to convert the county names seen in the shapefile to those in the table then using the Python Parser of the Field Calculator or an ArcPy cursor could be an alternative.

|improve this answer
  • Add another field to both and calc with python using string.split(!field!,",")[0] to extract everything left of the comma into the new field then join by that. – Michael Stimson Feb 20 '15 at 2:18
  • @MichaelMiles-Stimson Unfortunately, US county names are not unique so I think the county-state combination will have to stay. – PolyGeo♦ Feb 20 '15 at 2:43
  • fair enough, I'm not familiar with U.S. States and Counties - it would have been a simple solution if Counties were unique. – Michael Stimson Feb 20 '15 at 3:22
2

You'll have to correct the state values to match exactly.

I'd suggest taking the table into excel and separating the columns. Or using Excel's Find and Replace tool.

|improve this answer
  • ArcMap also has Find & Replace, but may require you to start editing first. – Michael Stimson Feb 20 '15 at 2:19
  • True. I always find it cumbersome to use though, so I always take things into Excel first and then bring in the CSVs – BDD Feb 20 '15 at 2:24
  • 1
    I find text/Excel data flaky, so I always go Excel->Personal Geodatabase (using Microsoft Access) when bringing it back to ArcMap.. for me that's enough mucking around to put up with the cumbersome Find & Replace on the attribute table; I admit though it's a personal preference and either way gets the result in the end. – Michael Stimson Feb 20 '15 at 2:29
0

As an alternative/expansion to the other two answers: Open the attribute table of the shapefile. From the attribute table window export the table to a csv file. Open the csv in Excel and sort it on the county name field (you can delete all other columns if you like). Open your original Excel file and sort it on county name. Copy and paste the column from the csv into your spreadsheet. The two county name columns should match up in terms of the actual county name, but it might be worth a quick review/scan down the columns to make sure nothing is out of order or lacking a match.

This differs a bit from BDD's solution in that it doesn't require splitting the contents of the column (though having separate columns for county and state is probably better data organization practice). Once you've saved the Excel, you should have two (or delete the original) county columns, one of which you can join on.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.