--- title: "2 Data transformation" author: " R for Data Science (available online)" format: html: toc: true toc-depth: 2 code-copy: true df-print: paged embed-resources: true execute: warning: false message: false --- ## 2.1 Introduction Visualization is an important tool for generating insight, but it’s rare that you get the data in exactly the right form you need to make the graph you want. Often you’ll need to create some new variables or summaries to answer your questions with your data, or maybe you just want to rename the variables or reorder the observations to make the data a little easier to work with. You’ll learn how to do all that (and more!) in today's discussion, which will introduce you to data transformation using the dplyr package and a dataset on flights that departed from New York City in 2013. The goal for today is to give you an overview of some of the key tools for transforming a data frame. We’ll start with functions that operate on rows and then columns of a data frame. ### 2.1.1 Prerequisites In today’s discussion, we’ll focus on the dplyr package, another core member of the tidyverse. We’ll illustrate the key ideas using data from the nycflights13 package and use ggplot2 to help us understand the data. ```{r} # install nycflights13 using install.packages("nycflights13") first library(tidyverse) library(nycflights13) ``` Take careful note of the conflicts message that’s printed when you load the tidyverse. It tells you that dplyr overwrites some functions in base R. If you want to use the base version of these functions after loading dplyr, you’ll need to use their full names: stats::filter() and stats::lag(). So far, we’ve mostly ignored which package a function comes from because it doesn’t usually matter. However, knowing the package can help you find help and find related functions, so when we need to be precise about which package a function comes from, we’ll use the same syntax as R: packagename::functionname(). ### 2.1.2 nycflights13 To explore the basic dplyr verbs, we will use nycflights13::flights. This dataset contains all 336,776 flights that departed from New York City in 2013. The data comes from the US Bureau of Transportation Statistics and is documented in ?flights. flights is a tibble, a special type of data frame used by the tidyverse to avoid some common gotchas. The most important difference between tibbles and data frames is the way tibbles print; they are designed for large datasets, so they only show the first few rows and only the columns that fit on one screen. There are a few options to see everything. If you’re using RStudio, the most convenient is probably View(flights), which opens an interactive, scrollable, and filterable view. Otherwise you can use print(flights, width = Inf) to show all columns, or use glimpse(): ```{r} glimpse(flights) ``` In both views, the variable names are followed by abbreviations that tell you the type of each variable: is short for integer, is short for double (aka real numbers), for character (aka strings), and for date-time. These are important because the operations you can perform on a column depend heavily on its “type.” ### 2.1.3 dplyr basics You’re about to learn the primary dplyr verbs (functions), which will allow you to solve the vast majority of your data manipulation challenges. But before we discuss their individual differences, it’s worth stating what they have in common: 1) The first argument is always a data frame. 2) The subsequent arguments typically describe which columns to operate on using the variable names (without quotes). 3) The output is always a new data frame. Because each verb does one thing well, solving complex problems will usually require combining multiple verbs, and we’ll do so with the pipe, |>. We’ll discuss the pipe more in Section 3.4, but in brief, the pipe takes the thing on its left and passes it along to the function on its right so that x |> f(y) is equivalent to f(x, y), and x |> f(y) |> g(z) is equivalent to g(f(x, y), z). The easiest way to pronounce the pipe is “then”. That makes it possible to get a sense of the following code even though you haven’t yet learned the details: ```{r} flights |> filter(dest == "IAH") |> group_by(year, month, day) |> summarize( arr_delay = mean(arr_delay, na.rm = TRUE) ) ``` dplyr’s verbs are organized into four groups based on what they operate on: rows, columns, groups, or tables. In the following sections, you’ll learn the most important verbs for rows, columns, and groups. ## 2.2 Rows The most important verbs that operate on rows of a dataset are filter(), which changes which rows are present without changing their order, and arrange(), which changes the order of the rows without changing which are present. Both functions only affect the rows, and the columns are left unchanged. We’ll also discuss distinct() which finds rows with unique values. Unlike arrange() and filter() it can also optionally modify the columns. ### 2.2.1 filter() filter() allows you to keep rows based on the values of the columns1. The first argument is the data frame. The second and subsequent arguments are the conditions that must be true to keep the row. For example, we could find all flights that departed more than 120 minutes (two hours) late: ```{r} flights |> filter(dep_delay > 120) ``` As well as > (greater than), you can use >= (greater than or equal to), < (less than), <= (less than or equal to), == (equal to), and != (not equal to). You can also combine conditions with & or , to indicate “and” (check for both conditions) or with | to indicate “or” (check for either condition): ```{r} # Flights that departed on January 1 flights |> filter(month == 1 & day == 1) # Flights that departed in January or February flights |> filter(month == 1 | month == 2) ``` There’s a useful shortcut when you’re combining | and ==: %in%. It keeps rows where the variable equals one of the values on the right: ```{r} # A shorter way to select flights that departed in January or February flights |> filter(month %in% c(1, 2)) ``` When you run filter() dplyr executes the filtering operation, creating a new data frame, and then prints it. It doesn’t modify the existing flights dataset because dplyr functions never modify their inputs. To save the result, you need to use the assignment operator, <-: ```{r} jan1 <- flights |> filter(month == 1 & day == 1) ``` ### 2.2.2 arrange() arrange() changes the order of the rows based on the value of the columns. It takes a data frame and a set of column names (or more complicated expressions) to order by. If you provide more than one column name, each additional column will be used to break ties in the values of the preceding columns. For example, the following code sorts by the departure time, which is spread over four columns. We get the earliest years first, then within a year, the earliest months, etc. ```{r} flights |> arrange(year, month, day, dep_time) ``` You can use desc() on a column inside of arrange() to re-order the data frame based on that column in descending (big-to-small) order. For example, this code orders flights from most to least delayed: ```{r} flights |> arrange(desc(dep_delay)) ``` ### 2.2.3 distinct() distinct() finds all the unique rows in a dataset, so technically, it primarily operates on the rows. Most of the time, however, you’ll want the distinct combination of some variables, so you can also optionally supply column names: ```{r} # Remove duplicate rows, if any flights |> distinct() # Find all unique origin and destination pairs flights |> distinct(origin, dest) ``` Alternatively, if you want to keep the other columns when filtering for unique rows, you can use the .keep_all = TRUE option. ```{r} flights |> distinct(origin, dest, .keep_all = TRUE) ``` It’s not a coincidence that all of these distinct flights are on January 1: distinct() will find the first occurrence of a unique row in the dataset and discard the rest. If you want to find the number of occurrences instead, you’re better off swapping distinct() for count(). With the sort = TRUE argument, you can arrange them in descending order of the number of occurrences. ```{r} flights |> count(origin, dest, sort = TRUE) ``` ### 2.2.4 Exercises 1. In a single pipeline for each condition, find all flights that meet the condition: - Had an arrival delay of two or more hours - Flew to Houston (IAH or HOU) - Were operated by United, American, or Delta - Departed in summer (July, August, and September) - Arrived more than two hours late but didn’t leave late - Were delayed by at least an hour, but made up over 30 minutes in flight 2. Sort flights to find the flights with the longest departure delays. Find the flights that left earliest in the morning. 3. Sort flights to find the fastest flights. (Hint: Try including a math calculation inside of your function.) 4. Was there a flight on every day of 2013? 5. Which flights traveled the farthest distance? Which traveled the least distance? 6. Does it matter what order you used filter() and arrange() if you’re using both? Why/why not? Think about the results and how much work the functions would have to do. ## 2.3 Columns There are four important verbs that affect the columns without changing the rows: mutate() creates new columns that are derived from the existing columns, select() changes which columns are present, rename() changes the names of the columns, and relocate() changes the positions of the columns. ### 2.3.1 mutate() The job of mutate() is to add new columns that are calculated from the existing columns. In the transform chapters, you’ll learn a large set of functions that you can use to manipulate different types of variables. For now, we’ll stick with basic algebra, which allows us to compute the gain, how much time a delayed flight made up in the air, and the speed in miles per hour: ```{r} flights |> mutate( gain = dep_delay - arr_delay, speed = distance / air_time * 60 ) ``` By default, mutate() adds new columns on the right-hand side of your dataset, which makes it difficult to see what’s happening here. We can use the .before argument to instead add the variables to the left-hand side: ```{r} flights |> mutate( gain = dep_delay - arr_delay, speed = distance / air_time * 60, .before = 1 ) ``` The . indicates that .before is an argument to the function, not the name of a third new variable we are creating. You can also use .after to add after a variable, and in both .before and .after you can use the variable name instead of a position. For example, we could add the new variables after day: ```{r} flights |> mutate( gain = dep_delay - arr_delay, speed = distance / air_time * 60, .after = day ) ``` ### 2.3.1 select() It’s not uncommon to get datasets with hundreds or even thousands of variables. In this situation, the first challenge is often just focusing on the variables you’re interested in. select() allows you to rapidly zoom in on a useful subset using operations based on the names of the variables: Select columns by name: ```{r} flights |> select(year, month, day) ``` Select all columns between year and day (inclusive): ```{r} flights |> select(year:day) ``` Select all columns except those from year to day (inclusive): ```{r} flights |> select(!year:day) ``` ### 2.3.3 rename() If you want to keep all the existing variables and just want to rename a few, you can use rename() instead of select(): ```{r} flights |> rename(tail_num = tailnum) ``` ### 3.3.4 relocate() Use relocate() to move variables around. You might want to collect related variables together or move important variables to the front. By default relocate() moves variables to the front: ```{r} flights |> relocate(time_hour, air_time) ``` ### 2.3.5 Exercises 1. Compare dep_time, sched_dep_time, and dep_delay. How would you expect those three numbers to be related? 2. Brainstorm as many ways as possible to select dep_time, dep_delay, arr_time, and arr_delay from flights. 3. What happens if you specify the name of the same variable multiple times in a select() call? 4. What does the any_of() function do? Why might it be helpful in conjunction with this vector? ```{r} variables <- c("year", "month", "day", "dep_delay", "arr_delay") ``` 5. Does the result of running the following code surprise you? How do the select helpers deal with upper and lower case by default? How can you change that default? ```{r} flights |> select(contains("TIME")) ``` 6. Rename air_time to air_time_min to indicate units of measurement and move it to the beginning of the data frame. 7. Why doesn’t the following work, and what does the error mean?