Cumulative sequence of occurrences of values

Question

I have a dataset that looks something like this, with a column that can have four different values:

dataset <- data.frame(out = c("a","b","c","a","d","b","c","a","d","b","c","a"))

In R, I'd like to create a second column that tallies, in sequence, the cumulative number of rows containing a particular value. Thus the output column would look like this:

out
1
1
1
2
1
2
2
3
2
3
3
4

Answer 1

Try this:

dataset <- data.frame(out = c("a","b","c","a","d","b","c","a","d","b","c","a"))
with(dataset, ave(as.character(out), out, FUN = seq_along))
# [1] "1" "1" "1" "2" "1" "2" "2" "3" "2" "3" "3" "4"

Of course, you can assign the output to a column in your data.frame using something like out$asNumbers <- with(dataset, ave(as.character(out), out, FUN = seq_along))

Update

The "dplyr" approach is also quite nice. The logic is very similar to the "data.table" approach. An advantage is that you don't need to wrap the output with as.numeric which would be required with the ave approach mentioned above.

dataset %>% group_by(out) %>% mutate(count = sequence(n()))
# Source: local data frame [12 x 2]
# Groups: out
# 
#    out count
# 1    a     1
# 2    b     1
# 3    c     1
# 4    a     2
# 5    d     1
# 6    b     2
# 7    c     2
# 8    a     3
# 9    d     2
# 10   b     3
# 11   c     3
# 12   a     4

---

A third option is to use getanID from my "splitstackshape" package. For this particular example, you just need to specify the data.frame name (since it's a single column), however, generally, you would be more specific and mention the column(s) that presently serve as "ids", and the function would check whether they are unique or whether a cumulative sequence is required to make them unique.

library(splitstackshape)
# getanID(dataset, "out")  ## Example of being specific about column to use
getanID(dataset)
#     out .id
#  1:   a   1
#  2:   b   1
#  3:   c   1
#  4:   a   2
#  5:   d   1
#  6:   b   2
#  7:   c   2
#  8:   a   3
#  9:   d   2
# 10:   b   3
# 11:   c   3
# 12:   a   4

Answer 2

Update:

As Ananda pointed out, you can use the simpler:

 DT[, counts := sequence(.N), by = "V1"]

(where DT is as below)

---

You can create a "counts" column, initialized to 1, then tally the cumulative sum, by factor. below is a quick implementation with data.table

# Called the column V1
dataset<-data.frame(V1=c("a","b","c","a","d","b","c","a","d","b","c","a"))

library(data.table)

DT <- data.table(dataset)

DT[, counts := 1L]
DT[, counts := cumsum(counts), by=V1]; DT

#     V1 counts
#  1:  a      1
#  2:  b      1
#  3:  c      1
#  4:  a      2
#  5:  d      1
#  6:  b      2
#  7:  c      2
#  8:  a      3
#  9:  d      2
# 10:  b      3
# 11:  c      3
# 12:  a      4