changing data into long format using two variable

Question

I was hoping to get some help on a matter i am stuck in!

I have a sample data frame as follows (dput structure created):

df <- structure(list(id = structure(1:3, .Label = c("1", "2", "3"), class = "factor"), 
result.1 = structure(1:3, .Label = c("10", "34", "60"), class = "factor"), 
date.1 = structure(1:3, .Label = c("10.6.13", "21.6.13", 
"30.6.13"), class = "factor"), result.2 = structure(c(1L, 
2L, NA), .Label = c("23", "43"), class = "factor"), date.2 = structure(c(1L, 
2L, NA), .Label = c("11.6.13", "14.12.14"), class = "factor"), 
result.3 = structure(c(NA, 1L, NA), .Label = "34", class = "factor"), 
date.3 = structure(c(NA, 1L, NA), .Label = "1.1.15", class = "factor")), .Names = c("id", "result.1", "date.1", "result.2", "date.2", "result.3", "date.3"), row.names = c(NA, -3L), class = "data.frame")
df

each row represents a patient who can have multiple tests at different dates

I need the data to appear in long format as shown below (dput output given) using the results and dates

structure(list(id.2 = structure(c(1L, 1L, 2L, 2L, 2L, 3L), .Label = c("1", 
"2", "3"), class = "factor"), test.result = structure(c(1L, 2L, 
1L, 2L, 3L, 1L), .Label = c("result.1", "result.2", "result.3"
), class = "factor"), test.value = structure(c(1L, 2L, 3L, 4L, 
3L, 5L), .Label = c("10", "23", "34", "43", "56"), class = "factor"), 
test.date = structure(c(1L, 2L, 1L, 2L, 3L, 1L), .Label = c("date.1", 
"date.2", "date.3"), class = "factor"), date = structure(c(2L, 
3L, 5L, 4L, 1L, 6L), .Label = c("1.1.15", "10.6.13", "11.6.13", 
"14.12.14", "21.6.13", "30.6.13"), class = "factor")), .Names = c("id.2", 
"test.result", "test.value", "test.date", "date"), row.names = c(NA, 
-6L), class = "data.frame")

Any help greatly appreciated!!

Anoop

Answer 1

Base R's reshape() does the heavy lifting here. It's a terrifically useful function, but it is, notoriously, every bit as difficult to grock as it is powerful!

x <- reshape(df, idvar=1, varying=2:7, direction="long", timevar="n")
x <- x[complete.cases(x),]  ## to get rid of rows with NA's in them
#     id n result     date
# 1.1  1 1     10  10.6.13
# 2.1  2 1     34  21.6.13
# 3.1  3 1     60  30.6.13
# 1.2  1 2     23  11.6.13
# 2.2  2 2     43 14.12.14
# 2.3  2 3     34   1.1.15

## And, to complete the job _exactly_ as you described it
with(x, data.frame(id.2=id, 
                   test.result=paste0("result.",n), test.value=result,
                   test.date=paste0("date.",n), date=date))
#   id.2 test.result test.value test.date     date
# 1    1    result.1         10    date.1  10.6.13
# 2    2    result.1         34    date.1  21.6.13
# 3    3    result.1         60    date.1  30.6.13
# 4    1    result.2         23    date.2  11.6.13
# 5    2    result.2         43    date.2 14.12.14
# 6    2    result.3         34    date.3   1.1.15