R tutorial: How to reshape data in R

Combining multiple datasets, whether by stacking or joining, is commonly necessary as is changing the shape of data. The plyr and reshape2 packages offer good functions for accomplishing this in addition to base tools such as rbind, cbind, and merge.

Manipulating data takes a great deal of effort before serious analysis can begin. In this chapter, I consider when the data need to be rearranged from column-oriented to row-oriented (or the opposite) and when the data are in multiple, separate sets and need to be combined into one.

There are base functions to accomplish these tasks, but I focus on those in plyr, reshape2, and data.table.

While the tools covered in this chapter still form the backbone of data reshaping, newer packages like tidyr and dplyr are starting to supersede them.

cbind and rbind

The simplest case is when you have a two datasets with either identical columns (both the number of and names) or the same number of rows. In this case, either rbind or cbind work great.

As a first trivial example, I create two simple data.frames by combining a few vectors with cbind, and then stack them using rbind.

> # make three vectors and combine them as columns in a data.frame
> sport <- c("Hockey", "Baseball", "Football")
> league <- c("NHL", "MLB", "NFL")
> trophy <- c("Stanley Cup", "Commissioner's Trophy",
+             "Vince Lombardi Trophy")
> trophies1 <- cbind(sport, league, trophy)
>
> # make another data.frame using data.frame()
> trophies2 <- data.frame(sport=c("Basketball", "Golf"),
+                         league=c("NBA", "PGA"),
+                         trophy=c("Larry O'Brien Championship Trophy",
+                                  "Wanamaker Trophy"),
+                         stringsAsFactors=FALSE)
>
> # combine them into one data.frame with rbind
> trophies <- rbind(trophies1, trophies2)

Both cbind and rbind can take multiple arguments to combine an arbitrary number of objects. Note that it is possible to assign new column names to vectors in cbind.

>> cbind(Sport=sport, Association=league, Prize=trophy)

     Sport      Association Prize
[1,] "Hockey"   "NHL"       "Stanley Cup"
[2,] "Baseball" "MLB"       "Commissioner's Trophy"
[3,] "Football" "NFL"       "Vince Lombardi Trophy"

Joins

Data do not always come so nicely aligned for combing using cbind and need to be joined together using a common key. This concept should be familiar to SQL users. Joins in R are not as flexible as SQL joins, but are still an essential operation in the data analysis process.

The three most commonly used functions for joins are merge in base R, join in plyr, and the merging functionality in data.table. Each has pros and cons, with some pros outweighing their respective cons.

To illustrate these functions I have prepared data originally made available as part of the USAID Open Government initiative. The data have been chopped into eight separate files so that they can be joined together. They are all available in a zip file at http://jaredlander.com/data/US_Foreign_Aid.zip. These should be downloaded and unzipped to a folder on our computer. This can be done a number of ways (including using a mouse!) but we show how to download and unzip using R.

> download.file(url="http://jaredlander.com/data/US_Foreign_Aid.zip",
+              destfile="data/ForeignAid.zip")
> unzip("data/ForeignAid.zip", exdir="data")

To load all of these files programatically, we utilize a for loop as seen in Section 10.1. We get a list of the files using dir, and then loop through that list, assigning each dataset to a name specified using assign. The function str_sub extracts individual characters from a character vector and is explained in Section 16.3.

>> library(stringr)
> # first get a list of the files
> theFiles <- dir("data/", pattern="\\.csv")
> ## loop through those files
> for(a in theFiles)
+ {
+     # build a good name to assign to the data
+     nameToUse <- str_sub(string=a, start=12, end=18)
+     # read in the csv using read.table
+     # file.path is a convenient way to specify a folder and file name
+     temp <- read.table(file=file.path("data", a),
+                        header=TRUE, sep=",", stringsAsFactors=FALSE)
+     # assign them into the workspace
+     assign(x=nameToUse, value=temp)
+ }

merge

R comes with a built-in function, called merge, to merge two data.frames.

> Aid90s00s <- merge(x=Aid_90s, y=Aid_00s,
+                    by.x=c("Country.Name", "Program.Name"),
+                    by.y=c("Country.Name", "Program.Name"))
> head(Aid90s00s)

  Country.Name                                      Program.Name
1  Afghanistan                         Child Survival and Health
2  Afghanistan         Department of Defense Security Assistance
3  Afghanistan                            Development Assistance
4  Afghanistan Economic Support Fund/Security Support Assistance
5  Afghanistan                                Food For Education
6  Afghanistan                  Global Health and Child Survival
  FY1990 FY1991 FY1992   FY1993  FY1994 FY1995 FY1996 FY1997 FY1998
1     NA     NA     NA       NA      NA     NA     NA     NA     NA
2     NA     NA     NA       NA      NA     NA     NA     NA     NA
3     NA     NA     NA       NA      NA     NA     NA     NA     NA
4     NA     NA     NA 14178135 2769948     NA     NA     NA     NA
5     NA     NA     NA       NA      NA     NA     NA     NA     NA
6     NA     NA     NA       NA      NA     NA     NA     NA     NA
  FY1999 FY2000  FY2001   FY2002    FY2003     FY2004     FY2005
1     NA     NA      NA  2586555  56501189   40215304   39817970
2     NA     NA      NA  2964313        NA   45635526  151334908
3     NA     NA 4110478  8762080  54538965  180539337  193598227
4     NA     NA   61144 31827014 341306822 1025522037 1157530168
5     NA     NA      NA       NA   3957312    2610006    3254408
6     NA     NA      NA       NA        NA         NA         NA
      FY2006     FY2007     FY2008     FY2009
1   40856382   72527069   28397435         NA
2  230501318  214505892  495539084  552524990
3  212648440  173134034  150529862    3675202
4 1357750249 1266653993 1400237791 1418688520
5     386891         NA         NA         NA
6         NA         NA   63064912    1764252

The by.x specifies the key column(s) in the left data.frame and by.y does the same for the right data.frame. The ability to specify different column names for each data.frame is the most useful feature of merge. The biggest drawback, however, is that merge can be much slower than the alternatives.

plyr join

Returning to Hadley Wickham’s plyr package, it includes a join function, which works similarly to merge but is much faster. The biggest drawback, though, is that the key column(s) in each table must have the same name. I use the same data used previously to illustrate.

> library(plyr)
> Aid90s00sJoin <- join(x=Aid_90s, y=Aid_00s,
+                       by=c("Country.Name", "Program.Name"))
> head(Aid90s00sJoin)

  Country.Name                                      Program.Name
1  Afghanistan                         Child Survival and Health
2  Afghanistan         Department of Defense Security Assistance
3  Afghanistan                            Development Assistance
4  Afghanistan Economic Support Fund/Security Support Assistance
5  Afghanistan                                Food For Education
6  Afghanistan                  Global Health and Child Survival
  FY1990 FY1991 FY1992   FY1993  FY1994 FY1995 FY1996 FY1997 FY1998
1     NA     NA     NA       NA      NA     NA     NA     NA     NA
2     NA     NA     NA       NA      NA     NA     NA     NA     NA
3     NA     NA     NA       NA      NA     NA     NA     NA     NA
4     NA     NA     NA 14178135 2769948     NA     NA     NA     NA
5     NA     NA     NA       NA      NA     NA     NA     NA     NA
6     NA     NA     NA       NA      NA     NA     NA     NA     NA
  FY1999 FY2000  FY2001   FY2002    FY2003     FY2004     FY2005
1     NA     NA      NA  2586555  56501189   40215304   39817970
2     NA     NA      NA  2964313        NA   45635526  151334908
3     NA     NA 4110478  8762080  54538965  180539337  193598227
4     NA     NA   61144 31827014 341306822 1025522037 1157530168
5     NA     NA      NA       NA   3957312    2610006    3254408
6     NA     NA      NA       NA        NA         NA         NA
      FY2006     FY2007     FY2008     FY2009
1   40856382   72527069   28397435         NA
2  230501318  214505892  495539084  552524990
3  212648440  173134034  150529862    3675202
4 1357750249 1266653993 1400237791 1418688520
5     386891         NA         NA         NA
6         NA         NA   63064912    1764252

join has an argument for specifying a left, right, inner, or full (outer) join.

If you have eight data.frames containing foreign assistance data that you would like to combine into one data.frame without hand coding each join, the best way to do this is put all the data.frames into a list, and then successively join them together using Reduce.

> # first figure out the names of the data.frames
> frameNames <- str_sub(string=theFiles, start=12, end=18)
> # build an empty list
> frameList <- vector("list", length(frameNames))
> names(frameList) <- frameNames
> # add each data.frame into the list
> for(a in frameNames)
+ {
+     frameList[[a]] <- eval(parse(text=a))
+ }

A lot happened in that section of code, so let’s go over it carefully. First, I reconstructed the names of the data.frames using str_sub from Hadley Wickham’s stringr package. Then I built an empty list with as many elements as there are data.frames—in this case eight—using vector and assigning its mode to “list.” I then set appropriate names to the list.

Once the list was built and named, I looped through it, assigning to each element the appropriate data.frame. The problem is that I have the names of the data.frames as characters but the <- operator requires a variable, not a character. So I parsed and evaluated the character, which realizes the actual variable. Upon inspection, you can see that the list does indeed contain the appropriate data.frames.