CODEWITHSUNDEEP
rdata.table
TMo
TMo

Reputation: 465

How to Replace Text Currency Columns As Numeric R data.table

How do I convert many columns to numeric when they are characters because they include '$'?

library(data.table)
dt = data.table( text = c('a', 'b', 'c'), price = c('1.1', 2.2, 3.3), other = c('$1','$2','$3') )

I thought that the following might work:

dt[ , names(dt) := as.list( gsub('\\$', '', dt) ) ]
dt = type.convert( dt , as.is = TRUE )

But, it did not.

I get the following error:

Warning messages:
1: In `[.data.table`(dt, , `:=`(names(dt), as.list(gsub("\\$", "",  :
  Coercing 'character' RHS to 'double' to match the type of the target column (column 2 named 'price').
2: In `[.data.table`(dt, , `:=`(names(dt), as.list(gsub("\\$", "",  :
  NAs introduced by coercion

Could you please help?

Upvotes: 0

Views: 127

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388972

Use lapply -

library(data.table)

cols <- names(dt)
#Or for selected columns.
#cols <- c('other', 'price')
dt[ , (cols) := lapply(.SD, function(x) gsub('\\$', '', x)), .SDcols = cols]
dt = type.convert( dt , as.is = TRUE )
dt

#   text price other
#1:    a   1.1     1
#2:    b   2.2     2
#3:    c   3.3     3

Upvotes: 2

Related Questions