{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7921029,"sourceType":"competition"}],"dockerImageVersionId":30684,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# <b>Objectives</b>","metadata":{}},{"cell_type":"markdown","source":"* This notebook aims to introduce duckdb, a library where we can run SQLs in memory. See too [DuckDB Page](https://duckdb.org/)","metadata":{"execution":{"iopub.status.busy":"2024-04-14T19:25:48.029597Z","iopub.execute_input":"2024-04-14T19:25:48.030057Z","iopub.status.idle":"2024-04-14T19:25:48.037776Z","shell.execute_reply.started":"2024-04-14T19:25:48.030020Z","shell.execute_reply":"2024-04-14T19:25:48.036245Z"}}},{"cell_type":"markdown","source":"# <b>Install DuckDB</b>","metadata":{}},{"cell_type":"code","source":"!pip install duckdb","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2024-04-15T00:23:04.101210Z","iopub.status.idle":"2024-04-15T00:23:20.738850Z","shell.execute_reply.started":"2024-04-15T00:23:04.101796Z","shell.execute_reply":"2024-04-15T00:23:20.736760Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b>Imports</b>","metadata":{}},{"cell_type":"code","source":"import polars as pl\nimport numpy as np\nimport pandas as pd\nimport lightgbm as lgb\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import roc_auc_score \nimport duckdb\nimport sys\nfrom colorama import Style, Fore\nfrom pathlib import Path\nimport subprocess\nfrom glob import glob\nfrom datetime import datetime\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport os\n\nimport warnings\nwarnings.filterwarnings('ignore')\n","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-04-15T00:23:20.741867Z","iopub.execute_input":"2024-04-15T00:23:20.742310Z","iopub.status.idle":"2024-04-15T00:23:20.751954Z","shell.execute_reply.started":"2024-04-15T00:23:20.742274Z","shell.execute_reply":"2024-04-15T00:23:20.750696Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b>Path of the files </b>","metadata":{}},{"cell_type":"code","source":"ROOT            = Path(\"/kaggle/input/home-credit-credit-risk-model-stability\")\n\nTRAIN_DIR       = ROOT / \"parquet_files\" / \"train\"\nTEST_DIR        = ROOT / \"parquet_files\" / \"test\"","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:23:20.753805Z","iopub.execute_input":"2024-04-15T00:23:20.754203Z","iopub.status.idle":"2024-04-15T00:23:20.767670Z","shell.execute_reply.started":"2024-04-15T00:23:20.754155Z","shell.execute_reply":"2024-04-15T00:23:20.766176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b>Load data with DuckDB</b>","metadata":{}},{"cell_type":"markdown","source":"* Connect to DuckDB.\n* The database = :memory parameter means that everything will be executed in memory, without any data persistence on disk","metadata":{}},{"cell_type":"code","source":"conn = duckdb.connect(database=':memory:', read_only=False)","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:23:20.772525Z","iopub.execute_input":"2024-04-15T00:23:20.772904Z","iopub.status.idle":"2024-04-15T00:23:20.790595Z","shell.execute_reply.started":"2024-04-15T00:23:20.772875Z","shell.execute_reply":"2024-04-15T00:23:20.789656Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# uploading only the first 4 files for teaching purposes\nLIMIT_TABLE_LOAD = 3\nparquet_files = [file for file in os.listdir(TRAIN_DIR) if file.endswith('.parquet')]\nparquet_files.sort()\n\nfor i,file in enumerate(parquet_files):\n    table_name = os.path.splitext(file)[0] \n    conn.sql(f\"CREATE TABLE IF NOT EXISTS {table_name} AS SELECT * FROM parquet_scan('{os.path.join(TRAIN_DIR, file)}')\");\n    if i == LIMIT_TABLE_LOAD:\n        break;","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:27:50.956310Z","iopub.execute_input":"2024-04-15T00:27:50.956778Z","iopub.status.idle":"2024-04-15T00:27:50.982778Z","shell.execute_reply.started":"2024-04-15T00:27:50.956743Z","shell.execute_reply":"2024-04-15T00:27:50.981376Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b>Tables overview</b>","metadata":{}},{"cell_type":"markdown","source":"* checking the number of tables and names.","metadata":{}},{"cell_type":"code","source":"conn.sql('show tables')","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:27:53.044908Z","iopub.execute_input":"2024-04-15T00:27:53.046021Z","iopub.status.idle":"2024-04-15T00:27:53.077781Z","shell.execute_reply.started":"2024-04-15T00:27:53.045962Z","shell.execute_reply":"2024-04-15T00:27:53.072067Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* Now we will check the first 3 rows of each table, in pandas it is the equivalent of the .head() function. But first, to simplify things, we can export the data from duckdb to a pandas dataframe, using to_df().\n* Now let's check the first 3 rows of each table, in pandas it is equivalent to the .head() function. But first, to simplify things, we can export the duckdb data to a pandas dataframe using to_df().\n* And to have the equivalent of .head(), in duckdb we use .limit(X), where X is the number of lines to be displayed.","metadata":{}},{"cell_type":"code","source":"for tab in conn.sql('show tables').to_df()['name'].values:\n    # viewing the first 5 rows of the table\n    print(f\"{Style.BRIGHT}{Fore.GREEN}Table: {tab}{Style.NORMAL}{Fore.RESET}\\n{conn.sql('select * from {}'.format(tab)).limit(5)}\")","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:27:55.750044Z","iopub.execute_input":"2024-04-15T00:27:55.751415Z","iopub.status.idle":"2024-04-15T00:27:55.826674Z","shell.execute_reply.started":"2024-04-15T00:27:55.751347Z","shell.execute_reply":"2024-04-15T00:27:55.824860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* As we can quickly see, we have the 'case_id' column in common between the tables, which we will use later to make queries.\n","metadata":{}},{"cell_type":"markdown","source":"# <b>Print Values</b>","metadata":{}},{"cell_type":"markdown","source":"* One way to also display the values on the screen is through .show()","metadata":{}},{"cell_type":"code","source":"conn.sql('select * from train_base').show()","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:27:58.605791Z","iopub.execute_input":"2024-04-15T00:27:58.606424Z","iopub.status.idle":"2024-04-15T00:27:58.617667Z","shell.execute_reply.started":"2024-04-15T00:27:58.606377Z","shell.execute_reply":"2024-04-15T00:27:58.615756Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* This has some parameters, such as the maximum number of lines to be displayed.","metadata":{}},{"cell_type":"code","source":"conn.sql('select * from train_applprev_1_1').show(max_rows=4)","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:28:00.581932Z","iopub.execute_input":"2024-04-15T00:28:00.582454Z","iopub.status.idle":"2024-04-15T00:28:00.620878Z","shell.execute_reply.started":"2024-04-15T00:28:00.582417Z","shell.execute_reply":"2024-04-15T00:28:00.619206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b>Data Types</b>","metadata":{}},{"cell_type":"markdown","source":"* so we don't have to write that expression again to load the tables, I will create a list to store their names.","metadata":{}},{"cell_type":"code","source":"table_list = conn.sql('show tables').to_df()['name'].to_list()","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:28:02.413985Z","iopub.execute_input":"2024-04-15T00:28:02.415721Z","iopub.status.idle":"2024-04-15T00:28:02.445191Z","shell.execute_reply.started":"2024-04-15T00:28:02.415647Z","shell.execute_reply":"2024-04-15T00:28:02.444177Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* As in pandas, where we have .dtypes, in SQL we can do the same with describe.","metadata":{"execution":{"iopub.status.busy":"2024-04-14T23:15:58.351525Z","iopub.execute_input":"2024-04-14T23:15:58.351950Z","iopub.status.idle":"2024-04-14T23:15:58.359957Z","shell.execute_reply.started":"2024-04-14T23:15:58.351918Z","shell.execute_reply":"2024-04-14T23:15:58.358367Z"}}},{"cell_type":"code","source":"for tab in table_list:\n    conn.sql('DESCRIBE {}'.format(tab)).show(max_rows=80);","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:28:05.661073Z","iopub.execute_input":"2024-04-15T00:28:05.662045Z","iopub.status.idle":"2024-04-15T00:28:05.682597Z","shell.execute_reply.started":"2024-04-15T00:28:05.661999Z","shell.execute_reply":"2024-04-15T00:28:05.680571Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# <b>Operations</b>","metadata":{}},{"cell_type":"markdown","source":"## Aggregate","metadata":{}},{"cell_type":"markdown","source":" * It is common to have to perform aggregation operations, such as sum, minimum value, among others.","metadata":{}},{"cell_type":"code","source":"res = conn.sql('select * from train_applprev_1_0')\nres.aggregate(\"case_id % 1 AS g, sum(annuity_853A), min(annuity_853A), max(annuity_853A), avg(annuity_853A)\")","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:38:26.908058Z","iopub.execute_input":"2024-04-15T00:38:26.909544Z","iopub.status.idle":"2024-04-15T00:38:27.024254Z","shell.execute_reply.started":"2024-04-15T00:38:26.909490Z","shell.execute_reply":"2024-04-15T00:38:27.023061Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Filter","metadata":{}},{"cell_type":"markdown","source":"* filters according to some condition.","metadata":{}},{"cell_type":"code","source":"res = conn.sql('select * from train_applprev_1_0')\nres.filter(\"annuity_853A > 100\").show()","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:42:35.512920Z","iopub.execute_input":"2024-04-15T00:42:35.513436Z","iopub.status.idle":"2024-04-15T00:42:35.551473Z","shell.execute_reply.started":"2024-04-15T00:42:35.513401Z","shell.execute_reply":"2024-04-15T00:42:35.550280Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Join","metadata":{}},{"cell_type":"markdown","source":"* It is common to have to do it together between tables, the famous .merge in pandas.","metadata":{}},{"cell_type":"code","source":"conn.sql('show tables')","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:57:42.354829Z","iopub.execute_input":"2024-04-15T00:57:42.355783Z","iopub.status.idle":"2024-04-15T00:57:42.387292Z","shell.execute_reply.started":"2024-04-15T00:57:42.355722Z","shell.execute_reply":"2024-04-15T00:57:42.385668Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"conn.sql(\"SELECT * FROM train_base\").limit(1)","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:58:30.051532Z","iopub.execute_input":"2024-04-15T00:58:30.052070Z","iopub.status.idle":"2024-04-15T00:58:30.066755Z","shell.execute_reply.started":"2024-04-15T00:58:30.052034Z","shell.execute_reply":"2024-04-15T00:58:30.065568Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"conn.sql(\"SELECT * FROM train_applprev_1_0\").limit(1)","metadata":{"execution":{"iopub.status.busy":"2024-04-15T00:58:42.323672Z","iopub.execute_input":"2024-04-15T00:58:42.324328Z","iopub.status.idle":"2024-04-15T00:58:42.353208Z","shell.execute_reply.started":"2024-04-15T00:58:42.324289Z","shell.execute_reply":"2024-04-15T00:58:42.352182Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tb1 = conn.sql(\"SELECT case_id, date_decision, month FROM train_base\").set_alias(\"tb1\")\ntb2 = conn.sql(\"SELECT case_id, annuity_853A from train_applprev_1_0\").set_alias(\"tb2\")\ntb1.join(tb2, \"tb1.case_id  = tb2.case_id\").show()\n","metadata":{"execution":{"iopub.status.busy":"2024-04-15T01:00:04.148599Z","iopub.execute_input":"2024-04-15T01:00:04.149004Z","iopub.status.idle":"2024-04-15T01:00:04.287391Z","shell.execute_reply.started":"2024-04-15T01:00:04.148971Z","shell.execute_reply":"2024-04-15T01:00:04.286136Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Order","metadata":{}},{"cell_type":"markdown","source":"* this is the same as pandas sort_values()","metadata":{}},{"cell_type":"code","source":"# decrescent\nres = conn.sql(\"SELECT * FROM train_base\")\nres.order(\"WEEK_NUM desc\").limit(5).show()","metadata":{"execution":{"iopub.status.busy":"2024-04-15T01:02:22.667349Z","iopub.execute_input":"2024-04-15T01:02:22.668590Z","iopub.status.idle":"2024-04-15T01:02:22.722446Z","shell.execute_reply.started":"2024-04-15T01:02:22.668543Z","shell.execute_reply":"2024-04-15T01:02:22.721629Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# ascending\nres = conn.sql(\"SELECT * FROM train_base\")\nres.order(\"WEEK_NUM asc\").limit(5).show()","metadata":{"execution":{"iopub.status.busy":"2024-04-15T01:02:37.301521Z","iopub.execute_input":"2024-04-15T01:02:37.302160Z","iopub.status.idle":"2024-04-15T01:02:37.328525Z","shell.execute_reply.started":"2024-04-15T01:02:37.302123Z","shell.execute_reply":"2024-04-15T01:02:37.327315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# To be continued..\nUpdates coming soon.","metadata":{}}]}