0

I have a dataframe called Deals and one of the variables is $DealYear. This contains 3 factors (2013, 2014, 2015). I have another column in Deals called $GDPDeflator which is currently unpopulated. For example:

#Deals table 

DealID   DealAmt  DealYear  Name  GDPDeflator   Website
100101    200       2013     ABC        0       www.abc.com
120022    3000      2014     EFG        0       www.efg.com
300012    650       2013     HIJ        0       www.hij.com

I have a small second table called Deflator which contains the values I need for Deals$GDPDeflator:

#Deflator table
Year   Defl
2012   1.10
2013   1.08
2014   1.055
2015   1.046
2016   1.03

How can I look up the values for Deals$GDPDeflator from Deflator$Defl based on Deals$DealYear and Deflator$Year?

user213544
  • 2,046
  • 3
  • 22
  • 52
RUser108
  • 3
  • 1
  • Rename and merge perhaps? Could you show what your expected output is? – NelsonGon Jan 15 '20 at 11:41
  • Does this answer your question? [Replace values in a dataframe based on lookup table](https://stackoverflow.com/questions/35636315/replace-values-in-a-dataframe-based-on-lookup-table) – jay.sf Jan 15 '20 at 11:49

1 Answers1

0

This is a typical use of merge function as suggested in comments.

Since you did not provide a minimal reproducible example I have to prepare a toy example.

deals.df <- data.frame(DealID = abs(rnorm(3)),
                       DealYear = c(2013,2014,2015),
                       DealAmt = abs(rnorm(3)))
deflator.df <- data.frame(Year=c(2012:2016),
                          Defl=c(1.1,1.08,1.055,1.046,1.03))

At this point you could rename DealYear to Year in deals.df (or vice versa) or, as I show below, use by.x and by.y to tell merge the name of the column in each data frame

NEW.deals.df <- merge(deals.df, deflator.df, by.x = "DealYear", by.y = "Year")

NEW.deals.df

output

  DealYear    DealID   DealAmt  Defl
1     2013 2.4428505 0.8423267 1.080
2     2014 0.7864217 1.7308812 1.055
3     2015 1.2319621 0.7857849 1.046

Now you can rearrange columns if needed.

Please note that you can match rows between data frames with more than one column as identifier using by=c("colname1","colname2",...).

Alfonso
  • 644
  • 7
  • 17
  • Thanks this helped a lot. I actually had to tweak it because whilst your code works it didn't work with my actual dataframes and variables. I changed it by putting `merge( x= deals, y= deflator....etc)` rather than `merge(deals.df, deflator.df)`. For some reason the latter didn't work. – RUser108 Jan 15 '20 at 12:39
  • Interesting, since you did not add a MWE I had to made mine. Anyway happy it helped. You may want to upvote and/or mark as answered if this answer helped you. – Alfonso Jan 15 '20 at 17:21