[R] Exceptional slowness with read.csv

Wed Apr 10 20:38:36 CEST 2024

Dave,

Your method works for you and seems to be a one-time fix of a corrupted data file so please accept what I write not as a criticism but explaining my alternate reasoning which I suspect may work faster in some situations.

Here is my understanding of what you are doing:

You have a file in CSV format containing N rows with commas to make M columns. A few rows have a glitch in that there is a double quote character at the beginning or end (meaning between commas adjacent to one, or perhaps at the beginning or end of the line of text) that mess things up. This may be in a specific known column or in several.

So your algorithm is to read the entire file in, or alternately you could do one at a time. Note the types of the columns may not be apparent to you when you start as you are not allowing read.csv() see what it needs to or perform all kinds of processing like dealing with a comment.
You then call functions millions of times (N) such as read.csv(). Argh!

You do that by setting up an environment N times to catch errors. Of course, most lines are fine and no error.

Only on error lines do you check for a regular expression that checks for quotes not immediately adjacent to a comma. I am not sure what you used albeit I imagine sometimes spaces could intervene. You fix any such lines and re-evaluate.

It seems your goal was to rewrite a corrected file so you are doing so while appending to it a row/line at a time.

My strategy was a bit different.

- Call read.csv() just once with no error checking but an option to not treat a quote specially. Note if the quoted region may contain commas, this is a bad strategy. If all it has is spaces or other non-comma items, it may be fine. 

There is now a data.frame or other similar data structure in memory if it works with N rows and M columns.

- Pick only columns that may have this issue, meaning the ones containing say text as compared to numbers or logical values.
- Using those columns, perhaps one at a time, evaluate them all at once for a regular expression that tests the entry for the presence of exactly one quote either at the start or end (the commas you used as anchors are not in this version.) So you are looking for something like:

"words perhaps including, commas
Or
words perhaps including, commas"

but not for:

words perhaps including, commas
"words perhaps including, commas"

You can save the query as a Boolean vector of TRUE/FALSE as one method, to mark which rows need fixing. Or you might use an ifelse() or the equivalent in which you selectively apply a fix to the rows. One method is to use something like sub() to both match all text except an initial or terminal quote and replace it with a quote followed by the match followed by a quote, if any quotes were found.

Whatever you choose can be done in a vectorized manner that may be more efficient. You do not need to check for failures, let alone N times. And you only need process those columns that need it.

When done, you may want to make sure all the columns are of the type you want as who knows if read.csv() made a bad choice on those columns, or others.

Note again, this is only a suggestion and it fails if commas can be part of the quoted parts or even misquoted parts.

-----Original Message-----
From: R-help <r-help-bounces using r-project.org> On Behalf Of Dave Dixon
Sent: Wednesday, April 10, 2024 12:20 PM
To: Rui Barradas <ruipbarradas using sapo.pt>; r-help using r-project.org
Subject: Re: [R] Exceptional slowness with read.csv

That's basically what I did

1. Get text lines using readLines
2. use tryCatch to parse each line using read.csv(text=...)
3. in the catch, use gregexpr to find any quotes not adjacent to a comma 
(gregexpr("[^,]\"[^,]",...)
4. escape any quotes found by adding a second quote (using str_sub from 
stringr)
6. parse the patched text using read.csv(text=...)
7. write out the parsed fields as I go along using write.table(..., 
append=TRUE) so I'm not keeping too much in memory.

I went directly to tryCatch because there were 3.5 million records, and 
I only expected a few to have errors.

I found only 6 bad records, but it had to be done to make the datafile 
usable with read.csv(), for the benefit of other researchers using these 
data.

On 4/10/24 07:46, Rui Barradas wrote:
> Às 06:47 de 08/04/2024, Dave Dixon escreveu:
>> Greetings,
>>
>> I have a csv file of 76 fields and about 4 million records. I know 
>> that some of the records have errors - unmatched quotes, 
>> specifically. Reading the file with readLines and parsing the lines 
>> with read.csv(text = ...) is really slow. I know that the first 
>> 2459465 records are good. So I try this:
>>
>>  > startTime <- Sys.time()
>>  > first_records <- read.csv(file_name, nrows = 2459465)
>>  > endTime <- Sys.time()
>>  > cat("elapsed time = ", endTime - startTime, "\n")
>>
>> elapsed time =   24.12598
>>
>>  > startTime <- Sys.time()
>>  > second_records <- read.csv(file_name, skip = 2459465, nrows = 5)
>>  > endTime <- Sys.time()
>>  > cat("elapsed time = ", endTime - startTime, "\n")
>>
>> This appears to never finish. I have been waiting over 20 minutes.
>>
>> So why would (skip = 2459465, nrows = 5) take orders of magnitude 
>> longer than (nrows = 2459465) ?
>>
>> Thanks!
>>
>> -dave
>>
>> PS: readLines(n=2459470) takes 10.42731 seconds.
>>
>> ______________________________________________
>> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide 
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
> Hello,
>
> Can the following function be of help?
> After reading the data setting argument quote=FALSE, call a function 
> applying gregexpr to its character columns, then transforming the 
> output in a two column data.frame with columns
>
>  Col - the column processed;
>  Unbalanced - the rows with unbalanced double quotes.
>
> I am assuming the quotes are double quotes. It shouldn't be difficult 
> to adapt it to other cas, single quotes, both cases.
>
>
>
>
> unbalanced_dquotes <- function(x) {
>   char_cols <- sapply(x, is.character) |> which()
>   lapply(char_cols, \(i) {
>     y <- x[[i]]
>     Unbalanced <- gregexpr('"', y) |>
>       sapply(\(x) attr(x, "match.length") |> length()) |>
>       {\(x) (x %% 2L) == 1L}() |>
>       which()
>     data.frame(Col = i, Unbalanced = Unbalanced)
>   }) |>
>   do.call(rbind, args = _)
> }
>
> # read the data disregardin g quoted strings
> df1 <- read.csv(fl, quote = "")
> # determine which strings have unbalanced quotes and
> # where
> unbalanced_dquotes(df1)
>
>
> Hope this helps,
>
> Rui Barradas
>
>

______________________________________________
R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.