Making a while loop more efficient for use on a large data.table to delete rows based on certain conditions

Question

I have a pretty big amount of data in a data table. I would like to delete a number of rows if there is a certain value in a cell.

Below is an excerpt from my data table:

             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

Using dput(head(x)) so the table can be easily copied

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"
), .internal.selfref = <pointer: 0x7fdb3b0128e0>)

I have grouped my data by the ISIN Number in column 1. For some of these ISIN's, I do not have any price data, which can be seen by the #NV next to .Close

What I would like to do is delete the whole ISIN entry if there is a #NV next to .Close.

The data table should look as follows after deleting the rows I want:

             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

I have written a while loop which works fine for small amounts of test data. However, when I apply this while loop to my full data.table the loop is very inefficient and takes a long time to run.

The while loop looks as follows

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
  }
}

Is there any way to make this loop more efficient?

Can you please provide your data in a more usable format? The empty strings make it problematic to just copy and read.table() it, please use paste in the output from dput(head(x)). Thanks! — r2evans
I edited my question so now there is an output from dput(head(x)) in the question text — Elias K.

Kodiakflds Kodiakflds · Accepted Answer · 2022-05-11T16:26:45

A quick solution if V3 is the column you are filtering, uses dplyr::filter() to drop rows meeting a certain condition.

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

Also, does this question ask the same thing?

Making a while loop more efficient for use on a large data.table to delete rows based on certain conditions

1 Answers