Change entry with number suffix to full number in r?

Question

Can you change "1.00K" to "1,000" or "1.00M" to "1,000,000" in r? Currently listed as a character string.

Answer 1

If you need the result as numeric, you could do it with regular expressions:

numbers <- c("5.00K", "1.00M", "100", "3.453M")

as.numeric(sub("^(\\d+\\.?\\d*).*$", "\\1", numbers)) *
  ifelse(grepl("K", numbers), 1000, 1) * 
  ifelse(grepl("M", numbers), 1e6, 1)
#> [1]    5000 1000000     100 3453000

Answer 2

We may also do this by replacing the 'K', 'M' with e3 and e4 respectively using str_replace and then directly convert to numeric

library(stringr)
as.numeric(str_replace_all(str1, setNames(c("e3", "e6"), c("K", "M"))))
[1]    5000 1000000     100 3453000

data

str1 <- c("5.00K", "1.00M", "100", "3.453M")

Answer 3

Here is another approach:

x <- "1.00K"
format(as.numeric(sub("K", "e3", x, fixed = TRUE)), big.mark = ",")
[1] "1,000"

options(scipen = 100)
y <- "1.00M"
format(as.numeric(sub("M", "e6", y, fixed = TRUE)), big.mark=",")
[1] "1,000,000"

• Explanation:

sub("K", "e3", x, fixed = TRUE) gives "1.00e3" (e.g.: K is replaced by e3)

and adding as.numeric(..):

as.numeric("1.00e3") gives 1000

and

wraping it around format(..., bigmark=","):

format(as.numeric(sub("K", "e3", x, fixed = TRUE)), big.mark = ",") gives 1,000

• Now same procedure for M but here we need e6

Answer 4

The stringr library should address this. Try the following:

# load library
library(stringr)

# construct a vector requiring the change
foo <-  c("1.00K", "bar")
foo

# replace values
foo <- str_replace_all(foo, pattern = "1.00K", replacement = "1,000")
foo

To make the other changes, like converting "1.00M" to "1,000,000", simply alter the value for the replacement = argument. When cleaning data, I often assemble all of these cleaning steps in a separate R script that gets called early in my R Markdown document.