grepl across multiple, specified columns

Question

I want to create a new column in my data frame that is either TRUE or FALSE depending on whether a term occurs in two specified columns. This is some example data:

AB <- c('CHINAS PARTY CONGRESS','JAPAN-US RELATIONS','JAPAN TRIES TO')
TI <- c('AMERICAN FOREIGN POLICY', 'CHINESE ATTEMPTS TO', 'BRITAIN HAS TEA')
AU <- c('AUTHOR 1', 'AUTHOR 2','AUTHOR 3')
M  <- data.frame(AB,TI,AU)

I can do it for one column, or the other, but I cannot figure out how to do it for both. In other words, I don't know how to combine these two lines that would not mutually overwrite each other.

M$China <- mapply(grepl, "CHINA|CHINESE|SINO", x=M$AB)
M$China <- mapply(grepl, "CHINA|CHINESE|SINO", x=M$TI)

It is important that I specify the columns, I cannot choose the whole data.frame.I have looked for other similar questions, but none seemed to apply to my case and I haven't been able to adapt any existing examples. This is what would make sense to me:

M$China <- mapply(grepl, "CHINA|CHINESE|SINO", x=(M$AB|M$TI)

I am not exactly sure what Map does, but it gives this output $`CHINA|CHINESE|SINO` [1] TRUE FALSE FALSE $ [1] FALSE TRUE FALSE $ [1] FALSE FALSE FALSE. It seems to run over the whole dataframe, also it does not add the new column. — SCW, Dec 22 '17 at 12:44
It is in a `list` format. You can convert it to data.frame by wrapping `data.frame` or assign it to the columns `M[] <- Map(grepl, "CHINA|CHINESE|SINO", M)` — akrun, Dec 22 '17 at 12:45
In that case it overwrites the values, which shouldn't happen. — SCW, Dec 22 '17 at 12:48

Jaap · Accepted Answer · 2017-12-22T12:55:05.213

7

Using:

M$China <- !!rowSums(sapply(M[1:2], grepl, pattern = "CHINA|CHINESE|SINO"))

gives:

> M
                     AB                      TI       AU China
1 CHINAS PARTY CONGRESS AMERICAN FOREIGN POLICY AUTHOR 1  TRUE
2    JAPAN-US RELATIONS     CHINESE ATTEMPTS TO AUTHOR 2  TRUE
3        JAPAN TRIES TO         BRITAIN HAS TEA AUTHOR 3 FALSE

What this does:

sapply(M[1:2], grepl, pattern = "CHINA|CHINESE|SINO") loops over the two AB and TI columns and looks whether one of the parts of the pattern ("CHINA|CHINESE|SINO") is present.

The sapply-call returns a matrix of TRUE/FALSE values:

        AB    TI
[1,]  TRUE FALSE
[2,] FALSE  TRUE
[3,] FALSE FALSE

With rowSums you check how many TRUE-values each row has.
By adding !! in front ofrowSums you convert all values from the rowSums-call higher than zero to TRUE and all eros to FALSE.

edited Dec 22 '17 at 12:55

answered Dec 22 '17 at 12:45

Jaap

81,064
34
182
193

YES, that works! Don't completely understand it, but I guess in M[1:2] I can specify any column number I want, also in my own data.frame, no? – SCW Dec 22 '17 at 12:47
@SCW `1:2` specifies indeed the column positions – Jaap Dec 22 '17 at 12:48
Thank you so much! – SCW Dec 22 '17 at 12:49
@SCW added now an explanation as well – Jaap Dec 22 '17 at 12:55

score 3 · Answer 2 · answered Dec 22 '17 at 12:50

If we need to collapse to a single vector, use the Map to loop through the columns, apply the pattern to get a list of logical vector, then Reduce it to a logical vector using |

M$China <- Reduce(`|`, Map(grepl, "CHINA|CHINESE|SINO", M))
M
#                     AB                      TI       AU China
#1 CHINAS PARTY CONGRESS AMERICAN FOREIGN POLICY AUTHOR 1  TRUE
#2    JAPAN-US RELATIONS     CHINESE ATTEMPTS TO AUTHOR 2  TRUE
#3        JAPAN TRIES TO         BRITAIN HAS TEA AUTHOR 3 FALSE

Or using the same methodology in tidyverse

library(tidyverse)
M %>%
   mutate_all(funs(str_detect(., "CHINA|CHINESE|SINO")))  %>% 
   reduce(`|`) %>%
   mutate(M, China = .)

grepl across multiple, specified columns

2 Answers2

Linked