根据带有数字的模式自动按特定顺序/setcolorder对宽data.table: dcast列进行排序

Question

我有这样一个DT：

id = c(rep(1,10), rep(2, 5), rep(3,12))
th = c(rep(c(0,1),5), c(0, 1, 0, 1, 0), rep(c(1,0,1),4 ))
drugs = c(rep(c("A","B","C","D","E"),2), c("A", "B", "B", "B", "A"), rep(c("C","D","c"),4 ))

DT = data.table(id, th, drugs)

DT
    id th drugs seq
 1:  1  0     A   1
 2:  1  1     B   2
 3:  1  0     C   3
 4:  1  1     D   4
 5:  1  0     E   5
 6:  1  1     A   6
 7:  1  0     B   7
 8:  1  1     C   8
 9:  1  0     D   9
10:  1  1     E  10
11:  2  0     A   1
12:  2  1     B   2
13:  2  0     B   3
14:  2  1     B   4
15:  2  0     A   5
16:  3  1     C   1
17:  3  0     D   2
18:  3  1     c   3
19:  3  1     C   4
20:  3  0     D   5
21:  3  1     c   6
22:  3  1     C   7
23:  3  0     D   8
24:  3  1     c   9
25:  3  1     C  10
26:  3  0     D  11
27:  3  1     c  12

我用id做了一个计数器("seq")，把所有的药物都放在一个观察/id中，使它变宽。

DT_wide =  DT[, seq := seq(.N), by = .(id)][, dcast.data.table(.SD, id ~ paste0("rx", seq), value.var = c("th", "drugs"))]

获得：

    DT_wide
   id th_rx1 th_rx10 th_rx11 th_rx12 th_rx2 th_rx3 th_rx4 th_rx5 th_rx6 th_rx7 th_rx8 th_rx9 drugs_rx1 drugs_rx10 drugs_rx11 drugs_rx12 drugs_rx2 drugs_rx3 drugs_rx4 drugs_rx5 drugs_rx6 drugs_rx7 drugs_rx8 drugs_rx9
1:  1      0       1      NA      NA      1      0      1      0      1      0      1      0         A          E       <NA>       <NA>         B         C         D         E         A         B         C         D
2:  2      0      NA      NA      NA      1      0      1      0     NA     NA     NA     NA         A       <NA>       <NA>       <NA>         B         B         B         A      <NA>      <NA>      <NA>      <NA>
3:  3      1       1       0       1      0      1      1      0      1      1      0      1         C          C          D          c         D         c         C         D         c         C         D         c

想要的输出是以这种方式排序DT_wide列：

"id“、"th_rx1”、"drugs_rx1“、"th_rx2”、"drugs_rx2“、.、"th_rx12”、"drugs_rx12“

是否有更好的方法来执行dcast，或者需要一个带有特定regex的后置dcast setcolorder()？

由于_rx1和_rx10问题，我尝试了一个没有结果的setcolorder：

setcolorder(DT_wide, c("id", grep("_rx1", colnames(DT_wide), value = TRUE)))

提前感谢您的帮助！

Answer 1

尝试以下解决方法

cols <- c("th", "drugs")
# a shorter way of achieving your dcast
#  numbering comes from rowid()
DT.wide <- dcast(DT, id ~ paste0("rx", rowid(id)), value.var = cols)
# new order of colnames
new_colorder <- CJ(unique(rowid(DT$id)), cols, sorted = FALSE)[, paste(cols, V1, sep = "_rx")]
# reorder the relevant columns
setcolorder(DT.wide, c(setdiff(names(DT.wide), new_colorder), new_colorder))


#    id th_rx1 drugs_rx1 th_rx2 drugs_rx2 th_rx3 drugs_rx3 th_rx4 drugs_rx4 th_rx5 drugs_rx5 th_rx6
# 1:  1      0         A      1         B      0         C      1         D      0         E      1
# 2:  2      0         A      1         B      0         B      1         B      0         A     NA
# 3:  3      1         C      0         D      1         c      1         C      0         D      1
#    drugs_rx6 th_rx7 drugs_rx7 th_rx8 drugs_rx8 th_rx9 drugs_rx9 th_rx10 drugs_rx10 th_rx11 drugs_rx11
# 1:         A      0         B      1         C      0         D       1          E      NA       <NA>
# 2:      <NA>     NA      <NA>     NA      <NA>     NA      <NA>      NA       <NA>      NA       <NA>
# 3:         c      1         C      0         D      1         c       1          C       0          D
#    th_rx12 drugs_rx12
# 1:      NA       <NA>
# 2:      NA       <NA>
# 3:       1          c

Answer 2

与温佩尔的回答几乎相同，但细节不同，例如sprintf()和rowid(id)在dcast()中的使用

library(data.table)
library(magrittr)
DTw <- dcast(DT, id ~ sprintf("rx%02i", rowid(id)), value.var = c("th", "drugs"))
newcols <- DT[, CJ(max(rowid(id)) %>% seq() %>% sprintf("rx%02i", .), 
                   setdiff(names(.SD), "id"))][
  , c("id",paste(V2, V1, sep = "_"))]
setcolorder(DTw, newcols)
DTw

id drugs\_rx01 th\_rx01 drugs\_rx02 th\_rx02 drugs\_rx03 th\_rx03 drugs\_rx04 th\_rx04 drugs\_rx05 th\_rx05 drugs\_rx06 th\_rx06 1: 1 A 0 B 1 C 0 D 1 E 0 A 1 2: 2 A 0 B 1 B 0 B 1 A 0 <NA> NA 3: 3 C 1 D 0 c 1 C 1 D 0 c 1 drugs\_rx07 th\_rx07 drugs\_rx08 th\_rx08 drugs\_rx09 th\_rx09 drugs\_rx10 th\_rx10 drugs\_rx11 th\_rx11 drugs\_rx12 th\_rx12 1: B 0 C 1 D 0 E 1 <NA> NA <NA> NA 2: <NA> NA <NA> NA <NA> NA <NA> NA <NA> NA <NA> NA 3: C 1 D 0 c 1 C 1 D 0 c 1

顺便说一句: github 用公式的RHS选择dcast()中多个value.var的顺序列上有一个特性请求

编辑:自动调整sprintf()

在评论中，OP认为，如果一个id有超过99行，则必须在代码中更改sprintf()中的格式。

如果事先不知道id中的最大行数，则可以编程地采用sprintf()：

# create another sample dataset
id <- c(rep(1,200), rep(2, 5), rep(3,12))
th <- c(rep(c(0,1),100), c(0, 1, 0, 1, 0), rep(c(1,0,1),4 )) 
drugs <- c(rep(c("A","B","C","D","E"), 40), c("A", "B", "B", "B", "A"), rep(c("C","D","c"),4 )) 
DT2 <- data.table(id, th, drugs)

# compute fmt programmatically
max_id_count <- DT2[, max(rowid(id))]
fmt <- max_id_count %>% log10() %>% ceiling() %>% paste0("rx%0", ., "i")
DTw <- dcast(DT2, id ~ sprintf(fmt, rowid(id)), value.var = c("th", "drugs"))
newcols <- DT2[, CJ(max_id_count %>% seq() %>% sprintf(fmt, .), 
                    setdiff(names(.SD), "id"))][
                      , c("id",paste(V2, V1, sep = "_"))]
setcolorder(DTw, newcols)
DTw

id drugs\_rx001 th\_rx001 drugs\_rx002 th\_rx002 drugs\_rx003 th\_rx003 drugs\_rx004 th\_rx004 drugs\_rx005 th\_rx005 1: 1 A 0 B 1 C 0 D 1 E 0 2: 2 A 0 B 1 B 0 B 1 A 0 drugs\_rx006 th\_rx006 drugs\_rx007 th\_rx007 drugs\_rx008 th\_rx008 drugs\_rx009 th\_rx009 drugs\_rx010 th\_rx010 drugs\_rx011 1: A 1 B 0 C 1 D 0 E 1 A 2: <NA> NA <NA> NA <NA> NA <NA> NA <NA> NA <NA> ...

在此示例数据集中，max_id_count为200。通过将日志取为10基并向上四舍五入，我们可以编程创建一个拟合的fmt参数"rx%03i"。