5 Transforming data

Where the previous chapter dealt with selecting columns, filtering rows, and rearranging the resulting data set, this chapter will look into transforming data by calculating new values and summarizing groups of rows.

As a running example through this chapter, let’s have a look at the bechdel data, which contains information on the famous Bechdel test for female representation in films: does the film contain any scene where two women talk about something other than a man? This might seem a very low bar, but that will turn out to be a fairly naive assumption.

Note that this dataset is a simplified version of the data published by the 538 data-driven politics blog, now sadly discontinued. Although ABC (the new owner of 538) removed the earlier articles (literally the day that we wrote the first version of this chapter!), you can still read the original article using the archive.ph link: The Dollar-And-Cents Case Against Hollywood’s Exclusion of Women.

To download and read the data, we use the built-in download.file function and the tidyverse read_csv function:

Looking at the column names and types, can you take a guess at what each column means?

Suppose we want to know whether movies with better female representation give a better return on investment. What steps would we need to take to find this answer? And what if we want to know whether female representation is improving?

Thinking ahead for the next chapter on visualization: - what kind of plots did they use in the linked 538 article? - What data steps would be needed to produce the numbers for these figures? - Can you think of other useful visualizations for this data?

Columns in the bechdel data - click to expand

Variable	Type	Explanation
imdb	character	IMDb identifier for the film
year	numeric (dbl)	Year of release
title	character	Title of the film
test	character	Detailed Bechdel-test outcome: one of `ok`, `dubious`, `men`, `notalk`, `nowomen`, sometimes with a `-disagree` suffix when the rating was contested
budget	numeric (dbl)	Production budget in US dollars (inflation-adjusted)
domestic_gross	numeric (dbl)	Domestic (US) box-office gross in US dollars
foreign_gross	numeric (dbl)	International box-office gross in US dollars

5.1 Changing or computing values with `mutate`

The essential tool in the data transformation toolbox is the mutate function. With mutate, you can create new columns or change the values in existing columns.

For example, if we want to know whether films that pass the test have a better return on investment, we need to compute the return on investment (dividing earnings by budget) and recode the 10 different values for test into a single yes or no (dummy / boolean) column.

The `mutate()` function

The mutate function makes it easy to create new variables or to modify existing ones.

For example, this computes the total gross earnings (international plus domestic) for each movie:

(You’ll notice R can display large numbers in scientific notation, i.e. using 1.3e7 to mean \(1.3 \times 10^7\), or 13 million. )

The syntax of mutate is similar to that of the filter() and select() functions introduced in the previous chapter: The first argument is the data frame to mutate, and then any number of additional arguments can be given to perform mutations. The mutations themselves are named arguments in the form column_name = calculation. The result of the calculation will then be stored in the named column, which can either be a new column or you can overwrite an existing column.

For calculations, you can use mathematical symbols such as +, / (division), and * (multiplication). There are also many useful functions that you can use in calculations, such as round() to round values, floor() to round values down to the nearest whole number, or abs() to take the absolute (positive) value of numbers.

For example, we could round the budget to millions of dollars:

Note that where the previous example created a new column by assigning to a column name that was not yet in the data, this directly modifies the existing budget column by re-assigning it to an existing name.

Chaining calculations and calculating multiple values

Note that you can calculate multiple columns at the same time. For example, you can compute the total gross and round both gross income and budget to millions of dollars:

Three things to note here. First, we assign the result of the mutate to a new object, bechdel_recoded, which means that this is a new copy of the data and the original bechdel will also still be available. Second, we added an extra digits=1 option to the round function. Lastly, note also that the second line computes gross, and the third line than modifies this newly computed value. In general, you can use the result of previous calculations directly within the same mutate function.

Common error: Closing brackets and R/RStudio prompts

The example above included a multi-line mutate statement. More complex and longer statements will be quite common as your code becomes more sophisticated. One danger is that you may forget the closing parenthesis ()).

In the online interactive prompts, this will lead to a somewhat understandable error message: unexpected end of input – the input ended, but R was still expecting a closing bracket.

If you run an include chunk of code in Rstudio / R, however, it will not give an error message but instead you will see that the prompt changes from > to +:

> mutate(bechdel, budget = round(budget / 1000000, 1)
+

What the + means is that R is still waiting for you to close a parenthesis or a quoted text. You can give many extra commands after this, but it will patiently wait for you to close the parenthesis before actually running all of them.

So, when R feels unresponsive, check that it actually gives a > prompt. If it shows an unexpected + prompt, you can click to activate the console window, press Escape to cancel the current command, and fix and restart the command that was causing trouble.

Exercise

Starting from the original bechdel data set, modify the code below to compute two new columns:

gross: The total gross income of a movie (international plus domestic)
roi The Return on Investment of each movie (gross income divided by budget)

🎓 Solution:

You should compute two columns with two separate arguments to mutate: mutate(bechdel, gross = ____, roi = ____)
Use the pattern mutate(data, new_column = calculation, second_column = calculation)
Remember that you don’t need to quote column names, and that you can use the gross column created in the first calculation in the second

bechdel <- mutate(bechdel,
  gross = foreign_gross + domestic_gross,
  roi = gross / budget
)
head(bechdel)

5.2 Recoding values

So far we’ve used mutate to calculate new values from old ones. Its other major job is recoding — turning the values in a column into different values. Recoding shows up constantly: collapsing many categories into a few, binning a continuous variable into ranges, or flipping cryptic codes into readable labels. R offers a few different functions for this, depending on what shape the recoding takes.

Recoding into two options with `if_else`

The if_else function checks a condition for each row, and chooses one value if it’s true, or an alternative value if it’s false. For example, we can check whether the gross income of a film was higher than it’s budget, and designate that it made a profit if so, and a loss otherwise:

Second, the syntax of if_else is if_else(condition, value_if_true, value_if_false). So, the first argument (gross > budget) tells R to check for each row whether the gross column is larger than budget. Then, if it is true the second argument will be chosen (profit), while for rows where it is false the third argument is chosen (loss)

An important things to note here is that the if_else function is used within mutate. This is generally true for the functions in this chapter: if_else computes a value, and mutate places that value in the data frame.

Recoding multiple values with `recode_values` and `replace_values`

Next, let’s consider the use case of recoding multiple values. For example, the bechdel data set contains a test column, with 10 different values: 5 possible test outcomes (nowomen, notalk, men, dubious, and ok), where each outcome can have -disagree appended to show that there was substantial disagreement among the (crowd) coders this column was based on.

We can use the recode_values function to recode each test value into a new value with a more descriptive name:

Please take a second to look at the slightly odd syntax of recode_values. Its first argument is the input column (test). This is followed by a series of arguments of the form old_value ~ new_value, where you can read the tilde (~) as a kind of arrow: old ⤳ new. So, you can specify as many recode rules as you need.

If multiple recode rules have the same target value, you don’t need to make a separate rule for each input. You can also specify more than one input value to replace by using the c() function to combine multiple values. For example, if we wish to drop the distinction between disputed and undisputed codings, we could do:

As briefly mentioned above, recode_values has a sister function replace_values. The difference between the two is what happens if you don’t specify all possible input values. In the case of replace_values, unnamed values are left unchanged. In contrast, recode_values will turn them into missing values (NA), unless an optional argument default="Some value" is given, in which case that value is used instead.

Package versions and updating packages

In this section we used the recode_values function. This function was introduced in dplyr version 1.2, released in April 2026.

If you run this on your own computer (e.g. in RStudio) and get an error could not find function "recode_values", it’s possible that you have an older version of dplyr.

In general, to check what version of a package you have installed you can use:

> packageVersion("dplyr")
[1] ‘1.2.1’

If you have a version below 1.2, you should either upgrade it or use the older function case_match (which is very similar to recode_values). To upgrade a specific package, you can use the install.packages code that you probably already used to install tidyverse initially. You can also use update.packages(ask=FALSE) to update all packages that have an available update. However, it is a good idea to restart R before upgrading packages, as (especially on Windows) it can be problematic to upgrade a package that is already loaded. So, the procedure is:

Save your current script(s)
Restart the R session (Session → Restart R)
Update some or all packages: install.packages("dplyr", dependencies=TRUE) or update.packages(ask=FALSE)

After this, you can run packageVersion("dplyr") again to see whether the new version is installed.

Note that on restarting R, by default R will save and load your ‘environment’, that is the data you have currently loaded in memory. This feels convenient, but we strongly advise you to disable saving the environment. Go into Tools → Global Options, uncheck Restore .RData into workspace at startup and set Save workspace to .RData on exit to Never.

This can prevent problems with automatically re-loading packages on restarting R, but more importantly it is better ‘code hygiene’: you want the results of your script to only reflect the actual commands in the script, and not be dependent on data you have loaded in your environment.

Note that for more serious package management, advanced users might want to look at renv for per-project package management, and/or pak for more sophisticated dependency management.

Arbitrary recoding with `case_when`

recode_values allowed us to create recode rules by specifying old ⤳ new pairs. Sometimes, however, there is no clear 1:1 mapping between old and new, especially when dealing with continuous ranges or comparisons involving multiple columns.

For a simple example let’s start with the task of recoding the year into periods:

Again, take a minute to look at the syntax of case_when, as it is similar to, but also slightly different from recode_values:

First, case_when has no separate input column argument, jumping directly to the recode rules which include the column name(s) in their conditions.
Second, the recode rules take arbitrary conditions, such as the year < 1980 condition, rather than specifying only the input value. This makes the conditions a bit more verbose as you repeat the column name, but do offer the flexibility of using more powerful expressions, such as the comparisons used above.
Third, case_when will evaluate the rules in the order given. So, if year < 1980 it will return 1970, even if the next condition (year < 1990) also matches.
Finally, like recode_values it accepts a .default= argument which is used if none of the rules match, but confusingly it’s called .default= here, and default= for recode_values

Note that the example above shows only a relatively simple application of case_when (and frankly one that could be replaced easily by a recode_values). The real power of case_when lies in its ability to use multiple columns and conditions that cannot be simply expressed as discrete values.

For a more complicated example, let’s classify movies into big-budget success, big-budget flop, indie success, and indie flop:

Again, have a careful look at the code above. It features three new columns that all build on each other (gross, roi, and category). In the case_when rules, it combines comparisons of the budget and the roi:

First, a high budget and roi > 1 is coded as a ‘big-budget success’.
The next rule codes any other film with a high budget as a ‘big-budget flop’: a check for roi <= 1 is not needed as these would have been matched by the first rule.
Then, it codes every remaining profit-making film as an ‘indie success’, as all high budget films are already coded by the first two rules.
Finally, all remaining films must be ‘indie flops’, as all other categories are already coded.

When to use which recode function?

This section introduced three different recode functions, each with their own use case. Like a carpenter with different tools in their box, you should choose the most appropriate tool for a specific use case. To help, the table below summarizes the three functions:

Function	Description	Use case
`if_else(cond, yes, no)`	Returns `yes` where `cond` is `TRUE`, `no` otherwise	Binary outcomes (pass/fail, profit/loss)
`recode_values(x, old ~ new, ...)`	Replaces specific input values with specific output values	One-to-one or many-to-one value substitution for categorical data
`case_when(cond ~ result, ..., .default = ...)`	Evaluates rules top-to-bottom, returns the result of the first matching condition	Multi-way recoding based on conditions — ranges, thresholds, multi-column logic

R being R, there are of course many more functions for recoding. However, we suggest using the newer recode_values instead of recode or case_match (unless you’re stuck on an old R version), as the newer function is a clear improvement over the older functions. Similarly, avoid the built-in ifelse function in favor of tidyverse’s if_else (with the underscore _) function: the functions are generally the same, but if_else avoids some serious (but rare) problems with data types, for example when dealing with date/time values.

Exercise

To see whether films that pass the Bechdel test do better or worse financially, we need to compute a single column that determines whether it passed the test or not.

In the code below, starting from the original bechdel dataset, compute two columns: - roi: the total gross income divided by the budget - passed: Use if_else to determine whether a film passed the test or not. Treat both ok and ok-disagree as passing.

🎓 Solution:

For the roi, you can do roi = (domestic_gross + foreign_gross) / budget in a single line
For if_else, use the condition test %in% c("ok", "ok-disagree")

bechdel <- mutate(bechdel,
  roi = (domestic_gross + foreign_gross) / budget,
  passed = if_else(test %in% c("ok", "ok-disagree"), "passed", "not passed")
)
head(select(bechdel, year, title, roi, passed))

The code above computed the needed columns (roi and passed) – what would the next step be for answering the question whether there is a relation between the two? What data steps would you need to get to that answer?

More technically, our proposed solution for the question above was using if_else, but you could also use either recode_values or case_when to achieve the same result.

Can you modify the code to use either function?
Why do you think we used if_else here?
Would you have done the same?

Finally, Section 8.1 will talk about dealing with text in more detail, but as a sneak preview the function str_remove(column, text_to_remove) will remove the specified text from the column. Can you use this to remove the -disagree label in the test column, and simplify the if_else function?

5.3 Grouping and summarizing data

So far, all transformations have worked on individual rows. For many questions, however, we want to compute values for groups of rows. For example, what is the average roi for films that pass the Bechdel test compared to films that don’t?

Summarizing data with `group_by` and `summarize`

Using the functions group_by() and summarize() we can anwer this question quite directly:

The code above consists of two steps. In the first step, the data is grouped by passed. This doesn’t make any change in the data itself, but prepares for the next step by defining the two groups of interest.

The second step takes the grouped data (bechdel_grouped) and uses the summarize function. Syntactically, this function looks identical to the mutate function: you specify column = calculation, and it puts the result of the calculation in the named column.

The big difference is that summarize computes a single value for each group of rows: in this case, it computes the mean of the roi values (setting na.rm=TRUE to ignore any missing values).

As you can see from the result, summarize produces a radically smaller data frame than the original, which had 7 columns and almost 1,800 rows. In fact summarize will always create a data frame with one row per group (in this case, passed and not passed), and keeps only the grouping columns (passed) and the columns with the summary values (mean_roi).

Substantively, it seems that films that pass the Bechdel test actually have a lower return on investment than films that don’t. Surprisingly, this seems to go against the conclusions of the original article. We’ll dive into this in more detail later, but first we need to introduce a new piece of syntax.

Pipelines: The `|>` operator

The example above used two functions (group_by and summarize), and the output (result) of the first function was used as the input (first argument) of the next function. This essentially creates a ‘pipeline’ of operations that are applied to the data.

Since this is so common, R has introduced a special operator to make this more convenient: the pipeline operator |>. Let’s rewrite the code above with this new operator:

As you see, the two statements are now ‘chained’ together in a single pipeline: the bechdel data is piped into the group_by function, and the result of the grouping is piped into summarize.

The biggest advantage of this is that it removes the awkward intermediate objects bechdel_grouped, and it saves having to type the name of the data set for every function.

We introduce this notation with group_by() |> summarize() because these functions form a natural pair, but note that almost all tidyverse functions can be chained together in a pipe like this.

It is important to stress that there is absolutely no difference in the end whether you use the pipeline notation or use separate statements for each step. However, since data processing and analysis very often boils down to a sequence of steps (like read data, filtering it, computing values, and visualizing the result), pipelines are a very natural way to capture this. So, regardless of whether you use the notation, it is important that you are able to read it.

One last important thing to note is that |> was introduced as a base R feature in 2021. Before that, tidyverse shipped with its own pipeline operator, %>%. For all our concerns, the two operators are identical, and there is no reason to use the older operator anymore. However, you will find many examples on the Internet still use %>%, and probably as a result code generated by AI also tends to use it. So, if you see %>% being used just mentally translate to |>, and you should stick to |> in your own code.

Improving the film analysis pipeline

When we first calculated the mean ROI grouped by test outcome, we were surprised to find that passing the test was actually a negative predictor of return on investment, contrary to the claim made in the original 538 article. Also, if you are familiar with the film industry, you would also have noted that returns on investment in the 8-11 range are very atypical: most films are happy to make a small profit, but films earning many times their budget are quite exceptional (and statistically should probably be seen as outliers!)

To improve our analysis and get closer to the last figure of the article (Dollars Earned for Every Dollar Spent), we should make three changes to our analysis:

We should use the median rather than the mean ROI, which is much less sensitive to outliers
We should filter on films from 1990-2013 (as that is what they used)
We should categorize ‘dubious’ films as passing the test, and split between domestic and foreign ROI.

Using the pipeline syntax introduced above, we can make a single analysis pipeline that performs all these steps correctly:

This gives quite a different picture! The numbers are now much more realistic, with up to 37% median profit rather than the earlier exaggerated means. More importantly, the numbers now match the outcome of the 538 article: Films that pass the Bechdel test actually perform better in the US (and Canada, which is considered domestic), while performing similarly in the foreign market. This shows the importance of using the right summary statistic (as well as of preprocessing decisions).

Some things to note about this code:

Similar to mutate, we create multiple columns in the summarize call by passing named arguments, n=, roi_domestic= and roi_foreign=
We used n=n() to add the total amount of cases per group. This is a very useful function, but it can be confusing since it doesn’t take any arguments - it just counts the number of rows in each group.
We don’t store the result in an object, but only display it on the screen. If you want to continue analysing the summary result, you can simply assign it to an object like any other.
We use str_remove(test, "-disagree") to remove the -disagree suffix from the test column, see Section 8.1 for an in-depth discussion of text handling
We prefix the outcomes with a number (1 to 4) to make the alphabetic sorting match the natural ordering. See ?sec-factor for a more elegant solution

Exercise: Bechdel test over time (part 1)

The first figure in the 538 article shows a bar chart per (half) decade of the proportion of films passing the bechdel test. This exercise builds towards reproducing this chart by computing the total number of films per decade per bechdel outcome.

Concretely, starting from the bechdel_decade data:

Use str_remove to remove the -disagree suffix
Summarize the data to get the total amount of films per decade for each test category

🎓 Solution:

Use test = str_remove(test, ‘-disagree’) to remove the suffix
Group by decade and test
Use the n() function to count the number of films

bechdel_over_time <- bechdel_decade |>
  # remove the `-disagree` suffix
  mutate(test = str_remove(test, '-disagree')) |>
  # summarize total amount of films per decade for each `test` category
  group_by(decade, test) |>
  summarize(n_films=n())
head(bechdel_over_time)

Adding group statistics with `group_by |> mutate`

Above we used group_by |> summarize, which replaced the current data set by a summary frame containing only one row per group and dropping all columns except for the group identifiers and summary columns.

Although group_by is often paired with summarize, you can also follow it with a mutate call.

For example, using the decade column calculated earlier, we can calculate the average budget of films per decade:

Two things to notice. First: rather than replacing the data frame with the summary values, group_by |> mutate added the summary values to the existing data set. Second, the values for mean_budget are repeated for the top six rows. In fact, the mean budget in that decade will be repeated for each row in each decade. In that sense, group_by |> mutate is very close to a normal mutate call, in the sense that it just adds (or replaces) the specified columns, leaving the rest of the data alone. What is special about the group_by is that any summary values used in the subsequent mutate are not calculated over the whole data set, but are calculated for each group: in this case the mean budget per decade.

It might seem strange to repeat a value like this, but it is really useful in cases where you want to compare individual rows to the group statistic. For example, we could see which films had a relatively high budget compared to other films in that decade:

As you can see, Avatar was the film with the most exceptionally high budget, having cost more than 8 times the average film in that decade. Interestingly, this top-6 of (relatively) exceptionally expensive movies is a decent list of well known blockbusters, with the possible exception of Shaft (1971) - but admittedly the authors of this book weren’t around back then.

Note that the list of relatively expensive movies is not saved as an object here – it is piped to head(), printing the top 6 rows on the screen for inspection without saving it.

Exercise: Bechdel test over time (part 2)

The first figure in the 538 article shows a bar chart per (half) decade of the proportion of films passing the bechdel test. This exercise builds on the summary made in the previous exercise, turning the absolute counts per decade into proportions.

Concretely, starting from the bechdel_over_time data, compute two new columns: - total_n: The total number of films in each decade - proportion: The percentage of films per category within each decade

The ggplot code to produce the stacked bar chart is given, but don’t worry if it doesn’t make sense yet - it will be explained in detail in the next chapter.

🎓 Solution:

Use group_by(decade), since you want to compute the total number of films per decade
Use total_n = sum(n_films) to calculate the total number of films
Re-use the calculated total_n in the calculation of proportion

# Solution with blanks filled in:
bechdel_over_time_rel <- bechdel_over_time |>
  group_by(decade) |>
  mutate(total_n = sum(n_films),
         proportion = n_films / total_n)

# No need to change the code below, but feel free to have a look!
ggplot(bechdel_over_time_rel, aes(x=decade, y=proportion, fill=test)) +
  geom_col(position="stack") +
  theme_classic() +
  scale_y_continuous(labels = scales::percent) +
  labs(title="The Bechdel Test Over Time",
      x = "Decade", y="", caption="Source: github.com/fivethirtyeight/data")

In this chapter we got two quite different answers to question whether “do Bechdel-passing films make more money?”, depending on seemingly small methodological choices:

mean vs median
what counts as “passing”
which years we include
domestic vs worldwide gross

Which choice do you think is the most impactful? Can you re-run the analysis with only that choice made differently? How does your conclusion change?

Now imagine a journalist asks you: “Do Bechdel-passing films perform better financially?” What would you say?

5.1 Changing or computing values with mutate

The mutate() function

Chaining calculations and calculating multiple values

Exercise

5.2 Recoding values

Recoding into two options with if_else

Recoding multiple values with recode_values and replace_values

Arbitrary recoding with case_when

Exercise

5.3 Grouping and summarizing data

Summarizing data with group_by and summarize

Pipelines: The |> operator

Improving the film analysis pipeline

Exercise: Bechdel test over time (part 1)

Adding group statistics with group_by |> mutate

Exercise: Bechdel test over time (part 2)

5.1 Changing or computing values with `mutate`

The `mutate()` function

Recoding into two options with `if_else`

Recoding multiple values with `recode_values` and `replace_values`

Arbitrary recoding with `case_when`

Summarizing data with `group_by` and `summarize`

Pipelines: The `|>` operator

Adding group statistics with `group_by |> mutate`