data <- read_excel("./data/palmtrees.xlsx")
data## # A tibble: 2,557 × 29
## spec_name acc_genus acc_species palm_tribe palm_subfamily climbing
## <chr> <chr> <chr> <chr> <chr> <chr>
## 1 Acanthophoenix crin… Acanthop… crinita Areceae Arecoideae climbing
## 2 Acanthophoenix rous… Acanthop… rousselii Areceae Arecoideae climbing
## 3 Acanthophoenix rubra Acanthop… rubra Areceae Arecoideae climbing
## 4 Acoelorrhaphe wrigh… Acoelorr… wrightii Trachycar… Coryphoideae climbing
## 5 Acrocomia aculeata Acrocomia aculeata Cocoseae Arecoideae climbing
## 6 Acrocomia crispa Acrocomia crispa Cocoseae Arecoideae climbing
## 7 Acrocomia emensis Acrocomia emensis Cocoseae Arecoideae climbing
## 8 Acrocomia glaucesce… Acrocomia glaucescens Cocoseae Arecoideae climbing
## 9 Acrocomia hassleri Acrocomia hassleri Cocoseae Arecoideae climbing
## 10 Acrocomia intumesce… Acrocomia intumescens Cocoseae Arecoideae climbing
## # ℹ 2,547 more rows
## # ℹ 23 more variables: acaulescent <chr>, erect <chr>, stem_solitary <chr>,
## # stem_armed <chr>, leaves_armed <chr>, max_stem_height_m <chr>,
## # max_stem_dia_cm <chr>, understorey_canopy <chr>, max_leaf_number <chr>,
## # max__blade__length_m <chr>, max__rachis__length_m <chr>,
## # max__petiole_length_m <chr>, average_fruit_length_cm <chr>,
## # min_fruit_length_cm <chr>, max_fruit_length_cm <chr>, …data_long <- data %>%
pivot_longer(cols = c(average_fruit_length_cm, max_stem_dia_cm), names_to = "sizes", values_to = "measurements")
data_long## # A tibble: 5,114 × 29
## spec_name acc_genus acc_species palm_tribe palm_subfamily climbing
## <chr> <chr> <chr> <chr> <chr> <chr>
## 1 Acanthophoenix crin… Acanthop… crinita Areceae Arecoideae climbing
## 2 Acanthophoenix crin… Acanthop… crinita Areceae Arecoideae climbing
## 3 Acanthophoenix rous… Acanthop… rousselii Areceae Arecoideae climbing
## 4 Acanthophoenix rous… Acanthop… rousselii Areceae Arecoideae climbing
## 5 Acanthophoenix rubra Acanthop… rubra Areceae Arecoideae climbing
## 6 Acanthophoenix rubra Acanthop… rubra Areceae Arecoideae climbing
## 7 Acoelorrhaphe wrigh… Acoelorr… wrightii Trachycar… Coryphoideae climbing
## 8 Acoelorrhaphe wrigh… Acoelorr… wrightii Trachycar… Coryphoideae climbing
## 9 Acrocomia aculeata Acrocomia aculeata Cocoseae Arecoideae climbing
## 10 Acrocomia aculeata Acrocomia aculeata Cocoseae Arecoideae climbing
## # ℹ 5,104 more rows
## # ℹ 23 more variables: acaulescent <chr>, erect <chr>, stem_solitary <chr>,
## # stem_armed <chr>, leaves_armed <chr>, max_stem_height_m <chr>,
## # understorey_canopy <chr>, max_leaf_number <chr>,
## # max__blade__length_m <chr>, max__rachis__length_m <chr>,
## # max__petiole_length_m <chr>, min_fruit_length_cm <chr>,
## # max_fruit_length_cm <chr>, average_fruit_width_cm <chr>, …data_long %>%
pivot_wider(names_from = sizes, values_from = measurements)## # A tibble: 2,557 × 29
## spec_name acc_genus acc_species palm_tribe palm_subfamily climbing
## <chr> <chr> <chr> <chr> <chr> <chr>
## 1 Acanthophoenix crin… Acanthop… crinita Areceae Arecoideae climbing
## 2 Acanthophoenix rous… Acanthop… rousselii Areceae Arecoideae climbing
## 3 Acanthophoenix rubra Acanthop… rubra Areceae Arecoideae climbing
## 4 Acoelorrhaphe wrigh… Acoelorr… wrightii Trachycar… Coryphoideae climbing
## 5 Acrocomia aculeata Acrocomia aculeata Cocoseae Arecoideae climbing
## 6 Acrocomia crispa Acrocomia crispa Cocoseae Arecoideae climbing
## 7 Acrocomia emensis Acrocomia emensis Cocoseae Arecoideae climbing
## 8 Acrocomia glaucesce… Acrocomia glaucescens Cocoseae Arecoideae climbing
## 9 Acrocomia hassleri Acrocomia hassleri Cocoseae Arecoideae climbing
## 10 Acrocomia intumesce… Acrocomia intumescens Cocoseae Arecoideae climbing
## # ℹ 2,547 more rows
## # ℹ 23 more variables: acaulescent <chr>, erect <chr>, stem_solitary <chr>,
## # stem_armed <chr>, leaves_armed <chr>, max_stem_height_m <chr>,
## # understorey_canopy <chr>, max_leaf_number <chr>,
## # max__blade__length_m <chr>, max__rachis__length_m <chr>,
## # max__petiole_length_m <chr>, min_fruit_length_cm <chr>,
## # max_fruit_length_cm <chr>, average_fruit_width_cm <chr>, …table_sep <- data %>%
separate(col = main_fruit_colors, into = c("fruit_color1", "fruit_color2"))## Warning: Expected 2 pieces. Additional pieces discarded in 103 rows [26, 30, 67, 68, 71,
## 78, 93, 112, 119, 177, 183, 259, 267, 278, 303, 309, 351, 372, 400, 498, ...].## Warning: Expected 2 pieces. Missing pieces filled with `NA` in 1966 rows [1, 2, 3, 5, 7,
## 8, 9, 10, 11, 14, 15, 17, 18, 21, 22, 23, 24, 25, 28, 29, ...].table_sep[c("spec_name", "fruit_color1", "fruit_color2")]## # A tibble: 2,557 × 3
## spec_name fruit_color1 fruit_color2
## <chr> <chr> <chr>
## 1 Acanthophoenix crinita black <NA>
## 2 Acanthophoenix rousselii black <NA>
## 3 Acanthophoenix rubra black <NA>
## 4 Acoelorrhaphe wrightii brown black
## 5 Acrocomia aculeata green <NA>
## 6 Acrocomia crispa yellow orange
## 7 Acrocomia emensis NA <NA>
## 8 Acrocomia glaucescens NA <NA>
## 9 Acrocomia hassleri brown <NA>
## 10 Acrocomia intumescens yellow <NA>
## # ℹ 2,547 more rowstable_com <- table_sep %>%
unite(col = "main_fruit_colors", fruit_color1:fruit_color2, sep = "; ", na.rm = TRUE)
table_com[c("spec_name", "main_fruit_colors")]## # A tibble: 2,557 × 2
## spec_name main_fruit_colors
## <chr> <chr>
## 1 Acanthophoenix crinita black
## 2 Acanthophoenix rousselii black
## 3 Acanthophoenix rubra black
## 4 Acoelorrhaphe wrightii brown; black
## 5 Acrocomia aculeata green
## 6 Acrocomia crispa yellow; orange
## 7 Acrocomia emensis NA
## 8 Acrocomia glaucescens NA
## 9 Acrocomia hassleri brown
## 10 Acrocomia intumescens yellow
## # ℹ 2,547 more rowstable_wrong <- table_sep %>%
fill(fruit_color2, .direction = "downup")
table_wrong[c("spec_name", "fruit_color1", "fruit_color2")]## # A tibble: 2,557 × 3
## spec_name fruit_color1 fruit_color2
## <chr> <chr> <chr>
## 1 Acanthophoenix crinita black black
## 2 Acanthophoenix rousselii black black
## 3 Acanthophoenix rubra black black
## 4 Acoelorrhaphe wrightii brown black
## 5 Acrocomia aculeata green black
## 6 Acrocomia crispa yellow orange
## 7 Acrocomia emensis NA orange
## 8 Acrocomia glaucescens NA orange
## 9 Acrocomia hassleri brown orange
## 10 Acrocomia intumescens yellow orange
## # ℹ 2,547 more rows