Combine 3 columns into 2 columns and omit NAs

Question

I am trying to take 3 columns and combine them into 2 columns. For each row, only two of the three columns have data (and the other is NA).

My first thought was to use coalesce but I can't get it to work for my case.

tibble(
  col1 = c(NA, 1, 0),
  col2 = c(1, NA, 1),
  col3 = c(0, 1, NA)
)

# A tibble: 3 × 3
  col1 col2 col3
   <dbl>  <dbl>  <dbl>
1     NA      1      0
2      1     NA      1
3      0      1     NA

My desired output would be like this:

tibble(
  col1 = c(NA, 1, 0),
  col2 = c(1, NA, 1),
  col3 = c(0, 1, NA)
  out1 = c(1, 1, 0),
  out2 = c(0, 1, 1)
)

# A tibble: 3 × 5
  col1 col2 col3 test1 test2
   <dbl>  <dbl>  <dbl> <dbl> <dbl>
1     NA      1      0     1     0
2      1     NA      1     1     1
3      0      1     NA     0     1

See also: [Shifting non-NA cells to the left](https://stackoverflow.com/questions/23285215/shifting-non-na-cells-to-the-left) — Henrik, Jul 13 '22 at 21:31

score 4 · Accepted Answer · answered Jul 07 '22 at 19:39

One quick way:

df %>%
  rowwise() %>%
  mutate(test=list(na.omit(c_across(everything()))))%>%
  unnest_wider(test, names_sep = '')

  col1  col2  col3 test1 test2
  <dbl> <dbl> <dbl> <dbl> <dbl>
1    NA     1     0     1     0
2     1    NA     1     1     1
3     0     1    NA     0     1

in Base R:

cbind(df, test=t(unstack(na.omit(stack(data.frame(t(df)))))))
   col1 col2 col3 test.1 test.2
X1   NA    1    0      1      0
X2    1   NA    1      1      1
X3    0    1   NA      0      1

score 2 · Answer 2 · answered Jul 07 '22 at 20:09

2

Here's another way to achieve this in base:

cbind(dat1, as.data.frame(t(matrix(as.vector(na.omit(unlist(dat1))), nrow = 2))))

#>   col1 col2 col3 V1 V2
#> 1   NA    1    0  1  0
#> 2    1   NA    1  1  1
#> 3    0    1   NA  0  1

answered Jul 07 '22 at 20:09

M--

25,431
8
61
93

score 2 · Answer 3 · answered Jul 07 '22 at 23:05

Here is another tidyverse solution: We could use first unite and then separate:

library(dplyr)
library(tidyr)

df %>% 
  unite(newcol, everything(), na.rm = TRUE, remove = FALSE) %>% 
  separate(newcol, into = c("test1", "test2"))

  test1 test2  col1  col2  col3
  <chr> <chr> <dbl> <dbl> <dbl>
1 1     0        NA     1     0
2 1     1         1    NA     1
3 0     1         0     1    NA

PaulS · Answer 4 · 2022-07-07T20:07:06.297

1

Another possible solution:

library(tidyverse)

df %>% 
  mutate(apply(df, 1, \(x) na.omit(x)) %>% t %>% as.data.frame %>% 
    set_names(str_c("test", 1:ncol(.))))

#> # A tibble: 3 × 5
#>    col1  col2  col3 test1 test2
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1    NA     1     0     1     0
#> 2     1    NA     1     1     1
#> 3     0     1    NA     0     1

edited Jul 07 '22 at 20:07

answered Jul 07 '22 at 19:52

PaulS

21,159
2
9
26

Combine 3 columns into 2 columns and omit NAs

4 Answers4