gpt4 book ai didi

r - data.table:如何根据包含列名的分组唯一行值更改列值

转载 作者:行者123 更新时间:2023-12-01 09:19:46 24 4
gpt4 key购买 nike

我有一个包含 ~18^6 行的 data.table,我需要通过 ID 获取 CLASS 的唯一值,并将它们各自的列设置为 1,如下面的示例所示

DT <- data.table::data.table(ID=c("1","1","1","2","2"),
CLASS=c("a","a","b","c","b"),
a=c(0,0,0,0,0),
b=c(0,0,0,0,0),
c=c(0,0,0,0,0))

### Start with this
ID CLASS a b c
1 a 0 0 0
1 a 0 0 0
1 b 0 0 0
2 c 0 0 0
2 b 0 0 0

### Want this
ID CLASS a b c
1 a 1 1 0
1 a 1 1 0
1 b 1 1 0
2 c 0 1 1
2 b 0 1 1

我的第一直觉是尝试下面的代码,但发现它会将所有列设置为 1,因为 unique(DT$CLASS) 固有地包含所有 ID 的所有唯一值,并且不会通过“分组”参数传递说。

### Tried this
DT[,unique(DT$CLASS):=1,by=ID]

### Got this
ID CLASS a b c
1 a 1 1 1
1 a 1 1 1
1 b 1 1 1
2 c 1 1 1
2 b 1 1 1

我一直在努力充分利用 data.table 的潜力和速度,并希望仅使用 data.table 参数中的命令来创建所需的输出。

谁能帮我编写正确的代码,仅使用 data.table 命令/参数,以便我的第 j 个索引仅包含唯一值,按 ID,并将适当的列设置为 1?

Follow-up Question:

假设每一行也有一个关联的日期 RXDATE,我想创建所有类值的相应列名,这些值按 ID 按 CLASS 保存最小 RXDATE。我也可以为此求助于 dcast 吗?

### Start with this
ID CLASS a b c RXDATE
1 a 1 1 0 1-1-99
1 a 1 1 0 1-2-99
1 b 1 1 0 1-3-99
2 c 0 1 1 5-4-00
2 b 0 1 1 6-5-01

### Want this
ID CLASS a b c RXDATE a_DT b_DT c_DT
1 a 1 1 0 1-1-99 1-1-99 1-3-99 NA
1 a 1 1 0 1-2-99 1-1-99 1-3-99 NA
1 b 1 1 0 1-3-99 1-1-99 1-3-99 NA
2 c 0 1 1 5-4-00 NA 6-5-01 5-4-00
2 b 0 1 1 6-5-01 NA 6-5-01 5-4-00

最佳答案

使用 dcastmerge 你还可以:

DT <- data.table::data.table(ID=c("1","1","1","2","2"),
CLASS=c("a","a","b","c","b"),
a=c(0,0,0,0,0),
b=c(0,0,0,0,0),
c=c(0,0,0,0,0))

# dcast to convert to wide
DT_dcast <- dcast(DT[, .(ID, CLASS)], ID ~ CLASS, fun.aggregate = function(x) length(unique(x)), value.var = "CLASS")
DT_dcast
ID a b c
1: 1 1 1 0
2: 2 0 1 1

# Then merge with the original data.table
DT_m <- merge(DT[, .(ID, CLASS)], DT_dcast, by = "ID")
DT_m
ID CLASS a b c
1: 1 a 1 1 0
2: 1 a 1 1 0
3: 1 b 1 1 0
4: 2 c 0 1 1
5: 2 b 0 1 1

编辑您仍然可以对 dcastmerge 使用相同的方法。

我从您的“从这个开始”数据中注意到第 2 行有不同的 RX 日期,并且从“想要这个”数据中您只为此保留了“1-1-99”。

DT2 <- data.table::data.table(ID=c("1","1","1","2","2"),
CLASS=c("a","a","b","c","b"),
a=c(0,0,0,0,0),
b=c(0,0,0,0,0),
c=c(0,0,0,0,0),
RXDate = c("1-1-99", "1-2-99", "1-3-99", "5-4-00", "6-5-01"))

# 2nd row from the data provided has different RXDate under same ID and Class.
# Use x[1] to pick first
DT_dcast <- dcast(DT2[, .(ID, CLASS, RXDate)], ID ~ CLASS,
fun.aggregate = function(x) x[1],
value.var = c("CLASS", "RXDate"))
DT_dcast
ID CLASS.1_a CLASS.1_b CLASS.1_c RXDate_a RXDate_b RXDate_c
1: 1 a b <NA> 1-1-99 1-3-99 <NA>
2: 2 <NA> b c <NA> 6-5-01 5-4-00

# Convert 1 or 0 under CLASS
class_cols <- names(DT_dcast)[grepl("CLASS", names(DT_dcast))]
for (col in class_cols) set(DT_dcast, j = col, value = ifelse(is.na(DT_dcast[[col]]), 0, 1))

DT_dcast
ID CLASS.1_a CLASS.1_b CLASS.1_c RXDate_a RXDate_b RXDate_c
1: 1 1 1 0 1-1-99 1-3-99 <NA>
2: 2 0 1 1 <NA> 6-5-01 5-4-00

# Then merge with the original data.table
DT_m <- merge(DT2[, .(ID, CLASS, RXDate)], DT_dcast, by = "ID")
DT_m

ID CLASS RXDate CLASS.1_a CLASS.1_b CLASS.1_c RXDate_a RXDate_b RXDate_c
1: 1 a 1-1-99 1 1 0 1-1-99 1-3-99 <NA>
2: 1 a 1-2-99 1 1 0 1-1-99 1-3-99 <NA>
3: 1 b 1-3-99 1 1 0 1-1-99 1-3-99 <NA>
4: 2 c 5-4-00 0 1 1 <NA> 6-5-01 5-4-00
5: 2 b 6-5-01 0 1 1 <NA> 6-5-01 5-4-00

如果你想重命名列,那么你可以使用 setnames

关于r - data.table:如何根据包含列名的分组唯一行值更改列值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57699570/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com