how to combine repeated rows with missing fields R

user_948397 0
Viewed 47

I have a a dataset with multiple repeated entries, the entries are largely the same, but with data missing, The missing data could be in any field except for the ID. For example:

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

I need to combine the rows, i.e. merging the NAs with the given values from other rows that have the same ID

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

Above data example as a dput:

structure(list(ID = c("0001", "0001", "0002", "0002", "0003"), 
name = c("Peter", NA, "Jane", "Jane", "Billy"), age = c(13, 
13, 13, NA, 12), fsm = c(NA, 1, 1, 1, 0)), class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -5L), 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
2 Answers

Updated Special thanks to dear @akrun who does not hesitate to provide insight to our solutions and share his knowledge and experience with us.

I hope this is what you are looking for:

library(dplyr)

df %>%
  group_by(ID) %>%
  summarise(across(everything(), ~ first(na.omit(.x))))

# A tibble: 3 x 4
  ID    name    age   fsm
  <chr> <chr> <dbl> <dbl>
1 0001  Peter    13     1
2 0002  Jane     13     1
3 0003  Billy    12     0

This solution also works. It may sound a bit verbose but quite useful and handy in these kinda situations:

library(dplyr)
library(tidyr)
library(purrr)

df %>%
  nest(data = -c(ID)) %>%
  mutate(data = map(data, ~ map_dfc(., na.omit))) %>%     # We use one map function inside the other since one will iterate elements of the nested list and the other iterate over the elements of the underlying tibbles
  unnest(cols = c(data)) %>%
  group_by(ID) %>%
  summarise(across(everything(), first))

# A tibble: 3 x 4
  ID    name    age   fsm
  <chr> <chr> <dbl> <dbl>
1 0001  Peter    13     1
2 0002  Jane     13     1
3 0003  Billy    12     0
user_143145200

One data.table option

> setDT(df)[, lapply(.SD, function(...) fcoalesce(as.list(...))), ID]
     ID  name age fsm
1: 0001 Peter  13   1
2: 0002  Jane  13   1
3: 0003 Billy  12   0

or

> type.convert(setDT(df)[, as.list(do.call(fcoalesce, asplit(.SD, 1))), ID], as.is = TRUE)
   ID  name age fsm
1:  1 Peter  13   1
2:  2  Jane  13   1
3:  3 Billy  12   0
user_121587570
Related