Read csv file in R with currency column as numeric

Question

I'm trying to read into R a csv file that contains information on political contributions. From what I understand, the columns by default are imported as factors, but I need the the amount column ('CTRIB_AMT' in the dataset) to be imported as a numeric column so I can run a variety of functions that wouldn't work for factors. The column is formatted as a currency with a "$" as prefix.

I used a simple read command to import the file initially:

contribs <- read.csv('path/to/file')

And then tried to convert the CTRIB_AMT from currency to numeric:

as.numeric(as.character(sub("$","",contribs$CTRIB_AMT, fixed=TRUE)))

But that didn't work. The functions I'm trying to use for the CTRIB_AMT columns are:

vals<-sort(unique(dfr$CTRIB_AMT))
sums<-tapply( dfr$CTRIB_AMT, dfr$CTRIB_AMT, sum)
counts<-tapply( dfr$CTRIB_AMT, dfr$CTRIB_AMT, length)

See related question here.

Any thoughts on how to import file initially so column is numeric or how to convert it after importing?

Answer 1

I'm not sure how to read it in directly, but you can modify it once it's in:

> A <- read.csv("~/Desktop/data.csv")
> A
  id   desc price
1  0  apple $1.00
2  1 banana $2.25
3  2 grapes $1.97
> A$price <- as.numeric(sub("\\$","", A$price))
> A
  id   desc price
1  0  apple  1.00
2  1 banana  2.25
3  2 grapes  1.97
> str(A)
'data.frame':   3 obs. of  3 variables:
 $ id   : int  0 1 2
 $ desc : Factor w/ 3 levels "apple","banana",..: 1 2 3
 $ price: num  1 2.25 1.97

I think it might just have been a missing escape in your sub. $ indicates the end of a line in regular expressions. \$ is a dollar sign. But then you have to escape the escape...