5. Data Wrangling

Self-Paced Workshop #5

The Wrangling Verbs

Last week’s workshop was about one command, mutate(), which changes the values in a dataset. This week’s lecture was about changing its structure: keeping some rows and columns and dropping others, gluing two datasets together, collapsing many rows into a few summary numbers, weighting a survey, and reshaping a table from wide to long. That is a lot of commands, but they share one grammar — a dataset, a pipe, a verb — and each one on its own is small.

This page gives you the smallest possible example of each. Run every box and read every printout, and by the end the tidyverse cheatsheets will read like a menu instead of a foreign language.

NoteBefore you start

Do Self-Paced Workshop #4 first if you haven’t; this page assumes you know what |> and mutate() do. As before, the boxes share one R session — run them top to bottom, and if you see object not found, re-run the box above that creates it.

Step 1. Load the packages

Three packages this time. dplyr has the row, column, join, and summary verbs; tidyr has the reshaping verbs; srvyr handles survey weights.¹ Run this box and wait for it to finish — the browser has to fetch the packages the first time, which can take a moment.

Step 2. A tiny dataset

Nine survey respondents across three election years, with whether they voted (turnout, 1 = yes) and their party (D, R, or I).

Step 3. filter(): keep the rows you want

filter() keeps the rows for which a question is true and drops the rest. To keep only the 2020 respondents:

Three rows.² Remember: two equals signs == asks a question: whether things are equal. (One equals sign, as in mutate(x = ...), assigns something.) The opposite question is !=, “not equal to”:

Several questions separated by commas must all be true — “and.” Respondents who are not from 2020 and who voted:

Try it yourself: keep only the Democrats who turned out to vote. Text values need quotes, "D"; numbers do not.³

filter() does not change votes itself — like mutate(), it shows you a result, and you keep it only by assigning with <-. We’ll leave votes whole for the rest of the page.

Step 4. select(): keep the columns you want

filter() picks rows; select() picks columns. List the ones to keep:

Or name the ones to drop, with a minus sign. This gives the same result:

Verbs chain. “Take votes, and then keep three columns, and then keep the 2020 rows”:

Try it yourself: in one pipeline, keep just id and party for the 2016 respondents.

Step 5. Stacking datasets with bind_rows()

Suppose the 2024 wave arrives as its own file, with the same columns. To add more rows — the same variables, new cases — stack the two with bind_rows():

Twelve rows, all of them printed.⁴ bind_rows() matches columns by name, not by position, so the names have to agree exactly; a column that one dataset has and the other doesn’t gets filled with NA. A quick way to check the result is to count rows per year:

Step 6. Joining datasets with left_join()

The other way to combine data is to add more columns for the same cases — a merge or, in tidyverse language, a join. Two datasets about the same people, each with an id, and each missing one person the other has:

A left_join() keeps every row of the left (first) dataset and attaches whatever matches from the right, lining rows up by the key you name in by =:

Person 1 is kept, with NA for income (nothing to match). Person 4 is gone: they were never in ages. The other joins differ only in which rows survive:

Try it yourself: change left_join to right_join in the box below, and predict the result before you run it.⁵

Joins are where data quietly goes missing, so always look at what did not match. anti_join() shows the rows of the first dataset that have no partner in the second:

Joins also do not have to be one-to-one. Here five people live in two counties, and we want each person to carry their county’s election result. Many people, one county — a left join handles it, repeating the county’s value for everyone who lives there:

Step 7. Collapsing data with group_by() and summarise()

Often the question is not about your rows but about groups of them: not students but classrooms, not respondents but years. Seven students in three classes:

summarise() boils a dataset down to one row of summary numbers. The mean score for everyone:

Put a group_by() in front of it, and the same summarise() runs once per group instead — one row per class:

Notice what happened to the data: it now has three rows, one per class, and no id column. The unit of analysis changed from the student to the class. You can ask for several summaries at once; n() counts the rows in each group:

The last line, .groups = "drop", tells R to forget the grouping once it has finished summarising — a good habit, since a still-grouped dataset behaves differently in later steps.⁶

One trap. If a value is missing, mean() gives up and returns NA:

Tell it to remove the missing values first with na.rm = TRUE, and class A gets a mean again:

Try it yourself: back in votes, the mean of turnout is the turnout rate. Add a turnout_rate = mean(turnout) summary to the box below to get the rate for each year.⁷

Step 8. Survey weights with srvyr

Real surveys are rarely perfectly representative, so they ship with a weight variable: a number per respondent saying how many people that respondent should stand for. Here is a small poll that over-recruited college graduates, with a weight pwt that corrects for it — the two high-school respondents count double, the three graduates count half.

Those are the unweighted answers, and they lean young and high-turnout because of who was over-recruited. To use the weights, first tell R about the survey’s design — which column is the weight — and store that as a new object:

Then summarise the design object with survey_mean() in place of mean():

Older, and lower turnout, once the weights are applied.⁸ The extra _se columns are standard errors — how uncertain each weighted estimate is — which you can ignore for now.

Try it yourself: group_by() works on design objects too. Add group_by(educ) |> before the summarise() below to get a weighted mean age for each education level.

Step 9. Reshaping with pivot_longer() and pivot_wider()

Finally, the same data can be laid out in more than one shape. Two students’ grades in two classes, in wide form — one row per student, one column per class:

pivot_longer() turns it long — one row per student per class. You tell it which columns to fold up (cols), what to call the column that will hold their names (names_to), and what to call the column that will hold their values (values_to):

Four rows: the two class names have become values in a subject column, and the four grades are stacked in a score column. This is the tidy shape for most of what you’ll do with ggplot2 next week. pivot_wider() is the exact reverse: say which column supplies the new column names and which supplies the values.

Try it yourself: there is a third shape. Change names_from = subject to names_from = student and see what the rows and columns become. Which shape you want depends on what one row is supposed to be.

Work on Your Own

That is every verb from this week’s lecture, each in its smallest form. Problem Set 6 uses them at scale on the General Social Survey: filter() to pick years, mutate() to recode, group_by() and summarise() to get a rate per year, and the survey weights to make those rates representative. Keep the dplyr and tidyr cheatsheets open while you work.

Footnotes

1. In your own R, library(tidyverse) loads dplyr and tidyr (and several other packages) in one line. srvyr is not part of the tidyverse bundle, so it needs its own install.packages("srvyr") once and its own library(srvyr) each session. Any notes about “masked” objects are normal.

2. Respondents 7, 8, and 9.

3. You should end up with respondents 1 and 8.

4. A tibble prints every row up to twenty. Beyond that it shows only the first ten, plus a note such as 2,990 more rows, so a real survey never floods your screen — the “concise data view” mentioned in lecture, and one reason to prefer tibbles to plain data frames.

5. right_join() keeps every row of the right dataset, incomes: ids 2, 3, and 4, with NA for person 4’s age.

6. With one grouping variable, summarise() drops the grouping by itself; with two or more (group_by(school, class)) it keeps the outer ones and prints a message about it. Writing .groups = "drop" makes the behaviour the same either way.

7. 0.333 in 2012, 0.667 in 2016, and 1 in 2020.

8. A mean age of about 44.4 rather than 38, and a turnout rate of about 0.54 rather than 0.67.