{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.7.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"gpu","dataSources":[{"sourceId":35332,"databundleVersionId":3723648,"sourceType":"competition"},{"sourceId":3739819,"sourceType":"datasetVersion","datasetId":2231132}],"dockerImageVersionId":30198,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":true}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# AMEX Feature Engineering with GPU *or* CPU! No memory overflow!\n\n\nKey improvements:\n1. Process *both* train and test in chunks.\n2. Handle GPU or CPU, without running out of memory or hitting cudf errors when switching!\n\nDisclaimer: I ran into a lot of gotcha's while getting this to work on both CPU and GPU with same code. I can't 100% guarantee it works *correctly* even now, much less with your own customizations. Use at your own risk. Depending on your feature engineering, it is possible to run out of memory even with increasing the chunk size, however, I do expect it is much more likely to work with your own custom feature engineering code than most other popular starter notebooks.\n\n\nCUDF (GPU) code template and feature engineering acknowledgements:\n1. https://www.kaggle.com/code/cdeotte/xgboost-starter-0-793\n    a. https://www.kaggle.com/datasets/raddar/amex-data-integer-dtypes-parquet-format\n    b. https://www.kaggle.com/competitions/amex-default-prediction/discussion/328514\n    c. https://www.kaggle.com/code/huseyincot/amex-catboost-0-793\n    d. https://www.kaggle.com/code/huseyincot/amex-agg-data-how-it-created\n2. https://www.kaggle.com/code/jiweiliu/rapids-cudf-feature-engineering-xgb \n\n\nFeature Engineering, besides in above notebooks:\n1. Convert date-time to simple 0-12 value based on year and month. Ignore day. Add S_2_min and S_2_count to the feature set. Normalize S_2_min.\n2. Don't fill NaN until *after* creating aggregation features. NaN will be ignored when calculating std, mean, etc.\n3. Add delta columns, and match columns, comparing last row vs prior row.\n4. Removed 6 engineered columns that, in batches, often only has single value for all rows.\n5. Fill any nans in D_50 and S_23-based columns with -32783, to ensure lowest possible number.\n6. Get total count of data per customer, and total count in last row.\n7. Drop B_29. (I might end up submitting one submission with B_29 dropped, one with it included. https://www.kaggle.com/competitions/amex-default-prediction/discussion/328756 )\n8. Calculate a simplified hull moving average over the 13 monthly statements for each customer and column.\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":"2024-10-04T13:33:16.005270Z","iopub.execute_input":"2024-10-04T13:33:16.005704Z","iopub.status.idle":"2024-10-04T13:33:20.043982Z","shell.execute_reply.started":"2024-10-04T13:33:16.005582Z","shell.execute_reply":"2024-10-04T13:33:20.043115Z"},"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 = 6\n    TEST_SECTIONS = 6\n    TEST_NUM_PARTS = 6\n    \nelse:\n    TRAIN_NUM_PARTS = 6\n    TEST_SECTIONS = 4\n    TEST_NUM_PARTS = 4\n\nprint(\"VER:\", VER)\nif not PROCESS_TEST_DATA:\n    print(\"NOT processing test data!\")","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:41:27.609685Z","iopub.execute_input":"2024-10-04T13:41:27.610644Z","iopub.status.idle":"2024-10-04T13:41:27.616812Z","shell.execute_reply.started":"2024-10-04T13:41:27.610584Z","shell.execute_reply":"2024-10-04T13:41:27.615846Z"},"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":"2024-10-04T13:34:28.049965Z","iopub.execute_input":"2024-10-04T13:34:28.050362Z","iopub.status.idle":"2024-10-04T13:34:28.059279Z","shell.execute_reply.started":"2024-10-04T13:34:28.050327Z","shell.execute_reply":"2024-10-04T13:34:28.058435Z"},"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":"2024-10-04T13:34:30.300700Z","iopub.execute_input":"2024-10-04T13:34:30.301457Z","iopub.status.idle":"2024-10-04T13:34:30.325423Z","shell.execute_reply.started":"2024-10-04T13:34:30.301421Z","shell.execute_reply":"2024-10-04T13:34:30.324493Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## For understanding\n# ##\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# ##\n# def 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', 'std', 'last'])\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#     ## Drop delta for now. I haven't had success getting 1500+ features without running out of memory.\n#     ##   Out of memory: Not only while feature engineering (probably solvable), but again while running the model from pre-processed feature dataset (harder to solve)\n#     ## TODO: try swapping out other feature to add this one in?\n#     ##   Feature selection on subsets, then combine best features?\n#     ##   Reduce float64, int64, etc, to lower precision and fit more that way?\n# #     ### Add delta\n# #     test_num_agg2 = groups[num_features].nth(-1) - groups[num_features].nth(-2)\n# #     test_num_agg2 = test_num_agg2.fillna(0)\n# #     test_num_agg2.columns = [x + '_delta' for x in test_num_agg2.columns]\n\n#     ## Note for optimization: several of these are probably super inefficient to re-calculate it from the group data. Should be able to re-use test_num_agg in some way.\n\n#     ### Add current level: range from 0.0-1.0. For example: 0.0 means last = min; 0.5 means last = (max+min)/2; 1.0 means last = max.\n#     test_num_agg2 = (groups[num_features].last() - groups[num_features].min()) / (groups[num_features].max() - groups[num_features].min())\n#     test_num_agg2 = test_num_agg2.fillna(0)\n#     test_num_agg2.columns = [x + '_curLevel' for x in test_num_agg2.columns]\n\n#     ### Add magnitude: max - min\n#     test_num_agg3 = groups[num_features].max() - groups[num_features].min()\n#     test_num_agg3 = test_num_agg3.fillna(0)\n#     test_num_agg3.columns = [x + '_magnitude' for x in test_num_agg3.columns]\n\n#     ### Add last-mean\n#     test_num_agg4 = groups[num_features].last() - groups[num_features].mean()\n#     test_num_agg4 = test_num_agg4.fillna(0)\n#     test_num_agg4.columns = [x + '_last-mean' for x in test_num_agg4.columns]\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#     ## Forward fill, and then backward fill to remove all nans before using \"nth\" to calc hma\n#     groups = groups.ffill()\n#     groups[\"customer_ID\"] = df[\"customer_ID\"]\n#     groups = groups.groupby(\"customer_ID\").bfill()\n#     groups[\"customer_ID\"] = df[\"customer_ID\"]\n#     groups = groups.groupby(\"customer_ID\")\n\n#     print(\".\", end = '')\n\n#     ### Add HMA. Hull moving average is a smoothed moving average sometimes used on time series data. (e.g. stock price).\n#     ## I happen to like it. As usual, I can't actually say whether it helps a lot, a little, or not at all.\n#     ##   Especially with the inherent CV variance, I haven't done nearly enough (any) experiments to see whether it helps a lot, a little, or not at all.\n#     ##   It didn't obviously help compared with other XGB public notebooks until I lowered column subsampling and learning rate.\n#     ##   With those hyper parameter changes helping a ton, I haven't checked which personal feature engg touches are actually contributing (if any).\n#     ## For calculation simplicity, this version of HMA is a bit simpler, while keeping the key idea that mean(range(10)) = 5, but hma(range(10)) = 10.\n#     ## TODO: Maybe consider calculating reverse HMA, for example last - reverse HMA, or HMA-reverseHMA to more directly look at slope of the data.\n#     ##\n#     ## Calculate the biggest down to the smallest, then take the biggest that isn't nan, to handle varying group sizes\n#     h13 = hma13(groups, num_features)\n#     h11 = hma11(groups, num_features)\n#     h9 = hma9(groups, num_features)\n#     h7 = hma7(groups, num_features)\n#     h5 = hma5(groups, num_features)\n#     h3 = hma3(groups, num_features)\n#     h1 = hma1(groups, num_features)\n#     hma_df = cudf.concat([h1, h3, h5, h7, h9, h11, h13], axis=0)\n#     del h1, h3, h5, h7, h9, h11, h13, assert_s2_check\n#     gc.collect()\n#     print(\".\", end = '')\n#     hma_df = hma_df.sort_index()\n#     hma_df = hma_df.reset_index()\n#     hma_df = hma_df.groupby(\"customer_ID\").last()\n#     hma_df.columns = [x + \"_hma\" for x in hma_df.columns]\n\n#     print(\".\", end = '')\n\n#     df = cudf.concat([test_s2_agg, test_num_agg, test_cat_agg, test_num_agg2, test_cat_agg2, hma_df, test_num_agg3, test_num_agg4], 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 I forgot to save the link, maybe on 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 probably better in XGBoost to just leave them there, and let XGBoost decide!\n#     nan_col = ['D_50_mean', 'D_50_std', 'D_50_last', 'S_23_mean', 'S_23_std', '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_num_agg2, test_cat_agg2, test_num_agg3, test_num_agg4\n\n#     return df\n\n\n# def hma13(groups, columns):\n#     return (5/14)*groups[columns].nth(-1) + (27/91)*groups[columns].nth(-2) + (43/182)*groups[columns].nth(-3) + (16/91)*groups[columns].nth(-4) + (3/26)*groups[columns].nth(-5) + (5/91)*groups[columns].nth(-6) - (1/182)*groups[columns].nth(-7) - (6/91)*groups[columns].nth(-8) - (5/91)*groups[columns].nth(-9) - (4/91)*groups[columns].nth(-10) - (3/91)*groups[columns].nth(-11) - (2/91)*groups[columns].nth(-12) - (1/91)*groups[columns].nth(-13)\n# def hma11(groups, columns):\n#     return (17/42)*groups[columns].nth(-1) + (25/77)*groups[columns].nth(-2) + (113/462)*groups[columns].nth(-3) + (38/231)*groups[columns].nth(-4) + (13/154)*groups[columns].nth(-5) + (1/231)*groups[columns].nth(-6) - (5/66)*groups[columns].nth(-7) - (2/33)*groups[columns].nth(-8) - (1/22)*groups[columns].nth(-9) - (1/33)*groups[columns].nth(-10) - (1/66)*groups[columns].nth(-11)\n# def hma9(groups, columns):\n#     return (7/15)*groups[columns].nth(-1) + (16/45)*groups[columns].nth(-2) + (11/45)*groups[columns].nth(-3) + (2/15)*groups[columns].nth(-4) + (1/45)*groups[columns].nth(-5) - (4/45)*groups[columns].nth(-6) - (1/15)*groups[columns].nth(-7) - (2/45)*groups[columns].nth(-8) - (1/45)*groups[columns].nth(-9)\n# def hma7(groups, columns):\n#     return (11/20)*groups[columns].nth(-1) + (27/70)*groups[columns].nth(-2) + (31/140)*groups[columns].nth(-3) + (2/35)*groups[columns].nth(-4) - (3/28)*groups[columns].nth(-5) - (1/14)*groups[columns].nth(-6) - (1/28)*groups[columns].nth(-7)\n# def hma5(groups, columns):\n#     return (2/3)*groups[columns].nth(-1) + (2/5)*groups[columns].nth(-2) + (2/15)*groups[columns].nth(-3) - (2/15)*groups[columns].nth(-4) - (1/15)*groups[columns].nth(-5)\n# def hma3(groups, columns):\n#     return (5/6)*groups[columns].nth(-1) + (1/3)*groups[columns].nth(-2) - (1/6)*groups[columns].nth(-3)\n# def hma1(groups, columns):\n#     return groups[columns].nth(-1)","metadata":{"execution":{"iopub.status.busy":"2024-10-04T11:49:37.928903Z","iopub.execute_input":"2024-10-04T11:49:37.929268Z","iopub.status.idle":"2024-10-04T11:49:37.941976Z","shell.execute_reply.started":"2024-10-04T11:49:37.929234Z","shell.execute_reply":"2024-10-04T11:49:37.941011Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature Engineering","metadata":{}},{"cell_type":"code","source":"def process_and_feature_engineer(df):\n    print(\".\", end = '')\n\n    # 1. Process customer columns\n    df = process_customer_columns_step(df)\n\n    print(\".\", end = '')\n\n    # 2. Drop unnecessary columns\n    df = drop_columns_step(df)\n\n    print(\".\", end = '')\n\n    # 3. Add after pay features\n    df = add_after_pay_features(df)\n\n    # 4. Handle NaN counts\n    nan_sum, nan_last = compute_nan_counts(df)\n\n    print(\".\", end = '')\n\n    # 5. Aggregate numerical features\n    test_num_agg = aggregate_numerical_features(df)\n\n    print(\".\", end = '')\n\n    # 6. Aggregate categorical features\n    test_cat_agg = aggregate_categorical_features(df)\n\n    print(\".\", end = '')\n\n    # 7. Aggregate S_2 features\n    test_s2_agg = aggregate_s2_features(df)\n\n    print(\".\", end = '')\n\n    # 8. Add engineered features (current level, magnitude, etc.)\n    test_num_agg2,test_num_agg3,test_num_agg4 = get_test_num_agg2_4(df)\n\n    print(\".\", end = '')\n    \n    # 9. Get test_cat_agg2\n    test_cat_agg2 = get_test_cat_agg2(df)\n\n    # 10. Add HMA (Hull Moving Average)\n    hma_df = add_hma(df)\n\n    print(\".\", end = '')\n    \n    # Concatenate final data\n    df = finalize_data(df, test_s2_agg, test_num_agg, test_cat_agg, test_num_agg2, test_cat_agg2, hma_df, test_num_agg3, test_num_agg4, nan_sum, nan_last)\n    \n    print(\".\")\n    \n    print('shape after engineering', df.shape)\n    \n    check_for_droppable_columns(df)\n    \n    return df","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:54.832500Z","iopub.execute_input":"2024-10-04T13:34:54.833021Z","iopub.status.idle":"2024-10-04T13:34:54.845913Z","shell.execute_reply.started":"2024-10-04T13:34:54.832978Z","shell.execute_reply":"2024-10-04T13:34:54.844823Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 1. Process customer columns\ndef process_customer_columns_step(df):\n    df = process_customer_columns(df)\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:56.870613Z","iopub.execute_input":"2024-10-04T13:34:56.871253Z","iopub.status.idle":"2024-10-04T13:34:56.875422Z","shell.execute_reply.started":"2024-10-04T13:34:56.871218Z","shell.execute_reply":"2024-10-04T13:34:56.874573Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 2. Drop unnecessary columns\ndef drop_columns_step(df):\n    df.drop(\"B_29\", inplace=True, axis=1)\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:57.150021Z","iopub.execute_input":"2024-10-04T13:34:57.150327Z","iopub.status.idle":"2024-10-04T13:34:57.155362Z","shell.execute_reply.started":"2024-10-04T13:34:57.150299Z","shell.execute_reply":"2024-10-04T13:34:57.154557Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 3. Add After Pay Features\ndef add_after_pay_features(df):\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    return df\n","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:57.585236Z","iopub.execute_input":"2024-10-04T13:34:57.585934Z","iopub.status.idle":"2024-10-04T13:34:57.591787Z","shell.execute_reply.started":"2024-10-04T13:34:57.585899Z","shell.execute_reply":"2024-10-04T13:34:57.590943Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 4. Compute NaN Counts\ndef compute_nan_counts(df):\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    return nan_sum, nan_last","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:57.922600Z","iopub.execute_input":"2024-10-04T13:34:57.922940Z","iopub.status.idle":"2024-10-04T13:34:57.928625Z","shell.execute_reply.started":"2024-10-04T13:34:57.922911Z","shell.execute_reply":"2024-10-04T13:34:57.927837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 5. Aggregate Numerical Features\ndef aggregate_numerical_features(df):\n    groups = df.groupby(\"customer_ID\")\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    test_num_agg = groups[num_features].agg(['mean', 'std', 'last'])\n    test_num_agg.columns = ['_'.join(x) for x in test_num_agg.columns]\n    return test_num_agg\n","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:58.125466Z","iopub.execute_input":"2024-10-04T13:34:58.126091Z","iopub.status.idle":"2024-10-04T13:34:58.133000Z","shell.execute_reply.started":"2024-10-04T13:34:58.126043Z","shell.execute_reply":"2024-10-04T13:34:58.132191Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 6. Aggregate Categorical Features\ndef aggregate_categorical_features(df):\n    groups = df.groupby(\"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    \n    test_cat_agg = groups[cat_features].last()\n    test_cat_agg.columns = [x + \"_last\" for x in test_cat_agg.columns]\n    return test_cat_agg\n","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:58.365279Z","iopub.execute_input":"2024-10-04T13:34:58.365559Z","iopub.status.idle":"2024-10-04T13:34:58.371164Z","shell.execute_reply.started":"2024-10-04T13:34:58.365533Z","shell.execute_reply":"2024-10-04T13:34:58.370377Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#7. Aggregate S_2 Features\n\ndef aggregate_s2_features(df):\n    groups = df.groupby(\"customer_ID\")\n    \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    return test_s2_agg","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:58.755047Z","iopub.execute_input":"2024-10-04T13:34:58.755655Z","iopub.status.idle":"2024-10-04T13:34:58.761377Z","shell.execute_reply.started":"2024-10-04T13:34:58.755625Z","shell.execute_reply":"2024-10-04T13:34:58.760524Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 8. Get test num agg 2-4\ndef get_test_num_agg2_4(df):\n    groups = df.groupby(\"customer_ID\")\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    test_num_agg2 = (groups[num_features].last() - groups[num_features].min()) / (groups[num_features].max() - groups[num_features].min())\n    test_num_agg2 = test_num_agg2.fillna(0)\n    test_num_agg2.columns = [x + '_curLevel' for x in test_num_agg2.columns]\n\n    ### Add magnitude: max - min\n    test_num_agg3 = groups[num_features].max() - groups[num_features].min()\n    test_num_agg3 = test_num_agg3.fillna(0)\n    test_num_agg3.columns = [x + '_magnitude' for x in test_num_agg3.columns]\n\n    ### Add last-mean\n    test_num_agg4 = groups[num_features].last() - groups[num_features].mean()\n    test_num_agg4 = test_num_agg4.fillna(0)\n    test_num_agg4.columns = [x + '_last-mean' for x in test_num_agg4.columns]\n\n    return test_num_agg2,test_num_agg3,test_num_agg4\n","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:59.361942Z","iopub.execute_input":"2024-10-04T13:34:59.362558Z","iopub.status.idle":"2024-10-04T13:34:59.372313Z","shell.execute_reply.started":"2024-10-04T13:34:59.362524Z","shell.execute_reply":"2024-10-04T13:34:59.371520Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#9. get test_cat_agg2\ndef get_test_cat_agg2(df):\n    groups = df.groupby(\"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    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    return test_cat_agg2","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:34:59.878255Z","iopub.execute_input":"2024-10-04T13:34:59.879001Z","iopub.status.idle":"2024-10-04T13:34:59.884832Z","shell.execute_reply.started":"2024-10-04T13:34:59.878968Z","shell.execute_reply":"2024-10-04T13:34:59.883982Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 10. Add HMA (Hull Moving Average)\ndef hma13(groups, columns):\n    return (5/14)*groups[columns].nth(-1) + (27/91)*groups[columns].nth(-2) + (43/182)*groups[columns].nth(-3) + (16/91)*groups[columns].nth(-4) + (3/26)*groups[columns].nth(-5) + (5/91)*groups[columns].nth(-6) - (1/182)*groups[columns].nth(-7) - (6/91)*groups[columns].nth(-8) - (5/91)*groups[columns].nth(-9) - (4/91)*groups[columns].nth(-10) - (3/91)*groups[columns].nth(-11) - (2/91)*groups[columns].nth(-12) - (1/91)*groups[columns].nth(-13)\ndef hma11(groups, columns):\n    return (17/42)*groups[columns].nth(-1) + (25/77)*groups[columns].nth(-2) + (113/462)*groups[columns].nth(-3) + (38/231)*groups[columns].nth(-4) + (13/154)*groups[columns].nth(-5) + (1/231)*groups[columns].nth(-6) - (5/66)*groups[columns].nth(-7) - (2/33)*groups[columns].nth(-8) - (1/22)*groups[columns].nth(-9) - (1/33)*groups[columns].nth(-10) - (1/66)*groups[columns].nth(-11)\ndef hma9(groups, columns):\n    return (7/15)*groups[columns].nth(-1) + (16/45)*groups[columns].nth(-2) + (11/45)*groups[columns].nth(-3) + (2/15)*groups[columns].nth(-4) + (1/45)*groups[columns].nth(-5) - (4/45)*groups[columns].nth(-6) - (1/15)*groups[columns].nth(-7) - (2/45)*groups[columns].nth(-8) - (1/45)*groups[columns].nth(-9)\ndef hma7(groups, columns):\n    return (11/20)*groups[columns].nth(-1) + (27/70)*groups[columns].nth(-2) + (31/140)*groups[columns].nth(-3) + (2/35)*groups[columns].nth(-4) - (3/28)*groups[columns].nth(-5) - (1/14)*groups[columns].nth(-6) - (1/28)*groups[columns].nth(-7)\ndef hma5(groups, columns):\n    return (2/3)*groups[columns].nth(-1) + (2/5)*groups[columns].nth(-2) + (2/15)*groups[columns].nth(-3) - (2/15)*groups[columns].nth(-4) - (1/15)*groups[columns].nth(-5)\ndef hma3(groups, columns):\n    return (5/6)*groups[columns].nth(-1) + (1/3)*groups[columns].nth(-2) - (1/6)*groups[columns].nth(-3)\ndef hma1(groups, columns):\n    return groups[columns].nth(-1)\n\ndef add_hma(df):\n    groups = df.groupby(\"customer_ID\")\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    h13 = hma13(groups, num_features)\n    h11 = hma11(groups, num_features)\n    h9 = hma9(groups, num_features)\n    h7 = hma7(groups, num_features)\n    h5 = hma5(groups, num_features)\n    h3 = hma3(groups, num_features)\n    h1 = hma1(groups, num_features)\n    \n    hma_df = cudf.concat([h1, h3, h5, h7, h9, h11, h13], axis=0)\n    hma_df = hma_df.sort_index().reset_index().groupby(\"customer_ID\").last()\n    hma_df.columns = [x + \"_hma\" for x in hma_df.columns]\n    \n    return hma_df\n","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:35:00.173011Z","iopub.execute_input":"2024-10-04T13:35:00.173633Z","iopub.status.idle":"2024-10-04T13:35:00.202453Z","shell.execute_reply.started":"2024-10-04T13:35:00.173582Z","shell.execute_reply":"2024-10-04T13:35:00.201567Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 11. Finalize Data\ndef finalize_data(df, test_s2_agg, test_num_agg, test_cat_agg, test_num_agg2, test_cat_agg2, hma_df, test_num_agg3, test_num_agg4, nan_sum, nan_last):\n    df = cudf.concat([test_s2_agg, test_num_agg, test_cat_agg, test_num_agg2, test_cat_agg2, hma_df, test_num_agg3, test_num_agg4], axis=1)\n    df[\"total_data_count\"] = nan_sum\n    df[\"total_data_last\"] = nan_last\n    \n    nan_col = ['D_50_mean', 'D_50_std', 'D_50_last', 'S_23_mean', 'S_23_std', 'S_23_last']\n    df[nan_col] = df[nan_col].fillna(-32783)\n    df = df.fillna(NAN_VALUE)\n    \n    return df","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:36:22.836787Z","iopub.execute_input":"2024-10-04T13:36:22.837171Z","iopub.status.idle":"2024-10-04T13:36:22.844310Z","shell.execute_reply.started":"2024-10-04T13:36:22.837140Z","shell.execute_reply":"2024-10-04T13:36:22.843382Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#12. Check for Droppable Columns\ndef check_for_droppable_columns(df):\n    for col in df.columns:\n        if len(df[col].unique()) == 1:\n            print(\"Consider dropping column!?\", col)","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:36:24.109147Z","iopub.execute_input":"2024-10-04T13:36:24.109531Z","iopub.status.idle":"2024-10-04T13:36:24.114616Z","shell.execute_reply.started":"2024-10-04T13:36:24.109500Z","shell.execute_reply":"2024-10-04T13:36:24.113640Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# # 13. add  missing next payment feature\n## not running on kaggle\n\n# def add_miss_next_payment_feature(df):\n#      # Step 1: Calculate D_39_lag\n#     df[\"D_39_lag\"] = df.groupby(\"customer_ID\")[\"D_39\"].shift(-1)\n\n#     # Step 2: Convert 'S_2' to datetime\n#     df[\"S_2\"] = cudf.to_datetime(df[\"S_2\"])\n\n#     # Step 3: Calculate S_2_lag\n#     df[\"S_2_lag\"] = df.groupby(\"customer_ID\")[\"S_2\"].shift(-1)\n\n#     # Step 4: Convert S_2_lag from timedelta to number of days (in float)\n#     # Here we calculate the difference in days between S_2 and S_2_lag\n#     df[\"S_2_lag_days\"] = (df[\"S_2_lag\"] - df[\"S_2\"]).dt.days.fillna(0)\n\n#     # Step 5: Initialize 'miss_next_payment' column\n#     df[\"miss_next_payment\"] = 0\n\n#     # Step 6: Create the conditions for updating 'miss_next_payment'\n#     condition_1 = df[\"D_39_lag\"] <= -28\n#     condition_2 = (df[\"D_39_lag\"] < -14) & (df[\"S_2_lag_days\"] >= df[\"D_39_lag\"]) & (df[\"D_39\"] - df[\"D_39_lag\"] >= 28)\n\n#     # Step 7: Update 'miss_next_payment' based on conditions\n#     df.loc[condition_1 | condition_2, \"miss_next_payment\"] = 1\n\n#     # Handle cases where there's no change in D_39\n#     df.loc[df[\"D_39\"] - df[\"D_39_lag\"] == 0, \"miss_next_payment\"] = -1\n\n#     # Step 8: Return only the new columns\n#     result = df[[\"customer_ID\", \"miss_next_payment\"]].copy()\n\n#     # Drop intermediate columns that are not needed anymore\n#     df.drop(columns=[\"D_39\", \"D_39_lag\", \"S_2_lag\", \"S_2_lag_days\"], inplace=True)\n\n#     return result\n\n\n","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:35:20.731064Z","iopub.execute_input":"2024-10-04T13:35:20.731450Z","iopub.status.idle":"2024-10-04T13:35:20.736843Z","shell.execute_reply.started":"2024-10-04T13:35:20.731418Z","shell.execute_reply":"2024-10-04T13:35:20.736054Z"},"trusted":true},"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\")","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:36:26.067847Z","iopub.execute_input":"2024-10-04T13:36:26.068648Z","iopub.status.idle":"2024-10-04T13:39:15.780964Z","shell.execute_reply.started":"2024-10-04T13:36:26.068591Z","shell.execute_reply":"2024-10-04T13:39:15.780028Z"},"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\n## ~4 minutes GPU: 2*1 parts\n## ~5-6 minutes GPU: 2*4 parts\n\n## ~20 minutes CPU: 2*4","metadata":{"execution":{"iopub.status.busy":"2024-10-04T13:44:12.419866Z","iopub.execute_input":"2024-10-04T13:44:12.420790Z","iopub.status.idle":"2024-10-04T13:57:54.972066Z","shell.execute_reply.started":"2024-10-04T13:44:12.420752Z","shell.execute_reply":"2024-10-04T13:57:54.971012Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}