codercc codercc - 1 year ago 66
R Question

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

I have a data table:

"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:

"jso" 1
"and" 1
"jso" 2
"mo" 1
"jso" 3
"and" 2

How would you do this?

Answer Source

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'

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'.

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


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))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download