How to join multiple columns together on blanks of one column in R-CodePudding

This is my dataframe:

df <- data.frame(option_1 = c("Box 1", "", ""), option_2 = c("", 4, ""), Width = c("","",3))

I want to get this data frame:

  option_1
1    Box 1
2        4
3        3

I'm doing this on a much bigger dataframe with 5 columns I'm merging on blanks with respect to the option_1 column. I have tried using coalesce, but some of the columns won't "merge" on the blanks. For example:

df %>%
mutate(option_value_1 = coalesce(option_value_1, option_value_2, option_value_3, option_value_4, option_value_5, option_value_6, option_value_7))

option_value_5 wouldn't come together with option_value_1 on the blanks, but the other option values did. Should I put the vectors in a list then use coalesce?

CodePudding user response：

We convert the blank ("") to NA and coalesce with the bang-bang (!!!) operator. According to ?"!!!"

The big-bang operator !!! forces-splice a list of objects. The elements of the list are spliced in place, meaning that they each become one single argument.

library(dplyr)
df %>% 
  na_if("") %>%
  transmute(option_1 = coalesce(!!! .))

-output

  option_1
1    Box 1
2        4
3        3

If we are interested only in the 'option' columns, subset the columns (also can use invoke with coalesce

library(purrr)
df %>% 
  na_if("") %>%
   mutate(option_1 = invoke(coalesce, 
        across(starts_with("option"))), .keep = "unused")

CodePudding user response：

With a base R approach:

df <- data.frame(option_1 = apply(df, 1, \(x) paste(x, collapse = "")))

df

#>   option_1
#> 1    Box 1
#> 2        4
#> 3        3

Or using tidyverse:

df %>% 
  rowwise %>% 
  transmute(option_1 = str_c(c_across(everything()), collapse = "")) %>% 
  ungroup