Data Reshaping in R

This chapter is from the book

R for Everyone: Advanced Analytics and Graphics

Learn More Buy

This chapter is from the book

This chapter is from the book 

R for Everyone: Advanced Analytics and Graphics

Learn More Buy

12.2. Joins

Data do not always come so nicely aligned for combining using cbind, so they 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 programmatically, we use 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.

> require(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)
+ }

12.2.1. 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.

12.2.2. plyr join

Returning to Hadley Wickham’s plyr package, we see 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. We use the same data used previously to illustrate.

> require(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.

We have eight data.frames containing foreign assistance data that we would like to combine into one data.frame without hand coding each join. The best way to do this is to 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 we reconstructed the names of the data.frames using str_sub from Hadley Wickham’s stringr package, which is shown in more detail in Chapter 13. Then we 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.” We then set appropriate names to the list.

Now that the list is built and named, we loop through it, assigning to each element the appropriate data.frame. The problem is that we have the names of the data.frames as characters but the <- operator requires a variable, not a character. So we parse and evaluate the character, which realizes the actual variable. Inspecting, we see that the list does indeed contain the appropriate data.frames.

> head(frameList[[1]])

  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
  FY2000  FY2001   FY2002    FY2003     FY2004     FY2005     FY2006
1     NA      NA  2586555  56501189   40215304   39817970    40856382
2     NA      NA  2964313        NA   45635526   45635526   230501318
3     NA 4110478  8762080  54538965  180539337  193598227   212648440
4     NA   61144 31827014 341306822 1025522037 1157530168  1357750249
5     NA      NA       NA   3957312    2610006    3254408      386891
6     NA      NA       NA        NA         NA         NA          NA
      FY2007     FY2008     FY2009
1   72527069   28397435         NA
2  214505892  495539084  552524990
3  173134034  150529862    3675202
4 1266653993 1400237791 1418688520
5         NA         NA         NA
6         NA   63064912    1764252

> head(frameList[["Aid_00s"]])

  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
  FY2000  FY2001   FY2002    FY2003     FY2004     FY2005     FY2006
1     NA      NA  2586555  56501189   40215304   39817970   40856382
2     NA      NA  2964313        NA   45635526  151334908  230501318
3     NA 4110478  8762080  54538965  180539337  193598227  212648440
4     NA   61144 31827014 341306822 1025522037 1157530168 1357750249
5     NA      NA       NA   3957312    2610006    3254408     386891
6     NA      NA       NA        NA         NA         NA         NA
      FY2007     FY2008     FY2009
1   72527069   28397435         NA
2  214505892  495539084  552524990
3  173134034  150529862    3675202
4 1266653993 1400237791 1418688520
5         NA         NA         NA
6         NA   63064912    1764252

> head(frameList[[5]])

  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
  FY1960 FY1961    FY1962 FY1963 FY1964 FY1965 FY1966 FY1967 FY1968
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 181177853     NA     NA     NA     NA     NA     NA
5     NA     NA        NA     NA     NA     NA     NA     NA     NA
6     NA     NA        NA     NA     NA     NA     NA     NA     NA
  FY1969
1     NA
2     NA
3     NA
4     NA
5     NA
6     NA

> head(frameList[["Aid_60s"]])

  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
  FY1960 FY1961    FY1962 FY1963 FY1964 FY1965 FY1966 FY1967 FY1968
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 181177853     NA     NA     NA     NA     NA     NA
5     NA     NA        NA     NA     NA     NA     NA     NA     NA
6     NA     NA        NA     NA     NA     NA     NA     NA     NA
  FY1969
1     NA
2     NA
3     NA
4     NA
5     NA
6     NA

Having all the data.frames in a list allows us to iterate through the list, joining all the elements together (or applying any function to the elements iteratively). Rather than using a loop, we use the Reduce function to speed up the operation.

> allAid <- Reduce(function(...)
+ {
+     join(..., by = c("Country.Name", "Program.Name"))
+ }, frameList)
> dim(allAid)

[1] 2453  67

> require(useful)
> corner(allAid, c = 15)

  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
  FY2000  FY2001   FY2002    FY2003     FY2004     FY2005     FY2006
1     NA      NA  2586555  56501189   40215304   39817970   40856382
2     NA      NA  2964313        NA   45635526  151334908  230501318
3     NA 4110478  8762080  54538965  180539337  193598227  212648440
4     NA   61144 31827014 341306822 1025522037 1157530168 1357750249
5     NA      NA       NA   3957312    2610006    3254408     386891
      FY2007     FY2008     FY2009     FY2010  FY1946 FY1947
1   72527069   28397435         NA         NA      NA     NA
2  214505892  495539084  552524990  316514796      NA     NA
3  173134034  150529862    3675202         NA      NA     NA
4 1266653993 1400237791 1418688520 2797488331      NA     NA
5         NA         NA         NA         NA      NA     NA

> bottomleft(allAid, c = 15)

     Country.Name           Program.Name   FY2000  FY2001   FY2002
2449     Zimbabwe Other State Assistance  1341952  322842       NA
2450     Zimbabwe Other USAID Assistance  3033599 8464897  6624408
2451     Zimbabwe            Peace  Corps 2140530 1150732   407834
2452     Zimbabwe                Title I       NA      NA       NA
2453     Zimbabwe               Title II       NA      NA 31019776
       FY2003   FY2004   FY2005  FY2006     FY2007    FY2008    FY2009
2449       NA   318655    44553  883546    1164632   2455592   2193057
2450 11580999 12805688 10091759 4567577   10627613  11466426  41940500
2451       NA       NA       NA      NA         NA        NA        NA
2452       NA       NA       NA      NA         NA        NA        NA
2453       NA       NA       NA  277468  100053600 180000717 174572685
       FY2010 FY1946 FY1947
2449  1605765     NA     NA
2450 30011970     NA     NA
2451       NA     NA     NA
2452       NA     NA     NA
2453 79545100     NA     NA

Reduce can be a difficult function to grasp, so we illustrate it with a simple example. Let’s say we have a vector of the first ten integers, 1:10, and want to sum them (forget for a moment that sum(1:10) will work perfectly). We can call Reduce(sum, 1:10), which will first add 1 and 2. It will then add 3 to that result, then 4 to that result, and so on, resulting in 55.

Likewise, we passed a list to a function that joins its inputs, which in this case was simply . . . , meaning that anything could be passed. Using . . . is an advanced trick of R programming that can be difficult to get right. Reduce passed the first two data.frames in the list, which were then joined. That result was then joined to the next data.frame and so on until they were all joined together.

12.2.3. data.table merge

Like many other operations in data.table, joining data requires a different syntax, and possibly a different way of thinking. To start, we convert two of our foreign aid datasets’ data.frames into data.tables.

> require(data.table)
> dt90 <- data.table(Aid_90s, key = c("Country.Name", "Program.Name"))
> dt00 <- data.table(Aid_00s, key = c("Country.Name", "Program.Name"))

Then, doing the join is a simple operation. Note that the join requires specifying the keys for the data.tables, which we did during their creation.

> dt0090 <- dt90[dt00]

In this case dt90 is the left side, dt00 is the right side and a left join was performed.