Subsetting dataframe based on lots of columns in R

swr 0
Viewed 58

thanks in advance for any help! I have a dataframe with lots of columns, and I'd like to filter it to only display rows that match specific values in each of those columns. I can easily produce the conditions I'd like to filter by, but I feel like there's an easier way to actually filter the data than by filtering for each value individually.

For a reproducible example, I'll generate a dataset that has lots of columns that will produce matching entries using anagrams. I'm not just looking for all duplicates, but each of them have duplicates -- I'm looking for duplicates of a given test value (test below). Glad for any help!

# load my important libraries
library(dplyr); library(stringr)


corpus <- tibble(word  = c("tables", "stables",
                           "elbow", "below", "bowel",
                           "ascot", "coats", "coast", "tacos",
                           "aridest", "astride", "staider", "tardies", "tirades"))

corpus[letters] <- NA

corpus %<>%
  pivot_longer(cols = letters) %>%
  mutate(value = str_count(word, name)) %>%
  pivot_wider(names_from = name, values_from = value)


# I'm choosing a completely arbitrary row (8) as an example value to look for.
test <- corpus[8, letters]

I want to determine which rows in my dataframe, the corpus, have the same combination of letters as my test value. I can certainly filter the corpus one letter at a time, but it's so inelegant:

corpus %>%
  filter(a == test$a,
         b == test$b,
         c == test$c,
         d == test$d,
         e == test$e,
         # etc . . .
         x == test$x,
         y == test$y,
         z == test$z)

I feel like there has to be an easier way to do this using either the match, which, or %in% commands, so that it could match even 100 columns if necessary without me writing out each individual one. My ideal solution would look something like this:

corpus %>%
  which(test)

or

which(corpus[letters] == test[letters])

and give me the results:

# A tibble: 4 × 27
  word      a     b     c     d     e     f     g     h     i     j     k     l     m     n     o     p     q     r     s
  <chr> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1 ascot     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1
2 coats     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1
3 coast     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1
4 tacos     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1
# … with 7 more variables: t <int>, u <int>, v <int>, w <int>, x <int>, y <int>, z <int>
subset match filter r
3 Answers

We could use across

library(dplyr)
corpus %>% 
      filter(across(-1, ~ .x == test[[cur_column()]]))

-output

# A tibble: 4 × 27
  word      a     b     c     d     e     f     g     h     i     j     k     l     m     n     o     p     q     r     s     t     u
  <chr> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1 ascot     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1     1     0
2 coats     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1     1     0
3 coast     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1     1     0
4 tacos     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1     1     0
# … with 5 more variables: v <int>, w <int>, x <int>, y <int>, z <int>

Or another option with imap/filter

library(purrr)
imap(corpus[-1], ~ .x == test[[.y]]) %>%
   reduce(`&`) %>% 
   filter(corpus, .)

-output

# A tibble: 4 × 27
  word      a     b     c     d     e     f     g     h     i     j     k     l     m     n     o     p     q     r     s     t     u
  <chr> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1 ascot     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1     1     0
2 coats     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1     1     0
3 coast     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1     1     0
4 tacos     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0     0     0     1     1     0
# … with 5 more variables: v <int>, w <int>, x <int>, y <int>, z <int>
akrun0

Using the (paste(x, collapse="")) function to collapse all the columns into a single string and then compare.

corpus[apply(X=corpus[-1], MARGIN=1, FUN=function(x) {paste(test, collapse = "")==paste(x, collapse = "")}),]
# A method without the anonymous function and the comparison outside of apply
# Small speed improvement:
corpus[apply(corpus[-1],1,paste,collapse="")==paste(test, collapse = ""),]
# Do.Call method, Fastest yet:
corpus[ do.call(paste0, corpus[-1]) == paste(test, collapse = ""),]

  word      a     b     c     d     e     f     g     h     i     j     k     l     m     n     o     p
1 ascot     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0
2 coats     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0
3 coast     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0
4 tacos     1     0     1     0     0     0     0     0     0     0     0     0     0     0     1     0
M.Viking0

Here is tidyverse approach: We could compare test row with corpus row after using bind_rows, unite all to one column and compare the column for duplicates. Finally filter the duplicates and remove first row by drop_na (because word gives NA in test row):

library(dplyr)
library(tidyr)

test %>% 
  bind_rows(corpus) %>% 
  unite(., helper, -word, remove = FALSE) %>% 
  mutate(helper = ifelse(helper == first(helper), 1, 0)) %>% 
  filter(helper == 1) %>% 
  drop_na() %>% 
  select(word, everything(), -helper)

  word      a     b     c     d     e     f     g     h     i     j     k
  <chr> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1 ascot     1     0     1     0     0     0     0     0     0     0     0
2 coats     1     0     1     0     0     0     0     0     0     0     0
3 coast     1     0     1     0     0     0     0     0     0     0     0
4 tacos     1     0     1     0     0     0     0     0     0     0     0
# ... with 15 more variables: l <int>, m <int>, n <int>, o <int>,
#   p <int>, q <int>, r <int>, s <int>, t <int>, u <int>, v <int>,
#   w <int>, x <int>, y <int>, z <int>
# i Use `colnames()` to see all variable names
TarJae0
Related