Create variable based on first observation of a group in R / Rstudio

Question

i have a data.table, and trying to create a new variable based on the first observation of a group e.g. :

    ID, Month, Value
    A    Jan     5
    A    Feb     10
    A    Mar     7
    B    Feb     3
    B    Mar     6

desired output:

    ID, Month, Value First.Month
    A    Jan     5      Jan
    A    Feb     10     Jan
    A    Mar     7      Jan
    B    Feb     3      Feb
    B    Mar     6      Feb

I am working in Rstudio, any help is appreciated

Thanks in advance!

akrun akrun · Accepted Answer · 2017-05-24T12:50:46

With data.table, we can group by 'ID' and assign (:=) the first element of 'Month' i.e. 'Month[1L]' (1L is just a integer representation, it can be dropped to 1) to create the 'First.Month'

library(data.table)
dt[, First.Month := Month[1L], by = ID]
dt
#   ID Month Value First.Month
#1:  A   Jan     5         Jan
#2:  A   Feb    10         Jan
#3:  A   Mar     7         Jan
#4:  B   Feb     3         Feb
#5:  B   Mar     6         Feb

Note: If it is not a data.table, convert to data.table first

setDT(dt)

Create variable based on first observation of a group in R / Rstudio

1 Answers