Final Github Markdown
Loading packages
FTE DATA
Upload data and create a dataframe called khn
Create a new dataframe that filters just for NC
Select certain categories and filter out rows without fte data
Find the average FTE per capita for the years we have available for each health department
## `summarise()` ungrouping output (override with `.groups` argument)## # A tibble: 94 x 2
## lhd_name Avg_fte_per_100000
## <chr> <dbl>
## 1 Yancey County Health Department 608.
## 2 Hyde County Health Department 481.
## 3 Swain County Health Department 373.
## 4 Graham County Health Department 334.
## 5 Alexander County Health Department 307.
## 6 Toe River District 306.
## 7 Northampton County Health Department 301.
## 8 Clay County Health Department 291.
## 9 Martin Tyrrell Washington District 268.
## 10 Dare County Health Department 257.
## # ... with 84 more rowsFind the highest FTE per capita and FTE in 2017
## year lhd_name population fte
## 1 2017 Hyde County Health Department 5240 14
## 2 2017 Clay County Health Department 11004 29
## 3 2017 Graham County Health Department 8524 21
## 4 2017 Northampton County Health Department 19890 48
## 5 2017 Dare County Health Department 36199 87
## 6 2017 Cleveland County Health Department 97134 229
## 7 2017 Warren County Health Department 19849 44
## 8 2017 Swain County Health Department 14263 29
## 9 2017 Jones County Health Department 9573 19
## 10 2017 Martin Tyrrell Washington District 38887 74
## 11 2017 Caswell County Health Department 22616 42
## 12 2017 Macon County Public Health Center 34581 64
## 13 2017 Madison County Health Department 21563 38
## 14 2017 Bladen County Health Department 33443 56
## 15 2017 Hertford Health Department 23923 40
## 16 2017 Surry County Health and Nutrition Center 72107 115
## 17 2017 Albemarle Regional Health Services 134453 210
## 18 2017 Halifax County Department of Health 51311 80
## 19 2017 Craven County Health Department 102492 150
## 20 2017 Edgecombe County Health Department 52756 76
## 21 2017 Wilson County Department of Public Health 81436 110
## 22 2017 Davie County Health Department 42308 56
## 23 2017 Jackson County Department of Public Health 43224 57
## 24 2017 Richmond County Health Department 44827 59
## 25 2017 Greene County Health Department 20963 26
## 26 2017 Person County Health Department 39338 48
## 27 2017 Nash County Health Department 94019 113
## 28 2017 Toe River District 50206 60
## 29 2017 Pamlico County Health Department 12638 15
## 30 2017 Montgomery County Health Department 27269 30
## 31 2017 Pender County Health Department 60719 65
## 32 2017 Stanly County Health Department 61505 65
## 33 2017 Robeson County Health Department 132626 138
## 34 2017 Cherokee County Health Department 27954 29
## 35 2017 Chatham County Public Health Department 71189 72
## 36 2017 Wayne County Health Department 123034 123
## 37 2017 Scotland County Health Department 35174 35
## 38 2017 Public Health Authority of Cabarrus County 206988 205
## 39 2017 Caldwell County Health Department 81933 78
## 40 2017 Wilkes County Health Department 68489 63
## 41 2017 Beaufort County Health Department 47033 43
## 42 2017 Columbus County Health Department 56047 50
## 43 2017 Carteret County Health Department 68921 61
## 44 2017 Lenoir County Health Department 56622 50
## 45 2017 Gaston County Health Department 219656 190
## 46 2017 Haywood County Health Department 61005 52
## 47 2017 Appalachian District Health Department 92956 79
## 48 2017 Cumberland County Health Department 330994 279
## 49 2017 Transylvania County Health Department 33767 28
## 50 2017 Henderson County Health Department 115216 95
## 51 2017 Lincoln County Health Department 82557 67
## 52 2017 Rockingham County Health Department 90791 73
## 53 2017 Alexander County Health Department 37143 29
## 54 2017 Anson County Health Department 24829 19
## 55 2017 Mecklenburg County Health Department 1078128 825
## 56 2017 Pitt County Public Health Center 178607 133
## 57 2017 Johnston County Health Department 196374 146
## 58 2017 Guilford County Department of Public Health 528243 385
## 59 2017 Sampson County Health Department 63263 46
## 60 2017 Durham County Health Department 312153 224
## 61 2017 Granville-Vance District Health Department 103653 74
## 62 2017 Franklin County Health Department 66155 47
## 63 2017 Orange County Health Department 143626 102
## 64 2017 Stokes County Health Depart/Family Health Center 45724 32
## 65 2017 New Hanover County Health Department 228728 160
## 66 2017 Duplin County Health Department 58943 41
## 67 2017 Lee County Health Department 60403 42
## 68 2017 Forsyth County Department of Public Health 375840 236
## 69 2017 Catawba County Health Department 157852 99
## 70 2017 Iredell County Health Department 175634 106
## 71 2017 Davidson County Health Department 165180 98
## 72 2017 Wake County Human Services 1071706 614
## 73 2017 Alamance County Health Department 163276 92
## 74 2017 Randolph County Health Department 143037 80
## 75 2017 Onslow County Health Department 195066 108
## 76 2017 Harnett County Health Department 132395 68
## 77 2017 Rutherford-Polk-McDowell District 132176 67
## 78 2017 Burke County Health Department 90157 45
## 79 2017 Rowan County Health Department 140356 69
## 80 2017 Hoke County Health Center 54138 26
## 81 2017 Yadkin County Health Department 37588 18
## 82 2017 Moore County Health Department 97368 46
## 83 2017 Brunswick County Health Department 130859 60
## 84 2017 Buncombe County Health Department 257071 110
## 85 2017 Union County Health Department 231350 87
## fte_per_100000
## 1 267.17557
## 2 263.54053
## 3 246.36321
## 4 241.32730
## 5 240.33813
## 6 235.75679
## 7 221.67364
## 8 203.32328
## 9 198.47488
## 10 190.29496
## 11 185.70923
## 12 185.07273
## 13 176.22780
## 14 167.44909
## 15 167.20311
## 16 159.48521
## 17 156.18841
## 18 155.91199
## 19 146.35289
## 20 144.05944
## 21 135.07540
## 22 132.36267
## 23 131.87118
## 24 131.61711
## 25 124.02805
## 26 122.01942
## 27 120.18847
## 28 119.50763
## 29 118.68967
## 30 110.01504
## 31 107.05051
## 32 105.68246
## 33 104.05200
## 34 103.74186
## 35 101.13922
## 36 99.97237
## 37 99.50532
## 38 99.03956
## 39 95.19974
## 40 91.98557
## 41 91.42517
## 42 89.21084
## 43 88.50713
## 44 88.30490
## 45 86.49889
## 46 85.23891
## 47 84.98645
## 48 84.29156
## 49 82.92120
## 50 82.45383
## 51 81.15605
## 52 80.40445
## 53 78.07662
## 54 76.52342
## 55 76.52153
## 56 74.46517
## 57 74.34793
## 58 72.88312
## 59 72.71233
## 60 71.75968
## 61 71.39205
## 62 71.04527
## 63 71.01778
## 64 69.98513
## 65 69.95208
## 66 69.55873
## 67 69.53297
## 68 62.79268
## 69 62.71698
## 70 60.35278
## 71 59.32922
## 72 57.29183
## 73 56.34631
## 74 55.92958
## 75 55.36588
## 76 51.36146
## 77 50.68999
## 78 49.91293
## 79 49.16071
## 80 48.02542
## 81 47.88762
## 82 47.24345
## 83 45.85088
## 84 42.78974
## 85 37.60536## year lhd_name population fte
## 1 2017 Hyde County Health Department 5240 14
## 2 2017 Pamlico County Health Department 12638 15
## 3 2017 Yadkin County Health Department 37588 18
## 4 2017 Anson County Health Department 24829 19
## 5 2017 Jones County Health Department 9573 19
## 6 2017 Graham County Health Department 8524 21
## 7 2017 Greene County Health Department 20963 26
## 8 2017 Hoke County Health Center 54138 26
## 9 2017 Transylvania County Health Department 33767 28
## 10 2017 Alexander County Health Department 37143 29
## 11 2017 Cherokee County Health Department 27954 29
## 12 2017 Clay County Health Department 11004 29
## 13 2017 Swain County Health Department 14263 29
## 14 2017 Montgomery County Health Department 27269 30
## 15 2017 Stokes County Health Depart/Family Health Center 45724 32
## 16 2017 Scotland County Health Department 35174 35
## 17 2017 Madison County Health Department 21563 38
## 18 2017 Hertford Health Department 23923 40
## 19 2017 Duplin County Health Department 58943 41
## 20 2017 Lee County Health Department 60403 42
## 21 2017 Caswell County Health Department 22616 42
## 22 2017 Beaufort County Health Department 47033 43
## 23 2017 Warren County Health Department 19849 44
## 24 2017 Burke County Health Department 90157 45
## 25 2017 Moore County Health Department 97368 46
## 26 2017 Sampson County Health Department 63263 46
## 27 2017 Franklin County Health Department 66155 47
## 28 2017 Northampton County Health Department 19890 48
## 29 2017 Person County Health Department 39338 48
## 30 2017 Columbus County Health Department 56047 50
## 31 2017 Lenoir County Health Department 56622 50
## 32 2017 Haywood County Health Department 61005 52
## 33 2017 Bladen County Health Department 33443 56
## 34 2017 Davie County Health Department 42308 56
## 35 2017 Jackson County Department of Public Health 43224 57
## 36 2017 Richmond County Health Department 44827 59
## 37 2017 Brunswick County Health Department 130859 60
## 38 2017 Toe River District 50206 60
## 39 2017 Carteret County Health Department 68921 61
## 40 2017 Wilkes County Health Department 68489 63
## 41 2017 Macon County Public Health Center 34581 64
## 42 2017 Pender County Health Department 60719 65
## 43 2017 Stanly County Health Department 61505 65
## 44 2017 Lincoln County Health Department 82557 67
## 45 2017 Rutherford-Polk-McDowell District 132176 67
## 46 2017 Harnett County Health Department 132395 68
## 47 2017 Rowan County Health Department 140356 69
## 48 2017 Chatham County Public Health Department 71189 72
## 49 2017 Rockingham County Health Department 90791 73
## 50 2017 Granville-Vance District Health Department 103653 74
## 51 2017 Martin Tyrrell Washington District 38887 74
## 52 2017 Edgecombe County Health Department 52756 76
## 53 2017 Caldwell County Health Department 81933 78
## 54 2017 Appalachian District Health Department 92956 79
## 55 2017 Halifax County Department of Health 51311 80
## 56 2017 Randolph County Health Department 143037 80
## 57 2017 Dare County Health Department 36199 87
## 58 2017 Union County Health Department 231350 87
## 59 2017 Alamance County Health Department 163276 92
## 60 2017 Henderson County Health Department 115216 95
## 61 2017 Davidson County Health Department 165180 98
## 62 2017 Catawba County Health Department 157852 99
## 63 2017 Orange County Health Department 143626 102
## 64 2017 Iredell County Health Department 175634 106
## 65 2017 Onslow County Health Department 195066 108
## 66 2017 Buncombe County Health Department 257071 110
## 67 2017 Wilson County Department of Public Health 81436 110
## 68 2017 Nash County Health Department 94019 113
## 69 2017 Surry County Health and Nutrition Center 72107 115
## 70 2017 Wayne County Health Department 123034 123
## 71 2017 Pitt County Public Health Center 178607 133
## 72 2017 Robeson County Health Department 132626 138
## 73 2017 Johnston County Health Department 196374 146
## 74 2017 Craven County Health Department 102492 150
## 75 2017 New Hanover County Health Department 228728 160
## 76 2017 Gaston County Health Department 219656 190
## 77 2017 Public Health Authority of Cabarrus County 206988 205
## 78 2017 Albemarle Regional Health Services 134453 210
## 79 2017 Durham County Health Department 312153 224
## 80 2017 Cleveland County Health Department 97134 229
## 81 2017 Forsyth County Department of Public Health 375840 236
## 82 2017 Cumberland County Health Department 330994 279
## 83 2017 Guilford County Department of Public Health 528243 385
## 84 2017 Wake County Human Services 1071706 614
## 85 2017 Mecklenburg County Health Department 1078128 825
## fte_per_100000
## 1 267.17557
## 2 118.68967
## 3 47.88762
## 4 76.52342
## 5 198.47488
## 6 246.36321
## 7 124.02805
## 8 48.02542
## 9 82.92120
## 10 78.07662
## 11 103.74186
## 12 263.54053
## 13 203.32328
## 14 110.01504
## 15 69.98513
## 16 99.50532
## 17 176.22780
## 18 167.20311
## 19 69.55873
## 20 69.53297
## 21 185.70923
## 22 91.42517
## 23 221.67364
## 24 49.91293
## 25 47.24345
## 26 72.71233
## 27 71.04527
## 28 241.32730
## 29 122.01942
## 30 89.21084
## 31 88.30490
## 32 85.23891
## 33 167.44909
## 34 132.36267
## 35 131.87118
## 36 131.61711
## 37 45.85088
## 38 119.50763
## 39 88.50713
## 40 91.98557
## 41 185.07273
## 42 107.05051
## 43 105.68246
## 44 81.15605
## 45 50.68999
## 46 51.36146
## 47 49.16071
## 48 101.13922
## 49 80.40445
## 50 71.39205
## 51 190.29496
## 52 144.05944
## 53 95.19974
## 54 84.98645
## 55 155.91199
## 56 55.92958
## 57 240.33813
## 58 37.60536
## 59 56.34631
## 60 82.45383
## 61 59.32922
## 62 62.71698
## 63 71.01778
## 64 60.35278
## 65 55.36588
## 66 42.78974
## 67 135.07540
## 68 120.18847
## 69 159.48521
## 70 99.97237
## 71 74.46517
## 72 104.05200
## 73 74.34793
## 74 146.35289
## 75 69.95208
## 76 86.49889
## 77 99.03956
## 78 156.18841
## 79 71.75968
## 80 235.75679
## 81 62.79268
## 82 84.29156
## 83 72.88312
## 84 57.29183
## 85 76.52153Create a vector that lists counties in our sample size
Find the average percent change in fte from 2007-2017
## # A tibble: 49 x 5
## lhd_name `2007` `2017` pct_change avg
## <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Mecklenburg County Health Department 379 825 118. -12.5
## 2 Madison County Health Department 25 38 52 -12.5
## 3 Johnston County Health Department 107 146 36.4 -12.5
## 4 Jones County Health Department 15 19 26.7 -12.5
## 5 Pender County Health Department 56 65 16.1 -12.5
## 6 Cleveland County Health Department 202 229 13.4 -12.5
## 7 Orange County Health Department 94 102 8.51 -12.5
## 8 Pitt County Public Health Center 124 133 7.26 -12.5
## 9 Henderson County Health Department 90 95 5.56 -12.5
## 10 Stanly County Health Department 63 65 3.17 -12.5
## # ... with 39 more rowsEXPENDITURE DATA ACCOUNTING FOR INFLATION
Loading in necessary datasets.
## Warning: Missing column names filled in: 'X8' [8], 'X9' [9]## Parsed with column specification:
## cols(
## year = col_double(),
## fips_state = col_character(),
## state_code = col_character(),
## population = col_double(),
## fte = col_double(),
## expenditures = col_double(),
## expenditures_infl = col_double(),
## fte_per_100000 = col_double(),
## expenditures_per_capita = col_double(),
## expenditures_infl_per_capita = col_double()
## )Creating a dataset of inflation rates, no data for 2013.
## Warning in max(expenditures, na.rm = TRUE): no non-missing arguments to max;
## returning -Inf
## Warning in max(expenditures, na.rm = TRUE): no non-missing arguments to max;
## returning -Inf## Warning in max(expenditures_infl, na.rm = TRUE): no non-missing arguments to
## max; returning -Inf
## Warning in max(expenditures_infl, na.rm = TRUE): no non-missing arguments to
## max; returning -Inf## `summarise()` ungrouping output (override with `.groups` argument)Using the infl_rates data to create a new dataframe of all LHD expenditures, adjusted for inflation.
Turning the years from a character string to an integer.
Pivoting the dataset so that the counties are in one row, and are separated by year columns.
Creating a line graph of every counties’ per capita expenditures, adjusted for inflation.
## Warning: `group_by_()` is deprecated as of dplyr 0.7.0.
## Please use `group_by()` instead.
## See vignette('programming') for more help
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_warnings()` to see where this warning was generated.Overall trend is a slight decline, or at least the majority are staying the same.
From that chart we can tell that Franklin, Hyde, Dare, and the Martin-Tyrell-Washington District spend far more per person than any others.
Comparing county expenditures to those of the North Carolina Department of Health and Human Services.
Loading in more necessary datasets.
## Parsed with column specification:
## cols(
## year = col_double(),
## fips_state = col_character(),
## state_code = col_character(),
## population = col_double(),
## fte = col_double(),
## expenditures = col_double(),
## expenditures_infl = col_double(),
## fte_per_100000 = col_double(),
## expenditures_per_capita = col_double(),
## expenditures_infl_per_capita = col_double()
## )## Parsed with column specification:
## cols(
## year = col_double(),
## state_code = col_character(),
## level = col_character(),
## population = col_double(),
## category_code = col_character(),
## category_name = col_character(),
## expenditures = col_double(),
## expenditures_infl = col_double(),
## expenditures_per_capita = col_double(),
## expenditures_infl_per_capita = col_double(),
## expenditures_pct = col_double()
## )## Parsed with column specification:
## cols(
## year = col_double(),
## state_code = col_character(),
## fips_county = col_character(),
## county_name = col_character(),
## population = col_double(),
## category_name = col_character(),
## expenditures = col_double(),
## expenditures_infl = col_double(),
## expenditures_per_capita = col_double(),
## expenditures_infl_per_capita = col_double(),
## expenditures_pct = col_double()
## )## Parsed with column specification:
## cols(
## year = col_double(),
## state_code = col_character(),
## population = col_double(),
## fte = col_double(),
## expenditures = col_double(),
## expenditures_infl = col_double(),
## expenditures_infl_per_capita = col_double(),
## fte_per_100000 = col_double()
## )Proving the claim that spending has dropped by 16% since 2010 across the U.S.
## `summarise()` ungrouping output (override with `.groups` argument)## # A tibble: 2 x 4
## year expenditures_infl population expenditures_infl_per_capita
## <dbl> <dbl> <dbl> <dbl>
## 1 2010 29699234065. 267641724 111.
## 2 2018 26219994200. 281375539 93.2Checking to see how much spending dropped in NC in the same 8 year span.
## # A tibble: 2 x 2
## year expenditures_infl_per_capita
## <dbl> <dbl>
## 1 2010 100.
## 2 2018 72.8While expenditures per capita declined 16% in the United States, they fell 27% in North Carolina.
For every dollar spent on state agency public health nationally in 2010, NC spent 90 cents.
By 2018, that was only 77 cents per dollar.
Rearranging the national dataframe from least to greatest, to see where NC ranks in expenditures per capita in 2018.
## # A tibble: 47 x 2
## state_code expenditures_infl_per_capita
## <chr> <dbl>
## 1 IL 30.8
## 2 SC 44.5
## 3 OH 50.1
## 4 WI 54.1
## 5 ID 58.3
## 6 IN 60.6
## 7 OR 61.7
## 8 KS 63.1
## 9 NH 64.0
## 10 NV 65.5
## # ... with 37 more rowsAt the state level in 2018, North Carolina spends less per person than all but 13 states. It spends about $72 per person.
While the spending at the state level has decreased, that has left counties to pick up the tab.
Checking to see how much spending has dropped on average for NC counties.
## `summarise()` ungrouping output (override with `.groups` argument)## # A tibble: 2 x 4
## year cum_expenditures cum_population cum_expenditures_per_capita
## <int> <dbl> <dbl> <dbl>
## 1 2010 519968843. 6078140 85.5
## 2 2018 487368803. 7238081 67.3On top of the 27% drop at the state level, county-level spending also dropped 21%.
It’s been left to local agencies to pick up the slack. Some have. Some haven’t. But nobody really knows. The report for the first time ever tries to shine light on the local public health spending in North Carolina.
Showing where each individual LHD ranks by pct_change in per capita.
## # A tibble: 44 x 11
## county_name year2010 year2011 year2012 year2013 year2014 year2015 year2016
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Wake 107. 101. NaN NA 42.2 43.9 45.4
## 2 Yadkin 86.1 80.4 NaN NA 38.3 39.3 40.5
## 3 Moore 69.3 59.6 NaN NA 41.9 41.8 41.6
## 4 Catawba 94.5 92.6 NaN NA 73.9 71.7 63.1
## 5 Jones 153. 127. NaN NA 101. 95.4 105.
## 6 Brunswick 87.6 80.5 NaN NA 60.4 60.7 54.8
## 7 Hyde 333. 297. NaN NA 272. 285. 305.
## 8 Harnett 68.5 61.2 NaN NA 48.7 51.6 49.4
## 9 Avery, Mit~ 131. 125. NaN NA 114. 112. 97.6
## 10 Gaston 129. 110. NaN NA 96.9 84.9 85.8
## # ... with 34 more rows, and 3 more variables: year2017 <dbl>, year2018 <dbl>,
## # pct_change <dbl>The county with the biggest decline was Wake. From 2011 to 2014, it’s per capita spending dropped nearly 60 percent.
On the other hand, Mecklenburg saw the biggest per capita increase – 40% over those 8 years, and a jump of 28% from 2015 to 2016 alone.
Did that happen everywhere in 2015-2016?
Looking at percent change numbers for all counties from 2015-2016. Mecklenburg had almost 14% more of an increase than the next highest, which was Richmond county at 14%.
## # A tibble: 51 x 4
## county_name year2015 year2016 pct_change
## <chr> <dbl> <dbl> <dbl>
## 1 Lincoln 67.2 57.5 -0.144
## 2 Avery, Mitchell & Yancey 112. 97.6 -0.128
## 3 Catawba 71.7 63.1 -0.120
## 4 Alexander 83.5 73.9 -0.115
## 5 Brunswick 60.7 54.8 -0.0974
## 6 Martin, Tyrrell & Washington 171. 161. -0.0551
## 7 Harnett 51.6 49.4 -0.0437
## 8 Burke 37.0 35.7 -0.0335
## 9 Person 115. 112. -0.0308
## 10 Wayne 64.3 62.5 -0.0281
## # ... with 41 more rowsRight next door to Wake, in more rural Johnston County in the same year, you had a 4% increase.
Mecklenburg dipped, too, but the rocketed back to see a 40% increase in per capita spending.
Ordering the counties by how they ranked for their per capita expenditures in 2010.
## # A tibble: 44 x 11
## county_name year2010 year2011 year2012 year2013 year2014 year2015 year2016
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Stanly 44.0 45.1 NaN NA 42.4 44.1 43.5
## 2 Burke 47.5 43.7 NaN NA 36.3 37.0 35.7
## 3 Mecklenburg 49.1 48.1 NaN NA 40.7 52.3 66.9
## 4 Rowan 49.5 49.9 NaN NA 41.4 41.2 41.3
## 5 Union 49.5 48.2 NaN NA 38.9 42.3 43.8
## 6 Johnston 53.8 51.7 NaN NA 68.4 70.2 73.1
## 7 Lee 57.4 51.3 NaN NA 50.1 51.7 55.7
## 8 Iredell 59.9 54.5 NaN NA 49.4 47.6 47.9
## 9 Lenoir 63.5 64.6 NaN NA 63.3 69.1 73.3
## 10 Granville ~ 65.9 62.7 NaN NA 65.6 64.0 62.4
## # ... with 34 more rows, and 3 more variables: year2017 <dbl>, year2018 <dbl>,
## # pct_change <dbl>In 2010, Mecklenburg had one of the lowest per capita rates in the state – similar to tiny Stanly and Burke counties. (Show an animated map change from 2010 to 2018 per capita.)
In 2010, Meck and Union were both near the bottom, spending similar amounts per person. But by 2018 as Meck continued to spend, Union’ expenditures dropped nearly 32%.
Sorting counties by their per capita expenditures in 2018.
## # A tibble: 44 x 11
## county_name year2010 year2011 year2012 year2013 year2014 year2015 year2016
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Union 49.5 48.2 NaN NA 38.9 42.3 43.8
## 2 Burke 47.5 43.7 NaN NA 36.3 37.0 35.7
## 3 Moore 69.3 59.6 NaN NA 41.9 41.8 41.6
## 4 Yadkin 86.1 80.4 NaN NA 38.3 39.3 40.5
## 5 Rowan 49.5 49.9 NaN NA 41.4 41.2 41.3
## 6 Stanly 44.0 45.1 NaN NA 42.4 44.1 43.5
## 7 Harnett 68.5 61.2 NaN NA 48.7 51.6 49.4
## 8 Wake 107. 101. NaN NA 42.2 43.9 45.4
## 9 Iredell 59.9 54.5 NaN NA 49.4 47.6 47.9
## 10 Lee 57.4 51.3 NaN NA 50.1 51.7 55.7
## # ... with 34 more rows, and 3 more variables: year2017 <dbl>, year2018 <dbl>,
## # pct_change <dbl>Creating a new dataframe that shows the change in population for each county.
Some counties saw their per capita spending increase because their population plummeted even while state population grew about 10%.
For example, Richmond County’s per capita spending increased nearly 15% even while it’s population decreased 3%.
Creating a new dataframe that shows counties that increased in per capita spending, and then finding where they intersect with counties that decreased in population. The only two counties where both conditions are true are Cleveland and Richmond counties.
## [1] "Cleveland" "Richmond"Putting what was found above in a more readable chart, along with the corresponding numbers.
## # A tibble: 2 x 3
## county_name per_cap_spending_increase pop_pct_decrease
## <chr> <dbl> <dbl>
## 1 Cleveland 0.0335 -0.00937
## 2 Richmond 0.147 -0.0309Which counties saw decreased spending?
Creating a dataframe showing exclusively counties that had an overall decrease in expenditures from 2010-2018.
## # A tibble: 20 x 6
## county_name year2010 year2018 expend_diff raw_diff change
## <chr> <dbl> <dbl> <dbl> <dbl> <chr>
## 1 Alexander 2763318. 2376515. -386802. -386802. decrea~
## 2 Avery, Mitchell & Yancey 5493127. 4248446. -1244681. -1244681. decrea~
## 3 Brunswick 7734922 7041307 -693615 -693615 decrea~
## 4 Burke 3551815 3099064 -452751 -452751 decrea~
## 5 Catawba 11986882. 8294862. -3692020. -3692020. decrea~
## 6 Gaston 21823121 17854499 -3968622 -3968622 decrea~
## 7 Greene 1741202. 1673625. -67577. -67577. decrea~
## 8 Guilford 36994870 31664164 -5330706 -5330706 decrea~
## 9 Harnett 6458474. 5558680. -899794. -899794. decrea~
## 10 Hyde 1589018. 1042602. -546416. -546416. decrea~
## 11 Jones 1273596 900836 -372760 -372760 decrea~
## 12 Lincoln 5301387 4417331 -884056 -884056 decrea~
## 13 Martin, Tyrrell & Washing~ 7828145 6028625 -1799520 -1799520 decrea~
## 14 Moore 5023285 3539264 -1484021 -1484021 decrea~
## 15 Person 4268046 3564406 -703640 -703640 decrea~
## 16 Rowan 5629894 5587848 -42046 -42046 decrea~
## 17 Swain 1845786. 1604354. -241432. -241432. decrea~
## 18 Union 8187069 7652358 -534711 -534711 decrea~
## 19 Wake 79391383 46664534 -32726849 -32726849 decrea~
## 20 Yadkin 2716611. 1467770. -1248841. -1248841. decrea~Did any counties see decrease in spending but increase in per-capita?
## character(0)No counties had a decrease in spending while also having an increase in per-capita spending.
When looking at Full-Time-Employees (FTEs), the year range has to change from 2007-2017, because the state does not report FTE data in even-numbered years, and the most recent report was in 2017.
Because the FTE is reported, we do have data for all 85 North Carolina local health departments.
Creating a dataframe to better examine the changes in FTEs from 2009-2017.
Of the 85 LHDs, 62 had fewer people working at them in 2017 than in 2009.
Wake County has cut 268 FTEs over that decade – 30% of its workforce. Meanwhile, Mecklenburg County more than doubled its workforce from 379 in 2007 to 825 in 2017. That’s the biggest increase in terms of both raw numbers of new employees as well as percent increase.
Ranking each LHD by their pct_change in FTE.
## # A tibble: 13 x 5
## lhd_name year2009 year2017 change pct_change
## <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Alexander County Health Department 45 29 -16 -0.356
## 2 Northampton County Health Department 75 48 -27 -0.36
## 3 Edgecombe County Health Department 119 76 -43 -0.361
## 4 Swain County Health Department 46 29 -17 -0.370
## 5 Hertford Health Department 71 40 -31 -0.437
## 6 Burke County Health Department 81 45 -36 -0.444
## 7 Surry County Health and Nutrition Center 222 115 -107 -0.482
## 8 Rutherford-Polk-McDowell District 136 67 -69 -0.507
## 9 Toe River District 124 60 -64 -0.516
## 10 Yadkin County Health Department 38 18 -20 -0.526
## 11 Buncombe County Health Department 233 110 -123 -0.528
## 12 Albemarle Regional Health Services 445 210 -235 -0.528
## 13 Hoke County Health Center 68 26 -42 -0.618Wake’s cuts were biggest in terms of raw numbers, but 13 health departments across the state saw their staffing drop by at least a third. Hyde County lost two thirds of its FTE. The Toe River Health District went from 200 to 60 people.
Filtering each county so only increases in FTEs are shown.
## # A tibble: 32 x 6
## county_name year2009 year2017 pop_diff pct_diff change
## <chr> <dbl> <dbl> <dbl> <dbl> <chr>
## 1 Alexander 37053 37143 90 0.00243 increase
## 2 Avery, Mitchell & Yancey 51320 51426 106 0.00207 increase
## 3 Brunswick 105226 131644 26418 0.251 increase
## 4 Burke 90521 91402 881 0.00973 increase
## 5 Cabarrus 175993 206988 30995 0.176 increase
## 6 Carteret 65670 68921 3251 0.0495 increase
## 7 Catawba 154610 157852 3242 0.0210 increase
## 8 Craven 101923 102492 569 0.00558 increase
## 9 Dare 33679 36199 2520 0.0748 increase
## 10 Forsyth 347333 372845 25512 0.0735 increase
## # ... with 22 more rowsFiltering to find all the health departments that had their number of FTEs decrease.
## # A tibble: 62 x 6
## lhd_name county_name year2009 year2017 change pct_change
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Alamance County Health De~ Alamance Coun~ 111 92 -19 -0.171
## 2 Albemarle Regional Health~ <NA> 445 210 -235 -0.528
## 3 Alexander County Health D~ Alexander Cou~ 45 29 -16 -0.356
## 4 Anson County Health Depar~ Anson County 24 19 -5 -0.208
## 5 Appalachian District Heal~ <NA> 92 79 -13 -0.141
## 6 Bladen County Health Depa~ Bladen County 65 56 -9 -0.138
## 7 Brunswick County Health D~ Brunswick Cou~ 87 60 -27 -0.310
## 8 Buncombe County Health De~ Buncombe Coun~ 233 110 -123 -0.528
## 9 Burke County Health Depar~ Burke County 81 45 -36 -0.444
## 10 Caldwell County Health De~ Caldwell Coun~ 114 78 -36 -0.316
## # ... with 52 more rowsBrunswick County’s population exploded by more than a third over those 10 years. At the same time, it’s public health staff was cut by 41%.
23 other counties or districts that gained population but lost public health FTEs: * Avery, Mitchell, Yancey (Toe River) * Catawba * (Public Health Authority of) Cabarrus * Craven * Dare * Forsyth * Franklin * Granville & Vance * Guilford * Iredell * Jackson * Lee * Lincoln * Moore * Person * Rowan * Swain * Transylvania * Union * Wayne
Ranking all the LHD’s by their number of FTEs in 2017.
## # A tibble: 85 x 2
## lhd_name fte
## <chr> <dbl>
## 1 Hyde County Health Department 14
## 2 Pamlico County Health Department 15
## 3 Yadkin County Health Department 18
## 4 Anson County Health Department 19
## 5 Jones County Health Department 19
## 6 Graham County Health Department 21
## 7 Greene County Health Department 26
## 8 Hoke County Health Center 26
## 9 Transylvania County Health Department 28
## 10 Alexander County Health Department 29
## # ... with 75 more rowsRanking the counties by their per capita expenditures.
## # A tibble: 126 x 3
## county_name year expenditures_infl_per_capita
## <chr> <int> <dbl>
## 1 Union 2018 33.6
## 2 Burke 2018 34.5
## 3 Moore 2018 36.4
## 4 Onslow 2018 37.3
## 5 Yadkin 2018 39.8
## 6 Rowan 2018 40.9
## 7 Stanly 2018 41.8
## 8 Harnett 2018 43.6
## 9 Wake 2018 44.5
## 10 Iredell 2018 47.2
## # ... with 116 more rowsThis isn’t just about poverty. Union County has the highest median household income in the state – about $86,000 – but in 2018 spent less per person on public health than any other county – just $32.92.
Creating a plot that shows the expenditures per capita for each county, with the size of the points being equal to the county population in 2018. 
Taking a closer look at Jones County’s FTE data. Jones is near the bottom in FTE ranking, and multiple interviews have noted Jones as a good example of an underfunded and understaffed health department.
## # A tibble: 8 x 2
## year fte
## <dbl> <dbl>
## 1 1999 12
## 2 2003 15
## 3 2005 60
## 4 2007 15
## 5 2009 17
## 6 2011 17
## 7 2013 21
## 8 2017 19Getting the inflation rates for each year.
## Warning in max(expenditures, na.rm = TRUE): no non-missing arguments to max;
## returning -Inf
## Warning in max(expenditures, na.rm = TRUE): no non-missing arguments to max;
## returning -Inf## Warning in max(expenditures_infl, na.rm = TRUE): no non-missing arguments to
## max; returning -Inf
## Warning in max(expenditures_infl, na.rm = TRUE): no non-missing arguments to
## max; returning -Inf## `summarise()` ungrouping output (override with `.groups` argument)## # A tibble: 9 x 4
## year total_raw total_infl multiplier2019
## <dbl> <dbl> <dbl> <dbl>
## 1 2010 3514717482 4277404753. 1.22
## 2 2011 3472819064 4093661998. 1.18
## 3 2012 -Inf -Inf NaN
## 4 2014 4938873972 5386128525. 1.09
## 5 2015 4128673660 4501194675. 1.09
## 6 2016 5253848049 5718313351. 1.09
## 7 2017 2772623026 2937887484. 1.06
## 8 2018 6985573518 7136717414. 1.02
## 9 2019 -Inf -Inf NaNThere was no state expenditure data collected in 2012, at least in this dataset.
## # A tibble: 51 x 3
## year expenditures expenditures_infl
## <dbl> <dbl> <dbl>
## 1 2012 NA NA
## 2 2012 NA NA
## 3 2012 NA NA
## 4 2012 NA NA
## 5 2012 NA NA
## 6 2012 NA NA
## 7 2012 NA NA
## 8 2012 NA NA
## 9 2012 NA NA
## 10 2012 NA NA
## # ... with 41 more rowsAdding the inflation rates to the inflation-adjusted CompiledLHDExpenditures dataframe.
## # A tibble: 829 x 8
## year lhd_name lhd_area_type county_name population expenditures
## <dbl> <chr> <chr> <chr> <dbl> <dbl>
## 1 2003 Alexand~ Single Alexander 34998 1781515.
## 2 2004 Alexand~ Single Alexander 34959 1995525.
## 3 2005 Alexand~ Single Alexander 35786 2091491.
## 4 2006 Alexand~ Single Alexander 36042 2247852.
## 5 2007 Alexand~ Single Alexander 36609 2513023.
## 6 2008 Alexand~ Single Alexander 36743 2638066.
## 7 2009 Alexand~ Single Alexander 37053 2687629.
## 8 2010 Alexand~ Single Alexander 37198 2763318.
## 9 2011 Alexand~ Single Alexander 37026 2784168.
## 10 2012 Alexand~ Single Alexander 37323 2911935.
## # ... with 819 more rows, and 2 more variables: expenditures_infl <dbl>,
## # expenditures_per_capita <dbl>GRAPHING
Loading data
## Warning: Missing column names filled in: 'X8' [8], 'X9' [9]LOLLIPOP CHART SHOWING COUNTIES THAT HAVE INCREASED IN PER CAPITA SPENDING VS. DECREASED FROM 2010-2018 (Using inflation adjusted numbers)
Loading the data
Create a new dataframe with a new column that shows the percent change in spending from 2010-2018
Create a lollipop chart that visualizes each health department’s percent change in expenditures 
Save the image as a png file
## Saving 7 x 5 in imageLOLLIPOP CHART OF FTE CHANGE PER HEALTH DEPARTMENT FROM 2007 TO 2017
Loading the data
Create the lollipop chart 
Save the image as a png file
CHANGE IN PER CAPITA SPENDING FOR MECKLENBURG, WAKE, UNION, JOHNSTON
Creating a new dataframe
Creating a line chart
LINE CHART SHOWING CHANGE IN HEALTH DEPARTMENT SPENDING PER CAPITA FROM 2014-2018Creating a new dataframe
Creating a line chart
CREATING INTERACTIVE MAP Colored in by per capita spending On hover also shows population, expenditure and fte percent change
Changing the dataframe to calculate percent change
Added rows for multi-county data in Google Sheets so those counties get filled in on map. Loading in that data.
Downloading shapefile of NC counties
Joining data with fte data
Leaflet map where health districts get filled in
## Warning: sf layer has inconsistent datum (+proj=longlat +datum=NAD83 +no_defs).
## Need '+proj=longlat +datum=WGS84'Line chart showing change in national per capita spending at state level and NC per capita spending at state level
## Parsed with column specification:
## cols(
## year = col_double(),
## fips_state = col_character(),
## state_code = col_character(),
## population = col_double(),
## fte = col_double(),
## expenditures = col_double(),
## expenditures_infl = col_double(),
## fte_per_100000 = col_double(),
## expenditures_per_capita = col_double(),
## expenditures_infl_per_capita = col_double()
## )## Warning: Tried to calculate with group_by(), but the calculation failed.
## Falling back to ungrouped filter operation...## label_key: state_code## Warning: Removed 51 row(s) containing missing values (geom_path).## Warning: Removed 1 row(s) containing missing values (geom_path).## Warning: Removed 1 rows containing missing values (geom_label_repel).## Warning in grid.Call(C_stringMetric, as.graphicsAnnot(x$label)): font family not
## found in Windows font database
## Warning in grid.Call(C_stringMetric, as.graphicsAnnot(x$label)): font family not
## found in Windows font database
## Warning in grid.Call(C_stringMetric, as.graphicsAnnot(x$label)): font family not
## found in Windows font database## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database## Warning in grid.Call.graphics(C_text, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, : font
## family not found in Windows font database
Create a new dataframe for Jones County data
Create a line chart showing Jones County FTE per capita change over time
## Warning: `arrange_()` is deprecated as of dplyr 0.7.0.
## Please use `arrange()` instead.
## See vignette('programming') for more help
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_warnings()` to see where this warning was generated.