Can't grepl be used in Apply function?

Question

I have a dataframe with values as below:

BrandName  Expense
Apple      $1.8B
Google     $3.2B
GE         -
facebook   $281M
McDonald   $719M

I want to clean these expense values such that they are finally on same scale (in billions). For ex the final data frame should look like:

BrandName  Expense
Apple      1.8
Google     3.2
facebook   0.281
McDonald   0.719

$ can be simply removed by gsub. This is fine. But I am facing problem afterwards. I am applying a function A which uses grepl to check if the value contains 'M', if true (strip 'M', convert to numeric value, and divide by 1000) and if it returns false (strip 'B', convert to numeric value)

A <- function(x){
  if (grepl("M", x))
  {
    str_replace(x, "M", "")
    as.numeric(x)
    x <- x/1000
  }
  else if (grepl("B", x))
  {
    str_replace(x, "B", "")
    as.numeric(x)
  }
}
frame <- data.frame(frame[1], apply(frame[2],2, A))

But all the expense values are coming out to be NA in final result. On further analysis, I noticed for all values, its going in elseif part. Am i making a bad use of grepl in apply function ? If yes how can i fix it.

or any other better solution to solve this particular problem?

Answer 1

Here is a base R solution which might be more sensible for your problem, depending on your needs:

df$ExpenseScaled <- as.numeric(gsub("[$MB]", "", df$Expense))
m.index          <- substr(df$Expense, nchar(df$Expense), nchar(df$Expense)) == 'M'
df$ExpenseScaled[m.index] <- df$ExpenseScaled[m.index] / 1000

 df
 BrandName Expense ExpenseScaled
1     Apple   $1.8B         1.800
2    Google   $3.2B         3.200
3  Facebook   $281M         0.281
4 McDonalds   $719M         0.719

The first line of code removes the dollar sign and amount symbol (B or M) to obtain a numerical amount. The next two lines of code conditionally divide millions figures by 1000 per your specification.

Answer 2

We can do this with gsubfn. We remove the $ with sub, then replace the 'B', and 'M' with 1 and * 1/1000 using gsubfn, loop through the vector and evaluate the string.

library(gsubfn)
df1$Expense <-  unname(sapply(gsubfn("([A-Z])$", list(B=1, M=' * 1/1000'), 
          sub("[$]", "", df1$Expense)), function(x) eval(parse(text=x))))
df1
#   BrandName Expense
#1     Apple   1.810
#2    Google   3.210
#3  facebook   0.281
#4  McDonald   0.719

---

Or a base R option would be to extract the numeric substring ('val'), the substring at the end ('nm1'), convert the 'val' to numeric and multiply with the 1, 1/1000 based on matching the substring 'nm1' with the key/value' string created.

val <- gsub("[^0-9.]+", "", df1$Expense)
nm1 <- sub(".*(.)$", "\\1", df1$Expense)
df1$Expense <-  as.numeric(val)*setNames(c(1, 1/1000), c("B", "M"))[nm1]
df1
#  BrandName Expense
#1     Apple   1.800
#2    Google   3.200
#3  facebook   0.281
#4  McDonald   0.719

NOTE: This should be also be extended in case there are Trillions, Thousands etc. in both the methods, i.e. in the first method change inside the list(...) and in second we change by creating more key/value groups in setNames(c(1, ...), c("B", "M", ...))

---

Another option is parse_number from readr with dplyr

library(dplyr)
library(readr)
df1 %>% 
   mutate(Expense = parse_number(Expense)/c(1, 1000)[grepl("M", Expense)+1])
#   BrandName Expense
#1     Apple   1.800
#2    Google   3.200
#3  facebook   0.281
#4  McDonald   0.719

data

df1 <- structure(list(BrandName = c("Apple", "Google", "facebook", "McDonald"
), Expense = c("$1.8B", "$3.2B", "$281M", "$719M")), .Names = c("BrandName", 
"Expense"), class = "data.frame", row.names = c(NA, -4L))