r remove keywords in a column

Question

I have a column in my dataframe with words like this.

ColA
2-4 Model
Group1
Group ACH
Group2
Phenols
Group1
Group ACH
Group2
MONO MHPP
Group1
Group ACH
Group2

I want to create two additional columns like this: 1) without keywords c("Group1", "Group (ACH)", "Group2") and 2) a second column that retains only those bag of words.

ColA          ColB        ColC
2-4 Model     2-4 Model   
Group1                    Group1
Group (ACH)               Group (ACH) 
Group2                    Group2
Phenols      Phenols 
Group1                    Group1
Group (ACH)               Group (ACH)
Group2                    Group2
MONO MHPP    MONO MHPP
Group1                    Group1
Group (ACH)               Group (ACH)
Group2                    Group2

I tried gsub and str_replace but no results. So any suggestion is much appreciated.

AndrewGB · Answer 1 · 2022-01-08T19:37:44.120

Here is another option using tidyverse. First, I create a new column (ColB) using str_remove to remove any of the keywords. Then, I use str_extract_all to pull out only the keywords to create another new column (ColC).

library(tidyverse)

val <- c('Group1', 'Group ACH', 'Group2')

df %>%
  mutate(ColB = str_remove(ColA, paste(val, collapse = "|")),
         ColC = str_extract_all(ColA, paste(val, collapse = "|")))

Output

        ColA      ColB      ColC
1  2-4 Model 2-4 Model          
2     Group1              Group1
3  Group ACH           Group ACH
4     Group2              Group2
5    Phenols   Phenols          
6     Group1              Group1
7  Group ACH           Group ACH
8     Group2              Group2
9  MONO MHPP MONO MHPP          
10    Group1              Group1
11 Group ACH           Group ACH
12    Group2              Group2

score 3 · Answer 2 · answered Jan 08 '22 at 10:12

Here is an alternative approach: This is possible because your data have a clear pattern:

How it works:

Create groups with 4 rows each
Then combine dyplr with base R subsetting for each column what you need( in your case ColB is always the first of each group and ColC is row 2:4 in each group:

library(dplyr)  
df %>% 
  group_by(group = rep(row_number(), each=4, length.out = n())) %>% 
  mutate(ColB = c(ColA[1], rep(NA, 3)), ColC= c(NA, ColA[2:4]))

   ColA      group ColB      ColC     
   <chr>     <int> <chr>     <chr>    
 1 2-4 Model     1 2-4 Model NA       
 2 Group1        1 NA        Group1   
 3 Group ACH     1 NA        Group ACH
 4 Group2        1 NA        Group2   
 5 Phenols       2 Phenols   NA       
 6 Group1        2 NA        Group1   
 7 Group ACH     2 NA        Group ACH
 8 Group2        2 NA        Group2   
 9 MONO MHPP     3 MONO MHPP NA       
10 Group1        3 NA        Group1   
11 Group ACH     3 NA        Group ACH
12 Group2        3 NA        Group2

score 2 · Accepted Answer · answered Jan 08 '22 at 06:18

You may use an ifelse -

val <- c('Group1', 'Group ACH', 'Group2')
df <- transform(df, ColB = ifelse(ColA %in% val, '', ColA), 
                    ColC = ifelse(ColA %in% val, ColA, ''))
df

#        ColA      ColB      ColC
#1  2-4 Model 2-4 Model          
#2     Group1              Group1
#3  Group ACH           Group ACH
#4     Group2              Group2
#5    Phenols   Phenols          
#6     Group1              Group1
#7  Group ACH           Group ACH
#8     Group2              Group2
#9  MONO MHPP MONO MHPP          
#10    Group1              Group1
#11 Group ACH           Group ACH
#12    Group2              Group2

If in general, you want to check for all "Group" values you may use grepl instead of mentioning all of them in val.

df <- transform(df, ColB = ifelse(grepl('Group', ColA), '', ColA), 
                    ColC = ifelse(grepl('Group', ColA), ColA, ''))

r remove keywords in a column

3 Answers3