Find rows in a dataframe which contain all elements of a row of another dataframe

user_6049784 0
Viewed 95

I have a dataframe which contains three columns, and a second which contains two columns.

df1 <- data.frame(X1 = c('A', 'A', 'A', 'A', 'A', 'A', 'B'), 
                  X2 = c('B', 'B', 'B', 'C', 'C', 'D', 'C'), 
                  X3 = c('C', 'D', 'E', 'D', 'E', 'E', 'D'))

df2 <- data.frame(X1 = c('A', 'A'), 
                  X2 = c('B', 'D'))

Questions:

  1. How do I find the rows in df1 which contain all the elements of a row of df2? i.e. rows 1:3 of df1 contain both A and B (first row of df2). I am looking to remove any rows of df1 which contain both elements of the rows of df2. So in the example, I would like to remove rows 1, 2, 3, 4 and 6 of df1 as these include A and B OR A and D.
  2. Is there a quick way to count the number of rows for each row of df2 without looping? i.e. df2 row 1 would have a count of 3 and row 2 a count of 3.
dataframe r
3 Answers

Here is base R option using outer + intersect

mat <- lengths(
  outer(
    asplit(df1, 1),
    asplit(df2, 1),
    Vectorize(intersect)
  )
) >= ncol(df2)

and you will obtain

> subset(df1, !rowSums(mat))
  X1 X2 X3
5  A  C  E
7  B  C  D

> within(df2, cnt <- colSums(mat))
  X1 X2 cnt
1  A  B   3
2  A  D   3
  • asplit splits the data frames by rows
  • outer produces all combinations of rows from df1 and df2
  • intersect gives the intersected elements of rows from two data frames
  • subset selects the rows which has less than one common elements
user_121587570

Using apply:

df1[ !apply(df1, 1, function(i) any(apply(df2, 1, function(j) all(j %in% i)))), ]
#   X1 X2 X3
# 5  A  C  E
# 7  B  C  D

Do the similar loops for df2 match counts:

cbind(df2, 
      cnt = apply(df2, 1, function(i) sum(apply(df1, 1, function(j) all(i %in% j)))))
#   X1 X2 cnt
# 1  A  B   3
# 2  A  D   3
user_6800680

You need to loop somehow. Here is one way to do it using dplyr and purrr:

1.

for(iRow in seq_len(nrow(df2))){
  
  df1 <- df1 %>% 
    rowwise() %>% 
    filter(!all(as.character(df2[iRow,]) %in% c_across(everything())))
}

2.

df2 %>% 
  rowwise() %>% 
  mutate(n = sum(map_int(transpose(df1), ~all(c_across(everything()) %in% .x))))

Just be sure to do 2nd part before 1st because 1st part removes rows. Also you can first detect which rows to remove for each row of df2. This way you can count them and afterwards remove them.

df2 <- df2 %>% 
  rowwise() %>% 
  mutate(
    indices = list(which(map_lgl(transpose(df1), ~all(c_across(everything()) %in% .x))))
  ) %>%
  ungroup() %>%
  mutate(n = map_int(indices, length))

df1 <- df2[["indices"]] %>%
  unlist() %>%
  unique() %>%
  "*"(-1) %>%
  df1[.,]

df2 <- df2 %>% select(-indices)
user_121484020
Related