{"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":"markdown","source":"# Exponential Averages: AMEX Feature Engineering\n\nA split off of my other feature engineering notebook: https://www.kaggle.com/code/roberthatch/amex-feature-engg-gpu-or-cpu-process-in-chunks\nThe key purpose of this notebook is to demonstate a fast (enough) method to create exponential moving averages (well, not actually moving) of every numeric column. This is a natural fit for this competition, since logically more recent data is more relevant, but capturing the signal from less recent data is both important and non-trivial.\n\nUsing these features here: https://www.kaggle.com/roberthatch/train-with-3000-features-xgb-pyramid\n\nTodo:\nSince 'last' can and should be captured separately, consider creating the ema of all observations EXCEPT last? I created the weights (e.g. ema2_ignore_last) but didn't test it. Customers with single row would just get a bunch of nan, as well. \n","metadata":{}},{"cell_type":"markdown","source":"# Load Libraries","metadata":{}},{"cell_type":"code","source":"# LOAD LIBRARIES\nimport pandas as pd, numpy as np # CPU libraries\nimport gc, os\n\nGPU = True\ntry:\n    import cupy, cudf\nexcept ImportError:\n    GPU = False\n\nif GPU:\n    print('RAPIDS version',cudf.__version__)\nelse:\n    print(\"Disabling cudf, using pandas instead\")\n    cudf = pd","metadata":{"execution":{"iopub.status.busy":"2022-07-11T19:40:45.824519Z","iopub.execute_input":"2022-07-11T19:40:45.824998Z","iopub.status.idle":"2022-07-11T19:40:45.855911Z","shell.execute_reply.started":"2022-07-11T19:40:45.824904Z","shell.execute_reply":"2022-07-11T19:40:45.854971Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Config","metadata":{}},{"cell_type":"code","source":"PROCESS_TEST_DATA = True\n\n# VERSION NAME FOR SAVED PARQUET FILES\nVER = 111\n\n# FILL NAN VALUE\nNAN_VALUE = -127 # will fit in int8\n\nif GPU:\n    TRAIN_NUM_PARTS = 2\n    TEST_SECTIONS = 2\n    TEST_NUM_PARTS = 2\nelse:\n    TRAIN_NUM_PARTS = 6\n    TEST_SECTIONS = 2\n    TEST_NUM_PARTS = 6\n\nprint(\"VER:\", VER)\nif not PROCESS_TEST_DATA:\n    print(\"NOT processing test data!\")","metadata":{"execution":{"iopub.status.busy":"2022-07-11T19:40:45.859108Z","iopub.execute_input":"2022-07-11T19:40:45.859457Z","iopub.status.idle":"2022-07-11T19:40:45.868352Z","shell.execute_reply.started":"2022-07-11T19:40:45.859424Z","shell.execute_reply":"2022-07-11T19:40:45.867566Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Pre-Processing and reading in chunks","metadata":{}},{"cell_type":"code","source":"## Basic reading and formatting from initial raddar parquet file\n\ndef process_customer_columns(df):\n    # REDUCE DTYPE FOR CUSTOMER AND DATE\n    if GPU:\n        df['customer_ID'] = df['customer_ID'].str[-16:].str.hex_to_int().astype('int64')\n    else:\n        df['customer_ID'] = df['customer_ID'].str[-16:].apply(int, base=16).astype('int64')\n    year = cudf.to_numeric(df['S_2'].str[:4])\n    month = cudf.to_numeric(df['S_2'].str[5:7])\n    df['S_2'] = year.mul(12).add(month).sub(24207).astype('int8')\n    return df\n\ndef read_file(path='', usecols=None):\n    # LOAD DATAFRAME\n    if usecols is not None:\n        df = cudf.read_parquet(path, columns=usecols)\n        df = process_customer_columns(df)\n    else:\n        df = cudf.read_parquet(path)\n\n    print('Shape of data:', df.shape)\n    \n    return df","metadata":{"execution":{"iopub.status.busy":"2022-07-11T19:40:45.869703Z","iopub.execute_input":"2022-07-11T19:40:45.870293Z","iopub.status.idle":"2022-07-11T19:40:45.880085Z","shell.execute_reply.started":"2022-07-11T19:40:45.870261Z","shell.execute_reply":"2022-07-11T19:40:45.87929Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## CALCULATE SIZE OF EACH SEPARATE PART\ndef get_rows(customers, df, NUM_PARTS=4, verbose=''):\n    chunk = len(customers)//NUM_PARTS\n    if verbose != '':\n        print(f'We will process {verbose} data as {NUM_PARTS} separate parts.')\n        print(f'There will be {chunk} customers in each part (except the last part).')\n        print('Below are number of rows in each part:')\n    rows = []\n\n    for k in range(NUM_PARTS):\n        if k==NUM_PARTS-1: cc = customers[k*chunk:]\n        else: cc = customers[k*chunk:(k+1)*chunk]\n        s = df.loc[df.customer_ID.isin(cc)].shape[0]\n        rows.append(s)\n    if verbose != '': print( rows )\n    return rows,chunk\n\ndef getAndProcessDataInChunks(filename, is_train=False, NUM_PARTS=4, NUM_SECTIONS=1, split_k=0, verbose=''):\n    gc.collect()\n\n    print(f'Reading customer_IDs from {verbose} data...')\n    df = read_file(path = filename, usecols = ['customer_ID','S_2'])\n    customers = df[['customer_ID']].drop_duplicates().sort_index().values.flatten()\n    rows,num_cust = get_rows(customers, df[['customer_ID']], NUM_PARTS=NUM_PARTS*NUM_SECTIONS, verbose=verbose)\n\n    # INFER DATA IN PARTS\n    skip_rows = 0\n    skip_cust = 0\n    allData = []\n\n    del df\n    gc.collect()\n\n    print(f'\\nReading {verbose} data...')\n    df_file = read_file(path = filename)\n\n    if is_train:\n        assert(NUM_SECTIONS == 1) ## Splitting not implemented for target labels\n        targets = cudf.read_csv('../input/amex-default-prediction/train_labels.csv')\n        if GPU:\n            targets['customer_ID'] = targets['customer_ID'].str[-16:].str.hex_to_int().astype('int64')\n        else:\n            targets['customer_ID'] = targets['customer_ID'].str[-16:].apply(int, base=16).astype('int64')\n        targets = targets.set_index('customer_ID')\n        targets.target = targets.target.astype('int8')\n\n    if NUM_SECTIONS > 1:\n        startRow = 0\n        for i in range(NUM_SECTIONS):\n            if i == split_k:\n                startRow = skip_rows\n            for k in range(NUM_PARTS):\n                skip_rows += rows[i*NUM_PARTS + k]\n            if i == split_k:\n                df_file = df_file.iloc[startRow:skip_rows].reset_index(drop=True)\n                rows = rows[i*NUM_PARTS:(i+1)*NUM_PARTS]\n                gc.collect()\n                skip_rows = 0\n                break\n    for k in range(NUM_PARTS):\n        # READ PART OF DATA\n        df = df_file.iloc[skip_rows:skip_rows+rows[k]].reset_index(drop=True)\n        skip_rows += rows[k]\n        print(f'=> {verbose} part {k+1} has shape', df.shape )\n\n        # PROCESS AND FEATURE ENGINEER PART OF DATA\n        df = process_and_feature_engineer(df)\n\n        if is_train:\n            ## Relies on assumption that initial train data has customer IDs in same sorted order as train_labels.csv\n            if k==NUM_PARTS-1: targetSlice = targets.iloc[skip_cust:]\n            else: targetSlice = targets.iloc[skip_cust:skip_cust+num_cust]\n            skip_cust += num_cust\n\n            print(\"|...\")\n            df = cudf.concat([df, targetSlice], axis=1)\n            print(\" ...|\")\n\n        if GPU:\n            print(\"|...\")\n            df = df.to_pandas()\n            print(\" ...|\")\n\n        allData.append(df)\n        gc.collect()\n\n    print(\".\", end='')\n    del df_file\n    gc.collect()\n    allData = pd.concat(allData, axis=0)\n    del df\n    gc.collect()\n    if is_train:\n        print(\".\", end='')\n        allData = allData.sort_index()\n        gc.collect()\n        print(\".\", end='')\n        allData = allData.reset_index()\n    print(\"|\")\n    return allData","metadata":{"execution":{"iopub.status.busy":"2022-07-11T19:40:45.881718Z","iopub.execute_input":"2022-07-11T19:40:45.882226Z","iopub.status.idle":"2022-07-11T19:40:45.905562Z","shell.execute_reply.started":"2022-07-11T19:40:45.882194Z","shell.execute_reply":"2022-07-11T19:40:45.904727Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature Engineering","metadata":{}},{"cell_type":"code","source":"##\n## Easy to replace or modify this function with your own custom feature engineering,\n## and keep the other boilerplate untouched to allow switching from GPU to CPU based on GPU quota.\n##\ndef process_and_feature_engineer(df):\n    print(\".\", end = '')\n\n    ## Save space on customer ID, and encode S_2 based on month and year as 0-12 for train set, 13-25 or 19-31 for test set.\n    df = process_customer_columns(df)\n\n    print(\".\", end = '')\n\n    ## Consider dropping B_29: https://www.kaggle.com/competitions/amex-default-prediction/discussion/328756\n    df.drop(\"B_29\",inplace=True,axis=1)\n\n    # compute \"after pay\" features\n    # https://www.kaggle.com/code/jiweiliu/rapids-cudf-feature-engineering-xgb \n    for bcol in [f'B_{i}' for i in [11,14,17]]+['D_39','D_131']+[f'S_{i}' for i in [16,23]]:\n        for pcol in ['P_2','P_3']:\n            if bcol in df.columns:\n                result = df[bcol] - df[pcol]\n                df[f'{bcol}-{pcol}'] = result.fillna(0)\n\n    # FEATURE ENGINEERING heavily modified, started from: \n    # https://www.kaggle.com/code/huseyincot/amex-agg-data-how-it-created\n    all_cols = [c for c in list(df.columns) if c not in ['customer_ID']]\n    cat_features = [\"B_30\",\"B_38\",\"D_114\",\"D_116\",\"D_117\",\"D_120\",\"D_126\",\"D_63\",\"D_64\",\"D_66\",\"D_68\"]\n    num_features = [col for col in all_cols if col not in (cat_features + [\"S_2\"])]\n\n    print(\".\", end = '')\n\n    ## For each customer, count all NaN in any row, and count all NaN in the last row. Later we will add it as two columns\n    df_nan = (df.mul(0) + 1).fillna(0)\n    df_nan['customer_ID'] = df['customer_ID']\n    nan_sum = df_nan.groupby(\"customer_ID\").sum().sum(axis=1)\n    nan_last = df_nan.groupby(\"customer_ID\").last().sum(axis=1)\n    del df_nan\n    print(\".\", end = '')\n\n    groups = df.groupby(\"customer_ID\")\n    test_num_agg = groups[num_features].agg(['mean', 'last', 'max'])\n    test_num_agg.columns = ['_'.join(x) for x in test_num_agg.columns]\n\n    print(\"+\", end = '')\n\n    ## TODO: One-hot encode or convert to non-numeric? I believe raddar's clean dataset (and original data?) stores as numeric,\n    ## and XGBoost doesn't(?) have a way to explicitly call out categorical columns\n    test_cat_agg = groups[cat_features].last()\n    test_cat_agg.columns = [x + \"_last\" for x in test_cat_agg.columns]\n\n    print(\".\", end = '')\n\n    ## S_2 test data has different range of values for S_2, normalize 'min' by subtracting max-12. Aka min = min + 12 - max.\n    ## S_2 max will always be the same as last, and (after normalization), the same for every customer.\n    ## Min tells us if they are a shorter term customer or not. Count under 13 tells us they're EITHER a shorter term customer OR they have some gap months.\n    ## TODO: Min is more relevant (much higher default rate for short term customers than for gap customers), but might be worth encoding both into single column.\n    ##   Basically, count + 143 minus 12*min (normal customer gets 156, long term gap customer gets 145-155, short term customer gets 0-143)\n    ##   If not combining into one, could arguably use count+min instead of count, to directly highlight gap customers.\n    test_s2_agg = groups[[\"S_2\"]].agg(['min', 'count', 'max'])\n    test_s2_agg.columns = ['_'.join(x) for x in test_s2_agg.columns]\n    test_s2_agg['S_2_min'] = test_s2_agg['S_2_min'] + 12 - test_s2_agg['S_2_max']\n    test_s2_agg.drop(['S_2_max'],inplace=True,axis=1)\n\n    ## Quick sanity sorting check: confirm last value of each group is from the last (max) statement by checking S_2:\n    assert_s2_check = groups[\"S_2\"].last() - groups[\"S_2\"].max()\n    assert((assert_s2_check == 0).all())\n\n    ### Add match for categorical: 1 if last and next to last are the same, 0 if not. If both are nan, or next to last is nan, treat as the same via fillna(1)\n    ## TODO: in case it matters, move below forwards/backwards fill, just below.\n    test_cat_agg2 = 1 + groups[cat_features].nth(-1) - groups[cat_features].nth(-2)\n    test_cat_agg2 = test_cat_agg2.fillna(1)\n    test_cat_agg2[test_cat_agg2 != 1] = 0\n    test_cat_agg2.columns = [x + '_match' for x in test_cat_agg2.columns]\n\n    print(\".\", end = '')\n\n    ## Add exponential moving average\n    groups = df.groupby(\"customer_ID\")\n\n    print(\".\")\n    e2 = ema(groups, num_features, ema2)\n    print(\"+\")\n    e2.columns = [x + \"_e2\" for x in e2.columns]\n    e3 = ema(groups, num_features, ema3)\n    print(\"+\")\n    e3.columns = [x + \"_e3\" for x in e3.columns]\n    e5 = ema(groups, num_features, ema5)\n    print(\"+\")\n    e5.columns = [x + \"_e5\" for x in e5.columns]\n    e7 = ema(groups, num_features, ema7)\n    print(\"+\")\n    e7.columns = [x + \"_e7\" for x in e7.columns]\n    e11 = ema(groups, num_features, ema11)\n    print(\"+\")\n    e11.columns = [x + \"_e11\" for x in e11.columns]\n\n\n    df = cudf.concat([test_s2_agg, test_num_agg, test_cat_agg, test_cat_agg2, e2, e3, e5, e7, e11], axis=1)\n\n    print(\"*\")\n\n    ## Finally add NaN counts from earlier\n    df[\"total_data_count\"] = nan_sum\n    df[\"total_data_last\"] = nan_last\n\n    ## Per discussion in cdeotte's XGBoost Starter notebook, there's two columns with actual numbers often going below '-127', the default fillna.\n    ## TODO is to handle some categories of nan differently anyways (per raddar's work), and agg often ignores them (which I think is a fine way) anyways.\n    ##   The remaining nans: it's might be better in XGBoost to just leave them there, and let XGBoost decide!\n    nan_col = ['D_50_mean', 'D_50_last', 'S_23_mean', 'S_23_last']\n    df[nan_col] = df[nan_col].fillna(-32783)\n    df = df.fillna(NAN_VALUE)\n\n    print('shape after engineering', df.shape )\n    for col in df.columns:\n        if len(df[col].unique()) == 1:\n            print(\"Consider dropping column!?\", col)\n\n    del test_s2_agg, test_num_agg, test_cat_agg\n    del test_cat_agg2\n    del e2, e3, e5, e7, e11\n\n    return df\n","metadata":{"execution":{"iopub.status.busy":"2022-07-11T19:40:45.953465Z","iopub.execute_input":"2022-07-11T19:40:45.954105Z","iopub.status.idle":"2022-07-11T19:40:46.014971Z","shell.execute_reply.started":"2022-07-11T19:40:45.954071Z","shell.execute_reply":"2022-07-11T19:40:46.01388Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"p2 = 1/3\np3 = 1/2\np5 = 2/3\np7 = 3/4\np11 = 5/6\nema2 = [1.0, p2**1, p2**2, p2**3, p2**4, p2**5, p2**6, p2**7, p2**8, p2**9, p2**10, p2**11, p2**12]\nema3 = [1.0, p3**1, p3**2, p3**3, p3**4, p3**5, p3**6, p3**7, p3**8, p3**9, p3**10, p3**11, p3**12]\nema5 = [1.0, p5**1, p5**2, p5**3, p5**4, p5**5, p5**6, p5**7, p5**8, p5**9, p5**10, p5**11, p5**12]\nema7 = [1.0, p7**1, p7**2, p7**3, p7**4, p7**5, p7**6, p7**7, p7**8, p7**9, p7**10, p7**11, p7**12]\nema11 = [1.0, p11**1, p11**2, p11**3, p11**4, p11**5, p11**6, p11**7, p11**8, p11**9, p11**10, p11**11, p11**12]\nema2_ignore_last = [0.0, p2**1, p2**2, p2**3, p2**4, p2**5, p2**6, p2**7, p2**8, p2**9, p2**10, p2**11, p2**12]\nema3_ignore_last = [0.0, p3**1, p3**2, p3**3, p3**4, p3**5, p3**6, p3**7, p3**8, p3**9, p3**10, p3**11, p3**12]\nema5_ignore_last = [0.0, p5**1, p5**2, p5**3, p5**4, p5**5, p5**6, p5**7, p5**8, p5**9, p5**10, p5**11, p5**12]\nema7_ignore_last = [0.0, p7**1, p7**2, p7**3, p7**4, p7**5, p7**6, p7**7, p7**8, p7**9, p7**10, p7**11, p7**12]\nema11_ignore_last = [0.0, p11**1, p11**2, p11**3, p11**4, p11**5, p11**6, p11**7, p11**8, p11**9, p11**10, p11**11, p11**12]\n\n\ndef ema(groups, columns, weights):\n    x1 = groups[columns].nth(-1)\n    x2 = groups[columns].nth(-2)\n    x3 = groups[columns].nth(-3)\n    x4 = groups[columns].nth(-4)\n    x5 = groups[columns].nth(-5)\n    x6 = groups[columns].nth(-6)\n    x7 = groups[columns].nth(-7)\n    x8 = groups[columns].nth(-8)\n    x9 = groups[columns].nth(-9)\n    x10 = groups[columns].nth(-10)\n    x11 = groups[columns].nth(-11)\n    x12 = groups[columns].nth(-12)\n    x13 = groups[columns].nth(-13)\n    w1 = x1.notna().astype('int8') * weights[0]\n    w2 = x2.notna().astype('int8') * weights[1]\n    w3 = x3.notna().astype('int8') * weights[2]\n    w4 = x4.notna().astype('int8') * weights[3]\n    w5 = x5.notna().astype('int8') * weights[4]\n    w6 = x6.notna().astype('int8') * weights[5]\n    w7 = x7.notna().astype('int8') * weights[6]\n    w8 = x8.notna().astype('int8') * weights[7]\n    w9 = x9.notna().astype('int8') * weights[8]\n    w10 = x10.notna().astype('int8') * weights[9]\n    w11 = x11.notna().astype('int8') * weights[10]\n    w12 = x12.notna().astype('int8') * weights[11]\n    w13 = x13.notna().astype('int8') * weights[12]\n    x1 = x1 * w1\n    x2 = x2 * w2\n    x3 = x3 * w3\n    x4 = x4 * w4\n    x5 = x5 * w5\n    x6 = x6 * w6\n    x7 = x7 * w7\n    x8 = x8 * w8\n    x9 = x9 * w9\n    x10 = x10 * w10\n    x11 = x11 * w11\n    x12 = x12 * w12\n    x13 = x13 * w13\n\n    x = x1.add(x2, fill_value=0).add(x3, fill_value=0).add(x4, fill_value=0).add(x5, fill_value=0).add(x6, fill_value=0).add(x7, fill_value=0).add(x8, fill_value=0).add(x9, fill_value=0).add(x10, fill_value=0).add(x11, fill_value=0).add(x12, fill_value=0).add(x13, fill_value=0)\n    w = w1.add(w2, fill_value=0).add(w3, fill_value=0).add(w4, fill_value=0).add(w5, fill_value=0).add(w6, fill_value=0).add(w7, fill_value=0).add(w8, fill_value=0).add(w9, fill_value=0).add(w10, fill_value=0).add(w11, fill_value=0).add(w12, fill_value=0).add(w13, fill_value=0)\n    x = x / w\n\n    return(x)\n","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# PROCESS TRAIN DATA","metadata":{}},{"cell_type":"code","source":"TRAIN_PATH = '../input/amex-data-integer-dtypes-parquet-format/train.parquet'\ntrain = getAndProcessDataInChunks(TRAIN_PATH, is_train=True, NUM_PARTS=TRAIN_NUM_PARTS, verbose='train')\n\nprint(train.shape)\nprint(train.head())\ntrain.to_parquet(f'train_fe_v{VER}.parquet')\nprint(\"done\")\n","metadata":{"execution":{"iopub.status.busy":"2022-07-11T19:40:46.016854Z","iopub.execute_input":"2022-07-11T19:40:46.017554Z","iopub.status.idle":"2022-07-11T19:51:20.486304Z","shell.execute_reply.started":"2022-07-11T19:40:46.017517Z","shell.execute_reply":"2022-07-11T19:51:20.48555Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# PROCESS TEST DATA","metadata":{}},{"cell_type":"code","source":"if PROCESS_TEST_DATA:\n    del train\n    gc.collect()\n\n    TEST_PATH = '../input/amex-data-integer-dtypes-parquet-format/test.parquet'\n    for k in range(TEST_SECTIONS):\n        test = getAndProcessDataInChunks(TEST_PATH, NUM_PARTS=TEST_NUM_PARTS, NUM_SECTIONS=TEST_SECTIONS, split_k=k, verbose='test')\n\n        print(test.shape)\n        print(test.head())\n        test.to_parquet(f'test{k}_fe_v{VER}.parquet')\n        print(\"done\")\n\n        del test\n        gc.collect()\n","metadata":{"execution":{"iopub.status.busy":"2022-07-11T19:51:20.487839Z","iopub.execute_input":"2022-07-11T19:51:20.488175Z"},"trusted":true},"execution_count":null,"outputs":[]}]}