TechQA.

Adding a derived conditional column using dplyr

85 views Asked by At

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")
r dplyr tidyr derived-column
Original Q&A
2

There are 2 answers

1
Sabrina Xie On BEST ANSWER

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.

0
Bex Middleton On

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])))

Related Questions in R

Related Questions in DPLYR

Related Questions in TIDYR

Related Questions in DERIVED-COLUMN

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json

Trending Questions