In PsyTeachR/reprores-v2: Data Skills for Reproducible Research

Data Wrangling {#dplyr}

Learning Objectives {#ilo-dplyr}

Basic {-}

1. Be able to use the 6 main dplyr one-table verbs: (video){class="video"}
   • select()
   • filter()
   • arrange()
   • mutate()
   • summarise()
   • group_by()
2. Be able to wrangle data by chaining tidyr and dplyr functions (video){class="video"}
3. Be able to use these additional one-table verbs: (video){class="video"}
   • rename()
   • distinct()
   • count()
   • slice()
   • pull()

Intermediate {-}

1. Fine control of select() operations (video){class="video"}
2. Use window functions (video){class="video"}

Setup {#setup-dplyr}

# libraries needed for these examples
library(tidyverse)
library(lubridate)
library(reprores)
set.seed(8675309) # makes sure random numbers are reproducible

The disgust dataset {#data-disgust}

These examples will use data from reprores::disgust, which contains data from the Three Domain Disgust Scale. Each participant is identified by a unique user_id and each questionnaire completion has a unique id. Look at the Help for this dataset to see the individual questions.

data("disgust", package = "reprores")

#disgust <- read_csv("https://psyteachr.github.io/reprores/data/disgust.csv")

Six main dplyr verbs

Most of the r glossary("data wrangling") you'll want to do with psychological data will involve the tidyr functions you learned in Chapter 4 and the six main dplyr verbs: select, filter, arrange, mutate, summarise, and group_by.

select() {#select}

Select columns by name or number.

You can select each column individually, separated by commas (e.g., col1, col2). You can also select all columns between two columns by separating them with a colon (e.g., start_col:end_col).

moral <- disgust %>% select(user_id, moral1:moral7)
names(moral)

You can select columns by number, which is useful when the column names are long or complicated.

sexual <- disgust %>% select(2, 11:17)
names(sexual)

You can use a minus symbol to unselect columns, leaving all of the other columns. If you want to exclude a span of columns, put parentheses around the span first (e.g., -(moral1:moral7), not -moral1:moral7).

pathogen <- disgust %>% select(-id, -date, -(moral1:sexual7))
names(pathogen)

Select helpers {#select_helpers}

You can select columns based on criteria about the column names.

starts_with() {#starts_with}

Select columns that start with a character string.

u <- disgust %>% select(starts_with("u"))
names(u)

ends_with() {#ends_with}

Select columns that end with a character string.

firstq <- disgust %>% select(ends_with("1"))
names(firstq)

contains() {#contains}

Select columns that contain a character string.

pathogen <- disgust %>% select(contains("pathogen"))
names(pathogen)

num_range() {#num_range}

Select columns with a name that matches the pattern prefix.

moral2_4 <- disgust %>% select(num_range("moral", 2:4))
names(moral2_4)

::: {.info data-latex=""} Use width to set the number of digits with leading zeros. For example, num_range('var_', 8:10, width=2) selects columns var_08, var_09, and var_10. :::

filter() {#filter}

Select rows by matching column criteria.

Select all rows where the user_id is 1 (that's Lisa).

disgust %>% filter(user_id == 1)

::: {.warning data-latex=""} Remember to use == and not = to check if two things are equivalent. A single = assigns the righthand value to the lefthand variable and (usually) evaluates to TRUE. :::

You can select on multiple criteria by separating them with commas.

amoral <- disgust %>% filter(
  moral1 == 0, 
  moral2 == 0,
  moral3 == 0, 
  moral4 == 0,
  moral5 == 0,
  moral6 == 0,
  moral7 == 0
)

You can use the symbols &, |, and ! to mean "and", "or", and "not". You can also use other operators to make equations.

# everyone who chose either 0 or 7 for question moral1
moral_extremes <- disgust %>% 
  filter(moral1 == 0 | moral1 == 7)

# everyone who chose the same answer for all moral questions
moral_consistent <- disgust %>% 
  filter(
    moral2 == moral1 & 
    moral3 == moral1 & 
    moral4 == moral1 &
    moral5 == moral1 &
    moral6 == moral1 &
    moral7 == moral1
  )

# everyone who did not answer 7 for all 7 moral questions
moral_no_ceiling <- disgust %>%
  filter(moral1+moral2+moral3+moral4+moral5+moral6+moral7 != 7*7)

Match operator (%in%) {#match-operator}

Sometimes you need to exclude some participant IDs for reasons that can't be described in code. The match operator (%in%) is useful here for testing if a column value is in a list. Surround the equation with parentheses and put ! in front to test that a value is not in the list.

no_researchers <- disgust %>%
  filter(!(user_id %in% c(1,2)))

Dates {#dates}

You can use the lubridate package to work with dates. For example, you can use the year() function to return just the year from the date column and then select only data collected in 2010.

disgust2010 <- disgust %>%
  filter(year(date) == 2010)

r showtbl(disgust2010)

Or select data from at least 5 years ago. You can use the range function to check the minimum and maximum dates in the resulting dataset.

disgust_5ago <- disgust %>%
  filter(date < today() - dyears(5))

range(disgust_5ago$date)

arrange() {#arrange}

Sort your dataset using arrange(). You will find yourself needing to sort data in R much less than you do in Excel, since you don't need to have rows next to each other in order to, for example, calculate group means. But arrange() can be useful when preparing data from display in tables.

disgust_order <- disgust %>%
  arrange(date, moral1)

r showtbl(disgust_order)

Reverse the order using desc()

disgust_order_desc <- disgust %>%
  arrange(desc(date))

r showtbl(disgust_order_desc)

mutate() {#mutate}

Add new columns. This is one of the most useful functions in the tidyverse.

Refer to other columns by their names (unquoted). You can add more than one column in the same mutate function, just separate the columns with a comma. Once you make a new column, you can use it in further column definitions e.g., total below).

disgust_total <- disgust %>%
  mutate(
    pathogen = pathogen1 + pathogen2 + pathogen3 + pathogen4 + pathogen5 + pathogen6 + pathogen7,
    moral = moral1 + moral2 + moral3 + moral4 + moral5 + moral6 + moral7,
    sexual = sexual1 + sexual2 + sexual3 + sexual4 + sexual5 + sexual6 + sexual7,
    total = pathogen + moral + sexual,
    user_id = paste0("U", user_id)
  )

r showtbl(disgust_total)

::: {.warning data-latex=""} You can overwrite a column by giving a new column the same name as the old column (see user_id) above. Make sure that you mean to do this and that you aren't trying to use the old column value after you redefine it. :::

summarise() {#summarise}

Create summary statistics for the dataset. Check the Data Wrangling Cheat Sheet or the Data Transformation Cheat Sheet for various summary functions. Some common ones are: mean(), sd(), n(), sum(), and quantile().

disgust_summary<- disgust_total %>%
  summarise(
    n = n(),
    q25 = quantile(total, .25, na.rm = TRUE),
    q50 = quantile(total, .50, na.rm = TRUE),
    q75 = quantile(total, .75, na.rm = TRUE),
    avg_total = mean(total, na.rm = TRUE),
    sd_total  = sd(total, na.rm = TRUE),
    min_total = min(total, na.rm = TRUE),
    max_total = max(total, na.rm = TRUE)
  )

r showtbl(disgust_summary)

group_by() {#group_by}

Create subsets of the data. You can use this to create summaries, like the mean value for all of your experimental groups.

Here, we'll use mutate to create a new column called year, group by year, and calculate the average scores.

disgust_groups <- disgust_total %>%
  mutate(year = year(date)) %>%
  group_by(year) %>%
  summarise(
    n = n(),
    avg_total = mean(total, na.rm = TRUE),
    sd_total  = sd(total, na.rm = TRUE),
    min_total = min(total, na.rm = TRUE),
    max_total = max(total, na.rm = TRUE),
    .groups = "drop"
  )

r showtbl(disgust_groups, 10)

::: {.warning data-latex=""} If you don't add .groups = "drop" at the end of the summarise() function, you will get the following message: "summarise() ungrouping output (override with .groups argument)". This just reminds you that the groups are still in effect and any further functions will also be grouped.

Older versions of dplyr didn't do this, so older code will generate this warning if you run it with newer version of dplyr. Older code might ungroup() after summarise() to indicate that groupings should be dropped. The default behaviour is usually correct, so you don't need to worry, but it's best to explicitly set .groups in a summarise() function after group_by() if you want to "keep" or "drop" the groupings. :::

You can use filter after group_by. The following example returns the lowest total score from each year (i.e., the row where the rank() of the value in the column total is equivalent to 1).

disgust_lowest <- disgust_total %>%
  mutate(year = year(date)) %>%
  select(user_id, year, total) %>%
  group_by(year) %>%
  filter(rank(total) == 1) %>%
  arrange(year)

r showtbl(disgust_lowest, 7)

You can also use mutate after group_by. The following example calculates subject-mean-centered scores by grouping the scores by user_id and then subtracting the group-specific mean from each score. Note the use of gather to tidy the data into a long format first.

disgust_smc <- disgust %>%
  gather("question", "score", moral1:pathogen7) %>%
  group_by(user_id) %>%
  mutate(score_smc = score - mean(score, na.rm = TRUE)) %>% 
  ungroup()

::: {.warning data-latex=""} Use ungroup() as soon as you are done with grouped functions, otherwise the data table will still be grouped when you use it in the future. :::

r showtbl(disgust_smc)

All Together {#all-together}

A lot of what we did above would be easier if the data were tidy, so let's do that first. Then we can use group_by to calculate the domain scores.

After that, we can spread out the 3 domains, calculate the total score, remove any rows with a missing (NA) total, and calculate mean values by year.

disgust_tidy <- reprores::disgust %>%
  gather("question", "score", moral1:pathogen7) %>%
  separate(question, c("domain","q_num"), sep = -1) %>%
  group_by(id, user_id, date, domain) %>%
  summarise(score = mean(score), .groups = "drop")

r showtbl(disgust_tidy)

disgust_scored <- disgust_tidy %>%
  spread(domain, score) %>%
  mutate(
    total = moral + sexual + pathogen,
    year = year(date)
  ) %>%
  filter(!is.na(total)) %>%
  arrange(user_id) 

r showtbl(disgust_scored)

disgust_summarised <- disgust_scored %>%
  group_by(year) %>%
  summarise(
    n = n(),
    avg_pathogen = mean(pathogen),
    avg_moral = mean(moral),
    avg_sexual = mean(sexual),
    first_user = first(user_id),
    last_user = last(user_id),
    .groups = "drop"
  )

r showtbl(disgust_summarised)

Additional dplyr one-table verbs

Use the code examples below and the help pages to figure out what the following one-table verbs do. Most have pretty self-explanatory names.

rename() {#rename}

You can rename columns with rename(). Set the argument name to the new name, and the value to the old name. You need to put a name in quotes or backticks if it doesn't follow the rules for a good variable name (contains only letter, numbers, underscores, and full stops; and doesn't start with a number).

sw <- starwars %>%
  rename(Name = name,
         Height = height,
         Mass = mass,
         `Hair Colour` = hair_color,
         `Skin Colour` = skin_color,
         `Eye Colour` = eye_color,
         `Birth Year` = birth_year)

names(sw)

::: {.try data-latex=""} Almost everyone gets confused at some point with rename() and tries to put the original names on the left and the new names on the right. Try it and see what the error message looks like. :::

distinct() {#distinct}

Get rid of exactly duplicate rows with distinct(). This can be helpful if, for example, you are merging data from multiple computers and some of the data got copied from one computer to another, creating duplicate rows.

# create a data table with duplicated values
dupes <- tibble(
  id = c( 1,   2,   1,   2,   1,   2),
  dv = c("A", "B", "C", "D", "A", "B")
)

distinct(dupes)

count() {#count}

The function count() is a quick shortcut for the common combination of group_by() and summarise() used to count the number of rows per group.

starwars %>%
  group_by(sex) %>%
  summarise(n = n(), .groups = "drop")

count(starwars, sex)

slice() {#slice}

slice(starwars, 1:3, 10)

pull() {#pull}

starwars %>%
  filter(species == "Droid") %>%
  pull(name)

Window functions {#window}

Window functions use the order of rows to calculate values. You can use them to do things that require ranking or ordering, like choose the top scores in each class, or accessing the previous and next rows, like calculating cumulative sums or means.

The dplyr window functions vignette has very good detailed explanations of these functions, but we've described a few of the most useful ones below.

Ranking functions

grades <- tibble(
  id = 1:5,
  "Data Skills" = c(16, 17, 17, 19, 20), 
  "Statistics"  = c(14, 16, 18, 18, 19)
) %>%
  gather(class, grade, 2:3) %>%
  group_by(class) %>%
  mutate(row_number = row_number(),
         rank       = rank(grade),
         min_rank   = min_rank(grade),
         dense_rank = dense_rank(grade),
         quartile   = ntile(grade, 4),
         percentile = ntile(grade, 100))

r showtbl(grades, 10)

::: {.try data-latex=""} What are the differences among row_number(), rank(), min_rank(), dense_rank(), and ntile()? Why doesn't row_number() need an argument? What would happen if you gave it the argument grade or class? What do you think would happen if you removed the group_by(class) line above? What if you added id to the grouping? What happens if you change the order of the rows? * What does the second argument in ntile() do? :::

You can use window functions to group your data into quantiles.

sw_mass <- starwars %>%
  group_by(tertile = ntile(mass, 3)) %>%
  summarise(min = min(mass),
            max = max(mass),
            mean = mean(mass),
            .groups = "drop")

r showtbl(sw_mass, 4)

::: {.try data-latex=""} Why is there a row of NA values? How would you get rid of them? :::

Offset functions

The function lag() gives a previous row's value. It defaults to 1 row back, but you can change that with the n argument. The function lead() gives values ahead of the current row.

lag_lead <- tibble(x = 1:6) %>%
  mutate(lag = lag(x),
         lag2 = lag(x, n = 2),
         lead = lead(x, default = 0))

r showtbl(lag_lead)

You can use offset functions to calculate change between trials or where a value changes. Use the order_by argument to specify the order of the rows. Alternatively, you can use arrange() before the offset functions.

trials <- tibble(
  trial = sample(1:10, 10),
  cond = sample(c("exp", "ctrl"), 10, T),
  score = rpois(10, 4)
) %>%
  mutate(
    score_change = score - lag(score, order_by = trial),
    change_cond = cond != lag(cond, order_by = trial, 
                              default = "no condition")
  ) %>%
  arrange(trial)

r showtbl(trials, 10)

::: {.try data-latex=""} Look at the help pages for lag() and lead().

• What happens if you remove the order_by argument or change it to cond?
• What does the default argument do?
• Can you think of circumstances in your own data where you might need to use lag() or lead()? :::

Cumulative aggregates

cumsum(), cummin(), and cummax() are base R functions for calculating cumulative means, minimums, and maximums. The dplyr package introduces cumany() and cumall(), which return TRUE if any or all of the previous values meet their criteria.

cumulative <- tibble(
  time = 1:10,
  obs = c(2, 2, 1, 2, 4, 3, 1, 0, 3, 5)
) %>%
  mutate(
    cumsum = cumsum(obs),
    cummin = cummin(obs),
    cummax = cummax(obs),
    cumany = cumany(obs == 3),
    cumall = cumall(obs < 4)
  )

r showtbl(cumulative, 10)

::: {.try data-latex=""} What would happen if you change cumany(obs == 3) to cumany(obs > 2)? What would happen if you change cumall(obs < 4) to cumall(obs < 2)? * Can you think of circumstances in your own data where you might need to use cumany() or cumall()? :::

Glossary {#glossary-dplyr}

r glossary_table()

Further Resources {#resources-dplyr}

• Chapter 5: Data Transformation in R for Data Science
• Data transformation cheat sheet
• Chapter 16: Date and times in R for Data Science

PsyTeachR/reprores-v2 documentation built on Sept. 26, 2022, 10:06 a.m.