{"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":7602123,"sourceType":"competition"},{"sourceId":7708681,"sourceType":"datasetVersion","datasetId":4500884}],"dockerImageVersionId":30646,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# __Pyspark Offline__\n\n<br>\n\n## __Install Pyspark__","metadata":{}},{"cell_type":"code","source":"# Set enviroment variables an Instal the pyspark 3.5.1.whl.\n# pyspark 3.5.1 -- https://www.kaggle.com/datasets/justinmustaine/pyspark-3-5-1/data\n# Some enviroment variables may not be needed. I believe SPARK_HOME is only requried if\n# you build from source. HADOOP_HOME, I have no idea how pyspark is not complaing about \n# this. The intranet says to ignore,\"WARN NativeCodeLoader: Unable to load native-hadoop\". \n\nimport os\n        \nos.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\nos.environ[\"PYSPARK_DRIVER_PYTHON_OPTS\"] = \"notebook\"\nos.environ[\"PYSPARK_DRIVER_PYTHON\"] = \"jupyter\"\nos.environ[\"PYARROW_IGNORE_TIMEZONE\"] = \"1\"\n# os.environ[\"SPARK_HOME\"] = \"tbd\"\n# os.environ[\"HADOOP_HOME\"] = \"tbd\"\n\n!pip install --no-index --no-deps ../input/pyspark-3-5-1/wheelhouse/*.whl","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:56:18.169484Z","iopub.execute_input":"2024-03-01T03:56:18.170257Z","iopub.status.idle":"2024-03-01T03:56:29.350991Z","shell.execute_reply.started":"2024-03-01T03:56:18.170208Z","shell.execute_reply":"2024-03-01T03:56:29.349458Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## __Import Packages__","metadata":{}},{"cell_type":"code","source":"# Import Packages\nimport pyspark\nfrom pyspark.sql import SparkSession\nimport pyspark.sql.functions as f\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport warnings\nimport gc\n\nsns.set()\nwarnings.filterwarnings('ignore')\n\nTRAIN_RAW_PATH = \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train\"\nTEST_RAW_PATH = \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test\"\nSUBMISSION_PATH = \"/kaggle/working/\"\nWORKING_PATH = \"./\"\n\nTRAIN_BASE = TRAIN_RAW_PATH + \"/train_base.parquet\" # tb  \nTRAIN_CREDIT_BUREAU_A_DEPTH_1 = TRAIN_RAW_PATH + \"/train_credit_bureau_a_1_*.parquet\"  # tcb_ad1 ... {0-3}\nTRAIN_CREDIT_BUREAU_A_DEPTH_2 = TRAIN_RAW_PATH + \"/train_credit_bureau_a_2_*.parquet\" # tcb_ad2 ... {0-9}\nTRAIN_CREDIT_BUREAU_B_DEPTH_1 = TRAIN_RAW_PATH + \"/train_credit_bureau_b_1.parquet\"   # tcb_bd1\nTRAIN_CREDIT_BUREAU_B_DEPTH_2 = TRAIN_RAW_PATH + \"/train_credit_bureau_b_2.parquet\"   # tcb_bd2\nTRAIN_OTHER_DEPTH_1 = TRAIN_RAW_PATH + \"/train_other_1.parquet\" # to_d1\n","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:56:29.355549Z","iopub.execute_input":"2024-03-01T03:56:29.356051Z","iopub.status.idle":"2024-03-01T03:56:30.798754Z","shell.execute_reply.started":"2024-03-01T03:56:29.356005Z","shell.execute_reply":"2024-03-01T03:56:30.797490Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## __Spark Session__","metadata":{}},{"cell_type":"code","source":"spark = SparkSession.builder.master(\"local\")\\\n    .appName(\"HomeCreditApplication\")\\\n    .config(\"spark.sql.debug.maxToStringFields\", 1000)\\\n    .config(\"spark.sql.execution.arrow.pyspark.enabled\", True)\\\n    .config(\"spark.executer.memory\", \"5g\")\\\n    .config(\"spark.driver.memory\", \"8g\")\\\n    .getOrCreate()\n\nspark\n\n\n# Notes:\n# Config is mostly used in a cluster enviroment? \n# spark.executer.memory: 1 Executor or Node will have 2 GB of memory. \n# Since conf.master is set to local,then I believe there will only be \n# one executor.\n# executer.driver vs .executor.Memory: I dont think either are much effected\n# in this enviroment or when master is set to local or local[*all cores].   ","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:56:30.806866Z","iopub.execute_input":"2024-03-01T03:56:30.807287Z","iopub.status.idle":"2024-03-01T03:56:38.593247Z","shell.execute_reply.started":"2024-03-01T03:56:30.807248Z","shell.execute_reply":"2024-03-01T03:56:38.592154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# __Train Base__","metadata":{}},{"cell_type":"code","source":"# Read in train base.\ntb = spark.read.parquet(TRAIN_BASE,\n    header=True,\n    inferSchema=True)\n\n#  *** Derive a bunch temporal features ***\ntb = tb\\\n    .withColumn(\"date_decision\",f.to_date(\"date_decision\",'yyyy-MM-dd'))\\\n    .withColumn('month_str',f.date_format('date_decision','MMMM'))\ntb = tb\\\n    .withColumn(\"week_of_year\", f.weekofyear(tb.date_decision))\\\n    .withColumn(\"week\", f.weekofyear(tb.date_decision))\\\n    .withColumn(\"day_of_year\", f.dayofyear(tb.date_decision))\ntb = tb\\\n    .withColumn(\"month_int\", f.month(tb.date_decision)) \\\n    .withColumn(\"day_of_month\", f.dayofmonth(tb.date_decision))\ntb = tb\\\n    .withColumn(\"year\",f.year(tb.date_decision))\\\n    .withColumn(\"day_of_week\", f.dayofweek(tb.date_decision)) \n\n# Get Train Base Shape\ntb_shape = f\"({tb.count()}, {len(tb.columns)})\"\nprint(f\"Shape: {tb_shape}\")\n\n# Print table sample\ntb.show(n=20,truncate=False)","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:56:38.595259Z","iopub.execute_input":"2024-03-01T03:56:38.596555Z","iopub.status.idle":"2024-03-01T03:56:47.342467Z","shell.execute_reply.started":"2024-03-01T03:56:38.596497Z","shell.execute_reply":"2024-03-01T03:56:47.341178Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## __Partition: Train Base / Year / Month / Week #__","metadata":{}},{"cell_type":"code","source":"# Partition the data by year/month\ntb.write.partitionBy('year', 'month', 'week_of_year')\\\n    .mode(\"overwrite\")\\\n    .parquet(WORKING_PATH+\\\n             r\"/tb_year_month_week_partition_path/\")\n\n# Record the first partition path.\nWORKING_PATH_PAR_BY_YEAR = r\"./tb_year_month_week_partition_path/\"\n\ndel tb\n_ = gc.collect()\n\nprint(\"Partition Complete\")","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:56:47.343893Z","iopub.execute_input":"2024-03-01T03:56:47.344382Z","iopub.status.idle":"2024-03-01T03:57:00.443410Z","shell.execute_reply.started":"2024-03-01T03:56:47.344340Z","shell.execute_reply":"2024-03-01T03:57:00.442516Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## __Train Base 2019 Observations__ ","metadata":{}},{"cell_type":"code","source":"# *** Notes about 2019 Train Base ***\n\n#\n#  1)  The number of Case ID's observed appear to grow steadily over the\n#      course of 2019.\n#\n#  2)  The number of Case ID's observed from June 2019 to October 2019, \n#      are visually consistent or flat-lined during this period.\n#    \n#  3) The number of Case ID's with defaults (target=1) observed from \n#     June 2019 to December 2019 appears to have increased.\n#  \n\n#  Thoughts ...\n\n#  With bullet points 2 & 3, I believe the finacial institution had Zero\n#  growth for new loans from June 2019 to October 2019. During that same period\n#  there was an increase in default loans. Then from October to December, \n#  both number of loans observed and loans that would deafult, \n#  would close out the 2019 on a positive trend.\n#\n\n# Deep Thoughts ...\n\n#  * How did covid effect 2019?\n#  * Can a case_id be related to another?  \n#  * If case_id's are not related, then when a case_id == 0,does\n#    this mean the loan was paid in full at this time. ","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:57:00.444797Z","iopub.execute_input":"2024-03-01T03:57:00.446157Z","iopub.status.idle":"2024-03-01T03:57:00.453323Z","shell.execute_reply.started":"2024-03-01T03:57:00.446099Z","shell.execute_reply":"2024-03-01T03:57:00.451919Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Queue up the 2019 Train Base data.\ntb2019 = spark.read.parquet(WORKING_PATH_PAR_BY_YEAR+\\\n                            r'year=2019/month=*/week_of_year=*')\ntb2019.createOrReplaceTempView(\"tb2019\")","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:57:00.455048Z","iopub.execute_input":"2024-03-01T03:57:00.455538Z","iopub.status.idle":"2024-03-01T03:57:02.095888Z","shell.execute_reply.started":"2024-03-01T03:57:00.455499Z","shell.execute_reply":"2024-03-01T03:57:02.092877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"SQL_1 = \"\"\"\nSELECT month_int, month, COUNT(case_id) AS case_id_count\nFROM(\nSELECT month_int, date_format(tb2019.date_decision,'MMM') AS month, tb2019.case_id\nFROM tb2019)\nGROUP By 1, 2\nORDER BY 1;\n\"\"\"\n\nSQL_2 = \"\"\"\nSELECT month,month_int,COUNT(case_id) AS case_id_count\nFROM(\n    SELECT  month_int, date_format(tb2019.date_decision,'MMM') AS month, tb2019.case_id, target\n    FROM tb2019\n    WHERE target==0\n    )\nGROUP BY 1,2\nORDER BY 2\n\"\"\"\n\n# Querry the custom SQL to a pandas DF.  \nmonths_2019 = spark.sql(SQL_1).toPandas()\nmonths_2019_t0 = spark.sql(SQL_2).toPandas()\n\nfig, ax = plt.subplots(figsize=(16,6))\nsns.lineplot(data=months_2019, x=\"month\", y=\"case_id_count\",ax=ax,color='red', label=\"Target = 0 or 1\")\nsns.lineplot(data=months_2019_t0, x=\"month\", y=\"case_id_count\",ax=ax,color='orange', label=\"Target = 0\")\nax.set_xlabel(\"Month\",fontdict= { 'fontsize': 12,'fontweight':'bold'})\nax.set_ylabel(\"Case ID Count\",fontdict= { 'fontsize': 12,'fontweight':'bold'})\nax.set_title('2019 Case ID count\\'s',fontdict= { 'fontsize': 20,'fontweight':'bold'})\nax.tick_params(axis='x', labelrotation = 45)\nplt.tight_layout()\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-01T03:57:02.097272Z","iopub.execute_input":"2024-03-01T03:57:02.097704Z","iopub.status.idle":"2024-03-01T03:57:08.460800Z","shell.execute_reply.started":"2024-03-01T03:57:02.097666Z","shell.execute_reply":"2024-03-01T03:57:08.459169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"SQL = \"\"\"\nSELECT month,month_int,COUNT(case_id) AS case_id_count\nFROM(\n    SELECT  month_int, date_format(tb2019.date_decision,'MMM') AS month, tb2019.case_id, target\n    FROM tb2019\n    WHERE target==1\n    )\nGROUP BY 1,2\nORDER BY 2;\n\"\"\"\nmonths_2019_t1 = spark.sql(SQL).toPandas()\n\nfig, ax = plt.subplots(figsize=(16,6))\nsns.lineplot(data=months_2019_t1, x=\"month\", y=\"case_id_count\",ax=ax,color='blue',label=\"Target = 1\")\nax.set_xlabel(\"Month\",fontdict= { 'fontsize': 12,'fontweight':'bold'})\nax.set_ylabel(\"Case ID Count\",fontdict= { 'fontsize': 12,'fontweight':'bold'})\nax.set_title('2019 Count of Case ID\\'s \\nper month and Target=1',fontdict= { 'fontsize': 20,'fontweight':'bold'})\nax.tick_params(axis='x', labelrotation = 45)\nplt.tight_layout()\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-01T03:57:08.465938Z","iopub.execute_input":"2024-03-01T03:57:08.466995Z","iopub.status.idle":"2024-03-01T03:57:10.394472Z","shell.execute_reply.started":"2024-03-01T03:57:08.466939Z","shell.execute_reply":"2024-03-01T03:57:10.392903Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## __Train Base 2020 Observations__ ","metadata":{}},{"cell_type":"code","source":"# *** Notes about 2020 Train Base ***\n\n#\n#  1) No visual trend for number of Case ID's observed. \n# \n#  2) No visual/major increase for Case ID's who target==1. \n\n# Thaughts ...\n#\n#  Did Covid reduced loan defaults? It appears loans still\n#  recieved a case ID, but defaults remain low.\n#  April 2020 took a big hit. \n\n# hmmm ...","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:57:10.396389Z","iopub.execute_input":"2024-03-01T03:57:10.397296Z","iopub.status.idle":"2024-03-01T03:57:10.404098Z","shell.execute_reply.started":"2024-03-01T03:57:10.397254Z","shell.execute_reply":"2024-03-01T03:57:10.402749Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Queue up the 2020 Train Base Data \ntb2020 = spark.read.parquet(WORKING_PATH_PAR_BY_YEAR+r'year=2020/month=*/week_of_year=*')\ntb2020.createOrReplaceTempView(\"tb2020\")","metadata":{"execution":{"iopub.status.busy":"2024-03-01T03:57:10.405776Z","iopub.execute_input":"2024-03-01T03:57:10.406217Z","iopub.status.idle":"2024-03-01T03:57:11.300634Z","shell.execute_reply.started":"2024-03-01T03:57:10.406188Z","shell.execute_reply":"2024-03-01T03:57:11.299417Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"SQL_1 = \"\"\"\nSELECT month_int, month, COUNT(case_id) AS case_id_count\nFROM(\nSELECT month_int, date_format(tb2020.date_decision,'MMM') AS month, tb2020.case_id\nFROM tb2020)\nGROUP By 1, 2\nORDER BY 1;\n\"\"\"\n\nSQL_2 = \"\"\"\nSELECT month,month_int,COUNT(case_id) AS case_id_count\nFROM(\n    SELECT  month_int, date_format(tb2020.date_decision,'MMM') AS month, tb2020.case_id, target\n    FROM tb2020\n    WHERE target==0\n    )\nGROUP BY 1,2\nORDER BY 2\n\"\"\"\n\n# Querry the custom SQL to a pandas DF.  \nmonths_2020 = spark.sql(SQL_1).toPandas()\nmonths_2020_t0 = spark.sql(SQL_2).toPandas()\n\nfig, ax = plt.subplots(figsize=(16,6))\nsns.lineplot(data=months_2020, x=\"month\", y=\"case_id_count\",ax=ax,color='green', label=\"Target = 0 or 1\")\nsns.lineplot(data=months_2020_t0, x=\"month\", y=\"case_id_count\",ax=ax,color='purple', label=\"Target = 0\")\nax.set_xlabel(\"Month\",fontdict= { 'fontsize': 12,'fontweight':'bold'})\nax.set_ylabel(\"Case ID Count\",fontdict= { 'fontsize': 12,'fontweight':'bold'})\nax.set_title('2020 Case ID count\\'s',fontdict= { 'fontsize': 20,'fontweight':'bold'})\nax.tick_params(axis='x', labelrotation = 45)\nplt.tight_layout()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-01T03:57:11.301889Z","iopub.execute_input":"2024-03-01T03:57:11.302322Z","iopub.status.idle":"2024-03-01T03:57:13.929112Z","shell.execute_reply.started":"2024-03-01T03:57:11.302284Z","shell.execute_reply":"2024-03-01T03:57:13.928117Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"SQL = \"\"\"\nSELECT month,month_int,COUNT(case_id) AS case_id_count\nFROM(\n    SELECT  month_int, date_format(tb2020.date_decision,'MMM') AS month, tb2020.case_id, target\n    FROM tb2020\n    WHERE target==1\n    )\nGROUP BY 1,2\nORDER BY 2;\n\"\"\"\nmonths_2020_t1 = spark.sql(SQL).toPandas()\n\nfig, ax = plt.subplots(figsize=(16,6))\nsns.lineplot(data=months_2020_t1, x=\"month\", y=\"case_id_count\",ax=ax,color='red',label=\"Target = 1\")\nax.set_xlabel(\"Month\",fontdict= { 'fontsize': 12,'fontweight':'bold'})\nax.set_ylabel(\"Case ID Count\",fontdict= { 'fontsize': 12,'fontweight':'bold'})\nax.set_title('2020 Count of Case ID\\'s \\nper month and Target=1',fontdict= { 'fontsize': 20,'fontweight':'bold'})\nax.tick_params(axis='x', labelrotation = 45)\nplt.tight_layout()\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-01T03:57:13.930535Z","iopub.execute_input":"2024-03-01T03:57:13.931530Z","iopub.status.idle":"2024-03-01T03:57:15.279540Z","shell.execute_reply.started":"2024-03-01T03:57:13.931496Z","shell.execute_reply":"2024-03-01T03:57:15.277829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# __Train_Applprev_(1_0 & 1_1)__\n_____","metadata":{}},{"cell_type":"markdown","source":"| Variable   |      Description  |  Dtype |\n|:----------|:-------------:|:------:|\n| actualdpdtolerance_344P |  DPD of client with tolerance. | Float |\n| annuity_853A |    Monthly annuity for previous applications.  |   Float |\n| approvaldate_319D | Approval Date of Previous Application |  Date |\n| byoccupationinc_3656910L| Applicant's income from previous applications. | Float |\n| cancelreason_3545846M|Application cancellation reason.  | String |\n| childnum_21L| Number of children in the previous application. |Float |\n| creationdate_885D | Date when previous application was created. | Date  |\n| credacc_actualbalance_314A | Actual balance on credit account. | Float |\n| credacc_credlmt_575A| Credit card credit limit provided for previous applications. | Float |\n| credacc_maxhisbal_375A| Maximal historical balance of previous credit account | Float |\n| credacc_minhisbal_90A | Minimum historical balance of previous credit accounts. | Float |\n|credacc_status_367L | Account status of previous credit applications. | String  |\n|credacc_transactions_402L | Number of transactions made with the previous credit account of the applicant. | Float |\n|credamount_590A | Loan amount or card limit of previous applications. | Float |\n|credtype_587L |Credit type of previous application.  | String  |\n|currdebt_94A | Previous application's current debt. | Float |\n| district_544M  | District of the address used in the previous loan application. | String |\n| downpmt_134A| Previous application downpayment amount.  |Float  |\n| dtlastpmt_581D | Date of last payment made by the applicant.| Float |\n| dtlastpmtallstes_3545839D| Date of the applicant's last payment. | Date |\n| education_1138M |  Applicant's education level from their previous application.| String |\n| employedfrom_700D|  Employment start date from the previous application.| Date |\n| familystate_726L|  Family State in previous application of applicant.| String |\n| firstnonzeroinstldate_307D|  Date of first instalment in the previous application.| Date |\n| inittransactioncode_279L| Type of the initial transaction made in the previous application of the client. | String |\n| isbidproduct_390L|Flag for determining if the product is a cross-sell in previous applications. | Bool |\n| isdebitcard_527L| Previous application flag indicating if product being applied for is a debit card. | * |\n| mainoccupationinc_437A|  Client's main income amount in their previous application.| Float |\n| maxdpdtolerance_577P| Maximum DPD with tolerance (on previous application/s). | Float |\n| outstandingdebt_522A|  Amount of outstanding debt on the client's previous application.| Float |\n| pmtnum_8L| Number of payments made for the previous application. | Float |\n| postype_4733339M| Type of point of sale. | String |\n|rejectreasonclient_4145042M | Reason for rejection of the client's previous application. | String |\n|revolvingaccount_394A| Revolving account that was present in the applicant's previous application. | String |\n|status_219L| Previous application status. | String |\n|tenor_203L | Number of instalments in the previous application. | Float |\n\n(* Observed only as ___None___)","metadata":{}},{"cell_type":"code","source":"T_APPLPRV =  TRAIN_RAW_PATH + \"/train_applprev_1_*.parquet\"\n\nt_applprv = spark.read.parquet(T_APPLPRV,\n    header=True,\n    inferSchema=True)\n\nt_applprv.createOrReplaceTempView(\"t_applprv\")\n# t_applprv.pandas_api().head()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-01T04:16:25.192270Z","iopub.execute_input":"2024-03-01T04:16:25.193624Z","iopub.status.idle":"2024-03-01T04:17:10.538494Z","shell.execute_reply.started":"2024-03-01T04:16:25.193568Z","shell.execute_reply":"2024-03-01T04:17:10.537341Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## __Train_Applprev_(1_0 & 1_1) education_1138M & mainoccupationinc_437A__\n","metadata":{}},{"cell_type":"code","source":"# Check to see if an Applicant's reported income differs amongs education levels.\n# h0: Applicants with High Income = Applicants with high edu levels. \n\nSQL = \"\"\"\nSELECT education_1138M, AVG(mainoccupationinc_437A) AS Average_Income,\\\n                        MIN(mainoccupationinc_437A) AS Min_Income,\\\n                        MAX(mainoccupationinc_437A) AS Max_Income\nFROM t_applprv\nGROUP BY education_1138M\nORDER BY 2 DESC;\n\"\"\"\nspark.sql(SQL).toPandas()\n\n\n# Pandas Method\n# DF_objct.groupby(['education_1138M'])\\\n#     .agg({\"mainoccupationinc_437A\":[\"min\",\"max\",\"mean\",]})","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2024-03-01T04:39:44.535662Z","iopub.execute_input":"2024-03-01T04:39:44.536182Z","iopub.status.idle":"2024-03-01T04:39:46.281314Z","shell.execute_reply.started":"2024-03-01T04:39:44.536148Z","shell.execute_reply":"2024-03-01T04:39:46.278585Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Get the last application submited per CASE_ID.\n\nSQL = \"\"\"\nSELECT case_id, education_1138M AS Education,\n                mainoccupationinc_437A AS Income,\n                creationdate_885D AS Date\nFROM (\n    SELECT t1.*\n    FROM t_applprv t1\n    JOIN (\n        SELECT case_id,MAX(DATE(creationdate_885D)) AS latest_date\n        FROM t_applprv\n        GROUP BY case_id\n        ) t2\n    ON t1.case_id = t2.case_id\n    AND t1.creationdate_885D = t2.latest_date\n    ORDER BY t1.case_id\n    );\n\"\"\"\n\n# Note, there will be duplicates hence the drop_duplicates func.\nlast_application = spark.sql(SQL).toPandas()\\\n    .drop_duplicates(subset=['case_id'], keep='first')\n\nlast_application","metadata":{"execution":{"iopub.status.busy":"2024-03-01T06:57:12.886639Z","iopub.execute_input":"2024-03-01T06:57:12.887139Z","iopub.status.idle":"2024-03-01T06:57:34.593508Z","shell.execute_reply.started":"2024-03-01T06:57:12.887060Z","shell.execute_reply":"2024-03-01T06:57:34.591972Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Manually compare to the Train Base. \nSQL = \"\"\"\nSELECT * \nFROM tb2019\nWHERE case_id = 3\n\"\"\"\nspark.sql(SQL).toPandas()\n\n# Cell Notes:\n# Assuming my query to locate the last application per client is correct...\n# You'll find some Applications in the Train_Applprev_(1_0 & 1_1) having been submitted after the \n# Date reported in the train_base. This would ultimately mean that an applicantion was \n# subbmited after the target variable was recorded in the train_base.target report.\n\n","metadata":{"execution":{"iopub.status.busy":"2024-03-01T06:58:01.114731Z","iopub.execute_input":"2024-03-01T06:58:01.115236Z","iopub.status.idle":"2024-03-01T06:58:01.467236Z","shell.execute_reply.started":"2024-03-01T06:58:01.115191Z","shell.execute_reply":"2024-03-01T06:58:01.466054Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# My Notes b4 bed:\n# Partition Ideas - Education? Education level is ordinal feat, but is masked. order of Applications submitted per Case_id?   \n# Clean up duplicates, determine if the most recent application was submitted before or after the target date.\n# Ram-6.4  Disk-5.","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"SQL = \"\"\"\nSELECT * \nFROM tb2020\nWHERE case_id = 2703450\n\"\"\"\nspark.sql(SQL).toPandas()","metadata":{"execution":{"iopub.status.busy":"2024-03-01T06:14:45.639631Z","iopub.execute_input":"2024-03-01T06:14:45.640103Z","iopub.status.idle":"2024-03-01T06:14:45.892325Z","shell.execute_reply.started":"2024-03-01T06:14:45.640044Z","shell.execute_reply":"2024-03-01T06:14:45.891103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}