Manipulating Data with dplyr

In this lesson, you’ll learn how to manipulate data using dplyr. dplyr is a fast and powerful R package written by Hadley Wickham and Romain Francois that provides a consistent and concise grammar for manipulating tabular data.

One unique aspect of dplyr is that the same set of tools allow you to work with tabular data from a variety of sources, including data frames, data tables, databases and multidimensional arrays. In this lesson, we’ll focus on data frames, but everything you learn will apply equally to other formats.

As you may know, “CRAN is a network of ftp and web servers around the world that store identical, up-to-date, versions of code and documentation for R” (http://cran.rstudio.com/). RStudio maintains one of these so-called ‘CRAN mirrors’ and they generously make their download logs publicly available (http://cran-logs.rstudio.com/).

We’ll be working with the log from July 8, 2014, which contains information on roughly 225,000 package downloads.

I’ve created a variable called path2csv, which contains the full file path to the dataset. Call read.csv() with two arguments, path2csv and stringsAsFactors = FALSE, and save the result in a new variable called mydf. Check ?read.csv if you need help.

path2csv <- "/home/cabunic/R/x86_64-pc-linux-gnu-library/3.4/swirl/Courses/Getting_and_Cleaning_Data/Manipulating_Data_with_dplyr/2014-07-08.csv"
mydf <- read.csv(path2csv, stringsAsFactors = FALSE)

Use dim() to look at the dimensions of mydf.

dim(mydf)

[1] 225468     11

Now use head() to preview the data.

head(mydf)

The dplyr package was automatically installed (if necessary) and loaded at the beginning of this lesson. Normally, this is something you would have to do on your own. Just to build the habit, type library(dplyr) now to load the package again.

library(dplyr)

It’s important that you have dplyr version 0.4.0 or later. To confirm this, type packageVersion(“dplyr”).

If your dplyr version is not at least 0.4.0, then you should hit the Esc key now, reinstall dplyr, then resume this lesson where you left off.

packageVersion("dplyr")

[1] ‘0.7.4’

The first step of working with data in dplyr is to load the data into what the package authors call a ‘data frame tbl’ or ‘tbl_df’. Use the following code to create a new tbl_df called cran: cran <- tbl_df(mydf).

cran <- tbl_df(mydf)

To avoid confusion and keep things running smoothly, let’s remove the original data frame from your workspace with rm(“mydf”).

rm("mydf")

From ?tbl_df, “The main advantage to using a tbl_df over a regular data frame is the printing.” Let’s see what is meant by this. Type cran to print our tbl_df to the console.

cran

This output is much more informative and compact than what we would get if we printed the original data frame (mydf) to the console.

First, we are shown the class and dimensions of the dataset. Just below that, we get a preview of the data. Instead of attempting to print the entire dataset, dplyr just shows us the first 10 rows of data and only as many columns as fit neatly in our console. At the bottom, we see the names and classes for any variables that didn’t fit on our screen.

According to the “Introduction to dplyr” vignette written by the package authors, “The dplyr philosophy is to have small functions that each do one thing well.”

Specifically, dplyr supplies five ‘verbs’ that cover most fundamental data manipulation tasks:

1. select()

2. filter()

3. arrange()

4. mutate()

5. summarize()

---

select()

Use ?select to pull up the documentation for the first of these core functions.

?select

Help files for the other functions are accessible in the same way.

As may often be the case, particularly with larger datasets, we are only interested in some of the variables. Use select(cran, ip_id, package, country) to select only the ip_id, package, and country variables from the cran dataset.

select(cran, ip_id, package, country)

The first thing to notice is that we don’t have to type cran\(ip_id, cran\)package, and cran$country, as we normally would when referring to columns of a data frame. The select() function knows we are referring to columns of the cran dataset.

Also, note that the columns are returned to us in the order we specified, even though ip_id is the rightmost column in the original dataset.

Recall that in R, the : operator provides a compact notation for creating a sequence of numbers. For example, try 5:20.

5:20

 [1]  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20

Normally, this notation is reserved for numbers, but select() allows you to specify a sequence of columns this way, which can save a bunch of typing. Use select(cran, r_arch:country) to select all columns starting from r_arch and ending with country.

select(cran, r_arch:country)

We can also select the same columns in reverse order. Give it a try.

select(cran, country:r_arch)

Print the entire dataset again, just to remind yourself of what it looks like. You can do this at anytime during the lesson.

cran

Instead of specifying the columns we want to keep, we can also specify the columns we want to throw away. To see how this works, do select(cran, -time) to omit the time column.

select(cran, -time)

The negative sign in front of time tells select() that we DON’T want the time column. Now, let’s combine strategies to omit all columns from X through size (X:size). To see how this might work, let’s look at a numerical example with -5:20.

-5:20

 [1] -5 -4 -3 -2 -1  0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20

Oops! That gaves us a vector of numbers from -5 through 20, which is not what we want. Instead, we want to negate the entire sequence of numbers from 5 through 20, so that we get -5, -6, -7, … , -18, -19, -20. Try the same thing, except surround 5:20 with parentheses so that R knows we want it to first come up with the sequence of numbers, then apply the negative sign to the whole thing.

-(5:20)

 [1]  -5  -6  -7  -8  -9 -10 -11 -12 -13 -14 -15 -16 -17 -18 -19 -20

Use this knowledge to omit all columns X:size using select().

select(cran, -(X:size))

---

filter()

Now that you know how to select a subset of columns using select(), a natural next question is “How do I select a subset of rows?” That’s where the filter() function comes in.

Use filter(cran, package == “swirl”) to select all rows for which the package variable is equal to “swirl”. Be sure to use two equals signs side-by-side!

filter(cran, package == "swirl")

Again, note that filter() recognizes ‘package’ as a column of cran, without you having to explicitly specify cran$package.

The == operator asks whether the thing on the left is equal to the thing on the right. If yes, then it returns TRUE. If no, then FALSE. In this case, package is an entire vector (column) of values, so package == “swirl” returns a vector of TRUEs and FALSEs. filter() then returns only the rows of cran corresponding to the TRUEs.

You can specify as many conditions as you want, separated by commas. For example filter(cran, r_version == “3.1.1”, country == “US”) will return all rows of cran corresponding to downloads from users in the US running R version 3.1.1. Try it out.

filter(cran, r_version == "3.1.1", country == "US")

The conditions passed to filter() can make use of any of the standard comparison operators. Pull up the relevant documentation with ?Comparison (that’s an uppercase C).

?Comparison

Edit your previous call to filter() to instead return rows corresponding to users in “IN” (India) running an R version that is less than or equal to “3.0.2”. The up arrow on your keyboard may come in handy here. Don’t forget your double quotes!

filter(cran, r_version <= "3.0.2", country == "IN")

Our last two calls to filter() requested all rows for which some condition AND another condition were TRUE. We can also request rows for which EITHER one condition OR another condition are TRUE. For example, filter(cran, country == “US” | country == “IN”) will gives us all rows for which the country variable equals either “US” or “IN”. Give it a go.

filter(cran, country == "US" | country == "IN")

Now, use filter() to fetch all rows for which size is strictly greater than (>) 100500 (no quotes, since size is numeric) AND r_os equals “linux-gnu”. Hint: You are passing three arguments to filter(): the name of the dataset, the first condition, and the second condition.

filter(cran, size > 100500, r_os == "linux-gnu")

Finally, we want to get only the rows for which the r_version is not missing. R represents missing values with NA and these missing values can be detected using the is.na() function.

To see how this works, try is.na(c(3, 5, NA, 10)).

is.na(c(3, 5, NA, 10))

[1] FALSE FALSE  TRUE FALSE

Now, put an exclamation point (!) before is.na() to change all of the TRUEs to FALSEs and all of the FALSEs to TRUEs, thus telling us what is NOT NA: !is.na(c(3, 5, NA, 10)).

!is.na(c(3, 5, NA, 10))

[1]  TRUE  TRUE FALSE  TRUE

Okay, ready to put all of this together? Use filter() to return all rows of cran for which r_version is NOT NA. Hint: You will need to use !is.na() as part of your second argument to filter().

filter(cran, !is.na(r_version))

We’ve seen how to select a subset of columns and rows from our dataset using select() and filter(), respectively. Inherent in select() was also the ability to arrange our selected columns in any order we please.

---

arrange()

Sometimes we want to order the rows of a dataset according to the values of a particular variable. This is the job of arrange().

To see how arrange() works, let’s first take a subset of cran. select() all columns from size through ip_id and store the result in cran2.

cran2 <- select(cran, size:ip_id)

Now, to order the ROWS of cran2 so that ip_id is in ascending order (from small to large), type arrange(cran2, ip_id). You may want to make your console wide enough so that you can see ip_id, which is the last column.

arrange(cran2, ip_id)

To do the same, but in descending order, change the second argument to desc(ip_id), where desc() stands for ‘descending’. Go ahead.

arrange(cran2, desc(ip_id))

We can also arrange the data according to the values of multiple variables. For example, arrange(cran2, package, ip_id) will first arrange by package names (ascending alphabetically), then by ip_id. This means that if there are multiple rows with the same value for package, they will be sorted by ip_id (ascending numerically). Try arrange(cran2, package, ip_id) now.

arrange(cran2, package, ip_id)

arrange(cran2, country, desc(r_version), ip_id)

---

mutate()

To illustrate the next major function in dplyr, let’s take another subset of our original data. Use select() to grab 3 columns from cran – ip_id, package, and size (in that order) – and store the result in a new variable called cran3.

cran3 <- select(cran, ip_id, package, size)

Take a look at cran3 now.

cran3

It’s common to create a new variable based on the value of one or more variables already in a dataset. The mutate() function does exactly this.

The size variable represents the download size in bytes, which are units of computer memory. These days, megabytes (MB) are a more common unit of measurement. One megabyte is equal to 2^20 bytes. That’s 2 to the power of 20, which is approximately one million bytes!

We want to add a column called size_mb that contains the download size in megabytes. Here’s the code to do it: mutate(cran3, size_mb = size / 2^20)

mutate(cran3, size_mb = size / 2^20)

An even larger unit of memory is a gigabyte (GB), which equals 2^10 megabytes. We might as well add another column for download size in gigabytes!

One very nice feature of mutate() is that you can use the value computed for your second column (size_mb) to create a third column, all in the same line of code. To see this in action, repeat the exact same command as above, except add a third argument creating a column that is named size_gb and equal to size_mb / 2^10.

mutate(cran3, size_mb = size/2^20, size_gb = size_mb/2^10)

Let’s try one more for practice. Pretend we discovered a glitch in the system that provided the original values for the size variable. All of the values in cran3 are 1000 bytes less than they should be. Using cran3, create just one new column called correct_size that contains the correct size.

mutate(cran3, correct_size = size + 1000)

---

summarize()

The last of the five core dplyr verbs, summarize(), collapses the dataset to a single row. Let’s say we’re interested in knowing the average download size. summarize(cran, avg_bytes = mean(size)) will yield the mean value of the size variable. Here we’ve chosen to label the result ‘avg_bytes’, but we could have named it anything. Give it a try.

summarize(cran, avg_bytes = mean(size))

That’s not particularly interesting. summarize() is most useful when working with data that has been grouped by the values of a particular variable.

We’ll look at grouped data in the next lesson, but the idea is that summarize() can give you the requested value FOR EACH group in your dataset.

In this lesson, you learned how to manipulate data using dplyr’s five main functions. In the next lesson, we’ll look at how to take advantage of some other useful features of dplyr to make your life as a data analyst much easier.

---

END

---