Reshaping Data in R - Hadley Wickham

1 Proceedings of the 4th International Workshopon Directions in Statistical Computing (DSC2005) Reshaping data in RHadley Wickham . data is a common task in practical data analysis, and itis usually unintuitive and tedious. data often has multiple levels of group-ing (nested treatments, split plot designs, or repeated measurements) andtypically requires investigation at multiple levels. For example, from a longterm clinical study we may be interested investigating relationships over time,or between times or patients or treatments. Performing these investigationsfluently requires the data to be reshaped in different R supplies a reshape function that can perform some of thesetasks, but confounds multiple steps in the process and is hard to use. Wepropose a new conceptual framework for Reshaping operations and an R pack-age to deshape data frames and then flexibly reshape them to meet yourneeds.

2 This framework also produces contingency tables, cross-tabulations,and summary IntroductionThis paper discusses a conceptual framework for data Reshaping , and describes animplementation of these principles in a R package, Reshaping is easiest to define with respect to aggregation. Aggregation is acommon and familiar task where data is reduced and rearranged into a smaller, moreconvenient form, with a concomitant reduction in the amount of information. Onecommonly used aggregation procedure are Excel s Pivot tables. Reshaping involvesa similar rearrangement, but preserves all original information. Where aggregationreduces many cells in the original data set to one cell in the new dataset, reshapingpreserves a one-to-one are a number of general R functions that can aggregate data , for exampletapply,byandaggregate, and a function specifically for Reshaping data , of DSC 20052 Each of these functions tends to deal well with one or two specific scenarios, andeach requires slightly different input arguments.

3 In practice, careful thought isusually required to piece together the correct sequence of operations to arrange thedata how you want. Thereshapepackage overcomes these problems by using theconceptual framework defined below to solve a general set of problems using justtwo functions, Conceptual frameworkTo help us think about all the ways we might rearrange a data set it is useful to thinkabout data in a somewhat unusual fashion. Usually, we think about data in terms ofa matrix or data frame, where we have observations in the rows and variables in thecolumns. In this form it is difficult to investigate relationships between other facetsof the data : between subjects, or treatments, or replicates. Reshaping the dataallows to explore these other relationships while still being able to use the familiartools that operate on columns.

4 Reshaping is an important (but often unrecognised)part of practical data analysis and is often necessary when exploring, displayingand analysing the purposes of Reshaping , we can divide the variables into two groups:identifier and measured , or id, variables identify the unit that measurements take place variables are usually discrete, and are typically fixed by design. In ANOVA notation (Yijk), id variables are the indices on the variables (i, j, k). variables represent what is measured on that unit (Y).It is possible to take this abstraction a step further and say there are onlyid variables and a value, where the id variables now also identify what measuredvariable the value represents. For example, we could represent this table:SubjectTimeAgeWeightHeightJohn :SubjectTimeVariableValueJohn Smith1 Age50 John Smith1 Height90 John each row represents one observation of one variable.

5 This is what I will referto as deshaped data . Compared to the original data set, it has a new id variableProceedings of DSC 20053 variable , and a new column value , which represents the value of that observa-tion. We now have the data in a form in which there is no distinction between ouroriginal observed variables and other id form, in itself, is not terribly useful, but it is easy to manipulate. An-other interesting feature of this form is that we do not need to store missing valuesexplicitly, but instead are they are reconstructed as necessary when the data ImplementationWith this conceptual framework established, I will discuss particular details of theimplementation in R. Ideally, we want easy to use tools to restructure data framesthat use the insights from the ideas above. I will discuss why we need a new packageto reshape data , and how we can specify how the form of the reshaped first step is to deshape the data .

6 This is essentially a trivial operation,and very similar to the existing R functionstack. The next challenge is to specifyhow we want the data to look with thereshapefunction. A natural way to dothis is to specify which variables should form the columns and which should formthe rows. In the usual data frame, the variable id variable forms the columns,while all other id variables form the rows. Aggregation occurs when the variablesdo not uniquely identify one row, and in this case we need an aggregation functionto reduce the data . Examples later in the chapter will make this order the row and column variables are specified in is very with a contingency table there are many possible ways of displaying the samevariables, and the way they are organised reveals different patterns in the specified first vary slowest, and those specified last vary fastest.

7 Becausecomparisons are made most easily between adjacent cells, the variable you are mostinterested in making comparisons between should be specified last, and the earlyvariables should be thought of as conditioning variables. An additional constraintis that displays have limited width but essentially infinite length, so variables withmany levels must be specified as row variables. It is also desirable to adhere tocommon conventions, so where possible, variable should appear in the DeshapingThe R command to deshape a data set isdeshape. If you don t specify eithermeasured or id variables, the function will try to guess which are id variables: anyfactors, integers or columns with 5 or less different values. If you specify only themeasured variables, it assumes the remainder are identifier variables, and vice complication of this design is that all values must be of the same type.

8 Thisis not usually a big problem because most of the time you are dealing with numericdata. I have been experimenting with storing this data in a list for maximumflexibility - this however makes later code more complicated as we can no longerrely on straightforward of DSC Functions that return multiple valuesOccasionally it is useful to aggregate with a function that returns multiple values, range, summary etc. This can be thought of as combining multiple reshapeseach with an aggregation function that returns one variable. We do this with anadditional variable,resultvariablethat differentiates the multiple return names if available, otherwise will create names of theform X1, X2,.. By default, this new id variable will be shown as last columnvariable, but you can specify the position manually by includingresultvariablein the list of row and column Row and column namesThere are two ways to think about the results from an aggregation command,as either a matrix of numbers with some attributes that describe the row andcolumn names, or as a data frame with the row names as columns.

9 Most currentR aggregation functions return the first, implicit, form, whereas reshape returnsthe explicit data frame form. Why the difference? The implicit form is ofteninconvenient to deal with rownames are data ExampleThe reshape package is available on CRAN and can be installed using the R (" reshape "). This section will work through some tech-niques using the reshape package with an example data set (frenchfries). Thedata is from a sensory experiment investigating the effect of different frying oilson the taste of french fries over time. There are three different types of frying oils(treatment), each in two different fryers (rep), tested by 12 people (subject) on 10different days (time). The sensory attributes recorded, in order of desirability, arepotato, buttery, grassy, rancid, painty flavours. The first few rows of the data looklike:time treatment subject rep potato buttery grassy rancid painty61 of the first things we might be interested in is how balanced this design is,and whether there are many different missing values.

10 We can investigate this usinglength as our aggregation function:ff_d <- deshape(french_fries, id=1:4) reshape (ff_d, subject ~ time, length)Proceedings of DSC 20055subject X1 X2 X3 X4 X5 X6 X7 X8 X9 X103 30 30 30 30 30 30 30 30 30 NA10 30 30 30 30 30 30 30 30 30 3015 30 30 30 30 25 30 30 30 30 3016 30 30 30 30 30 30 30 29 30 3019 30 30 30 30 30 30 30 30 30 3031 30 30 30 30 30 30 30 30 NA 3051 30 30 30 30 30 30 30 30 30 3052 30 30 30 30 30 30 30 30 30 3063 30 30 30 30 30 30 30 30 30 3078 30 30 30 30 30 30 30 30 30 3079 30 30 30 30 30 30 29 28 30 NA86 30 30 30 30 30 30 30 30 NA 30Of course we can also create our own aggregation function. Each subject shouldhave had 30 observations at each time, so by displaying the difference we can moreeasily see where the data is (ff_d, subject ~ time, function(x) 30 - length(x))subject X1 X2 X3 X4 X5 X6 X7 X8 X9 X103 0 0 0 0 0 0 0 0 0 NA10 0 0 0 0 0 0 0 0 0 015 0 0 0 0 5 0 0 0 0 016 0 0 0 0 0 0 0 1 0 019 0 0 0 0 0 0 0 0 0 031 0 0 0 0 0 0 0 0 NA 051 0 0 0 0 0 0 0 0 0 052 0 0 0 0 0 0 0 0 0 063 0 0 0 0 0 0 0 0 0 078 0 0 0 0 0 0 0 0 0 079 0 0 0 0 0 0 1 2 0 NA86 0 0 0 0 0 0 0 0 NA 0We can also easily see the range of values that each variable takes: reshape (ff_d, variable ~.)