Week 8: Apply it to your data 7

Import your data

bee_colonies <- read_excel("../00_data/MyData3.xlsx")

set.seed(123)
bee_colonies_smaller <- bee_colonies %>%
    select(year, state, colony_lost) %>%
        sample_n(10)

bee_colonies_smaller

## # A tibble: 10 × 3
##     year state      colony_lost
##    <dbl> <chr>            <dbl>
##  1  2017 Utah              2700
##  2  2017 Vermont            170
##  3  2015 Texas            25000
##  4  2017 Hawaii             130
##  5  2016 Florida          45000
##  6  2019 Wyoming           3300
##  7  2021 Kansas            1400
##  8  2020 California       69000
##  9  2018 Florida          30000
## 10  2018 Texas            22000

Pivoting

long to wide form

beecolony_wide <- bee_colonies_smaller %>% pivot_wider(names_from = year, values_from = colony_lost)

beecolony_wide

## # A tibble: 8 × 8
##   state      `2017` `2015` `2016` `2019` `2021` `2020` `2018`
##   <chr>       <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
## 1 Utah         2700     NA     NA     NA     NA     NA     NA
## 2 Vermont       170     NA     NA     NA     NA     NA     NA
## 3 Texas          NA  25000     NA     NA     NA     NA  22000
## 4 Hawaii        130     NA     NA     NA     NA     NA     NA
## 5 Florida        NA     NA  45000     NA     NA     NA  30000
## 6 Wyoming        NA     NA     NA   3300     NA     NA     NA
## 7 Kansas         NA     NA     NA     NA   1400     NA     NA
## 8 California     NA     NA     NA     NA     NA  69000     NA

wide to long form

beecolony_long <- beecolony_wide %>% 
    
    pivot_longer(cols = `2017`:`2018`,
                 names_to = "year",
                 values_to = "colony_lost",
                 values_drop_na = TRUE)

beecolony_long

## # A tibble: 10 × 3
##    state      year  colony_lost
##    <chr>      <chr>       <dbl>
##  1 Utah       2017         2700
##  2 Vermont    2017          170
##  3 Texas      2015        25000
##  4 Texas      2018        22000
##  5 Hawaii     2017          130
##  6 Florida    2016        45000
##  7 Florida    2018        30000
##  8 Wyoming    2019         3300
##  9 Kansas     2021         1400
## 10 California 2020        69000

Separating and Uniting

Unite two columns

beecolony_united <- bee_colonies %>%
    
    unite(col = "month & state", months:state, sep = "/", remove = TRUE)

beecolony_united

## # A tibble: 1,222 × 9
##     year `month & state`      colony_size colony_max colony_lost colony_lost_pct
##    <dbl> <chr>                      <dbl> <chr>            <dbl>           <dbl>
##  1  2015 January-March/Alaba…        7000 7000              1800              26
##  2  2015 January-March/Arizo…       35000 35000             4600              13
##  3  2015 January-March/Arkan…       13000 14000             1500              11
##  4  2015 January-March/Calif…     1440000 1690000         255000              15
##  5  2015 January-March/Color…        3500 12500             1500              12
##  6  2015 January-March/Conne…        3900 3900               870              22
##  7  2015 January-March/Flori…      305000 315000           42000              13
##  8  2015 January-March/Georg…      104000 105000           14500              14
##  9  2015 January-March/Hawaii       10500 10500              380               4
## 10  2015 January-March/Idaho        81000 88000             3700               4
## # ℹ 1,212 more rows
## # ℹ 3 more variables: colony_added <chr>, colony_reno <chr>,
## #   colony_reno_pct <chr>

Separate a column

beecolony_united %>%
    
    separate(col = `month & state`, into = c("months", "state"), sep = "/")

## # A tibble: 1,222 × 10
##     year months        state  colony_size colony_max colony_lost colony_lost_pct
##    <dbl> <chr>         <chr>        <dbl> <chr>            <dbl>           <dbl>
##  1  2015 January-March Alaba…        7000 7000              1800              26
##  2  2015 January-March Arizo…       35000 35000             4600              13
##  3  2015 January-March Arkan…       13000 14000             1500              11
##  4  2015 January-March Calif…     1440000 1690000         255000              15
##  5  2015 January-March Color…        3500 12500             1500              12
##  6  2015 January-March Conne…        3900 3900               870              22
##  7  2015 January-March Flori…      305000 315000           42000              13
##  8  2015 January-March Georg…      104000 105000           14500              14
##  9  2015 January-March Hawaii       10500 10500              380               4
## 10  2015 January-March Idaho        81000 88000             3700               4
## # ℹ 1,212 more rows
## # ℹ 3 more variables: colony_added <chr>, colony_reno <chr>,
## #   colony_reno_pct <chr>

Missing Values

bee_colonies_smaller %>%
    complete(year, state)

## # A tibble: 56 × 3
##     year state      colony_lost
##    <dbl> <chr>            <dbl>
##  1  2015 California          NA
##  2  2015 Florida             NA
##  3  2015 Hawaii              NA
##  4  2015 Kansas              NA
##  5  2015 Texas            25000
##  6  2015 Utah                NA
##  7  2015 Vermont             NA
##  8  2015 Wyoming             NA
##  9  2016 California          NA
## 10  2016 Florida          45000
## # ℹ 46 more rows