Cross References all Counties in the CSSEGISandData Data Set with US Census Data to Find Missing Counties

covid19_stats_missing_us_counties.py

import csv
import os.path
from os import path
import urllib.request
from datetime import datetime, timedelta

censusCSVUrl = 'https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/counties/totals/co-est2019-alldata.csv'
censusCSVFileName = 'co-est2019-alldata.csv'
censusCSVFilePath = censusCSVFileName

if not path.exists(censusCSVFilePath):
	print("Downloading US Census data...")
	urllib.request.urlretrieve(censusCSVUrl, censusCSVFilePath)

covidDailyStatsCSVFileName = (datetime.now() - timedelta(1)).strftime('%m-%d-%Y') + '.csv'
covidDailyStatsCSVUrl = 'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/' + covidDailyStatsCSVFileName
covidDailyStatsCSVFilePath = covidDailyStatsCSVFileName

if not path.exists(covidDailyStatsCSVFilePath):
	print("Downloading Covid19 daily stats data...")
	urllib.request.urlretrieve(covidDailyStatsCSVUrl, covidDailyStatsCSVFilePath)

covidDailyStatsRows = [] 
covidDailyStatsFieldNames = ['FIPS','Admin2','Province_State','Country_Region','Last_Update','Lat','Long_','Confirmed','Deaths','Recovered','Active','Combined_Key']

with open(covidDailyStatsCSVFilePath, 'r') as covidDailyStatsCsvfile: 
    csvReader = csv.DictReader(covidDailyStatsCsvfile, fieldnames=covidDailyStatsFieldNames) 
      
    # skip field name first row
    next(csvReader) 
  
    for row in csvReader: 
        covidDailyStatsRows.append(row) 

censusRows = []
censusFieldNames = ['SUMLEV','REGION','DIVISION','STATE','COUNTY','STNAME','CTYNAME','CENSUS2010POP','ESTIMATESBASE2010','POPESTIMATE2010','POPESTIMATE2011','POPESTIMATE2012','POPESTIMATE2013','POPESTIMATE2014','POPESTIMATE2015','POPESTIMATE2016','POPESTIMATE2017','POPESTIMATE2018','POPESTIMATE2019','NPOPCHG_2010','NPOPCHG_2011','NPOPCHG_2012','NPOPCHG_2013','NPOPCHG_2014','NPOPCHG_2015','NPOPCHG_2016','NPOPCHG_2017','NPOPCHG_2018','NPOPCHG_2019','BIRTHS2010','BIRTHS2011','BIRTHS2012','BIRTHS2013','BIRTHS2014','BIRTHS2015','BIRTHS2016','BIRTHS2017','BIRTHS2018','BIRTHS2019','DEATHS2010','DEATHS2011','DEATHS2012','DEATHS2013','DEATHS2014','DEATHS2015','DEATHS2016','DEATHS2017','DEATHS2018','DEATHS2019','NATURALINC2010','NATURALINC2011','NATURALINC2012','NATURALINC2013','NATURALINC2014','NATURALINC2015','NATURALINC2016','NATURALINC2017','NATURALINC2018','NATURALINC2019','INTERNATIONALMIG2010','INTERNATIONALMIG2011','INTERNATIONALMIG2012','INTERNATIONALMIG2013','INTERNATIONALMIG2014','INTERNATIONALMIG2015','INTERNATIONALMIG2016','INTERNATIONALMIG2017','INTERNATIONALMIG2018','INTERNATIONALMIG2019','DOMESTICMIG2010','DOMESTICMIG2011','DOMESTICMIG2012','DOMESTICMIG2013','DOMESTICMIG2014','DOMESTICMIG2015','DOMESTICMIG2016','DOMESTICMIG2017','DOMESTICMIG2018','DOMESTICMIG2019','NETMIG2010','NETMIG2011','NETMIG2012','NETMIG2013','NETMIG2014','NETMIG2015','NETMIG2016','NETMIG2017','NETMIG2018','NETMIG2019','RESIDUAL2010','RESIDUAL2011','RESIDUAL2012','RESIDUAL2013','RESIDUAL2014','RESIDUAL2015','RESIDUAL2016','RESIDUAL2017','RESIDUAL2018','RESIDUAL2019','GQESTIMATESBASE2010','GQESTIMATES2010','GQESTIMATES2011','GQESTIMATES2012','GQESTIMATES2013','GQESTIMATES2014','GQESTIMATES2015','GQESTIMATES2016','GQESTIMATES2017','GQESTIMATES2018','GQESTIMATES2019','RBIRTH2011','RBIRTH2012','RBIRTH2013','RBIRTH2014','RBIRTH2015','RBIRTH2016','RBIRTH2017','RBIRTH2018','RBIRTH2019','RDEATH2011','RDEATH2012','RDEATH2013','RDEATH2014','RDEATH2015','RDEATH2016','RDEATH2017','RDEATH2018','RDEATH2019','RNATURALINC2011','RNATURALINC2012','RNATURALINC2013','RNATURALINC2014','RNATURALINC2015','RNATURALINC2016','RNATURALINC2017','RNATURALINC2018','RNATURALINC2019','RINTERNATIONALMIG2011','RINTERNATIONALMIG2012','RINTERNATIONALMIG2013','RINTERNATIONALMIG2014','RINTERNATIONALMIG2015','RINTERNATIONALMIG2016','RINTERNATIONALMIG2017','RINTERNATIONALMIG2018','RINTERNATIONALMIG2019','RDOMESTICMIG2011','RDOMESTICMIG2012','RDOMESTICMIG2013','RDOMESTICMIG2014','RDOMESTICMIG2015','RDOMESTICMIG2016','RDOMESTICMIG2017','RDOMESTICMIG2018','RDOMESTICMIG2019','RNETMIG2011','RNETMIG2012','RNETMIG2013','RNETMIG2014','RNETMIG2015','RNETMIG2016','RNETMIG2017','RNETMIG2018','RNETMIG2019']

with open(censusCSVFilePath, 'r', encoding="windows-1252") as censusCsvfile: 
    csvReader = csv.DictReader(censusCsvfile, fieldnames=censusFieldNames) 
      
    # skip field name first row
    next(csvReader) 
  
    # extracting each data row one by one 
    for row in csvReader: 
        censusRows.append(row) 

records = {}

for row in covidDailyStatsRows:
    country = row["Country_Region"]
    # Find US counties only
    if country == "US":
        records[row["Admin2"] + "::" + row["Province_State"]] = row

print("Missing US Counties:") 
for row in censusRows:
    if " County" not in row["CTYNAME"]: continue
    state = row["STNAME"]
    county = row["CTYNAME"].replace(" County", "")
    popEstimate2019 = row["POPESTIMATE2019"]
    record = records.get(county + "::" + state)
    if not record:
        print(county + " County, " + state)