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?
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