flagging redundant rows with NA

user_6472593 0
Viewed 93

I have a data.frame with complete and incomplete rows, something like :

dat <- data.frame(
  "one" = c(1, 1, 1, 3, NA),
  "two" = c(2, 2, NA, 4, 4),
  "three" = c(1, 1, NA, 5, 5),
  "four"  = c(2, NA, 2, 6, 6)
)

I want to tag the rows that contain NA's as TRUE where their values equal values in the same column, on a row with fewer NA's, so

dat$redundant <- c(FALSE, TRUE, TRUE, FALSE, TRUE)

Essentially I'm looking for duplicated(dat, MARGIN = 1) but with NA counting as a match to any present values. Is this possible?

r
4 Answers

We can use

library(dplyr)
library(tidyr)
dat %>% 
     fill(everything()) %>% 
     duplicated
 #[1] FALSE  TRUE  TRUE FALSE  TRUE
user_37322710

You can sweep the difference per row, build the rowSums ignoring NA, ignor the upper tri and diag. From this where the rowSums are >0 should indicate the duplicated rows ignoring NA.

tt <- sapply(seq_len(nrow(dat)),
 function(i) rowSums(sweep(dat, 2, unlist(dat[i,])), TRUE)==0)
tt[upper.tri(tt, TRUE)] <- FALSE
rowSums(tt) > 0
#[1] FALSE  TRUE  TRUE FALSE  TRUE

Adding line 2 to dat and try it again:

dat2 <- rbind(dat, dat[2,])
tt <- sapply(seq_len(nrow(dat2)),
 function(i) rowSums(sweep(dat2, 2, unlist(dat2[i,])), TRUE)==0)
tt[upper.tri(tt, TRUE)] <- FALSE
dat2$redundant <- rowSums(tt) > 0
dat2
#   one two three four redundant
#1    1   2     1    2     FALSE
#2    1   2     1   NA      TRUE
#3    1  NA    NA    2      TRUE
#4    3   4     5    6     FALSE
#5   NA   4     5    6      TRUE
#21   1   2     1   NA      TRUE

And with the data from @Peter-Ellis (Thanks!):

dat <- data.frame("one" = c(1, 1, 1, 3, NA, NA), "two" = c(2, 2, NA, 4, 4, 9),   
  "three" = c(1, 1, NA, 5, 5, 5), "four"  = c(2, NA, 2, 6, 6,6) )
tt <- sapply(seq_len(nrow(dat)),
 function(i) rowSums(sweep(dat, 2, unlist(dat[i,])), TRUE)==0)
tt[upper.tri(tt, TRUE)] <- FALSE
rowSums(tt) > 0
#[1] FALSE  TRUE  TRUE FALSE  TRUE FALSE
user_104885040

OK, this seems to me really tricky and I may have misunderstood the question. Here is at least the beginning of an answer. As per my comments, here is an extended version of your original example

dat <- data.frame(
  "one" = c(1, 1, 1, 3, NA, NA),
  "two" = c(2, 2, NA, 4, 4, 9),
  "three" = c(1, 1, NA, 5, 5, 5),
  "four"  = c(2, NA, 2, 6, 6,6)
)

# desired behaviour is foo(dat) = c(FALSE, TRUE, TRUE, FALSE, TRUE, FALSE)

For the record, this is what dat looks like:

> dat
  one two three four
1   1   2     1    2
2   1   2     1   NA
3   1  NA    NA    2
4   3   4     5    6
5  NA   4     5    6
6  NA   9     5    6

Desired behaviour is TRUE when a row both contains an NA, AND does not contain in any of its columns a value that is unique for that column (unique that is for the columns that are in the data after we remove the other rows that are going to be flagged TRUE - an important proviso I might be misinterpreting from your question).

Here is a very clunky solution. It involves going through the data one cell at a time! Very un-R like.

foo <- function(d){
  # we are going to approach this backwards! You are a "good" row if you 
  # are either complete, or one of your cells is unique in its column, compared
  # to the good rows
  
  
  flag1 <- complete.cases(d)
  
  flag2 <- rep(FALSE, nrow(d))
  for(i in 1:nrow(d)){
    for(j in 1:ncol(d)){
      # check if the value in this row, col is NOT IN any of the complete cases
      # of data in other rows or rows that we are keeping
      if(!is.na(d[i, j]) && !d[i, j] %in% d[-i, j][flag1 | flag2]){
        flag2[i] <- TRUE
      }
    }
  }
  
  # so flag3 is EITHER your row is complete, OR it has a unique value
  flag3 <- (flag1 | flag2)
  
  # now we return the not-good rows, so TRUE will be redundant rows
  return(!flag3)

}

It works with the original test case:

> foo(dat)
[1] FALSE  TRUE  TRUE FALSE  TRUE FALSE

However, I can think of problematic edge cases. What if the last row of our data was repeated? I'm not sure what the desired behaviour here is, but I am giving a FALSE to the first time you get a 9 in column two, then TRUE later (as these are duplicates of previous rows). See:

> dat2 <- rbind(dat, c(NA, 9, 5, 6))
> dat2
  one two three four
1   1   2     1    2
2   1   2     1   NA
3   1  NA    NA    2
4   3   4     5    6
5  NA   4     5    6
6  NA   9     5    6
7  NA   9     5    6
> foo(dat2)
[1] FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE

So you might need to edit this depending on what your actual desired behaviour is in this and related edge cases. But hopefully this has given you a start.

user_11810970

Ok, wound up using a grep-based approach instead, which I guess is a bit weird but works:

  find_unique_combos <- function(dat) {

    na_count <- rowSums(is.na(dat))

    strings <- apply(dat, MARGIN = 1, function(row) {
      row[is.na(row)] <- '.'
      paste0(row, collapse ='')
    })
    
    sapply(seq_along(strings), function(i) {
      if(na_count[i] == 0) { return(TRUE) }
      test_targets <- strings[na_count <= na_count[i]]
      test_targets <- test_targets[!test_targets %in% strings[i]]
      !any(grepl(strings[i], test_targets))
    })

  }

There are no duplicate rows in my input datasets and they're not very big tables, so this should be problem-free. Thanks for getting me thinking, all.

user_64725930
Related