16

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
A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
Luke
  • 4,769
  • 5
  • 28
  • 36

2 Answers2

19

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
A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
  • My thought as well. A question: don't you reckon the documentation for argument `x` in `?ave` is misleading? (It indicates `x` should be numeric, but "character" class vectors obviously work too.) – Josh O'Brien Mar 05 '13 at 17:46
  • @JoshO'Brien, true... And the name of the function... and the first line in the documentation... All misleading! I think I only learned about this feature from observing a few answers here on SO (a lot from DWin). – A5C1D2H2I1M1N2O1R2T1 Mar 05 '13 at 17:50
  • 2
    What's the best way to notify R-core about little details like this, I wonder? Is the R-devel mailing list the recommended channel? Or is a documentation error like this too small for them to care about? – Josh O'Brien Mar 05 '13 at 17:55
  • `%.%` was not recognized function. `%>%` worked for me. – Urvah Shabbir Aug 23 '17 at 06:47
  • 1
    @urwaCFC, note the date of the original answer. Back then, the "dplyr" package, which is still in rapid function-breaking development mode, used `%.%` instead of `%>%`. – A5C1D2H2I1M1N2O1R2T1 Aug 23 '17 at 09:56
7

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
Community
  • 1
  • 1
Ricardo Saporta
  • 54,400
  • 17
  • 144
  • 178
  • A more direct approach would be: `DT[, counts := sequence(.N), by = "V1"]` ... but I'm not sure what was available in `data.table` at the time of this answer! :) – A5C1D2H2I1M1N2O1R2T1 Sep 25 '13 at 10:27
  • haha, yes, absolutely. This answer is from 7 months ago, ;) but I'm sure .N was already available, I just overlooked it. Ive gone ahead and edited – Ricardo Saporta Sep 25 '13 at 13:34