Replace NAs values within a column according to consensus value in another

user_12559770 0
Viewed 41

I have a dataframe such as :

Groups Event Value
G1     1     Canidae
G1     1     Canidae
G1     1     Felidae
G1     1     NA
G1     2     Felidae
G1     2     NA
G1     2     NA 
G1     2     Felidae
G1     3     NA
G2     1     NA
G2     1     NA
G3     1     Lemuridae
G3     2     NA 
G3     3     Lemuridae 
G4     1     Felidae
G4     1     Felidae
G4     1     unknown
G5     1     unknown
G5     1     Felidae

And I would like within each Groups and Event to fill NA values with a Value according to the consensus value, for instance there is one NA in the G1 Event1, then the consensus value is Canidae, so I replace the NA by Canidae

At the end I should get :

Groups Event Value
G1     1     Canidae
G1     1     Canidae
G1     1     Canidae
G1     2     Felidae
G1     2     Felidae
G1     2     Felidae
G1     2     Felidae
G1     3     NA
G2     1     Lemuridae
G2     1     Lemuridae
G3     1     Lemuridae
G3     2     NA 
G3     3     Lemuridae 
G4     1     Felidae
G4     1     Felidae
G4     1     Felidae
G5     1     Felidae
G5     1     Felidae

Does someone have an idea please? Thank you very much for your time.

Here are the data:

structure(list(Groups = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L), .Label = c("G1", "G2", 
"G3", "G4"), class = "factor"), Event = c(1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 3L, 1L, 1L, 1L, 2L, 3L, 1L, 1L, 1L), Value = structure(c(1L, 
1L, 2L, NA, 2L, NA, NA, 2L, NA, NA, NA, 3L, NA, 3L, 2L, 2L, 4L
), .Label = c("Canidae", "Felidae", "Lemuridae", "unknown"), class = "factor")), class = "data.frame", row.names = c(NA, 
-17L))
dplyr r
2 Answers

We can do a group by Mode

library(dplyr)
df1 %>%
   mutate(Value = as.character(Value)) %>%
   group_by(Groups, Event) %>%
   mutate(Value = replace(Value, is.na(Value)|Value %in% "unknown", 
         Mode(Value[Value != "unknown"])))

where

Mode <- function(x) {
  ux <- unique(x)
  ux[which.max(tabulate(match(x, ux)))]
}
user_37322710

A data.table option

setDT(df)[
  ,
  Value := replace(
    Value,
    is.na(Value),
    ifelse(all(is.na(Value)),
      NA,
      names(rev(sort(table(na.omit(Value)))))[1]
    )
  ), .(Groups, Event)
]

gives

    Groups Event     Value
 1:     G1     1   Canidae
 2:     G1     1   Canidae
 3:     G1     1   Felidae
 4:     G1     1   Canidae
 5:     G1     2   Felidae
 6:     G1     2   Felidae
 7:     G1     2   Felidae
 8:     G1     2   Felidae
 9:     G1     3      <NA>
10:     G2     1      <NA>
11:     G2     1      <NA>
12:     G3     1 Lemuridae
13:     G3     2      <NA>
14:     G3     3 Lemuridae
user_121587570
Related