Multiple sub() criteria over multiple columns

Question

I'm trying to perform a find and replace using sub(), and apply it over multiple columns.

My dataset looks similar to this:

> mydata
  col1      col2     col3        col4
1    1     $1.40    $5.39      $23.42
2    2   $(2.40) $(38.29) $(1,239.30)
3    3 $1,302.00  $102.32      $23.10

with several numerical fields expressed in traditional accounting formatting.

I have tried writing the following function to swap out the parenthesis negatives, the thousands separators, and the dollar figures.

find_replace <- function(df, cols){
  df[, cols] <- sub('\\,','',df[, cols])
  df[, cols] <- sub('\\$','',df[, cols])
  df[, cols] <- sub('\\-','',df[, cols])
  df[, cols] <- sub('\\(','-',df[, cols])
  df[, cols] <- sub('\\)','',df[, cols])
  df[, cols] <- as.numeric(df[, cols])
}

mydata[,2:4] <- lapply(mydata[,2:4], find_replace(mydata, 2:4))

...but keep receiving the following error when I test it on the data fram above

Error in match.fun(FUN) : 
  'find_replace(mydata, 2:4)' is not a function, character or symbol

And when I try running it over my actual dataset (applying it over 6 columns and approximately 4.8 million rows), it gets hung up and have to stop the operation before I get the error, but I would imagine it's the same.

Any suggestions for an efficient way to end up with the following, where all fields are numeric? I have also tried using the colClass argument with a SetClass function when reading in the csv similar to this approach but without success.

> mydata
  col1    col2   col3     col4
1    1    1.40   5.39    23.42
2    2   -2.40  38.29 -1239.30
3    3 1302.00 102.32    23.10

Thank you in advance!

Edit: trying the setClass option again, and using the regex from @waterling:

setClass("acntngFmt")
# [1] "acntngFmt"
setAs("character", "acntngFmt",
      function(from) as.numeric(gsub("(?![.])[[:punct:]]", "", col, perl=TRUE, from)))

Input <- "A, B, C
$1.40, $(2.40), $1,302.00
$5.39, $(38.29), $102.32
$23.42, $(1,239.30), $23.10"

DF <- read.csv(textConnection(Input), header = TRUE,
               colClasses = c("acntngFmt", "acntngFmt", "acntngFmt"))
Error in as.character(x) : 
  cannot coerce type 'closure' to vector of type 'character'

I think this is a duplicate: http://stackoverflow.com/questions/5068705/processing-negative-number-in-accounting-format/5069649#5069649 — IRTFM, Jan 31 '17 at 02:10
I edited the question to include that. I had tried that previously, but was unsuccessful. I think the regex is wrong. — Mark Panny, Jan 31 '17 at 02:48

Jean · Accepted Answer · 2017-01-31T03:51:02.620

1

df<-data.frame(V1=c("$1.40","$(2.40)","$(1,302.00)"), V2=c("$5.39","$(38.29)","$0.00"))
           V1       V2
1       $1.40    $5.39
2     $(2.40) $(38.29)
3 $(1,302.00)    $0.00

apply(df, 2, function(col) as.numeric(gsub("(?![.])[[:punct:]]", "", col, perl=TRUE)))
         V1    V2
[1,]    1.4  5.39
[2,]    2.4 38.29
[3,] 1302.0  0.00

edited

apply(df, 2, function(col) {
  as.numeric(
    gsub("\\((.*)\\)","-\\1", 
         gsub("(?![.\\(\\)])[[:punct:]]", "", col, perl=TRUE)
         )
  )
})

 V1     V2
[1,]     1.4   5.39
[2,]    -2.4 -38.29
[3,] -1302.0   0.00

edited Jan 31 '17 at 03:51

answered Jan 31 '17 at 02:10

Jean

1,480
15
27

Seems like you were able to make it work by improving the regex portion? How would you edit it to include replacing "$(2.40)" with -2.40? The parantehesis indicate a negative number. – Mark Panny Jan 31 '17 at 02:13

score 1 · Answer 2 · answered Jan 31 '17 at 06:25

This first converts the leading parentheses to minus signs, then removes all the commas, closing parentheses, and dollar signs.

setClass("acntngFmt")

setAs("character", "acntngFmt",
    function(from) as.numeric( gsub("[$),]", "", gsub("\\(", "-", from))))
DF <- data.frame( lapply(mydata[2:4], as, "acntngFmt"))
#---------------
 DF
    col2   col3     col4
1    1.4   5.39    23.42
2   -2.4 -38.29 -1239.30
3 1302.0 102.32    23.10

Rather than use colClasses, this just use the generic as-function on the character-classed columns. If your columns were factor class, you would first need to convert to character.

mydata <- 
structure(list(col1 = 1:3, col2 = structure(c(3L, 1L, 2L), .Label = c("$(2.40)", 
"$1,302.00", "$1.40"), class = "factor"), col3 = structure(c(3L, 
1L, 2L), .Label = c("$(38.29)", "$102.32", "$5.39"), class = "factor"), 
    col4 = structure(c(3L, 1L, 2L), .Label = c("$(1,239.30)", 
    "$23.10", "$23.42"), class = "factor")), .Names = c("col1", 
"col2", "col3", "col4"), class = "data.frame", row.names = c("1", 
"2", "3"))

Multiple sub() criteria over multiple columns

2 Answers2

edited