How to create new dataframe column with a character that comes from three other columns but only if present in a list, in R

Question

I've been trying to do this with mutate(), str_detect(), but it's too complex and dont know how to do all the steps, please help!

I have a dataframe in which 3 cols contain let's say fruits, animals, or "none".

col1	col2	col3
apple	cat	none
apple	dog	none
pear	none	none
pear	apple	none
none	none	none

And then I have two lists:

fruit <- c("apple", "pear", banana") animal <- c("cat", "dog", "sheep")

I want to create two new columns in the dataframe: col4 should display only fruits from col1, col2, col3. If more than one fruit, I need them separated by commas. col5 does the same but for animals. If col1, col2, col3 dont contain an animal or a fruit, I need col4 and col5 to say "none".

col1	col2	col3	col4	col5
apple	cat	none	apple	cat
cat	dog	none	none	cat, dog
pear	none	none	pear	none
pear	apple	none	pear, apple	none
none	none	none	none	none

akrun · Answer 1 · 2023-02-05T01:52:57.310

Here is one method

library(dplyr)
library(purrr)
imap_dfc(list(col4 = fruit, col5 = animal), \(x, y) 
    df1 %>% 
     mutate(across(everything(), ~ replace(.x, !.x %in% x, NA_character_))) %>%
     unite(coln, everything(), sep = ", ", na.rm = TRUE) %>%
     setNames(y)) %>%
     mutate(across(everything(), 
       ~ case_when(.x == "" ~ "none", TRUE ~ .x))) %>%     
    bind_cols(df1, .)

-output

  col1  col2 col3        col4     col5
1 apple   cat none       apple      cat
2   cat   dog none             cat, dog
3  pear  none none        pear         
4  pear apple none pear, apple         
5  none  none none

Or using pmap

library(tidyr)
df1 %>%
    mutate(col = pmap(across(everything()), ~ {
    v1 <- c(...)
   tibble(`4` = toString(v1[v1 %in% fruit]), 
        `5` = toString(v1[v1 %in% animal]))})) %>%
   unnest_wider(col, names_sep = "") %>%
   mutate(across(everything(), ~ case_when(.x == "" ~ "none", TRUE ~ .x)))

-output

# A tibble: 5 × 5
  col1  col2  col3  col4        col5    
  <chr> <chr> <chr> <chr>       <chr>   
1 apple cat   none  apple       cat     
2 cat   dog   none  none        cat, dog
3 pear  none  none  pear        none    
4 pear  apple none  pear, apple none    
5 none  none  none  none        none

data

df1 <- structure(list(col1 = c("apple", "cat", "pear", "pear", "none"
), col2 = c("cat", "dog", "none", "apple", "none"), col3 = c("none", 
"none", "none", "none", "none")), class = "data.frame", 
row.names = c(NA, 
-5L))

kinda worked, but I needed col4 and col5 to say "none" if no animals and fruits :) — Pame, Feb 05 '23 at 01:51
@Pame It is a bad practise to use `"none"`. I would use `NA` as it can be easily removed — akrun, Feb 05 '23 at 01:53

score 0 · Accepted Answer · answered Feb 05 '23 at 01:37

This gives you what you want:

df <- data.frame(col1=c("apple", "cat", "pear", "pear","none"),
                 col2=c("cat", "dog", "none", "apple", "none"),
                 col3 = c("none", "none", "none", "none", "none"))

fruit <- c("apple", "pear", "banana")
animal <- c("cat", "dog", "sheep")

df %>% mutate(col4 = apply(.,1, function(x) paste0(x[x != "none" & x %in% fruit], collapse = ",")),
              col5 = apply(.,1, function(x) paste0(x[x != "none" & x %in% animal], collapse = ",")),
              across(.cols = c("col4", "col5"), .fns = ~replace(.x, .x == "", "none")))

output:

  col1  col2 col3       col4    col5
1 apple   cat none      apple     cat
2   cat   dog none       none cat,dog
3  pear  none none       pear    none
4  pear apple none pear,apple    none
5  none  none none       none    none

``

Thanks! it worked... I have so much to study :D – Pame Feb 05 '23 at 01:46 — Pame, Feb 05 '23 at 01:46

score 0 · Answer 3 · answered Feb 05 '23 at 01:42

Please check the below code with ifelse,
to make the code work we need to update the vectors fruit and animal with none

library(tidyverse)

fruit <- c("apple", "pear", "banana",'none') 
animal <- c("cat", "dog", "sheep",'none')

df %>% 
mutate(col4=ifelse(!(col2 %in% animal), paste0(col1,',',col2), ifelse(col1 %in% fruit, col1, 'none')),
       col5=ifelse(!(col1 %in% fruit), paste0(col1,',',col2), ifelse(col2 %in% animal, col2, 'none'))
                          )

^{Created on 2023-02-04 with reprex v2.0.2}

   col1  col2 col3       col4    col5
1 apple   cat none      apple     cat
2   cat   dog none       none cat,dog
3  pear  none none       pear    none
4  pear apple none pear,apple    none
5  none  none none       none    none

score 0 · Answer 4 · answered Feb 05 '23 at 01:56

You can also do it by creating base R function. For col4 you can write:

fruit <- c("apple", "pear", "banana") ; animal <- c("cat", "dog", "sheep")

df$col4 <- apply(df, 1, function(x) {
fruits <- x[x %in% fruit]
if (length(fruits) > 0) {
    paste(fruits, collapse=", ")
} else {
    "none"
  }
})

and for col5 the function is:

df$col5 <- apply(df, 1, function(x) {
animals <- x[x %in% animal]
if (length(animals) > 0) {
paste(animals, collapse=", ")
} else {
"none"
 }
})

How to create new dataframe column with a character that comes from three other columns but only if present in a list, in R

4 Answers4

data