Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to replace NAs in multiple columns with dplyr

Tags:

r

dplyr

I would like to replace NAs in the columns that begin with v with the values in column x using current dplyr (1.0.2) code.

The same question is posted here, but the answer is outdated.

I have no trouble with one column:

suppressMessages(library(dplyr))
df <- data.frame(v1 = c(NA, 1, 2), v2 = c(3, NA, 4), v3 = c(5, 6, NA), x = c(7, 8, 9))
df %>% mutate(v1 = coalesce(v1, x))
#>   v1 v2 v3 x
#> 1  7  3  5 7
#> 2  1 NA  6 8
#> 3  2  4 NA 9

Created on 2020-11-03 by the reprex package (v0.3.0)

but can't figure out how to get it to work across multiple columns.

Here are a few things I've tried to no avail:

suppressMessages(library(dplyr))
df <- data.frame(v1 = c(NA, 1, 2), v2 = c(3, NA, 4), v3 = c(5, 6, NA), x = c(7, 8, 9))
df %>% mutate(across(starts_with("v")), . = coalesce(., x))
#> Error in list2(...): object 'x' not found

Created on 2020-11-03 by the reprex package (v0.3.0)

suppressMessages(library(dplyr))
df <- data.frame(v1 = c(NA, 1, 2), v2 = c(3, NA, 4), v3 = c(5, 6, NA), x = c(7, 8, 9))
df %>% mutate(across(starts_with("v")), . = coalesce(., df$x))
#> Error: Can't combine `..1` <data.frame> and `..2` <double>.

Created on 2020-11-03 by the reprex package (v0.3.0)

Appreciate your help.

like image 694
jtr13 Avatar asked Mar 02 '23 22:03

jtr13


1 Answers

You were very close with across(). The approach you want is:

df %>%
  mutate(across(starts_with("v"), coalesce, x))

Notice that the coalesce goes inside the across(), and that x (the second argument to coalesce() can be provided as a third argument. Result:

  v1 v2 v3 x
1  7  3  5 7
2  1  8  6 8
3  2  4  9 9

If you prefer something closer to your approach with coalesce(., x), you can also pass that as an anonymous function with a ~:

df %>%
  mutate(across(starts_with("v"), ~ coalesce(., x)))

In other situations, this can be more flexible (for instance, if . is not the first argument to the function).

like image 53
David Robinson Avatar answered Mar 04 '23 22:03

David Robinson