data <- read_excel("../00_data/myData.xlsx")
data %>% skimr::skim()| Name | Piped data |
| Number of rows | 21560 |
| Number of columns | 12 |
| _______________________ | |
| Column type frequency: | |
| character | 8 |
| numeric | 4 |
| ________________________ | |
| Group variables | None |
Variable type: character
| skim_variable | n_missing | complete_rate | min | max | empty | n_unique | whitespace |
|---|---|---|---|---|---|---|---|
| geometry | 0 | 1 | 7 | 12 | 0 | 2 | 0 |
| metadata | 0 | 1 | 2 | 57 | 0 | 329 | 0 |
| parkname | 0 | 1 | 2 | 51 | 0 | 337 | 0 |
| region | 0 | 1 | 2 | 2 | 0 | 8 | 0 |
| state | 0 | 1 | 2 | 2 | 0 | 54 | 0 |
| unit_code | 0 | 1 | 4 | 4 | 0 | 378 | 0 |
| unit_name | 0 | 1 | 11 | 83 | 0 | 386 | 0 |
| unit_type | 0 | 1 | 4 | 37 | 0 | 30 | 0 |
Variable type: numeric
| skim_variable | n_missing | complete_rate | mean | sd | p0 | p25 | p50 | p75 | p100 | hist |
|---|---|---|---|---|---|---|---|---|---|---|
| year | 386 | 0.98 | 1981.85 | 24.33 | 1904 | 1965.00 | 1986 | 2002.0 | 2016 | ▁▂▃▆▇ |
| gnis_id | 2 | 1.00 | 1070862.84 | 730809.24 | 2877 | 401309.00 | 1009494 | 1530459.0 | 2775865 | ▇▆▇▃▂ |
| number_of_records | 0 | 1.00 | 1.00 | 0.00 | 1 | 1.00 | 1 | 1.0 | 1 | ▁▁▇▁▁ |
| visitors | 4 | 1.00 | 1277104.58 | 11901479.39 | 0 | 39124.75 | 155219 | 608143.8 | 871922828 | ▇▁▁▁▁ |
A combination of year, gnis_id, unit_name, and visitors
Divide it using dplyr::select in a way the two have a common variable, which you could use to join the two.
data_1half <- data %>% select(year:parkname, unit_name, visitors)
data_2half <- data %>% select(parkname:visitors, year, gnis_id)Use tidyr::left_join or other joining functions.
left_join(data_1half, data_2half)## Joining, by = c("year", "gnis_id", "parkname", "unit_name", "visitors")## # A tibble: 21,560 × 12
## year gnis_id geometry metad…¹ numbe…² parkn…³ unit_…⁴ visit…⁵ region state
## <dbl> <dbl> <chr> <chr> <dbl> <chr> <chr> <dbl> <chr> <chr>
## 1 1904 1163670 POLYGON NA 1 Crater… Crater… 1500 PW OR
## 2 1941 1531834 MULTIPOLY… NA 1 Lake R… Lake R… 0 PW WA
## 3 1961 2055170 MULTIPOLY… NA 1 Lewis … Lewis … 69000 PW WA
## 4 1935 1530459 MULTIPOLY… NA 1 Olympic Olympi… 2200 PW WA
## 5 1982 277263 POLYGON NA 1 Santa … Santa … 468144 PW CA
## 6 1919 578853 MULTIPOLY… NA 1 NA Acadia… 64000 NE ME
## 7 1969 1329499 MULTIPOLY… NA 1 NA Amista… 448000 IM TX
## 8 1967 589056 POLYGON NA 1 NA Assate… 738700 NE MD
## 9 1944 1377082 POLYGON NA 1 NA Big Be… 1409 IM TX
## 10 1989 302659 POLYGON NA 1 NA Big Cy… 81157 SE FL
## # … with 21,550 more rows, 2 more variables: unit_code <chr>, unit_type <chr>,
## # and abbreviated variable names ¹metadata, ²number_of_records, ³parkname,
## # ⁴unit_name, ⁵visitorssum(str_detect(data$parkname, "^B"))## [1] 915historic <- c("Historic", "Historical", "Monument", "Memorial", "Battlefield")
historic_match <- str_c(historic, collapse = "|")
historic_match## [1] "Historic|Historical|Monument|Memorial|Battlefield"# Extract historic parks
is_historic <- str_subset(data$unit_type, historic_match)
str_extract(is_historic, historic_match) %>% head(200)## [1] "Historic" "Historic" "Monument" "Monument" "Historic"
## [6] "Historic" "Historic" "Historic" "Historic" "Battlefield"
## [11] "Historic" "Battlefield" "Historic" "Monument" "Monument"
## [16] "Historic" "Monument" "Battlefield" "Historic" "Monument"
## [21] "Monument" "Battlefield" "Battlefield" "Monument" "Monument"
## [26] "Memorial" "Memorial" "Memorial" "Historic" "Monument"
## [31] "Battlefield" "Battlefield" "Monument" "Historic" "Monument"
## [36] "Monument" "Historic" "Historic" "Historic" "Monument"
## [41] "Monument" "Monument" "Historic" "Historic" "Historic"
## [46] "Historic" "Historic" "Monument" "Monument" "Historic"
## [51] "Historic" "Monument" "Monument" "Monument" "Monument"
## [56] "Monument" "Monument" "Historic" "Historic" "Monument"
## [61] "Historic" "Monument" "Monument" "Historic" "Historic"
## [66] "Memorial" "Monument" "Historic" "Historic" "Monument"
## [71] "Monument" "Historic" "Historic" "Historic" "Historic"
## [76] "Historic" "Historic" "Historic" "Historic" "Historic"
## [81] "Historic" "Monument" "Historic" "Historic" "Monument"
## [86] "Historic" "Historic" "Memorial" "Memorial" "Battlefield"
## [91] "Monument" "Historic" "Monument" "Historic" "Monument"
## [96] "Historic" "Historic" "Historic" "Monument" "Memorial"
## [101] "Historic" "Historic" "Memorial" "Historic" "Monument"
## [106] "Monument" "Historic" "Monument" "Historic" "Monument"
## [111] "Monument" "Historic" "Memorial" "Memorial" "Monument"
## [116] "Historic" "Battlefield" "Historic" "Historic" "Historic"
## [121] "Historic" "Memorial" "Monument" "Historic" "Historic"
## [126] "Historic" "Memorial" "Memorial" "Historic" "Historic"
## [131] "Historic" "Historic" "Monument" "Historic" "Historic"
## [136] "Historic" "Historic" "Memorial" "Monument" "Historic"
## [141] "Historic" "Memorial" "Memorial" "Memorial" "Memorial"
## [146] "Monument" "Historic" "Historic" "Historic" "Historic"
## [151] "Monument" "Historic" "Historic" "Historic" "Historic"
## [156] "Historic" "Historic" "Monument" "Monument" "Memorial"
## [161] "Historic" "Historic" "Monument" "Monument" "Memorial"
## [166] "Monument" "Historic" "Historic" "Historic" "Monument"
## [171] "Historic" "Historic" "Historic" "Monument" "Monument"
## [176] "Battlefield" "Historic" "Monument" "Historic" "Memorial"
## [181] "Monument" "Historic" "Memorial" "Monument" "Battlefield"
## [186] "Historic" "Historic" "Memorial" "Historic" "Monument"
## [191] "Battlefield" "Monument" "Historic" "Historic" "Monument"
## [196] "Historic" "Monument" "Memorial" "Battlefield" "Memorial"data_rev <- data %>% mutate(unit_code_rev = unit_code %>% str_replace("C", "-"))
data_rev %>% glimpse()## Rows: 21,560
## Columns: 13
## $ year <dbl> 1904, 1941, 1961, 1935, 1982, 1919, 1969, 1967, 1944…
## $ gnis_id <dbl> 1163670, 1531834, 2055170, 1530459, 277263, 578853, …
## $ geometry <chr> "POLYGON", "MULTIPOLYGON", "MULTIPOLYGON", "MULTIPOL…
## $ metadata <chr> "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA"…
## $ number_of_records <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ parkname <chr> "Crater Lake", "Lake Roosevelt", "Lewis and Clark", …
## $ region <chr> "PW", "PW", "PW", "PW", "PW", "NE", "IM", "NE", "IM"…
## $ state <chr> "OR", "WA", "WA", "WA", "CA", "ME", "TX", "MD", "TX"…
## $ unit_code <chr> "CRLA", "LARO", "LEWI", "OLYM", "SAMO", "ACAD", "AMI…
## $ unit_name <chr> "Crater Lake National Park", "Lake Roosevelt Nationa…
## $ unit_type <chr> "National Park", "National Recreation Area", "Nation…
## $ visitors <dbl> 1500, 0, 69000, 2200, 468144, 64000, 448000, 738700,…
## $ unit_code_rev <chr> "-RLA", "LARO", "LEWI", "OLYM", "SAMO", "A-AD", "AMI…