How can I find the number of a vector's elements in another vector?

Question

I have two vectors. First vector name is comments$author_id and second is enrolments$learner_id. I want to add new column into enrolmens dataframe that shows count of repeated rows in comments$author_id vector for each enrolment$learner_id row.

Example:

 if(enrolments$learner_id[1] repeated 5 times in comments$author_id)
    enrolments$freqs[1] = 5

Can I do this don't using any loops?

The vector samples are as follows:

df1 <- data.frame(v1 = c(1,1,1,4,5,5,4,1,2,3,5,6,2,1,5,2,3,4,1,6,4,2,3,5,1,2,5,4))
df2 <- data.frame(v2 = c(1,2,3,4,5,6))

I want to add "counts" column to "df2" that shows counts of repeated v2 element in v1.

Including a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) in your question will increase your chances of getting an answer. — Samuel, Oct 20 '17 at 00:18
Use `dput` function to paste in the dataframe you are referring to, or parts of it. — Samuel, Oct 20 '17 at 00:27
@jsb I tried to add minimal reproducible example. I hope it is as you said. — Yunus YILDIRIM, Oct 20 '17 at 00:36
@JonahThunderbolt - I've edited it slightly so it makes valid data.frames. I hope this is okay. — thelatemail, Oct 20 '17 at 00:37
`df2$count <- tabulate(factor(df1$v1, df2$v2))` should do the trick. — lukeA, Oct 20 '17 at 00:49
@lukeA It gives me this error: Error in `$<-.data.frame(*tmp*, "comments_count", value = c(0L, 0L, : replacement has 25596 rows, data has 25597 ` — Yunus YILDIRIM, Oct 20 '17 at 01:06
@JonahThunderbolt See my answer - there's probably one value at the end of v2, which is not part of v1. — lukeA, Oct 20 '17 at 09:36

jruf003 · Answer 1 · 2017-10-20T00:43:48.017

1

Using your df1 and df2 example, you could do it like this:

# Make data
df1 = data.frame(v1 = c(1,1,1,4,5,5,4,1,2,3,5,6,2,1,5,2,3,4,1,6,4,2,3,5,1,2,5,4))
df2 = data.frame(v2 = c(1,2,3,4,5,6))

# Add 'count' variable as reqeuested
df2$counts = sapply(df2$v2, function(x) {
  sum(df1$v1 == x, na.rm = T) #na.rm=T just in case df1$v1 has missing values
})
df2 #view output

edited Oct 20 '17 at 00:43

answered Oct 20 '17 at 00:38

jruf003

980
5
19

May be it will works but take a long time. I am waiting for 5 minutes but it's not finished yet – Yunus YILDIRIM Oct 20 '17 at 01:03

score 1 · Answer 2 · answered Oct 20 '17 at 01:09

1

What you essentially are doing is aggregating the df1 to get a count, and then adding this count back to the df2 set. This logic can be easily translated to a bunch of different methods:

# base R
merge(
  df2,
  aggregate(cbind(df1[0], count=1), df1["v1"], FUN=sum),
  by.x="v2", by.y="v1", all.x=TRUE
)

# data.table
library(data.table)
setDT(df1)
setDT(df2)
df2[df1[, .(count=.N), by=v1], on=c("v2"="v1")]

# dplyr
library(dplyr)
df1 %>%
  group_by(v1) %>%
  count() %>%
  left_join(df2, ., by=c("v2"="v1"))

#  v2 count
#1  1     7
#2  2     5
#3  3     3
#4  4     5
#5  5     6
#6  6     2

answered Oct 20 '17 at 01:09

thelatemail

91,185
12
128
188

Last code gives me this error and its too complex for me :D: `Warning message: In left_join_impl(x, y, by$x, by$y, suffix$x, suffix$y) : joining factors with different levels, coercing to character vector`. I started to learn R one week ego. – Yunus YILDIRIM Oct 20 '17 at 01:23
@JonahThunderbolt - that's a warning, not an error. It just means that you have categories in one of the data.frames that is not present in the other. The join should still work just fine. – thelatemail Oct 20 '17 at 01:24
It does not make any changes to the dataframe :S. I have a long todo list and I am crying now – Yunus YILDIRIM Oct 20 '17 at 01:42
@JonahThunderbolt - you need to overwrite the previous dataset - `df2 <- ` – thelatemail Oct 20 '17 at 01:44
Yes it works. Thanks a lot bro. But I tried the same code to count each learner likes. `final = final[comments[, .(total_likes=.N), by=likes], on=c("learner_id"="author_id")]` How should I edit this code? I want to learn more about this syntax. – Yunus YILDIRIM Oct 20 '17 at 02:25
yesterday's commands are not working today. I used second method and it worked but now doesnt work. Gives me this error: `Error in [.data.frame(comments, , .(count = .N), by = author_id) : unused argument (by = author_id)` – Yunus YILDIRIM Oct 24 '17 at 23:06

score 1 · Accepted Answer · answered Oct 20 '17 at 09:35

"[tabulate] gives me this error: Error in $<-.data.frame(tmp, "comments_count", value = c(0L, 0L, : replacement has 25596 rows, data has 25597"

That is prly because there is one value at the end of df2$v2, which are not part of df1$v1 - I add 0 and 7 to your example to show that:

df1 <- data.frame(v1 = c(1,1,1,4,5,5,4,1,2,3,5,6,2,1,5,2,3,4,1,6,4,2,3,5,1,2,5,4))
df2 <- data.frame(v2 = c(1,2,3,0,4,5,6,7))
df2$count <- tabulate(factor(df1$v1, df2$v2))
# Error in `$<-.data.frame`(`*tmp*`, count, value = c(7L, 5L, 3L, 0L, 5L,  : 
#   replacement has 7 rows, data has 8

To correct that using tabulate, which might be the fastest solution on larger data:

df2$count <- tabulate(factor(df1$v1, df2$v2), length(df2$v2))
df2
# v2 count
# 1  1     7
# 2  2     5
# 3  3     3
# 4  0     0
# 5  4     5
# 6  5     6
# 7  6     2
# 8  7     0

See ?tabulate for the documentation on that function.

@lukeaA final$comments_count2 = tabulate(factor(comments$author_id, final$learner_id), length(final$learner_id))I tried it and it worked first but it is not working now. what's wrong? It adds new column that indicates only 0's. — Yunus YILDIRIM, Oct 21 '17 at 03:18

How can I find the number of a vector's elements in another vector?

3 Answers3