Error while using reshape function in R

Question

I have a dataframe as follows,

  id  sex age trt.1 response.1 trt.2 response.2
1  1 <NA>  NA     A          1     B          1
2  2 <NA>  NA     A          1     B          1
3  3 <NA>  NA     A          1     B          1
4  4    M  28     A          1     B          1
5  5    F  39     A          1     B          1
6  6    M  47     A          1     B          1

I want to change it to

  id  sex age times response
1  1 <NA>  NA  A      1
2  1 <NA>  NA  B      1
3  2 <NA>  NA  A      1
4  2 <NA>  NA  B      1
.
.
.
.

I tried the following,

reshape(merged, idvar = "id", varying = list(4:7), v.names="response", times=c("A","B"), direction="long")

But I am getting this error,

Error in reshapeLong(data, idvar = idvar, timevar = timevar, varying = varying,  : 
  'times' is wrong length

I tried times = C("A","B","A","B") that time I get the output, but the original data frame is 70 rows and the output should be 140 rows, but when I use this I am get 280 rows which is wrong. Can anybody help me where I am doing mistake?

rawr · Accepted Answer · 2015-12-09T04:58:58.793

Is this what you're expecting? You just need to change varying = list(4:7) to varying = list(c(4,6), c(5,7)) since you are actually melting two pieces simultaneously.

dd <- read.table(stringsAsFactors = FALSE, header = TRUE, text = "id  sex age trt.1 response.1 trt.2 response.2
1  1 <NA>  NA     A          1     B          1
2  2 <NA>  NA     A          1     B          1
3  3 <NA>  NA     A          1     B          1
4  4    M  28     A          1     B          1
5  5    F  39     A          1     B          1
6  6    M  47     A          1     B          1")


reshape(dd, idvar = "id", varying = list(c(4,6), c(5,7)), direction="long",
        v.names = c('trt','response'))

#     id  sex age time trt response
# 1.1  1 <NA>  NA    1   A        1
# 2.1  2 <NA>  NA    1   A        1
# 3.1  3 <NA>  NA    1   A        1
# 4.1  4    M  28    1   A        1
# 5.1  5    F  39    1   A        1
# 6.1  6    M  47    1   A        1
# 1.2  1 <NA>  NA    2   B        1
# 2.2  2 <NA>  NA    2   B        1
# 3.2  3 <NA>  NA    2   B        1
# 4.2  4    M  28    2   B        1
# 5.2  5    F  39    2   B        1
# 6.2  6    M  47    2   B        1

Your output doesn't give the exact output which I want. Though it gives me an idea of how to do it. Can you help me in correctly it? response should be 1s no As and Bs — haimen, Dec 09 '15 at 04:54
@haimen that's what your v.names and times arguments did, see edits — rawr, Dec 09 '15 at 04:58

score 0 · Answer 2 · answered Dec 09 '15 at 04:51

Reshape is awesome but I really don't see why you need to use it in this case.

text = "id  sex age trt.1 response.1 trt.2 response.2
1  1 <NA>  NA     A          1     B          1
2  2 <NA>  NA     A          1     B          1
3  3 <NA>  NA     A          1     B          1
4  4    M  28     A          1     B          1
5  5    F  39     A          1     B          1
6  6    M  47     A          1     B          1"

data <- read.table(text = text)

result1 <- data[, c("id", "sex", "age", "trt.1", "response.1")]
result2 <- data[, c("id", "sex", "age", "trt.2", "response.2")]

names(result1) <- c("id", "sex", "age", "trt", "response")
names(result2) <- c("id", "sex", "age", "trt", "response")

result <- rbind(result1, result2)

Here is the output:

id  sex age trt response
1   1 <NA>  NA   A        1
2   2 <NA>  NA   A        1
3   3 <NA>  NA   A        1
4   4    M  28   A        1
5   5    F  39   A        1
6   6    M  47   A        1
11  1 <NA>  NA   B        1
...

score 0 · Answer 3 · answered Dec 09 '15 at 05:13

0

Here is the tidyr way to do it:

library(dplyr)
library(tidyr)

data %>%
  gather(variable, value,
         trt.1:response.2) %>%
  separate(variable, c("variable", "number")) %>%
  spread(variable, value)

answered Dec 09 '15 at 05:13

bramtayl

3,764
2
9
17

score 0 · Answer 4 · answered Dec 09 '15 at 05:53

We can use melt from library(data.table) which can take multiple measure columns with the pattern argument

library(data.table)#v1.9.6+
melt(setDT(df2), measure=patterns('^trt', 'response'), value.name=c('times', 'response'))
#    id  sex age variable times response
# 1:  1 <NA>  NA        1     A        1
# 2:  2 <NA>  NA        1     A        1
# 3:  3 <NA>  NA        1     A        1
# 4:  4    M  28        1     A        1
# 5:  5    F  39        1     A        1
# 6:  6    M  47        1     A        1
# 7:  1 <NA>  NA        2     B        1
# 8:  2 <NA>  NA        2     B        1
# 9:  3 <NA>  NA        2     B        1
#10:  4    M  28        2     B        1
#11:  5    F  39        2     B        1
#12:  6    M  47        2     B        1

Error while using reshape function in R

4 Answers4