How do I stack only some columns in a data frame?

Question

I have some data in a data frame in the following form:

A  B  C  V1  V2  V3
1  1  1  x   y   z
1  1  2  a   b   c
...

Where A,B,C are factors, and the combination A,B,C is unique for each row.

I need to convert some of the columns into factors, to achieve a form like:

A  B  C  V  val
1  1  1  V1 x
1  1  1  V2 y
1  1  1  V3 z
1  1  2  V1 a
1  1  2  V2 b
1  1  2  V2 c
...

This seems to relate to both stack and the inverse of xtabs, but I don't see how to specify that only certain columns should be "stacked".

Answer 1

And before @AnandaMahto gets here and offers his base reshape solution, here's my attempt:

dat <- read.table(text = 'A  B  C  V1  V2  V3
1  1  1  x   y   z
1  1  2  a   b   c',header= T)

expandvars <- c("V1","V2","V3")

datreshape <- reshape(dat,
                   idvar=c("A","B","C"),
                   varying=list(expandvars),
                   v.names=c("val"),
                   times=expandvars,
                   direction="long")

> datreshape
         A B C time val
1.1.1.V1 1 1 1   V1   x
1.1.2.V1 1 1 2   V1   a
1.1.1.V2 1 1 1   V2   y
1.1.2.V2 1 1 2   V2   b
1.1.1.V3 1 1 1   V3   z
1.1.2.V3 1 1 2   V3   c

Answer 2

Using reshape2 package

dat <- read.table(text = 'A  B  C  V1  V2  V3
1  1  1  x   y   z
1  1  2  a   b   c',header= T)
library(reshape2)
melt(dat,id.vars = c('A','B','C'))
 A B C variable value
1 1 1 1       V1     x
2 1 1 2       V1     a
3 1 1 1       V2     y
4 1 1 2       V2     b
5 1 1 1       V3     z
6 1 1 2       V3     c

Answer 3

stack

You are right that stack is a possibility, but you perhaps missed a key line in the documentation for stack:

Note that stack applies to vectors (as determined by is.vector): non-vector columns (e.g., factors) will be ignored (with a warning as from R 2.15.0).

So, how do we proceed?

Here's your data:

dat <- read.table(text = 'A  B  C  V1  V2  V3
    1  1  1  x   y   z
    1  1  2  a   b   c',header= T)

Here, we convert the factors to as.character:

dat[sapply(dat, is.factor)] = lapply(dat[sapply(dat, is.factor)], as.character)

Here's how we specify which columns to stack:

stack(dat[4:6])
#   values ind
# 1      x  V1
# 2      a  V1
# 3      y  V2
# 4      b  V2
# 5      z  V3
# 6      c  V3

But, we still need to "expand" your rows for columns 1-3. See here for how to do that.

With this information, we can use cbind to get the desired result.

cbind(dat[rep(row.names(dat), 3), 1:3], stack(dat[4:6]))
#     A B C values ind
# 1   1 1 1      x  V1
# 2   1 1 2      a  V1
# 1.1 1 1 1      y  V2
# 2.1 1 1 2      b  V2
# 1.2 1 1 1      z  V3
# 2.2 1 1 2      c  V3

xtabs

You are also right that xtabs seems like it could be a likely possibility, but xtabs actually expects the opposite of what you've provided. That is to say, when you specify a formula, it expects the items on the left hand side to be numbers, and the items on the right hand side to be factors. Thus, is your data were swapped, you could certainly use xtabs.

Here's a demonstration (which only works because you are using a simple example where we can easily match "letters" to "numbers").

dat2 <- dat # Make a copy of "dat"
# Swap out dat 4-6 with numbers
dat2[4:6] <- lapply(dat2[4:6], function(x) match(x, letters))
# Swap out dat 1-3 with letters
dat2[1:3] <- lapply(dat2[1:3], function(x) letters[x])
# Our new "dat"
dat2
#   A B C V1 V2 V3
# 1 a a a 24 25 26
# 2 a a b  1  2  3
data.frame(xtabs(cbind(V1, V2, V3) ~ A + B + C, dat2))
#   A B C Var4 Freq
# 1 a a a   V1   24
# 2 a a b   V1    1
# 3 a a a   V2   25
# 4 a a b   V2    2
# 5 a a a   V3   26
# 6 a a b   V3    3

---

In other words, your choice of tools could potentially be right, but your data needs to also be in the form that the tools expect.

But, I'm not sure why you'd want to do all the work I've shown when better solutions exist with reshape and friends ;)

---

Very late update...

You can also look at merged.stack from my "splitstackshape" package:

library(splitstackshape)
merged.stack(dat, var.stubs = "V", sep = "NoSep")
#    A B C .time_1 V
# 1: 1 1 1      V1 x
# 2: 1 1 1      V2 y
# 3: 1 1 1      V3 z
# 4: 1 1 2      V1 a
# 5: 1 1 2      V2 b
# 6: 1 1 2      V3 c

Or gather from "tidyr":

library(dplyr)
library(tidyr)
# gather(dat, var, val, V1:V3)
dat %>% gather(var, val, V1:V3) 
#   A B C var val
# 1 1 1 1  V1   x
# 2 1 1 2  V1   a
# 3 1 1 1  V2   y
# 4 1 1 2  V2   b
# 5 1 1 1  V3   z
# 6 1 1 2  V3   c