Selecting columns based on row values in multiple columns using dplyr

Question

I am trying to select columns where at least one row equals 1, only if the same row also has a certain value in a second column. I would prefer to achieve this using dplyr, but any computationally efficient solution is welcome.

Example:

Select columns among a1, a2, a3 containing at least one row where the value is 1 AND where column b=="B"

Example data:

rand <- function(S) {set.seed(S); sample(x = c(0,1),size = 3, replace=T)}
df <- data.frame(a1=rand(1),a2=rand(2),a3=rand(3),b=c("A","B","A"))

Input data:

  a1 a2 a3 b
1  0  0  0 A
2  0  1  1 B
3  1  1  0 A

Desired output:

I managed to obtain the correct output with the following code, however this is a very inefficient solution and I need to run it on a very large dataframe (365,000 rows X 314 columns).

df %>% select_if(function(x) any(paste0(x,.$b) == '1B'))

You should better convert your data to long format. The reason you find this difficult is because you're trying to compute it in wide-format. — talat
– talat, Commented Dec 6, 2017 at 8:13
@docendodiscimus Thanks for the hint, that seems easier indeed ! — cmdoret
– cmdoret, Commented Dec 6, 2017 at 9:05

jlesuffleur · Accepted Answer · 2017-12-06 08:13:02Z

3

A solution, not using dplyr:

df[sapply(df[df$b == "B",], function(x) 1 %in% x)]

answered Dec 6, 2017 at 8:13

jlesuffleur

1,3031 gold badge9 silver badges20 bronze badges

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

Comments

J_F · Accepted Answer · 2017-12-06 08:52:47Z

2

Here is my dplyr solution:

ids <- df %>% 
  reshape2::melt(id.vars = "b") %>% 
  filter(value == 1 & b == "B") %>% 
  select(variable)

df[,unlist(ids)]

#  a2 a3
#1  0  0
#2  1  1
#3  1  0

As suggested by @docendo-discimus it is easier to convert to long format

answered Dec 6, 2017 at 8:52

J_F

10.4k2 gold badges35 silver badges59 bronze badges

Collectives™ on Stack Overflow

Selecting columns based on row values in multiple columns using dplyr

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related