Has anyone figured out how to Map US Census FIPS codes

Options
tgb417
tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron

I'm looking at some NYC Times COVID-19 data that provides US Census FIPS codes.

https://github.com/nytimes/covid-19-data

I'd like to Map this data using DSS.

It has a FIPS code. No GeoPoints of Lat Lon.

SO it looks like I have to do some Geo Location work here.

It looks like the Census might have an API to geocode the FIPS codes.

I see this discussion. https://www.dataiku.com/product/plugins/us-census/

Has anyone worked this out.

Is there a Python or R library that would make this easy?

Best Answer

  • seanmcg23
    seanmcg23 Dataiker Alumni Posts: 1 ✭✭✭✭
    Answer ✓
    Options

    Hi Tom,

    I see that you posted this a while ago but, I just became aware of it. I also used the NYTimes git data to create a few dashboards on the number of deaths and infections from COVID-19 as well as the infection and death rates per 100,000 population. I just recently added a few dashboards using the "2019_Gaz_counties_national.txt" file from Gazetteer Files to provide the lat/long that I used to create geopoints.

    The link between the NYTimes data and the gazetteer file is the fips code but, it's not readily apparent. The fips code is buried in gazetteer file as the first five digits of the "GEOID" column. The first two digits are the state, the next three are the county and the last six dig deeper into cities, hamlets, and smaller entities. Because the the filled map only requires any point within the county, I selected the last lat/lon associated with the fips code and used that to create the insights for the dashboard. I attached a screenshot of the California insights below.

    The data is updated from the NYTimes git daily.

    CaliforniaDashboard20200602.png

    If you're still working on this and have questions, let me know, happy to help.

    --Sean

Answers

  • nicog
    nicog Dataiker, Alpha Tester, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner Posts: 6 Dataiker
    Options

    Hi Tom,

    The US Census plugin won't be useful in your context since it enriches data with US Census data not performing reverse geocoding. I believe what you are looking for is here in in the gazetteer files, spot the US Counties : https://www.census.gov/geographies/reference-files/time-series/geo/gazetteer-files.html

    Best

    Nicolas.

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    Options

    Thanks for the suggestion.

    In looking at this several times, I've not found a connection between the fips, codes in the NY Time data and the census data that tells something about the location of the areas.

    Maybe I don't really know what a fips code is. (in the case of the NY times data this looks like it is somehow connected to a county.

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    Options

    @seanmcg23

    Thanks so much for this insight. The Census has some really interesting data. However, they DO NOT make it easy for one to figure out how to gather and use this data.

    Thanks for part of the rosetta stone.

    --Tom

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    Options

    @seanmcg23

    Here is what I've been able to do with this data.

    NYC Times Covid-19 Case Count.jpg

    “Data from The New York Times, based on reports from state and local health agencies.”

    https://www.nytimes.com/interactive/2020/us/coronavirus-us-cases.html.

  • nicog
    nicog Dataiker, Alpha Tester, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner Posts: 6 Dataiker
    Options
  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    Options

    After a bit of work. I've been able to get this animation pulled together on New Cases of COVID-19 by the week of the year. This is at the US county level of data. (NYC's 5 counties are not part of this visualization.)

    COVID-19-2020-Week-4---23.gif

    It's clear that this thing is not over.

    The next step would be to get county population and present the numbers as cases per 100,000 inhabitants.

    --Tom

    “Data from The New York Times, based on reports from state and local health agencies.”

    https://www.nytimes.com/interactive/2020/us/coronavirus-us-cases.html.

Setup Info
    Tags
      Help me…