{"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","scrolled":true,"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-06-01T06:21:26.697646Z","iopub.execute_input":"2022-06-01T06:21:26.698340Z","iopub.status.idle":"2022-06-01T06:21:26.747002Z","shell.execute_reply.started":"2022-06-01T06:21:26.698251Z","shell.execute_reply":"2022-06-01T06:21:26.746328Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install pyspark","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:21:26.748411Z","iopub.execute_input":"2022-06-01T06:21:26.748709Z","iopub.status.idle":"2022-06-01T06:22:15.335281Z","shell.execute_reply.started":"2022-06-01T06:21:26.748681Z","shell.execute_reply":"2022-06-01T06:22:15.334069Z"},"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":{"_kg_hide-output":true,"execution":{"iopub.status.busy":"2022-06-01T06:22:15.337311Z","iopub.execute_input":"2022-06-01T06:22:15.337654Z","iopub.status.idle":"2022-06-01T06:22:21.072460Z","shell.execute_reply.started":"2022-06-01T06:22:15.337620Z","shell.execute_reply":"2022-06-01T06:22:21.071373Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df = spark.read.parquet('../input/amex-default-prediction-parquet-files/test*.pqt')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:22:21.074977Z","iopub.execute_input":"2022-06-01T06:22:21.075457Z","iopub.status.idle":"2022-06-01T06:22:26.358475Z","shell.execute_reply.started":"2022-06-01T06:22:21.075407Z","shell.execute_reply":"2022-06-01T06:22:26.357214Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"Total Number of rows: {test_df.count()}, Total Number of columns: {len(test_df.columns)}\")","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:22:26.364067Z","iopub.execute_input":"2022-06-01T06:22:26.364399Z","iopub.status.idle":"2022-06-01T06:22:29.795117Z","shell.execute_reply.started":"2022-06-01T06:22:26.364362Z","shell.execute_reply":"2022-06-01T06:22:29.794197Z"},"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-01T06:22:29.796700Z","iopub.execute_input":"2022-06-01T06:22:29.797113Z","iopub.status.idle":"2022-06-01T06:22:29.810047Z","shell.execute_reply.started":"2022-06-01T06:22:29.797067Z","shell.execute_reply":"2022-06-01T06:22:29.809127Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_df = test_df.withColumn(\"customer_ID\",test_df[\"customer_ID\"].cast(StringType()))","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:22:29.813072Z","iopub.execute_input":"2022-06-01T06:22:29.813377Z","iopub.status.idle":"2022-06-01T06:22:29.943150Z","shell.execute_reply.started":"2022-06-01T06:22:29.813350Z","shell.execute_reply":"2022-06-01T06:22:29.942009Z"},"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-01T06:22:29.944329Z","iopub.execute_input":"2022-06-01T06:22:29.944770Z","iopub.status.idle":"2022-06-01T06:22:29.951774Z","shell.execute_reply.started":"2022-06-01T06:22:29.944713Z","shell.execute_reply":"2022-06-01T06:22:29.950864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols = []\nfor col in test_df.columns[2:]: # exclude the customer id and date columns\n    if col not in cat_cols:\n        num_cols.append(col)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:22:29.953470Z","iopub.execute_input":"2022-06-01T06:22:29.954203Z","iopub.status.idle":"2022-06-01T06:22:30.012125Z","shell.execute_reply.started":"2022-06-01T06:22:29.954158Z","shell.execute_reply":"2022-06-01T06:22:30.011159Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ncount_exprs = {col: 'count' for col in cat_cols}\ncat_count_df = test_df.groupby('customer_ID').agg(count_exprs).orderBy('customer_ID', ascending=True).toPandas()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:22:30.017021Z","iopub.execute_input":"2022-06-01T06:22:30.019030Z","iopub.status.idle":"2022-06-01T06:22:54.309010Z","shell.execute_reply.started":"2022-06-01T06:22:30.018987Z","shell.execute_reply":"2022-06-01T06:22:54.308157Z"},"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-01T06:22:54.312027Z","iopub.execute_input":"2022-06-01T06:22:54.312817Z","iopub.status.idle":"2022-06-01T06:22:54.333253Z","shell.execute_reply.started":"2022-06-01T06:22:54.312783Z","shell.execute_reply":"2022-06-01T06:22:54.332434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:22:54.334222Z","iopub.execute_input":"2022-06-01T06:22:54.334514Z","iopub.status.idle":"2022-06-01T06:22:54.432490Z","shell.execute_reply.started":"2022-06-01T06:22:54.334488Z","shell.execute_reply":"2022-06-01T06:22:54.431413Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nlast_exprs = {col: 'last' for col in cat_cols}\ncat_last_df = test_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-01T06:22:54.434022Z","iopub.execute_input":"2022-06-01T06:22:54.434649Z","iopub.status.idle":"2022-06-01T06:23:18.714285Z","shell.execute_reply.started":"2022-06-01T06:22:54.434614Z","shell.execute_reply":"2022-06-01T06:23:18.713405Z"},"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-01T06:23:18.715573Z","iopub.execute_input":"2022-06-01T06:23:18.715987Z","iopub.status.idle":"2022-06-01T06:23:18.813579Z","shell.execute_reply.started":"2022-06-01T06:23:18.715946Z","shell.execute_reply":"2022-06-01T06:23:18.812800Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:23:18.814640Z","iopub.execute_input":"2022-06-01T06:23:18.814965Z","iopub.status.idle":"2022-06-01T06:23:18.917597Z","shell.execute_reply.started":"2022-06-01T06:23:18.814936Z","shell.execute_reply":"2022-06-01T06:23:18.916772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def grp_unique_count(test_df):\n    agg_df = []\n    for col in cat_cols:\n        agg_df.append(test_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-01T06:23:18.918856Z","iopub.execute_input":"2022-06-01T06:23:18.919225Z","iopub.status.idle":"2022-06-01T06:23:18.926216Z","shell.execute_reply.started":"2022-06-01T06:23:18.919193Z","shell.execute_reply":"2022-06-01T06:23:18.925524Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ncat_nc_df = grp_unique_count(test_df)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:23:18.927547Z","iopub.execute_input":"2022-06-01T06:23:18.927951Z","iopub.status.idle":"2022-06-01T06:25:23.819600Z","shell.execute_reply.started":"2022-06-01T06:23:18.927910Z","shell.execute_reply":"2022-06-01T06:25:23.818756Z"},"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-01T06:25:23.821163Z","iopub.execute_input":"2022-06-01T06:25:23.821542Z","iopub.status.idle":"2022-06-01T06:25:23.872596Z","shell.execute_reply.started":"2022-06-01T06:25:23.821512Z","shell.execute_reply":"2022-06-01T06:25:23.871236Z"},"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-01T06:25:23.874223Z","iopub.execute_input":"2022-06-01T06:25:23.874685Z","iopub.status.idle":"2022-06-01T06:26:45.568577Z","shell.execute_reply.started":"2022-06-01T06:25:23.874641Z","shell.execute_reply":"2022-06-01T06:26:45.567100Z"},"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-01T06:26:45.570259Z","iopub.execute_input":"2022-06-01T06:26:45.570634Z","iopub.status.idle":"2022-06-01T06:26:45.705119Z","shell.execute_reply.started":"2022-06-01T06:26:45.570598Z","shell.execute_reply":"2022-06-01T06:26:45.704379Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:26:45.706536Z","iopub.execute_input":"2022-06-01T06:26:45.706944Z","iopub.status.idle":"2022-06-01T06:26:45.820280Z","shell.execute_reply.started":"2022-06-01T06:26:45.706909Z","shell.execute_reply":"2022-06-01T06:26:45.819331Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def grp_num_cols(test_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(test_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-01T06:26:45.821846Z","iopub.execute_input":"2022-06-01T06:26:45.822344Z","iopub.status.idle":"2022-06-01T06:26:45.834084Z","shell.execute_reply.started":"2022-06-01T06:26:45.822284Z","shell.execute_reply":"2022-06-01T06:26:45.833319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nnum_cols_df = grp_num_cols(test_df)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:26:45.835878Z","iopub.execute_input":"2022-06-01T06:26:45.836297Z","iopub.status.idle":"2022-06-01T06:43:29.802117Z","shell.execute_reply.started":"2022-06-01T06:26:45.836260Z","shell.execute_reply":"2022-06-01T06:43:29.800944Z"},"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-01T06:44:25.731199Z","iopub.execute_input":"2022-06-01T06:44:25.731588Z","iopub.status.idle":"2022-06-01T06:49:05.345137Z","shell.execute_reply.started":"2022-06-01T06:44:25.731555Z","shell.execute_reply":"2022-06-01T06:49:05.344250Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del(num_cols_df)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:49:05.346708Z","iopub.execute_input":"2022-06-01T06:49:05.347030Z","iopub.status.idle":"2022-06-01T06:49:05.365164Z","shell.execute_reply.started":"2022-06-01T06:49:05.347000Z","shell.execute_reply":"2022-06-01T06:49:05.364190Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:49:05.366426Z","iopub.execute_input":"2022-06-01T06:49:05.367201Z","iopub.status.idle":"2022-06-01T06:49:05.484445Z","shell.execute_reply.started":"2022-06-01T06:49:05.367164Z","shell.execute_reply":"2022-06-01T06:49:05.483424Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cat_cols_df = pd.read_pickle('./cat_cols_df.pkl', compression='gzip')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:57:08.627545Z","iopub.execute_input":"2022-06-01T06:57:08.628523Z","iopub.status.idle":"2022-06-01T06:57:09.813923Z","shell.execute_reply.started":"2022-06-01T06:57:08.628480Z","shell.execute_reply":"2022-06-01T06:57:09.812923Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"num_cols_df = pd.read_pickle('./num_cols_df.pkl', compression='gzip')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:57:40.951692Z","iopub.execute_input":"2022-06-01T06:57:40.952170Z","iopub.status.idle":"2022-06-01T06:57:54.434176Z","shell.execute_reply.started":"2022-06-01T06:57:40.952133Z","shell.execute_reply":"2022-06-01T06:57:54.433335Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"final_test_df = pd.concat([cat_cols_df, num_cols_df], axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:58:32.271027Z","iopub.execute_input":"2022-06-01T06:58:32.271407Z","iopub.status.idle":"2022-06-01T06:58:33.218970Z","shell.execute_reply.started":"2022-06-01T06:58:32.271376Z","shell.execute_reply":"2022-06-01T06:58:33.217999Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del(cat_cols_df)\ndel(num_cols_df)","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:59:17.356710Z","iopub.execute_input":"2022-06-01T06:59:17.357126Z","iopub.status.idle":"2022-06-01T06:59:17.388237Z","shell.execute_reply.started":"2022-06-01T06:59:17.357095Z","shell.execute_reply":"2022-06-01T06:59:17.386848Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:59:23.588574Z","iopub.execute_input":"2022-06-01T06:59:23.589948Z","iopub.status.idle":"2022-06-01T06:59:23.749943Z","shell.execute_reply.started":"2022-06-01T06:59:23.589775Z","shell.execute_reply":"2022-06-01T06:59:23.748889Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"final_test_df.to_pickle('test_agg.pkl', compression='gzip')","metadata":{"execution":{"iopub.status.busy":"2022-06-01T06:59:26.983804Z","iopub.execute_input":"2022-06-01T06:59:26.984519Z","iopub.status.idle":"2022-06-01T07:05:30.906903Z","shell.execute_reply.started":"2022-06-01T06:59:26.984476Z","shell.execute_reply":"2022-06-01T07:05:30.905832Z"},"trusted":true},"execution_count":null,"outputs":[]}]}