作者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