split char currency into two separate columns in data frame

Question

I have this data frame df

  Items        Item Code           Prices
1  Beds           1630            $135.60
2  Big Shelve     1229            89.5USD
3  Small Shelve   1229            ¥3680.03
4  Chair          445             92.63€
5  Desk           802             206.43 euro
6  Lamp           832             25307.1 JPY

I want to split the prices column into three column: Prices and Currency and Exchange rate from USD using

  Items        Item Code           Prices         Currency      Exchange rates
1  Beds           1630            135.60          USD                 1.00
2  Big Shelve     1229            89.50           USD                 1.00
3  Small Shelve   1229            3680.03         JPY                 115.71
4  Chair          445             92.63           EUR                 0.90
5  Desk           802             206.43          EUR                 0.90
6  Lamp           832             25307.10        JPY                 115.71

I tried using dplyr::separate() but instead it would separate at comma instead. If I try using the gsub() it gives me this error

> df2 <- df %>%
+ mutate(price = as.numeric(gsub'[$,€,¥,]','', df$Col3))
Error: unexpected string constant in:
"df2 <- df %>%
mutate(price = as.numeric(gsub'[$,€,¥,]'" 

Any ideas what to do? Also, how would I able to reference the currency to correct items?

Answer 1

This should solve the problem. Using the quantmod package, you can get the current exchange rate and add that into the data:

  library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
  library(stringr)
  library(tidyr)
  library(quantmod)
#> Loading required package: xts
#> Loading required package: zoo
#> 
#> Attaching package: 'zoo'
#> The following objects are masked from 'package:base':
#> 
#>     as.Date, as.Date.numeric
#> 
#> Attaching package: 'xts'
#> The following objects are masked from 'package:dplyr':
#> 
#>     first, last
#> Loading required package: TTR
#> Registered S3 method overwritten by 'quantmod':
#>   method            from
#>   as.zoo.data.frame zoo
dat <- tibble::tribble(
  ~Items,        ~"Item Code",           ~Prices,
"Beds",           1630,            "$135.60",
"Big Shelve",     1229,            "89.5USD",
"Small Shelve",   1229,            "¥3680.03",
"Chair",         445,             "92.63€",
"Desk",           802,             "206.43 euro",
"Lamp",          832,             "25307.1 JPY")


dat <- dat %>% 
  mutate(currency = c(trimws(str_extract_all(Prices, "[^\\d\\.]+", simplify = TRUE))),
         currency = case_when(currency %in% c("€", "euro") ~ "EUR", 
                              currency == "$" ~ "USD", 
                              currency == "¥" ~ "JPY", 
                              TRUE ~ currency), 
         Prices = as.numeric(str_extract_all(Prices, "\\d+\\.\\d+", simplify=TRUE)),
         xr = paste0("USD", currency, "=X")) %>% 
  left_join(getQuote(unique(.$xr)) %>% as_tibble(rownames = "xr") %>% select(xr, Last)) %>% 
  select(-xr) %>% 
  rename("Exchange rates" = "Last")
#> Joining, by = "xr"

dat
#> # A tibble: 6 × 5
#>   Items        `Item Code`  Prices currency `Exchange rates`
#>   <chr>              <dbl>   <dbl> <chr>               <dbl>
#> 1 Beds                1630   136.  USD                 1    
#> 2 Big Shelve          1229    89.5 USD                 1    
#> 3 Small Shelve        1229  3680.  JPY               116.   
#> 4 Chair                445    92.6 EUR                 0.902
#> 5 Desk                 802   206.  EUR                 0.902
#> 6 Lamp                 832 25307.  JPY               116.

^{Created on 2022-03-03 by the reprex package (v2.0.1)}