{"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":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport gc\nimport random\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-07-26T08:00:34.666141Z","iopub.execute_input":"2022-07-26T08:00:34.666768Z","iopub.status.idle":"2022-07-26T08:00:34.700272Z","shell.execute_reply.started":"2022-07-26T08:00:34.666606Z","shell.execute_reply":"2022-07-26T08:00:34.698877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step 1: Read data frames","metadata":{}},{"cell_type":"code","source":"train = pd.read_parquet('/kaggle/input/amex-data-integer-dtypes-parquet-format/train.parquet')\n#test = pd.read_parquet('/kaggle/input/amex-data-integer-dtypes-parquet-format/test.parquet')\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(train.columns)\n#print(test.columns)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step 2: Add short IDs to save space in sample df","metadata":{}},{"cell_type":"code","source":"train['customer_ID_new'] = train['customer_ID'].rank(method=\"dense\").astype('int64')\ntrain.set_index('customer_ID', inplace=True)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step3: append target variable","metadata":{}},{"cell_type":"code","source":"label_df = pd.read_csv(\"/kaggle/input/amex-default-prediction/train_labels.csv\")\nlabel_df.set_index('customer_ID', inplace=True)\nlabel_df.index","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Join labels on DF\ntrain = train.join(label_df)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndel label_df\ngc.collect()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Change index:\ntrain['customer_ID'] = train.index\ntrain.set_index('customer_ID_new', inplace=True)\n\ndel train['customer_ID']\ngc.collect()\ntrain.head(5)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step4: Creating sample","metadata":{}},{"cell_type":"code","source":"random.seed(30)\nid_list_unique_sample = random.sample(train.index.unique().tolist(),350000)\nid_list_unique_sample.sort()\nprint(id_list_unique_sample[1:50])","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_sample = train[train.index.isin(id_list_unique_sample)]\n\ndel train\ngc.collect()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_sample.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_sample.to_parquet(\"train_data_sample_350k.parquet\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#train_sample = pd.read_parquet(\"train_data_sample_350k.parquet\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step 5: Collapse into individuals","metadata":{}},{"cell_type":"code","source":"train_individual = train_sample.groupby( ['customer_ID_new', \"target\"] ).size().to_frame(name = 'TOTAL_Count').reset_index()\n\ntrain_individual.set_index('customer_ID_new', inplace=True)\n\ntrain_individual.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step 6a: aggregate categorical variables:","metadata":{"execution":{"iopub.status.busy":"2022-07-26T07:47:02.930938Z","iopub.execute_input":"2022-07-26T07:47:02.931398Z","iopub.status.idle":"2022-07-26T07:47:02.938957Z","shell.execute_reply.started":"2022-07-26T07:47:02.931361Z","shell.execute_reply":"2022-07-26T07:47:02.937130Z"}}},{"cell_type":"code","source":"cat_vars = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\ntrain_sample[cat_vars] = train_sample[cat_vars].replace(-1, 99) # replace -1 cat with 99 to avoid problems with variable creation","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"group_by_string = ['customer_ID_new'] + cat_vars # create group by string\ndf_tmp_grouped = pd.DataFrame(train_sample.groupby(group_by_string, as_index=True, group_keys=False)[\"S_2\"].count() ) # group by customer_ID and all cat variables\ndf_tmp_grouped = df_tmp_grouped.reset_index()\n\ndf_tmp_grouped.rename(columns = {'S_2':'Count'}, inplace = True) #rename count variable\n                             \ndf_tmp_grouped.head(5)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for var in cat_vars: #loop over cat variables\n    unique_values_in_catvar = df_tmp_grouped[var].unique() #Which unique values does the variable have\n    \n    for v in unique_values_in_catvar: #Loop overunique values of variable\n        name_of_var_CAT = \"CAT_\"+var+\"_\"+str(v) #CAT set name for variable by combining variable name and value\n        name_of_var_SHARE = \"SHARE_\"+var+\"_\"+str(v) #CAT set name for variable by combining variable name and value\n        \n        train_individual[name_of_var_CAT] = 0 #initiate variable in train_individual as 0\n        #train_individual[name_of_var_SHARE] = 0 #initiate variable in train_individual as 0\n        \n        train_individual[name_of_var_CAT] = train_individual[name_of_var_CAT].astype(np.int8) #Change to smaller datetype to save memory\n        #train_individual[name_of_var_SHARE] = train_individual[name_of_var_SHARE].astype(np.float32) #Change datetype\n        \n        df_tmp_grouped_filtered = df_tmp_grouped.query(str(var) + \"==\" + str(v)) # Filter on var==v\n        \n        list_matches = df_tmp_grouped_filtered['customer_ID_new'].unique() #create  alist of matching customers which have the value for the variable\n        sum_matches = df_tmp_grouped_filtered.groupby(['customer_ID_new'], as_index=False)[\"Count\"].sum()\n\n        sum_matches.rename(columns={\"Count\": name_of_var_SHARE}, inplace=True)\n        \n        \n        train_individual.loc[train_individual.index.isin(list_matches), name_of_var_CAT] = 1 #set to 1 for these customers in CAT Variable\n        train_individual = pd.merge(train_individual, sum_matches[['customer_ID_new', name_of_var_SHARE]] , how=\"left\", on='customer_ID_new') #Join share_var\n        train_individual.set_index('customer_ID_new', inplace=True)\n        train_individual[name_of_var_SHARE] = train_individual[name_of_var_SHARE].fillna(0)\n        \n        train_individual[name_of_var_SHARE] = train_individual[name_of_var_SHARE]/train_individual[\"TOTAL_Count\"]\n        \n    print(var)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_individual.head(10)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step 6b: aggregate numeric variables:","metadata":{"execution":{"iopub.status.busy":"2022-07-26T07:52:20.064704Z","iopub.execute_input":"2022-07-26T07:52:20.065210Z","iopub.status.idle":"2022-07-26T07:52:20.073101Z","shell.execute_reply.started":"2022-07-26T07:52:20.065173Z","shell.execute_reply":"2022-07-26T07:52:20.071597Z"}}},{"cell_type":"code","source":"features = train_sample.columns.to_list() # define all feature columns\nnum_vars = [col for col in features if col not in cat_vars] # select numeric features\nnum_vars =  num_vars[1:len(num_vars)-1]# cut other variables ( last (=target))\nprint(num_vars)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Compute Mean, STD, Min, Max & Last for numerical features\nfor var in num_vars:\n    train_num_agg = train_sample.groupby(\"customer_ID_new\")[var].agg(['mean', 'std', 'min', 'max', 'last']) # create aggregate function for all numeric columns\n    #train_num_agg.columns = ['_'.join(x) for x in train_num_agg.columns] # collapse column names\n    train_num_agg.columns = var + \"_\" + train_num_agg.columns\n    \n    train_individual = train_individual.join(train_num_agg)\n    print(var)\n\n\n#Compute Count, last, nunique for categorical features\nfor var in cat_vars:\n    train_num_agg = train_sample.groupby(\"customer_ID_new\")[var].agg(['count', 'last', 'nunique']) # create aggregate function for all numeric columns\n    #train_num_agg.columns = ['_'.join(x) for x in train_num_agg.columns] # collapse column names\n    train_num_agg.columns = var + \"_\" + train_num_agg.columns\n    \n    train_individual = train_individual.join(train_num_agg)\n    print(var)\n\ntrain_individual.head()","metadata":{"scrolled":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del train_sample\ngc.collect()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_individual.to_parquet(\"train_data_sample_350k_individual.parquet\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del train_individual\ngc.collect()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%reset -f","metadata":{"execution":{"iopub.status.busy":"2022-07-26T09:23:04.074934Z","iopub.execute_input":"2022-07-26T09:23:04.075354Z","iopub.status.idle":"2022-07-26T09:23:04.551217Z","shell.execute_reply.started":"2022-07-26T09:23:04.075322Z","shell.execute_reply":"2022-07-26T09:23:04.549877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport gc\nimport random\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Test data","metadata":{"execution":{"iopub.status.busy":"2022-07-26T07:58:54.305004Z","iopub.execute_input":"2022-07-26T07:58:54.305496Z","iopub.status.idle":"2022-07-26T07:58:54.310409Z","shell.execute_reply.started":"2022-07-26T07:58:54.305455Z","shell.execute_reply":"2022-07-26T07:58:54.309487Z"}}},{"cell_type":"markdown","source":"***\n# Step 1: Read data frames","metadata":{}},{"cell_type":"code","source":"test = pd.read_parquet('/kaggle/input/amex-data-integer-dtypes-parquet-format/test.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:00:42.119364Z","iopub.execute_input":"2022-07-26T08:00:42.119800Z","iopub.status.idle":"2022-07-26T08:01:21.186298Z","shell.execute_reply.started":"2022-07-26T08:00:42.119752Z","shell.execute_reply":"2022-07-26T08:01:21.185123Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step 5: Collapse into individuals","metadata":{}},{"cell_type":"code","source":"test_individual = test.groupby( ['customer_ID'] ).size().to_frame(name = 'TOTAL_Count').reset_index()\n\ntest_individual.set_index('customer_ID', inplace=True)\n\ntest_individual.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:02:42.671593Z","iopub.execute_input":"2022-07-26T08:02:42.672583Z","iopub.status.idle":"2022-07-26T08:02:45.988344Z","shell.execute_reply.started":"2022-07-26T08:02:42.672532Z","shell.execute_reply":"2022-07-26T08:02:45.987027Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step 6a: aggregate categorical variables:","metadata":{}},{"cell_type":"code","source":"cat_vars = ['B_30', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68']\ntest[cat_vars] = test[cat_vars].replace(-1, 99) # replace -1 cat with 99 to avoid problems with variable creation","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:03:41.440751Z","iopub.execute_input":"2022-07-26T08:03:41.442134Z","iopub.status.idle":"2022-07-26T08:03:42.034345Z","shell.execute_reply.started":"2022-07-26T08:03:41.442085Z","shell.execute_reply":"2022-07-26T08:03:42.032823Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"group_by_string = ['customer_ID'] + cat_vars # create group by string\ndf_tmp_grouped = pd.DataFrame(test.groupby(group_by_string, as_index=True, group_keys=False)[\"S_2\"].count() ) # group by customer_ID and all cat variables\ndf_tmp_grouped = df_tmp_grouped.reset_index()\n\ndf_tmp_grouped.rename(columns = {'S_2':'Count'}, inplace = True) #rename count variable\n                             \ndf_tmp_grouped.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:04:07.872119Z","iopub.execute_input":"2022-07-26T08:04:07.872608Z","iopub.status.idle":"2022-07-26T08:04:17.112194Z","shell.execute_reply.started":"2022-07-26T08:04:07.872572Z","shell.execute_reply":"2022-07-26T08:04:17.110588Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for var in cat_vars: #loop over cat variables\n    unique_values_in_catvar = df_tmp_grouped[var].unique() #Which unique values does the variable have\n    \n    for v in unique_values_in_catvar: #Loop overunique values of variable\n        name_of_var_CAT = \"CAT_\"+var+\"_\"+str(v) #CAT set name for variable by combining variable name and value\n        name_of_var_SHARE = \"SHARE_\"+var+\"_\"+str(v) #CAT set name for variable by combining variable name and value\n        \n        test_individual[name_of_var_CAT] = 0 #initiate variable in test_individual as 0\n        #test_individual[name_of_var_SHARE] = 0 #initiate variable in test_individual as 0\n        \n        test_individual[name_of_var_CAT] = test_individual[name_of_var_CAT].astype(np.int8) #Change to smaller datetype to save memory\n        #test_individual[name_of_var_SHARE] = test_individual[name_of_var_SHARE].astype(np.float32) #Change datetype\n        \n        df_tmp_grouped_filtered = df_tmp_grouped.query(str(var) + \"==\" + str(v)) # Filter on var==v\n        \n        list_matches = df_tmp_grouped_filtered['customer_ID'].unique() #create  alist of matching customers which have the value for the variable\n        sum_matches = df_tmp_grouped_filtered.groupby(['customer_ID'], as_index=False)[\"Count\"].sum()\n\n        sum_matches.rename(columns={\"Count\": name_of_var_SHARE}, inplace=True)\n        \n        \n        test_individual.loc[test_individual.index.isin(list_matches), name_of_var_CAT] = 1 #set to 1 for these customers in CAT Variable\n        test_individual = pd.merge(test_individual, sum_matches[['customer_ID', name_of_var_SHARE]] , how=\"left\", on='customer_ID') #Join share_var\n        test_individual.set_index('customer_ID', inplace=True)\n        test_individual[name_of_var_SHARE] = test_individual[name_of_var_SHARE].fillna(0)\n        \n        test_individual[name_of_var_SHARE] = test_individual[name_of_var_SHARE]/test_individual[\"TOTAL_Count\"]\n        \n        del list_matches, sum_matches\n        gc.collect()\n        \n    print(var)","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:06:54.419953Z","iopub.execute_input":"2022-07-26T08:06:54.420391Z","iopub.status.idle":"2022-07-26T08:08:50.799566Z","shell.execute_reply.started":"2022-07-26T08:06:54.420358Z","shell.execute_reply":"2022-07-26T08:08:50.797988Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Defragmentation\ntest_individual = test_individual.copy()","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:09:43.741499Z","iopub.execute_input":"2022-07-26T08:09:43.742052Z","iopub.status.idle":"2022-07-26T08:09:43.925288Z","shell.execute_reply.started":"2022-07-26T08:09:43.741994Z","shell.execute_reply":"2022-07-26T08:09:43.923993Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"***\n# Step 6b: aggregate numeric variables:","metadata":{}},{"cell_type":"code","source":"features = test.columns.to_list() # define all feature columns\nnum_vars = [col for col in features if col not in cat_vars] # select numeric features\nnum_vars =  num_vars[2:len(num_vars)]# cut customer_ID\nprint(num_vars)","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:14:13.299933Z","iopub.execute_input":"2022-07-26T08:14:13.301245Z","iopub.status.idle":"2022-07-26T08:14:13.309557Z","shell.execute_reply.started":"2022-07-26T08:14:13.301178Z","shell.execute_reply":"2022-07-26T08:14:13.308389Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Compute Mean, STD, Min, Max & Last for numerical features\nfor var in num_vars:\n    test_num_agg = test.groupby(\"customer_ID\")[var].agg(['mean', 'std', 'min', 'max', 'last']) # create aggregate function for all numeric columns\n    #train_num_agg.columns = ['_'.join(x) for x in train_num_agg.columns] # collapse column names\n    test_num_agg.columns = var + \"_\" + test_num_agg.columns\n    \n    test_individual = test_individual.join(test_num_agg)\n    print(var)\n\n\n#Compute Count, last, nunique for categorical features\nfor var in cat_vars:\n    test_num_agg = test.groupby(\"customer_ID\")[var].agg(['count', 'last', 'nunique']) # create aggregate function for all numeric columns\n    #train_num_agg.columns = ['_'.join(x) for x in train_num_agg.columns] # collapse column names\n    test_num_agg.columns = var + \"_\" + test_num_agg.columns\n    \n    test_individual = test_individual.join(test_num_agg)\n    print(var)\n\ntest_individual.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:14:19.739364Z","iopub.execute_input":"2022-07-26T08:14:19.740975Z","iopub.status.idle":"2022-07-26T08:28:03.779642Z","shell.execute_reply.started":"2022-07-26T08:14:19.740924Z","shell.execute_reply":"2022-07-26T08:28:03.777988Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del test\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:28:14.710445Z","iopub.execute_input":"2022-07-26T08:28:14.710918Z","iopub.status.idle":"2022-07-26T08:28:14.982356Z","shell.execute_reply.started":"2022-07-26T08:28:14.710857Z","shell.execute_reply":"2022-07-26T08:28:14.981441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_individual.to_parquet(\"test_data_individual.parquet\")","metadata":{"execution":{"iopub.status.busy":"2022-07-26T08:28:38.258044Z","iopub.execute_input":"2022-07-26T08:28:38.258548Z","iopub.status.idle":"2022-07-26T08:29:13.806051Z","shell.execute_reply.started":"2022-07-26T08:28:38.258509Z","shell.execute_reply":"2022-07-26T08:29:13.803596Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del test_individual","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"execution":{"iopub.status.busy":"2022-07-26T09:22:39.847628Z","iopub.execute_input":"2022-07-26T09:22:39.848172Z","iopub.status.idle":"2022-07-26T09:22:40.992675Z","shell.execute_reply.started":"2022-07-26T09:22:39.848131Z","shell.execute_reply":"2022-07-26T09:22:40.991461Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dir()","metadata":{"execution":{"iopub.status.busy":"2022-07-26T09:22:47.091889Z","iopub.execute_input":"2022-07-26T09:22:47.093137Z","iopub.status.idle":"2022-07-26T09:22:47.100730Z","shell.execute_reply.started":"2022-07-26T09:22:47.093077Z","shell.execute_reply":"2022-07-26T09:22:47.099855Z"},"trusted":true},"execution_count":null,"outputs":[]}]}