I have a data table with 4 columns: ID, Name, Rate1, Rate2.
I want to remove duplicates where ID, Rate1, and Rate 2 are the same, but if they are both NA, I would like to keep both rows.
Basically, I want to conditionally remove duplicates, but only if the conditions != NA.
For example, I would like this:
ID Name Rate1 Rate2
1 Xyz 1 2
1 Abc 1 2
2 Def NA NA
2 Lmn NA NA
3 Hij 3 5
3 Qrs 3 7
to become this:
ID Name Rate1 Rate2
1 Xyz 1 2
2 Def NA NA
2 Lmn NA NA
3 Hij 3 5
3 Qrs 3 7
Thanks in advance!
EDIT: I know it's possible to just take a subset of the data table where the Rates are NA, then remove duplicates on what's left, then add the NA rows back in - but, I would rather avoid this strategy. This is because in reality there are quite a few couplets of rates that I want to do this for consecutively.
EDIT2: Added in some more rows to the example for clarity.
A base R option would be to use duplicated on the subset of dataset without the 'Name' column i.e. column index 2 to create a logical vector, negate (! - TRUE becomes FALSE and viceversa) so that TRUE would be non-duplicated rows. Along with that create another condition with rowSumson a logical matrix (is.na(df1[3:4]) - Rate columns) to get rows that are all NA's - here we compare it with 2 - i.e. the number of Rate columns in the dataset). Both the conditions are joined by | to create the expected logical index
i1 <- !duplicated(df1[-2])| rowSums(is.na(df1[3:4])) == 2
df1[i1,]
# ID Name Rate1 Rate2
#1 1 Xyz 1 2
#3 2 Def NA NA
#4 2 Lmn NA NA
Or with Reduce from base R
df1[Reduce(`&`, lapply(df1[3:4], is.na)) | !duplicated(df1[-2]), ]
Wrapping it in a function
f1 <- function(dat, i, method ) {
nm1 <- grep("^Rate", colnames(dat), value = TRUE)
i1 <- !duplicated(dat[-i])
i2 <- switch(method,
"rowSums" = rowSums(is.na(dat[nm1])) == length(nm1),
"Reduce" = Reduce(`&`, lapply(dat[nm1], is.na))
)
i3 <- i1|i2
dat[i3,]
}
-testing
f1(df1, 2, "rowSums")
# ID Name Rate1 Rate2
#1 1 Xyz 1 2
#3 2 Def NA NA
#4 2 Lmn NA NA
f1(df1, 2, "Reduce")
# ID Name Rate1 Rate2
#1 1 Xyz 1 2
#3 2 Def NA NA
#4 2 Lmn NA NA
f1(df2, 2, "rowSums")
# ID Name Rate1 Rate2
#1 1 Xyz 1 2
#3 2 Def NA NA
#4 2 Lmn NA NA
#5 3 Hij 3 5
#6 3 Qrs 3 7
f1(df2, 2, "Reduce")
# ID Name Rate1 Rate2
#1 1 Xyz 1 2
#3 2 Def NA NA
#4 2 Lmn NA NA
#5 3 Hij 3 5
#6 3 Qrs 3 7
if there are multiple 'Rate' columns (say 100 or more - only thing to change in the first solution is 2 should be changed to the number of 'Rate' columns)
Or using tidyverse
library(tidyvesrse)
df1 %>%
group_by(ID) %>%
filter_at(vars(Rate1, Rate2), any_vars(!duplicated(.)|is.na(.)))
# A tibble: 3 x 4
# Groups: ID [2]
# ID Name Rate1 Rate2
# <int> <chr> <int> <int>
#1 1 Xyz 1 2
#2 2 Def NA NA
#3 2 Lmn NA NA
df2 %>%
group_by(ID) %>%
filter_at(vars(Rate1, Rate2), any_vars(!duplicated(.)|is.na(.)))
# A tibble: 5 x 4
# Groups: ID [3]
# ID Name Rate1 Rate2
# <int> <chr> <int> <int>
#1 1 Xyz 1 2
#2 2 Def NA NA
#3 2 Lmn NA NA
#4 3 Hij 3 5
#5 3 Qrs 3 7
As @Paul mentioned in the comments, the updated tidyverse syntax as on Nov 4 2021 is
library(dplyr)
df2 %>%
group_by(ID) %>%
filter(if_any(cRate1, Rate2), ~ !duplicated(.)|is.na(.)))
df1 <- structure(list(ID = c(1L, 1L, 2L, 2L), Name = c("Xyz", "Abc",
"Def", "Lmn"), Rate1 = c(1L, 1L, NA, NA), Rate2 = c(2L, 2L, NA,
NA)), class = "data.frame", row.names = c(NA, -4L))
df2 <- structure(list(ID = c(1L, 1L, 2L, 2L, 3L, 3L), Name = c("Xyz",
"Abc", "Def", "Lmn", "Hij", "Qrs"), Rate1 = c(1L, 1L, NA, NA,
3L, 3L), Rate2 = c(2L, 2L, NA, NA, 5L, 7L)), class = "data.frame",
row.names = c(NA, -6L))