'Coalesce pairs of variables within a dataframe based on a regular expression

I want to use dplyr::coalesce to find the first non-missing value between pairs of variables in a dataframe containing multiple pairs of variable. The goal is to create a new dataframe with now only one copy for each pair of variable (a coalesce variable without NA values).

Here is an example:

df <- data.frame(
      A_1=c(NA, NA, 3, 4, 5),
      A_2=c(1, 2, NA, NA, NA),
      B_1=c(NA, NA, 13, 14, 15),
      B_2=c(11, 12, NA, NA, NA))


Expected output: 

A  B
1  11
2  12
3  13
4  14
5  15

I am guessing a mix of dplyr::coalesce with maybe dplyr::mutate_at based on regular expression could be use but I am not sure how to do it. Is there a way to complete this task with the tidyverse grammar?

Thanks!

EDIT: thanks everyone for your answers! However, I should have included the naming convention for my variables to facilitate the transfer of your answers to my actual problem.. I am sorry about that. My variables are geochemistry variables named in two parts (name of chemical element plus name of core).

Example: Al_TAC4.25.275 where Al is the element and TAC4.25.275 is the core. I want to coalesce the data from 3 different cores (second part of name) for each element (first part of name). I have 25 pairs of element to coalesce.

r dplyr tidyverse purrr coalesce reactjs autocomplete material-ui

Solution 1:^[1]

Here is another more concise solution, comparing to my other one. I think the use of cur_data() function is very helpful but you could also use across(everything()) in its place:

library(dplyr)
library(purrr)

unique(sub("(\\D)_\\d+", "\\1", names(df))) %>%
  map_dfc(~ df %>%
            select(starts_with(.x)) %>%
             summarise(!!.x := do.call(coalesce, cur_data())))

  A  B
1 1 11
2 2 12
3 3 13
4 4 14
5 5 15

Here is another solution for as many pairs as possible. Just note that I used bang bang operator !!! in order to collapse elements of a data frame into standalone single arguments so that I could apply coalesce on them:

library(dplyr)
library(rlang)

as.data.frame(do.call(cbind, lapply(split.default(df, sub("(\\D)_\\d+", "\\1", names(df))), function(x) {
  coalesce(!!!x)
})))

  A  B
1 1 11
2 2 12
3 3 13
4 4 14
5 5 15

Solution 2:^[2]

A base R option

list2DF(
  lapply(
    split.default(df, gsub("_.*", "", names(df))),
    rowSums,
    na.rm = TRUE
  )
)

gives

Solution 3:^[3]

Here is an alternative with pivoting:

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(
    everything()
  ) %>% 
  mutate(name = substr(name, 1, 1)) %>% 
  na.omit %>% 
  pivot_wider(
    names_from = name,
    values_from = value,
    values_fn = list
  ) %>% 
  unnest(cols = c(A, B))

      A     B
  <dbl> <dbl>
1     1    11
2     2    12
3     3    13
4     4    14
5     5    15

Solution 4:^[4]

Edit: I believe this solution continues to work even after your edit. It works regardless of the number of elements or the number of cores per element. You just need to make sure things are named consistently, in the form "{element}_{core}".

library(tidyverse)
df %>% 
  mutate(id = 1:n()) %>% 
  pivot_longer(-id) %>% 
  filter(!is.na(value)) %>% 
  mutate(variable = str_extract(name, "^[^_]+")) %>% 
  group_by(id, variable) %>% 
  # Arrange by name (e.g. A_1) so that we could select the first non-NA
  arrange(name) %>% 
  summarise(value = value[1]) %>% 
  pivot_wider(names_from = "variable")

Output

# A tibble: 5 x 3
     id     A     B
  <int> <dbl> <dbl>
1     1     1    11
2     2     2    12
3     3     3    13
4     4     4    14
5     5     5    15

Solution 5:^[5]

I have asked for it here: https://github.com/tidyverse/dplyr/issues/6109 where there are some possible solutions. For example

library(dplyr)
library(purrr)
df %>% 
    transmute(map2_dfc(.x = across(ends_with("_1"), .names = '{sub("_1","",.col)}'), 
                    .y = across(ends_with("_2")), 
                    .f = coalesce))
  A  B
1 1 11
2 2 12
3 3 13
4 4 14
5 5 15

Or also using the function

coalesce_prefix <- function(prefix) {
  exprs <- map(prefix, function(p) {
    expr(coalesce(
      !!sym(paste0(p, ".x")),
      !!sym(paste0(p, ".y"))
    ))
  })
  names(exprs) <- prefix
  exprs
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	ThomasIsCoding
Solution 3	TarJae
Solution 4
Solution 5	iago

'Coalesce pairs of variables within a dataframe based on a regular expression

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]