[問題類型]:
經驗諮詢(我想用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
[關鍵字]: