CODEWITHSUNDEEP
rdataframe
Jain
Jain

Reputation: 999

Mapping values between data frame in R

Records:-

 UniqueID      Country        Price
    AAPL         USA            107
    AAPL         USA            105
    GOOG         USA            555
    GOOG         USA            555
    VW           DEU            320

Mapping:-

 UniqueID      Country        Price
    AAPL         USA            120
    GOOG         USA            550
    VW           DEU            300

I want to add a column Final and map the values from the mapping table to the records tables . For e.g. all the AAPL entries in the records table should have a final value of 120. 

Output:-

Records:-

 UniqueID      Country       Price     Final
    AAPL         USA          107        120
    AAPL         USA          105        120
    GOOG         USA          555        550
    GOOG         USA          555        550
    VW           DEU          320        300

I used the following line of code:-

Records$Final <- Mapping[which(Records$UniqueID==Mapping$UniqueID),"Price"]

It throws me an error saying the replacement and data length are different. Also using merge duplicates the columns, which I don't want to.

Upvotes: 0

Views: 887

Answers (3)

Atendra Gautam
Atendra Gautam

Reputation: 475

Records=data.frame(UniqueID=c("AAPL","AAPL","GOOG","GOOG","VW"),country=c("USA","USA","USA","USA","DEU"),Price=c(107,105,555,555,320))

Mapping=data.frame(UniqueID=c("AAPL","GOOG","VW"),country=c("USA","USA","DEU"),Price=c(120,550,300))

names(Mapping)[3] <- "Final"

Output <- merge(x=Records,y=Mapping[,c(1,3)],by="UniqueID",all.x=TRUE)

Upvotes: 0

Sotos
Sotos

Reputation: 51582

We can use inner_join,

library(dplyr)
inner_join(records, Mapping, by = c('UniqueID', 'Country'))
#  UniqueID Country Price.x Price.y
#1     AAPL     USA     107     120
#2     AAPL     USA     105     120
#3     GOOG     USA     555     550
#4     GOOG     USA     555     550
#5       VW     DEU     320     300

To follow your method then,

Records$Final <- Mapping$Price[match(Records$UniqueID, Mapping$UniqueID)]
Records
#  UniqueID Country Price Final
#1     AAPL     USA   107   120
#2     AAPL     USA   105   120
#3     GOOG     USA   555   550
#4     GOOG     USA   555   550
#5       VW     DEU   320   300

Upvotes: 3

vasanthcullen
vasanthcullen

Reputation: 386

First, in the Mapping table rename the column Price to Final

colnames(Mapping)[colnames(Mapping) == "Price"] <- "Final"

Then, use merge(). You should be getting what you wanted

Upvotes: 0

Related Questions