I have a data table with columns of county and state names as well as FIPS codes for 1990 U.S. counties.

I cannot easily merge shape data into the data (e.g. the Census ones), but CartoDB does not recognize the "administrative regions" being these counties at all when I ask to georeference this data.

Is there a way around this?

  • did you try using the cartodb merge command to join your csv data on 1990 census county shapefiles using the FIPS code? – clhenrick Dec 6 '14 at 22:46
  • @chrishenrick Thanks, but I'm confused. The Census shape files I linked to have the three mandatory files, shp, shx and and dbf. Which one shall I import into a CartoDB table, then, to merge later? The main data only has the FIPS identifier (other than county-wide data to map). – László Dec 6 '14 at 23:36
  • see my answer below – clhenrick Dec 7 '14 at 1:04

Follow these steps:

  1. Download a us counties shapefile. Here's one that will work.

  2. Import the zipped UScounties file into CartoDB as a new table.

  3. Change the fips column's data type to number. (click on the word 'string' right below 'fips' and select number).

  4. Click the merge tables button on the right side of the tool bar when you are in data view.

  5. Choose 'column join' as the merge type.

  6. Select the fips column for the U.S. Counties and the county_fips_code column from your CSV file.

This worked for me and I'm including screen shots below. You can also follow this tutorial from CartoDB on how to do a merge using their software.

enter image description here enter image description here enter image description here enter image description here

  • This is wonderful, thank you — I have an unrelated question though: some counties have no estimates here, but missing data is simply part of the lowest bucket on the chloropeth here. Is that easy to switch off and call a bucket of its own (say with transparent/no coloring?)? – László Dec 7 '14 at 2:36
  • 1
    You can either use SQL to exclude all values that are null / zero or alter the lowest bucket in the cartoCSS to correspond to the minimum value of your data. – clhenrick Dec 7 '14 at 13:04

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.