Chapter 12. Data Reshaping
As noted in Chapter 11, manipulating the data takes a great deal of effort before serious analysis can begin. In this chapter we will consider when the data needs 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 we will focus on those in plyr, reshape2 and data.table.
12.1. cbind and rbind
The simplest case is when we have 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, we create two simple data.frames by combining a few vectors with cbind, and then stack them using rbind.
> # make two 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"
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.1 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.
1. More information about the data is available at http://gbk.eads.usaidallnet.gov/.
> 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.
12.3. reshape2
The next most common munging need is either melting data (going from column orientation to row orientation) or casting data (going from row orientation to column orientation). As with most other procedures in R, there are multiple functions available to accomplish these tasks but we will focus on Hadley Wickham’s reshape2 package. (We talk about Wickham a lot because his products have become so fundamental to the R developer’s toolbox.)
12.3.1. melt
Looking at the Aid 00s data.frame, we see that each year is stored in its own column. That is, the dollar amount for a given country and program is found in a different column for each year. This is called a cross table, which, while nice for human consumption, is not ideal for graphing with ggplot2 or for some analysis algorithms.
> head(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
We want it set up so that each row represents a single country-program-year entry with the dollar amount stored in one column. To achieve this we melt the data using melt from reshape2.
> require(reshape2)
> melt00 <- melt(Aid_00s, id.vars=c("Country.Name", "Program.Name"),
+ variable.name="Year", value.name="Dollars")
> tail(melt00, 10)
Country.Name
24521 Zimbabwe
24522 Zimbabwe
24523 Zimbabwe
24524 Zimbabwe
24525 Zimbabwe
24526 Zimbabwe
24527 Zimbabwe
24528 Zimbabwe
24529 Zimbabwe
24530 Zimbabwe
Program.Name Year
24521 Migration and Refugee Assistance FY2009
24522 Narcotics Control FY2009
24523 Nonproliferation, Anti-Terrorism, Demining and Related FY2009
24524 Other Active Grant Programs FY2009
24525 Other Food Aid Programs FY2009
24526 Other State Assistance FY2009
24527 Other USAID Assistance FY2009
24528 Peace Corps FY2009
24529 Title I FY2009
24530 Title II FY2009
Dollars
24521 3627384
24522 NA
24523 NA
24524 7951032
24525 NA
24526 2193057
24527 41940500
24528 NA
24529 NA
24530 174572685
The id.vars argument specifies which columns uniquely identify a row. After some manipulation of the Year column and aggregating, this is now prime for plotting, as shown in Figure 12.1. The plot uses faceting allowing us to quickly see and understand the funding for each program over time.
> require(scales)
> # strip the "FY" out of the year column and convert it to numeric
> melt00$Year <- as.numeric(str_sub(melt00$Year, start=3, 6))
> # aggregate the data so we have yearly numbers by program
> meltAgg <- aggregate(Dollars ~ Program.Name + Year, data=melt00,
+ sum, na.rm=TRUE)
> # just keep the first 10 characters of program name
> # then it will fit in the plot
> meltAgg$Program.Name <- str_sub(meltAgg$Program.Name, start=1,
+ end=10)
>
> ggplot(meltAgg, aes(x=Year, y=Dollars)) +
+ geom_line(aes(group=Program.Name)) +
+ facet_wrap(~ Program.Name) +
+ scale_x_continuous(breaks=seq(from=2000, to=2009, by=2)) +
+ theme(axis.text.x=element_text(angle=90, vjust=1, hjust=0)) +
+ scale_y_continuous(labels=multiple_format(extra=dollar,
+ multiple="B"))
12.3.2. dcast
Now that we have the foreign aid data melted, we cast it back into the wide format for illustration purposes. The function for this is dcast, and it has trickier arguments than melt. The first is the data to be used, in our case melt00. The second argument is a formula where the left side specifies the columns that should remain columns and the right side specifies the columns that should become row names. The third argument is the column (as a character) that holds the values to be populated into the new columns representing the unique values of the right side of the formula argument.
> cast00 <- dcast(melt00, Country.Name + Program.Name ~ Year,
+ value.var = "Dollars")
> head(cast00)
Country.Name Program.Name 2000
1 Afghanistan Child Survival and Health NA
2 Afghanistan Department of Defense Security Assistance NA
3 Afghanistan Development Assistance NA
4 Afghanistan Economic Support Fund/Security Support Assistance NA
5 Afghanistan Food For Education NA
6 Afghanistan Global Health and Child Survival NA
2001 2002 2003 2004 2005 2006
1 NA 2586555 56501189 40215304 39817970 40856382
2 NA 2964313 NA 45635526 151334908 230501318
3 4110478 8762080 54538965 180539337 193598227 212648440
4 61144 31827014 341306822 1025522037 1157530168 1357750249
5 NA NA 3957312 2610006 3254408 386891
6 NA NA NA NA NA NA
2007 2008 2009
1 72527069 28397435 NA
2 214505892 495539084 552524990
3 173134034 150529862 3675202
4 1266653993 1400237791 1418688520
5 NA NA NA
6 NA 63064912 1764252
12.4. Conclusion
Getting the data just right to analyze can be a time-consuming part of our work flow, although it is often inescapable. In this chapter we examined combining multiple datasets into one and changing the orientation from column based (wide) to row based (long). We used plyr, reshape2 and data.table along with base functions to accomplish this. This chapter combined with Chapter 11 covers most of the basics of data munging with an eye to both convenience and speed.