R joining on counts of elements in a vector to matching index in a dataframe-CodePudding

I have a dataframe df that looks like this:

   indx
1     1
2     2
3     3
4     4
5     5
6     6
7     7
8     8
9     9
10   10

df<-structure(list(indx = 1:10), row.names = c(NA, 10L), class = "data.frame")

And a vector vec that looks like this:

vec<-c(3,5,9,9,5,4,3,3)

I would like to find the counts of each element in vec e.g. by doing this:

vec_counts<-table(vec)

3 4 5 9 
3 1 2 2

And then join these counts into the matching indx in df. The final result should be:

   indx   vec_counts
1     1   0
2     2   0
3     3   3
4     4   1
5     5   2
6     6   0
7     7   0
8     8   0
9     9   2
10   10   0

CodePudding user response：

You can use merge.

x <- merge(df, as.data.frame(table(vec)), by.x = "indx", by.y = "vec", all.x = TRUE)
names(x) <- c("indx", "vec_counts")
x$vec_counts[is.na(x$vec_counts)] <- 0      

x
#    indx vec_counts
# 1     1          0
# 2     2          0
# 3     3          3
# 4     4          1
# 5     5          2
# 6     6          0
# 7     7          0
# 8     8          0
# 9     9          2
# 10   10          0

Also you can use outer() to calculate it in one quick step without table().

df$vec_counts <- rowSums(outer(df$indx, vec, `==`))

# or with dplyr

library(dplyr)
df %>% 
  mutate(vec_counts = rowSums(outer(indx, vec, `==`)))

CodePudding user response：

A possible solution, based on tidyverse:

library(tidyverse)

df %>% 
  mutate(count = map_dbl(indx, ~ sum(.x == vec)))

#>    indx count
#> 1     1     0
#> 2     2     0
#> 3     3     3
#> 4     4     1
#> 5     5     2
#> 6     6     0
#> 7     7     0
#> 8     8     0
#> 9     9     2
#> 10   10     0

Or in base R:

df$count <- apply(df, 1, \(x) sum(x[1] == vec))

Or even, still in base R, using sapply:

df$count <- sapply(df$indx, \(x) sum(x == vec))