Counting elements in another dataframe in R

Question

I need to append a new column to my first dataframe with the number of appearances of the corresponding id in the second dataframe. I've created a toy example to try to do this. I'm thinking it's going to require a for loop, but cannot figure it out. Here's the toy datasets:

id <- c(1:10)
gender <- c('M','M','M','F','F','F','M','F','F','F')
age <- c(23,33,45,66,12,6,18,31,26,77)
first_df <- data.frame(id,gender,age)

id <- c(1,3,8,1,2,3,7,9)
second_df <- data.frame(id)

So where id is 1 in my first_df, I would like a column that has the value 2 because it appears twice in the second_df. And for id 5 should have a 0 or NA in that new column because it does not appear in the second_df.

Answer 1

first_df$counts <- lapply(first_df$id, function(x) sum(x == id))

Answer 2

Here is a solution using base R:

second_df <- as.data.frame(table(second_df))
first_df <- merge(x = first_df, y = second_df, by.x = 'id', by.y = 'second_df', all = TRUE)
first_df$Freq[is.na(first_df$Freq)] <- 0
names(first_df)[4] <- 'N'

Here is a solution using dplyr:

library(dplyr)    

second_df <- second_df %>% 
      group_by_all %>% 
      tally 
    
first_df <- left_join(first_df, second_df, by = "id") %>% 
  replace(is.na(.), 0)

This gives you:

  id gender age n
1   1      M  23 2
2   2      M  33 1
3   3      M  45 2
4   4      F  66 0
5   5      F  12 0
6   6      F   6 0
7   7      M  18 1
8   8      F  31 1
9   9      F  26 1
10 10      F  77 0

Answer 3

You can use a join. Here summarise counts the number of times each id occurs in second_df and stores that value in numb. Then inner_join attaches second_df to first_df matching on id

library(dplyr)
second_df <- second_df %>% 
  group_by(id) %>% 
  summarise(numb = n())

first_df %>% 
  left_join(second_df, by = "id")
   id gender age numb
1   1      M  23    2
2   2      M  33    1
3   3      M  45    2
4   4      F  66   NA
5   5      F  12   NA
6   6      F   6   NA
7   7      M  18    1
8   8      F  31    1
9   9      F  26    1
10 10      F  77   NA