How to read a csv file in R where some numbers contain commas?

Question

I have a large csv file with a mix of character and numeric columns. Some of the numerical values are expressed as strings with commas. e.g., "1,513" instead of 1513. What is the simplest way to read the data into R?

I can use read.csv(...,colClasses="character"), but then I have to strip out the commas from the relevant elements before converting those columns to numeric, and I can't find a neat way to do that.

It is easy enough to do it by brute force, but I was hoping there might be some simple solution out there.

Answer 1

Not sure about how to have read.csv interpret it properly, but you can reformat the character vector with gsub:

> y <- c("1,200","20,000","100","12,111")
> as.numeric(gsub(",","", y))
[1]  1200 20000   100 12111

This was also answered previously on R-Help (and in Q2 here).

Alternatively, you can pre-process the file, for instance with sed in unix.

Answer 2

I think preprocessing is the way to go. You could use Notepad++ which has a regular expression replace option.

For example, if your file were like this:

"1,234","123","1,234"
"234","123","1,234"
123,456,789

Then, you could use the regular expression "([0-9]+),([0-9]+)" and replace it with \1\2

1234,"123",1234
"234","123",1234
123,456,789

Then you could use x <- read.csv(file="x.csv",header=FALSE) to read the file.

Answer 3

I want to use R rather than pre-processing the data as it makes it easier when the data are revised. Following Shane's suggestion of using gsub, I think this is about as neat as I can do:

x <- read.csv("file.csv",header=TRUE,colClasses="character")
col2cvt <- 15:41
x[,col2cvt] <- lapply(x[,col2cvt],function(x){as.numeric(gsub(",", "", x))})

Answer 4

"Preprocess" in R:

lines <- "www, rrr, 1,234, ttt \n rrr,zzz, 1,234,567, rrr"

Can use readLines on a textConnection. Then remove only those the commas that are between digits:

gsub("([0-9])+\,+([0-9])", "\1\2", lines)

[1] "www, rrr, 1234, ttt" "rrr,zzz, 124567, rrr"

Answer 5

You can have read.table or read.csv do this conversion for you semi-automatically, just create a conversion function and set it as an "as" method using the setAs function like so:

setAs("character", "num.with.commas", 
        function(from) as.numeric(gsub(",", "", from) ) )

Then run read.csv like:

DF <- read.csv('your.file.here', 
   colClasses=c('num.with.commas','factor','character','numeric','num.with.commas'))