[问题类型]:
经验咨询(我想用R 连接某些数据库，请问大家的经验)
[软件熟悉度]:
开发者
[问题叙述]:
原资料
NodeID InProductionTime Quantity Censor FailureTime
Node1 2021/1/1 2 1 N/A
Node1 2021/1/1 1 0 2021/4/1
Node1 2021/1/1 1 0 2021/6/1
Node1 2021/4/1 1 0 2021/7/1
Node2 2021/4/1 2 1 N/A
Node2 2021/4/1 1 0 2021/7/1
Node3 2021/5/1 4 1 N/A
Node3 2021/5/1 1 0 2021/7/1
Node3 2021/7/1 1 0 2021/9/1
补充说明 censor=1的 都是安装纪录 censor=0的是失败纪录
但是我要转换的目标是存活纪录跟失败纪录
如果当初安装的装置都失败了 就不该有当初的安装纪录 或是数量要减少
预期结果
NodeID InProductionTime Quantity Censor FailureTime
Node1 2021/6/1 1 1 N/A
Node1 2021/7/1 1 1 N/A
Node1 2021/1/1 1 0 2021/4/1
Node1 2021/1/1 1 0 2021/6/1
Node1 2021/4/1 1 0 2021/7/1
Node2 2021/4/1 1 1 N/A
Node2 2021/7/1 1 1 N/A
Node2 2021/4/1 1 0 2021/7/1
Node3 2021/5/1 3 1 N/A
Node3 2021/9/1 1 1 N/A
Node3 2021/5/1 1 0 2021/7/1
想问问看有没有做过这个资料转换的经验
我自己写了一版 但是我不是很满意现在的写法
想说问问看有没有其他人有其他想法
PS: 原资料的censor=0的数量很大 也不太可能先展开censor=0然后做计算
PS2: censor=1的时候 quantity有可能>1 但目前程式没办法考虑到这种情况
# EX:
# Node4 2021/6/1 2 1 N/A
# Node4 2021/6/1 2 0 2021/8/1
# Node4 2021/8/1 1 0 2021/9/1
# 预期输出
# Node4 2021/8/1 1 1 N/A
# Node4 2021/9/1 1 1 N/A
# Node4 2021/6/1 2 0 2021/8/1
# Node4 2021/8/1 1 0 2021/9/1
[程式范例]:
library(data.table)
library(lubridate)
library(magrittr)
DT <- data.table(
nodeId = c("Node1", "Node1", "Node1", "Node1", "Node2", "Node2", "Node3",
"Node3", "Node3"),
inProductionTime = as_date(c("2020-01-01", "2020-01-01", "2020-01-01",
"2020-04-01", "2020-04-01", "2020-04-01", "2020-05-01", "2020-05-01",
"2020-07-01")),
quantity = c(2L, 1L, 1L, 1L, 2L, 1L, 4L, 1L, 1L),
censor = c(1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 0L),
failureTime = as_date(c(NA, "2020-04-01", "2020-06-01", "2020-07-01", NA,
"2020-07-01", NA, "2020-07-01", "2020-09-01"))
)
DT[ , `:=`(
UID = paste(nodeId, format(inProductionTime, "%Y%m%d"), sep = "-"),
ReplacedUID = ifelse(
is.na(failureTime),
NA,
paste(nodeId, format(failureTime, "%Y%m%d"), sep = "-")
)
)] %>% `[`(, index := 1:.N, by = .(nodeId))
censoredDT <- DT[ , .(
index, quantity, UID, censor,
newUIDs = list(na.omit(ReplacedUID[!ReplacedUID %in% UID]))
), by = .(nodeId)] %>%
`[`(censor == 1) %>%
`[`(, quantity := quantity - sapply(newUIDs, length)) %>%
{
rbind(
.[quantity > 0, .(nodeId, index, quantity, censor, UID)],
.[ , .(quantity=1, UID = unlist(newUIDs)), by = .(nodeId, index,
censor)]
)
} %>%
`[`(, inProductionTime2 := tstrsplit(UID, "-", fixed=TRUE, keep=2L)) %>%
`[`(, `:=`(inProductionTime = as_date(inProductionTime2), failureTime =
as_date(NA)))
resultDT <- rbind(
censoredDT[ , .(nodeId, inProductionTime, quantity, censor, failureTime)],
DT[censor == 0, .(nodeId, inProductionTime, quantity, censor, failureTime)]
) %>%
`[`(order(nodeId, -censor, inProductionTime))
不知道有没有人有处理过类似的资料 有更好的写法可以提供给我参考
[环境叙述]:
R-4.0.3 on Windows
[关键字]: