Add a Count field to a data frame

Question

I have the following dataframe dat:

        > dat
  subjectid variable
1      1234 12
2      1234 14
3      2143 19
4      3456 12
5      3456 14
6      3456 13

How do I add another column which shows the count of each unique subjectid?

ddply(dat,.(subjectid),summarize,quan_95=quantile(variable,0.95),uniq=count(unique(subjectid)))

@josilber, this is definitely a duplicate, but the Q & A you identified is not one of them. — A5C1D2H2I1M1N2O1R2T1, Jul 10 '14 at 16:14

score 1 · Answer 1 · answered Jul 10 '14 at 16:43

Similar to ave(), you may also use split/lapply/unsplit:

i = split(dat$variable, dat$subjectid)
count = unsplit(lapply(i, length), dat$subjectid)

Then graft the count variable back using data.frame() or whatever your preferred method.

The split() function just creates a list of dat$variable values for each value of dat$subjectid. The count is found by using lapply() to apply the length() function over each index in the list (i) and unsplit() puts everything back in order.

unsplit() is pure magic and fairy dust. I didn't believe it the first 100 times.

score 1 · Accepted Answer · answered Jul 10 '14 at 18:40

Here is an approach via dplyr. First we group by subjectid, then use the function n() to count number of rows in each group:

dat <- read.table(text="
subjectid variable
1      1234 12
2      1234 14
3      2143 19
4      3456 12
5      3456 14
6      3456 13")

library(dplyr)

dat %>%
  group_by(subjectid) %>%
  mutate(count = n())

  subjectid variable count
1      1234       12     2
2      1234       14     2
3      2143       19     1
4      3456       12     3
5      3456       14     3
6      3456       13     3

score 0 · Answer 3 · answered Jul 10 '14 at 16:27

If dat is ordered by subjectid

   tbl <- table(dat[,1])
    transform(dat, count=rep(tbl, tbl))
   # subjectid variable count
   #1      1234       12     2
   #2      1234       14     2
   #3      2143       19     1
   #4      3456       12     3
   #5      3456       14     3
   #6      3456       13     3

Add a Count field to a data frame

3 Answers3