COVID - 19 Prevalence in Tennessee by County

Load Data

The data source for this is the Tennessee department of health. The source of the data about Tennessee counties is the curated data from Wolfram Research.

In[]:=

tnData=Import["https://www.tn.gov/content/dam/tn/health/documents/cedep/novel-coronavirus/datasets/Public-Dataset-County-New.XLSX",{"Dataset",1},HeaderLines1];

Set up the mapping between the counties that are on the TN Department of Health to the entities that are used to the entities that Wolfram understands. This is because the TN dataset has “Dekalb” while Wolfram has the correct “DeKalb”.

In[]:=

countyMap=KeySortBy[countyMapRaw,-countyMap[#][EntityProperty["AdministrativeDivision","Population"]]&];

In[]:=

events=Import["https://raw.githubusercontent.com/VecGS/tn-covid19-data/master/events.csv"];

In[]:=

(*events=Import["~/Dropbox/Temp/TN County Events.csv"];*)

Processing

Exclude the data that is marked as “Pending” or “Out of State” since this doesn’t work well in the context of county-specific processing. At this stage we also add the additional property of PopulationPercentTotal which is the ratio of the people that have tested positive for COVID-19 over the population of each county.

In[]:=

augTnData=tnData[Select[KeyExistsQ[countyMap,#COUNTY]&],<|#,"PopulationPercentTotal"If[#"TOTAL_CASES"≠"",#"TOTAL_CASES"/QuantityMagnitude[countyMap[#COUNTY][EntityProperty["AdministrativeDivision","Population"]]],0]|>&];

Next we group the data by county. From here out we deal with counties in isolation.

In[]:=

countyData=KeySortBy[augTnData[GroupBy["COUNTY"]],-countyMap[#][EntityProperty["AdministrativeDivision","Population"]]&];

Finally create a graph of the data from each of the county’s dataset. We are plotting two different values that bear some correlation: the total number of active cases in the county as well as the percentage of the population of people who have tested positive in that county over time. Note that the total positive percentage can only effectively go up.

In[]:=

countyCallout[c_]:=TemplateApply[StringTemplate["Population: `pop`\nDensity: `dens`",<|"pop"->c[EntityProperty["AdministrativeDivision","Population"]],"dens"->c[EntityProperty["AdministrativeDivision","PopulationDensity"]]|>]]

In[]:=

percentPopulationTimeseries=TimeSeries[Normal[{#DATE,#PopulationPercentTotal*100}&/@#]]&/@countyData;

In[]:=

activeCaseTimeseries=Normal[{#DATE,#"TOTAL_ACTIVE"}&/@#]&/@countyData//Normal;

In[]:=

Fori=2,i<=Length[events],i++,IfDateObject[events[[i,2]],"Instant"]<=DateObject[],name=events[[i,3]]<>" "<>events[[i,2]];date=DateObject[events[[i,2]],Instant]+

days

;date=If[dateDateObject[{2020,6,28},Instant],DateObject[{2020,6,29,0,0,0.}],date];activeCaseTimeseries[events[[i,1]]]=activeCaseTimeseries[events[[i,1]]]/.{date,x_}Callout[{date,x},name,Automatic,date,LeaderSize{{15,140°,5},{10,165°}},LabelVisibilityAll,BackgroundNone]

(*activeCaseTimeseries["McMinn"]*)

In[]:=

DateListPlot[activeCaseTimeseries["Davidson"],PlotRangeAll,ImageSizeLarge]

Out[]=

In[]:=

xRange=

DateObject[{2020,3,10}]

,Automatic;

In[]:=

countyGraphs=#->Legended

[◼]	CombinePlots

[DateListPlot[Legended[activeCaseTimeseries[#],"Active Cases"],PlotRange{xRange,{0,All}},AxesLabelAutomatic],DateListPlot[Legended[percentPopulationTimeseries[#],"% pop ever test pos"],PlotStyleRed,FrameStyleRed,PlotRange{xRange,{0,If[percentPopulationTimeseries[#]["LastValue"]≤4,4,All]}}],"AxesSides""TwoY",PlotLabel#,ImageSizeLarge],Placed[Framed[countyCallout[countyMap[#]]],{0.3,0.9}]&/@Keys[countyMap]//Association;