[問題] data.table資料轉換

看板R_Language作者celestialgod (天)時間3年前 (2022/02/20 15:03)推噓0(0推 0噓 1→)

留言1則, 1人參與討論串1/1

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

→

Wush978

02/20 15:53, 3年前 , 1^F

02/20 15:53, 1^F

我今天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