Combine 3 columns into 2 columns and omit NAs-CodePudding

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
  coder1 coder2 coder3
   <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
  coder1 coder2 coder3 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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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