CODEWITHSUNDEEP
rformattingcurrencynumeric
Nick
Nick

Reputation: 863

Convert currency with commas into numeric

I have a column in a dataframe as follows:

COL1
$54,345
$65,231
$76,234

How do I convert it into this:

COL1
54345
65231
76234

The way I tried it at first was:

df$COL1<-as.numeric(as.character(df$COL1))

That didn't work because it said NA's were introduced.

Then I tried it like this: 

df$COL1<-as.numeric(gsub("\\$","",as.character(df$COL1)))

And the same this happened.

Any ideas?

Upvotes: 29

Views: 41456

Answers (4)

hello_friend
hello_friend

Reputation: 5788

Nested gsub to handle negatives and transform to make it functional and to take advantage of NSE

transform(df, COL1 = as.numeric(gsub("[$),]", "", gsub("^\\(", "-", COL1))))

Upvotes: 0

charlehl
charlehl

Reputation: 11

Another option using stringr library to remove '$' and ',' then convert as follows:

df %>% mutate(COL1 = COL1 %>% str_remove_all("\\$,") %>% as.numeric())

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388962

We could use parse_number from readr package which removes any non-numeric characters.

library(readr)
parse_number(df$COL1)
#[1] 54345 65231 76234

Upvotes: 58

akrun
akrun

Reputation: 887078

The reason why the gsub didn't work was there was , in the column, which is still non-numeric. So when convert to 'numeric' with as.numeric, all the non-numeric elements are converted to NA. So, we need to remove both , and $ to make it work.

df1$COL1 <- as.numeric(gsub('[$,]', '', df1$COL1))

We match the $ and , inside the square brackets ([$,]) so that it will be considered as that character ($ left alone has special meaning i.e. it signifies the end of the string.) and replace it with ''.

Or we can escape (\\) the character ($) to match it and replace by ''.

df1$COL1 <- as.numeric(gsub('\\$|,', '', df1$COL1))

Upvotes: 39

Related Questions