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")
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.