0

I have two data.tables. One data.table (dt1) contains the information where an individual is from (country) and when the individual was born (date). The other data.table (dt2) contains an index for each country and each year.

dt1 <- data.table(country=c("Argentina", "Brazil", "United States"), 
                  date = c("2012-25-12", "2015-21-05", "2017-01-06"))

dt2 <- data.table(country=c("Argentina", "Brazil", "United States"), 
                  "2012" = c(0.25, 0.85, 0.11),
                  "2013" = c(0.43, 0.23, 0.95),
                  "2014" = c(0.35, 0.45, 0.66),
                  "2015" = c(0.45, 0.63, 0.45),
                  "2016" = c(0.78, 0.54, 0.22),
                  "2017" = c(0.22, 0.22, 0.32))

I want to add the information of one data.table (dt2) to the other data.table (dt1), depending on year and country.

I've tried a for loop

years <- as.character(2012:2017)
for(i in 1:length(years)){
  for(j in countries)
  {dt1[country==j & date <=as.Date(paste0(years[i], "-12-31")), index:=dt2[country==(j), (1+i)]]
    
  }
}

I expected dt1 to look like this:

         country       date index
1:     Argentina 2012-12-25     0.25
2:        Brazil 2015-05-21     0.63
3: United States 2017-01-06     0.32

instead, dt1 looks like this:

         country       date index
1:     Argentina 2012-12-25     7
2:        Brazil 2015-05-21     7
3: United States 2017-01-06     7

i.e., index is equal to the 1+i of the last, i.e., 6th iteration of the loop

What do I need to change so that index is actually the element from dt2 that fulfills the country and year conditions?

I appreciate any help and feedback, also if this question lacks in clarity or reproducibility. Thanks

Odysseus
  • 25
  • 3

2 Answers2

0

Try merge():

merge(x = dt1, y = dt2, by="country")
Mario
  • 2,393
  • 2
  • 17
  • 37
0
merge(
  dt1[, year := substr(date, 1, 4)],
  melt(dt2, id = "country", variable.name = "year", value.name = "index")
)

#          country year       date index
# 1:     Argentina 2012 2012-25-12  0.25
# 2:        Brazil 2015 2015-21-05  0.63
# 3: United States 2017 2017-01-06  0.32
Merijn van Tilborg
  • 5,452
  • 1
  • 7
  • 22