I have a df with thousands of tickers for different future contracts. They have the abbreviated name (which appears later) and the long name (which I want to have in other df)
full_list <- structure(
list(
Ticker = c("AC", "AIC", "BBS", "BO", "C", "DF"),
Long_Name = c("Ethanol -- CBOT", "DJ UBS Commodity Index -- CBOT", "South American Soybeans -- CBOT", "Soybean Oil -- CBT", "Corn -- CBT", "Dow Jones Industrial Average -- CBT")
),
.Names = c("Ticker", "Long_Name"),
row.names = c(NA, 6L),
class = "data.frame"
)
This df has the list that I receive daily. I have to go and lookup the abbreviated name and match it to the long name.
replace <- structure(
list(
Type = c("F", "F", "F", "F", "F", "F"),
Location = c("US", "US", "US", "US", "US", "US"),
Symbol = c("BO", "C", "DF", "AIC", "AC", "BBS"),
Month = c("V13", "U13", "U13", "U13", "U13", "U13")
),
.Names = c("Type", "Location", "Symbol", "Month"),
row.names = c(NA, 6L),
class = "data.frame"
)
What I am looking for R to do is take replace$Symbol column and find those values in full_list$Ticker column and add a column, replace$Long_Name, where the respective full_list$Long_Name is copied over. Hope this makes sense. I understand the column names are difficult to follow.
This would be an easy VLookup in excel but I have a script I will use on a daily basis almost completed in R.
Answer
merge them:
> merge(full_list, replace, by.x="Ticker", by.y="Symbol")
Ticker Long_Name Type Location Month
1 AC Ethanol -- CBOT F US U13
2 AIC DJ UBS Commodity Index -- CBOT F US U13
3 BBS South American Soybeans -- CBOT F US U13
4 BO Soybean Oil -- CBT F US V13
5 C Corn -- CBT F US U13
6 DF Dow Jones Industrial Average -- CBT F US U13