Duplicate a data table while changing one column's value for each unique value in list-CodePudding

I have a data.table in R that I need to duplicate x amount of times with x being number of values in a list. The values in the list represent a column value I need to change/update for each unique duplication of the data.table.

data_table <- 
     structure(data.table(`Item ID` = c("Item 1", "Item 1", "Item 1", 
     "Item 2", "Item 2", "Item 2"), Percentile = c(0, 1, 2, 0, 1, 
     2), Value = c(4.161290323, 3.649700599, 3.5, 4.58965, 6, 2),
     `ListCode` = NA))

l <- list(2, 3, 4)

What I have:

    Item ID    Percentile  Value         ListCode
1:   Item 1        0         4.161290       NA
2:   Item 1        1         3.649701       NA
3:   Item 1        2         3.500000       NA
4:   Item 2        0         4.589650       NA
5:   Item 2        1         6.000000       NA
6:   Item 2        2         2.000000       NA

What I want:

    Item ID    Percentile  Value         ListCode
1:   Item 1        0         4.161290       2
2:   Item 1        1         3.649701       2
3:   Item 1        2         3.500000       2
4:   Item 2        0         4.589650       2
5:   Item 2        1         6.000000       2
6:   Item 2        2         2.000000       2
7:   Item 1        0         4.161290       3
8:   Item 1        1         3.649701       3
9:   Item 1        2         3.500000       3
10:  Item 2        0         4.589650       3
11:  Item 2        1         6.000000       3
12:  Item 2        2         2.000000       3 
13:  Item 1        0         4.161290       4
14:  Item 1        1         3.649701       4
15:  Item 1        2         3.500000       4
16:  Item 2        0         4.589650       4
17:  Item 2        1         6.000000       4
18:  Item 2        2         2.000000       4

I found out how to easily duplicate the table using the following code:

data_table <- as.data.table(uncount(data_table, weights = length(l)))

I can't figure out how to append the list value to the ListCode column. Any help is appreciated!

CodePudding user response：

The following should work ...

cbind(
  data_table[, -"ListCode"][rep(1:nrow(data_table), length(l)), ], 
  ListCode = rep(unlist(l), each = nrow(data_table))
)
#     Item ID Percentile    Value ListCode
#  1:  Item 1          0 4.161290        2
#  2:  Item 1          1 3.649701        2
#  3:  Item 1          2 3.500000        2
#  4:  Item 2          0 4.589650        2
#  5:  Item 2          1 6.000000        2
#  6:  Item 2          2 2.000000        2
#  7:  Item 1          0 4.161290        3
#  8:  Item 1          1 3.649701        3
#  9:  Item 1          2 3.500000        3
# 10:  Item 2          0 4.589650        3
# 11:  Item 2          1 6.000000        3
# 12:  Item 2          2 2.000000        3
# 13:  Item 1          0 4.161290        4
# 14:  Item 1          1 3.649701        4
# 15:  Item 1          2 3.500000        4
# 16:  Item 2          0 4.589650        4
# 17:  Item 2          1 6.000000        4
# 18:  Item 2          2 2.000000        4

CodePudding user response：

We can use

library(data.table)

ans <- do.call(rbind , lapply(1:length(l) , \(x) data_table))[ , ListCode := as.numeric(ListCode)][]

ans[ , ListCode := as.vector(sapply(l , \(x) rep(x , .N / length(l))))]

output

   Item ID Percentile    Value ListCode
 1:  Item 1          0 4.161290        2
 2:  Item 1          1 3.649701        2
 3:  Item 1          2 3.500000        2
 4:  Item 2          0 4.589650        2
 5:  Item 2          1 6.000000        2
 6:  Item 2          2 2.000000        2
 7:  Item 1          0 4.161290        3
 8:  Item 1          1 3.649701        3
 9:  Item 1          2 3.500000        3
10:  Item 2          0 4.589650        3
11:  Item 2          1 6.000000        3
12:  Item 2          2 2.000000        3
13:  Item 1          0 4.161290        4
14:  Item 1          1 3.649701        4
15:  Item 1          2 3.500000        4
16:  Item 2          0 4.589650        4
17:  Item 2          1 6.000000        4
18:  Item 2          2 2.000000        4