Loading a CSV file in chunks based on date condition

Question

R novice here. I am trying to load a large csv file while checking if date is greater than 2019-01-01 due to memory issues. This is what the file looks like.

new_patient_id	date
00001526	19-Jun-19
00001527	19-Jun-18
00001528	20-Jul-19

so in this instance it should return 2 rows.

This is the code I came up with -

library(readr)
library(dplyr)

# Define a function to filter each chunk
filter_chunk <- function(chunk, index) {
  chunk <- chunk %>%
    mutate(date = as.Date(date, format = "%d-%b-%y"))
  filtered_chunk <- chunk %>%
    filter(date >= as.Date("2019-01-01"))
  return(filtered_chunk)
}

# Read the file in chunks and filter each chunk
chunk_size <- 1000  # Adjust this value based on your memory constraints
con <- file("C:/Users/vidnguq/Downloads/r test data.csv", "rb")
vinah_contact <- readr::read_csv_chunked(con, callback = filter_chunk, 
                                         chunk_size = chunk_size, 
                                         col_types = cols(new_patient_id = col_character(), date = col_character()))

# Combine the filtered chunks into a single data frame
filtered_vinah_contact <- bind_rows(vinah_contact)

# View the filtered data
print(filtered_vinah_contact)

# Close the file connection
close(con)

I was expecting 2 rows but it returned a blank tibble. What am i doing wrong?

Answer 1

You need to create a callback function object from filter_chunk using DataFrameCallback$new:

vinah_contact <- readr::read_csv_chunked(con, callback = DataFrameCallback$new(filter_chunk), 
                                         chunk_size = chunk_size, 
                                         col_types = cols(new_patient_id = col_character(), date = col_character()))

Answer 2

We can use findstr which comes with Windows (or the corresponding grep on Linux) to extract the desired lines. Alternative patterns are separated by spaces. We have assumed that the header line and lines with a minus followed by a 2 digit year between 19 and 24 inclusive are to be kept and all others rejected. Only the surviving lines are ever read into R -- the others are removed by findstr before they ever reach R so this should be relatively efficient.

library(readr)

cmd <- 'findstr "date -19 -2[0-4]" "C:/Users/vidnguq/Downloads/r test data.csv"'
read_csv(pipe(cmd))

Alternative

This alternative is similar but it checks that the pattern is at the end of the line (via /e) rather than checking that the year follows a minus sign. It is not quite as robust as the first approach since findstr when used with /e requires Windows/DOS line endings. In contrast the first alternative above works with either type of line endings. To use this approach with a file having UNIX line endings first convert it

cmd <- 'findstr /e "date 19 2[0-4]" "C:/Users/vidnguq/Downloads/r test data.csv"'
read_csv(pipe(cmd))

or use grep from Rtools (which works with both Windows/DOS and UNIX line endings). Lines matching any of the -e arguments are returned. This also works in Linux possibly with minor quoting changes depending on your shell.

cmd <- 'grep -e "date" -e "-19" -e "-2[0-4]" myfile.csv'
read_csv(pipe(cmd))

Update

Improved regular expression, added clarifications and the Alternative and Update sections.