Extract All Rows in Dataframe with 0 and 0.00% values

[xiaoni]

I have a data as follows.

df <- data.frame(Type=c(1,2,3,4,5), Category1=c(0,1,0,1,0), Category2=c("0.00 %", "1.00 %", "1.00 %", "1.00 %","0.00 %"), Category3=c(0,1,1,0,0),Category4=c("0.00 %", "1.00 %", "1.00 %", "1.00 %","1.00 %"))

I want to only check those rows with 0 and 0.00%.

a <- df[which(df[,2:ncol(df)] == 0),1]
b <- df[which(df[,3:ncol(df)] == sprintf("%.2f %%", 100*0)),1]
intersect(a,b)

It seems 2:ncol(df) is not working.

Expected Result, the return of intersect(a,b) should only be 1.

[siddharthprabhu]

It's hard to recreate your data from a screenshot. It would be better if you could share a minimal reprex instead. Instructions on how to do so can be found here: FAQ: What's a reproducible example (`reprex`) and how do I do one?

[xiaoni]

Hi @siddharthprabhu I have updated the topic. Thank you.

[cholyavkin]

If I understand you correctly, a simple dplyr::filter can help you to extract the required rows.

library(dplyr)
df %>% 
  filter(Category1 == 0,
         Category2 == "0.00 %",
         Category3 == 0,
         Category4 == "0.00 %")

[xiaoni]

@cholyavkin I think this can be done but my problem is my df can be more than 4 columns. So I needed a dynamic version. Do you have idea?

[cholyavkin]

Oh I see. Actually, I can offer a solution but you need different naming pattern for your values and percentage columns. I replaced the value columns as "Category_val", percentage columns as "Category_per". Here is a example:

df <- data.frame(Type=c(1,2,3,4,5), 
                 Category_val1 = c(0,1,0,1,0), 
                 Category_per2 = c("0.00 %", "1.00 %", "1.00 %", "1.00 %","0.00 %"), 
                 Category_val3 = c(0,1,1,0,0),
                 Category_per4 = c("0.00 %", "1.00 %", "1.00 %", "1.00 %","1.00 %"))

If you can prepare the data like this you apply generic filter to all columns starts with Category_val or Category_perc with dplyr::filter_at() function. Like this:

df %>% 
  filter_at(vars(starts_with("Category_val")), all_vars(. == 0)) %>% 
  filter_at(vars(starts_with("Category_per")), all_vars(. == "0.00 %"))

  Type Category_val1 Category_per2 Category_val3 Category_per4
     1             0        0.00 %             0        0.00 %

If it is not possible to rename the columns, reply again. We can also solve that in a way.

[xiaoni]

Yes. This is working. Thank you