Select rows based on values in various columns in R

Question

Three columns of my data.frame contain subjects. I want to subset this data.frame for different subjects. E.g. if I want to have a data.frame with the subject "apple", the row should be selected if the word "apple" appears in one of the three columns.

doc    <- c("blabla1", "blabla2", "blabla3", "blabla4")
subj.1 <- c("apple", "prune", "coconut", "berry")
subj.2 <- c("coconut", "apple", "cherry", "banana and prune")
subj.3 <- c("berry", "banana", "apple and berry", "pear", "prune")
subjects <- c("apple", "prune", "coconut", "berry", "cherry", "pear", "banana")

mydf <- data.frame(doc, subj.1, subj.2, subj.3, stringsAsFactors=FALSE) 
mydf

#       doc   subj.1            subj.2             subj.3
# 1 blabla1    apple           coconut              berry
# 2 blabla2    prune             apple             banana
# 3 blabla3  coconut            cherry    apple and berry
# 4 blabla4    berry  banana and prune               pear

the output for subject "apple" should look like this:

#       doc   subj.1            subj.2             subj.3
# 1 blabla1    apple           coconut              berry
# 2 blabla2    prune             apple             banana
# 3 blabla3  coconut            cherry    apple and berry

EDIT1: In addition, let's say i have about 200 different subjects and therefor I want 200 different data.frames. How could I do that?

I tried a loop approach:

mylist <- vector('list', length(subjects))

for(i in 1:length(subjects)) {
pattern <- subjects[i]
filter <- grepl(pattern, ignore.case=T, mydf$subj.1)
      grepl(pattern, ignore.case=T, mydf$subj.2)
      grepl(pattern, ignore.case=T, mydf$subj.3)
    subDF <- panel[filter,] 

mylist[[i]] <- subDF
  }

but there's the error:

Error in grepl(pattern, ignore.case = T, panel$SUBJECT.1) : 
 invalid regular expression 'C++ PROGRAMMING', reason 'Invalid use of repetition operators'

EDIT2: oh I see, in the original data.frame, one of the subjects is "C++ PROGRAMMING". Might that "++" cause the error?

digEmAll · Accepted Answer · 2014-05-08 07:13:10Z

2

You can use grepl function :

pattern <- 'apple'
filter <- grepl(pattern, ignore.case=T, mydf$subj.1) | 
          grepl(pattern, ignore.case=T, mydf$subj.2) | 
          grepl(pattern, ignore.case=T, mydf$subj.3)
subDF <- mydf[filter,] 

> subDF 
      doc  subj.1  subj.2          subj.3
1 blabla1   apple coconut           berry
2 blabla2   prune   apple          banana
3 blabla3 coconut  cherry apple and berry

EDIT :

About your question on for-loop, I don't see any problem in using it, and I doubt using a apply-family function would give many benefits in term of execution time.

For the error, the problem is that the string pattern passed to grepl has to be a valid regular expression but '+' is a special character and so '++' is not allowed.
Anyway, if you just want to check if the subject string is contained in the column, you can disable the regular expression engine by setting the grepl argument fixed=TRUE ( this means pattern is a string to be matched as is).

The only drawback is that ignore.case cannot be used with fixed = TRUE.

edited May 8, 2014 at 7:13

answered May 7, 2014 at 16:10

digEmAll

57.3k9 gold badges118 silver badges142 bronze badges

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

5 Comments

MrFlick Over a year ago

Here's another way to make that filter variable if you have a lot of columns: filter<-apply(sapply(mydf[,2:4], grepl, pattern="apple", ignore.case=T), 1, any) -- just change 2:4 to whatever columns you want to search

A5C1D2H2I1M1N2O1R2T1 Over a year ago

@MrFlick, I was thinking more along unique(unlist(lapply(mydf[-1], function(x) which(grepl("apple", x))))).

MrFlick Over a year ago

@AnandaMahto Good suggestion. That appears to be more efficient in my benchmark tests r-fiddle.org/#/fiddle?id=mGlxBYaJ&version=1

A5C1D2H2I1M1N2O1R2T1 Over a year ago

@MrFlick, it avoids the dual *applys, and lapply should always be faster than sapply. Thanks for benchmarking! :-)

cptn Over a year ago

@All: thank you for your answers! I edited the question, because I'm trying a loop approach to speed up the process in a big data.frame. Has anyone got an idea how to do that properly?

Collectives™ on Stack Overflow

Select rows based on values in various columns in R

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related