amex-data-preparation_V29.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c1cc89ef",
   "metadata": {
    "_cell_guid": "dcb2670b-869a-474d-b788-5c77bdbda89a",
    "_uuid": "ec0a3025-123f-4845-8547-11373afbdc3d",
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:04.748287Z",
     "iopub.status.busy": "2022-08-03T16:17:04.747788Z",
     "iopub.status.idle": "2022-08-03T16:17:09.197951Z",
     "shell.execute_reply": "2022-08-03T16:17:09.197122Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "papermill": {
     "duration": 4.462335,
     "end_time": "2022-08-03T16:17:09.200187",
     "exception": false,
     "start_time": "2022-08-03T16:17:04.737852",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd \n",
    "import time\n",
    "\n",
    "import joblib\n",
    "from tqdm import tqdm\n",
    "import cudf \n",
    "from itertools import combinations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b2199030",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:09.229502Z",
     "iopub.status.busy": "2022-08-03T16:17:09.229194Z",
     "iopub.status.idle": "2022-08-03T16:17:09.243448Z",
     "shell.execute_reply": "2022-08-03T16:17:09.242760Z"
    },
    "papermill": {
     "duration": 0.023656,
     "end_time": "2022-08-03T16:17:09.245090",
     "exception": false,
     "start_time": "2022-08-03T16:17:09.221434",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "permutationimportance = pd.read_csv(\"../input/permutationimportance-amex/permutationimportance_repeats3_meantransposed_v1.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "284403a2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:09.260009Z",
     "iopub.status.busy": "2022-08-03T16:17:09.259706Z",
     "iopub.status.idle": "2022-08-03T16:17:09.263541Z",
     "shell.execute_reply": "2022-08-03T16:17:09.262703Z"
    },
    "papermill": {
     "duration": 0.013104,
     "end_time": "2022-08-03T16:17:09.265201",
     "exception": false,
     "start_time": "2022-08-03T16:17:09.252097",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#permutationimportance_neg = permutationimportance[permutationimportance[\"importance\"] < 0].column.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80c4ff31",
   "metadata": {
    "papermill": {
     "duration": 0.006855,
     "end_time": "2022-08-03T16:17:09.278865",
     "exception": false,
     "start_time": "2022-08-03T16:17:09.272010",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Constants:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f5b9aa40",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:09.293518Z",
     "iopub.status.busy": "2022-08-03T16:17:09.293245Z",
     "iopub.status.idle": "2022-08-03T16:17:09.297303Z",
     "shell.execute_reply": "2022-08-03T16:17:09.296494Z"
    },
    "papermill": {
     "duration": 0.013172,
     "end_time": "2022-08-03T16:17:09.298989",
     "exception": false,
     "start_time": "2022-08-03T16:17:09.285817",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "RANDOM_STATE = 42\n",
    "\n",
    "NAN_VALUE = -127\n",
    "\n",
    "#k-folds:\n",
    "FOLDS = 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b01d3b8c",
   "metadata": {
    "papermill": {
     "duration": 0.00657,
     "end_time": "2022-08-03T16:17:09.334456",
     "exception": false,
     "start_time": "2022-08-03T16:17:09.327886",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Processing and feature engineering:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "96ef7a89",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:09.349986Z",
     "iopub.status.busy": "2022-08-03T16:17:09.349445Z",
     "iopub.status.idle": "2022-08-03T16:17:09.386124Z",
     "shell.execute_reply": "2022-08-03T16:17:09.385443Z"
    },
    "papermill": {
     "duration": 0.046492,
     "end_time": "2022-08-03T16:17:09.387760",
     "exception": false,
     "start_time": "2022-08-03T16:17:09.341268",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def process_and_feature(df):\n",
    "    df = df.sort_values(['customer_ID','S_2']) \n",
    "    df = df.reset_index(drop=True)\n",
    "    \n",
    "    df[\"S_2\"] = cudf.to_datetime(df[\"S_2\"])\n",
    "    \n",
    "    # setting up permutation importance variables:\n",
    "    permutationimportance_neg = permutationimportance[:50].column.values\n",
    "    top3 = permutationimportance[-3:].column.values\n",
    "    top5 = permutationimportance[-5:].column.values\n",
    "    top7 = permutationimportance[-7:].column.values\n",
    "    top10 = permutationimportance[-10:].column.values\n",
    "    top15 = permutationimportance[-15:].column.values\n",
    "    top25 = permutationimportance[-25:].column.values\n",
    "\n",
    "    # set np seed:\n",
    "    np.random.seed(RANDOM_STATE)\n",
    "    \n",
    "    # sorting on customer ID and then time, to use last properly\n",
    "    df = df.sort_values(['customer_ID','S_2'])\n",
    "    \n",
    "    \n",
    "    cat_col = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\n",
    "    num_col = [col for col in df.columns if col not in (cat_col + [\"customer_ID\", \"S_2\",\"target\"])]\n",
    "    all_col = num_col + cat_col\n",
    "\n",
    "    def convert64to32(df):\n",
    "    #float64 and int64 to 32 : https://www.kaggle.com/code/ragnar123/amex-lgbm-dart-cv-0-7977\n",
    "        float64_cols = list(df.dtypes[df.dtypes == \"float64\"].index)\n",
    "        for col in float64_cols:\n",
    "            df[col] = df[col].astype(np.float32)\n",
    "        int64_cols = list(df.dtypes[df.dtypes == \"int64\"].index)\n",
    "        for col in int64_cols:\n",
    "            df[col] = df[col].astype(np.int32)\n",
    "        return df\n",
    "\n",
    "    # mean and sum of the different sort of variables\n",
    "    D_col_num = []\n",
    "    S_col_num = []\n",
    "    P_col_num = []\n",
    "    B_col_num = []\n",
    "    R_col_num = []\n",
    "    for col in df.columns:\n",
    "        if col not in (cat_col + [\"customer_ID\", \"S_2\"]):\n",
    "            if \"D_\" in col:\n",
    "                D_col_num.append(col)\n",
    "            elif \"S_\" in col:\n",
    "                S_col_num.append(col)\n",
    "            elif \"P_\" in col:\n",
    "                P_col_num.append(col)\n",
    "            elif \"B_\" in col:\n",
    "                B_col_num.append(col)\n",
    "            elif \"R_\" in col:\n",
    "                R_col_num.append(col)\n",
    "        else:\n",
    "            pass\n",
    "    df[\"D_mean\"] = df[D_col_num].mean(axis=1)\n",
    "    df[\"D_sum\"] = df[D_col_num].sum(axis=1)\n",
    "    df[\"S_mean\"] = df[S_col_num].mean(axis=1)\n",
    "    df[\"S_sum\"] = df[S_col_num].sum(axis=1)\n",
    "    df[\"P_mean\"] = df[P_col_num].mean(axis=1)\n",
    "    df[\"P_sum\"] = df[P_col_num].sum(axis=1)\n",
    "    df[\"B_mean\"] = df[B_col_num].mean(axis=1)\n",
    "    df[\"B_sum\"] = df[B_col_num].sum(axis=1)\n",
    "    df[\"R_mean\"] = df[R_col_num].mean(axis=1)\n",
    "    df[\"R_sum\"] = df[R_col_num].sum(axis=1)\n",
    "\n",
    "    var_groups_cols = [\"D_mean\",\"S_mean\",\"P_mean\",\"B_mean\",\"R_mean\",\n",
    "                       \"D_sum\",\"S_sum\",\"P_sum\",\"B_sum\",\"R_sum\",\n",
    "                      ]\n",
    "    df_var_groups = df.groupby(\"customer_ID\")[var_groups_cols].agg([\"last\",\"max\",\"mean\"])\n",
    "    df_var_groups.columns = ['_'.join(x) for x in df_var_groups.columns]\n",
    "    df_var_groups = convert64to32(df_var_groups)\n",
    "\n",
    "    ######################\n",
    "    # P_2 ratios:   \n",
    "    #P_2÷B_9\n",
    "    df[\"P2divB9\"] = df[\"P_2\"]/df[\"B_9\"]\n",
    "    \n",
    "#     P2_ratio_col = [\"D_42\", \"B_3\",\"B_1\",\"B_4\",\"D_39\"]\n",
    "#     P2_ratio_col_finished = []\n",
    "#     for col in P2_ratio_col:\n",
    "#         df[f\"P2-{col}-ratio\"] = df[col]/df[\"P_2\"]\n",
    "#         P2_ratio_col_finished.append(f\"P2-{col}-ratio\")\n",
    "\n",
    "    P2_ratio_col_finished = [\"P2divB9\"]\n",
    "    df_P2_ratios = df.groupby(\"customer_ID\")[P2_ratio_col_finished].agg([\"last\",\"min\",\"max\",\"mean\"])\n",
    "    df_P2_ratios.columns = ['_'.join(x) for x in df_P2_ratios.columns]\n",
    "    #################################\n",
    "\n",
    "    \n",
    "    # count:\n",
    "    df_count = df.groupby(\"customer_ID\")[\"S_2\"].agg([\"count\"])\n",
    "#     for col in df_count.columns:\n",
    "#         df_count[col] = df_count[col].astype(np.int8) # moved below\n",
    "    \n",
    "    # count non NaN values:\n",
    "#     df_nonNANcount = df.groupby(\"customer_ID\")[all_col][df[all_col] != -127].count().sort_index()\n",
    "#     df_nonNANcount.columns = [f\"{col}_nonNAN\" for col in df_nonNANcount.columns]\n",
    "#     for col in df_nonNANcount.columns:\n",
    "#         df_nonNANcount[col] = df_nonNANcount[col].astype(\"int8\")\n",
    "        \n",
    "    \n",
    "    # time difference:\n",
    "    df_date = df.groupby(\"customer_ID\")[[\"S_2\"]].agg([\"first\",\"last\"])\n",
    "    df_date.columns = ['_'.join(x) for x in df_date.columns]\n",
    "    df_date[\"S_2_diff\"] = ((df_date[\"S_2_last\"] - df_date[\"S_2_first\"])/np.timedelta64(1, 'D'))\n",
    "    df_date[\"S_2_diff\"] = df_date[\"S_2_diff\"].astype(\"int16\")\n",
    "    df_date = df_date.drop(columns=[\"S_2_first\",\"S_2_last\"])\n",
    "    \n",
    "    # time variables\n",
    "    df[\"weekday\"] = df.S_2.dt.weekday\n",
    "    df[\"month\"] = df.S_2.dt.month\n",
    "    df[\"percofyear\"] = np.round(((df.S_2.dt.dayofyear / 365) * 100), 1)\n",
    "    df[\"percofmonth\"] = np.round(((df.S_2.dt.day / df.S_2.dt.days_in_month) * 100), 1)\n",
    "    df[\"quarter\"] = df.S_2.dt.quarter\n",
    "    time_col = [\"weekday\",\"month\",\"percofyear\",\"percofmonth\",\"quarter\"]\n",
    "    df_time = df.groupby(\"customer_ID\")[time_col].agg([\"last\"])\n",
    "    df_time.columns = ['_'.join(x) for x in df_time.columns]\n",
    "    df_time = convert64to32(df_time)\n",
    "    for col in [\"weekday_last\", \"month_last\", \"quarter_last\"]:\n",
    "        df_time[col] = df_time[col].astype(\"int8\")\n",
    "        \n",
    "    # 2nd last variables\n",
    "    all_2nd_col = all_col + var_groups_cols + P2_ratio_col_finished\n",
    "    df_2ndlast = df.groupby(\"customer_ID\")[all_2nd_col].nth(-2) #, as_index=False\n",
    "    df_2ndlast.columns = [str(x) + \"_2ndlast\" for x in df_2ndlast.columns]\n",
    "    # https://www.kaggle.com/competitions/amex-default-prediction/discussion/332880#1830894 @Carl McBride Ellis\n",
    "    df_2ndlast = convert64to32(df_2ndlast)\n",
    "    \n",
    "    # categorical columns:\n",
    "    df_cat = df.groupby(\"customer_ID\")[cat_col].agg([\"count\",\"first\",\"last\",\"nunique\"]) \n",
    "    df_cat.columns = ['_'.join(x) for x in df_cat.columns]\n",
    "    df_cat = convert64to32(df_cat)\n",
    "    \n",
    "    # Numerical columns:\n",
    "    df_num = df.groupby(\"customer_ID\")[num_col].agg([\"first\",\"mean\",\"std\",\"min\",\"max\",\"last\"]) \n",
    "    df_num.columns = ['_'.join(x) for x in df_num.columns]\n",
    "    df_num = convert64to32(df_num)\n",
    "    \n",
    "    ######\n",
    "    global last_col\n",
    "    last_col = all_col #+ var_groups_cols\n",
    "    last_col = [f\"{col}_last\" for col in last_col]\n",
    "    last_col = [col for col in last_col if col not in permutationimportance_neg]\n",
    "    \n",
    "    \n",
    "    \n",
    "    ############# Concat ######################################################\n",
    "    print(\"concat\")\n",
    "    df = cudf.concat([df_date,df_time, df_count,df_cat, df_num, df_var_groups, df_2ndlast,df_P2_ratios], axis=1) \n",
    "    \n",
    "    \n",
    "    \n",
    "    del df_date,df_time, df_count,df_cat, df_num, df_var_groups, df_2ndlast, df_P2_ratios\n",
    "    gc.collect()\n",
    "    \n",
    "\n",
    "    \n",
    "    \n",
    "    # fill in NaN for 2ndlast with only 1 customer:\n",
    "    \n",
    "    for col in all_2nd_col:\n",
    "        df[f\"{col}_2ndlast\"].fillna(df[f\"{col}_last\"], inplace=True) \n",
    "    \n",
    "    # second stage:\n",
    "    \n",
    "    for col in num_col:\n",
    "        df[f\"{col}_last-mean\"] = df[f\"{col}_last\"] - df[f\"{col}_mean\"]\n",
    "#         df[f\"{col}_MaxdivMin\"] = df[f\"{col}_max\"] - df[f\"{col}_min\"]\n",
    "\n",
    "        # new ones V22\n",
    "        df[f\"{col}_last-first\"] = df[f\"{col}_last\"] - df[f\"{col}_first\"]\n",
    "        df[f\"{col}_lastDIVfirst\"] = (df[f\"{col}_last\"]+0.0001) / (df[f\"{col}_first\"]+0.0001) # +0.0001 to prevent inf\n",
    "\n",
    "        \n",
    "    for col in all_2nd_col:\n",
    "        df[f\"{col}_last-2ndlast\"] = df[f\"{col}_last\"] - df[f\"{col}_2ndlast\"]\n",
    "#         df = df.drop(columns=f\"{col}_2ndlast\")\n",
    "\n",
    "    df = df.reset_index()\n",
    "    \n",
    "    #permutation importance features:\n",
    "    \n",
    "    top_dic = {\"top3\":top3, \"top5\":top5,\"top7\":top7,\"top10\":top10,\"top15\":top15,\"top25\":top25}\n",
    "    for top in top_dic:\n",
    "        df[f\"{top}_mean\"] = df[top_dic[top]].mean(axis=1)\n",
    "        df[f\"{top}_sum\"] = df[top_dic[top]].sum(axis=1)\n",
    "        df[f\"{top}_min\"] = df[top_dic[top]].min(axis=1)\n",
    "        df[f\"{top}_max\"] = df[top_dic[top]].max(axis=1)\n",
    "    #### \n",
    "    skip_list = []\n",
    "    top5combinations = combinations(top5, 2)\n",
    "    for i in top5combinations:\n",
    "        df[f\"{i[0]}DIV{i[1]}\"] = (df[i[0]]+0.0001)/(df[i[1]]+0.0001)\n",
    "        df[f\"{i[0]}MIN{i[1]}\"] = df[i[0]]-df[i[1]]\n",
    "    for i in top5:\n",
    "        df[f\"{i}_top5perc\"] = df[i]/ df[\"top5_sum\"] * 100\n",
    "        df[f\"{i}_top5perc\"] = df[f\"{i}_top5perc\"].astype(\"int8\")\n",
    "        \n",
    "    float64_cols = list(df.dtypes[df.dtypes == \"float64\"].index)\n",
    "    for col in float64_cols:\n",
    "        df[col] = df[col].astype(np.float32)     \n",
    "    count_cols = [col for col in df.columns if \"count\" in col]\n",
    "    for col in count_cols:\n",
    "        df[col] = df[col].astype(np.int8)\n",
    "    nunique_cols = [col for col in df.columns if \"nunique\" in col]\n",
    "    for col in nunique_cols:\n",
    "        df[col] = df[col].astype(np.int8)\n",
    "    # dropping columns:\n",
    "    df = df.drop(columns=permutationimportance_neg)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4dcea825",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:09.425754Z",
     "iopub.status.busy": "2022-08-03T16:17:09.425506Z",
     "iopub.status.idle": "2022-08-03T16:17:09.431770Z",
     "shell.execute_reply": "2022-08-03T16:17:09.430980Z"
    },
    "papermill": {
     "duration": 0.015981,
     "end_time": "2022-08-03T16:17:09.433470",
     "exception": false,
     "start_time": "2022-08-03T16:17:09.417489",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def train_process():\n",
    "    # read in data:\n",
    "    train = cudf.read_parquet(\"../input/amex-data-integer-dtypes-parquet-format/train.parquet\")\n",
    "    # Categorical columns are label encoded and missing values are replaced by -1 @Raddar\n",
    "\n",
    "    train[\"customer_ID\"] = train[\"customer_ID\"].str[-16:].str.hex_to_int().astype(\"int64\") # customer ID to hexadecimal, not needed for test\n",
    "    \n",
    "    \n",
    "    # feature engineering:\n",
    "    train = process_and_feature(train) \n",
    "    \n",
    "\n",
    "    # del train\n",
    "    gc.collect()\n",
    "    \n",
    "    train_labels = cudf.read_csv(\"../input/amex-default-prediction/train_labels.csv\")\n",
    "    \n",
    "\n",
    "    \n",
    "    train_labels[\"customer_ID\"] = train_labels[\"customer_ID\"].str[-16:].str.hex_to_int().astype(\"int64\") # not needed for test\n",
    "    \n",
    "    # add target labels:\n",
    "    train_labels.target = train_labels.target.astype('int8')\n",
    "    train = train.merge(train_labels, on=\"customer_ID\")\n",
    "    train = train.sort_values(\"customer_ID\").reset_index(drop=True)\n",
    "\n",
    "    del train_labels\n",
    "    gc.collect()\n",
    "    print(f\"shape:{train.shape}\")\n",
    "    \n",
    "    train = train.to_pandas() # free up GPU\n",
    "     \n",
    "    gc.collect()\n",
    "    return train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4cd6ab69",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:09.448389Z",
     "iopub.status.busy": "2022-08-03T16:17:09.448130Z",
     "iopub.status.idle": "2022-08-03T16:17:58.317947Z",
     "shell.execute_reply": "2022-08-03T16:17:58.317073Z"
    },
    "papermill": {
     "duration": 48.879858,
     "end_time": "2022-08-03T16:17:58.320163",
     "exception": false,
     "start_time": "2022-08-03T16:17:09.440305",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "concat\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/cupy/_statistics/order.py:111: RuntimeWarning: All-NaN slice encountered\n",
      "  warnings.warn('All-NaN slice encountered', RuntimeWarning)\n",
      "/opt/conda/lib/python3.7/site-packages/cupy/_statistics/order.py:142: RuntimeWarning: All-NaN slice encountered\n",
      "  warnings.warn('All-NaN slice encountered', RuntimeWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape:(458913, 2077)\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "train_final = train_process() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "47139743",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:58.337691Z",
     "iopub.status.busy": "2022-08-03T16:17:58.336185Z",
     "iopub.status.idle": "2022-08-03T16:17:58.406918Z",
     "shell.execute_reply": "2022-08-03T16:17:58.406057Z"
    },
    "papermill": {
     "duration": 0.080864,
     "end_time": "2022-08-03T16:17:58.408743",
     "exception": false,
     "start_time": "2022-08-03T16:17:58.327879",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_ID</th>\n",
       "      <th>S_2_diff</th>\n",
       "      <th>weekday_last</th>\n",
       "      <th>month_last</th>\n",
       "      <th>percofyear_last</th>\n",
       "      <th>percofmonth_last</th>\n",
       "      <th>quarter_last</th>\n",
       "      <th>count</th>\n",
       "      <th>B_30_count</th>\n",
       "      <th>B_30_first</th>\n",
       "      <th>...</th>\n",
       "      <th>B_3_lastDIVP_2_last</th>\n",
       "      <th>B_3_lastMINP_2_last</th>\n",
       "      <th>D_42_maxDIVP_2_last</th>\n",
       "      <th>D_42_maxMINP_2_last</th>\n",
       "      <th>B_4_last_top5perc</th>\n",
       "      <th>B_1_last_top5perc</th>\n",
       "      <th>B_3_last_top5perc</th>\n",
       "      <th>D_42_max_top5perc</th>\n",
       "      <th>P_2_last_top5perc</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-9223358381327749917</td>\n",
       "      <td>346</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>19.500000</td>\n",
       "      <td>38.700001</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1.574852</td>\n",
       "      <td>0.222932</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>86</td>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-9223193039457028513</td>\n",
       "      <td>358</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>18.900000</td>\n",
       "      <td>32.299999</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002899</td>\n",
       "      <td>-0.998569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-9223189665817919541</td>\n",
       "      <td>364</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>9.700000</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.006249</td>\n",
       "      <td>-0.689835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-9223188534444851899</td>\n",
       "      <td>365</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>23.600000</td>\n",
       "      <td>87.099998</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012012</td>\n",
       "      <td>-0.778579</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-9223173911659837606</td>\n",
       "      <td>346</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>18.900000</td>\n",
       "      <td>32.299999</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>14.510227</td>\n",
       "      <td>0.548325</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>98</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458908</th>\n",
       "      <td>9223275399009481284</td>\n",
       "      <td>372</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>23.600000</td>\n",
       "      <td>87.099998</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.371197</td>\n",
       "      <td>0.186412</td>\n",
       "      <td>0.462203</td>\n",
       "      <td>-0.270078</td>\n",
       "      <td>95</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458909</th>\n",
       "      <td>9223300670094807586</td>\n",
       "      <td>380</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>23.299999</td>\n",
       "      <td>83.900002</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.763024</td>\n",
       "      <td>0.208749</td>\n",
       "      <td>2.201805</td>\n",
       "      <td>0.142299</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458910</th>\n",
       "      <td>9223303087902649707</td>\n",
       "      <td>369</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>22.200001</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.199910</td>\n",
       "      <td>-0.295234</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458911</th>\n",
       "      <td>9223345210145379887</td>\n",
       "      <td>355</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>21.400000</td>\n",
       "      <td>61.299999</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.069707</td>\n",
       "      <td>-0.839763</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458912</th>\n",
       "      <td>9223350112805974911</td>\n",
       "      <td>365</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>22.200001</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.484421</td>\n",
       "      <td>0.454126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>94</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>458913 rows × 2077 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                customer_ID  S_2_diff  weekday_last  month_last  \\\n",
       "0      -9223358381327749917       346             0           3   \n",
       "1      -9223193039457028513       358             5           3   \n",
       "2      -9223189665817919541       364             5           3   \n",
       "3      -9223188534444851899       365             1           3   \n",
       "4      -9223173911659837606       346             5           3   \n",
       "...                     ...       ...           ...         ...   \n",
       "458908  9223275399009481284       372             1           3   \n",
       "458909  9223300670094807586       380             0           3   \n",
       "458910  9223303087902649707       369             3           3   \n",
       "458911  9223345210145379887       355             0           3   \n",
       "458912  9223350112805974911       365             3           3   \n",
       "\n",
       "        percofyear_last  percofmonth_last  quarter_last  count  B_30_count  \\\n",
       "0             19.500000         38.700001             1     13          13   \n",
       "1             18.900000         32.299999             1     13          13   \n",
       "2             17.000000          9.700000             1     13          13   \n",
       "3             23.600000         87.099998             1     13          13   \n",
       "4             18.900000         32.299999             1     13          13   \n",
       "...                 ...               ...           ...    ...         ...   \n",
       "458908        23.600000         87.099998             1     13          13   \n",
       "458909        23.299999         83.900002             1     13          13   \n",
       "458910        22.200001         71.000000             1     13          13   \n",
       "458911        21.400000         61.299999             1     13          13   \n",
       "458912        22.200001         71.000000             1     13          13   \n",
       "\n",
       "        B_30_first  ...  B_3_lastDIVP_2_last  B_3_lastMINP_2_last  \\\n",
       "0                1  ...             1.574852             0.222932   \n",
       "1                0  ...             0.002899            -0.998569   \n",
       "2                0  ...             0.006249            -0.689835   \n",
       "3                0  ...             0.012012            -0.778579   \n",
       "4                1  ...            14.510227             0.548325   \n",
       "...            ...  ...                  ...                  ...   \n",
       "458908           0  ...             1.371197             0.186412   \n",
       "458909           0  ...             2.763024             0.208749   \n",
       "458910           0  ...             0.199910            -0.295234   \n",
       "458911           0  ...             0.069707            -0.839763   \n",
       "458912           0  ...             2.484421             0.454126   \n",
       "\n",
       "        D_42_maxDIVP_2_last  D_42_maxMINP_2_last  B_4_last_top5perc  \\\n",
       "0                       NaN                  NaN                 86   \n",
       "1                       NaN                  NaN                  0   \n",
       "2                       NaN                  NaN                  0   \n",
       "3                       NaN                  NaN                  0   \n",
       "4                       NaN                  NaN                 98   \n",
       "...                     ...                  ...                ...   \n",
       "458908             0.462203            -0.270078                 95   \n",
       "458909             2.201805             0.142299                 97   \n",
       "458910                  NaN                  NaN                 96   \n",
       "458911                  NaN                  NaN                 74   \n",
       "458912                  NaN                  NaN                 94   \n",
       "\n",
       "        B_1_last_top5perc  B_3_last_top5perc  D_42_max_top5perc  \\\n",
       "0                       4                5.0                NaN   \n",
       "1                       0                0.0                NaN   \n",
       "2                       1                0.0                NaN   \n",
       "3                       0                1.0                NaN   \n",
       "4                       0                1.0                NaN   \n",
       "...                   ...                ...                ...   \n",
       "458908                  0                1.0                0.0   \n",
       "458909                  0                0.0                0.0   \n",
       "458910                  1                0.0                NaN   \n",
       "458911                  1                1.0                NaN   \n",
       "458912                  2                1.0                NaN   \n",
       "\n",
       "        P_2_last_top5perc  target  \n",
       "0                     3.0       1  \n",
       "1                    99.0       0  \n",
       "2                    97.0       0  \n",
       "3                    98.0       0  \n",
       "4                     0.0       1  \n",
       "...                   ...     ...  \n",
       "458908                1.0       0  \n",
       "458909                0.0       1  \n",
       "458910                2.0       1  \n",
       "458911               22.0       0  \n",
       "458912                0.0       1  \n",
       "\n",
       "[458913 rows x 2077 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b43fff98",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:17:58.595853Z",
     "iopub.status.busy": "2022-08-03T16:17:58.595557Z",
     "iopub.status.idle": "2022-08-03T16:18:37.444133Z",
     "shell.execute_reply": "2022-08-03T16:18:37.442349Z"
    },
    "papermill": {
     "duration": 38.859985,
     "end_time": "2022-08-03T16:18:37.447162",
     "exception": false,
     "start_time": "2022-08-03T16:17:58.587177",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "train_final.to_parquet(\"train_processed.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "f2c997fe",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:18:37.473297Z",
     "iopub.status.busy": "2022-08-03T16:18:37.472850Z",
     "iopub.status.idle": "2022-08-03T16:18:37.675476Z",
     "shell.execute_reply": "2022-08-03T16:18:37.674530Z"
    },
    "papermill": {
     "duration": 0.218499,
     "end_time": "2022-08-03T16:18:37.678355",
     "exception": false,
     "start_time": "2022-08-03T16:18:37.459856",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del train_final\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df0900d8",
   "metadata": {
    "papermill": {
     "duration": 0.014618,
     "end_time": "2022-08-03T16:18:37.709879",
     "exception": false,
     "start_time": "2022-08-03T16:18:37.695261",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# test:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1d7a0265",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:18:37.735165Z",
     "iopub.status.busy": "2022-08-03T16:18:37.734677Z",
     "iopub.status.idle": "2022-08-03T16:18:37.903364Z",
     "shell.execute_reply": "2022-08-03T16:18:37.902648Z"
    },
    "papermill": {
     "duration": 0.183967,
     "end_time": "2022-08-03T16:18:37.905605",
     "exception": false,
     "start_time": "2022-08-03T16:18:37.721638",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clear GPU memory\n",
    "# torch.cuda.empty_cache()\n",
    "# cuda.select_device(0)\n",
    "# cuda.close()\n",
    "# cuda.select_device(0)\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "654c5e4f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-08-03T16:18:37.931949Z",
     "iopub.status.busy": "2022-08-03T16:18:37.931557Z",
     "iopub.status.idle": "2022-08-03T16:23:32.890682Z",
     "shell.execute_reply": "2022-08-03T16:23:32.888969Z"
    },
    "papermill": {
     "duration": 294.974309,
     "end_time": "2022-08-03T16:23:32.892876",
     "exception": false,
     "start_time": "2022-08-03T16:18:37.918567",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "chunk:0\n",
      "chunk#0, start chunk id 0, end_chunk id 1420901\n",
      "loaded chunk\n",
      "concat\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/cupy/_statistics/order.py:111: RuntimeWarning: All-NaN slice encountered\n",
      "  warnings.warn('All-NaN slice encountered', RuntimeWarning)\n",
      "/opt/conda/lib/python3.7/site-packages/cupy/_statistics/order.py:142: RuntimeWarning: All-NaN slice encountered\n",
      "  warnings.warn('All-NaN slice encountered', RuntimeWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "feature engineered\n",
      "chunk:1\n",
      "chunk#1, start chunk id 1420901, end_chunk id 2841209\n",
      "loaded chunk\n",
      "concat\n",
      "feature engineered\n",
      "chunk:2\n",
      "chunk#2, start chunk id 2841209, end_chunk id 4261572\n",
      "loaded chunk\n",
      "concat\n",
      "feature engineered\n",
      "chunk:3\n",
      "chunk#3, start chunk id 4261572, end_chunk id 5681053\n",
      "loaded chunk\n",
      "concat\n",
      "feature engineered\n",
      "chunk:4\n",
      "chunk#4, start chunk id 5681053, end_chunk id 7102344\n",
      "loaded chunk\n",
      "concat\n",
      "feature engineered\n",
      "chunk:5\n",
      "chunk#5, start chunk id 7102344, end_chunk id 8523150\n",
      "loaded chunk\n",
      "concat\n",
      "feature engineered\n",
      "chunk:6\n",
      "chunk#6, start chunk id 8523150, end_chunk id 9944645\n",
      "loaded chunk\n",
      "concat\n",
      "feature engineered\n",
      "chunk:7\n",
      "chunk#7, start chunk id 9944645, end_chunk id 11363763\n",
      "loaded chunk\n",
      "concat\n",
      "feature engineered\n"
     ]
    }
   ],
   "source": [
    "%%time \n",
    "chunks = 8\n",
    "test_rows = 11363762\n",
    "groupby_test_rows = 924621\n",
    "start_chunk = 0\n",
    "length_chunk = int(groupby_test_rows/chunks)\n",
    "\n",
    "test_full_id = cudf.DataFrame()\n",
    "\n",
    "for chunk in range(chunks):\n",
    "\n",
    "    print(f\"chunk:{chunk}\")\n",
    "    test = cudf.read_parquet(\"../input/amex-data-integer-dtypes-parquet-format/test.parquet\")\n",
    "    test = test.sort_values([\"customer_ID\",\"S_2\"])\n",
    "    test = test.reset_index(drop=True)\n",
    "    if (chunk+1) == chunks:\n",
    "        end_chunk = test_rows +1\n",
    "    else:\n",
    "        end_chunk = test.loc[test[\"customer_ID\"] == test.groupby(\"customer_ID\").agg([\"last\"]).iloc[(length_chunk*(chunk+1))].index[0]].tail(1).index[0]\n",
    "        end_chunk += 1\n",
    "    print(f\"chunk#{chunk}, start chunk id {start_chunk}, end_chunk id {end_chunk}\")\n",
    "    test = test.iloc[start_chunk:end_chunk]\n",
    "    gc.collect()\n",
    "    print(\"loaded chunk\")\n",
    "    test = process_and_feature(test)\n",
    "    gc.collect()\n",
    "    print(\"feature engineered\")\n",
    "    \n",
    "    test = test.sort_values(\"customer_ID\").reset_index(drop=True)\n",
    "    test_full_id = test_full_id.append(test[[\"customer_ID\"]])\n",
    "    test = test.to_pandas()\n",
    "    test.to_parquet(f\"test_processed_chunk{chunk}.parquet\")\n",
    "    \n",
    "    del test\n",
    "    gc.collect()\n",
    "    start_chunk = end_chunk"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 400.671842,
   "end_time": "2022-08-03T16:23:37.587291",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2022-08-03T16:16:56.915449",
   "version": "2.3.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}