{"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"}},"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)\nimport gc\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","_kg_hide-output":true,"scrolled":true,"execution":{"iopub.status.busy":"2022-06-01T07:59:50.166668Z","iopub.execute_input":"2022-06-01T07:59:50.167331Z","iopub.status.idle":"2022-06-01T07:59:50.233027Z","shell.execute_reply.started":"2022-06-01T07:59:50.167216Z","shell.execute_reply":"2022-06-01T07:59:50.231924Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Please refer to [this](https://www.kaggle.com/code/sravanneeli/convert-train-and-test-multiple-parquet-files) notebook for creating train and test parquet files","metadata":{}},{"cell_type":"code","source":"!pip install pyspark","metadata":{"execution":{"iopub.status.busy":"2022-06-01T07:59:50.234588Z","iopub.execute_input":"2022-06-01T07:59:50.234895Z","iopub.status.idle":"2022-06-01T08:00:39.129787Z","shell.execute_reply.started":"2022-06-01T07:59:50.234869Z","shell.execute_reply":"2022-06-01T08:00:39.12878Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pyspark.sql import SparkSession\n \nspark = SparkSession.builder \\\n    .master('local[*]') \\\n    .config(\"spark.driver.memory\", \"15g\") \\\n    .appName('amex-test') \\\n    .getOrCreate()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:00:39.131725Z","iopub.execute_input":"2022-06-01T08:00:39.132357Z","iopub.status.idle":"2022-06-01T08:00:45.312663Z","shell.execute_reply.started":"2022-06-01T08:00:39.132317Z","shell.execute_reply":"2022-06-01T08:00:45.311324Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df = spark.read.parquet('../input/amex-default-prediction-parquet-files/train*.pqt')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:00:45.314235Z","iopub.execute_input":"2022-06-01T08:00:45.317119Z","iopub.status.idle":"2022-06-01T08:00:50.456909Z","shell.execute_reply.started":"2022-06-01T08:00:45.317066Z","shell.execute_reply":"2022-06-01T08:00:50.455928Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.printSchema()","metadata":{"_kg_hide-output":true,"scrolled":true,"execution":{"iopub.status.busy":"2022-06-01T08:00:50.459005Z","iopub.execute_input":"2022-06-01T08:00:50.460661Z","iopub.status.idle":"2022-06-01T08:00:50.504254Z","shell.execute_reply.started":"2022-06-01T08:00:50.4606Z","shell.execute_reply":"2022-06-01T08:00:50.503264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from pyspark.sql.types import StringType, StructType\nimport pyspark.sql.functions as func ","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:00:50.506423Z","iopub.execute_input":"2022-06-01T08:00:50.507039Z","iopub.status.idle":"2022-06-01T08:00:51.668259Z","shell.execute_reply.started":"2022-06-01T08:00:50.506993Z","shell.execute_reply":"2022-06-01T08:00:51.667393Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df = train_df.withColumn(\"customer_ID\",train_df[\"customer_ID\"].cast(StringType()))","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:00:51.669535Z","iopub.execute_input":"2022-06-01T08:00:51.670008Z","iopub.status.idle":"2022-06-01T08:00:51.861506Z","shell.execute_reply.started":"2022-06-01T08:00:51.669966Z","shell.execute_reply":"2022-06-01T08:00:51.860515Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"Total number of rows: {train_df.count()} and Tota number of cols: {len(train_df.columns)}\")","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:00:51.862685Z","iopub.execute_input":"2022-06-01T08:00:51.863115Z","iopub.status.idle":"2022-06-01T08:00:54.767999Z","shell.execute_reply.started":"2022-06-01T08:00:51.863073Z","shell.execute_reply":"2022-06-01T08:00:54.767091Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:00:54.769126Z","iopub.execute_input":"2022-06-01T08:00:54.76954Z","iopub.status.idle":"2022-06-01T08:00:54.776587Z","shell.execute_reply.started":"2022-06-01T08:00:54.769502Z","shell.execute_reply":"2022-06-01T08:00:54.775317Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols = []\nfor col in train_df.columns[2:]:\n    if col not in cat_cols:\n        num_cols.append(col)\n","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:00:54.77852Z","iopub.execute_input":"2022-06-01T08:00:54.77931Z","iopub.status.idle":"2022-06-01T08:00:54.792731Z","shell.execute_reply.started":"2022-06-01T08:00:54.77924Z","shell.execute_reply":"2022-06-01T08:00:54.791571Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Group By for each Individual Customer ID","metadata":{}},{"cell_type":"markdown","source":"## The usage of pyspark\n* I observed in other notebook they where using feather data which was created by somebody but there was no proper source where that has been created.\n* So I though and first create parquet files from csv files which I attached above.\n* Once of we have parquet files we can now load `train` and `test` data using pyspark which will do aggregation with parallel manner with very less concumption RAM.\n* Order each pyspark dataframe with `customer_ID` and at the end convert them to pandas DataFrame and just concat them horizontally because all are sorted.","metadata":{}},{"cell_type":"code","source":"%%time\ncount_exprs = {col: 'count' for col in cat_cols}\ncat_count_df = train_df.groupby('customer_ID').agg(count_exprs).orderBy('customer_ID', ascending=True).toPandas()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:00:54.79672Z","iopub.execute_input":"2022-06-01T08:00:54.797157Z","iopub.status.idle":"2022-06-01T08:01:12.539127Z","shell.execute_reply.started":"2022-06-01T08:00:54.797124Z","shell.execute_reply":"2022-06-01T08:01:12.537731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in cat_count_df.columns[1:]:\n    cat_count_df[col] = cat_count_df[col].astype('int8')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:12.540463Z","iopub.execute_input":"2022-06-01T08:01:12.541603Z","iopub.status.idle":"2022-06-01T08:01:12.560215Z","shell.execute_reply.started":"2022-06-01T08:01:12.541555Z","shell.execute_reply":"2022-06-01T08:01:12.559405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_count_df.shape","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:12.561244Z","iopub.execute_input":"2022-06-01T08:01:12.561742Z","iopub.status.idle":"2022-06-01T08:01:12.569684Z","shell.execute_reply.started":"2022-06-01T08:01:12.561681Z","shell.execute_reply":"2022-06-01T08:01:12.56883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:12.571333Z","iopub.execute_input":"2022-06-01T08:01:12.571986Z","iopub.status.idle":"2022-06-01T08:01:12.685993Z","shell.execute_reply.started":"2022-06-01T08:01:12.571944Z","shell.execute_reply":"2022-06-01T08:01:12.684957Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nlast_exprs = {col: 'last' for col in cat_cols}\ncat_last_df = train_df.groupby('customer_ID').agg(last_exprs).orderBy('customer_ID', ascending=True).toPandas().drop('customer_ID', axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:12.687612Z","iopub.execute_input":"2022-06-01T08:01:12.688031Z","iopub.status.idle":"2022-06-01T08:01:30.024379Z","shell.execute_reply.started":"2022-06-01T08:01:12.68799Z","shell.execute_reply":"2022-06-01T08:01:30.023403Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in cat_last_df:\n    if cat_last_df[col].dtype == \"float32\":\n        cat_last_df[col] = cat_last_df[col].astype('float16')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:30.025613Z","iopub.execute_input":"2022-06-01T08:01:30.027915Z","iopub.status.idle":"2022-06-01T08:01:30.082373Z","shell.execute_reply.started":"2022-06-01T08:01:30.027878Z","shell.execute_reply":"2022-06-01T08:01:30.081373Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_last_df.shape","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:30.083733Z","iopub.execute_input":"2022-06-01T08:01:30.08417Z","iopub.status.idle":"2022-06-01T08:01:30.090846Z","shell.execute_reply.started":"2022-06-01T08:01:30.084124Z","shell.execute_reply":"2022-06-01T08:01:30.089943Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:30.092259Z","iopub.execute_input":"2022-06-01T08:01:30.093168Z","iopub.status.idle":"2022-06-01T08:01:30.203896Z","shell.execute_reply.started":"2022-06-01T08:01:30.093114Z","shell.execute_reply":"2022-06-01T08:01:30.203016Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def grp_unique_count(train_df):\n    agg_df = []\n    for col in cat_cols:\n        agg_df.append(train_df.groupby('customer_ID').agg(func.expr(f'count(distinct {col})').alias(f'nunique({col})')).orderBy('customer_ID', ascending=True).toPandas().drop('customer_ID', axis=1))\n    final_df = pd.concat(agg_df, axis=1).astype('int8')\n    gc.collect()\n    return final_df","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:30.205471Z","iopub.execute_input":"2022-06-01T08:01:30.205803Z","iopub.status.idle":"2022-06-01T08:01:30.215169Z","shell.execute_reply.started":"2022-06-01T08:01:30.205775Z","shell.execute_reply":"2022-06-01T08:01:30.214519Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ncat_nc_df = grp_unique_count(train_df)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:01:30.216321Z","iopub.execute_input":"2022-06-01T08:01:30.216613Z","iopub.status.idle":"2022-06-01T08:02:52.170395Z","shell.execute_reply.started":"2022-06-01T08:01:30.216587Z","shell.execute_reply":"2022-06-01T08:02:52.169416Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols_df = pd.concat([cat_count_df, cat_last_df, cat_nc_df], axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:02:52.171825Z","iopub.execute_input":"2022-06-01T08:02:52.172411Z","iopub.status.idle":"2022-06-01T08:02:52.205106Z","shell.execute_reply.started":"2022-06-01T08:02:52.172353Z","shell.execute_reply":"2022-06-01T08:02:52.204157Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols_df.to_pickle('cat_cols_df.pkl', compression='gzip')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:02:52.206675Z","iopub.execute_input":"2022-06-01T08:02:52.207456Z","iopub.status.idle":"2022-06-01T08:03:33.05516Z","shell.execute_reply.started":"2022-06-01T08:02:52.207422Z","shell.execute_reply":"2022-06-01T08:03:33.053552Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del(cat_count_df)\ndel(cat_last_df)\ndel(cat_nc_df)\ndel(cat_cols_df)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:03:33.056697Z","iopub.execute_input":"2022-06-01T08:03:33.057131Z","iopub.status.idle":"2022-06-01T08:03:33.13253Z","shell.execute_reply.started":"2022-06-01T08:03:33.057092Z","shell.execute_reply":"2022-06-01T08:03:33.131416Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:03:33.134257Z","iopub.execute_input":"2022-06-01T08:03:33.134861Z","iopub.status.idle":"2022-06-01T08:03:33.245895Z","shell.execute_reply.started":"2022-06-01T08:03:33.134818Z","shell.execute_reply":"2022-06-01T08:03:33.245022Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def grp_num_cols(train_df):\n    def agg_num(agg_func):\n        agg_df = []\n        for i in range(0, len(num_cols), 20):\n            exprs = {col: agg_func for col in num_cols[i:i+20]}\n            agg_df.append(train_df.groupBy('customer_ID').agg(exprs).orderBy('customer_ID', ascending=True).toPandas().drop('customer_ID', axis=1))\n        final_df = pd.concat(agg_df, axis=1).astype('float16')\n        gc.collect()\n        return final_df\n    num_mean_df = agg_num(\"mean\")\n    num_std_df = agg_num(\"std\")\n    num_min_df = agg_num(\"min\")\n    num_max_df = agg_num(\"max\")\n    final_df = pd.concat([num_mean_df, num_std_df, num_min_df, num_max_df], axis=1)\n    return final_df","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:03:33.246913Z","iopub.execute_input":"2022-06-01T08:03:33.24756Z","iopub.status.idle":"2022-06-01T08:03:33.259735Z","shell.execute_reply.started":"2022-06-01T08:03:33.247525Z","shell.execute_reply":"2022-06-01T08:03:33.258812Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nnum_cols_df = grp_num_cols(train_df)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:03:33.260755Z","iopub.execute_input":"2022-06-01T08:03:33.261683Z","iopub.status.idle":"2022-06-01T08:13:55.472131Z","shell.execute_reply.started":"2022-06-01T08:03:33.261646Z","shell.execute_reply":"2022-06-01T08:13:55.471221Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols_df.to_pickle('num_cols_df.pkl', compression='gzip')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:13:55.473459Z","iopub.execute_input":"2022-06-01T08:13:55.474786Z","iopub.status.idle":"2022-06-01T08:16:10.983504Z","shell.execute_reply.started":"2022-06-01T08:13:55.474715Z","shell.execute_reply":"2022-06-01T08:16:10.981967Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del(num_cols_df)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:16:10.985762Z","iopub.execute_input":"2022-06-01T08:16:10.986848Z","iopub.status.idle":"2022-06-01T08:16:11.015472Z","shell.execute_reply.started":"2022-06-01T08:16:10.986796Z","shell.execute_reply":"2022-06-01T08:16:11.014545Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols_df = pd.read_pickle('./cat_cols_df.pkl', compression='gzip')\nnum_cols_df = pd.read_pickle('./num_cols_df.pkl', compression='gzip')\nfinal_train_df = pd.concat([cat_cols_df, num_cols_df], axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:16:11.021646Z","iopub.execute_input":"2022-06-01T08:16:11.023581Z","iopub.status.idle":"2022-06-01T08:16:18.550566Z","shell.execute_reply.started":"2022-06-01T08:16:11.023493Z","shell.execute_reply":"2022-06-01T08:16:18.549508Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del(cat_cols_df)\ndel(num_cols_df)\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:16:18.580498Z","iopub.execute_input":"2022-06-01T08:16:18.580786Z","iopub.status.idle":"2022-06-01T08:16:18.763019Z","shell.execute_reply.started":"2022-06-01T08:16:18.580755Z","shell.execute_reply":"2022-06-01T08:16:18.761841Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"final_train_df.to_pickle('train_agg.pkl', compression='gzip')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T08:16:18.764732Z","iopub.execute_input":"2022-06-01T08:16:18.765782Z","iopub.status.idle":"2022-06-01T08:19:14.940776Z","shell.execute_reply.started":"2022-06-01T08:16:18.765735Z","shell.execute_reply":"2022-06-01T08:19:14.939246Z"},"trusted":true},"execution_count":null,"outputs":[]}]}