Commit: c2a9e93444f3eed8593f1c8b239fed4913088c59 - myprojects-hg-reborn (hg)

Commit MetaInfo

• Revision: c2a9e93444f3eed8593f1c8b239fed4913088c59 (tree)
• Zeit: 2022-07-06 23:44:29
• Autor: Lorenzo Isella <lorenzo.isella@gmai...>
• Commiter: Lorenzo Isella

Log Message

I improved the function to convert a csv file to an sqlite database.

Ändern Zusammenfassung

• modified: R-codes/stat_lib.R

Diff

diff -r 9f0bc6a2077c -r c2a9e93444f3 R-codes/stat_lib.R
--- a/R-codes/stat_lib.R	Tue Jul 05 16:06:58 2022 +0200
+++ b/R-codes/stat_lib.R	Wed Jul 06 16:44:29 2022 +0200
@@ -4733,6 +4733,69 @@
 #' @importFrom readr read_delim read_delim_chunked
 #' @importFrom dplyr %>% select_if mutate_at
 #' @importFrom lubridate is.Date is.POSIXt
+## csv_to_sqlite <- function(csv_file, sqlite_file, table_name,
+##                           delim = ",",
+##                           pre_process_size = 1000, chunk_size = 50000,
+##                           show_progress_bar = TRUE, ...) {
+##     con <- dbConnect(SQLite(), dbname = sqlite_file)
+
+##     # read a first chunk of data to extract the colnames and types
+##     # to figure out the date and the datetime columns
+##     df <- read_delim(csv_file, delim = delim, n_max = pre_process_size, ...)
+##     date_cols <- df %>%
+##         select_if(is.Date) %>%
+##         colnames()
+##     datetime_cols <- df %>%
+##         select_if(is.POSIXt) %>%
+##         colnames()
+
+##     # write the first batch of lines to SQLITE table, converting dates to string
+##     # representation
+##     df <- df %>%
+##       mutate_at(.vars = date_cols, .funs = as.character.Date) %>%
+##       mutate_at(.vars = datetime_cols, .funs = as.character.POSIXt)
+##     dbWriteTable(con, table_name, df, overwrite = TRUE)
+
+##     # readr chunk functionality
+##     read_delim_chunked(
+##       csv_file,
+##       callback = append_to_sqlite(con = con, table_name = table_name,
+##                                   date_cols = date_cols,
+##                                   datetime_cols = datetime_cols),
+##       delim = delim,
+##       skip = pre_process_size, chunk_size = chunk_size,
+##       progress = show_progress_bar,
+##       col_names = colnames(df), ...)
+##     dbDisconnect(con)
+## }
+
+#' Callback function that appends new sections to the SQLite table.
+#' @param con A valid connection to SQLite database.
+#' @param table_name Name of the table to store the data table in the sqlite
+#'   database.
+#' @param date_cols Name of columns containing Date objects
+#' @param datetime_cols Name of columns containint POSIXt objects.
+#'
+#' @keywords internal
+## append_to_sqlite <- function(con, table_name,
+##                              date_cols, datetime_cols) {
+##   #' @param x Data.frame we are reading from.
+##   function(x, pos) {
+
+##     x <- as.data.frame(x)
+##     x <- x %>%
+##       mutate_at(.vars = date_cols, .funs = as.character.Date) %>%
+##       mutate_at(.vars = datetime_cols, .funs = as.character.POSIXt)
+##     # append data frame to table
+##     dbWriteTable(con, table_name, x, append = TRUE)
+
+##   }
+## }
+
+
+### A more modern version of the functions above
+
+
 csv_to_sqlite <- function(csv_file, sqlite_file, table_name,
                           delim = ",",
                           pre_process_size = 1000, chunk_size = 50000,
@@ -4742,18 +4805,29 @@
     # read a first chunk of data to extract the colnames and types
     # to figure out the date and the datetime columns
     df <- read_delim(csv_file, delim = delim, n_max = pre_process_size, ...)
-    date_cols <- df %>%
-        select_if(is.Date) %>%
+
+
+    date_cols <- df  |> 
+        select(where(is.Date)) |> 
         colnames()
-    datetime_cols <- df %>%
-        select_if(is.POSIXt) %>%
+    
+    datetime_cols <- df  |> 
+        select(where(is.POSIXt))  |> 
         colnames()
 
     # write the first batch of lines to SQLITE table, converting dates to string
     # representation
-    df <- df %>%
-      mutate_at(.vars = date_cols, .funs = as.character.Date) %>%
-      mutate_at(.vars = datetime_cols, .funs = as.character.POSIXt)
+    ## df <- df %>%
+    ##   mutate_at(.vars = date_cols, .funs = as.character.Date) %>%
+    ##   mutate_at(.vars = datetime_cols, .funs = as.character.POSIXt)
+
+
+    df <- df  |> 
+      mutate(across( all_of(date_cols), \(x)  as.character.Date(x)))  |> 
+      mutate(across(all_of(datetime_cols), \(x) as.character.POSIXt(x)))
+
+
+
     dbWriteTable(con, table_name, df, overwrite = TRUE)
 
     # readr chunk functionality
@@ -4769,23 +4843,19 @@
     dbDisconnect(con)
 }
 
-#' Callback function that appends new sections to the SQLite table.
-#' @param con A valid connection to SQLite database.
-#' @param table_name Name of the table to store the data table in the sqlite
-#'   database.
-#' @param date_cols Name of columns containing Date objects
-#' @param datetime_cols Name of columns containint POSIXt objects.
-#'
-#' @keywords internal
+
+
+
+
 append_to_sqlite <- function(con, table_name,
                              date_cols, datetime_cols) {
   #' @param x Data.frame we are reading from.
   function(x, pos) {
 
     x <- as.data.frame(x)
-    x <- x %>%
-      mutate_at(.vars = date_cols, .funs = as.character.Date) %>%
-      mutate_at(.vars = datetime_cols, .funs = as.character.POSIXt)
+    x <- x  |> 
+      mutate(across( all_of(date_cols), \(x) as.character.Date(x))) |> 
+      mutate(across( all_of(datetime_cols), \(x) as.character.POSIXt(x)))
     # append data frame to table
     dbWriteTable(con, table_name, x, append = TRUE)
 
@@ -4793,6 +4863,11 @@
 }
 
 
+
+
+
+
+
 #######################################################################
 #######################################################################
 #######################################################################