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
CodePudding user response:
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
CodePudding user response:
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
CodePudding user response:
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 subset
ing the data in base R
table(subset(df2, data %in% df1$reference, select = data))
CodePudding user response:
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
CodePudding user response:
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)
CodePudding user response:
df1$n <- colSums(outer(df2$data, df1$reference, '=='))
df1
#> reference n
#> 1 cat 1
#> 2 dog 2