1

I have two data frames - the first with dates and currencies, the second with dates, currencies and exchange rates. Here's a reproducible shortened example:

df1 <- data.frame(date = c(1:5),
            currency = c("USD", "SEK", "DKK", "USD", "EUR"),
            rate = rep(NA, 5))

df2 <- data.frame(date = rep(c(1:5), each = 4),
            currency = rep(c("EUR", "DKK", "SEK", "USD")),
            rate = abs(rnorm(20)))

I would like to transfer the correct rate from data frame 2 to data frame 1 matched on currency and date.

I prefer not to use loops if possible, so I tried this:

df1$rate <- df2$rate[df2$date == df1$date & df2$currency == df1$currency]

which transferred a single rate to all of the NAs in df1.

Apologies if I haven't explained myself well, I'm new to R.

Thanks for any help.

Jay Achar
  • 1,156
  • 9
  • 16

2 Answers2

2
df1$rate <- NULL

merge(df1,df2,by=c("date", "currency"),all.x=T)

result:

#  date currency      rate
#1    1      USD 0.1048500
#2    2      SEK 0.3448154
#3    3      DKK 0.8488186
#4    4      USD 0.2233925
#5    5      EUR 2.2532021
Andre Elrico
  • 10,956
  • 6
  • 50
  • 69
0

Adapted from this post Can dplyr join on multiple columns or composite key?

library(tidyverse)

left_join(df1, df2, by = c("currency", "date"))

That will leave you with both columns, you can then either keep both or use select to get rid of the one you don't want.

Ben G
  • 4,148
  • 2
  • 22
  • 42
  • 1
    Beginner error - I seem to forget how powerful joining or merging are......lesson learnt - thanks @BenG – Jay Achar Jun 21 '18 at 13:28
  • @JayAchar, no worries. Just mark one of the answers as "answered" (the check mark) so the question can be closed. – Ben G Jun 21 '18 at 13:31