Showing FIPS problems in Data.gov-hosted presidential elections county data https://github.com/dataofnote/us-presidential-election-county-results -- https://www.data.gov/issues/request-id/54251/

datawrangle.sh

# This script downloads the 2008 and 2012 county-level election results from Data.gov 
# and performs a SQL join to show how many of 2012's counties have the incorrect FIPS
#
# This script requires sqlite3 and ogr2ogr to be installed on the machine

mkdir -p /tmp/usgs/{2008,2012}
cd /tmp/usgs

# downloading 2012
# https://catalog.data.gov/dataset/presidential-general-election-results-2012-direct-download
curl \
      http://dds.cr.usgs.gov/pub/data/nationalatlas/elpo12p010g.shp_nt00887.tar.gz \
    | gzip -d \
    | tar xvf - -C ./2012
    

# downloading 2008
# https://catalog.data.gov/dataset/2008-presidential-general-election-county-results-direct-download
curl \
      http://dds.cr.usgs.gov/pub/data/nationalatlas/elpo08p020_nt00335.tar.gz \
    | gzip -d \
    | tar xvf - -C ./2008
    
# Write shapefiles to SQLite (sans WKT data):

ogr2ogr -f SQlite elections.sqlite -nln y2008 ./2008/elpo08p020.shp
ogr2ogr -f SQlite elections.sqlite -nln y2012 -update -append ./2012/elpo12p010g.shp


# Query the SQLite database and show where the 2012 and 2008 fips are matched
# but the county names are different. Because the 2012 dataset has a different
# naming convention than 2008, e.g. "St. Clair" vs "Saint Clair", I've removed all
# saints to make the comparison easier:

sqlite3 elections.sqlite \
    "
    SELECT y2012.fips, y2012.state, y2012.county, 
    y2008.fips, y2008.state, y2008.county
    FROM y2012
    INNER JOIN y2008
    ON y2008.fips = y2012.fips
    WHERE y2008.county NOT LIKE (y2012.county || '%')
        AND y2012.county NOT LIKE 'St%';
    "

results.md

2012fips	2012state	2012county	2008fips	2008state	2008county
08039	CO	El Paso	08039	CO	Elbert County
08041	CO	Elbert	08041	CO	El Paso County
08065	CO	La Plata	08065	CO	Lake County
08067	CO	Lake	08067	CO	La Plata County
10001	DE	New Castle	10001	DE	Kent County
11001	DC	DC	11001	DC	District of Columbia
13189	GA	Macon	13189	GA	McDuffie County
13193	GA	Marion	13193	GA	Macon County
13195	GA	McDuffie	13195	GA	Madison County
13197	GA	McIntosh	13197	GA	Marion County
17039	IL	Dewitt	17039	IL	De Witt County
17085	IL	Jodaviess	17085	IL	Jo Daviess County
17099	IL	Lasalle	17099	IL	La Salle County
17109	IL	Macon	17109	IL	McDonough County
17111	IL	Macoupin	17111	IL	McHenry County
17113	IL	Madison	17113	IL	McLean County
17115	IL	Marion	17115	IL	Macon County
17117	IL	Marshall	17117	IL	Macoupin County
17119	IL	Mason	17119	IL	Madison County
17121	IL	Massac	17121	IL	Marion County
17123	IL	Mcdonough	17123	IL	Marshall County
17125	IL	Mchenry	17125	IL	Mason County
17127	IL	Mclean	17127	IL	Massac County
18091	IN	La Porte	18091	IN	LaPorte County
20113	KS	Marion	20113	KS	McPherson County
20115	KS	Marshall	20115	KS	Marion County
20117	KS	Mcpherson	20117	KS	Marshall County
21145	KY	Madison	21145	KY	McCracken County
21147	KY	Magoffin	21147	KY	McCreary County
21149	KY	Marion	21149	KY	McLean County
21151	KY	Marshall	21151	KY	Madison County
21153	KY	Martin	21153	KY	Magoffin County
21155	KY	Mason	21155	KY	Marion County
21157	KY	Mccracken	21157	KY	Marshall County
21159	KY	Mccreary	21159	KY	Martin County
21161	KY	Mclean	21161	KY	Mason County
22059	LA	Lasalle	22059	LA	La Salle Parish
27085	MN	Mahnomen	27085	MN	McLeod County
27087	MN	Marshall	27087	MN	Mahnomen County
27089	MN	Martin	27089	MN	Marshall County
27091	MN	Mcleod	27091	MN	Martin County
29063	MO	De Kalb	29063	MO	DeKalb County
29119	MO	Macon	29119	MO	McDonald County
29121	MO	Madison	29121	MO	Macon County
29123	MO	Maries	29123	MO	Madison County
29125	MO	Marion	29125	MO	Maries County
29127	MO	Mcdonald	29127	MO	Marion County
30049	MT	Lewis & Clark	30049	MT	Lewis and Clark County
30055	MT	Madison	30055	MT	McCone County
30057	MT	Mccone	30057	MT	Madison County
31117	NE	Madison	31117	NE	McPherson County
31119	NE	Mcpherson	31119	NE	Madison County
37111	NC	Macon	37111	NC	McDowell County
37113	NC	Madison	37113	NC	Macon County
37115	NC	Martin	37115	NC	Madison County
37117	NC	Mcdowell	37117	NC	Martin County
40087	OK	Major	40087	OK	McClain County
40089	OK	Marshall	40089	OK	McCurtain County
40091	OK	Mayes	40091	OK	McIntosh County
40093	OK	Murray	40093	OK	Major County
40095	OK	Muskogee	40095	OK	Marshall County
40097	OK	Mcclain	40097	OK	Mayes County
40099	OK	Mccurtain	40099	OK	Murray County
40101	OK	Mcintosh	40101	OK	Muskogee County
45065	SC	Marion	45065	SC	McCormick County
45067	SC	Marlboro	45067	SC	Marion County
45069	SC	Mccormick	45069	SC	Marlboro County
46087	SD	Marshall	46087	SD	McCook County
46089	SD	Mccook	46089	SD	McPherson County
46091	SD	Mcpherson	46091	SD	Marshall County
47107	TN	Macon	47107	TN	McMinn County
47109	TN	Madison	47109	TN	McNairy County
47111	TN	Marion	47111	TN	Macon County
47113	TN	Marshall	47113	TN	Madison County
47115	TN	Maury	47115	TN	Marion County
47117	TN	Mcminn	47117	TN	Marshall County
47119	TN	Mcnairy	47119	TN	Maury County
48139	TX	El Paso	48139	TX	Ellis County
48141	TX	Ellis	48141	TX	El Paso County
48283	TX	Lasalle	48283	TX	La Salle County
48307	TX	Madison	48307	TX	McCulloch County
48309	TX	Marion	48309	TX	McLennan County
48311	TX	Martin	48311	TX	McMullen County
48313	TX	Mason	48313	TX	Madison County
48315	TX	Matagorda	48315	TX	Marion County
48317	TX	Maverick	48317	TX	Martin County
48319	TX	Mcculloch	48319	TX	Mason County
48323	TX	Mcmullen	48323	TX	Maverick County
51097	VA	King & Queen	51097	VA	King and Queen County
54047	WV	Marion	54047	WV	McDowell County
54049	WV	Marshall	54049	WV	Marion County
54051	WV	Mason	54051	WV	Marshall County
54053	WV	Mcdowell	54053	WV	Mason County
10003	DE	Kent	10003	DE	New Castle County
13191	GA	Madison	13191	GA	McIntosh County
13191	GA	Madison	13191	GA	McIntosh County
13191	GA	Madison	13191	GA	McIntosh County
13191	GA	Madison	13191	GA	McIntosh County
13191	GA	Madison	13191	GA	McIntosh County
13191	GA	Madison	13191	GA	McIntosh County
13191	GA	Madison	13191	GA	McIntosh County
26055	MI	Gd. Traverse	26055	MI	Grand Traverse County
48321	TX	Mclennan	48321	TX	Matagorda County
48321	TX	Mclennan	48321	TX	Matagorda County
40079	OK	Leflore	40079	OK	Le Flore County