{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7921029,"sourceType":"competition"},{"sourceId":7907808,"sourceType":"datasetVersion","datasetId":4460771}],"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import shutil\nsrc_path = r\"/kaggle/input/pyspark-package/pyspark-3.5.0.tar.gz.mp4\"\ndst_path = r\"/kaggle/working/pyspark-3.5.0.tar.gz\"\nshutil.copy(src_path, dst_path)\n!pip install /kaggle/working/pyspark-3.5.0.tar.gz","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pyspark.sql import SparkSession\nspark = SparkSession.builder.getOrCreate()\nfrom pyspark.sql.functions import when, col\nimport pyspark.sql.functions as F\nfrom functools import reduce\nfrom pyspark.sql import DataFrame\nfrom pyspark.ml.feature import PCA\nfrom pyspark.ml.feature import VectorAssembler\nfrom pyspark.ml.feature import StandardScaler\nfrom sklearn.ensemble import GradientBoostingRegressor\nfrom sklearn.model_selection import cross_val_score\nimport numpy as np\nimport gc\nimport warnings","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Impute_values**","metadata":{}},{"cell_type":"code","source":"def num_impute_values(temp_df):\n    int_cols = [col_name for col_name, data_type in temp_df.dtypes if data_type == 'integer']\n    temp_df=temp_df.fillna(0, subset=int_cols)\n    float_cols = [col_name for col_name, data_type in temp_df.dtypes if data_type == 'double']\n    temp_df=temp_df.fillna(0.0, subset=float_cols)\n    return temp_df","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_base.csv\", header=True,inferSchema=True)\ndf_train=df_train.drop(\"date_decision\")\ndf_train.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1 = spark.read.csv('/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_applprev_1_1.csv', header=True,inferSchema=True)\ndf2 = spark.read.csv('/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_applprev_1_0.csv', header=True,inferSchema=True)\n\nconcatenated_df = df2.unionByName(df1)\na=['case_id', 'actualdpd_943P','credamount_590A','currdebt_94A', 'downpmt_134A', 'outstandingdebt_522A', 'pmtnum_8L', 'tenor_203L']\n\ndf=concatenated_df.select(a)\ndf.head(5)\ndel concatenated_df\ndel df1\ndel df2","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train=df_train.join(df,df_train.case_id==df.case_id,\"left\").drop(df.case_id)\ndf_train.columns","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_credit_bureau_a_1_0.csv\",header=True,inferSchema=True)          \ndf2=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_credit_bureau_a_1_1.csv\",header=True,inferSchema=True)          \ndf3=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_credit_bureau_a_1_2.csv\",header=True,inferSchema=True)          \ndf4=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_credit_bureau_a_1_3.csv\",header=True,inferSchema=True)\n\na=[\"case_id\",\"credlmt_935A\",\"debtoutstand_525A\",\"dpdmax_139P\",\"instlamount_768A\",\"monthlyinstlamount_332A\",\"numberofcontrsvalue_258L\",\"numberofinstls_320L\",\"numberofoutstandinstls_59L\",\"totalamount_996A\"]\ndf1=df1.select(a)\ndf2=df2.select(a)\ndf3=df3.select(a)\ndf4=df4.select(a)\nconcatenated_df=reduce(DataFrame.unionByName,[df1,df2,df3,df4])\nconcatenated_df.head(5)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del df1\ndel df2\ndel df3\ndel df4\ndf_train=df_train.join(concatenated_df,df_train.case_id == concatenated_df.case_id,\"left\").drop(concatenated_df.case_id)\ndel concatenated_df\ngc.collect()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df2=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_person_1.csv\",header=True,inferSchema=True)\ndf2=df2.select(['case_id','contaddr_matchlist_1032L',\"remitter_829L\"])\ndf_train=df_train.join(df2,df_train.case_id ==df2.case_id,\"left\").drop(df2.case_id)\nprint(df_train.columns)\nlen(df_train.columns)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_0_0.csv\",header=True,inferSchema=True)\ndf2=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_0_1.csv\",header=True,inferSchema=True)\n\na=['case_id','annuity_780A','annuitynextmonth_57A','applications30d_658L','applicationscnt_1086L','applicationscnt_464L','avginstallast24m_3658937A','clientscnt_100L','clientscnt_360L','clientscnt_257L','clientscnt_493L','clientscnt_533L','clientscnt_946L','cntincpaycont9m_3716944L','credamount_770A','currdebt_22A','currdebtcredtyperange_828A','daysoverduetolerancedd_3976961L','disbursedcredamount_1113A','downpmt_116A','eir_270L','lastrejectcredamount_222A','maininc_215A',\n'maxdebt4_972A','maxdpdinstlnum_3546846P','maxdpdlast24m_143P','maxdpdtolerance_374P','maxlnamtstart6m_4525199A','numactivecreds_622L','numinstlallpaidearly3d_817L','numrejects9m_859L','pmtnum_254L','sumoutstandtotal_3546847A']\ndf1=df1.select(a)\ndf2=df2.select(a)\nconcatenated_df = df1.unionByName(df2)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df3=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_cb_0.csv\",header=True,inferSchema=True)\ndf3=df3.select(['case_id','days360_512L','firstquarter_103L','fourthquarter_440L','numberofqueries_373L','pmtaverage_3A','pmtcount_693L','pmtssum_45A','secondquarter_766L','thirdquarter_1082L'])\ndf=concatenated_df.join(df3,concatenated_df.case_id ==df3.case_id,\"left\").drop(df3.case_id)\nlen(df.columns)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()\nprint(len(df_train.columns),len(df.columns))","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train=df_train.join(df,df_train.case_id == df.case_id,\"left\").drop(df.case_id)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train=num_impute_values(df_train)\ngc.collect()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train=df_train.withColumn(\"contaddr_matchlist_1032L\",df_train[\"contaddr_matchlist_1032L\"].cast('integer')) \ndf_train=df_train.withColumn(\"remitter_829L\",df_train[\"remitter_829L\"].cast('integer'))\ndf_train=df_train.fillna(1, subset=[\"remitter_829L\"])\ndf_train=df_train.fillna(1, subset=[\"contaddr_matchlist_1032L\"])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in df_train.columns:\n    missing_values = df_train.filter(F.col(col).isNull()).count()\n    print(f\"Missing values in column {col}: {missing_values}\")","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train_sample_0=df_train.filter(df_train['target']==0).sample(False, 1.0/300)\nprint(df_train_sample_0.count())\nlen(df_train_sample_0.columns)\n","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train_sample_1=df_train.filter(df_train['target']==1).sample(False, 1.0/10)\nprint(df_train_sample_1.count())\nlen(df_train_sample_1.columns)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Train Data","metadata":{}},{"cell_type":"code","source":"df_train_sample=df_train_sample_0.unionByName(df_train_sample_1)\nprint(len(df_train_sample.columns))\ndf_train_sample.count()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_base.csv\",header=True,inferSchema=True)\ndf_test=df_test.drop(\"date_decision\")\ndf_test.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1 = spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_applprev_1_0.csv\",header=True,inferSchema=True)\na=['case_id', 'actualdpd_943P','credamount_590A','currdebt_94A', 'downpmt_134A', 'outstandingdebt_522A', 'pmtnum_8L', 'tenor_203L']\ndf1=df1.select(a)\ndf1.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test=df_test.join(df1,df_test.case_id==df1.case_id,\"left\").drop(df1.case_id)\ndf_test.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1 = spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_credit_bureau_a_1_0.csv\",header=True,inferSchema=True)\na=[\"case_id\",\"credlmt_935A\",\"debtoutstand_525A\",\"dpdmax_139P\",\"instlamount_768A\",\"monthlyinstlamount_332A\",\"numberofcontrsvalue_258L\",\"numberofinstls_320L\",\"numberofoutstandinstls_59L\",\"totalamount_996A\"]\ndf1=df1.select(a)\n\ndf_test=df_test.join(df1,df_test.case_id==df1.case_id,\"left\").drop(df1.case_id)\ndf_test.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1 = spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_person_1.csv\",header=True,inferSchema=True)\ndf1=df1.select(['case_id','contaddr_matchlist_1032L',\"remitter_829L\"])\ndf_test=df_test.join(df1,df_test.case_id ==df1.case_id,\"left\").drop(df1.case_id)\ndf_test.columns","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1 = spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_static_0_0.csv\",header=True,inferSchema=True)\na=['case_id','annuity_780A','annuitynextmonth_57A','applications30d_658L','applicationscnt_1086L','applicationscnt_464L','avginstallast24m_3658937A','clientscnt_100L','clientscnt_360L','clientscnt_257L','clientscnt_493L','clientscnt_533L','clientscnt_946L','cntincpaycont9m_3716944L','credamount_770A','currdebt_22A','currdebtcredtyperange_828A','daysoverduetolerancedd_3976961L','disbursedcredamount_1113A','downpmt_116A','eir_270L','lastrejectcredamount_222A','maininc_215A',\n'maxdebt4_972A','maxdpdinstlnum_3546846P','maxdpdlast24m_143P','maxdpdtolerance_374P','maxlnamtstart6m_4525199A','numactivecreds_622L','numinstlallpaidearly3d_817L','numrejects9m_859L','pmtnum_254L','sumoutstandtotal_3546847A']\ndf1=df1.select(a)\ndf_test=df_test.join(df1,df_test.case_id ==df1.case_id,\"left\").drop(df1.case_id)\n\ndf2=spark.read.csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_static_cb_0.csv\",header=True,inferSchema=True)\ndf2=df2.select(['case_id','days360_512L','firstquarter_103L','fourthquarter_440L','numberofqueries_373L','pmtaverage_3A','pmtcount_693L','pmtssum_45A','secondquarter_766L','thirdquarter_1082L'])\ndf_test=df_test.join(df2,df_test.case_id ==df2.case_id,\"left\").drop(df2.case_id)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(df_test.columns)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test=num_impute_values(df_test)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test=df_test.withColumn(\"contaddr_matchlist_1032L\",df_test[\"contaddr_matchlist_1032L\"].cast('integer')) \ndf_test=df_test.withColumn(\"remitter_829L\",df_test[\"remitter_829L\"].cast('integer'))\n\ndf_test=df_test.fillna(1, subset=[\"remitter_829L\"])\ndf_test=df_test.fillna(1, subset=[\"contaddr_matchlist_1032L\"])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"int_cols = [col_name for col_name, data_type in df_test.dtypes if data_type == 'string']\n\nfor x in int_cols:\n    df_test = df_test.withColumn(x, df_test[x].cast('integer'))","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for x in int_cols:\n    df_test=df_test.fillna(df_train.agg(F.mean(x)).collect()[0][0], subset=[x])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Test Data","metadata":{}},{"cell_type":"code","source":"df_test.count()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train_target=df_train_sample.select(\"target\")\ndf_train_not_reduced=df_train_sample.drop(\"target\",\"case_id\")\ndf_test_caseid=df_test.select(\"case_id\")\ndf_test_not_reduced=df_test.drop(\"case_id\")","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature Scaling, Dimensionality Reduction","metadata":{}},{"cell_type":"code","source":"assembler = VectorAssembler(inputCols=df_train_not_reduced.columns, outputCol=\"features\",handleInvalid=\"skip\")\nassembled_data = assembler.transform(df_train_not_reduced)\n\nscaler = StandardScaler(inputCol=\"features\", outputCol=\"scaledFeatures\", withMean=True, withStd=True)\nscaled_data = scaler.fit(assembled_data).transform(assembled_data)\n\nnum_components = 5 \npca = PCA(k=num_components, inputCol=\"scaledFeatures\", outputCol=\"pcaFeatures\")\nmodel = pca.fit(scaled_data)\n\n\ntransformed_data = model.transform(scaled_data)\n\n\ntransformed_data.select(\"pcaFeatures\").show(truncate=False)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"assembler_test = VectorAssembler(inputCols=df_test_not_reduced.columns, outputCol=\"features_test\",handleInvalid=\"skip\")\nassembled_data_test = assembler_test.transform(df_test_not_reduced)\n\n\nscaler_test = StandardScaler(inputCol=\"features_test\", outputCol=\"scaledFeatures_test\", withMean=True, withStd=True)\nscaled_data_test = scaler_test.fit(assembled_data_test).transform(assembled_data_test)\n\n\nnum_components = 5 \npca_test = PCA(k=num_components, inputCol=\"scaledFeatures_test\", outputCol=\"pcaFeatures_test\")\nmodel_test = pca_test.fit(scaled_data_test)\n\n\ntransformed_data_test = model_test.transform(scaled_data_test)\n\n\ntransformed_data_test.select(\"pcaFeatures_test\").show(truncate=False)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"Y_train=df_train_target.select('target')\nY_train = Y_train.select('target').rdd.flatMap(lambda x: x).collect()\nY_train=np.array(Y_train)\nY_train.reshape(-1,1)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train =transformed_data.select(\"pcaFeatures\")\nX_test = transformed_data_test.select(\"pcaFeatures_test\")\n\nX_test = X_test.select('pcaFeatures_test').rdd.flatMap(lambda x: x).collect()\nX_test=np.array(X_test)\nX_train = X_train.select('pcaFeatures').rdd.flatMap(lambda x: x).collect()\nX_train=np.array(X_train)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(X_train.shape,X_test.shape,Y_train.shape)\na=min(X_train.shape[0],Y_train.shape[0])","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Prediction","metadata":{}},{"cell_type":"code","source":"gbr = GradientBoostingRegressor()\n\ngbr.fit(X_train[:a,:],Y_train[:a])\n\ntarget_pred = gbr.predict(X_test)\n\nscores = cross_val_score(gbr,X_train[:a,:],Y_train[:a], cv=5)\nscores.mean()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"target_pred","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_caseid=df_test_caseid.select('case_id').rdd.flatMap(lambda x: x).collect()\ngc.collect()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\ndic={\"case_id\":df_test_caseid,\"score\":target_pred}\nsubmission=pd.DataFrame(dic)\nsubmission=submission.groupby(\"case_id\")[\"score\"].mean().reset_index()\nsubmission=pd.DataFrame(submission)\nsubmission","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Submission","metadata":{}},{"cell_type":"code","source":"submission.to_csv(\"submission.csv\")","metadata":{},"execution_count":null,"outputs":[]}]}