How to reference the current line while creating a new variable in a datatable

General
1

How to reference the current line while creating a new variable in a datatable in R

Let's work from this example :

library(tidyverse)
library(data.table)
library(lubridate)
MWE <- as.data.table(ggplot2::economics) %>%
  .[,c("pce","psavert","uempmed","unemploy"):=NULL]

> MWE
           date      pop
  1: 1967-07-01 198712.0
  2: 1967-08-01 198911.0
  3: 1967-09-01 199113.0
  4: 1967-10-01 199311.0
  5: 1967-11-01 199498.0
 ---                    
570: 2014-12-01 319746.2
571: 2015-01-01 319928.6
572: 2015-02-01 320074.5
573: 2015-03-01 320230.8
574: 2015-04-01 320402.3

I have not found information on something that does not feel that exotic to me : how to reference the current line when creating a new variable.
In the mentioned datatable, I would like to create some new variables, for instance :

  • the month-on-month growth of population,
  • the year-on-year evolution,
  • a bit more complicated : the difference between current population and the average of the population the same month other the 5 preceding years.

I want something conceptually like that:

  • MWE_MoM <- MWE[,MonthOnMonth:=pop[Date=i]/pop[Date=(Date(i)-month(1))]
  • MWE_YoY <- MWE[,YearOnYear:=pop[Date=i]/pop[Date=(Date(i)-year(1))]
  • MWE_Special <- MWE[,Special:=mean(Value),by month=month(i) & year < year(i) & year >= year(i) + 6]
2

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.