How to combine scattered values into one row using R-CodePudding

I have a dataset like this and i want the desired dataset as below.

    dat <- read.table(text="Id  Bug    Drug1 Drug2 
A   Staph     NA  S
A   Staph  S  NA
A   E.coli    NA  S
A   E.coli  S  NA", header=TRUE)


dat.desired <- read.table(text="Id  Bug    Drug1 Drug2 
A   Staph     S  S
A   E.coli    S  S", header=TRUE)

CodePudding user response：

The following should work using the dplyr library:

library(dplyr)

dat %>% 
  group_by(Id, Bug) %>% 
  summarise(across(Drug1:Drug2, ~.x[!is.na(.x)][1]))

Output:

#  Id    Bug    Drug1 Drug2
# <chr> <chr>  <chr> <chr>
# 1 A     E.coli S     S    
# 2 A     Staph  S     S

CodePudding user response：

For the lazy (like me), package {dplyr} even offers an own verb for jpsmith's solution: coalesce

library(dplyr)

dat %>%
    mutate(across(Drug1:Drug2, ~ coalesce(Drug1, Drug2))) %>%
    distinct