# Transposing data frames in R via unstack [closed]

I want to transpose a data frame in R with unstack. Consider the two data frames, a and b:

> a
count state
1 199665  RSTO
2   4147  RSTR
3  31274    S1
4      1    S2
5   2522    S3
6 118009    SF
> b
count state
1  31956  RSTO
2  11689  RSTR
3   6702    S1
4   2838    S2
5   6268    S3
6 672561    SF


My problem is that unstacking a single one does not work:

> formula(a)
count ~ state
> unstack(a)
res
RSTO 199665
RSTR   4147
S1    31274
S2        1
S3     2522
SF   118009


However, if I concatenate a and b, unstack works as expected.

> unstack(rbind(a,b))
RSTO  RSTR    S1   S2   S3     SF
1 199665  4147 31274    1 2522 118009
2  31956 11689  6702 2838 6268 672561


Why is this happening? Does the groups (i.e., the RHS of the formula) need to repeat for unstack to work properly? How can I make unstack work with a single data frame?

Note that rbind(a, b) creates a single data frame, so that's not it. The unexpected behavior of unstack(a) results from the fact that you only have one observation (count) per factor level (state). To see what's going on, you have to look at the unstack() function.

# list source code for unstack()'s method for a data frame
> getS3method("unstack", "data.frame")
function (x, form, ...)
{
form <- if (missing(form))
stats::formula(x)
else stats::as.formula(form)
if (length(form) < 3)
stop("'form' must be a two-sided formula")
res <- c(tapply(eval(form[[2L]], x), eval(form[[3L]], x),
as.vector))
if (length(res) < 2L || any(diff(unlist(lapply(res, length))) !=
0L))
return(res)
data.frame(res)
}
<environment: namespace:utils>


The relevant bits are c(tapply(eval(form[[2L]], x), eval(form[[3L]], x), as.vector)) and data.frame(res). The first expression here is equivalent to

> c(tapply(a$count, a$state, as.vector))
RSTO   RSTR     S1     S2     S3     SF
199665   4147  31274      1   2522 118009


The output of tapply() is a 1-dimensional array if the provided function only returns one value when applied to the data in each level of the factor. c() strips the class, thus returning a numeric vector. data.frame(<vector>) creates a data frame with one variable equal to that vector. Compare this to what happens with two observations per condition:

> ab <- rbind(a, b)
> c(tapply(ab$count, ab$state, as.vector))
$RSTO [1] 199665 31956$RSTR
[1]  4147 11689

$S1 [1] 31274 6702$S2
[1]    1 2838

$S3 [1] 2522 6268$SF
[1] 118009 672561


Here, the function provided for tapply() returns a vector with two elements for each level of the factor. The result then is a list with as many components as you have factor levels, each component containing the result of the supplied function for that level. All components have equal length, hence data.frame(<list>) returns a data frame where each component becomes a variable (data frames are special lists with components of equal length).

Now, another question would be if the behavior of unstack() is sensible in a case like yours. If this is of practical importance, you can add an id-variable and use reshape() instead.

> a$id <- 1 > reshape(a, direction="wide", v.names="count", timevar="state", idvar="id") id count.RSTO count.RSTR count.S1 count.S2 count.S3 count.SF 1 1 199665 4147 31274 1 2522 118009 > ab$id <- rep(1:2, each=nrow(a))
> reshape(ab, direction="wide", v.names="count", timevar="state", idvar="id")
id count.RSTO count.RSTR count.S1 count.S2 count.S3 count.SF
1   1     199665       4147    31274        1     2522   118009
11  2      31956      11689     6702     2838     6268   672561

• Thanks for the illumination, that's quite a detailed answer! – mavam Mar 24 '11 at 6:20

To get unstacking of a similar to rbind(a,b) you can simply transpose the result:

> t(unstack(a))
RSTO RSTR    S1 S2   S3     SF
res 199665 4147 31274  1 2522 118009


You will get a matrix instead of data.frame though.

It is also possible to use the cast function from package reshape:

> cast(~state,data=a,value="count")
value   RSTO RSTR    S1 S2   S3     SF
1 (all) 199665 4147 31274  1 2522 118009


For the rbind(a,b) with added id column the cast will give you the following:

> ab <- rbind(a,b)
> ab\$id=rep(1:2,each=nrow(a))
> cast(id~state,data=ab,value="count")
id   RSTO  RSTR    S1   S2   S3     SF
1  1 199665  4147 31274    1 2522 118009
2  2  31956 11689  6702 2838 6268 672561

• The cast functions is really neat. – mavam Mar 25 '11 at 21:34
• @Matthias, yes it is. I say a prayer to Hadley Wickham everytime I use it :) – mpiktas Mar 25 '11 at 21:47

Caracal has answered the question. Here are some additional pieces of information.

Try this:

require(stats)
formula(PlantGrowth)
x = PlantGrowth[c(1,11,21),]
y = PlantGrowth[c(1,2,11,12,21,22),]
x
unstack(x)
y
unstack(y)


The output:

>     x
weight group
1    4.17  ctrl
11   4.81  trt1
21   6.31  trt2
>     unstack(x)
res
ctrl 4.17
trt1 4.81
trt2 6.31
>     y
weight group
1    4.17  ctrl
2    5.58  ctrl
11   4.81  trt1
12   4.17  trt1
21   6.31  trt2
22   5.12  trt2
>     unstack(y)
ctrl trt1 trt2
1 4.17 4.81 6.31
2 5.58 4.17 5.12


We just have to define a new unstack function:

NewUnstack <- function(x)
{
df = unstack(x)
if(dim(df)[2]==1)
{
df = t(df)
}
return(df)
}


Here we go:

> NewUnstack(x)
ctrl trt1 trt2
res 4.17 4.81 6.31
> NewUnstack(y)
ctrl trt1 trt2
1 4.17 4.81 6.31
2 5.58 4.17 5.12