{"metadata":{"kernelspec":{"name":"ir","display_name":"R","language":"R"},"language_info":{"name":"R","codemirror_mode":"r","pygments_lexer":"r","mimetype":"text/x-r-source","file_extension":".r","version":"4.0.5"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"***\n\n# Get in the game R people! 3 weeks left! This notebook (and dataset) might help!\n\n***\n\n### In this notebook:\n\n    - Produce pre-processed dataset that can be completely read into memory using readRDS()\n    \n    - I use a combination of public feather files (see radar) and and duckdb \n    \n    - Test dataset to come (it will have to be divided into a few chunks unfortunately, even with this approach...)\n\n\n### * Duck DB ref:\n\n          https://www.kaggle.com/code/igjit1/fast-and-less-memory-data-processing-with-duckdb\n\n\n***\n\n\n\n### Note... I don't usually publish datasets so if you want access but I didn't publish it correctly let me know\n\n\n\n***\n","metadata":{}},{"cell_type":"code","source":"# Process feather data and save in RDS for later use\n\nsuppressPackageStartupMessages(library(data.table)) \nsuppressPackageStartupMessages(library(tidyverse))\nsuppressPackageStartupMessages(library(dtplyr)) #data.table with tidy syntax\nsuppressPackageStartupMessages(library(arrow))\n\ndir(\"..\")\nprint(\"--------------\")\n\nprint('available files...')\nlist.files(path = \"../input/amex-default-prediction\") %>% print()\nlist.files(path = \"../input/amex-data-integer-dtypes-parquet-format\") %>% print()\nprint(\"--------------\")\n\npqt_dir <- '../input/amex-data-integer-dtypes-parquet-format'\ncsv_dir <- '../input/amex-default-prediction'","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:11:24.027108Z","iopub.execute_input":"2022-08-02T17:11:24.282016Z","iopub.status.idle":"2022-08-02T17:11:24.500789Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# go get duckdb\nrepos <- \"https://packagemanager.rstudio.com/cran/__linux__/focal/latest\"\nua <- sprintf(\"R/%s R (%s)\", getRversion(), paste(getRversion(), R.version[\"platform\"], R.version[\"arch\"], R.version[\"os\"]))\ninstall.packages(\"duckdb\", dependencies = FALSE, repos = repos, headers = c(\"User-Agent\" = ua))\npackageVersion(\"duckdb\")","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:11:24.504421Z","iopub.execute_input":"2022-08-02T17:11:24.516685Z","iopub.status.idle":"2022-08-02T17:11:57.310519Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#function to process the data\nprocess_and_feature_engineer <- function(df) {\n  cat_features <- c(\"B_30\", \"B_38\", \"D_114\", \"D_116\", \"D_117\", \"D_120\", \"D_126\", \"D_63\", \"D_64\", \"D_66\", \"D_68\")\n  num_features <- setdiff(colnames(df), c(cat_features, \"customer_ID\", \"S_2\"))\n\n  df %>%\n    group_by(customer_ID) %>%\n    summarise(n = n(),\n              across({{ cat_features }}, list(first = first, last = last, nd = n_distinct)),\n              across({{ num_features }}, list(min = min, max = max, mean = mean, sd = sd, first = first, last = last)))   \n}","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:11:57.313416Z","iopub.execute_input":"2022-08-02T17:11:57.314828Z","iopub.status.idle":"2022-08-02T17:11:57.328308Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# pull in the training data\nt1 <- Sys.time()\ntrain_X <- arrow::read_parquet(\"../input/amex-data-integer-dtypes-parquet-format/train.parquet\", as_data_frame = FALSE)\nt2 <- Sys.time()\n\nprint('Time to to pull in training data (as .parquet)...')\ndifftime(t2,t1, units=\"secs\")\n\nprint(\"nrow,ncol in test data...\")\nnrow(train_X)\nncol(train_X)","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:11:57.331211Z","iopub.execute_input":"2022-08-02T17:11:57.333034Z","iopub.status.idle":"2022-08-02T17:12:07.198801Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc()","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:12:07.201028Z","iopub.execute_input":"2022-08-02T17:12:07.202466Z","iopub.status.idle":"2022-08-02T17:12:07.422769Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# process it\nt1 <- Sys.time()\n\ntrain_X <- train_X %>%\n  to_duckdb() %>%\n  process_and_feature_engineer %>%\n  collect\n\nt2 <- Sys.time()\n\nprint('Time to to process data with duckdb...')\ndifftime(t2,t1, units=\"secs\")","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:12:07.424841Z","iopub.execute_input":"2022-08-02T17:12:07.426066Z","iopub.status.idle":"2022-08-02T17:14:30.140220Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc()","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:30.142518Z","iopub.execute_input":"2022-08-02T17:14:30.143880Z","iopub.status.idle":"2022-08-02T17:14:30.345735Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# bottom line stats\nprint('Number of rows')\ntrain_X %>% nrow()\nprint('Number of IDs')\ntrain_X %>% distinct(customer_ID) %>% nrow()\nprint('Number of columns...')\nncol(train_X)","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:30.347916Z","iopub.execute_input":"2022-08-02T17:14:30.349208Z","iopub.status.idle":"2022-08-02T17:14:30.887786Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# pull in training labels\nt1 <- Sys.time()\ntrain_Y <- fread(\"../input/amex-default-prediction/train_labels.csv\") %>% as_tibble()\nt2 <- Sys.time()\n\nprint('Time to load training labels..')\ndifftime(t2,t1, units=\"secs\")\nprint('Number of rows')\ntrain_Y %>% nrow()\nprint('Number of IDs')\ntrain_Y %>% distinct(customer_ID) %>% nrow()\nprint(paste('columns in target data..',colnames(train_Y)))","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:30.890460Z","iopub.execute_input":"2022-08-02T17:14:30.891720Z","iopub.status.idle":"2022-08-02T17:14:31.655582Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc()","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:31.658996Z","iopub.execute_input":"2022-08-02T17:14:31.660541Z","iopub.status.idle":"2022-08-02T17:14:32.094674Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# join them -- be super careful with order of ids\ntrain_X <- left_join(train_Y,train_X,by=c(\"customer_ID\"))","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:32.096850Z","iopub.execute_input":"2022-08-02T17:14:32.098175Z","iopub.status.idle":"2022-08-02T17:14:34.368911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# bottom line stats (again)\nprint('Number of rows')\ntrain_X %>% nrow()\nprint('Number of IDs')\ntrain_X %>% distinct(customer_ID) %>% nrow()\nprint('Number of columns...')\nncol(train_X)","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:34.371799Z","iopub.execute_input":"2022-08-02T17:14:34.373060Z","iopub.status.idle":"2022-08-02T17:14:34.478327Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#column check\nprint('columns in training data..')\ncolnames(train_X)","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:34.480379Z","iopub.execute_input":"2022-08-02T17:14:34.481904Z","iopub.status.idle":"2022-08-02T17:14:34.510469Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#quick question\nobject.size(train_X)","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:34.512621Z","iopub.execute_input":"2022-08-02T17:14:34.513862Z","iopub.status.idle":"2022-08-02T17:14:34.570814Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# export preprocessed training data to .rds files\nsaveRDS( object=train_X, file=\"train_data_preproc.rds\", ascii=F)","metadata":{"execution":{"iopub.status.busy":"2022-08-02T17:14:34.573352Z","iopub.execute_input":"2022-08-02T17:14:34.574744Z","iopub.status.idle":"2022-08-02T17:18:29.318773Z"},"trusted":true},"execution_count":null,"outputs":[]}]}