使用R从大型数据集中提取满足每个vendor_id多个条件的行

2024-05-20 • 问答

使用R编程。我有Vendor_id，Bank_account_no和Date超过300万的数据集。我想获取每个Bank_account_no更改的vendor_id的行，例如，在三个月内，从X到X到X（至少三倍，可能超过三个）到Y（仅一次）到X。数据集的更改都是随机的，因此窗口的固定值与每个vendor_id的行数无关。我使用 rle 函数来获取不同Bank_account_no的长度。考虑到要为每个vendor_id运行此逻辑，因此不确定如何在R中为这么多行创建逻辑。可能是data.table可以帮助您。输入如下：

Vendor_ID   Bank_account_no   Date    

<!-- -->
dddd       X              24-12-2018
dddd       X              24-12-2018
dddd       X              26-12-2018
dddd       Y              27-12-2018
dddd       X              28-12-2018
dddd       X              29-12-2018
dddd       X              29-12-2018
dddd       X              31-12-2018
dddd       X              24-01-2019
dddd       Z              25-01-2019
dddd       X              28-01-2019
dddd       G              28-01-2019
dddd       G              28-01-2019
eeee       A              30-01-2019
eeee       A              31-01-2019
eeee       A              31-01-2019    
eeee       B              31-01-2019
eeee       A              31-01-2019

输出应为：

Vendor_ID   Bank_account_no   Date    Case

<!-- -->
dddd       X              24-12-2018  Case1
dddd       X              24-12-2018  Case1
dddd       X              26-12-2018  Case1
dddd       Y              27-12-2018  Case1
dddd       X              28-12-2018  Case1
dddd       X              29-12-2018  Case2
dddd       X              29-12-2018  Case2
dddd       X              31-12-2018  Case2
dddd       X              24-01-2019  Case2
dddd       Z              25-01-2019  Case2
dddd       X              28-01-2019  Case2
eeee       A              30-01-2019  Case3
eeee       A              31-01-2019  Case3
eeee       A              31-01-2019  Case3    
eeee       B              31-01-2019  Case3
eeee       A              31-01-2019  Case3

可能没有涵盖一些极端情况，但这是开始您的选择：

#get run length encoding for ID
DT[,r := rleid(ID)]

#identify locations of desired changes in ID
DT[,w := ID!=shift(ID,-1L) & ID==shift(ID,-2L)]

#set to TRUE at the start of a sequence
DT[,sw := replace(shift(w,3L,fill=FALSE),1L,TRUE),Vendor_ID]

#filter out those not part of any sequence
ans <- DT[-DT[,{
        ix <- which(w)
        if (length(ix) > 0L) .I[!r %in% r[sapply(ix,`+`,0L:2L)]]
    },Vendor_ID]$V1]

#create the desired Case column
ans[,Case := paste0("Case",cumsum(sw))]

输出：

    Vendor_ID ID       Date r     w    sw  Case
 1:      dddd  X 24-12-2018 1 FALSE  TRUE Case1
 2:      dddd  X 24-12-2018 1 FALSE FALSE Case1
 3:      dddd  X 26-12-2018 1  TRUE FALSE Case1
 4:      dddd  Y 27-12-2018 2 FALSE FALSE Case1
 5:      dddd  X 28-12-2018 3 FALSE FALSE Case1
 6:      dddd  X 29-12-2018 3 FALSE  TRUE Case2
 7:      dddd  X 29-12-2018 3 FALSE FALSE Case2
 8:      dddd  X 31-12-2018 3 FALSE FALSE Case2
 9:      dddd  X 24-01-2019 3  TRUE FALSE Case2
10:      dddd  Z 25-01-2019 4 FALSE FALSE Case2
11:      dddd  X 28-01-2019 5 FALSE FALSE Case2
12:      eeee  A 30-01-2019 7 FALSE  TRUE Case3
13:      eeee  A 31-01-2019 7 FALSE FALSE Case3
14:      eeee  A 31-01-2019 7  TRUE FALSE Case3
15:      eeee  B 31-01-2019 8    NA FALSE Case3
16:      eeee  A 31-01-2019 9    NA FALSE Case3

数据：

library(data.table)
DT <- fread("Vendor_ID   ID   Date    
dddd       X              24-12-2018
dddd       X              24-12-2018
dddd       X              26-12-2018
dddd       Y              27-12-2018
dddd       X              28-12-2018
dddd       X              29-12-2018
dddd       X              29-12-2018
dddd       X              31-12-2018
dddd       X              24-01-2019
dddd       Z              25-01-2019
dddd       X              28-01-2019
dddd       G              28-01-2019
dddd       G              28-01-2019
eeee       A              30-01-2019
eeee       A              31-01-2019
eeee       A              31-01-2019    
eeee       B              31-01-2019
eeee       A              31-01-2019")

使用R从大型数据集中提取满足每个vendor_id多个条件的行

qwertyu1212121 回答：使用R从大型数据集中提取满足每个vendor_id多个条件的行

大家都在问