Import your data

data <- read_excel("../00_data/myData.xlsx")
data
## # A tibble: 21,560 × 12
##     year gnis_id geometry   metad…¹ numbe…² parkn…³ region state unit_…⁴ unit_…⁵
##    <dbl>   <dbl> <chr>      <chr>     <dbl> <chr>   <chr>  <chr> <chr>   <chr>  
##  1  1904 1163670 POLYGON    NA            1 Crater… PW     OR    CRLA    Crater…
##  2  1941 1531834 MULTIPOLY… NA            1 Lake R… PW     WA    LARO    Lake R…
##  3  1961 2055170 MULTIPOLY… NA            1 Lewis … PW     WA    LEWI    Lewis …
##  4  1935 1530459 MULTIPOLY… NA            1 Olympic PW     WA    OLYM    Olympi…
##  5  1982  277263 POLYGON    NA            1 Santa … PW     CA    SAMO    Santa …
##  6  1919  578853 MULTIPOLY… NA            1 NA      NE     ME    ACAD    Acadia…
##  7  1969 1329499 MULTIPOLY… NA            1 NA      IM     TX    AMIS    Amista…
##  8  1967  589056 POLYGON    NA            1 NA      NE     MD    ASIS    Assate…
##  9  1944 1377082 POLYGON    NA            1 NA      IM     TX    BIBE    Big Be…
## 10  1989  302659 POLYGON    NA            1 NA      SE     FL    BICY    Big Cy…
## # … with 21,550 more rows, 2 more variables: unit_type <chr>, visitors <dbl>,
## #   and abbreviated variable names ¹​metadata, ²​number_of_records, ³​parkname,
## #   ⁴​unit_code, ⁵​unit_name

Pivoting

Wide to long form

data_small <- data %>%
    
    select(year, geometry, parkname, visitors) %>%
    filter(parkname %in% c("Glacier Bay", "Great Sand Dunes")) %>%
    head(n = 10) %>%
    slice(-4, -7)
    
data_wider <- data_small %>% pivot_wider(names_from = parkname, values_from = visitors)

data_wider
## # A tibble: 7 × 4
##    year geometry     `Glacier Bay` `Great Sand Dunes`
##   <dbl> <chr>                <dbl>              <dbl>
## 1  1956 POLYGON               2200                 NA
## 2  1956 MULTIPOLYGON          2200                 NA
## 3  1932 POLYGON                 NA                500
## 4    NA POLYGON           11949920           13526909
## 5    NA MULTIPOLYGON      11949920                 NA
## 6  2016 POLYGON             520171                 NA
## 7  2016 MULTIPOLYGON        520171                 NA

Long to wide form

data_longer <- data_wider %>%
    
    pivot_longer(c("Glacier Bay", "Great Sand Dunes"), names_to = "parkname", values_to = "visitors")

data_longer
## # A tibble: 14 × 4
##     year geometry     parkname         visitors
##    <dbl> <chr>        <chr>               <dbl>
##  1  1956 POLYGON      Glacier Bay          2200
##  2  1956 POLYGON      Great Sand Dunes       NA
##  3  1956 MULTIPOLYGON Glacier Bay          2200
##  4  1956 MULTIPOLYGON Great Sand Dunes       NA
##  5  1932 POLYGON      Glacier Bay            NA
##  6  1932 POLYGON      Great Sand Dunes      500
##  7    NA POLYGON      Glacier Bay      11949920
##  8    NA POLYGON      Great Sand Dunes 13526909
##  9    NA MULTIPOLYGON Glacier Bay      11949920
## 10    NA MULTIPOLYGON Great Sand Dunes       NA
## 11  2016 POLYGON      Glacier Bay        520171
## 12  2016 POLYGON      Great Sand Dunes       NA
## 13  2016 MULTIPOLYGON Glacier Bay        520171
## 14  2016 MULTIPOLYGON Great Sand Dunes       NA

Separating and Uniting

N/A

Unite two columns

data_united <- data %>%
    
    unite(col = "location", c(state,region), sep = "/")
data_united
## # A tibble: 21,560 × 11
##     year gnis_id geome…¹ metad…² numbe…³ parkn…⁴ locat…⁵ unit_…⁶ unit_…⁷ unit_…⁸
##    <dbl>   <dbl> <chr>   <chr>     <dbl> <chr>   <chr>   <chr>   <chr>   <chr>  
##  1  1904 1163670 POLYGON NA            1 Crater… OR/PW   CRLA    Crater… Nation…
##  2  1941 1531834 MULTIP… NA            1 Lake R… WA/PW   LARO    Lake R… Nation…
##  3  1961 2055170 MULTIP… NA            1 Lewis … WA/PW   LEWI    Lewis … Nation…
##  4  1935 1530459 MULTIP… NA            1 Olympic WA/PW   OLYM    Olympi… Nation…
##  5  1982  277263 POLYGON NA            1 Santa … CA/PW   SAMO    Santa … Nation…
##  6  1919  578853 MULTIP… NA            1 NA      ME/NE   ACAD    Acadia… Nation…
##  7  1969 1329499 MULTIP… NA            1 NA      TX/IM   AMIS    Amista… Nation…
##  8  1967  589056 POLYGON NA            1 NA      MD/NE   ASIS    Assate… Nation…
##  9  1944 1377082 POLYGON NA            1 NA      TX/IM   BIBE    Big Be… Nation…
## 10  1989  302659 POLYGON NA            1 NA      FL/SE   BICY    Big Cy… Nation…
## # … with 21,550 more rows, 1 more variable: visitors <dbl>, and abbreviated
## #   variable names ¹​geometry, ²​metadata, ³​number_of_records, ⁴​parkname,
## #   ⁵​location, ⁶​unit_code, ⁷​unit_name, ⁸​unit_type

Separate a column

data_united %>%
    
    separate(col = location, into = c("state", "region"), sep = "/")
## # A tibble: 21,560 × 12
##     year gnis_id geometry   metad…¹ numbe…² parkn…³ state region unit_…⁴ unit_…⁵
##    <dbl>   <dbl> <chr>      <chr>     <dbl> <chr>   <chr> <chr>  <chr>   <chr>  
##  1  1904 1163670 POLYGON    NA            1 Crater… OR    PW     CRLA    Crater…
##  2  1941 1531834 MULTIPOLY… NA            1 Lake R… WA    PW     LARO    Lake R…
##  3  1961 2055170 MULTIPOLY… NA            1 Lewis … WA    PW     LEWI    Lewis …
##  4  1935 1530459 MULTIPOLY… NA            1 Olympic WA    PW     OLYM    Olympi…
##  5  1982  277263 POLYGON    NA            1 Santa … CA    PW     SAMO    Santa …
##  6  1919  578853 MULTIPOLY… NA            1 NA      ME    NE     ACAD    Acadia…
##  7  1969 1329499 MULTIPOLY… NA            1 NA      TX    IM     AMIS    Amista…
##  8  1967  589056 POLYGON    NA            1 NA      MD    NE     ASIS    Assate…
##  9  1944 1377082 POLYGON    NA            1 NA      TX    IM     BIBE    Big Be…
## 10  1989  302659 POLYGON    NA            1 NA      FL    SE     BICY    Big Cy…
## # … with 21,550 more rows, 2 more variables: unit_type <chr>, visitors <dbl>,
## #   and abbreviated variable names ¹​metadata, ²​number_of_records, ³​parkname,
## #   ⁴​unit_code, ⁵​unit_name

Missing Values

data %>%
    
    fill(region)
## # A tibble: 21,560 × 12
##     year gnis_id geometry   metad…¹ numbe…² parkn…³ region state unit_…⁴ unit_…⁵
##    <dbl>   <dbl> <chr>      <chr>     <dbl> <chr>   <chr>  <chr> <chr>   <chr>  
##  1  1904 1163670 POLYGON    NA            1 Crater… PW     OR    CRLA    Crater…
##  2  1941 1531834 MULTIPOLY… NA            1 Lake R… PW     WA    LARO    Lake R…
##  3  1961 2055170 MULTIPOLY… NA            1 Lewis … PW     WA    LEWI    Lewis …
##  4  1935 1530459 MULTIPOLY… NA            1 Olympic PW     WA    OLYM    Olympi…
##  5  1982  277263 POLYGON    NA            1 Santa … PW     CA    SAMO    Santa …
##  6  1919  578853 MULTIPOLY… NA            1 NA      NE     ME    ACAD    Acadia…
##  7  1969 1329499 MULTIPOLY… NA            1 NA      IM     TX    AMIS    Amista…
##  8  1967  589056 POLYGON    NA            1 NA      NE     MD    ASIS    Assate…
##  9  1944 1377082 POLYGON    NA            1 NA      IM     TX    BIBE    Big Be…
## 10  1989  302659 POLYGON    NA            1 NA      SE     FL    BICY    Big Cy…
## # … with 21,550 more rows, 2 more variables: unit_type <chr>, visitors <dbl>,
## #   and abbreviated variable names ¹​metadata, ²​number_of_records, ³​parkname,
## #   ⁴​unit_code, ⁵​unit_name