If elements are different, collapse

user_11518133 0
Viewed 39

I have two columns that I want to collapse if they are different. If they are the same, just keep the value. If there is one NA, keep the other one.

df
#   element1 element2
#1        A         
#2        B        B
#3        C        D
#4        A     <NA>

And I need the following column:

#  element1 element2 element12
#1        A                  A
#2        B        B         B
#3        C        D       C,D
#4        A     <NA>         A

Any hint on how can I do that?

Data:

df = data.frame(element1 = c("A", "B",  "C","A"),
                 element2 = c("", "B","D", NA));df
collapse dataframe r database
4 Answers

Try this approach with apply():

#Code 1
df$Var <- apply(df[1:2],1,function(x) trimws(paste0(unique(x[!is.na(x)]),collapse = ','),
                                             whitespace = ','))

Output:

  element1 element2 Var
1        A            A
2        B        B   B
3        C        D C,D
4        A     <NA>   A

Or the dplyr version:

library(dplyr)
#Code
df %>% mutate(across(everything(),~as.character(.))) %>%
  replace(is.na(.),',') %>%
  rowwise() %>%
  mutate(Var=trimws(paste0(unique(c_across(element1:element2)),
                                              collapse = ','),whitespace = ',')) %>%
  replace(.==',',NA)

Output:

# A tibble: 4 x 3
# Rowwise: 
  element1 element2 Var  
  <chr>    <chr>    <chr>
1 A        ""       A    
2 B        "B"      B    
3 C        "D"      C,D  
4 A         NA      A
user_20808480

Does this work:

> df %>% mutate(element3 = case_when(
There were 14 warnings (use warnings() to see them)
+                                   element1 == element2 ~ element1,
+                                   is.na(element1) & !is.na(element2) ~ element2,
+                                   is.na(element2) & !is.na(element1) ~ element1,
+                                   element1 == '' & element2 != '' ~ element2,
+                                   element1 != '' & element2 == '' ~ element1,
+                                   element1 != element2 ~ paste(element1, element2, sep = ',')
+ ))
  element1 element2 element3
1        A                 A
2        B        B        B
3        C        D      C,D
4        A     <NA>        A
>
user_107227520

Here is another tidyverse option.

If you want to get rid of annoying empty strings "" and make them NA, you can use na_if.

A case_when can concatenate the strings if they are different, with coalesce used in case one or other is missing (NA).

If neither are the case, then both columns are the same, and just set as first value.

library(tidyverse)

df %>%
  na_if("") %>%
  mutate(element12 = case_when(
    element1 != element2 ~ paste(element1, element2, sep = ','),
    is.na(element1) | is.na(element2) ~ coalesce(element1, element2),
    TRUE ~ element1))

Output

  element1 element2 element12
1        A     <NA>         A
2        B        B         B
3        C        D       C,D
4        A     <NA>         A
user_34606700

In base R, we can do this with paste and sub

df$elements12 <- gsub("(?<=.)(?=.)", ",", sub("(.)\\1+", "\\1", 
     do.call(paste, c(replace(df, is.na(df), ""), sep=""))), perl = TRUE)

-output

df$element12
#[1] "A"   "B"   "C,D" "A"
user_37322710
Related