How can I combine pairs of variables using suffixes in R?

Question

I have a very wide dataset (1,000+ columns) about 160 of which are pairs in the following format: Var1.r and Var1.s; Var2.r and Var2.s, and so on.

Here is a small example of what the data look like now:

df <- tibble(Var1.r=c("Apple", "Pear", NA), Var1.s = c(NA, NA, "Dog"), 
             Var2.r = c("Boat", NA, NA), Var2.s = c(NA, "Platypus", NA),
             AnotherVar = c(1,2,3))

# A tibble: 3 x 5
  Var1.r Var1.s Var2.r Var2.s   AnotherVar
  <chr>  <chr>  <chr>  <chr>         <dbl>
1 Apple  NA     Boat   NA                1
2 Pear   NA     NA     Platypus          2
3 NA     Dog    NA     NA                3

And what I would like it to look like:

> df2
# A tibble: 3 x 3
  Var1  Var2     AnotherVar
  <chr> <chr>         <dbl>
1 Apple Boat              1
2 Pear  Platypus          2
3 Dog   NA                3

I wrote a function to merge each pair of columns merge_columns which takes two columns as arguments and returns the desired merged column. Normally I would do something like:

df2 <- df %>% 
  mutate(Var1 = merge_cols(Var1.r, Var1.s),
         Var2 = merge_cols(Var2.r, Var2.s))

and then drop all the .r and .s columns. Except I don't want to write that same line 80 times.

There must be a better way, right?

UPDATE: I ended up going with an ugly but workable solution.

# select all the ".s" columns 
# (which will always have their .r counterparts)
to_merge <- df %>% select(ends_with(".s")) %>% names()

S <- NA
# loop through all the .s column names
for (S in to_merge) { 
  R <- gsub('(.+).s', '\\1.r', S) #create the equivalent .r col name
  # merge them using merge_cols() and save them to the .r column 
  df[R] <- merge_cols(df[[S]],df[[R]])
}

# drop all the .s columns
df <- df %>% select(-ends_with(".s"))
# rename the variables that end in .r to be the "main" variable
names(df) <- gsub('(.+).r$', '\\1', names(df))

It's super ugly but it works faster than reshaping the dataframe (because I have way too many columns but not that many rows) and allows me to use a custom merge_cols function depending on how I want to merge the data.

Could you specify an example of the input you have and the expected output? — iago
I've added better examples and more details. Thank you for your patience! :) — Julia B.

teofil teofil · Accepted Answer · 2019-09-19T20:11:40

You should be able to do this by converting your data frame into long format, then parsing the column names, followed by removing missing values. For example:

library(dplyr)
library(tidyr)

df <-
  tibble(
    Var1.r = c("Apple", "Pear", NA),
    Var1.s = c(NA, NA, "Dog"),
    Var2.r = c("Boat", NA, NA),
    Var2.s = c(NA, "Platypus", NA),
    AnotherVar = c(1, 2, 3)
  )

df %>% gather(Var, Val, -AnotherVar) %>% 
  separate(Var, into=c("Name", "Suffix"), sep="\\.") %>% 
  drop_na(Val) %>% 
  select(-Suffix) %>% 
  spread(Name, Val)

# A tibble: 3 x 3
  AnotherVar Var1  Var2    
       <dbl> <chr> <chr>   
1          1 Apple Boat    
2          2 Pear  Platypus
3          3 Dog   NA

Or a bit more general, capture the variables you want to gather with starts_with assuming they all start with Var:

df <-
  tibble(
    Var1.r = c("Apple", "Pear", NA),
    Var1.s = c(NA, NA, "Dog"),
    Var2.r = c("Boat", NA, NA),
    Var2.s = c(NA, "Platypus", NA),
    AnotherVar = c(1, 2, 3),
    AnotherVar2 = c("a", NA, "c"),
    AnotherVar3 = c("a1", "b2", NA)
  )

df %>% gather(Var, Val, starts_with("Var")) %>% 
  separate(Var, into=c("Name", "Suffix"), sep="\\.") %>% 
  drop_na(Val) %>% 
  select(-Suffix) %>% 
  spread(Name, Val)

# A tibble: 3 x 5
  AnotherVar AnotherVar2 AnotherVar3 Var1  Var2    
       <dbl> <chr>       <chr>       <chr> <chr>   
1          1 a           a1          Apple Boat    
2          2 NA          b2          Pear  Platypus
3          3 c           NA          Dog   NA

How can I combine pairs of variables using suffixes in R?

1 Answers