使while循环更有效，以便在大型data.table上根据特定条件删除行

Question

我在数据表中有相当多的数据。如果单元格中有某个值，我想删除若干行。

下面是我数据表的摘录：

             V1              V2       V3       V4       V5       V6       V7       V8       V9      V10
 1:                     01.01.16 04.01.16 05.01.16 06.01.16 07.01.16 08.01.16 11.01.16 12.01.16 13.01.16
 2: AT0000A1EKT9          .Close      #NV                                                               
 3:                    Ask.Close      #NV                                                               
 4:                    BID.Close      #NV                                                               
 5:               Bid Ask Spread      #NV        0        0        0        0        0        0        0
 6:              TR.IssuerRating      ba1      ba1      ba1      ba1      ba1      ba1      ba1      ba1
 7: AT0000A17HT4          .Close    3.436    3.426    3.376    3.347    3.388    3.379    3.349    3.325
 8:                    Ask.Close   98.092   98.149    98.43   98.596   98.366   98.415   98.584   98.721
 9:                    BID.Close   97.537   97.594   97.874   98.039    97.81   97.859   98.027   98.164
10:               Bid Ask Spread    0.555    0.555    0.556    0.557    0.556    0.556    0.557    0.557
11:              TR.IssuerRating      P-2      P-2      P-2      P-2      P-2      P-2      P-2      P-2

使用dput(头(X))，这样就可以很容易地复制表。

setDT(structure(list(V1 = c("", "AT0000A1EKT9", "", "", "", "", "AT0000A17HT4", 
"", "", "", ""), V2 = c("01.01.16", ".Close", "Ask.Close", "BID.Close", 
"Bid Ask Spread", "TR.IssuerRating", ".Close", "Ask.Close", "BID.Close", 
"Bid Ask Spread", "TR.IssuerRating"), V3 = c("04.01.16", "#NV", 
"#NV", "#NV", "#NV", "ba1", "3.436", "98.092", "97.537", "0.555", 
"P-2"), V4 = c("05.01.16", "", "", "", "0", "ba1", "3.426", "98.149", 
"97.594", "0.555", "P-2"), V5 = c("06.01.16", "", "", "", "0", 
"ba1", "3.376", "98.43", "97.874", "0.556", "P-2"), V6 = c("07.01.16", 
"", "", "", "0", "ba1", "3.347", "98.596", "98.039", "0.557", 
"P-2"), V7 = c("08.01.16", "", "", "", "0", "ba1", "3.388", "98.366", 
"97.81", "0.556", "P-2"), V8 = c("11.01.16", "", "", "", "0", 
"ba1", "3.379", "98.415", "97.859", "0.556", "P-2"), V9 = c("12.01.16", 
"", "", "", "0", "ba1", "3.349", "98.584", "98.027", "0.557", 
"P-2"), V10 = c("13.01.16", "", "", "", "0", "ba1", "3.325", 
"98.721", "98.164", "0.557", "P-2"), V11 = c("14.01.16", "", 
"", "", "0", "ba1", "3.3", "98.863", "98.305", "0.558", "P-2"
), V12 = c("15.01.16", "", "", "", "0", "ba1", "3.26", "99.089", 
"98.53", "0.559", "P-2"), V13 = c("18.01.16", "", "", "", "0", 
"ba1", "3.271", "99.026", "98.468", "0.558", "P-2"), V14 = c("19.01.16", 
"", "", "", "0", "ba1", "3.244", "99.177", "98.618", "0.559", 
"P-2"), V15 = c("20.01.16", "", "", "", "0", "ba1", "3.238", 
"99.211", "98.652", "0.559", "P-2"), V16 = c("21.01.16", "", 
"", "", "0", "ba1", "3.268", "99.044", "98.487", "0.557", "P-2"
)), row.names = c(NA, -11L), class = c("data.table", "data.frame"
)))

我已将我的数据按ISIN编号分组在第1栏中。对于其中一些ISIN，我没有任何价格数据，可以由#NV在.Close旁边看到。

如果在#NV旁边有一个.Close，我想要做的是删除整个ISIN条目。

数据表在删除我想要的行后应该如下所示：

             V1              V2       V3       V4       V5       V6       V7       V8       V9      V10
 1:                     01.01.16 04.01.16 05.01.16 06.01.16 07.01.16 08.01.16 11.01.16 12.01.16 13.01.16
 2: AT0000A17HT4          .Close    3.436    3.426    3.376    3.347    3.388    3.379    3.349    3.325
 3:                    Ask.Close   98.092   98.149    98.43   98.596   98.366   98.415   98.584   98.721
 4:                    BID.Close   97.537   97.594   97.874   98.039    97.81   97.859   98.027   98.164
 5:               Bid Ask Spread    0.555    0.555    0.556    0.557    0.556    0.556    0.557    0.557
 6:              TR.IssuerRating      P-2      P-2      P-2      P-2      P-2      P-2      P-2      P-2

我编写了一个while循环，它可以很好地处理少量的测试数据。但是，当我将这个while循环应用到我的完整data.table时，循环效率很低，运行起来需要很长时间，这使得它无法使用，因为我有大约100万行。

while循环如下所示

i <- 1
while(i < dim(test1)[1]){
  if (test1$V2[i] == ".Close" & test1$V3[i] == "#NV"){
    a <- i + 4                    # creating upper range of rows to be deleted
    test1 <- test1[-c(i:a)]       #deleting rows and overwriting data.table
    i <- 1                        #starting loop from beginning again since data.table is smaller
  }
  else{
    i <- i+1
  }
}

有什么方法可以让这个循环更高效吗？

Answer 1

一个格式良好的Excel电子表格为数据分析提供了一个很好的例子。

如果我正确理解，OP希望删除一个节的所有行，如果列V3中的任何值等于#NV。新的部分以列V1中的非空项开始。

library(data.table)
setDT(test1)[, grp := cumsum(nzchar(V1))][
  , if (!any(V3 == "#NV")) .SD, by = grp][
    , grp := NULL][]

V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 <char> <char> <char> <char> <char> <char> <char> <char> <char> <char> 1: 01.01.16 04.01.16 05.01.16 06.01.16 07.01.16 08.01.16 11.01.16 12.01.16 13.01.16 2: AT0000A17HT4 .Close 3.436 3.426 3.376 3.347 3.388 3.379 3.349 3.325 3: Ask.Close 98.092 98.149 98.43 98.596 98.366 98.415 98.584 98.721 4: BID.Close 97.537 97.594 97.874 98.039 97.81 97.859 98.027 98.164 5: Bid Ask Spread 0.555 0.555 0.556 0.557 0.556 0.556 0.557 0.557 6: TR.IssuerRating P-2 P-2 P-2 P-2 P-2 P-2 P-2 P-2 6 variable(s) not shown: [V11 <char>, V12 <char>, V13 <char>, V14 <char>, V15 <char>, V16 <char>]

Answer 2

从数据完整性的角度来看，空白V1与非空白V1位于一个单独的组中:您的数据有三个组，两个组每个一行(非空白)，一个组有9行(全部为空白V1)。要正确地“按V1分组”，我们需要修复这个问题。我认识到，有时它可以是美学的首选，有非重复的价值(空白)，所以我将保留原来的。

dat[, V1b := V1
  ][!nzchar(V1b),V1b := NA
  ][,V1b := zoo::na.locf(V1b, na.rm = FALSE)]
dat
#               V1              V2       V3       V4       V5       V6       V7       V8       V9      V10      V11      V12
#           <char>          <char>   <char>   <char>   <char>   <char>   <char>   <char>   <char>   <char>   <char>   <char>
#  1:                     01.01.16 04.01.16 05.01.16 06.01.16 07.01.16 08.01.16 11.01.16 12.01.16 13.01.16 14.01.16 15.01.16
#  2: AT0000A1EKT9          .Close      #NV                                                                                 
#  3:                    Ask.Close      #NV                                                                                 
#  4:                    BID.Close      #NV                                                                                 
#  5:               Bid Ask Spread      #NV        0        0        0        0        0        0        0        0        0
#  6:              TR.IssuerRating      ba1      ba1      ba1      ba1      ba1      ba1      ba1      ba1      ba1      ba1
#  7: AT0000A17HT4          .Close    3.436    3.426    3.376    3.347    3.388    3.379    3.349    3.325      3.3     3.26
#  8:                    Ask.Close   98.092   98.149    98.43   98.596   98.366   98.415   98.584   98.721   98.863   99.089
#  9:                    BID.Close   97.537   97.594   97.874   98.039    97.81   97.859   98.027   98.164   98.305    98.53
# 10:               Bid Ask Spread    0.555    0.555    0.556    0.557    0.556    0.556    0.557    0.557    0.558    0.559
# 11:              TR.IssuerRating      P-2      P-2      P-2      P-2      P-2      P-2      P-2      P-2      P-2      P-2
# 5 variables not shown: [V13 <char>, V14 <char>, V15 <char>, V16 <char>, V1b <char>]

从现在起，我认为过滤应该更容易。

dat[, .SD[!any(grepl("Close$", V2) & V3 == "#NV"),], by = V1b]
#             V1b           V1              V2       V3       V4       V5       V6       V7       V8       V9      V10      V11
#          <char>       <char>          <char>   <char>   <char>   <char>   <char>   <char>   <char>   <char>   <char>   <char>
# 1:         <NA>                     01.01.16 04.01.16 05.01.16 06.01.16 07.01.16 08.01.16 11.01.16 12.01.16 13.01.16 14.01.16
# 2: AT0000A17HT4 AT0000A17HT4          .Close    3.436    3.426    3.376    3.347    3.388    3.379    3.349    3.325      3.3
# 3: AT0000A17HT4                    Ask.Close   98.092   98.149    98.43   98.596   98.366   98.415   98.584   98.721   98.863
# 4: AT0000A17HT4                    BID.Close   97.537   97.594   97.874   98.039    97.81   97.859   98.027   98.164   98.305
# 5: AT0000A17HT4               Bid Ask Spread    0.555    0.555    0.556    0.557    0.556    0.556    0.557    0.557    0.558
# 6: AT0000A17HT4              TR.IssuerRating      P-2      P-2      P-2      P-2      P-2      P-2      P-2      P-2      P-2
# 5 variables not shown: [V12 <char>, V13 <char>, V14 <char>, V15 <char>, V16 <char>]

Answer 3

如果您正在筛选的列是V3，则使用dplyr::filter()快速解决方案来删除符合特定条件的行。

data.filtered = filter(data，!V3 == '#NV')

另外，does this question也问同样的问题吗？