Q:

Moving data.table columns to rows based on conditions

I have a large data.table that is set up like this:
ID Line.Rec Line.D.Amount Line.Desc Line1.Record Line1.D.Amount Line1.C.Amount Line2.Rec
1  1        100           test      2            500            200            3
2  1        200           testb     2            800            100            3
3  1        600           testc     2            900            500            NA

Each event/row contains an ID and other static columns such as Eventdate. But, there is a varying amounts of lines (potentially anywhere from 1 to 99). Lines contains varying amounts of columns as well. The lines are not fixed, and some files will have different lines than this one. Therefore, I have to use column names rather than position. 
I would like the data.table to look like this:
ID Record D.Amount C.Amount Description
1  1      100      0        test
1  2      500      200
1  3      0        0        
2  1      200               testb
2  2      800      100    
2  3      0        0  
3  1      600      0        testc
3  2      900      500      

The solution needs to be ensure that any column that matches first part of the name (line., line1., line2.,...line99.) are included in the correct row. The ID line (and EventDate) needs to be included as illustrated to make sure that I can trace which lines belong together.
Any ideas?

A:

Not really a data.table question. You might want to consider changing the tag. Here is something that should get you started:
library(data.table)
dt <- fread("ID Line.Rec Line.D.Amount Line.Desc Line1.Record Line1.D.Amount Line1.C.Amount Line2.Rec
1  1        100           test      2            500            200            3
2  1        200           testb     2            800            100            3
3  1        600           testc     2            900            500            NA")
#ensure that relevant columns share the same names
setnames(dt, gsub("Rec$", "Record", names(dt)))

#identify which columns forms a sub dataset
otherCols <- setdiff(names(dt), "ID")
groupCols <- split(otherCols, sapply(strsplit(otherCols, "\\."), `[`, 1))
newCols <- sapply(names(groupCols), 
    function(x) gsub(paste0(x, "."), "", groupCols[[x]]))

#take sub columns of original dataset by group    
subDataLs <- lapply(names(groupCols),
    function(x) setnames(dt[, c("ID", groupCols[[x]]), with=FALSE], 
        c("ID", newCols[[x]]))
)

#rbind sub datasets
output <- rbindlist(subDataLs, use.names=TRUE, fill=TRUE)

#format to desired output
cols <- names(output)[sapply(output, is.numeric)]
output[, (cols) := replace(.SD, is.na(.SD), 0), .SDcols=cols]
cols <- names(output)[sapply(output, is.character)]
output[, (cols) := replace(.SD, is.na(.SD), ""), .SDcols=cols]

output:
   ID Record D.Amount  Desc C.Amount
1:  1      1      100  test        0
2:  2      1      200 testb        0
3:  3      1      600 testc        0
4:  1      2      500            200
5:  2      2      800            100
6:  3      2      900            500
7:  1      3        0              0
8:  2      3        0              0
9:  3      0        0              0

