Project 3

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.4.4     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.1
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(openintro)

Loading required package: airports
Loading required package: cherryblossom
Loading required package: usdata

library(usdata)

Look at the data

head(county_complete)

  fips   state           name pop2000 pop2010 pop2011 pop2012 pop2013 pop2014
1 1001 Alabama Autauga County   43671   54571   55199   54927   54695   54864
2 1003 Alabama Baldwin County  140415  182265  186534  190048  194736  199064
3 1005 Alabama Barbour County   29038   27457   27351   27175   26947   26749
4 1007 Alabama    Bibb County   20826   22915   22745   22658   22503   22533
5 1009 Alabama  Blount County   51024   57322   57562   57595   57623   57546
6 1011 Alabama Bullock County   11714   10914   10675   10612   10549   10673
  pop2015 pop2016 pop2017 age_under_5_2010 age_under_5_2017 age_under_18_2010
1   54838   55278   55504              6.6              5.7              26.8
2  202863  207509  212628              6.1              5.7              23.0
3   26264   25774   25270              6.2              5.5              21.9
4   22561   22633   22668              6.0              5.7              22.7
5   57590   57562   58013              6.3              6.1              24.6
6   10419   10441   10309              6.8              5.8              22.3
  age_over_65_2010 age_over_65_2017 median_age_2017 female_2010 white_2010
1             12.0             14.3            37.8        51.3       78.5
2             16.8             19.0            42.6        51.1       85.7
3             14.2             17.4            39.7        46.9       48.0
4             12.7             15.1            39.8        46.3       75.8
5             14.7             17.4            40.9        50.5       92.6
6             13.5             15.2            40.8        45.8       23.0
  black_2010 black_2017 native_2010 native_2017 asian_2010 asian_2017
1       17.7       9.55         0.4        0.15        0.9       0.47
2        9.4       4.77         0.7        0.41        0.7       0.35
3       46.9      24.02         0.4        0.10        0.4       0.31
4       22.0      11.03         0.3        0.18        0.1       0.00
5        1.3       0.79         0.5        0.18        0.2       0.07
6       70.2      37.82         0.2        0.52        0.2       0.35
  pac_isl_2010 pac_isl_2017 other_single_race_2017 two_plus_races_2010
1           NA         0.04                   0.65                 1.6
2           NA         0.00                   0.39                 1.5
3           NA         0.00                   1.87                 0.9
4           NA         0.00                   0.02                 0.9
5           NA         0.00                   0.37                 1.2
6           NA         0.00                   0.01                 0.8
  two_plus_races_2017 hispanic_2010 hispanic_2017 white_not_hispanic_2010
1                0.84           2.4          2.67                    77.2
2                0.82           4.4          4.44                    83.5
3                0.41           5.1          4.21                    46.8
4                0.42           1.8          2.35                    75.0
5                0.85           8.1          9.01                    88.9
6                0.33           7.1          0.33                    21.9
  white_not_hispanic_2017 speak_english_only_2017 no_move_in_one_plus_year_2010
1                   75.42                    96.2                          86.3
2                   83.08                    94.5                          83.0
3                   45.74                    94.3                          83.0
4                   74.62                    97.8                          90.5
5                   87.37                    92.3                          87.2
6                   21.61                    97.2                          88.5
  foreign_born_2010 foreign_spoken_at_home_2010 women_16_to_50_birth_rate_2017
1               2.0                         3.7                            7.4
2               3.6                         5.5                            5.1
3               2.8                         4.7                            7.2
4               0.7                         1.5                            7.6
5               4.7                         7.2                            5.6
6               1.1                         3.8                            3.5
  hs_grad_2010 hs_grad_2016 hs_grad_2017 some_college_2016 some_college_2017
1         85.3         87.6         87.7              28.7              29.1
2         87.6         90.0         90.2              31.8              31.6
3         71.9         73.8         73.1              26.0              25.5
4         74.5         80.7         82.1              26.9              25.0
5         74.7         80.0         79.8              34.0              34.4
6         74.7         66.6         71.4              22.2              21.3
  bachelors_2010 bachelors_2016 bachelors_2017 veterans_2010 veterans_2017
1           21.7           24.6           25.0          5817          12.6
2           26.8           29.5           30.7         20396          11.9
3           13.5           12.9           12.0          2327           8.0
4           10.0           12.0           13.2          1883           7.4
5           12.5           13.1           13.1          4072           9.6
6           12.0           10.3           13.4           943           4.5
  mean_work_travel_2010 mean_work_travel_2017 broadband_2017 computer_2017
1                  25.1                  25.8           76.6          86.2
2                  25.8                  27.0           74.5          86.9
3                  23.8                  23.4           57.2          73.4
4                  28.3                  30.0           62.0          74.8
5                  33.2                  35.0           65.8          78.2
6                  28.1                  29.8           49.4          64.2
  housing_units_2010 homeownership_2010 housing_multi_unit_2010
1              22135               77.5                     7.2
2             104061               76.7                    22.6
3              11829               68.0                    11.1
4               8981               82.9                     6.6
5              23887               82.0                     3.7
6               4493               76.9                     9.9
  median_val_owner_occupied_2010 households_2010 households_2017
1                         133900           19718           21054
2                         177200           69476           76133
3                          88200            9795            9191
4                          81200            7441            6916
5                         113700           20605           20690
6                          66300            3732            3670
  persons_per_household_2010 persons_per_household_2017 per_capita_income_2010
1                       2.70                       2.59                  24568
2                       2.50                       2.63                  26469
3                       2.52                       2.54                  15875
4                       3.02                       2.97                  19918
5                       2.73                       2.76                  21070
6                       2.85                       2.74                  20289
  per_capita_income_2017 metro_2013 median_household_income_2010
1               27841.70          1                        53255
2               27779.85          1                        50147
3               17891.73          0                        33219
4               20572.05          1                        41770
5               21367.39          1                        45549
6               15444.16          0                        31602
  median_household_income_2016 median_household_income_2017
1                        54487                        55317
2                        56460                        52562
3                        32884                        33368
4                        43079                        43404
5                        47213                        47412
6                        34278                        29655
  private_nonfarm_establishments_2009 private_nonfarm_employment_2009
1                                 877                           10628
2                                4812                           52233
3                                 522                            7990
4                                 318                            2927
5                                 749                            6968
6                                 120                            1919
  percent_change_private_nonfarm_employment_2009
1                                           16.6
2                                           17.4
3                                          -27.0
4                                          -14.0
5                                          -11.4
6                                          -18.5
  nonemployment_establishments_2009 firms_2007 black_owned_firms_2007
1                              2971       4067                   15.2
2                             14175      19035                    2.7
3                              1527       1667                     NA
4                              1192       1385                   14.9
5                              3501       4458                     NA
6                               390        417                     NA
  native_owned_firms_2007 asian_owned_firms_2007 pac_isl_owned_firms_2007
1                      NA                    1.3                       NA
2                     0.4                    1.0                       NA
3                      NA                     NA                       NA
4                      NA                     NA                       NA
5                      NA                     NA                       NA
6                      NA                     NA                       NA
  hispanic_owned_firms_2007 women_owned_firms_2007 manufacturer_shipments_2007
1                       0.7                   31.7                          NA
2                       1.3                   27.3                     1410273
3                        NA                   27.0                          NA
4                        NA                     NA                           0
5                        NA                   23.2                      341544
6                        NA                   38.8                          NA
  mercent_whole_sales_2007 sales_2007 sales_per_capita_2007
1                       NA     598175                 12003
2                       NA    2966489                 17166
3                       NA     188337                  6334
4                       NA     124707                  5804
5                       NA     319700                  5622
6                       NA      43810                  3995
  accommodation_food_service_2007 building_permits_2010 fed_spending_2009
1                           88157                   191            331142
2                          436955                   696           1119082
3                              NA                    10            240308
4                           10757                     8            163201
5                           20941                    18            294114
6                            3670                     1            108846
  area_2010 density_2010 smoking_ban_2010 poverty_2010 poverty_2016
1    594.44         91.8             none         10.6         13.5
2   1589.78        114.6             none         12.2         11.7
3    884.88         31.0          partial         25.0         29.9
4    622.58         36.8             none         12.6         20.1
5    644.78         88.9             none         13.4         14.1
6    622.81         17.5             none         25.3         32.6
  poverty_2017 poverty_age_under_5_2017 poverty_age_under_18_2017
1         13.7                     17.2                      20.0
2         11.8                     19.4                      15.9
3         27.2                     56.8                      44.9
4         15.2                     21.6                      25.9
5         15.6                     29.5                      25.3
6         28.5                     59.7                      50.2
  civilian_labor_force_2007 employed_2007 unemployed_2007
1                     24383         23577             806
2                     82659         80099            2560
3                     10334          9684             650
4                      8791          8432             359
5                     26629         25780             849
6                      3653          3308             345
  unemployment_rate_2007 civilian_labor_force_2008 employed_2008
1                   3.31                     24687         23420
2                   3.10                     83223         79372
3                   6.29                     10161          9267
4                   4.08                      8749          8241
5                   3.19                     26698         25453
6                   9.44                      3634          3251
  unemployed_2008 unemployment_rate_2008 civilian_labor_force_2009
1            1267                   5.13                     24703
2            3851                   4.63                     82451
3             894                   8.80                     10003
4             508                   5.81                      8742
5            1245                   4.66                     26480
6             383                  10.54                      3739
  employed_2009 unemployed_2009 unemployment_rate_2009
1         22301            2402                   9.72
2         74403            8048                   9.76
3          8572            1431                  14.31
4          7581            1161                  13.28
5         23832            2648                  10.00
6          3156             583                  15.59
  civilian_labor_force_2010 employed_2010 unemployed_2010
1                     25713         23431            2282
2                     83459         75120            8339
3                     10221          8959            1262
4                      8934          7914            1020
5                     24906         22460            2446
6                      4941          4356             585
  unemployment_rate_2010 civilian_labor_force_2011 employed_2011
1                   8.87                     25836         23677
2                   9.99                     85045         77418
3                  12.35                      9849          8712
4                  11.42                      8933          7996
5                   9.82                     25123         22939
6                  11.84                      4833          4272
  unemployed_2011 unemployment_rate_2011 civilian_labor_force_2012
1            2159                   8.36                     25740
2            7627                   8.97                     84414
3            1137                  11.54                      9362
4             937                  10.49                      8798
5            2184                   8.69                     24960
6             561                  11.61                      4736
  employed_2012 unemployed_2012 unemployment_rate_2012
1         23961            1779                   6.91
2         78065            6349                   7.52
3          8283            1079                  11.53
4          8047             751                   8.54
5         23244            1716                   6.88
6          4245             491                  10.37
  civilian_labor_force_2013 employed_2013 unemployed_2013
1                     25810         24205            1605
2                     85280         79626            5654
3                      9099          8168             931
4                      8705          8016             689
5                     24887         23325            1562
6                      4778          4331             447
  unemployment_rate_2013 civilian_labor_force_2014 employed_2014
1                   6.22                     25602         24107
2                   6.63                     86415         81115
3                  10.23                      8848          7916
4                   7.91                      8562          7945
5                   6.28                     24535         23032
6                   9.36                      4733          4315
  unemployed_2014 unemployment_rate_2014 civilian_labor_force_2015
1            1495                   5.84                     25602
2            5300                   6.13                     87705
3             932                  10.53                      8609
4             617                   7.21                      8572
5            1503                   6.13                     24473
6             418                   8.83                      4777
  employed_2015 unemployed_2015 unemployment_rate_2015
1         24272            1330                   5.19
2         82843            4862                   5.54
3          7844             765                   8.89
4          8005             567                   6.61
5         23152            1321                   5.40
6          4398             379                   7.93
  civilian_labor_force_2016 employed_2016 unemployed_2016
1                     25918         24593            1325
2                     90500         85656            4844
3                      8402          7700             702
4                      8607          8050             557
5                     24576         23248            1328
6                      4824          4493             331
  unemployment_rate_2016 uninsured_2017 uninsured_age_under_6_2017
1                   5.11            8.8                        1.1
2                   5.35           10.8                        2.4
3                   8.36           12.3                        4.1
4                   6.47            8.1                        2.7
5                   5.40           11.0                        3.9
6                   6.86           14.1                          0
  uninsured_age_under_19_2017 uninsured_age_over_74_2017
1                         3.2                        0.0
2                         3.5                        0.4
3                         4.6                        0.0
4                         1.8                        0.0
5                         5.7                        0.2
6                         1.2                        0.0
  civilian_labor_force_2017 employed_2017 unemployed_2017
1                     25909         24908            1001
2                     91567         87915            3652
3                      8236          7750             486
4                      8506          8133             373
5                     24494         23509             985
6                      4812          4575             237
  unemployment_rate_2017 age_over_18_2019 age_over_65_2019 age_over_85_2019
1                   3.86             76.2             15.0              1.6
2                   3.99             78.3             20.0              1.9
3                   5.90             79.1             18.6              1.6
4                   4.39             79.4             15.9              2.0
5                   4.02             76.8             17.9              1.8
6                   4.93             79.2             16.0              1.7
  age_under_5_2019 asian_2019 avg_family_size_2019 bachelors_2019 black_2019
1              5.8        1.0                 3.09           26.6       19.0
2              5.5        0.9                 3.24           31.9        9.3
3              5.3        0.5                 3.01           11.6       47.6
4              5.8        0.1                 3.74           10.4       22.3
5              5.9        0.4                 3.33           13.1        1.6
6              5.3        0.5                 3.30           12.1       74.8
  hispanic_2019 household_has_broadband_2019 household_has_computer_2019
1           2.8                         80.6                        73.0
2           4.6                         81.8                        76.3
3           4.4                         60.5                        51.9
4           2.6                         69.2                        54.7
5           9.3                         73.0                        63.5
6           2.6                         60.1                        52.5
  household_has_smartphone_2019 households_2019
1                          78.4           21397
2                          81.7           80930
3                          64.2            9345
4                          66.6            6891
5                          70.1           20847
6                          70.6            3521
  households_speak_asian_or_pac_isl_2019 households_speak_limited_english_2019
1                                    1.8                                   0.7
2                                    0.6                                   1.2
3                                    0.6                                   1.6
4                                    0.0                                   0.6
5                                    0.1                                   1.8
6                                    0.4                                   1.4
  households_speak_other_2019 households_speak_other_indo_euro_lang_2019
1                         0.2                                        0.3
2                         0.0                                        1.8
3                         0.0                                        1.1
4                         0.0                                        0.5
5                         0.2                                        0.9
6                         0.0                                        1.6
  households_speak_spanish_2019 housing_mobile_homes_2019
1                           2.9                      26.7
2                           4.6                      24.8
3                           5.2                      39.1
4                           1.9                      25.6
5                           6.6                      21.2
6                           3.6                      28.9
  housing_one_unit_structures_2019 housing_two_unit_structures_2019
1                             17.3                             73.3
2                             11.5                             75.2
3                             26.1                             60.9
4                             29.7                             74.4
5                             24.0                             78.8
6                             39.3                             71.1
  hs_grad_2019 mean_household_income_2019 mean_work_travel_2019 median_age_2019
1         88.5                      75326                  24.4            38.2
2         90.8                      80986                    NA            43.0
3         73.2                      47068                    NA            40.4
4         79.1                      60182                    NA            40.9
5         80.5                      65639                    NA            40.7
6         74.7                      48571                  27.2            40.2
  median_household_income_2019 median_individual_income_2019
1                        58731                         29725
2                        58320                         29802
3                        32525                         17963
4                        47542                         21958
5                        49358                         26976
6                        37785                         21571
  median_individual_income_age_25plus_2019 native_2019 other_single_race_2019
1                                    40778         0.3                    0.7
2                                    37897         0.8                    1.1
3                                    27434         0.3                    3.6
4                                    28789         0.1                    0.0
5                                    39004         0.1                    0.9
6                                    29516         0.0                    2.0
  pac_isl_2019 per_capita_income_2019 persons_per_household_2019 pop_2019
1            0                  29819                       2.56    55380
2            0                  32626                       2.59   212830
3            0                  18473                       2.41    25361
4            0                  20778                       2.99    22493
5            0                  24747                       2.74    57681
6            0                  20877                       2.79    10248
  poverty_2019 poverty_65_and_over_2019 poverty_under_18_2019
1         15.2                      8.7                  23.2
2         10.4                      7.4                  13.4
3         30.7                     16.8                  50.1
4           NA                       NA                    NA
5         13.6                     10.9                  18.4
6           NA                       NA                    NA
  two_plus_races_2019 unemployment_rate_2019 uninsured_2019
1                 2.2                    3.5            7.1
2                 1.7                    4.0            8.9
3                 1.2                    9.4           11.3
4                 0.6                    7.0           10.7
5                 1.6                    3.1           10.8
6                 0.8                    4.1           11.4
  uninsured_65_and_older_2019 uninsured_under_19_2019 uninsured_under_6_2019
1                         0.0                     1.7                    1.7
2                         0.3                     3.8                    2.2
3                         0.3                     3.3                    3.4
4                         0.0                     2.0                    4.5
5                         0.2                     5.9                    6.1
6                         0.0                     1.0                    2.1
  veterans_2019 white_2019 white_not_hispanic_2019
1          12.6       76.8                    74.6
2          11.8       86.2                    83.1
3           6.6       46.8                    45.8
4           8.0       76.8                    74.5
5           7.7       95.5                    86.9
6           3.3       21.9                    21.4

head(county)

# A tibble: 6 × 15
  name           state  pop2000 pop2010 pop2017 pop_change poverty homeownership
  <chr>          <fct>    <dbl>   <dbl>   <int>      <dbl>   <dbl>         <dbl>
1 Autauga County Alaba…   43671   54571   55504       1.48    13.7          77.5
2 Baldwin County Alaba…  140415  182265  212628       9.19    11.8          76.7
3 Barbour County Alaba…   29038   27457   25270      -6.22    27.2          68  
4 Bibb County    Alaba…   20826   22915   22668       0.73    15.2          82.9
5 Blount County  Alaba…   51024   57322   58013       0.68    15.6          82  
6 Bullock County Alaba…   11714   10914   10309      -2.28    28.5          76.9
# ℹ 7 more variables: multi_unit <dbl>, unemployment_rate <dbl>, metro <fct>,
#   median_edu <fct>, per_capita_income <dbl>, median_hh_income <int>,
#   smoking_ban <fct>

Shortening the columns

tidycounty <- county |>
  # pivot longer is used when there is data in the column names typically years
  pivot_longer(
    cols = starts_with("pop2"),
    names_to = "year",
    values_to = "population",
  ) |> 
  mutate(
    year = parse_number(year)
    # or you could do (Year = str_sub(Year,4,7)) which takes a new string from position 4 to 7
  )

tidycounty |>
  relocate(name, state, year, population)

# A tibble: 9,426 × 14
   name       state  year population pop_change poverty homeownership multi_unit
   <chr>      <fct> <dbl>      <dbl>      <dbl>   <dbl>         <dbl>      <dbl>
 1 Autauga C… Alab…  2000      43671       1.48    13.7          77.5        7.2
 2 Autauga C… Alab…  2010      54571       1.48    13.7          77.5        7.2
 3 Autauga C… Alab…  2017      55504       1.48    13.7          77.5        7.2
 4 Baldwin C… Alab…  2000     140415       9.19    11.8          76.7       22.6
 5 Baldwin C… Alab…  2010     182265       9.19    11.8          76.7       22.6
 6 Baldwin C… Alab…  2017     212628       9.19    11.8          76.7       22.6
 7 Barbour C… Alab…  2000      29038      -6.22    27.2          68         11.1
 8 Barbour C… Alab…  2010      27457      -6.22    27.2          68         11.1
 9 Barbour C… Alab…  2017      25270      -6.22    27.2          68         11.1
10 Bibb Coun… Alab…  2000      20826       0.73    15.2          82.9        6.6
# ℹ 9,416 more rows
# ℹ 6 more variables: unemployment_rate <dbl>, metro <fct>, median_edu <fct>,
#   per_capita_income <dbl>, median_hh_income <int>, smoking_ban <fct>

Tidying general population

county_v1 <- county_complete |>
  pivot_longer(
    cols = starts_with("pop"),
    names_to = "year",
    values_to = "population",
    values_drop_na = TRUE
  ) |> 
  mutate(
    year = parse_number(year)
  )
head(county_v1)

# A tibble: 6 × 180
   fips state   name         age_under_5_2010 age_under_5_2017 age_under_18_2010
  <dbl> <chr>   <chr>                   <dbl>            <dbl>             <dbl>
1  1001 Alabama Autauga Cou…              6.6              5.7              26.8
2  1001 Alabama Autauga Cou…              6.6              5.7              26.8
3  1001 Alabama Autauga Cou…              6.6              5.7              26.8
4  1001 Alabama Autauga Cou…              6.6              5.7              26.8
5  1001 Alabama Autauga Cou…              6.6              5.7              26.8
6  1001 Alabama Autauga Cou…              6.6              5.7              26.8
# ℹ 174 more variables: age_over_65_2010 <dbl>, age_over_65_2017 <dbl>,
#   median_age_2017 <dbl>, female_2010 <dbl>, white_2010 <dbl>,
#   black_2010 <dbl>, black_2017 <dbl>, native_2010 <dbl>, native_2017 <dbl>,
#   asian_2010 <dbl>, asian_2017 <dbl>, pac_isl_2010 <dbl>, pac_isl_2017 <dbl>,
#   other_single_race_2017 <dbl>, two_plus_races_2010 <dbl>,
#   two_plus_races_2017 <dbl>, hispanic_2010 <dbl>, hispanic_2017 <dbl>,
#   white_not_hispanic_2010 <dbl>, white_not_hispanic_2017 <dbl>, …