{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import gc\nimport os\nimport pathlib\nimport random\nfrom typing import List\n\nimport cudf\nimport cupy\nimport cupyx\nfrom IPython.display import display\nimport joblib\nimport numpy as np\nimport pandas as pd\nfrom sklearn.preprocessing import LabelEncoder\nfrom tqdm.auto import tqdm\n\npd.set_option('display.max_rows', 200)\npd.set_option('display.max_columns', 200)\n\nSEED = 42\nrandom.seed(SEED)\nnp.random.seed(SEED)\nos.environ['PYTHONHASHSEED'] = str(SEED)","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:04:39.044627Z","iopub.execute_input":"2022-08-13T14:04:39.045085Z","iopub.status.idle":"2022-08-13T14:04:39.054071Z","shell.execute_reply.started":"2022-08-13T14:04:39.045053Z","shell.execute_reply":"2022-08-13T14:04:39.052499Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"raw_dir_path = pathlib.Path('../input/amex-default-prediction')\ndir_path = pathlib.Path('../input/amex-data-integer-dtypes-parquet-format')\nprint(type(dir_path))","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:04:39.067186Z","iopub.execute_input":"2022-08-13T14:04:39.068145Z","iopub.status.idle":"2022-08-13T14:04:39.076703Z","shell.execute_reply.started":"2022-08-13T14:04:39.068103Z","shell.execute_reply":"2022-08-13T14:04:39.075157Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def _load_parquet(path, columns=None, skiprows=None, num_rows=None):\n    if columns is not None:\n        df_ = cudf.read_parquet(path, columns=columns, skiprows=skiprows, num_rows=num_rows)\n    else:\n        df_ = cudf.read_parquet(path, skiprows=skiprows, num_rows=num_rows)\n    df_.drop('S_2', axis=1, inplace=True)\n    df_['customer_ID'] = df_['customer_ID'].str[-16:].str.hex_to_int().astype('int64')\n    return df_","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:04:39.085745Z","iopub.execute_input":"2022-08-13T14:04:39.086289Z","iopub.status.idle":"2022-08-13T14:04:39.098494Z","shell.execute_reply.started":"2022-08-13T14:04:39.086244Z","shell.execute_reply":"2022-08-13T14:04:39.095056Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = _load_parquet(dir_path.joinpath('train.parquet'))\nprint(df_train.shape)\ndisplay(df_train.head(20))","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:04:39.376104Z","iopub.execute_input":"2022-08-13T14:04:39.376799Z","iopub.status.idle":"2022-08-13T14:05:07.291299Z","shell.execute_reply.started":"2022-08-13T14:04:39.376766Z","shell.execute_reply":"2022-08-13T14:05:07.289755Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"raw_categorical_features = [\n    'B_30', 'B_38', 'D_114', 'D_116', 'D_117',\n    'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68',\n]\n\nadditional_categorical_features = [\n    'B_16', 'B_20', 'B_22', 'B_31', 'B_32',\n    'R_2', 'R_4', 'R_9', 'R_10', 'R_15', 'R_19', 'R_21',\n    'R_22', 'R_23', 'R_24', 'R_25', 'R_28',\n    'D_51', 'D_72', 'D_81', 'D_82', 'D_86', 'D_87',\n    'D_89', 'D_91', 'D_92', 'D_93', 'D_94', 'D_96',\n    'D_103', 'D_107', 'D_108', 'D_109', 'D_111', 'D_122',\n    'D_125', 'D_127', 'D_129', 'D_135',\n    'D_136', 'D_137', 'D_138', 'D_139', 'D_140', 'D_143',\n    'S_6', 'S_18', 'S_20',\n]\n\ncategorical_features = raw_categorical_features + additional_categorical_features\n\ncategorical_features = [c for c in categorical_features if c in df_train.columns]\n\nnumerical_features = [c for c in df_train.columns\n                      if c not in categorical_features and c != 'customer_ID']\nprint(len(categorical_features))\nprint(len(numerical_features))","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:05:07.294488Z","iopub.execute_input":"2022-08-13T14:05:07.294924Z","iopub.status.idle":"2022-08-13T14:05:07.319801Z","shell.execute_reply.started":"2022-08-13T14:05:07.294884Z","shell.execute_reply":"2022-08-13T14:05:07.317821Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def preprocess_df(df_: cudf.DataFrame) -> cudf.DataFrame:\n    last1 = lambda x: x.nth(-1); last1.__name__ = 'last'\n    last2 = lambda x: x.nth(-2); last2.__name__ = '2_last'\n    last3 = lambda x: x.nth(-3); last3.__name__ = '3_last'\n    last4 = lambda x: x.nth(-4); last4.__name__ = '4_last'\n    \n    Ps_features = ['P_2', 'P_3']\n    for f in Ps_features:\n        df_[f] = df_[f]**2\n    \n    group = df_.groupby('customer_ID', sort=False)\n    \n    g_nume = group[numerical_features].agg(\n        ['mean', 'std', 'min', 'max', 'first', last1, last2, last3, last4]\n    )\n    g_nume.columns = ['_'.join(x) for x in g_nume.columns]\n    \n    g_nume_calc = calculation(g_nume)\n    \n    g_cate = group[categorical_features].agg(\n        ['first', 'last']\n    )\n    g_cate.columns = ['_'.join(x) for x in g_cate.columns]\n    \n    g = cudf.concat([g_nume, g_nume_calc, g_cate], axis=1)\n    \n    float64_cols = {c: 'float32' for c in g.dtypes[g.dtypes=='float64'].index}\n    int64_cols = {c: 'int32' for c in g.dtypes[g.dtypes == 'int64'].index}\n    g.astype({**float64_cols, **int64_cols})\n    \n    print('g_nume_calc.shape', g_nume_calc.shape)\n    del group, g_nume, g_cate\n    gc.collect()\n    \n    print('g.shape ', g.shape)\n    del df_\n    gc.collect()\n    \n    return g\n\n\ndef calculation(df_: cudf.DataFrame) -> cudf.DataFrame:\n    results = []\n    for nf in numerical_features:\n        period_14 = [nf+'_last', nf+'_2_last', nf+'_3_last', nf+'_4_last']\n        \n        diff_last_first = df_[nf+'_last'] - df_[nf+'_first']\n        diff_last_first.name = nf + '_diff_last_first'\n        \n        # coefficient of variance\n        coeffov_14 = df_[period_14].std(axis=1, skipna=True) / df_[period_14].mean(axis=1, skipna=True)\n        coeffov_14.name = nf + '_coeffov_14'\n        \n        coeffov_all = df_[nf+'_std'] / df_[nf+'_mean']\n        coeffov_all.name = nf + '_coeffov_all'\n        \n        # statistical significance\n        stats_sig = (df_[nf+'_last'] - df_[nf+'_mean']) / df_[nf+'_std']\n        stats_sig.name = nf +'_stats_sig'\n        \n        results.append(\n            cudf.concat([df_[nf+'_last'], diff_last_first, coeffov_all, coeffov_14, stats_sig], axis=1)\n        )\n        \n        # drop\n        df_.drop([nf+'_mean', nf+'_std', nf+'_first']+period_14, axis=1, inplace=True)\n        \n        del period_14, diff_last_first, coeffov_all, coeffov_14, stats_sig\n        \n    results = cudf.concat(results, axis=1)\n    print('Done!\\n')\n    return results","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:05:07.322228Z","iopub.execute_input":"2022-08-13T14:05:07.323391Z","iopub.status.idle":"2022-08-13T14:05:07.344978Z","shell.execute_reply.started":"2022-08-13T14:05:07.323346Z","shell.execute_reply":"2022-08-13T14:05:07.343516Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ndf_train = preprocess_df(df_train)\ndf_train = df_train[sorted(list(df_train.columns))]\n\nprint(df_train.shape)\ndisplay(df_train.head())","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:05:07.350846Z","iopub.execute_input":"2022-08-13T14:05:07.351218Z","iopub.status.idle":"2022-08-13T14:05:17.902813Z","shell.execute_reply.started":"2022-08-13T14:05:07.351174Z","shell.execute_reply":"2022-08-13T14:05:17.901228Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"targets = cudf.read_csv(raw_dir_path.joinpath('train_labels.csv'))\ntargets['customer_ID'] = targets['customer_ID'].str[-16:].str.hex_to_int().astype('int64')\ntargets = targets.set_index('customer_ID')\n\ndf_train = df_train.merge(targets, left_index=True, right_index=True, how='left')\ndf_train.target = df_train.target.astype('int8')\ndf_train = df_train.sort_index().reset_index()\ndel targets\n\ndisplay(df_train.head())","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:05:17.905848Z","iopub.execute_input":"2022-08-13T14:05:17.906911Z","iopub.status.idle":"2022-08-13T14:05:22.594504Z","shell.execute_reply.started":"2022-08-13T14:05:17.906864Z","shell.execute_reply":"2022-08-13T14:05:22.593195Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()\n\ndf_train = df_train.nans_to_nulls()\ndisplay(df_train.head())\nprint(df_train.iloc[:len(df_train)//2].shape)\n\ndf_train.iloc[:len(df_train)//2].to_parquet('preprocessed_train_0.parquet')\ndf_train.iloc[len(df_train)//2:].to_parquet('preprocessed_train_1.parquet')\n\ndel df_train","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:05:22.596685Z","iopub.execute_input":"2022-08-13T14:05:22.597643Z","iopub.status.idle":"2022-08-13T14:05:40.691853Z","shell.execute_reply.started":"2022-08-13T14:05:22.597598Z","shell.execute_reply":"2022-08-13T14:05:40.690549Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:05:40.693940Z","iopub.execute_input":"2022-08-13T14:05:40.694273Z","iopub.status.idle":"2022-08-13T14:05:40.854148Z","shell.execute_reply.started":"2022-08-13T14:05:40.694244Z","shell.execute_reply":"2022-08-13T14:05:40.852656Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### preprocess test data","metadata":{}},{"cell_type":"code","source":"def preprocess_test_data(path: pathlib.Path) -> None:\n    indices = get_group_indices(path)\n    for e, i in enumerate(range(len(indices)-1)):\n        print(f'load data from {indices[i]} to {indices[i+1]}...')\n        df_test = _load_parquet(\n            path,\n            skiprows=indices[i],\n            num_rows=indices[i+1]-indices[i]\n        )\n        #display(df_test.head(2))\n        #display(df_test.tail(2))\n        #df_test = df_test[nan_columns]\n        df_test = preprocess_df(df_test)\n        df_test = df_test[sorted(list(df_test.columns))]\n        df_test = df_test.sort_index().reset_index()\n        \n        df_test = df_test.nans_to_nulls()\n        #display(df_test.head())\n        df_test.to_parquet(f'preprocessed_test_{e}.parquet')\n        \n        del df_test\n        gc.collect()\n        \n\ndef get_group_indices(path: pathlib.Path) -> List[int]:\n    df_ = cudf.read_parquet(path, columns=['customer_ID'])\n    df_.reset_index(inplace=True)\n    groups = df_.groupby('customer_ID').agg(['last'])\n    groups.reset_index(drop=True, inplace=True)\n    groups = groups.to_pandas()\n    \n    num_data = 0\n    indices = [0]\n    for i in groups[('index', 'last')]:\n        if i - num_data >= 3000000 or i == len(df_) -1:\n            num_data = i\n            indices.append(i+1)\n            \n    \"\"\"\n    for j in range(len(start)-1):\n        display(df_.iloc[start[j]-5:start[j]+5])\n        display(df_.iloc[start[j]:start[j+1]])\n        print(\"-\"*50)\n    \"\"\"\n        \n    return indices","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:05:40.857222Z","iopub.execute_input":"2022-08-13T14:05:40.857683Z","iopub.status.idle":"2022-08-13T14:05:41.594555Z","shell.execute_reply.started":"2022-08-13T14:05:40.857612Z","shell.execute_reply":"2022-08-13T14:05:41.592854Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"preprocess_test_data(dir_path.joinpath('test.parquet'))","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:05:41.597750Z","iopub.execute_input":"2022-08-13T14:05:41.598696Z","iopub.status.idle":"2022-08-13T14:07:05.605740Z","shell.execute_reply.started":"2022-08-13T14:05:41.598650Z","shell.execute_reply":"2022-08-13T14:07:05.604368Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-08-13T14:07:05.610440Z","iopub.execute_input":"2022-08-13T14:07:05.612487Z","iopub.status.idle":"2022-08-13T14:07:05.792605Z","shell.execute_reply.started":"2022-08-13T14:07:05.612440Z","shell.execute_reply":"2022-08-13T14:07:05.790700Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}