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:

``````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?

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