2

I have a CSV spreadsheet that contains some county names in Michigan and numerical data. It looks like this:

Allegan, 5, 4.51

Alpena, 1, 3.44

Antrim 2, 8.64

Barry 1, 1.69

Etc.

I am trying to make it into a choropleth. I went through the tutorials on CartoDB which allowed me to make a choropleth out of data they gave me that already had geometric data for all US counties (including Michigan counties!). My CSV does not have the geometric data. I'm very new to this. I'm not sure how to proceed. I'm assuming either I

  1. extract the Michigan county data from the US county data and link it to my dataset (no idea how to do this)
  2. Hand draw all 83 Michigan country maps myself (seems redundant but willing to do it if this is the only way)
  3. Something else?
1

In case someone else is trying to do this, here is how I eventually tackled the problem:

  1. I downloaded the US Counties data from CartoDB (as a CSV file).

  2. I opened my CSV file in Excel, and added the US Counties data as a separate worksheet.

  3. Using VLOOKUP in Excel, I was able to pull the the_geom data from the US Counties file by matching the County name in my own data. (tutorial here: http://blog.contextures.com/archives/2010/08/23/excel-vlookup-from-another-workbook/)

  4. A few counties I had to change manually, because county names are not unique across the country. So I had to pick the correct Clinton county, for example, which I did by population size.

  5. When they were all in the same file (geometric data and my data) I uploaded it to CartoDB and was able to create a visualization using CartoDB's wizards

1

If you have the Michigan counties loaded from the CartoDB tutorial still, you can join the two tables and grab the information you're needing from both.

You can type custom SQL into the tray on the right hand side of any map or dataset. See the CartoDB documentation for more information.

Something like the following should work:

SELECT
  c.the_geom,
  m.county_name,
  m.value1,
  m.value2
FROM
  your_michigan_table As m
 LEFT JOIN
  your_cartodb_tutorial_table_of_counties As c
 ON
  c.county_name = m.county_name

Be sure to replace the tables with the correct names, and associate the county name columns correctly for the two tables. And value1 and value2 should be replaced with the names in your table for the numeric columns you listed in your question.

Once successful with this, you can click "Create Table from Query" to produce a new table. Good luck!

  • where would i type those commands? i only see a graphical user interface for cartoDB – Christopher Jan 20 '16 at 0:50
  • Just updated answer! – Andy Eschbacher Jan 20 '16 at 2:43
0

You need to extract the Michigan county data from the US county data and link it to my dataset. You have to use GIS software to join those data using the name field (QGIS is a good option). here is a tutorial http://www.qgistutorials.com/en/docs/performing_table_joins.html

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.