How to reference a given line to create a variable in base 100

user_2674098 0
Viewed 64

I have a datable such as this one :

library(tidyverse)
library(lubridate)
library(data.table)

DT <- data.table( 
  "Date"=rep(seq(ymd('2000-01-01'),ymd('2020-12-01'), by = '1 month'),3),
  "Country"=rep(c("France","Germany","Italy"),each=21*12),
  "Population"=rep(seq(from=10000,to=(10000+21*12-1)),3))

(Yes I was not really inspired to simulate the population).

Now I want to have a new variable, for instance population2 but scaled back to value = 100 for early 2000, and specific to each country (even though they are the same here). So that each value would have the Population2:=100*Population/Population{for the given country, at date=2000-01-01}

data.table data-manipulation r
2 Answers

If the dates are not missing and is ordered, use first to return the first value of Population, grouped by 'Country'

DT[, Population2 := 100 * Population/first(Population), by = Country]

Or if it is not ordered, we can also use the i to do the order based on 'Date'

DT[order(Date), Population2 := 100 * Population/first(Population), by = Country]

Or can also subset by that specific 'Date' with a ==

DT[, Population2 := if("2000-01-01" %in% Date) {
        100 * Population/Population[Date == "2000-01-01"]
         } else NA_real_, by = Country]

Or another option is a join after subsetting based on 'Date'

DT[DT[Date == "2000-01-01"], Population2 := Population/i.Population, 
          on = .(Country)]
user_37322710

Using match :

library(data.table)
date_to_compare <- as.Date('2000-01-01')

DT[, Population2 := 100 * Population/Population[match(date_to_compare, Date)], Country]
DT

#           Date Country Population Population2
#  1: 2000-01-01  France      10000      100.00
#  2: 2000-02-01  France      10001      100.01
#  3: 2000-03-01  France      10002      100.02
#  4: 2000-04-01  France      10003      100.03
#  5: 2000-05-01  France      10004      100.04
# ---                                          
#752: 2020-08-01   Italy      10247      102.47
#753: 2020-09-01   Italy      10248      102.48
#754: 2020-10-01   Italy      10249      102.49
#755: 2020-11-01   Italy      10250      102.50
#756: 2020-12-01   Italy      10251      102.51
user_39629140
Related