R - Deleting both rows in a "paired" dataframe if one value = x

user_11649178 0
Viewed 88

I have a dataset where every two rows is part of a choice set - which will be used for discrete choice analysis in mlogit. I want to delete all rows associated with a choice set, based on the values in a column.

For example - with this simple data set, lets say I wanted to remove all the pairs where there was a yellow value in one of the rows in a pair (pairs defined here by "Set").

I am assuming there must be an easy way to say if this value = "x" , and this value of this other column matches the value of that row, remove both. I'm figuring it must be something to do with ifelse or case_when in dplyr, but its the matching value part that I'm not sure about. In excel I would use a cell reference to do a simple if_then, but not sure the best way to do that in R. Thanks!

data <-  data.frame(Set = sort(rep(1:20,2)), 
                    Choice = rep(c(T,F),20),
                    Color = sample(
                      rep(c("Red","Blue","Green","Yellow"),5)))
dplyr r
3 Answers

You could select those set which has all the values in Color column which not 'Yellow'.

library(dplyr)
data %>% group_by(Set) %>% filter(all(Color != 'Yellow'))

Or another way to write this would be :

data %>% group_by(Set) %>% filter(!any(Color == 'Yellow'))

This can also be written in base R

subset(data, ave(Color != 'Yellow', Set, FUN = all))

and data.table :

library(data.table)
setDT(df)[, .SD[all(Color != 'Yellow')], Set]
user_39629140

There shouldn't be a need to do a calculation for each group as such. You can find the Sets containing "Yellow", then only keep those that aren't in those Sets. Since we're talking sets, let's use some set theory:

data[data$Set %in% setdiff(data$Set, data$Set[data$Color == "Yellow"]),]

You can expand this to many colours easily:

data[data$Set %in% setdiff(data$Set, data$Set[data$Color %in% c("Yellow","Green")]),]

And why should you care? Because if you ever work with large datasets with many groups, doing all the calculations in a single pass is a lot faster then having to repeat everything for each group:

## one million groups!
data <- data[rep(1:20, each=1e5),]
data$Set <- rep(1:1e6, each=2)

library(dplyr)
system.time({ 
    data %>% group_by(Set) %>% filter(all(Color != 'Yellow'))
})
## i'm still waiting for this to finish 10 minutes later

system.time({
    data[data$Set %in% setdiff(data$Set, data$Set[data$Color == "Yellow"]),]
})
#   user  system elapsed 
#  0.301   0.000   0.302
user_4968030 
df[!(df$Set %in% with(df, Set[which(Color == "Yellow")])),]
user_92031580
Related