1

I am trying to convert currency values from factor to numeric. The format is €110.5M €565K I was able to get rid of € sign, M and K letters but I also need to make the conversion as to show 110.5M=110.500.000 and 565K=565.000. Is there a way that you can suggest?

value<-as.numeric(gsub("[€MK]", "", as.character(strength[1:18207,1])))

Data to be converted

€110.5M €565K
€77M    €405K
€118.5M €290K
akrun
  • 874,273
  • 37
  • 540
  • 662
Jean
  • 13
  • 2

2 Answers2

2

An option is gsubfn

library(gsubfn)
out <- unname(sapply(gsubfn("M|K", list(M = "* 1e6", K = "* 1e3"), 
    sub("€", "", str1)), function(x) eval(parse(text = x))))
out
#[1] 110500000    565000  77000000    405000 118500000    290000

scales::dollar_format(prefix = "", big.mark = ".")(out)
#[1] "110.500.000" "565.000"     "77.000.000"  "405.000"   
#[4] "118.500.000" "290.000"

data

str1 <- c("€110.5M",  "€565K", "€77M", "€405K", "€118.5M", "€290K")
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you. Just tried and got [1] 110.5 as result. It needs to be 110.500.000 for M and 110.500 for K – Jean May 26 '19 at 00:57
  • @Jean I get `[1] 110500000 565000 77000000 405000 118500000 290000` – akrun May 26 '19 at 00:58
  • @Jean I formatted with `dollar_format`. Can you check the output now – akrun May 26 '19 at 01:06
  • Well it worked for € format for one single value, but the problem now is when I enter a column of 18000 rows, it shows an errorBrowse[1]> sapply(exp, function(x) eval(parse(text=x))) Error during wrapup: :1:1: unexpected input 1: € ^ – Jean May 26 '19 at 01:14
  • @Jean It should work whether it is a single value or multple value if the format of the strings is as showed in the example. Do you have a `factor` or `character` column – akrun May 26 '19 at 01:15
  • @Jean My doubt is whether you have strings `"€110.5M"` or `"€110.5M €565K"` – akrun May 26 '19 at 01:19
  • I am getting an error at parse function. I think it does not recognise some values in the column. By the way I am working with Fifa2019 Dataset from Kaggle, Value column – Jean May 26 '19 at 01:19
  • @Jean If you can dput a small reprodcuible example that gives the error then it would be easier to debug – akrun May 26 '19 at 01:20
  • I think have problems with 0 € values. – Jean May 26 '19 at 01:20
  • One second akrun – Jean May 26 '19 at 01:21
  • @Jean Please make sure that the `dput` includes an example that gives the error – akrun May 26 '19 at 01:21
  • https://pasteboard.co/IgpH5p3.png you can see the error and the print out for the first line of the code. There is something wrong with 0 € values. They include € and a space before 0. Can this be the problem? Because it does not fit the format of others and text parser gives an error. – Jean May 26 '19 at 01:26
  • @Jean Can you check entry no. 997. What is the actual value – akrun May 26 '19 at 01:27
  • @Jean Just a doubt, are you executing my function or from the other post – akrun May 26 '19 at 01:28
  • 1
    :) Yes akrun I was running the code from other post. This code is ok and works for one value and also 18000 value. No problems for now. Thank you a lot :) – Jean May 26 '19 at 01:34
1

Here is a similar base R option using sub:

input <- c("€110.5M", "€565K")
exp <- sapply(input, function(x) sub("€(\\d+(?:\\.\\d+)?)K", "\\1*1000",
                  sub("€(\\d+(?:\\.\\d+)?)M", "\\1*1000000", x)))
exp
sapply(exp, function(x) eval(parse(text=x)))

€110.5M           €565K 
"110.5*1000000"      "565*1000" 
  €110.5M     €565K 
110500000    565000
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you very much for the help Tim. The code works for one value but gives an error with my entire dataset. https://pasteboard.co/IgpH5p3.png There may be a problem with 0 values. My problem is ok but I wanted to reply you. – Jean May 26 '19 at 01:37