data.table melt 3 columns into one

Question

After merging several data.tables, I get one that looks somewhat like this:

dt<- data.table(age = rep(c("5-16", "17-39"), each = 3), dur = rep(c("short","medium","long"), 2), short.count = rep(c(23,45),each = 3), medium.count = rep(c(12,34), each = 3), long.count = rep(c(3,5), each = 3))

> dt

     age    dur short.count medium.count long.count
1:  5-16  short          23           12          3
2:  5-16 medium          23           12          3
3:  5-16   long          23           12          3
4: 17-39  short          45           34          5
5: 17-39 medium          45           34          5
6: 17-39   long          45           34          5

I want to melt this so that I end up with one (appropriate) count value per row. e.g.


     age    dur       count 
1:  5-16  short          23           
2:  5-16 medium          12   
3:  5-16   long          3    
4: 17-39  short          45          
5: 17-39 medium          34           
6: 17-39   long          5

Is there an elegant way to do this?

score 1 · Accepted Answer · answered Jun 17 '22 at 01:35

Your data actually don't require melt, because you already have one row per age and duration. You can use fcase to create the new count column:

dt[, count:=fcase(
  dur=="short", short.count,
  dur=="medium", medium.count,
  dur=="long", long.count)][, c(1,2,6)]

Output:

     age    dur count
1:  5-16  short    23
2:  5-16 medium    12
3:  5-16   long     3
4: 17-39  short    45
5: 17-39 medium    34
6: 17-39   long     5

data.table melt 3 columns into one

1 Answers1