作者celestialgod (天)
看板R_Language
标题[问题] data.table资料转换
时间Sun Feb 20 15:03:44 2022
[问题类型]:
经验谘询(我想用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
[关键字]:
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 114.32.179.120 (台湾)
※ 编辑: celestialgod (114.32.179.120 台湾), 02/20/2022 15:45:55
1F：→ Wush978: 关於转换的逻辑，有没有简单一点的描述呢？ 02/20 15:53
我今天Node1 1/1 有两个装置 安装上去了
这是 第一笔资料
NodeID InProductionTime Quantity Censor FailureTime
Node1 2021/1/1 2 1 N/A
但是我有失败装置纪录
NodeID InProductionTime Quantity Censor FailureTime
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
所以我知道 1/1安装的两个装置 分别在4/1 跟 6/1 死掉 被换下来了
又还有一个资料告诉你说 4/1安装的装置 在 7/1死掉了
结果上面两个资料 我要整理成
1. 6/1安装的装置还活着
2. 7/1安装的装置还活着
3. 三笔失败的纪录
预期结果如下:
# 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
※ 编辑: celestialgod (114.32.179.120 台湾), 02/20/2022 16:03:30
※ 编辑: celestialgod (114.32.179.120 台湾), 02/20/2022 16:16:03
17:20更新, 我找到一个方法了
# raw data
DT <- data.table(
nodeId = c(
"Node1", "Node1", "Node1", "Node1", "Node2", "Node2",
"Node3", "Node3", "Node3", "Node4", "Node4", "Node4"
),
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",
"2020-06-01", "2020-06-01", "2020-08-01")),
quantity = c(2L, 1L, 1L, 1L, 2L, 1L, 4L, 1L, 1L, 2L, 2L, 1L),
censor = c(1L, 0L, 0L, 0L, 1L, 0L, 1L, 0L, 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",
NA, "2020-08-01", "2020-09-01"))
)
# expand censor == 0 & quantity > 1的node
expandedDT <- DT[(censor == 0L) & (quantity > 1L),
.(idx = unlist(1:quantity)),
by = .(nodeId, inProductionTime, quantity, censor, failureTime)] %>%
`[`( , `:=`(idx = NULL , quantity = 1))
DT[(censor == 0L) & (quantity > 1L), quantity := 0]
newDT <- rbind(DT[quantity > 0], expandedDT)
# 建立UIDs
installedUidDT <- newDT[
censor == 1,
.(UIDs = list(paste(nodeId, format(inProductionTime, "%Y%m%d"),
1:quantity, sep = "-"))),
by = .(nodeId)
]
# 建立failed的index (同个production time死掉的装置 要做编号)
newDT[censor == 0, failedIndex := 1:.N,
by = .(nodeId, inProductionTime, quantity)]
# 建出 censor = 1的完整列表
censoredDT <- newDT[
censor == 0,
.(
replacedUIDs = list(paste(nodeId, format(inProductionTime, "%Y%m%d"),
failedIndex, sep = "-")),
newUIDs = list(paste(nodeId, format(failureTime, "%Y%m%d"), failedIndex,
sep = "-"))
),
by = .(nodeId, quantity)
] %>%
merge(installedUidDT, by = c("nodeId")) %>%
`[`(,
.(currentUID = do.call(
c,
list(
setdiff(do.call(c, UIDs), do.call(c, replacedUIDs)),
setdiff(do.call(c, newUIDs), do.call(c, replacedUIDs))
)
)), by = .(nodeId)) %>%
`[`( , inProductionTimeStr := tstrsplit(currentUID, "-", keep = 2L)) %>%
`[`( , inProductionTime := as_date(inProductionTimeStr)) %>%
`[`( , .(quantity = .N), by = .(nodeId, inProductionTime)) %>%
`[`( , `:=`(censor = 1, failureTime = as_date(NA)))
# 最後结果
resultDT <- rbind(
censoredDT,
newDT[censor == 0,
.(nodeId, inProductionTime, quantity, censor, failureTime)]
) %>%
`[`(order(nodeId, -censor, inProductionTime))
※ 编辑: celestialgod (114.32.179.120 台湾), 02/20/2022 17:18:34