mardi 5 novembre 2019

Replacing NA's with values from another row, conditional on a specific country and years

I have a data.table with currencies (let's say in USD) as follows, which has NA's after the country has switched to EUR. In addition, I have the year in which the currency switches to EUR:

library(data.table)
DT <- fread(
    "Val iso   year   
     2   NLD   1998
     NA  NLD   1999   
     NA  NLD   2000
     NA  NLD   2001   
     NA  NLD   2002  
     1   GBR   1998
     1   GBR   1999   
     2   GBR   2000
     NA  GBR   2001   
     1   GBR   2002  
     NA  EUR   1998
     1   EUR   1999   
     1   EUR   2000
     2   EUR   2001   
     1   EUR   2002  
     3   GRC   1998
     NA  GRC   1999
     4   GRC   2000
     NA  GRC   2001
     NA  GRC   2002",
   header = TRUE
)

EUR <- fread(
    " e_iso e_year   
      NLD   1999   
      GRC   2001",
   header = TRUE
)

I would like to use the EUR currency to replace the NA's ONLY after the respective country has switched to EUR. So, for example Val for GRE in 1999 should remain NA.

I was thinking something along the lines of:

DT[EUR$e_iso %in% DT_iso & EUR$e_year %in% DT$year, Val := ?]

But I am not sure how to proceed. Desired Output:

library(data.table)
DT <- fread(
    "Val iso   year   
     2   NLD   1998
     1   NLD   1999   
     1   NLD   2000
     1   NLD   2001   
     1   NLD   2002  
     1   GBR   1998
     1   GBR   1999   
     2   GBR   2000
     NA  GBR   2001   
     1   GBR   2002  
     NA  EUR   1998
     1   EUR   1999   
     1   EUR   2000
     2   EUR   2001   
     1   EUR   2002  
     3   GRC   1998
     NA  GRC   1999
     4   GRC   2000
     1   GRC   2001
     1   GRC   2002",
   header = TRUE
)

Aucun commentaire:

Enregistrer un commentaire