Combining several pairs of columns (containing numbers and NA's) simultaneously in R

Question

I'm trying to determine how to combine columns efficiently. I've started with a dataframe that looks somewhat like the following. The variable names do not follow any specific pattern, and the columns I am trying to combine are not necessarily next to each other. I've included the column numbers to make it easier to refer to them.

Imagine I'm trying to combine columns 2 and 3, columns 4 and 7, and columns 5 and 6. As you can see, if there is a number in one of the columns that's being combined, then the corresponding column has an NA. If column 8 == a, column 2 is a number and column 3 is NA. If column 8 == b, column 2 is NA and column 3 is a number. A similar pattern follows for columns 9 (which maps onto 4 and 7) and 10 (which maps onto 5 and 6).

1     2      3      4     5     6     7     8     9     10

id    ab_1   ab2_1  dc_3  de_4  ze37  uh44  fac1  fac2  fac3
1     2      NA     NA    4     NA    5     a     c     e
2     NA     4      NA    NA    1     3     b     c     f
3     NA     7      2     5     NA    NA    b     d     e
4     5      NA     3     NA    7     NA    a     d     f

I am trying to generate 3 new columns: one with combined values for 2 and 3, one with combined values for 4 and 7, and one with combined values for 5 and 6. I would like them added to the end of the dataframe above, and I do not care if the original columns being combined remain in the dataframe. This is what the additional 3 columns should look like:

col1  col2  col3
2     5     4
4     3     1
7     2     5
5     3     7

This is how I have been doing this so far:

df <- df %>%    ## combining columns 2 and 3
      gather(., 'ab_1', 'ab2_1', key = "key", value = "col1") %>%
      filter(., fac1 == "a" & key == "ab1_1" | fac1 == "b" & key == "ab2_1")

df <- df %>%    ## combining columns 4 and 7
      gather(., 'dc_3', 'uh44', key = "key2", value = "col2") %>%
      filter(., fac2 == "c" & key2 == "uh44" | 
                fac2 == "d" & key2 == "dc_3")

df <- df %>%    ## combining columns 5 and 6
      gather(., 'de_4', 'ze37', key = "key3", value = "col3") %>%
      filter(., fac3 == "e" & key == "de_4" | fac3 == "f" & key == "ze37")

Is there a way to combine these so that I don't have to manually repeat the same functions to make each additional column? There are several more columns I need to combine, so I'm hoping there is a more efficient way of doing this. Please let me know if I can clarify anything.

score 2 · Answer 1 · answered Sep 13 '18 at 22:54

Perhaps something like this using dplyr::coalesce?

# Define the pairs
prs <- list(col1 = c(2, 3), col2 = c(4, 7), col3 = c(5, 6))

library(tidyverse)
imap_dfc(prs, ~df[, .x] %>% transmute(!!.y := coalesce(!!!syms(names(df)[.x]))))
#  col1 col2 col3
#1    2    5    4
#2    4    3    1
#3    7    2    5
#4    5    3    7

Sample data

df <- read.table(text =
    "id    ab_1   ab2_1  dc_3  de_4  ze37  uh44  fac1  fac2  fac3
1     2      NA     NA    4     NA    5     a     c     e
2     NA     4      NA    NA    1     3     b     c     f
3     NA     7      2     5     NA    NA    b     d     e
4     5      NA     3     NA    7     NA    a     d     f ", header = T)

score 0 · Answer 2 · answered Sep 13 '18 at 23:02

This is much more verbose than Maurits' solution, but it gets to the same place:

library(tidyverse)
col_grps <- tibble(col = colnames(df),
                   group = c(NA, 1, 1, 2, 3, 3, 2, NA, NA, NA))

output <- df %>%
  gather(col, value, -id) %>%
  left_join(col_grps) %>%
  mutate(value = value %>% as.numeric) %>%
  group_by(id, group) %>%
  summarise(sums = sum(value, na.rm = TRUE)) %>% ungroup() %>%
  spread(group, sums) %>%
  select(-id, -`<NA>`)

output
# A tibble: 4 x 3
    `1`   `2`   `3`
  <dbl> <dbl> <dbl>
1     2     5     4
2     4     3     1
3     7     2     5
4     5     3     7

Combining several pairs of columns (containing numbers and NA's) simultaneously in R

2 Answers2

Sample data