0

The initial dataset I am working with is a list of tarnatula prices overtime from two shops, one based in Canada and the other in Poland.

In order to make a fair comparison between the two shops, I want to convert the Polish Zloty to USD, depending on the exchange rate at the time of each data point. So far I've merged a timeline of exchange rates with tarantula prices overtime (See code below with sample data). However, Im unsure how to create a new derived column, e.g. "Comparible prices" where the prices already in USD remain the same but those in PLN are converted to USD. Ive tried to use the mutate function to create a new column where the price column is divided by the conversion rate, but am struggling how to make this conditional for the rows in Zloty only.

This is an example of the kind of output id be looking for:

Date 1 USD to PLN Price Currency Converted
2013-06-07 3.241658 10 $ 10
2013-06-07 3.241658 20 zl 6.17 
library(priceR)
library(dplyr)

Tarantula <- structure(list(Date = structure(c(15863, 16355, 15930, 15962, 
15903, 16121, 16545, 16575, 16608, 16000, 16639, 16670, 16306, 
16249, 17956, 17988, 18019, 18050, 18111, 18143, 17819, 17646, 
17676, 17707, 17738, 17648, 17770, 17801, 17679, 17710, 17740, 
17833, 17864, 17926, 17895, 18188, 17646, 17676, 17707, 17738, 
17648, 17770, 17679, 17710, 17740, 17833, 17864, 17895, 17444, 
17478, 17387, 17419, 17512, 17542, 17391, 17455, 17489, 17492, 
17584, 17554, 17523, 17433, 17435, 17465, 17526, 17405, 17559, 
17467, 17438, 17500, 15962, 15962, 18111, 18081, 18143, 18918, 
18188, 18578, 18689, 18708, 18923, 18897, 18578, 18689, 18708, 
17444, 17478, 17387, 17418, 17512, 17542, 17433, 17391, 17455, 
18918, 17489, 17584, 17492, 17554, 18923, 17523, 17646, 17435, 
17465, 17526, 17648, 17405, 18897, 17559, 17467, 17438, 17500, 
18578, 18689, 18708, 18923, 18897, 16355, 16545, 16575, 16306, 
16249, 18081, 16846, 16702, 17956, 18111, 18111, 18081, 18143, 
18143, 17819, 18188, 18188, 17738, 17770, 17801, 17740, 17833, 
17864, 17926, 17895, 17801, 17167, 17444, 17478, 17387, 17419, 
17391, 17180, 17300, 17331, 17362, 17455, 17121, 17122, 17489, 
17492, 17433, 17435, 17465, 17405, 17467, 17438, 17500, 16996, 
17028, 17059, 17090, 16969, 17646, 17676, 17707, 17738, 17648, 
17770, 17679, 17710, 17740, 17646, 17988, 18019, 18050, 16608, 
16639, 16867, 17331, 16846, 17362, 16876, 16908, 16939, 17167, 
17180, 17121, 17122, 17444, 17478, 17387, 17418, 17512, 17542, 
17433, 17391, 17455, 18918, 17489, 17492, 17554, 18923, 17523, 
17435, 17465, 17526, 17405, 18897, 17559, 17467, 17438, 17500, 
16545, 16575, 16608, 16639, 16670, 16121, 17478, 17512, 17542, 
18111, 18143, 17300, 17489, 17492, 17554, 17523, 17676, 17707, 
17526, 17559, 17679, 17500, 17710, 16355, 18111, 18081, 18143, 
18188, 18578, 18689, 18918, 18708, 18923, 18897, 18918, 18923, 
18897, 17387, 18578, 17391, 17405, 18923, 18897, 18578, 18689, 
18708, 16867, 16876, 16908, 16969, 16939, 18578, 18689, 18918, 
18708, 18923, 18897, 17988, 17988, 18019, 18050, 18111, 18081, 
18143, 18578, 18689, 17819, 18188, 18708, 17770, 17801, 17833, 
17864, 17926, 17895, 15863, 17956, 17988, 18019, 18050, 18111, 
18081, 18143, 15930, 17819, 15903, 18188, 17646, 17676, 17738, 
17648, 17770, 17801, 17710, 17740, 17833, 17864, 17926, 17895, 
17444, 17478, 17512, 17542, 18578, 18689, 17455, 18918, 17489, 
17584, 18708, 17492, 17554, 18923, 17523, 17435, 17465, 17526, 
18897, 17559, 17467, 17438, 17500, 17801, 17444, 17478, 17512, 
17542, 18578, 18689, 17433, 17819, 17455, 18918, 18188, 17489, 
17584, 18708, 17492, 17554, 18923, 17523, 17646, 17676, 17707, 
17738, 17435, 17465, 17526, 17648, 17770, 17801, 18897, 17559, 
17679, 17710, 17740, 17467, 17438, 17833, 17864, 17926, 17500, 
17895, 17956, 17988, 18019, 18050, 18111, 18081, 18143, 18578, 
18689, 17819, 18918, 18188, 18708, 18923, 17770, 17801, 18897, 
17833, 17864, 17926, 17895, 17444, 17478, 17512, 17542, 17956, 
17988, 18019, 18050, 18081, 18578, 18689, 17433, 17819, 17455, 
17489, 17584, 18708, 17492, 17554, 17523, 17646, 17676, 17707, 
17738, 17435, 17465, 17526, 17648, 17770, 17801, 17559, 17679, 
17710, 17740, 17467, 17438, 17833, 17864, 17926, 17500, 17895, 
16355, 18689, 16545, 16575, 18708, 16608, 16639, 16670, 18923, 
16702, 18897, 17167, 18689, 17090, 17121, 17122, 18918, 16121, 
18708, 18923, 16306, 18897, 16249, 16121, 18923, 18897, 18689, 
18918, 18708, 16867, 16996, 16846, 16876, 16908, 16939, 17980, 
18923, 18578, 18689, 16996, 17090, 16969, 16939, 18918, 18188, 
18708, 18923, 18897, 15962, 16121, 16000, 18578, 18689, 18708, 
17331, 17362, 16306, 17444, 17478, 17387, 17419, 17512, 17542, 
17391, 17300, 17331, 16996, 17362, 17455, 16908, 16969, 16939, 
17489, 17492, 17584, 17554, 17523, 17433, 17646, 17676, 17707, 
17980, 17646, 17738, 17435, 17465, 17526, 17648, 17405, 17770, 
17559, 17679, 17710, 17740, 17467, 17438, 17500, 15930, 15962, 
16121, 16000, 16306, 16249, 15863, 15863, 16355, 15930, 15930, 
15962, 15903, 15903, 16121, 16000, 16306, 16249, 18923, 18897, 
17956, 17988, 18019, 18050, 18111, 18081, 18143, 15962, 17819, 
18188, 16121, 16000, 17770, 17801, 17833, 17864, 17926, 17895, 
18578, 18689, 18918, 18708, 18923, 18897, 18578, 18689, 18918, 
18708, 18923, 18897, 18923, 18897, 16846, 16876, 16306, 17419, 
17391, 17405, 16249, 18019, 18050, 18111, 18081, 18143, 18188, 
18923, 18578, 18689, 18918, 18708, 16996, 16969, 16939, 17167, 
17478, 17090, 17121, 17122, 17489, 17492, 17500, 17444, 17478, 
17512, 17542, 17455, 17489, 17584, 17492, 17554, 17523, 17435, 
17465, 17526, 17559, 17467, 17438, 17500, 17956, 17988, 18019, 
18050, 18081, 17819, 17646, 17676, 17707, 17738, 17648, 17770, 
17801, 17679, 17710, 17740, 17833, 17864, 17926, 17895, 18111, 
18143, 18188, 18578, 18689, 18918, 18708, 18923, 18897, 18578, 
18689, 17819, 18918, 18708, 18923, 17770, 17801, 18897, 17833, 
17864, 17926, 17895, 18578, 18689, 18918, 18708, 18923, 18897, 
17956, 17988, 18019, 18050, 18111, 18081, 18143, 18578, 18689, 
17819, 18188, 18708, 17770, 17801, 17833, 17864, 17926, 17895, 
16939, 17444, 17419, 17391, 17455, 17433, 17435, 17405, 17438, 
18019, 18050, 18111, 18081, 18143, 17387, 17418, 17391, 17405, 
16876, 16908, 18689, 18708, 16306, 16249, 18923, 18918, 18923, 
18897, 15962, 17988, 17819, 17646, 17676, 17707, 17738, 17648, 
17770, 17801, 17679, 17710, 17740, 17833, 17864, 17926, 17895, 
15863, 15930, 15962, 15903, 16121, 16000, 18111, 18143, 17646, 
17676, 17707, 17738, 17648, 17679, 17710, 17740, 18918, 18923, 
18897, 18923, 18897, 18923, 18897, 17551, 17883, 17486, 17456, 
17427, 17610, 17551, 17518, 17486, 17456, 17427, 17610, 17551, 
17610, 17427, 17456, 17427, 19051, 17551, 17518, 17486, 17610, 
17551, 17518, 17486, 17456, 17427, 17486, 17456, 17427, 17551, 
17518, 17486, 17456, 17427, 17551, 17518, 17486, 17427, 17610, 
17610, 17456, 19051), class = "Date"), Price = c(10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
10L, 10L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 
100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 
100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 
100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 
100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 14L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 175L, 175L, 175L, 175L, 
175L, 175L, 175L, 175L, 175L, 175L, 175L, 175L, 175L, 175L, 175L, 
175L, 175L, 175L, 175L, 175L, 175L, 175L, 175L, 175L, 20L, 20L, 
20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 
20L, 20L, 20L, 20L, 20L, 20L, 20L, 200L, 200L, 200L, 200L, 200L, 
200L, 200L, 200L, 200L, 200L, 200L, 200L, 200L, 200L, 200L, 200L, 
200L, 200L, 200L, 200L, 200L, 225L, 225L, 225L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 
25L, 250L, 250L, 250L, 250L, 250L, 250L, 250L, 250L, 250L, 250L, 
250L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 
30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 
30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 
30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 35L, 35L, 35L, 35L, 
35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 
35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 
35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 
35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 
35L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 40L, 40L, 40L, 40L, 40L, 
40L, 40L, 40L, 40L, 40L, 40L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 
45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 
45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 
45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 
45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 
45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 
45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 
45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 50L, 
50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 55L, 55L, 
60L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 
65L, 65L, 65L, 65L, 75L, 75L, 75L, 75L, 75L, 75L, 75L, 75L, 75L, 
75L, 75L, 75L, 75L, 75L, 75L, 75L, 75L, 75L, 75L, 75L, 75L, 85L, 
85L, 100L, 100L, 100L, 100L, 100L, 110L, 110L, 110L, 110L, 120L, 
120L, 125L, 125L, 13L, 150L, 15L, 15L, 200L, 20L, 20L, 20L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 30L, 30L, 30L, 30L, 30L, 
30L, 30L, 30L, 30L, 35L, 35L, 40L, 90L), Currency = c("$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", "$", 
"$", "$", "$", "$", "$", "zl", "zl", "zl", "zl", "zl", "zl", 
"zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", 
"zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", 
"zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", "zl", 
"zl", "zl", "zl", "zl")), class = "data.frame", row.names = c(NA, 
-817L)) 

#Getting exchange rates from USD to PLN from 2013 to 2019
cur <- historical_exchange_rates("USD", to = "PLN",
                                 start_date = "2013-01-01", end_date = "2022-03-01")

colnames(cur) <- c('Date','1_USD_to_PLN') #changing column names. 

#making sure the date columns are formatted as dates 
cur$Date <- as.Date(cur$Date, format = "%Y.%m.%d") 
Tarantula$Date <- as.Date(Tarantula$Date)
 
#merging the two datasets together for rows where they share a date  
merged <- right_join(cur, Tarantula, by = "Date")
user438383
  • 5,716
  • 8
  • 28
  • 43
Bex Middleton
  • 17
  • 1
  • 7
  • 5
    It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Please do not share data or code as an image because we can't copy/paste that into R for testing. Show the code you've tried and describe exactly where you are getting stuck. – MrFlick Mar 07 '22 at 18:05
  • 2
    I second what MrFlick said. In addition, depending on your problem, converting Zloty to USD might not be enough because 1 USD in the US buys you less than 1 USD would buy you in Poland, e.g. checkbthe concwpt of purchase power parity (PPP): https://data.oecd.org/conversion/purchasing-power-parities-ppp.htm – deschen Mar 07 '22 at 18:19
  • @MrFlick Thank you! Ive now added a link to a git repository with the code / csv and have added code to the original post. Thanks :) – Bex Middleton Mar 08 '22 at 09:19
  • Uploading it to github is not very helpful. Please read the linked article from MrFlick, especially how to provide an example through the usage of `dput`. – deschen Mar 08 '22 at 09:20
  • 1
    @deschen Oh sorry about that! I've now added the dput of just the relevant columns from the tarantula csv (date, price and currency). I hope thats better! Thanks again – Bex Middleton Mar 08 '22 at 09:45
  • I've also now edited to add an example output, thanks to both of you for the feedback – Bex Middleton Mar 08 '22 at 10:52

2 Answers2

0

I think I've managed to answer my own question after a bit of playing round with dplyr!

Previously I had tried this:

converted <- mutate(merged, Converted = ifelse(merged$Currency=="$", (merged$Price), (merged$Price/merged$1_USD_to_PLN)))

And also tried it with speech marks around the 1_USD_to_PLN, but both of these returned an error.

This now seems to be working though:

converted <- mutate(merged, Converted = ifelse(merged$Currency=="$", (merged$Price), (merged$Price/merged[,2])))

And the full code:

Tarantula <- read.csv("Tarantula_Data.csv")

#Getting exchange rates from USD to PLN from 2013 to 2019
cur <- historical_exchange_rates("USD", to = "PLN",
                                 start_date = "2013-01-01", end_date = "2022-03-01")

colnames(cur) <- c('Date','1_USD_to_PLN') #changing column names

#making sure the date columns are formatted as dates
cur$Date <- as.Date(cur$Date, format = "%Y.%m.%d")
Tarantula$Date <- as.Date(Tarantula$Date)

#merging the two datasets together for rows where they share a date
merged <- right_join(cur, Tarantula, by = "Date") 

as.factor(merged$Currency)

converted <- mutate(merged, Converted = ifelse(merged$Currency=="$", (merged$Price), (merged$Price/merged[,2])))
Bex Middleton
  • 17
  • 1
  • 7
0

You could also use case_when() which lets you do logical statements in a mutate() and might be useful in the future if you have more conditions:

library(dplyr)

converted <- merged %>% 
  mutate(Conversion=as.numeric(`1_USD_to_PLN`),
         Price=as.numeric(Price),
         comparable=case_when(
           Currency=="$" ~ Price,
           Currency=="zl" ~ Price/Conversion
         ))

Also as a note, you can use a pair of backticks (`) to refer to names or combinations of symbols that are otherwise reserved or illegal.

user438383
  • 5,716
  • 8
  • 28
  • 43
Sabrina Xie
  • 108
  • 5