R dynamically merge multiple columns with NA into a single column

user_11439260 0
Viewed 97

Is there a way to dynamically merge columns with NA values?

tibble(
  x = c(1:2, NA, NA, NA, NA, NA),
  y = c(NA, NA, 4:7, NA),
  z = c(NA, NA, NA, NA, NA, NA, 9)
)

# A tibble: 7 x 3
      x     y     z
  <int> <int> <dbl>
1     1    NA    NA
2     2    NA    NA
3    NA     4    NA
4    NA     5    NA
5    NA     6    NA
6    NA     7    NA
7    NA    NA     9

expect:

# A tibble: 7 x 3
      x     y     z
  <int> <int> <dbl>
1     1    NA    NA
2     2    NA    NA
3     4     4    NA
4     5     5    NA
5     6     6    NA
6     7     7    NA
7     9    NA     9

This example is a read from an xlsx file, so in some cases the column yor z may not exist

dplyr dataframe r
2 Answers

Update With the help of Ronak. See comments: Now it should work for df with different columns: More general solution for coalesce in combination with do.call

library(dplyr)
df <- tibble(
  x = c(1:2, NA, NA, NA, NA, NA),
  y = c(NA, NA, 4:7, NA)
)

df %>%
  mutate(result = do.call(coalesce, df))

Output:

      x     y result
  <int> <int>  <int>
1     1    NA      1
2     2    NA      2
3    NA     4      4
4    NA     5      5
5    NA     6      6
6    NA     7      7
7    NA    NA     NA

First answer This is a job for coalesce from dplyr package: finds the first non-missing value at each position.

library(dplyr)
df %>% mutate(result = coalesce(x,y,z))

Output:

      x     y     z result
  <int> <int> <dbl>  <dbl>
1     1    NA    NA      1
2     2    NA    NA      2
3    NA     4    NA      4
4    NA     5    NA      5
5    NA     6    NA      6
6    NA     7    NA      7
7    NA    NA     9      9
user_133216470

You can get the first non-NA value from each row :

library(dplyr)

df %>%
  rowwise() %>%
  mutate(result = na.omit(c_across())[1]) %>%
  ungroup

#      x     y     z result
#  <int> <int> <dbl>  <dbl>
#1     1    NA    NA      1
#2     2    NA    NA      2
#3    NA     4    NA      4
#4    NA     5    NA      5
#5    NA     6    NA      6
#6    NA     7    NA      7
#7    NA    NA     9      9

In base R :

df$result <- apply(df, 1, function(x) na.omit(x)[1])
user_39629140
Related