Geek Answers Handbook

R: Create a new column in the data frame using a mapping from another data frame

I have the following data frames.

> temp
  x1    x2
1  1 INDIA
2  2 INDIA
3  3    US
4  4    US

> PortfolioIndices
  Country   Index CCY
1   INDIA   CNX50 INR
2      US   SP500 USD
3      UK FTSE100 GBP

I want to add another column to temp with the currencies corresponding to the respective countries in column x2 using the mapping from the PortfolioIndices data frame. Something like this should be a weekend

> temp
  x1    x2  x3
1  1 INDIA INR
2  2 INDIA INR
3  3    US USD
4  4    US USD

I do not want to use for a loop, since the actual data can be very large, and using a for loop in this case will be very inefficient. Is there a better way to achieve this result?

Thanks in advance.

+4
source share
2 answers

You can use merge:

> merge(temp, PortfolioIndices, by.x = "x2", by.y = "Country")
     x2 x1 Index CCY
1 INDIA  1 CNX50 INR
2 INDIA  2 CNX50 INR
3    US  3 SP500 USD
4    US  4 SP500 USD
+2
source

Here is the .table data approach recommended for large tables.

require(data.table)

# I imagine you have your data in data.frames.

temp <- data.frame(x1 = c(1:4), x2 = c("INDIA", "INDIA", "US", "US"))

PortfolioIndices <- data.frame(Country = c("INDIA", "US", "UK"),
                            Index = c("CNX50", "SP500", "FTSE100"),
                            CCY = c("INR", "USD", "GBP"))                           

# Coerce your data to data.table objects (they are still data.frames) and use the J() 
# function    

temp <- as.data.table(temp)
PortfolioIndices <- as.data.table(PortfolioIndices)
setkey(temp, x2)
setkey(PortfolioIndices, Country)

PortfolioIndices[temp, list(x1,Index,CCY),]

#   Country x1 Index CCY
# 1:   INDIA  1 CNX50 INR
# 2:   INDIA  2 CNX50 INR
# 3:      US  3 SP500 USD
# 4:      US  4 SP500 USD
0

More articles:


All Articles