function - In R data frame for each set of rows and column use value that is not na

Question

I have a data frame df of the following structure:

observation  x1  x2  x3  x4
"obs1"         NA  NA  NA  51
"obs1"         NA  NA  NA  NA
"obs1"         NA  25  NA  NA
"obs2"         NA  NA  NA  NA        
"obs2"         NA  NA  NA  NA
"obs2"         NA  NA  NA  56
"obs3"         26  NA  NA  NA
"obs3"         NA  82  NA  NA
"obs3"         NA  NA  "x" NA

I want a data frame df2 that, for each observation and for each column, takes the one value, that is not NA. The resulting data frame should look like this:

observation  x1  x2  x3  x4
"obs1"         NA  25  NA  51
"obs2"         NA  NA  NA  56
"obs3"         26  82  "x" NA

I tried to do:

only_value = function(x){
   x[which(!is.na(x))]
}
df2 = df %>% lapply(only_value) %>% as.data.frame()

However, this only works if there is the same amount of values for each observation. This is not the case in my example.

question from:https://stackoverflow.com/questions/65934720/in-r-data-frame-for-each-set-of-rows-and-column-use-value-that-is-not-na

Answer 1

Change the only_value function to return only 1st non-NA value.

only_value = function(x){
  x[!is.na(x)][1]
}

Now apply this function by group to columns x1 to x4 :

library(dplyr)
df %>%
  group_by(observation) %>%
  summarise(across(x1:x4, only_value))

# observation    x1    x2 x3       x4
#* <chr>       <int> <int> <chr> <int>
#1 obs1           NA    25 NA       51
#2 obs2           NA    NA NA       56
#3 obs3           26    82 x        NA