Read multiple files into DuckDB in R from CSV, with new variable indicating year from filename

Matt L. 0
Viewed 64

I have several (8) large files (1M rows each) with the same variables/format saved individually by year. I would like to save to a single table using the duckdb database format in R. The duck_read_csv() command does this nicely.

The problem: there is no variable indicating "year" using this method, so the trend for repeated measurements is lost. My DBI and SQL knowledge is limited, so there's probably an easy way to do this, but I'm stuck here (simple example demonstrating issue below):

library(duckdb)
con <- dbConnect(duckdb())

# example year 1 data
data <- data.frame(id = 1:3, b = letters[1:3]) # year = 1
path <- tempfile(fileext = ".csv")
write.csv(data, path, row.names = FALSE)

# example year 2 data
data2 <- data.frame(id = 1:3, b = letters[4:6]) # year = 2
path2 <- tempfile(fileext = ".csv")
write.csv(data2, path2, row.names = FALSE)

duckdb_read_csv(con, "data", files = c(path, path2)) # data is appended

which yields the following- data is appended by row but i need a variable to indicate "year":

dbReadTable(con, "data")
  id b
1  1 a
2  2 b
3  3 c
4  1 d
5  2 e
6  3 f

Is there a way to create a new variable during this process, or is it better to create a table for

duckdb sqlite r
2 Answers

One approach could be to use purrr::map_dfr + readr::read_csv for the reading, which allows you to assign an "id" column based on names assigned to the file paths, and then register that as a duckdb table:

library(dplyr)
purrr::map_dfr(c(year01 = path, 
                 year02 = path2),
               ~readr::read_csv(.x), 
               .id = "year") %>%
  duckdb_register(con, "data", .)

Result

tbl(con, "data") %>%
  collect()
# A tibble: 6 × 3
  year      id b    
  <chr>  <dbl> <chr>
1 year01     1 a    
2 year01     2 b    
3 year01     3 c    
4 year02     1 d    
5 year02     2 e    
6 year02     3 f
Jon Spring0

I modified answer by Jon to solve my issue (thanks Jon). Files were too large to read into memory all at once (~20M rows each), so I iterated over them one at a time to transfer into the database (~198M rows total).

I also extracted the "year" from the filename using gsub into a separate variable yrs.

library(dplyr)
library(duckdb)
con <- dbConnect(duckdb())

# example year 1 data
data <- data.frame(id = 1:3, b = letters[1:3]) # year = 1
path1 <- tempfile(fileext = ".csv")
write.csv(data, path1, row.names = FALSE)

# example year 2 data
data2 <- data.frame(id = 1:3, b = letters[4:6]) # year = 2
path2 <- tempfile(fileext = ".csv")
write.csv(data2, path2, row.names = FALSE)

paths <- c(path1, path2)
yrs <- c("year1", "year2")

purrr::walk2(paths, yrs,
      ~{
        readr::read_csv(.x, n_max = Inf) %>% # read 1 file into memory at a time
          mutate(year = .y) %>% 
          rename_all(. %>% tolower()) %>%    # i really prefer lower case variable names
          duckdb::dbWriteTable(conn = con, name =  "data", value = .,  append =T) 
      })
Matt L.0
Related