4 Wrangling data
One common task is to reformat and/or integrate information that has been stored inconveniently. Sometimes this involves combining or splitting columns because we’re unsatisfied with how data have been recorded. Other times this is because the data formats for recording, communicating, and analyzing data match rather imperfectly. Very often it’s sensible and efficient to record data in wide format (each row a case containing observations about that case stored in columns), review and/or communicate it in summary format (cased grouped by some shared value of a given variable), and analyze it in long format (each row a unique combination of case, variable, and value).
In the case of Table 6.1b, there’s information stored in the second sheet that we might like to include in our table. Because there’s not much of it, we can do this manually, by using case_when() to populate a new column . We’ll also explore automating the process, which you would want to do with there were 30 rows rather than 3.
4.1 case_when()
First we’ll use dplyr’s case_when() function, which is effectively a version of if_else(). We’ll combine it with mutate() to make a new column, and then populate that column depending on the value associated in another table. What we’re doing in R working out way down the rows of Table 6.1b, checking the site, then looking at Table 6.1b Sheet 2 to see what period that site belongs to, and filling that information in a new column.
## # A tibble: 3 × 3
## Site Period `Area (ha)`
## <chr> <chr> <dbl>
## 1 Oak Grove Middle Formative 2.2
## 2 Maple Knoll Middle Formative 2.9
## 3 Cypress Swamp Late Formative 4.1drennan6.1b %>% mutate(Period = case_when(
Site == "Oak Grove" ~ "Middle Formative",
Site == "Maple Knoll" ~ "Middle Formative",
Site == "Cypress Swamp" ~ "Late Formative"
))## # A tibble: 140 × 6
## Site Unit Incised Unincised PropIncised Period
## <fct> <fct> <dbl> <dbl> <dbl> <chr>
## 1 Oak Grove OG-29 31 86 0.265 Middle Formative
## 2 Maple Knoll MK-13 21 81 0.206 Middle Formative
## 3 Cypress Swamp CS-40 43 84 0.339 Late Formative
## 4 Cypress Swamp CS-38 35 97 0.265 Late Formative
## 5 Cypress Swamp CS-12 29 87 0.25 Late Formative
## 6 Cypress Swamp CS-37 17 96 0.150 Late Formative
## 7 Cypress Swamp CS-19 37 115 0.243 Late Formative
## 8 Oak Grove OG-42 39 111 0.26 Middle Formative
## 9 Oak Grove OG-4 25 101 0.198 Middle Formative
## 10 Maple Knoll MK-26 14 94 0.130 Middle Formative
## # ℹ 130 more rows#repeat for area
drennan6.1b %>% mutate(Area = case_when(
Site == "Oak Grove" ~ 2.2, #numeric so shouldn't be in ""
Site == "Maple Knoll" ~ 2.9,
Site == "Cypress Swamp" ~ 4.1
))## # A tibble: 140 × 6
## Site Unit Incised Unincised PropIncised Area
## <fct> <fct> <dbl> <dbl> <dbl> <dbl>
## 1 Oak Grove OG-29 31 86 0.265 2.2
## 2 Maple Knoll MK-13 21 81 0.206 2.9
## 3 Cypress Swamp CS-40 43 84 0.339 4.1
## 4 Cypress Swamp CS-38 35 97 0.265 4.1
## 5 Cypress Swamp CS-12 29 87 0.25 4.1
## 6 Cypress Swamp CS-37 17 96 0.150 4.1
## 7 Cypress Swamp CS-19 37 115 0.243 4.1
## 8 Oak Grove OG-42 39 111 0.26 2.2
## 9 Oak Grove OG-4 25 101 0.198 2.2
## 10 Maple Knoll MK-26 14 94 0.130 2.9
## # ℹ 130 more rows#can also combine these
drennan6.1b %>% mutate(
Period = case_when(
Site == "Oak Grove" ~ "Middle Formative",
Site == "Maple Knoll" ~ "Middle Formative",
Site == "Cypress Swamp" ~ "Late Formative"),
Area = case_when(
Site == "Oak Grove" ~ 2.2, #numeric so shouldn't be in ""
Site == "Maple Knoll" ~ 2.9,
Site == "Cypress Swamp" ~ 4.1
))## # A tibble: 140 × 7
## Site Unit Incised Unincised PropIncised Period Area
## <fct> <fct> <dbl> <dbl> <dbl> <chr> <dbl>
## 1 Oak Grove OG-29 31 86 0.265 Middle Formative 2.2
## 2 Maple Knoll MK-13 21 81 0.206 Middle Formative 2.9
## 3 Cypress Swamp CS-40 43 84 0.339 Late Formative 4.1
## 4 Cypress Swamp CS-38 35 97 0.265 Late Formative 4.1
## 5 Cypress Swamp CS-12 29 87 0.25 Late Formative 4.1
## 6 Cypress Swamp CS-37 17 96 0.150 Late Formative 4.1
## 7 Cypress Swamp CS-19 37 115 0.243 Late Formative 4.1
## 8 Oak Grove OG-42 39 111 0.26 Middle Formative 2.2
## 9 Oak Grove OG-4 25 101 0.198 Middle Formative 2.2
## 10 Maple Knoll MK-26 14 94 0.130 Middle Formative 2.9
## # ℹ 130 more rows4.2 left_join()
Note that, as above, without a <- we are not saving our new, improved object. Not to worry - we’ll produce it again - more efficiently - and save that. To do so we’ll use a join, which updates one table based on information in another, using shared values to define a relationship. There are multiple types of joins (inner/outer, left/right/full) that can be carried out in various ways (see ?join), and their implementation can get tricky. We’ll use a left join (left_join()), which takes the first table (‘x’), and searches a second table (‘y’) for matching values in shared (or specified) columns. That is, if both x and y have a column z, left_join() searches x$z, and if a value of "a" is present in both x$z and y$z, it takes whatever other information is in that row of y and appends it to the matching row in x. That’s probably hard to follow in the abstract, but have a look at how it works:
## # A tibble: 140 × 7
## Site Unit Incised Unincised PropIncised Period `Area (ha)`
## <chr> <fct> <dbl> <dbl> <dbl> <chr> <dbl>
## 1 Oak Grove OG-29 31 86 0.265 Middle Formati… 2.2
## 2 Maple Knoll MK-13 21 81 0.206 Middle Formati… 2.9
## 3 Cypress Swamp CS-40 43 84 0.339 Late Formative 4.1
## 4 Cypress Swamp CS-38 35 97 0.265 Late Formative 4.1
## 5 Cypress Swamp CS-12 29 87 0.25 Late Formative 4.1
## 6 Cypress Swamp CS-37 17 96 0.150 Late Formative 4.1
## 7 Cypress Swamp CS-19 37 115 0.243 Late Formative 4.1
## 8 Oak Grove OG-42 39 111 0.26 Middle Formati… 2.2
## 9 Oak Grove OG-4 25 101 0.198 Middle Formati… 2.2
## 10 Maple Knoll MK-26 14 94 0.130 Middle Formati… 2.9
## # ℹ 130 more rows4.3 separate()
Suppose that we sometimes want to lump all of these sites as belonging to the Formative Period, and other times we want to separate according to whether they’re Middle Formative or Late Formative? This would be simpler if we had a ‘Period’ column and a second ‘Subperiod’ column. There’s a convenient function for this is the tidyr package (another of the tendrils of the tidyverse).
## # A tibble: 140 × 8
## Site Unit Incised Unincised PropIncised Subperiod Period `Area (ha)`
## <chr> <fct> <dbl> <dbl> <dbl> <chr> <chr> <dbl>
## 1 Oak Grove OG-29 31 86 0.265 Middle Forma… 2.2
## 2 Maple Knoll MK-13 21 81 0.206 Middle Forma… 2.9
## 3 Cypress Swa… CS-40 43 84 0.339 Late Forma… 4.1
## 4 Cypress Swa… CS-38 35 97 0.265 Late Forma… 4.1
## 5 Cypress Swa… CS-12 29 87 0.25 Late Forma… 4.1
## 6 Cypress Swa… CS-37 17 96 0.150 Late Forma… 4.1
## 7 Cypress Swa… CS-19 37 115 0.243 Late Forma… 4.1
## 8 Oak Grove OG-42 39 111 0.26 Middle Forma… 2.2
## 9 Oak Grove OG-4 25 101 0.198 Middle Forma… 2.2
## 10 Maple Knoll MK-26 14 94 0.130 Middle Forma… 2.9
## # ℹ 130 more rows4.4 bind_rows()
In the case of the Eerkens et al. 2007 tables, joining is not what we want - the tables encode information about different sites, so we don’t want to take information from one table and fill it in to rows of another. Rather, we want to combine the tables by adding rows, and filling in columns with empty values where one table has a column and another does not.
eerk_comb <- bind_rows(eerk_tab1, eerk_tab2, eerk_tab3)
#reorder columns of result
eerk_comb <- eerk_comb %>% relocate(site, source_artifact, casa_diablo:whitewater_ridge)Now we can convert character columns to factors if appropriate.
## # A tibble: 10 × 31
## site source_artifact casa_diablo mono_glass_mountain truman_queen
## <fct> <fct> <dbl> <dbl> <dbl>
## 1 Sherwin Summit Formal Tools 51 16 13
## 2 Sherwin Summit Large Flakes 166 65 10
## 3 Sherwin Summit Small Non-pressu… 24 10 2
## 4 Sherwin Summit Small Pressure F… 7 3 7
## 5 Mohawk Valley Formal Tools NA NA NA
## 6 Mohawk Valley Large Flakes NA NA NA
## 7 Mohawk Valley Small Flakes NA NA 1
## 8 Bone Cave Formal Tools NA NA NA
## 9 Bone Cave Large Flakes NA NA NA
## 10 Bone Cave Small Flakes NA NA NA
## # ℹ 26 more variables: fish_springs <dbl>, mono_craters <dbl>,
## # bodie_hills <dbl>, mount_hicks <dbl>, coso <dbl>, buffalo_hills <dbl>,
## # south_warners <dbl>, bs_pp_fm <dbl>, gf_liw_rs <dbl>, cowhead_lake <dbl>,
## # cougar_butte <dbl>, buck_mountain <dbl>, napa_glass_mountain <dbl>,
## # borax_lake <dbl>, obsidian_cliffs <dbl>, mc_kay_butte <dbl>,
## # big_obsidian_flow <dbl>, east_lake_flow <dbl>, quartz_mountain <dbl>,
## # cougar_mountain <dbl>, silver_sycan <dbl>, brooks_canyon <dbl>, …4.5 group_by() and summarize()
Summarizing needs - logically - a function that will summarize multiple inputs in one. Keep in mind that you are squishing the values of many cells in the first table into a single cell in the second, so summarize() will need some function with which to do so (sum, mean, median, etc).
#grouping and summarizing
drennan6.1b %>% group_by(Site) %>% summarize(Incised = sum(Incised),
Unincised = sum(Unincised),
Units = length(Unit))## # A tibble: 3 × 4
## Site Incised Unincised Units
## <chr> <dbl> <dbl> <int>
## 1 Cypress Swamp 1408 3969 44
## 2 Maple Knoll 1091 3290 37
## 3 Oak Grove 1815 5281 59We can combine summarize() and across() to perform the same summary operation on multiple columns. Note that we may need to include an na.rm = T argument if our sums are likely to include NA values and we want to treat those as 0s.
eerk_bysite <- eerk_comb %>% group_by(site) %>%
summarize(across(casa_diablo:whitewater_ridge, sum, na.rm = T))
eerk_bysite## # A tibble: 3 × 30
## site casa_diablo mono_glass_mountain truman_queen fish_springs mono_craters
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Bone C… 0 0 0 0 0
## 2 Mohawk… 0 0 1 0 0
## 3 Sherwi… 248 94 32 25 3
## # ℹ 24 more variables: bodie_hills <dbl>, mount_hicks <dbl>, coso <dbl>,
## # buffalo_hills <dbl>, south_warners <dbl>, bs_pp_fm <dbl>, gf_liw_rs <dbl>,
## # cowhead_lake <dbl>, cougar_butte <dbl>, buck_mountain <dbl>,
## # napa_glass_mountain <dbl>, borax_lake <dbl>, obsidian_cliffs <dbl>,
## # mc_kay_butte <dbl>, big_obsidian_flow <dbl>, east_lake_flow <dbl>,
## # quartz_mountain <dbl>, cougar_mountain <dbl>, silver_sycan <dbl>,
## # brooks_canyon <dbl>, glass_buttes <dbl>, burns_butte <dbl>, …We might also want to consider how many sources contribute flakes in each category. In this case we’ll use an unholy mix of dplyr and Base R syntax. Don’t tell.
This makes it interesting to summarize by other factors than site.
## # A tibble: 5 × 2
## source_artifact source_diversity
## <chr> <dbl>
## 1 Formal Tools 5
## 2 Large Flakes 7
## 3 Small Flakes 10
## 4 Small Non-pressure Flakes 5
## 5 Small Pressure Flakes 54.6 pivot_wider() and pivot_longer()
We can also add a bit more data to the mix, in the form of Euclidean distances from sites to obsidian sources.
These data are of interest because Eerkens and colleagues argue that obsidians that are more and less difficult to procure will not only be more and less common, but also differently represented amongst different kinds of artifacts. These data are, however, in a form that is inconvenient, and in a way that can’t be addressed by simple summary. We could more easily make sense of a table that has, instead of one row for each unique combination of site and source, one row for each site, a column for each source, and a distance in each cell. Moving between these options is commonly called pivoting a table. The tidyverse functions for this are pivot_longer() and pivot_wider(), from the tidyr package. The pivot vocabulary and syntax are often incomprehensible, but it’s possible to follow the examples in the vignette that accompanies the package (try vignette("pivot"); many packages include demos [“vignettes”] that are often more obviously helpful than the abstracted info provided by ?).
library(tidyr)
eerk_dist_summ <- eerk_dist %>% pivot_wider(names_from = Source,
values_from = `Euclidean Distance`)Let’s reverse the process, just to see pivot_longer() in action. Here we tell the function to do this with every column except the first one, put the column names in a new column called ‘Source’, and put the values associated with each combination of site and source in a new column called ‘Distance’.
## # A tibble: 87 × 3
## Site Source Distance
## <chr> <chr> <dbl>
## 1 Bone Cave Bodie Hills 670
## 2 Bone Cave Napa Glass Mountain 620
## 3 Bone Cave Borax Lake 580
## 4 Bone Cave Buffalo Hills 380
## 5 Bone Cave BS/PP/FM 370
## 6 Bone Cave South Warners 350
## 7 Bone Cave Buck Mountain 270
## 8 Bone Cave Cowhead Lake 260
## 9 Bone Cave GF/LIW/RS 280
## 10 Bone Cave Cougar Butte 270
## # ℹ 77 more rows4.7 Combining information
We have one data frame that tells us how many artifacts of each class came from different sources from the three case-study sites, and another that tells us distances from sites to sources. One thing that we might want is a data frame that gives us distances from sites to used sources (since the distances to unused sources are at best irrelevant and at worst confusing).
eerk_dist_summ <- eerk_dist_summ %>% clean_names("snake")
#check that names match
compare_df_cols(eerk_bysite, eerk_dist_summ)## column_name eerk_bysite eerk_dist_summ
## 1 big_obsidian_flow numeric numeric
## 2 bodie_hills numeric numeric
## 3 borax_lake numeric numeric
## 4 brooks_canyon numeric numeric
## 5 bs_pp_fm numeric numeric
## 6 buck_mountain numeric numeric
## 7 buffalo_hills numeric numeric
## 8 burns_butte numeric numeric
## 9 casa_diablo numeric numeric
## 10 coso numeric numeric
## 11 cougar_butte numeric numeric
## 12 cougar_mountain numeric numeric
## 13 cowhead_lake numeric numeric
## 14 east_lake_flow numeric numeric
## 15 fish_springs numeric numeric
## 16 gf_liw_rs numeric numeric
## 17 glass_buttes numeric numeric
## 18 mc_kay_butte numeric numeric
## 19 mono_craters numeric numeric
## 20 mono_glass_mountain numeric numeric
## 21 mount_hicks numeric numeric
## 22 napa_glass_mountain numeric numeric
## 23 obsidian_cliffs numeric numeric
## 24 quartz_mountain numeric numeric
## 25 rimrock_spring numeric numeric
## 26 silver_sycan numeric numeric
## 27 site character character
## 28 south_warners numeric numeric
## 29 truman_queen numeric numeric
## 30 whitewater_ridge numeric numeric#make a binary table (sources used or not)
eerk_bysite_bin <- eerk_bysite %>% select(-site) %>% replace(.>0, 1)
#multiply binary table by distance table
#NA for unused sources
obsid_dist <- mapply("*", eerk_bysite_bin, eerk_dist_summ[,-1]) %>% data.frame() %>%
mutate(site = eerk_bysite$site) %>% relocate(site, .before = casa_diablo) %>%
replace(. == 0, NA)
obsid_dist## site casa_diablo mono_glass_mountain truman_queen fish_springs
## 1 Bone Cave NA NA NA NA
## 2 Mohawk Valley NA NA 200 NA
## 3 Sherwin Summit 100 360 390 380
## mono_craters bodie_hills mount_hicks coso buffalo_hills south_warners
## 1 NA NA NA NA NA NA
## 2 NA 150 220 NA 200 230
## 3 400 410 490 500 NA NA
## bs_pp_fm gf_liw_rs cowhead_lake cougar_butte buck_mountain
## 1 NA NA NA NA NA
## 2 370 410 450 450 510
## 3 NA NA NA NA NA
## napa_glass_mountain borax_lake obsidian_cliffs mc_kay_butte big_obsidian_flow
## 1 NA NA 50 30 40
## 2 430 430 NA NA NA
## 3 NA NA NA NA NA
## east_lake_flow quartz_mountain cougar_mountain silver_sycan brooks_canyon
## 1 40 50 950 820 730
## 2 NA NA NA NA NA
## 3 NA NA NA NA NA
## glass_buttes burns_butte rimrock_spring whitewater_ridge
## 1 730 710 680 720
## 2 NA NA NA NA
## 3 NA NA NA NA