CODEWITHSUNDEEP
rmergedata.tablevlookup
Siddharth Pandit
Siddharth Pandit

Reputation: 27

Vlookup in R referencing columns and rows

I would like help with replicating a vlookup from Excel in R. I have two data tables of the following kind but with several more rows and attributes. I have redimensioned them for the sake of simplicity -

FX <- data.table(Currency = c("USD","EUR","AUD"), Y2014 = c(2.13,3.45,1.8), Y2015 = c(2.16,3.48,1.7), Y2016 = c(2.19,3.49,1.6))

DATA <- data.table(Customer = c("Abc","Def","Ghi","Jkl","Mno"), Year = c(2013,2014,2015,2012,2018), CurrencyCode = c("AUD","USD","USD","EUR","USD"))

FX has a list of currencies as the rows and different years as columns denoting their exchange rate against a fixed currency (SEK) and DATA has some customer deals which were originally reported in that fixed currency (SEK).

I would like to add another attribute to DATA called, as an example, ConversionRate by first matching the Currency attribute in FX to CurrencyCode in DATA and then selecting the corresponding conversion rate for the year given in Year from DATA by matching it to the column Yxxxx in FX.

It would result in something like this -

data  <- data.table(Customer = c("Abc","Def","Ghi","Jkl","Mno"), Year = c(2013,2014,2015,2012,2018), ConversionRate = c(1.7,2.13,2.16,3.45,2.19))

Please note that for Year < 2014, I would like it to pick up the rate of the corresponding currency in 2014 and for Year > 2016, I would like it to pick up the rate of the corresponding currency in 2016 as it has done for Row 1,4,5.

I have tried using loops, merge, and even a custom vlookup function but it seems I am going wrong when it comes to comparing the Year to the column names Yxxxx.

Any idea on how this can be achieved?

Thank you!

Upvotes: 1

Views: 94

Answers (1)

IceCreamToucan
IceCreamToucan

Reputation: 28695

After melting FX to long and converting the "Y2016" etc. values to numbers, you can do an update join to DATA with this fx_long. If you want to join on a year other than the year in the data, you can first create a new column join_year and join on that instead.

library(data.table)

fx_long <- melt(FX, 'Currency')[, Year := as.numeric(sub('Y', '', variable))]

DATA[, join_year := pmin(pmax(Year, 2014), 2016)]

DATA[fx_long, on = .(join_year = Year, CurrencyCode = Currency), ConversionRate := i.value]



DATA    
#    Customer Year CurrencyCode join_year ConversionRate
# 1:      Abc 2013          AUD      2014           1.80
# 2:      Def 2014          USD      2014           2.13
# 3:      Ghi 2015          USD      2015           2.16
# 4:      Jkl 2012          EUR      2014           3.45
# 5:      Mno 2018          USD      2016           2.19

Upvotes: 3

Related Questions