combine several variables into one

[sunshine]

Hello All,
In my dataset there are eight different variables that all measure the same thing and also have the same coding. They are just divided into different variables because the survey was done in different languages and therefore not everyone answered the same question. If the question was not answered, it says NA. I would now like to merge these eight variables into one and delete the NAs (as everyone has answerd the question, but not in the same language). Can anyone help me with this?

[mattwarkentin]

Hi @sunshine,

There are a couple ways you could go about this, but I think this is a good use-case for the dplyr::coalesce() function, which will take the first non-missing value from a set of columns/variables (working left-to-right). For example:

library(tidyverse)

df <- 
  tribble(
    ~q1, ~q2, ~q3, ~q4, ~q5, ~q6, ~q7, ~q8,
    'yes', NA, NA, NA, NA, NA, NA, NA,
    NA, NA, NA, 'oui', NA, NA, NA, NA,
    NA, NA, NA, NA, NA, NA, 'ja', NA,
    NA, NA, 'sim', NA, NA, NA, NA, NA
    
  )
df
#> # A tibble: 4 × 8
#>   q1    q2    q3    q4    q5    q6    q7    q8   
#>   <chr> <lgl> <chr> <chr> <lgl> <lgl> <chr> <lgl>
#> 1 yes   NA    <NA>  <NA>  NA    NA    <NA>  NA   
#> 2 <NA>  NA    <NA>  oui   NA    NA    <NA>  NA   
#> 3 <NA>  NA    <NA>  <NA>  NA    NA    ja    NA   
#> 4 <NA>  NA    sim   <NA>  NA    NA    <NA>  NA

df %>% 
  mutate(
    q_all = coalesce(q1, q2, q3, q4, q5, q6, q7, q8)
  )
#> # A tibble: 4 × 9
#>   q1    q2    q3    q4    q5    q6    q7    q8    q_all
#>   <chr> <lgl> <chr> <chr> <lgl> <lgl> <chr> <lgl> <chr>
#> 1 yes   NA    <NA>  <NA>  NA    NA    <NA>  NA    yes  
#> 2 <NA>  NA    <NA>  oui   NA    NA    <NA>  NA    oui  
#> 3 <NA>  NA    <NA>  <NA>  NA    NA    ja    NA    ja   
#> 4 <NA>  NA    sim   <NA>  NA    NA    <NA>  NA    sim