Counting occurrences and occurrences which do not appear

user_13727756 0
Viewed 72

I have a dataframe which looks like this:

head(df)

id    id_child
1       1
1       2
1       3
2       1
4       1
4       2

I would like to create a variable which counts the number of children per parent. So I would like something like this:

head(nb_chilren)

id    id_child      
1       3
2       1
3       0
4       2

If possible, I would like that the person 3 is indicated as having 0 child even though she does not exist in the first frame.

Note: ids are sequential, in real data they are 1 to 10628.

Any suggestions? I suppose I must use the split() function, but I really do not know how to use it.

counting r
3 Answers

Convert id to factor with levels from minimum id value to maximum.

df$id <- factor(df$id, levels = min(df$id):max(df$id))

You can then use table in base R :

stack(table(df$id))[2:1]

Or count in dplyr :

library(dplyr)
df %>% count(id, .drop = FALSE)

#  id n
#1  1 3
#2  2 1
#3  3 0
#4  4 2
user_39629140

One dplyr option could be:

df %>%
 group_by(id = factor(id, levels = min(id):max(id)), .drop = FALSE) %>%
 summarise(id_child = n_distinct(id_child))

  id    id_child
  <fct>    <int>
1 1            3
2 2            1
3 3            0
4 4            2
user_59645570

Here is a solution with table

table(factor(df[[1]], levels = Reduce(':', range(df[[1]]))))
#1 2 3 4 
#3 1 0 2

In data.frame format:

tbl <- table(id = factor(df[[1]], levels = Reduce(':', range(df[[1]]))))
as.data.frame(tbl)
#  id Freq
#1  1    3
#2  2    1
#3  3    0
#4  4    2
user_82454060
Related