Create a column with number of times a value has appeared so far in R?

Question

I have a data table:

ID           FREQUENCY
"jso"        3
"and"        2
"jso"        3
"mo"         1
"jso"        3
"and"        2

It has a column with the frequency. However, I want to create a table with how many times the id has appeared so far. So I'd want my data table to look like this:

ID           FREQUENCY
"jso"        1
"and"        1
"jso"        2
"mo"         1
"jso"        3
"and"        2

How would you do this?

Answer 1

This can be done by group by operations. With data.table, convert the 'data.frame' to 'data.table' (setDT(df1)), grouped by 'ID', we get the sequence of rows (seq_len(.N)) and assign (:=) it to 'FREQUENCY'

library(data.table)
setDT(df1)[,FREQUENCY := seq_len(.N) , by = ID]

---

Or using dplyr, the row_number() is a convenient function for the sequence of rows (after grouping by 'ID'.

library(dplyr)
df1 %>%
    group_by(ID) %>%
    mutate(FREQUENCY = row_number())

---

Or with base R

with(df1, ave(FREQUENCY, ID, FUN = seq_along))
#[1] 1 1 2 1 3 2

data

df1 <- structure(list(ID = c("jso", "and", "jso", "mo", "jso", "and"
), FREQUENCY = c(3L, 2L, 3L, 1L, 3L, 2L)), .Names = c("ID", "FREQUENCY"
), class = "data.frame", row.names = c(NA, -6L))