代码之家 › 专栏 › 技术社区 › hannes101

从层次关系链中提取信息

data.table dplyr r

0

hannes101 · 技术社区 · 7 年前

最初,数据表示为具有父子关系的不同id,每行表示不同的交易及其id。我需要分析的原始数据集如下所示。

dt.original.data <- structure(list(msg_seq_nb = c("0005747", "0005747", "0005765", "0005765", "0005783", "0005783", "0008333", "0008333", "0008494", 
                                  "0008494", "0008556", "0008556", "0008560", "0008560", "0013622", "0013622", "0013797", "0013797", "0024541", "0024541", "0024569"
    ), orig_msg_seq_nb = c(NA, NA, "0005747", "0005747", "0005765", "0005765", "0005783", "0005783", "0008333", "0008333", "0008494", 
                           "0008494", "0008556", "0008556", "0008560", "0008560", "0013622", "0013622", "0013797", "0013797", "0024541")
    , trc_st = c("T","C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R")
    , trd_rpt_dt = structure(c(15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987), class = "Date")
    , trd_rpt_tm = c(34838, 34853, 34853, 34863, 34863, 36231, 36231, 36305, 36305, 36328, 36328, 36330, 36330, 38831, 38831, 38925, 38925, 42984, 42984, 43002, 43002))
    , row.names = c(NA, -21L), class = c("data.table", "data.frame"))

   >  dt.original.data
    msg_seq_nb orig_msg_seq_nb trc_st trd_rpt_dt trd_rpt_tm
 1:    0005747            <NA>      T 2013-10-09      34838
 2:    0005747            <NA>      C 2013-10-09      34853
 3:    0005765         0005747      R 2013-10-09      34853
 4:    0005765         0005747      C 2013-10-09      34863
 5:    0005783         0005765      R 2013-10-09      34863
 6:    0005783         0005765      C 2013-10-09      36231
 7:    0008333         0005783      R 2013-10-09      36231
 8:    0008333         0005783      C 2013-10-09      36305

正如您所看到的,在通过 orig_msg_seq_nb msg_seq_nb . 因此,我设法使用递归联接将匹配的组合添加到一行中。 https://stackoverflow.com/a/53395260/5795592 这个结果的摘录如下:

>      msg_seq_nb Initial.Trade.Status Initial.Trd.Rpt.Dt Initial.Trd.Rpt.Tm J2.Msg.Nb J2.Trade.Status J2.Trd.Rpt.Tm
   1:    0005747                    T         2013-10-09              34838   0005765               R         34853
   2:    0005747                    T         2013-10-09              34838   0005765               C         34863

我现在要分析每个关系链,并提取每个关系链末尾的状态。所以,对于上面的示例数据,自从上次在trc\U st中输入贸易状态以来 0008333 是 C

根据此关系链的最终状态(最初是不同交易的序列),确定ID交易是否保留在原始数据集中,或者初始状态是否必须用最终状态更新,即最后一个递归步骤后的状态。

它在某种程度上与这个SQL问题有关 https://dba.stackexchange.com/questions/96098/finding-the-end-of-a-relationship-chain-optimally

1 回复 | 直到 7 年前

1

0

ismirsehregal 7 年前

library(data.table)

dt.original.data <- structure(
  list(
    msg_seq_nb = c("0005747", "0005747", "0005765", "0005765", "0005783", "0005783", "0008333", "0008333", "0008494",
                   "0008494", "0008556", "0008556", "0008560", "0008560", "0013622", "0013622", "0013797", "0013797", "0024541", "0024541", "0024569"
), orig_msg_seq_nb = c(NA, NA, "0005747", "0005747", "0005765", "0005765", "0005783", "0005783", "0008333", "0008333", "0008494", 
                       "0008494", "0008556", "0008556", "0008560", "0008560", "0013622", "0013622", "0013797", "0013797", "0024541")
, trc_st = c("T","C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R", "C", "R")
, trd_rpt_dt = structure(c(15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987, 15987), class = "Date")
, trd_rpt_tm = c(34838, 34853, 34853, 34863, 34863, 36231, 36231, 36305, 36305, 36328, 36328, 36330, 36330, 38831, 38831, 38925, 38925, 42984, 42984, 43002, 43002))
, row.names = c(NA, -21L), class = c("data.table", "data.frame"))

final_transition <- dt.original.data[trc_st != "T" & orig_msg_seq_nb %in% dt.original.data[trc_st == "T"]$msg_seq_nb]
final_transition <- merge(dt.original.data[trc_st == "T", c("trc_st", "trd_rpt_tm", "msg_seq_nb")], final_transition, by.x = "msg_seq_nb", by.y = "orig_msg_seq_nb")

col_names <- c("msg_seq_nb", "Initial.Trade.Status", "Initial.Trd.Rpt.Dt", "Initial.Trd.Rpt.Tm", "J2.Msg.Nb", "J2.Trade.Status", "J2.Trd.Rpt.Tm")

setnames(final_transition, 
         c("msg_seq_nb", "trc_st.x", "trd_rpt_dt", "trd_rpt_tm.x", "msg_seq_nb.y", "trc_st.y", "trd_rpt_tm.y"),
         col_names)
setcolorder(final_transition, col_names)

final_transition