dplyr package solution

1.1

Functions introduced in this module:

read.csv, select, rename, rm, filter, slice, arrange, mutate, all.equal, ifelse, transmute, summarise, group_by, %>%

Introduction

This tutorial will import some data from the web and then explore it using the amazing dplyr package, a package which is quickly becoming the de facto standard among R users for manipulating data.

This introduction is modified based on R module “5. Manipulating data” and dplyr tutorial http://genomicsclass.github.io/book/pages/dplyr_tutorial.html.

Load packages

We load the mosaic package as usual, but this time it is to give us access to the dplyr package, which is loaded alongside our other mosaic package commands.

library(mosaic)

Importing CSV data

The file we’ll import is a random sample from all the commercial domestic flights that departed from Houston, Texas, in 2011.

We use the read.csv command to import a CSV file. In this case, we’re grabbing the file from a web page where the file is hosted. If you have a file on your computer, you can also put the file into your project directory and import it from there. Put the URL (for a web page) or the filename (for a file in your project directory) in quotes inside the read.csvcommand. We also need to assign the output to a data frame, so we’ve called it hf for “Houston flights”.

hf <- read.csv("https://raw.githubusercontent.com/VectorPosse/Intro_Stats/master/data/hf.csv")
hf

str(hf)

'data.frame':   22758 obs. of  21 variables:
 $ Year             : int  2011 2011 2011 2011 2011 2011 2011 2011 2011 2011 ...
 $ Month            : int  1 1 1 1 1 1 1 1 1 1 ...
 $ DayofMonth       : int  12 17 24 9 18 22 11 14 26 14 ...
 $ DayOfWeek        : int  3 1 1 7 2 6 2 5 3 5 ...
 $ DepTime          : int  1419 1530 1356 714 721 717 1953 2119 2009 1629 ...
 $ ArrTime          : int  1515 1634 1513 829 827 829 2051 2229 2103 1734 ...
 $ UniqueCarrier    : chr  "AA" "AA" "AA" "AA" ...
 $ FlightNum        : int  428 428 428 460 460 460 533 533 533 1121 ...
 $ TailNum          : chr  "N577AA" "N518AA" "N531AA" "N586AA" ...
 $ ActualElapsedTime: int  56 64 77 75 66 72 58 70 54 65 ...
 $ AirTime          : int  41 48 43 51 46 47 44 45 39 47 ...
 $ ArrDelay         : int  5 84 3 -6 -8 -6 -29 69 -17 -11 ...
 $ DepDelay         : int  19 90 -4 -6 1 -3 -12 74 4 -1 ...
 $ Origin           : chr  "IAH" "IAH" "IAH" "IAH" ...
 $ Dest             : chr  "DFW" "DFW" "DFW" "DFW" ...
 $ Distance         : int  224 224 224 224 224 224 224 224 224 224 ...
 $ TaxiIn           : int  4 8 6 11 7 18 3 5 9 8 ...
 $ TaxiOut          : int  11 8 28 13 13 7 11 20 6 10 ...
 $ Cancelled        : int  0 0 0 0 0 0 0 0 0 0 ...
 $ CancellationCode : chr  "" "" "" "" ...
 $ Diverted         : int  0 0 0 0 0 0 0 0 0 0 ...

The one disadvantage of a file imported from the internet or your computer is that it does not come with a help file. (Only packages in R have help files.) Hopefully you have access to some kind of information about the data you’re importing. In this case, we get lucky because the full Houston flights data set happens to be available in a package called hflights.

Introduction to dplyr

The dplyr package (pronounced “dee-ply-er”) contains tools for manipulating the rows and columns of data frames. The key to using dplyr is to familiarize yourself with the “key verbs”:

• select (and rename)
• filter (and slice)
• arrange
• mutate (and transmute)
• summarise (with group_by)

We’ll consider these one by one. We won’t have time to cover every aspect of these functions. More information appears in the help files, as well as this very helpful “cheat sheet”: https://github.com/rstudio/cheatsheets/raw/master/data-transformation.pdf

select

The select verb is very easy. It just selects some subset of variables (the columns of your data set). Suppose all we wanted to see was the carrier, origin, and destination. We would type

hf_select <- select(hf, UniqueCarrier, Origin, Dest)
hf_select

If you don’t like the names of the variables, you can change them as part of the select process.

hf_select <- select(hf, carrier = UniqueCarrier,
                    origin = Origin, dest = Dest)
hf_select

There are a few notational shortcuts. For example, see what the following do.

hf_select2 <- select(hf, DayOfWeek:UniqueCarrier)
hf_select2

hf_select3 <- select(hf, starts_with("Taxi"))
hf_select3

The rm command

Recall that earlier we mentioned the pros and cons of creating a new data frame every time we make a change. On one hand, making a new data frame instead of overwriting the original one will keep the original one available so that we can run different commands on it. On the other hand, making a new data frame does eat up a lot of memory.

One way to get rid of an object once we are done with it is the rm command, where rm is short for “remove”. When you run the code chunk below, you’ll see that all the data frames we created with select will disappear from your Global Environment.

rm(hf_select, hf_select2, hf_select3)

If you need one these data frames back later, you can always go back and re-run the code chunk that defined it.

We’ll use rm at the end of some of the following sections so that we don’t use up too much memory.

filter

The filter verb works a lot like select, but for rows instead of columns.

For example, let’s say we only want to see Delta flights. We use filter:

hf_filter <- filter(hf, UniqueCarrier == "DL")
hf_filter

In the printout of the data frame above, if you can’t see the UniqueCarrier column, click the black arrow on the right to scroll through the columns until you can see it. You can click “Next” at the bottom to scroll through the rows.

Just like select, the first argument is the data frame. Following that, you must specify some condition. Only rows meeting that condition will be included in the output.

Condition includes ==, <, >, !=, %in%, etc. Logical operations includes !, &, |.

As another example, suppose we wanted to find out all flights that leave before 6:00 a.m.

hf_filter2 <- filter(hf, DepTime < 600)
hf_filter2

The following will give us only the Delta flights that departed before 6:00 a.m.

hf_filter3 <- filter(hf, UniqueCarrier == "DL" & DepTime < 600)
# Another way to filter
# hf_filter3 <- filter(hf, UniqueCarrier == "DL", DepTime < 600)
hf_filter3

Again, check the cheat sheet for more complicated condition-checking.

Exercise 1

Use the filter command to create a data frame called hf_filter4 that finds all flights except those flying into Salt Lake City (“SLC”). As before, print the output to the screen.

# Add code here to define hf_filter4.
# Add code here to print hf_filter4.
hf_filter4 <- filter(hf, Dest != "SLC")
hf_filter4

Exercise 2

Use filter with %in% to create a data frame called hf_filter5 that only contains flight information of AA, DL, and UA.

# Add code here to define hf_filter5.
# Add code here to print hf_filter5.
hf_filter5 <- filter(hf, UniqueCarrier %in% c("AA", "DL", "UA"))
hf_filter5

Now use the rm command to remove all the extra data frames you created in this section with filter.

rm(hf_filter, hf_filter2, hf_filter3, hf_filter4, hf_filter5)

arrange

This just re-orders the rows, sorting on the values of one or more specified columns. As I mentioned before, in most data analyses you work with summaries of the data that do not depend on the order of the rows, so this is not quite as interesting as some of the other verbs. In fact, since the re-ordering is usually for the visual benefit of the reader, there is often no need to store the output in a new variable. We’ll just print the output to the screen.

arrange(hf, ActualElapsedTime)

Scroll over to the ActualElapsedTime variable in the output below to see that these are now sorted in ascending order.

Exercise 3

How long is the shortest actual elapsed time? Why is this flight so short? (Hint: look at the destination.) Which airline flies that route? You may have to use your best friend Google to look up airport and airline codes.

Please write up your answer here.

35 minutes to Austin because Houston and Austin are in the same state. The airline is Southwest.

---

If you want descending order, do this:

arrange(hf, desc(ActualElapsedTime))

mutate

Frequently, we want to create new variables that combine information from one or more existing variables. We use mutate for this. For example, suppose we wanted to find the total time of the flight. We might do this by adding up the minutes from several variables: TaxiOut, AirTime, and TaxiIn, and assigning that sum to a new variable called total. Scroll all the way to the right in the output below to see the new total variable.

hf_mutate <- mutate(hf, total = TaxiOut + AirTime + TaxiIn)
hf_mutate

As it turns out, that was wasted effort because that variable already exists in ActualElapsedTime. (The all.equal command checks that both specified columns contain the exact same values.)

all.equal(hf_mutate$total, hf$ActualElapsedTime)

[1] TRUE

Perhaps we want a variable that just classifies a flight as arriving late or not. Scroll all the way to the right to see the new late variable.

hf_mutate2 <- mutate(hf, late = (ArrDelay > 0))
hf_mutate2

Having said that, I would generally recommend that you leave these kinds of variables as logical types. It’s much easier to summarize such variables in R, namely because R treats TRUE as 1 and FALSE as 0, allowing us to do things like this:

mean(hf_mutate2$late, na.rm = TRUE)

[1] 0.4761522

This gives us the percentage of late flights.

One note of explanation in the mean command: there is an argument na.rm. This stands for “NA remove”, which refers to R’s convention of using NA to refer to missing values. For example, look at the 93rd row of the data frame:

hf_mutate2[93, ]

Notice that the all the times are missing. There are a bunch of rows like this. Since there is not always an arrival delay listed, the ArrDelay variable doesn’t always have a value, and if ArrDelay is NA, the late variable will be too. So if we try to calculate the mean with just the mean command, this happens:

mean(hf_mutate2$late)

[1] NA

Therefore, we have to remove the NAs in order to properly calculate the mean, and that’s what the na.rm argument does.

Exercise 4

Create a new data frame called hf_mutate3 that uses the mutate command to create a new variable called dist_k which measures the flight distance in kilometers instead of miles. (Hint: to get from miles to kilometers, multiply the distance by 1.60934.) Print the output to the screen.

# Add code here to define hf_mutate3.
hf_mutate3 <- mutate(hf, disk_k = Distance * 1.60934)
# Add code here to print hf_mutate3.
hf_mutate3

Before moving on to the next section, we’ll clean up the extra data frames lying around:

rm(hf_mutate, hf_mutate2, hf_mutate3)

summarise (with group_by)

First, before you mention that summarise is spelled wrong…well, the author of the dplyr package is named Hadley Wickham and he is from New Zealand. So that’s the way he spells it. He was nice enough to include the summarize function as an alias if you need to use it ’cause this is ’Murica!

The summarise function, by itself, is kind of boring, and doesn’t do anything that couldn’t be done more easily with base R functions.

summarise(hf, mean(Distance))

mean(hf$Distance)

[1] 790.5861

Where summarise shines is in combination with group_by. For example, let’s suppose that we want to see average flight distances, but broken down by airline.

hf_summ_grouped <- group_by(hf, UniqueCarrier)
hf_summ <- summarise(hf_summ_grouped, mean(Distance))

`summarise()` ungrouping output (override with `.groups` argument)

hf_summ

This is a good spot to introduce a time-saving and helpful device called “piping”, denoted by the symbol %>%. Piping always looks more complicated than it really is. The technical definition is that

x %>% f(y)

is equivalent to

f(x, y).

As a simple example, we could add two numbers like this:

sum(2, 3)

[1] 5

Or using the pipe, we could do it like this:

2 %>% sum(3)

[1] 5

All this is really saying is that the pipe takes the thing on its left, and plugs it into the first slot of the function on its right. So why do we care?

Let’s revisit the combination group_by/summarise example above. There are two ways to do this without pipes, and both are a little ugly. One way is above, where you have to keep reassigning the output to new variables (in the case above, to hf_summ_grouped and then hf_summ). The other way is to nest the functions:

summarise(group_by(hf, UniqueCarrier), mean(Distance))

`summarise()` ungrouping output (override with `.groups` argument)

This requires a lot of brain power to parse. In part, this is because the function is inside-out: first you group hf by UniqueCarrier, and then the result of that is summarized. Here’s how the pipe fixes it:

hf %>%
    group_by(UniqueCarrier) %>%
    summarise(mean(Distance))

`summarise()` ungrouping output (override with `.groups` argument)

Look at the group_by line. The group_by function should take two arguments, the data frame, and then the grouping variable. It appears to have only one argument. But look at the previous line. The pipe says to insert whatever is on its left (hf) into the first slot of the function on its right (group_by). So the net effect is still to evaluate the function group_by(hf, UniqueCarrier). Now look at the summarise line. Again, summarise is a function of two inputs, but all we see is the part that finds the mean. The pipe at the end of the previous line tells the summarise function to insert the stuff already computed (the grouped data frame returned by group_by(hf, UniqueCarrier)) into the first slot of the summarise function.

Piping takes a little getting used to, but once you’re good at it, you’ll never go back. It’s just makes more sense semantically. When I read the above set of commands, I see a set of instructions in chronological order:

Take the data frame hf. Now group by the carrier. Next summarize each group using the mean distance.

Now we can assign the result of all that to the new variable hf_summ:

hf_summ <- hf %>%
    group_by(UniqueCarrier) %>%
    summarise(mean(Distance))

`summarise()` ungrouping output (override with `.groups` argument)

hf_summ

Let’s try some counting using summarise. What if we wanted to know how many flights correspond to each carrier?

hf_summ2 <- hf %>%
    group_by(UniqueCarrier) %>%
    summarize(total_count = n())

`summarise()` ungrouping output (override with `.groups` argument)

hf_summ2

You can see the n() function at work. It simply counts the observations in each group. (You do need to include the empty parentheses after the n!) Also note that we can give the summary column a new name if we wish. In hf_summ, we didn’t give the new column an explicit name, so it showed up in our data frame as a column called mean(Distance). If we hadn’t included total_count in our definition of hf_summ2, the new column would simply be called n(). That’s okay, but not anywhere near as user-friendly as total_count.

Exercise 5

Create a data frame called hf_summ3 that lists the total count of flights for each day of the week. Be sure to use the pipe as above. Print the output to the screen

# Add code here to define hf_summ3.
hf_summ3 <- hf %>% 
  group_by(DayOfWeek) %>%
  summarize(total_count = n())

`summarise()` ungrouping output (override with `.groups` argument)

# Add code here to print hf_summ3.
hf_summ3

Do not forget to remove datasets which you do not need anymore but cost the memory.

rm(hf_summ_grouped, hf_summ, hf_summ2, hf_summ3)

Putting it all together

Often we need more than one of these verbs. In many data analyses, we need to do a sequence of operations to get at the answer we seek. This is most easily accomplished using a more complicated sequence of pipes.

Here’s a example of multi-step piping. Let’s say that we only care about Delta flights, and even then, we only want to know about the month of the flight and the departure delay. From there, we wish to group by months so we can find the maximum departure delay by month. Here is a solution, piping hot and ready to go. [groan]

hf_grand_finale <- hf %>%
    filter(UniqueCarrier == "DL") %>%
    group_by(Month) %>%
    summarise(max_delay = max(DepDelay, na.rm = TRUE))

`summarise()` ungrouping output (override with `.groups` argument)

hf_grand_finale

Go through each line of code carefully and translate it into English:

1. We define a variable called hf_grand_finale that starts with the original hf data.
2. We filter this data so that only Delta flights will be analyzed.
3. We group_by month so that the results will be displayed by month.
4. We summarise each month by listing the maximum value of DepDelay that appears within each month.
5. We print the result to the screen.

Exercise 6

Create a data frame called hf_final2 that counts the flights to LAX that were canceled, grouping by day of the week. (Hint: you need to filter to get both flights to LAX and flights that are canceled. Then you’ll need to group_by the day of the week in order to summarise the number of cancellations using n().) Print the output to the screen.

# Add code here to count the flights to LAX that were canceled,
# grouping by day of the week.
# Print the output to the screen.
hf_final2 <- hf %>%
    filter(Dest == "LAX" & Cancelled == 1) %>%
    group_by(DayOfWeek) %>%
    summarise(LAX_cancel = n())

`summarise()` ungrouping output (override with `.groups` argument)

hf_final2

Exercise 7

Create a data frame called hf_final3 that finds the median distance flight for each airline. Sort the resulting data frame from highest distance to lowest. (Hint: You’ll need to group_by carrier and summarise using the median function. Finally, you’ll need to arrange the result according to the median distance variable that you just created.) Print the output to the screen.

# Add code here to find the median distance by airline.
# Print the output to the screen.
hf_final3 <- hf %>%
  group_by(UniqueCarrier) %>%
  summarize(med_dis = median(Distance)) %>%
  arrange(desc(med_dis))

`summarise()` ungrouping output (override with `.groups` argument)

hf_final3

Conclusion

Raw data often doesn’t come in the right form for us to run our analyses. The dplyr verbs are powerful tools for manipulating data frames until they are in the right form.