{"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":"# Table of Contents\n* [Import Data](#import)\n* [EDA](#eda)\n    * [Column Overview](#eda_overview)\n    * [Basic Stats](#eda_basic)\n    * [Distributions](#eda_dist)\n    * [Correlation of Features](#eda_corr)\n    * [Mean/Median Calcs](#eda_mean)\n* [Evaluate Missings](#miss)\n    * [Column-wise](#miss_col)\n    * [Row-wise](#miss_row)\n    * [Missing indicators and dependencies](#miss_ind)\n* [Baselines](#baseline)\n    * [Mean Imputation](#base_mean)\n    * [Median_Imputation](#base_median)","metadata":{}},{"cell_type":"code","source":"# packages\nsuppressWarnings(library(tidyverse))\nsuppressWarnings(library(Hmisc))","metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-08-12T06:50:55.136026Z","iopub.execute_input":"2022-08-12T06:50:55.137915Z","iopub.status.idle":"2022-08-12T06:50:59.330044Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# configure plot size\nplot_w <- 8\nplot_h <- 5\noptions(repr.plot.width = plot_w, repr.plot.height = plot_h)\n\n# configure data frame display\noptions(repr.matrix.max.cols=500)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:50:59.335956Z","iopub.execute_input":"2022-08-12T06:50:59.490755Z","iopub.status.idle":"2022-08-12T06:50:59.535525Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='import'></a>\n# Import Data","metadata":{}},{"cell_type":"code","source":"# read data\nt1 <- Sys.time()\ndf <- read_delim('../input/tabular-playground-series-jun-2022/data.csv', delim=',')\nt2 <- Sys.time()\nprint(t2-t1)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:51:05.024648Z","iopub.execute_input":"2022-08-12T06:51:05.026877Z","iopub.status.idle":"2022-08-12T06:51:19.519417Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# conversions\ndf <- as.data.frame(df)\ndf$row_id <- as.factor(df$row_id)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:51:19.521537Z","iopub.execute_input":"2022-08-12T06:51:19.522769Z","iopub.status.idle":"2022-08-12T06:51:23.286921Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='eda'></a>\n# EDA","metadata":{}},{"cell_type":"markdown","source":"<a id='eda_overview'></a>\n## Column Overview","metadata":{}},{"cell_type":"code","source":"# column overview\nstr(df, list.len=1000)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:51:29.127757Z","iopub.execute_input":"2022-08-12T06:51:29.129335Z","iopub.status.idle":"2022-08-12T06:51:29.201452Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='eda_basic'></a>\n## Basic Stats","metadata":{}},{"cell_type":"code","source":"# basic stats\nfeatures <- setdiff(colnames(df),'row_id')\nsummary(df[features])","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:51:33.508201Z","iopub.execute_input":"2022-08-12T06:51:33.509734Z","iopub.status.idle":"2022-08-12T06:51:38.930827Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='eda_dist'></a>\n## Distributions","metadata":{}},{"cell_type":"code","source":"# plot all distributions\nfor (f in features) {\n  if (stringr::str_detect(f, 'F_2_')) {\n    plot(as.factor(df[,f]), col='blue', main=f, xlab=f)\n    grid()\n  } else {\n    hist(df[,f], main=f, col='lightblue', xlab=f)\n    grid()\n  }\n}","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:52:05.813664Z","iopub.execute_input":"2022-08-12T06:52:05.815493Z","iopub.status.idle":"2022-08-12T06:52:33.823172Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='eda_corr'></a>\n## Correlation of features","metadata":{}},{"cell_type":"code","source":"# calc correlations\ncorr_pearson <- cor(df[features], method='pearson',\n                    use = 'pairwise.complete.obs')","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:52:49.310624Z","iopub.execute_input":"2022-08-12T06:52:49.312407Z","iopub.status.idle":"2022-08-12T06:53:09.841214Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot correlation matrix\noptions(repr.plot.width = 16, repr.plot.height = 16)\ncorrplot::corrplot(corr_pearson)\noptions(repr.plot.width = plot_w, repr.plot.height = plot_h)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:53:09.843520Z","iopub.execute_input":"2022-08-12T06:53:09.844830Z","iopub.status.idle":"2022-08-12T06:53:10.629754Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Zoom in:","metadata":{}},{"cell_type":"code","source":"# show F2 block only\nfeatures_f2 <- paste0('F_2_',0:24)\ncor_p_f2 <- cor(df[features_f2], method='pearson',\n                use = 'pairwise.complete.obs')\n\ncorrplot::corrplot(cor_p_f2)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:53:10.631911Z","iopub.execute_input":"2022-08-12T06:53:10.633178Z","iopub.status.idle":"2022-08-12T06:53:12.786970Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# remove diagonal to get better view\ncor_p_f2_d <- cor_p_f2\ndiag(cor_p_f2_d) <- 0\ncorrplot::corrplot(cor_p_f2_d, is.corr = FALSE)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:53:12.789234Z","iopub.execute_input":"2022-08-12T06:53:12.790518Z","iopub.status.idle":"2022-08-12T06:53:12.986858Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# show F4 block only\nfeatures_f4 <- paste0('F_4_',0:14)\ncor_p_f4 <- cor(df[features_f4], method='pearson',\n                use = 'pairwise.complete.obs')\n\ncorrplot::corrplot(cor_p_f4)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:53:17.507053Z","iopub.execute_input":"2022-08-12T06:53:17.508546Z","iopub.status.idle":"2022-08-12T06:53:18.538973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Plot a few examples:","metadata":{}},{"cell_type":"code","source":"c <- cor(df$F_2_8, df$F_2_14, use='pairwise.complete.obs')\nmy_title <- paste0('Correlation = ', round(c,4))\nsmoothScatter(df$F_2_8, df$F_2_14, main=my_title); grid()","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:54:03.791975Z","iopub.execute_input":"2022-08-12T06:54:03.793529Z","iopub.status.idle":"2022-08-12T06:54:04.372164Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"c <- cor(df$F_2_8, df$F_2_0, use='pairwise.complete.obs')\nmy_title <- paste0('Correlation = ', round(c,4))\nsmoothScatter(df$F_2_8, df$F_2_0, main=my_title); grid()","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:54:30.552382Z","iopub.execute_input":"2022-08-12T06:54:30.557232Z","iopub.status.idle":"2022-08-12T06:54:31.143669Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"c <- cor(df$F_4_8, df$F_4_11, use='pairwise.complete.obs')\nmy_title <- paste0('Correlation = ', round(c,4))\nsmoothScatter(df$F_4_8, df$F_4_11, main=my_title); grid()","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:54:34.302980Z","iopub.execute_input":"2022-08-12T06:54:34.305173Z","iopub.status.idle":"2022-08-12T06:54:34.921419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='eda_mean'></a>\n## Mean/Median calcs","metadata":{}},{"cell_type":"markdown","source":"### Calc mean for each feature","metadata":{}},{"cell_type":"code","source":"mean_ignore_na <- function(x) {\n  mean(x, na.rm = TRUE)\n}\n\nmean_stats <- apply(df[features], 2, mean_ignore_na)\nprint(mean_stats)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:54:41.403254Z","iopub.execute_input":"2022-08-12T06:54:41.404996Z","iopub.status.idle":"2022-08-12T06:54:45.284600Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot means\noptions(repr.plot.width = 10, repr.plot.height = 20)\nbarplot(height=mean_stats, names.arg = names(mean_stats),\n        horiz = T, las=2, col='orange',\n        main='Means')\noptions(repr.plot.width = plot_w, repr.plot.height = plot_h)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:04.704196Z","iopub.execute_input":"2022-08-12T06:56:04.706435Z","iopub.status.idle":"2022-08-12T06:56:04.934832Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Calc median for each feature","metadata":{}},{"cell_type":"code","source":"median_ignore_na <- function(x) {\n  median(x, na.rm = TRUE)\n}\n\nmedian_stats <- apply(df[features], 2, median_ignore_na)\nprint(median_stats)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:54:51.660667Z","iopub.execute_input":"2022-08-12T06:54:51.662199Z","iopub.status.idle":"2022-08-12T06:54:56.701487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot medians\noptions(repr.plot.width = 10, repr.plot.height = 20)\nbarplot(height=median_stats, names.arg = names(median_stats),\n        horiz = T, las=2, col='orange',\n        main='Medians')\noptions(repr.plot.width = plot_w, repr.plot.height = plot_h)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:13.106895Z","iopub.execute_input":"2022-08-12T06:56:13.109988Z","iopub.status.idle":"2022-08-12T06:56:13.336885Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='miss'></a>\n# Evaluate Missings","metadata":{}},{"cell_type":"markdown","source":"<a id='miss_col'></a>\n## Column-wise","metadata":{}},{"cell_type":"code","source":"# missing count / percentage function\np_miss <- function(x) {\n  return(sum(is.na(x)) / length(x))\n}","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:55:03.833094Z","iopub.execute_input":"2022-08-12T06:55:03.834574Z","iopub.status.idle":"2022-08-12T06:55:03.848710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# apply missing count function to all columns\nmiss_stats <- apply(df[features], 2, p_miss)\nprint(miss_stats)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:55:05.351191Z","iopub.execute_input":"2022-08-12T06:55:05.352738Z","iopub.status.idle":"2022-08-12T06:55:08.009494Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot missing ratios\noptions(repr.plot.width = 10, repr.plot.height = 20)\nbarplot(height=miss_stats, names.arg = names(miss_stats),\n        horiz = TRUE, las=2, col='orange',\n        main='Missing Ratios')\ngrid()\noptions(repr.plot.width = plot_w, repr.plot.height = plot_h)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:20.869188Z","iopub.execute_input":"2022-08-12T06:56:20.873570Z","iopub.status.idle":"2022-08-12T06:56:21.119816Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# show stats for missing ratios\nsummary(miss_stats[miss_stats!=0])","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:55:33.457752Z","iopub.execute_input":"2022-08-12T06:55:33.459592Z","iopub.status.idle":"2022-08-12T06:55:33.478016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### We observe that the F_2_... features have no missings. The missing percentage for the other features is in a small band between 1.796% and 1.850%.","metadata":{"execution":{"iopub.status.busy":"2022-06-03T08:58:51.351864Z","iopub.execute_input":"2022-06-03T08:58:51.354286Z","iopub.status.idle":"2022-06-03T08:58:51.368865Z"}}},{"cell_type":"code","source":"# let's store the complete features for later\nfeatures_complete <- paste0('F_2_', 0:24)\nfeatures_complete","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:55:36.095175Z","iopub.execute_input":"2022-08-12T06:55:36.097398Z","iopub.status.idle":"2022-08-12T06:55:36.117196Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='miss_row'></a>\n## Row-wise","metadata":{}},{"cell_type":"code","source":"# apply missing count function to all rows\nmiss_stats_row <- apply(df[features], 1, p_miss)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:55:39.321400Z","iopub.execute_input":"2022-08-12T06:55:39.322843Z","iopub.status.idle":"2022-08-12T06:55:46.355465Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot using categorical plot as we have only a few different values\nplot(as.factor(miss_stats_row), col='orange', main='Missing ratio for rows')\ngrid()","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:32.661410Z","iopub.execute_input":"2022-08-12T06:56:32.663825Z","iopub.status.idle":"2022-08-12T06:56:34.228802Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# filter complete rows\ncomplete_rows <- which(miss_stats_row==0)\nn_complete_rows <- length(complete_rows)\ncat('Complete rows: ', n_complete_rows)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:40.019757Z","iopub.execute_input":"2022-08-12T06:56:40.022527Z","iopub.status.idle":"2022-08-12T06:56:40.053390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# show examples\ndf[complete_rows[1:10],]","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:40.716668Z","iopub.execute_input":"2022-08-12T06:56:40.718190Z","iopub.status.idle":"2022-08-12T06:56:40.859392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='miss_ind'></a>\n## Missing indicators and dependencies","metadata":{}},{"cell_type":"markdown","source":"#### Add missing indicators to data frame and check for correlations between missings:","metadata":{}},{"cell_type":"code","source":"# add missing indicators to data frame\nfeatures_miss <- setdiff(features, features_complete)\n\nmissing_indicators <- c()\nfor (f in features_miss) {\n  new_var <- paste0(f, '_miss')\n  df[,new_var] <- ifelse(is.na(df[,f]),1,0)\n  missing_indicators <- c(missing_indicators, new_var)\n}","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:45.152041Z","iopub.execute_input":"2022-08-12T06:56:45.153917Z","iopub.status.idle":"2022-08-12T06:56:48.469567Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# correlation of missing indicators\ncorr_miss <- cor(df[missing_indicators], method='pearson')\n\noptions(repr.plot.width = 16, repr.plot.height = 16)\ncorrplot::corrplot(corr_miss)\noptions(repr.plot.width = plot_w, repr.plot.height = plot_h)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:53.084595Z","iopub.execute_input":"2022-08-12T06:56:53.086378Z","iopub.status.idle":"2022-08-12T06:56:57.140192Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# remove diagonal values to see details\ndiag(corr_miss) <- 0\noptions(repr.plot.width = 16, repr.plot.height = 16)\ncorrplot::corrplot(corr_miss, is.corr=FALSE)\noptions(repr.plot.width = plot_w, repr.plot.height = plot_h)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:56:57.142622Z","iopub.execute_input":"2022-08-12T06:56:57.144035Z","iopub.status.idle":"2022-08-12T06:56:57.910424Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Check for each feature pair how many common missing occur:","metadata":{}},{"cell_type":"code","source":"# count common missing for each column pair\nmiss_mat <- as.matrix(df[missing_indicators])\nmiss_pair <- t(miss_mat) %*% miss_mat # matrix product ~ scalar product of each column pair\nrm(miss_mat)\n# diagonals of product matrix are number of missings for each variable\ndiag(miss_pair) <- 0 # remove diagonal values to see the details\n# plot\noptions(repr.plot.width = 16, repr.plot.height = 16)\ncorrplot::corrplot(miss_pair, is.corr = FALSE)\noptions(repr.plot.width = plot_w, repr.plot.height = plot_h)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:57:06.929651Z","iopub.execute_input":"2022-08-12T06:57:06.931588Z","iopub.status.idle":"2022-08-12T06:57:09.318632Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat('Common missing count ranges from', min(miss_pair[miss_pair!=0]), 'to', max(miss_pair),'.\\n')\ncat('In average we have', mean(miss_stats[miss_stats!=0] * 1e6), 'missings per feature.')","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:57:17.309499Z","iopub.execute_input":"2022-08-12T06:57:17.310937Z","iopub.status.idle":"2022-08-12T06:57:17.331228Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### We can observe that common missings are quite rare here...","metadata":{}},{"cell_type":"code","source":"# garbage collection\ninvisible(gc())","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:57:20.194856Z","iopub.execute_input":"2022-08-12T06:57:20.196358Z","iopub.status.idle":"2022-08-12T06:57:20.628731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='baseline'></a>\n# Baselines","metadata":{}},{"cell_type":"markdown","source":"<a id='base_mean'></a>\n## Mean Imputation","metadata":{}},{"cell_type":"code","source":"# put together submission file\ndf_sub <- read_delim('../input/tabular-playground-series-jun-2022/sample_submission.csv', delim=',')\ndf_sub <- as.data.frame(df_sub)\nhead(df_sub,10)","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:57:26.391971Z","iopub.execute_input":"2022-08-12T06:57:26.394942Z","iopub.status.idle":"2022-08-12T06:57:27.630699Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# fill value for each row with corresponding mean\nfor (f in setdiff(features,features_complete)) {\n  df_sub[str_detect(df_sub$`row-col`, f), 'value'] <- mean_stats[f]\n}","metadata":{"execution":{"iopub.status.busy":"2022-08-12T06:57:47.823614Z","iopub.execute_input":"2022-08-12T06:57:47.825255Z","iopub.status.idle":"2022-08-12T06:58:04.849126Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"head(df_sub,10)","metadata":{"_kg_hide-output":false,"execution":{"iopub.status.busy":"2022-08-12T06:58:04.852976Z","iopub.execute_input":"2022-08-12T06:58:04.854561Z","iopub.status.idle":"2022-08-12T06:58:04.878319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"write_delim(df_sub, file='sub_base_mean.csv', delim=',')","metadata":{"execution":{"iopub.status.busy":"2022-06-05T17:09:13.444086Z","iopub.execute_input":"2022-06-05T17:09:13.445944Z","iopub.status.idle":"2022-06-05T17:09:13.659949Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id='base_median'></a>\n## Median Imputation","metadata":{}},{"cell_type":"code","source":"# reload template\ndf_sub <- read_delim('../input/tabular-playground-series-jun-2022/sample_submission.csv', delim=',')\ndf_sub <- as.data.frame(df_sub)","metadata":{"execution":{"iopub.status.busy":"2022-06-05T17:09:13.662869Z","iopub.execute_input":"2022-06-05T17:09:13.664477Z","iopub.status.idle":"2022-06-05T17:09:14.292022Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# fill value for each row with corresponding mean\nfor (f in setdiff(features,features_complete)) {\n  df_sub[str_detect(df_sub$`row-col`, f), 'value'] <- median_stats[f]\n}","metadata":{"execution":{"iopub.status.busy":"2022-06-05T17:09:14.294755Z","iopub.execute_input":"2022-06-05T17:09:14.296364Z","iopub.status.idle":"2022-06-05T17:09:32.171886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"head(df_sub,10)","metadata":{"execution":{"iopub.status.busy":"2022-06-05T17:09:32.175814Z","iopub.execute_input":"2022-06-05T17:09:32.177695Z","iopub.status.idle":"2022-06-05T17:09:32.204506Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"write_delim(df_sub, file='sub_base_median.csv', delim=',')","metadata":{"execution":{"iopub.status.busy":"2022-06-05T17:09:32.208154Z","iopub.execute_input":"2022-06-05T17:09:32.209915Z","iopub.status.idle":"2022-06-05T17:09:32.363473Z"},"trusted":true},"execution_count":null,"outputs":[]}]}