7

I have two data frames that look like this:

df1 <- data.frame(reference=c("cat","dog"))
print(df1)
#>   reference
#> 1       cat
#> 2       dog
df2 <- data.frame(data=c("cat","car","catt","cart","dog","dog","pitbull"))
print(df2)
#>      data
#> 1     cat
#> 2     car
#> 3    catt
#> 4    cart
#> 5     dog
#> 6     dog
#> 7 pitbull

Created on 2021-12-29 by the reprex package (v2.0.1)

I want to find how many times the words cat and dog from the df1 exist in df2. I want my data to look like this

animals   n
cat       1
dog       2

Any help or guidance is appreciated. My reference list is huge. I tried to grep each one of them but ll take me time.

Thank you for your time. Happy holidays

M--
  • 25,431
  • 8
  • 61
  • 93
LDT
  • 2,856
  • 2
  • 15
  • 32
  • 1
    Re: "I tried to grep each one" - you need grep and regex when you are doing pattern matching or partial string matching. When you are matching whole exact strings as you are here, you just need `==` or `%in%` or other non-regex functions (as all the answers here illustrate). – Gregor Thomas Dec 29 '21 at 20:27

6 Answers6

6

Update: Thanks to Gregor Thomas:

library(dplyr)

left_join(df1,df2, by=c("reference"="data")) %>% 
  count(reference)

output:

  reference n
1       cat 1
2       dog 2

We could use semi_join and then count:

library(dplyr)

semi_join(df2,df1, by=c("data"="reference")) %>% 
  count(data)

  data n
1  cat 1
2  dog 2
TarJae
  • 72,363
  • 6
  • 19
  • 66
4

It may be faster with a join

library(data.table)
setDT(df2)[, .(animals = data)][df1, .(n = .N), 
     on = .(animals = reference), by = .EACHI]
   animals n
1:     cat 1
2:     dog 2

Or use table after subseting the data in base R

table(subset(df2, data %in% df1$reference, select = data))
akrun
  • 874,273
  • 37
  • 540
  • 662
4

A possible solution, tidyverse-based:

library(tidyverse)

df1 <- data.frame(reference=c("cat","dog"))
df2 <- data.frame(data=c("cat","car","catt","cart","dog","dog","pitbull"))

df1 %>% 
  group_by(animal = reference) %>% 
  summarise(n = sum(reference == df2$data), .groups = "drop")

#> # A tibble: 2 × 2
#>   animal     n
#>   <chr>  <int>
#> 1 cat        1
#> 2 dog        2
PaulS
  • 21,159
  • 2
  • 9
  • 26
2

Here is a third option:

library(tidyverse)

df1 <- tibble(reference=c("cat","dog"))
df2 <- tibble(data=c("cat","car","catt","cart","dog","dog","pitbull"))

df2 |>
  count(data) |>
  filter(data %in% df1$reference) |>
  rename(animal = data)
#> # A tibble: 2 x 2
#>   animal     n
#>   <chr>  <int>
#> 1 cat        1
#> 2 dog        2
AndS.
  • 7,748
  • 2
  • 12
  • 17
2

We can use str_count with the column in the second df collapsed into one string.

library(tidyverse)

df1 %>%
  transmute(animals = reference, n = str_c(df2$data, collapse = " ") %>%
    str_count(str_c("\\b", reference, "\\b")) )
#>   animals n
#> 1     cat 1
#> 2     dog 2

Created on 2021-12-29 by the reprex package (v2.0.1)

jpdugo17
  • 6,816
  • 2
  • 11
  • 23
1
df1$n <- colSums(outer(df2$data, df1$reference, '=='))

df1
#>   reference n
#> 1       cat 1
#> 2       dog 2
IceCreamToucan
  • 28,083
  • 2
  • 22
  • 38