Filtering by multiple columns with specific string then mutate in one column in R

Question

data <- data.frame(
  "grammer" = c("Python","C","Java","GO",NA,"SQL"),
  "score" = c(1,2,NA,4,7,10),
  "file" = c("GHG.txt", "GXG.ect", NA , "VAC.ect", "GBA.ect", "GHG.txt"),
  "file2" = c("GHG.txt", "GXG.dat", "AGG.ect", "VAC.txt", "GBA.dat", "GHG.dat"),
  "file3" = c("GHG.dat", "GXG.txt", "AGG.dat", "VAC.dat", "GBA.txt", NA )
)

I want to get suffix .ect from columns (file, file2, file3) and mutate in new column.

Following is my code. I want change to filter simultaneously.

d1 <- data %>% 
  filter(str_detect(file, ".ect")) %>%
  mutate(sub("\\..*", "", file))

d2 <- data %>% 
  filter(str_detect(file2, ".ect")) %>%
  mutate(sub("\\..*", "", file2))

d3 <- data %>% 
  filter(str_detect(file3, ".ect")) %>%
  mutate(sub("\\..*", "", file3))

Here is my expected outcome :

Please share how exactly your expected outcome should look like. — deschen
– deschen, Commented Aug 31, 2022 at 5:03
Your example in the code doesn't match your screenshot, e.g. in row 1 there is no "GHG.ect" in either of the files in your data. — deschen
– deschen, Commented Aug 31, 2022 at 6:24

deschen · Accepted Answer · 2022-08-31 06:27:41Z

2

We can use across:

library(tidyverse)
data |> 
  mutate(new = apply(across(c(file, file2, file3)), 1, function(x) str_remove(str_subset(c(x), ".ect"), ".ect$")))

which gives:

  grammer score    file   file2   file3 new
1  Python     1 GHG.txt GHG.txt GHG.dat    
2       C     2 GXG.ect GXG.dat GXG.txt GXG
3    Java    NA    <NA> AGG.ect AGG.dat AGG
4      GO     4 VAC.ect VAC.txt VAC.dat VAC
5    <NA>     7 GBA.ect GBA.dat GBA.txt GBA
6     SQL    10 GHG.txt GHG.dat    <NA>

Note, if there is no match, this solution just returns an empty character value, i.e. "", whereas the screenshot from TO wants a missing value NA. This can be achieved by a simple ifelse recode, so not adding it to the code here.

edited Aug 31, 2022 at 6:27

answered Aug 31, 2022 at 5:02

deschen

11.6k5 gold badges32 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

deschen Over a year ago

@FenNei if it solves your problem, please consider flagging this as your accepted answer.

Ollie Over a year ago

@ deschen ! OK!!!

Collectives™ on Stack Overflow

Filtering by multiple columns with specific string then mutate in one column in R

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related