codercc codercc - 3 months ago 14
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?

Answer

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))