I have duplicate IDs in a data set, and would like to keep the one with the least amount of NAs across the data columns

user_15926962 0
Viewed 47

I have a data frame that has duplicate IDS. I want to keep the ID that has the least amount of NAs (so the most complete set for the ID). In this example i would want to keep the second 123 and the second 124 (lease NAs)

I can identify the duplicates but I cant write a code to essentially say 1)For each duplicate, keep the one with less NAs The code could also say 2)For each duplicate, remove the one with more NAs,

Here is example data

id    Col1    col1 2   col 3  col 4
123   10       NA       NA     3
123   50       3        2      NA
124   30       5        7      NA 
124   30       8        1      2
na duplicates r
3 Answers

You can sort by the number of NAs in each row and then remove duplicates:

require(dplyr)
df %>% arrange(rowSums(is.na(df))) %>% filter(!duplicated(id)) %>% arrange(id)

   id Col1 col2 col3 col4
1 123   50    3    2   NA
2 124   30    8    1    2

Data:

df = read.table(text='id Col1 col2 col3 col4
123   10       NA       NA     3
123   50       3        2      NA
124   30       5        7      NA
124   30       8        1      2', header = T, strip.white = T)
user_158153170 
library(data.table)
setDT(df)

df[order(rowSums(is.na(df))), head(.SD, 1), by = id]

# id Col1 col2 col3 col4
# 1: 124   30    8    1    2
# 2: 123   50    3    2   NA
user_67627550

We could use slice

library(dplyr)
df %>% 
   group_by(id) %>%
   slice(which.min(rowSums(is.na(cur_data())))) %>%
   ungroup
# A tibble: 2 x 5
#     id  Col1  col2  col3  col4
#  <int> <int> <int> <int> <int>
#1   123    50     3     2    NA
#2   124    30     8     1     2

Or using c_across

df %>%
  rowwise %>% 
  mutate(cnt = sum(is.na(c_across(-id)))) %>%
  ungroup %>% 
  arrange(id, cnt) %>%
  distinct(id, .keep_all = TRUE)

data

df <- structure(list(id = c(123L, 123L, 124L, 124L), Col1 = c(10L, 
50L, 30L, 30L), col2 = c(NA, 3L, 5L, 8L), col3 = c(NA, 2L, 7L, 
1L), col4 = c(3L, NA, NA, 2L)), class = "data.frame", row.names = c(NA, 
-4L))
user_37322710
Related