How do I replace "k" and "m" with thousands and millions?

user_12081269 0
Viewed 726

I have a dataframe, parsed from Coursera. One of the columns is number of students enrolled on the course. Looks like this:

df <- data.frame(uni = c("Yale", "Toronto", "NYU"), students = c("16m", "240k", "7.5k"))

      uni students
1    Yale     "16m"
2 Toronto     "240k"
3     NYU     "7.5k"

What I need to get is

      uni students
1    Yale     16000000
2 Toronto     240000
3     NYU     75000

So, the main difficulty for me there is that the class of values is character, and I do not know function for replacing ks and ms, and transforming the class of column to numerics.

Please, help me!

dataframe r converters parsing replace
6 Answers

E.g.

d$students <- dplyr::case_when(
  stringr::str_detect(d$students, 'm') ~ readr::parse_number(d$students) * 1e6,
  stringr::str_detect(d$students, 'k') ~ readr::parse_number(d$students) * 1e3,
  TRUE ~ parse_number(d$students)
)
user_43414400

An option with base r:

df$students <- ifelse(grepl('m', ignore.case = TRUE, df$students), as.numeric(gsub("[$m]", "", df$students)) * 10^6,
                      as.numeric(gsub("[$k]", "", df$students)) * 10^3)

# uni students
# 1    Yale 16000000
# 2 Toronto   240000
# 3     NYU     7500
user_73260580

Using stringr and dplyr from tidyverse 

library(tidyverse)
df %>%
  mutate(students = case_when(
    str_detect(students, "m") ~ as.numeric(str_extract(students, "[\\d\\.]+")) * 1000000,
    str_detect(students, "k") ~ as.numeric(str_extract(students, "[\\d\\.]+")) * 1000,
  ))
# A tibble: 3 x 2
  uni     students
  <chr>      <dbl>
1 Yale    16000000
2 Toronto   240000
3 NYU         7500
user_109521400

Here's an approach with separate that would work for any arbitrary number of modifiers, simply keep defining them in the case_when statement.

library(dplyr)
library(tidry)
df %>%
  separate(students,into = c("value","modifier"),
           sep = "(?<=[\\d])(?=[^\\d.])") %>%
  mutate(modifier = case_when(modifier == "b" ~ 1000000000,
                              modifier == "m" ~ 1000000,
                              modifier == "k" ~ 1000,
                              TRUE ~ 1),
         result = as.numeric(value) * modifier)
      uni value modifier  result
1    Yale    16    1e+06 1.6e+07
2 Toronto   240    1e+03 2.4e+05
3     NYU   7.5    1e+03 7.5e+03
user_130953260

One can write a function that does the conversion, for example:

f <- function(s) {
  l <- nchar(s)
  x <- as.numeric(substr(s, 1, l-1))
  u <- substr(s, l, l)
  x * 10^(3 * match(u, c("k", "M", "G")))
}

f("2M")
f("200k")

Edit: or a little bit more generic:

f <- function(s) {
  x <- as.numeric(gsub("[kMG]", "", s))
  u <- gsub("[0-9.]", "", s)
  if (nchar(u))  x <- x * 10^(3 * match(u, c("k", "M", "G")))
  x
}

f("20")
f("2M")
f("200k")
user_36775760

Using gsub and dplyr:

df %>% mutate(
  unit=gsub("[0-9]+\\.*[0-9]*","",students), #selecting unit
  value=as.numeric(gsub("([0-9]+\\.*[0-9]+).", "\\1", students)), 
  students=ifelse(unit=="k",1e3*value,
                  ifelse(unit=="m",1e6*value,
                         ifelse(unit=="b",1e9*value,value)))) %>%
  select(-c(unit,value))
user_115336980
Related