How to convert from BLS to FIPS county codes

Photo by J Giacomoni

Don't be deceived by the whole Facebook brouhaha, that was an anomaly. I normally lead an extremely boring life, and quite like it that way. If you're a new reader, expect less drama and more code.

To illustrate that point, I just spent two hours of my life wrestling with conflicting federal standards for identifying US counties. The BLS has statistics on local unemployment for the last 20 years, and I want to visualize them. The Census provides some nice outlines of all the counties, which allows me to plot them on a map. So far so good. There's even a federal standard for assigning codes to each county called FIPS, which the Census data uses, and I thought the BLS unemployment data also used from a quick inspection.

That would be far too easy.

Instead, the BLS mostly uses FIPS, except when they don't. For example, they identify my old home of Ventura County, CA as 06122, whereas the correct FIPS code is 06111. The BLS aren't using 06111 for anything else, they've just decided they'd like to express their creativity by randomly shuffling the IDs around. They list the codes they're using here, but don't include a translation to FIPS. To handle the conversion, I had to write a script to read in the two files and try to match the names given to all the counties.

Doesn't sound too hard, right?

For starters, the BLS lists the areas as 'Ventura County, CA', but FIPS is 'Ventura, CA'. That's easy to fix, but then there's 'DeKalb, GA', versus 'De Kalb, GA', 'Miami-Dade, FL', versus 'Dade, FL', ad nauseum. Each inconsistency requires some fixup code, so the clock ticks by. Finally, I ironed out enough wrinkles to produce a decent-looking translation table. To save anyone else from the same sort of trouble, here's the result: blatofips.csv, and here's the script that produced it: createblatofips.php

This wasn't exactly the programming you see on TV, all enormous fonts, spinning 3D models and enhancing photos beyond belief. It's the part I love though, taking on a big ugly problem that's sitting between me and somewhere I want to go. I get to lose myself in a world of simple rules for a couple of hours, solve some puzzles, and emerge having made some tangible progress towards my goal.

So be warned there will be serious geekery ahead, but I hope you'll stay with me as I share my fascination with building these castles in the sky.

Comments

[Red Wing Black]

I'd be interested in hearing. The TOS seems rather clear that it is not unless expressly approved by Amazon. I guess if the library got it in writing then they would be ok.