I have a dataframe with different currencies spread across multiple columns where I need to convert all values to the same currency (USD).
Here is a sample df:
df <- data.frame(country = c("Romania", "Hungary", "Bulgaria", "Czechia"),
Value_1 = c("500 000.00 RON", "520 000.00 HUF", "480 000.00 BGN", "460 000.00 CZK"),
Value_2 = c("100 000.00 RON", "320 000.00 HUF", "580 000.00 BGN", "660 000.00 CZK"),
Value_3 = c("20 000.00 RON", "10 000.00 HUF", "60 000.00 BGN", "50 000.00 CZK"))
I have had a look at a couple of other questions as well as the priceR package. I am a bit confused how to approach it - is it possible to write a function that converts all currencies in the data set, or will I need to first separate the currency from the amount to be converted? This is the first time I've had to convert currencies in a dataset - any help would be appreciated.
EDIT
Just tried out Ronak's suggested solution, but noticed there was some unusual results for currencies and values that are the same - surely these would be converted to same values instead of different? See below a new sample dataframe and code:
df_No_2 <- data.frame(country = c("Romania", "UK", "UK", "UK"),
Value_1 = c("500 000.00 RON", "41 000 000.00 GBP", "32 000 000.00 GBP", "32 000 000.00 GBP"),
Value_2 = c("100 000.00 RON", "80 000.00 GBP", "80 000.00 GBP", "80 000.00 GBP"),
Value_3 = c("20 000.00 RON", "5 000.00 GBP", "5 000.00 GBP", "5 000.00 GBP"))
convert_currency_1 <- function(x) {
currency <- sub('.*\\s([A-Z]+)', '\\1', x)
exchange_rate <- getQuote(paste0(currency, "USD", "=X"))$Last
as.numeric(gsub('\\s|[A-Z]+', '', x)) * exchange_rate
}
df_No_2 <- df_No_2 %>%
mutate(across(starts_with("Value_"), convert_currency_1))
Here's a way to do this with getQuote
function from quantmod
library.
library(dplyr)
library(quantmod)
convert_currency_1 <- function(x) {
currency <- sub('.*\\s([A-Z]+)', '\\1', x)
symbol <- paste0(currency, "USD", "=X")
exchange_rate <- getQuote(symbol)[symbol, ]$Last
as.numeric(gsub('\\s|[A-Z]+', '', x)) * exchange_rate
}
df_No_2 %>%
mutate(across(starts_with("Value_"), convert_currency_1))
# country Value_1 Value_2 Value_3
#1 Romania 115441.4 23088.29 4617.658
#2 UK 56013986.8 109295.58 6830.974
#3 UK 43718233.6 109295.58 6830.974
#4 UK 43718233.6 109295.58 6830.974