Referring to data.table columns using dynamic text in a loop

Question

I am trying to bind a table onto itself several times each time selecting a different column that will be dependent on where we are in the loop but I can't get it to work.

#create a test data.table
dt<-data.table(start=c(1,1,2,2,3,3,4,4),
           start_2_end=0,
           id=rep(c("A","B")),
           ret1=1:8,
           ret2=9:16,
           ret3=17:24)


#Create table where start = end 
cretdt <- dt[,.(start,
                    end=start,
                    id,
                    start_2_end=0,
                    cret=1)]

#loop and bind onto itself and pull in the correct ret column
for (i in 1:2)
{
  cretdt <- rbind(cretdt,
                     dt[,.(start,
                          end=start+i,
                          id,
                          start_2_end=i,
                          cret=noquote(paste0("ret",(i+1)))
                          )]
                 )
 }

My problem lies in the 'cret=noquote(paste0("ret",(i+1)))' portion; it just pulls in the name "ret2" and "ret3" instead of the corresponding value in the ret2 or ret3 column.

I have tried passing cret into a variable and then using eval() but that does not seem to work.

Is this possible to accomplish? Please help. Let me know if you need more details.

Answer 1

You're really looking for a reshape operation here, but I'll show another way of getting around the loop:

s2e = 0:2
dt[,.(
  start = start,
  end   = start+rep(s2e,each=nrow(dt)),
  id    = id,
  s2e   = rep(s2e,each=nrow(dt)),
  cret  = unlist(mget(paste0('ret',s2e+1L)))
)]

which gives

    start end id s2e cret
 1:     1   1  A   0    1
 2:     1   1  B   0    2
 3:     2   2  A   0    3
 4:     2   2  B   0    4
 5:     3   3  A   0    5
 6:     3   3  B   0    6
 7:     4   4  A   0    7
 8:     4   4  B   0    8
 9:     1   2  A   1    9
10:     1   2  B   1   10
11:     2   3  A   1   11
12:     2   3  B   1   12
13:     3   4  A   1   13
14:     3   4  B   1   14
15:     4   5  A   1   15
16:     4   5  B   1   16
17:     1   3  A   2   17
18:     1   3  B   2   18
19:     2   4  A   2   19
20:     2   4  B   2   20
21:     3   5  A   2   21
22:     3   5  B   2   22
23:     4   6  A   2   23
24:     4   6  B   2   24
    start end id s2e cret

(Thanks @akrun for a simplification of the last term. I always forget about mget.)

Answer 2

You can obtain the column index for each iteration of the loop by using grep against colnames:

for (i in 1:2) {
    col.index <- grep(noquote(paste0("ret",(i+1))), colnames(dt))
    cretdt <- rbind(cretdt,
                 dt[,.(start,
                      end=start+i,
                      id,
                      start_2_end=i,
                      cret=col.index
                      )]
             )
}