dplyr function to combine repeated data, remove missing data, and maintain conflicting data

user_948397 0
Viewed 50

I have a dataset where some entries appear more than once, sometimes with data in one entry that is missing in another, sometimes with completely missing data, and sometimes with conflicting data:

# A tibble: 9 x 4 
  ID    name    age   fsm
  <chr> <chr> <dbl> <dbl>
1 0001  Peter    13    NA
2 0001  NA       13     1
3 0001  Barry    13     1
4 0002  Jane     13     1
5 0002  Jane     NA     1
6 0003  Billy    12     0
7 0003  Billy    12     1
8 0004  Sally    12    NA
9 0004  Sally    12    NA

I would like to combine entries with missing data (e.g. 0001 and 0002), maintain conflicting data (0003) and maintain data that double NA (0004). To produce this:

# A tibble: 9 x 4 
  ID    name    age   fsm
  <chr> <chr> <dbl> <dbl>
1 0001  Peter    13     1
2 0001  Barry    13     1
3 0002  Jane     13     1
4 0003  Billy    12     0
5 0003  Billy    12     1
6 0004  Sally    12    NA

Building on this: how to combine repeated rows with missing fields R

I've written the following:

tmp %>%
group_by(ID) %>%
summarise(across(everything(), ~ ifelse(length(na.omit(.x)) == 0, NA, na.omit(.x)))) %>%
distinct()

But it loses the double entry, 0003

   ID    name    age   fsm
   <chr> <chr> <dbl> <dbl>
 1 0001  Peter    13     1
 2 0002  Jane     13     1
 3 0003  Billy    12     0
 4 0004  Sally    12    NA

data as a dput:

structure(list(ID = c("0001", "0001", "0001", "0002", "0002", 
"0003", "0003", "0004", "0004"), name = c("Peter", NA, "Barry", 
"Jane", "Jane", "Billy", "Billy", "Sally", "Sally"), age = c(13, 
13, 13, 13, NA, 12, 12, 12, 12), fsm = c(NA, 1, 1, 1, 1, 0, 1, 
NA, NA)), class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -9L), spec = structure(list(cols = list(
ID = structure(list(), class = c("collector_character", "collector"
)), name = structure(list(), class = c("collector_character", 
"collector")), age = structure(list(), class = c("collector_double", 
"collector")), fsm = structure(list(), class = c("collector_double", 
"collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), skip = 1L), class = "col_spec"))
dplyr r
3 Answers

Are you looking for this kind of solution?

library(tidyverse)

df %>% 
  group_by(ID, name, age, fsm) %>% 
  mutate(dupe = n()>1) %>% 
  group_by(ID) %>%
  dplyr::summarise(across(everything(), ~ ifelse(length(na.omit(.x)) == 0  & dupe == TRUE, NA, na.omit(.x)))) %>%
  distinct() %>% 
  select(-dupe)

Output:

  ID    name    age   fsm
  <chr> <chr> <dbl> <dbl>
1 0001  Peter    13     1
2 0001  Barry    13     1
3 0002  Jane     13     1
4 0003  Billy    12     0
5 0003  Billy    12     1
6 0004  Sally    12    NA
user_133216470

I have a data.table option for you

library(data.table)

# convert to data.table
dt <- data.table(dt)

# this 1) groups by ID, 2) returns unique values per column and omitting NA's
dt[ ,lapply(.SD, function(x) unique(na.exclude(x))), .SDcols = 2:4, by = ID ]

# the result is
     ID  name age fsm
1: 0001 Peter  13   1
2: 0001 Barry  13   1
3: 0002  Jane  13   1
4: 0003 Billy  12   0
5: 0003 Billy  12   1
6: 0004 Sally  12  NA

P.S. I guess you want a tidyverse solution but the advantage of data.table is that it is much faster on big data sets. The syntax might be a bit daunting.

user_33373990 
library(tidyverse)

df <- tibble::tribble(
       ~ID,   ~name, ~age, ~fsm,
    "0001", "Peter",   13,   NA,
    "0001",      NA,   13,    1,
    "0001", "Barry",   13,    1,
    "0002",  "Jane",   13,    1,
    "0002",  "Jane",   NA,    1,
    "0003", "Billy",   12,    0,
    "0003", "Billy",   12,    1,
    "0004", "Sally",   12,   NA,
    "0004", "Sally",   12,   NA
    )

df %>%
  group_by(ID) %>%
  filter(length(.)==1 | across(everything(), ~ length(unique(na.omit(.)))>0)) %>%
  fill(everything(), .direction = "downup") %>%
  distinct()
#> # A tibble: 6 x 4
#> # Groups:   ID [4]
#>   ID    name    age   fsm
#>   <chr> <chr> <dbl> <dbl>
#> 1 0001  Peter    13     1
#> 2 0001  Barry    13     1
#> 3 0002  Jane     13     1
#> 4 0003  Billy    12     0
#> 5 0003  Billy    12     1
#> 6 0004  Sally    12    NA
user_38083940
Related