Subsetting data.table based on repeated rows

Question

I have a list of data tables stored in an object ddf (a sample is shown below):

 [[43]]
    V1 V2 V3
1:  b  c  a
2:  b  c  a
3:  b  c  a
4:  b  c  a
5:  b  b  a
6:  b  c  a
7:  b  c  a

 [[44]]
   V1 V2 V3
1:  a  c  a
2:  a  c  a
3:  a  c  a
4:  a  c  a
5:  a  c  a

 [[45]]
   V1 V2 V3
1:  a  c  b
2:  a  c  b
3:  a  c  b
4:  a  c  b
5:  a  c  b
6:  a  c  b
7:  a  c  b
8:  a  c  b
9:  a  c  b
               .............and so on till [[100]]

I want to Subset the list ddf such that the result only consists of ddf's which:

1. have at least 9 rows each
2. each of the 9 rows are same
3. I want to store this sub-setted output

I have written some code for this below:

 for(i in 1:100){
 m=(as.numeric(nrow(df[[i]]))>= 9)
 if(m == TRUE & df[[i]][1,] = df[[i]][2,] = 
 =df[[i]][3,] =df[[i]][4,] =df[[i]][5,] =df[[i]][6,]=
 df[[i]][7,]=df[[i]][8,]=df[[i]][9,]){
 print(df[[i]])
 }}

Please tell me whats wrong & how I can generalize the result for sub-setting based on "n" similar rows.

[Follow-up Question]

    Answer obtained from Main question:
    > ddf[sapply(ddf, function(x) nrow(x) >= n & nrow(unique(x)) == 1)]
      $`61`
         V1 V2 V3
      1:  a  c  b
      2:  a  c  b
      3:  a  c  b
      4:  a  c  b
      5:  a  c  b
      6:  a  c  b
      7:  a  c  b

      $`68`
         V1 V2 V3
      1:  a  c  a
      2:  a  c  a
      3:  a  c  a
      4:  a  c  a
      5:  a  c  a
      6:  a  c  a
      7:  a  c  a
      8:  a  c  a

      $`91`
         V1 V2 V3
      1:  b  c  a
      2:  b  c  a
      3:  b  c  a
      4:  b  c  a
      5:  b  c  a
      6:  b  c  a
      7:  b  c  a

               ..... till the last data.frame which meet the row matching criteria (of at least 9 similar rows)

      There are only 2 types of elements in the list: 
                  **[[.. ]]**        
     **Case 1.** >70% accuracy       
     **Case 2.** <70% accuracy       

You will notice that the Output shown above in the "Follow Up Question" is for

$'61', $'68' & $'91', but there is NO output for the other dataframes which don't match the "matching row" criteria.

I need an output where these missing values which don't match the output criteria give an output of "bad output".

Thus the Final list should be the same length as the input list.

By placing them side-by-side using paste I should be able to see each output.

Answer 1

We can loop through the list ('ddf'), subset only the duplicate rows with (duplicated), order the dataset, if the number of rows of the dataset 'x1' is greater than 8, then get the first 9 rows (head(x1, 9)) or else return 'bad result' printed

lapply(ddf, function(x) {
  x1 <- x[duplicated(x)|duplicated(x, fromLast=TRUE)]
if(nrow(x1)>9) {
 x1[order(V1, V2, V3), head(.SD, 9)] 

  } else "bad answer"
 })
#[[1]]
#   V1 V2 V3
#1:  b  c  a
#2:  b  c  a
#3:  b  c  a
#4:  b  c  a
#5:  b  c  a
#6:  b  c  a
#7:  b  c  a
#8:  b  c  a
#9:  b  c  a

#[[2]]
#[1] "bad answer"

#[[3]]
#[1] "bad answer"

data

ddf <- list(data.table(V1 = 'b', V2 = rep(c('c', 'b', 'c'), c(8, 1, 2)), V3 = 'a'),
       data.table(V1 = rep("a", 5), V2 = rep("c", 5), V3 = rep("a", 5)),
       data.table(V1 = c('b', 'a', 'b', 'b'), V2 = c('b', 'a', 'c', 'b'),
       V3 = c("c", "d", "a", "b")))

Answer 2

When ddf is your list of datatables, then:

ddf[sapply(ddf, nrow) >= 9 & sapply(ddf, function(x) nrow(unique(x))) == 1]

should give you the desired result.

Where:

• sapply(ddf, nrow) >= 9 checks whether the datatables have nine or more rows
• sapply(ddf, function(x) nrow(unique(x))) == 1 checks whether all the rows are the same.

Or with one sapply call as @docendodiscimus suggested:

ddf[sapply(ddf, function(x) nrow(x) >= 9 & nrow(unique(x)) == 1)]

Or by using the .N special symbol and the uniqueN function of data.table:

ddf[sapply(ddf, function(x) x[,.N] >= 9 & uniqueN(x) == 1)]

Another option is to use Filter (following the suggestion of @Frank in the comments):

Filter(function(x) nrow(x) >= 9 & uniqueN(x) == 1, ddf)

---

Two approaches to get the datatable numbers:

1. Using which:

which(sapply(ddf, function(x) nrow(x) >= 9 & nrow(unique(x)) == 1))

2. Assign names to the datatables in the list:

names(ddf) <- paste0('dt', 1:length(ddf))

now the output will have the datatable number in the output:

$dt4
  V1 V2 V3
1  a  c  b
2  a  c  b
3  a  c  b
4  a  c  b
5  a  c  b
6  a  c  b
7  a  c  b
8  a  c  b
9  a  c  b