{"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":"# Feature analysis: S_2\nIn this notebook I simply take a closer look at the monthly statements for each customer. Feature S_2. I'm interested in:\n* Converting to a number (useful for feature engineering).\n* Ignoring the 'day'.\n* Offsetting for test data to normalize with train data.\n* Comparing different use cases and associated default rate.\n    * Long-term customer: all 13 statements.\n    * Short-term customer: fewer than 13 statements, all statements are consecutive.\n    * Gap customer: fewer than 13 statements, statements are not consecutive.\n* Comparing the count of these use cases between the train data and the two sets of test data.\n\n\nI built this noteboook based on @cdeotte from [here][1], which in turn is built upon:\n* Smaller dataset: @raddar Kaggle dataset from [here][2] with discussion [here][3]. \n* feature engineering: suggested by @huseyincot in his notebooks [here][4] and [here][5].\n* GPU: Our feature engineering is performed using [RAPIDS][6] on the GPU to create new features quickly.\n\n[1]: https://www.kaggle.com/code/cdeotte/xgboost-starter-0-793\n[2]: https://www.kaggle.com/datasets/raddar/amex-data-integer-dtypes-parquet-format\n[3]: https://www.kaggle.com/competitions/amex-default-prediction/discussion/328514\n[4]: https://www.kaggle.com/code/huseyincot/amex-catboost-0-793\n[5]: https://www.kaggle.com/code/huseyincot/amex-agg-data-how-it-created\n[6]: https://rapids.ai/","metadata":{}},{"cell_type":"markdown","source":"# Load Libraries","metadata":{}},{"cell_type":"code","source":"# LOAD LIBRARIES\nimport pandas as pd, numpy as np # CPU libraries\nimport cupy, cudf # GPU libraries\nimport matplotlib.pyplot as plt, gc, os\n\nprint('RAPIDS version',cudf.__version__)","metadata":{"execution":{"iopub.status.busy":"2022-06-06T19:59:10.373770Z","iopub.execute_input":"2022-06-06T19:59:10.374168Z","iopub.status.idle":"2022-06-06T19:59:14.784092Z","shell.execute_reply.started":"2022-06-06T19:59:10.374096Z","shell.execute_reply":"2022-06-06T19:59:14.782263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Process and Feature Engineer Train Data\nWe will load @raddar Kaggle dataset from [here][1] with discussion [here][2]. Then we will engineer S_2, inspired by @huseyincot in his notebooks [here][3] and [here][4]. We will use [RAPIDS][5] and the GPU to create new features quickly.\n\n[1]: https://www.kaggle.com/datasets/raddar/amex-data-integer-dtypes-parquet-format\n[2]: https://www.kaggle.com/competitions/amex-default-prediction/discussion/328514\n[3]: https://www.kaggle.com/code/huseyincot/amex-catboost-0-793\n[4]: https://www.kaggle.com/code/huseyincot/amex-agg-data-how-it-created\n[5]: https://rapids.ai/","metadata":{}},{"cell_type":"code","source":"NAN_VALUE = -127 # will fit in int8\n\ndef read_file(path = '', usecols = None):\n    # LOAD DATAFRAME\n    if usecols is not None: df = cudf.read_parquet(path, columns=usecols)\n    else: df = cudf.read_parquet(path)\n    # REDUCE DTYPE FOR CUSTOMER AND DATE\n    df['customer_ID'] = df['customer_ID'].str[-16:].str.hex_to_int().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    # FILL NAN\n    df = df.fillna(NAN_VALUE) \n    print('shape of data:', df.shape)\n    \n    return df\n\nprint('Reading train data...')\nTRAIN_PATH = '../input/amex-data-integer-dtypes-parquet-format/train.parquet'\ntrain_base = read_file(path = TRAIN_PATH)","metadata":{"execution":{"iopub.status.busy":"2022-06-06T19:59:14.785935Z","iopub.execute_input":"2022-06-06T19:59:14.786275Z","iopub.status.idle":"2022-06-06T19:59:37.393520Z","shell.execute_reply.started":"2022-06-06T19:59:14.786239Z","shell.execute_reply":"2022-06-06T19:59:37.392745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def process_and_feature_engineer(df):\n    # INSPIRED BY\n    # https://www.kaggle.com/code/huseyincot/amex-agg-data-how-it-created\n\n    df = df.groupby(\"customer_ID\")[[\"S_2\"]].agg(['min', 'max', 'last', 'count'])\n    df.columns = ['_'.join(x) for x in df.columns]\n    print('shape after engineering', df.shape )\n    \n    return df\n\ndf = process_and_feature_engineer(train_base)\n\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2022-06-06T19:59:37.397465Z","iopub.execute_input":"2022-06-06T19:59:37.399811Z","iopub.status.idle":"2022-06-06T19:59:37.492443Z","shell.execute_reply.started":"2022-06-06T19:59:37.399774Z","shell.execute_reply":"2022-06-06T19:59:37.491658Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# ADD TARGETS\n# https://www.kaggle.com/code/cdeotte/xgboost-starter-0-793\ndef add_targets(train):\n    targets = cudf.read_csv('../input/amex-default-prediction/train_labels.csv')\n    targets['customer_ID'] = targets['customer_ID'].str[-16:].str.hex_to_int().astype('int64')\n    targets = targets.set_index('customer_ID')\n    train = train.merge(targets, left_index=True, right_index=True, how='left')\n    del targets\n    return train\n\ndf = add_targets(df)\ndf = df.to_pandas()\ndf = df.sort_index()\ndf = df.reset_index()\n\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2022-06-06T19:59:37.497052Z","iopub.execute_input":"2022-06-06T19:59:37.499165Z","iopub.status.idle":"2022-06-06T19:59:38.113535Z","shell.execute_reply.started":"2022-06-06T19:59:37.499127Z","shell.execute_reply":"2022-06-06T19:59:38.112777Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"TOTAL = len(df)\nTOTAL_Y = sum(df.target)\ntest = df[df['S_2_max'] == 12]\nassert(len(test) == TOTAL)\ntest = df[df['S_2_last'] == 12]\nassert(len(test) == TOTAL)\ntest = df[df['S_2_count'] == 13]\nlongterm = len(test)\nlongterm_y = sum(test.target)\ntest = df[df['S_2_min'] > 0]\nshort = test[test['S_2_max'] - test['S_2_min'] == test['S_2_count'] - 1]\nshortterm = len(short)\nshortterm_y = sum(short.target)\ngap = df[df['S_2_max'] - df['S_2_min'] > df['S_2_count'] - 1]\ngapCount = len(gap)\ngap_y = sum(gap.target)\nprint(longterm, shortterm, gapCount)\nassert(longterm+gapCount+shortterm == TOTAL)\nassert(longterm_y+gap_y+shortterm_y == TOTAL_Y)\nfor i in range(1, 14):\n    test1 = short[short['S_2_count'] == i]\n    test2 = gap[gap['S_2_count'] == i]\n    if i == 1:\n        print(i, \"S\", len(test1))\n        print(i, \"S\", sum(test1.target)/len(test1))\n    elif i < 13:\n        print(i, \"S\", len(test1), \"G\", len(test2))\n        print(i, \"S\", sum(test1.target)/len(test1), \"G\", sum(test2.target)/len(test2))\n    else:\n        print(i, \"L\", longterm)\n        print(i, \"L\", longterm_y/longterm)\n\nprint(TOTAL_Y, longterm_y, shortterm_y, gap_y)\nprint(\"Rate: (all):\", TOTAL_Y/TOTAL)\nprint(\"Long term:  \", longterm_y/longterm)\nprint(\"Short term: \", shortterm_y/shortterm)\nprint(\"Gap customer\", gap_y/gapCount)\n","metadata":{"execution":{"iopub.status.busy":"2022-06-06T19:59:38.117821Z","iopub.execute_input":"2022-06-06T19:59:38.120052Z","iopub.status.idle":"2022-06-06T19:59:38.618382Z","shell.execute_reply.started":"2022-06-06T19:59:38.120012Z","shell.execute_reply":"2022-06-06T19:59:38.617587Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Process and Feature Engineer Test Data\nWe will load @raddar Kaggle dataset from [here][1] with discussion [here][2]. Then we will engineer features suggested by @huseyincot in his notebooks [here][1] and [here][4]. We will use [RAPIDS][5] and the GPU to create new features quickly.\n\n[1]: https://www.kaggle.com/datasets/raddar/amex-data-integer-dtypes-parquet-format\n[2]: https://www.kaggle.com/competitions/amex-default-prediction/discussion/328514\n[3]: https://www.kaggle.com/code/huseyincot/amex-catboost-0-793\n[4]: https://www.kaggle.com/code/huseyincot/amex-agg-data-how-it-created\n[5]: https://rapids.ai/","metadata":{}},{"cell_type":"code","source":"# CALCULATE SIZE OF EACH SEPARATE TEST PART\ndef get_rows(customers, test, 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 = test.loc[test.customer_ID.isin(cc)].shape[0]\n        rows.append(s)\n    if verbose != '': print( rows )\n    return rows,chunk\n\n# COMPUTE SIZE OF 4 PARTS FOR TEST DATA\nNUM_PARTS = 4\nTEST_PATH = '../input/amex-data-integer-dtypes-parquet-format/test.parquet'\n\nprint(f'Reading test data...')\ntest = read_file(path = TEST_PATH, usecols = ['customer_ID','S_2'])\ncustomers = test[['customer_ID']].drop_duplicates().sort_index().values.flatten()\nrows,num_cust = get_rows(customers, test[['customer_ID']], NUM_PARTS = NUM_PARTS, verbose = 'test')","metadata":{"execution":{"iopub.status.busy":"2022-06-06T19:59:38.622416Z","iopub.execute_input":"2022-06-06T19:59:38.624581Z","iopub.status.idle":"2022-06-06T19:59:41.649886Z","shell.execute_reply.started":"2022-06-06T19:59:38.624541Z","shell.execute_reply":"2022-06-06T19:59:41.648955Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Load Test","metadata":{}},{"cell_type":"code","source":"def loadTestData():\n    # READ PART OF TEST DATA\n    print(f'\\nReading test data...')\n    test = read_file(path = TEST_PATH)\n    print(f'=> Test has shape', test.shape )\n    \n    # PROCESS AND FEATURE ENGINEER PART OF TEST DATA\n    test = process_and_feature_engineer(test)\n    return test\n\ndf = loadTestData()","metadata":{"execution":{"iopub.status.busy":"2022-06-06T19:59:41.651116Z","iopub.execute_input":"2022-06-06T19:59:41.651586Z","iopub.status.idle":"2022-06-06T20:00:23.547090Z","shell.execute_reply.started":"2022-06-06T19:59:41.651549Z","shell.execute_reply":"2022-06-06T20:00:23.546212Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def printStats(df, last=12):\n    TOTAL = len(df)\n\n    assert(len(df[df['S_2_max'] == last]) == TOTAL)\n    assert(len(df[df['S_2_last'] == last]) == TOTAL)\n\n    longterm = len(df[df['S_2_count'] == 13])\n\n    test = df[df['S_2_min'] > last-12]\n    short = test[test['S_2_max'] - test['S_2_min'] == test['S_2_count'] - 1]\n    shortterm = len(short)\n\n    gap = df[df['S_2_max'] - df['S_2_min'] > df['S_2_count'] - 1]\n    gapCount = len(gap)\n\n    print(longterm, shortterm, gapCount)\n    assert(longterm+gapCount+shortterm == TOTAL)\n    for i in range(1, 14):\n        test1 = short[short['S_2_count'] == i]\n        test2 = gap[gap['S_2_count'] == i]\n        if i == 1:\n            print(i, \"S\", len(test1))\n        elif i < 13:\n            print(i, \"S\", len(test1), \"G\", len(test2))\n        else:\n            print(i, \"L\", longterm)\n\nGRAND_TOTAL = len(df)\ntest = df[df['S_2_max'] == 25]\nTOTAL1 = len(test)\ntest = df[df['S_2_max'] == 31]\nTOTAL2 = len(test)\nassert(TOTAL1+TOTAL2 == GRAND_TOTAL)\nprintStats(df[df['S_2_max'] == 25], last=25)\nprintStats(df[df['S_2_max'] == 31], last=31)\n","metadata":{"execution":{"iopub.status.busy":"2022-06-06T20:00:23.548555Z","iopub.execute_input":"2022-06-06T20:00:23.549159Z","iopub.status.idle":"2022-06-06T20:00:23.636624Z","shell.execute_reply.started":"2022-06-06T20:00:23.549116Z","shell.execute_reply":"2022-06-06T20:00:23.635746Z"},"trusted":true},"execution_count":null,"outputs":[]}]}