How to select columns based on row values?

Question

Consider the following tibble:

## # A tibble: 16 x 4
##    name              genus        order        sleep_total
##    <chr>             <chr>        <chr>              <dbl>
##  1 Cheetah           Acinonyx     Carnivora          12.1 
##  2 Northern fur seal Callorhinus  Carnivora           8.70
##  3 Vesper mouse      Calomys      Rodentia            7.00
##  4 Dog               Canis        Carnivora          10.1 
##  5 Roe deer          Capreolus    Artiodactyla        3.00
##  6 Goat              Capri        Artiodactyla        5.30
##  7 Guinea pig        Cavis        Rodentia            9.40
##  8 Domestic cat      Felis        Carnivora          12.5 
##  9 Gray seal         Haliochoerus Carnivora           6.20
## 10 Tiger             Panthera     Carnivora          15.8 
## 11 Jaguar            Panthera     Carnivora          10.4 
## 12 Lion              Panthera     Carnivora          13.5 
## 13 Caspian seal      Phoca        Carnivora           3.50
## 14 Genet             Genetta      Carnivora           6.30
## 15 Arctic fox        Vulpes       Carnivora          12.5 
## 16 Red fox           Vulpes       Carnivora           9.80

I'd like to only select the columns that contain at least one row value called 'Carnivora'.

The expected output in this case would be:

## order       
## <chr>       
## Carnivora   
## Carnivora   
## Rodentia    
## Carnivora   
## Artiodactyla
## Artiodactyla
## Rodentia    
## Carnivora   
## Carnivora   
## Carnivora   
## Carnivora   
## Carnivora   
## Carnivora   
## Carnivora   
## Carnivora   
## Carnivora

Someone else has provided how to get the rows that contain this. However, this includes columns that don't contain this value.

sleep %>% 
  select(name:order, sleep_total, -vore) %>% 
  filter_all(any_vars(str_detect(., pattern = "Ca")))

Ronak Shah · Accepted Answer · 2019-10-10 09:36:00Z

4

We can use select_if

library(dplyr)
ggplot2::msleep %>% select_if(~any(. == "Carnivora", na.rm = TRUE))

#          order
#1     Carnivora
#2     Carnivora
#3      Rodentia
#4     Carnivora
#5  Artiodactyla
#6  Artiodactyla
#7      Rodentia
#8     Carnivora
#9     Carnivora
#10    Carnivora
#11    Carnivora
#12    Carnivora
#13    Carnivora
#14    Carnivora
#15    Carnivora
#16    Carnivora

Or in base R using colSums

msleep[colSums(msleep == "Carnivora", na.rm = TRUE) > 0]

Or apply

msleep[apply(msleep == "Carnivora", 2, any, na.rm = TRUE)]

edited Oct 10, 2019 at 9:36

answered Oct 10, 2019 at 9:09

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

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

5 Comments

PDog Over a year ago

<error> message: Only strings can be converted to symbols class: rlang_error` backtrace: 1. dplyr::select_if(., ~any(. == "Carnivora")) 9. dplyr:::vars_select_syms(vars, funs, .tbl) 10. rlang::syms(vars) 11. rlang:::map(x, sym) 12. base::lapply(.x, .f, ...) 13. rlang:::FUN(X[[i]], ...) 14. dplyr::select_if(., ~any(. == "Carnivora"))` @RonakShah

Ronak Shah Over a year ago

@PDog seems to work fine for me. Can you share the dput of your tibble? dput(sleep) ?

PDog Over a year ago

Too many character for the full output, but here's where the dataframe is from. sleep <- ggplot2::msleep dput(sleep) structure(list(name = c("Cheetah", class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -83L))

Ronak Shah Over a year ago

@PDog ohh..I see..there are NA characters in it hence the error. We need to do add na.rm = TRUE. Updated the answer.

PDog Over a year ago

100% on me, my apologies. I was also able to extend this for partial string matching! sleep %>% select_if(~any(str_detect(., "Ca"), na.rm = TRUE))

akrun · Accepted Answer · 2019-10-10 16:04:36Z

0

We can use %in% in base R with sapply

library(ggplot2)
msleep[sapply(msleep, function(x) "Carnivora" %in% x)]

edited Oct 10, 2019 at 16:04

answered Oct 10, 2019 at 15:53

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

How to select columns based on row values?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related