# Data transformation III
```{r}
#| include: false
# settings, placed in a chunk that will not show in the .html file (because include=FALSE)
# disables scientific notation so that small numbers appear as eg "0.00001" rather than "1e-05"
options(scipen = 999)
```
This chapter covers "*aggregating*" functions. They aggregate results across rows to create a new data frame that does not contain any of the rows of the original. For example, to convert the 'age' column that contains participants' ages to 'age_mean', which only has one row containing the average age.
## Summarizing across rows
It is very common that we need to create summaries across rows. For example, to create the mean and standard deviation of a column like age. This can be done with `summarize()`. Remember: `mutate()` creates new columns or modifies the contents of existing columns, but does not change the number of rows. Whereas `summarize()` reduces a data frame down to one row.
```{r}
# code to generated and clean data from data_transformation_1:
library(truffle)
library(dplyr)
library(stringr)
set.seed(42)
dat_demographics_messy <- data.frame(id = 1:500) %>%
truffle::truffle_demographics() %>%
truffle::dirt_demographics()
dat_gender_tidier <- dat_demographics_messy %>%
# convert to lower case and remove non-letters
mutate(gender = str_to_lower(gender),
gender = str_remove_all(gender, "[^A-Za-z]"),
# added age processing
age = str_remove_all(age, "![^A-Za-z]"),
age = if_else(age == "", NA_integer_, as.numeric(age))) %>%
# tidy up cases
mutate(gender = case_when(gender %in% c("female", "male", "nonbinary") ~ gender,
gender %in% c("femail", "fem ale", "femle", "femlae") ~ "female",
gender %in% c("malee", "mal", "ma le") ~ "male",
gender %in% c("non binary", "nonbinary") ~ "non-binary",
gender == "" ~ NA_character_, # if an empty character string, change to NA_character_
TRUE ~ gender)) # if none of the above apply, keep original value
```
```{r}
library(dplyr)
library(tidyr)
library(janitor)
# mean
dat_gender_tidier %>%
summarize(mean_age = mean(age, na.rm = TRUE))
dat_gender_tidier %>%
summarize(mean_age = mean(age))
# SD
dat_gender_tidier %>%
summarize(sd_age = sd(age, na.rm = TRUE))
# mean and SD with rounding, illustrating how multiple summarizes can be done in one function call
dat_gender_tidier %>%
summarize(mean_age = mean(age, na.rm = TRUE),
sd_age = sd(age, na.rm = TRUE)) %>%
mutate(mean_age = round_half_up(mean_age, digits = 2),
sd_age = round_half_up(sd_age, digits = 2))
```
### `group_by()`
Often, we don't want to reduce a data frame down to a single row / summarize the whole dataset, but instead we want to create a summary for each (sub)group. For example
```{r}
# illustrate use of group_by() and summarize()
dat_gender_tidier %>%
summarize(mean_age = mean(age, na.rm = TRUE),
sd_age = sd(age, na.rm = TRUE))
dat_gender_tidier %>%
group_by(gender) %>%
summarize(mean_age = round_half_up(mean(age, na.rm = TRUE), digits = 2),
sd_age = round_half_up(sd(age, na.rm = TRUE), digits = 2))
dat <- dat_gender_tidier %>%
group_by(gender)
dat %>%
summarize(mean_age = round_half_up(mean(age, na.rm = TRUE), digits = 2),
sd_age = round_half_up(sd(age, na.rm = TRUE), digits = 2))
```
### ungroup()
Grouping is an invisible property of a data frame or tibble. You can't find a value inside it that tells you it's grouped, but the grouping *can* persist unless you `ungroup()`. This can cause unexpected behavior, so watch out for it.
<!-- #### group_by() applied to filter and slice functions -->
<!-- TODO -->
### `n()`
`n()` calculates the number of rows, i.e., the N. It can be useful in summarize.
```{r}
# summarize n
dat_gender_tidier %>%
summarize(n_age = n())
# summarize n per gender group
dat_gender_tidier %>%
group_by(gender) %>%
summarize(n_age = n())
# dat_gender_tidier %>%
# summarize(n_age = n())
#
# dat_gender_tidier %>%
# n()
#
# dat_gender_tidier %>%
# mutate(n_age = n())
```
### Realise that `count()` is just a wrapper function for `summarize()`
`count()` is just the combination of group_by() and summarize() and n(). They produce the same results as above.
```{r}
# summarize n
dat_gender_tidier %>%
count()
# summarize n per gender group
dat_gender_tidier %>%
count(gender)
```
## `distinct()`
Distinct is a variation on `count()` that doesn't return the counts, just the distinct values.
```{r}
# summarize n per gender group
dat_gender_tidier %>%
count(gender)
# summarize n per gender group
dat_gender_tidier %>%
distinct(gender)
```
### Handling duplicates with multiple-`counts()` and `distinct()`
It can be used to remove duplicates, but it's important to remember that you don't know which row is retained vs dropped.
```{r}
# create duplicates that need cleaning
dat_gender_tidier_with_duplicates <- dat_gender_tidier %>%
dirt_duplicates(prop = 0.10)
# count rows
dat_gender_tidier_with_duplicates %>%
count()
# unique participant ids
dat_gender_tidier_with_duplicates %>%
count(id, name = "n_rows")
```
```{r}
# count unique participant ids and the number of rows they have
dat_gender_tidier_with_duplicates %>%
count(id, name = "n_rows") %>%
count(n_rows, name = "n_ids_with_n_rows")
```
```{r}
# count unique participant ids and the number of rows they have
dat_gender_tidier_duplicates_removed <- dat_gender_tidier_with_duplicates %>%
distinct(id, .keep_all = TRUE) # keep all other columns
dat_gender_tidier_duplicates_removed %>%
count(id, name = "n_rows") %>%
count(n_rows, name = "n_ids_with_n_rows")
# alternative, fewer assumptions
dat_gender_tidier_duplicates_removed2 <- dat_gender_tidier_with_duplicates %>%
distinct(id, age, gender)
dat_gender_tidier_duplicates_removed2 %>%
count(id, name = "n_rows") %>%
count(n_rows, name = "n_ids_with_n_rows")
```
### More complex summarizations
Like mutate, the operation you do to summarize can also be more complex, such as finding the mean result of a logical test to calculate a proportion. For example, the proportion of participants who are less than 25 years old:
```{r}
dat_gender_tidier %>%
summarize(proportion_less_than_25 = mean(age < 25, na.rm = TRUE)) %>%
mutate(percent_less_than_25 = round_half_up(proportion_less_than_25 * 100, 1))
dat_gender_tidier %>%
mutate(less_than_25 = if_else(age < 25, 1, 0)) %>%
summarize(percent_below_25 = mean(less_than_25, na.rm = TRUE)*100)
dat_gender_tidier %>%
mutate(less_than_25 = age < 25) %>%
head()
```
### Practice using `summarize()`
<!-- #### 1 -->
Calculate the N, min, max, mean, and SD of age in `dat_gender_tidier`.
```{r}
bind_rows(
dat_gender_tidier %>%
drop_na(age) %>%
summarize(N = n(),
min = min(age),
max = max(age),
mean = mean(age),
sd = sd(age)) %>%
round_half_up(2),
dat_gender_tidier %>%
summarize(N = n(),
min = min(age, na.rm = TRUE),
max = max(age, na.rm = TRUE),
mean = mean(age, na.rm = TRUE),
sd = sd(age, na.rm = TRUE)) %>%
round_half_up(2)
)
```
<!-- #### 2 TODO -->
<!-- Currently each participant has up to three responses on the self-report scales (three item scale: like, positive, and prefer). Create a new dataframe containing each unique_id's mean score across the items. Also calculate how many items each participant has data for, and whether they have complete data (i.e., data for three items). -->
<!-- ```{r} -->
<!-- ``` -->
<!-- #### 3 TODO -->
<!-- Using only participants with complete, calculate the mean and SD of all participant's mean scores on the self-reports. -->
<!-- ```{r} -->
<!-- ``` -->
<!-- #### 4 TODO -->
<!-- Create a new data frame that calculates the proportion of prime-congruent trials for each participant on the AMP (i.e., the mean of the 'correct' column), their proportion of too-fast trials, and their number of trials. -->
<!-- Also add to that data frame a new column called "exclude_amp" and set it to "exclude" if more than 10% of a participant's trials are too-fast trials and "include" if not. -->
<!-- ```{r} -->
<!-- ``` -->
<!-- #### 5 TODO -->
<!-- Calculate the proportion of participants who are to be excluded. -->
<!-- ```{r} -->
<!-- # data_amp_scored %>% -->
<!-- ``` -->
## Frequncies of sets of columns
Note that it is also possible to use count to obtain the frequencies of sets of unique values across columns, e.g., unique combinations of item and response.
```{r}
# data_demographics_trimmed %>%
# count(item)
#
# data_demographics_trimmed %>%
# count(response)
#
# data_demographics_trimmed %>%
# count(item, response)
```
It can be useful to arrange the output by the frequencies.
```{r}
# data_demographics_trimmed %>%
# count(item, response) %>%
# arrange(desc(n)) # arrange in descending order
```
### `summarize(across())`
<!-- TODO here or later in -->
## Check your learning
What is the difference between `mutate()` and `summarize()`? If I use the wrong one, will I get the same answer? E.g., mutate(mean_age = mean(age, na.rm = TRUE)) vs. summarize(mean_age = mean(age, na.rm = TRUE))