{"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":"# Walmart Trip Type Classification\n### Walmart uses both art and science to continually make progress on their core mission of better understanding and serving their customers. One way Walmart is able to improve customers' shopping experiences is by segmenting their store visits into different trip types. Whether they're on a last minute run for new puppy supplies or leisurely making their way through a weekly grocery list, classifying trip types enables Walmart to create the best shopping experience for every customer.\n\n### Currently, Walmart's trip types are created from a combination of existing customer insights (\"art\") and purchase history data (\"science\"). In their third recruiting competition, Walmart is challenging Kagglers to focus on the (data) science and classify customer trips using only a transactional dataset of the items they've purchased. Improving the science behind trip type classification will help Walmart refine their segmentation process.","metadata":{}},{"cell_type":"markdown","source":"# This is a supervised classification problem. And two main steps are conducted\n### Step 1. Cleaning data and feature engineering\n### Step 2. Running a random forest model (xgboost) on the training set and make a prediction on the test set","metadata":{}},{"cell_type":"markdown","source":"# Step 1. Cleaning data and feature engineering:\n## A. The train and test csv files are read into two pandas dataframes.\n### There are 1300700 rows of raw records, with train_set and test_set combined\n### There are in total 31 days of records, and 191348 visits, therefore about 6173 visits per day\n### Each row is a record, each visit has a 'VisitNumber', a visit may have multiple records, and each visit has a 'TripType'\n\n#### * 'TripType' is the target, 6 features include 'VisitNumber', 'Weekday', 'Upc', 'ScanCount', 'DepartmentDescription' and 'FinelineNumber'. \n####    1. TripType,                  int16\n####    2. VisitNumber,               int32\n####    3. Weekday,                  object\n####    4. Upc,                       int64\n####    5. ScanCount,                 int16\n####    6. DepartmentDescription,    object\n####    7. FinelineNumber,            int16\n####  \n","metadata":{}},{"cell_type":"markdown","source":"## B. New features are created\n#### 01. Feature 'Pos': int, 1 if 'ScanCount' is positive, 0 otherwise\n#### 02. Feature 'Neg': int, 1 if 'ScanCount' is negative, 0 otherwise. These first two features are not independent\n#### 03. Feature 'Time_of_day': float, inferred from the 'VisitNumber', a small 'VisitNumber' corresponds to a time earlier in the day, while a large one means a time later in the day\n#### 04. Feature 'Total_visit': int, the count of 'VisitNumber' in a day, which is the total visit happened in a day\n#### 05. Feature 'Total_sale': int, the sum of 'ScanCount' in a day, which is assigned as the total sale of goods in that day, regardless of the type of goods\n#### 06. Feature 'Return': int, 1 for a visit where the sum of 'ScanCount' > 0, -1 for a visit where the sum of 'ScanCount' < 0, 0, otherwise\n#### 07. Feature 'Total_Items': int, total items in each visit\n#### 08. Feature 'Ent_Upc': float, partial entropy of Upc\n#### 09. Feature 'Ent_Dept': float, entropy of 'DepartmentDescription'\n#### 10. Feature 'Ent_Fln': float, entropy of 'FinelineNumber'\n#### 11. Feature 'Uni_Dept': int, unique number of department for each visit\n#### 12. Feature 'Uni_Fln': int, unique number of FinelineNumber for each visit\n#### 13. Feature 'Uni_Upc': int, unique number of Upc for each visit\n#### 14. Feature 'Fac_Upc': int, splitting the Upc in to Factory and Item, the first 6 digits is for factory, the last 6 digits is for an item\n#### 15. Feature 'Item_Upc': int, the last 6 digits of Upc, which is the item digits\n#### 16. Feature 'Uni_Fac': int, unique number of 'Fac_Upc' for each visit\n#### 17. Feature 'Ent_Fac': float, entropy of 'Fac_Upc'\n#### 18. Feature 'Upc_tfidf': float, tfidf of 'Upc'\n#### 19. Feature 'dept_tfidf': float, tfidf of 'DepartmentDescription'\n#### 20. Feature 'fln_tfidf': float, tfidf of 'FinelineNumber'\n#### 21. Feature 'wd': int, weekday to number 1-7","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nfrom sklearn import preprocessing\nfrom sklearn.model_selection import StratifiedKFold\nfrom scipy.sparse import csr_matrix, hstack\nfrom sklearn.model_selection import StratifiedShuffleSplit\nimport xgboost as xgb\nimport datetime\nfrom pandas.api.types import CategoricalDtype","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:33:58.339972Z","iopub.execute_input":"2022-07-24T04:33:58.340792Z","iopub.status.idle":"2022-07-24T04:33:59.663127Z","shell.execute_reply.started":"2022-07-24T04:33:58.340674Z","shell.execute_reply":"2022-07-24T04:33:59.662231Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Data read-in and initial cleaning","metadata":{}},{"cell_type":"code","source":"# read data\ntrain = pd.read_csv('/kaggle/input/walmart-trip-type-train/train.csv', dtype={'Upc': object, \n                                        'FinelineNumber':object, \n                                        'ScanCount': np.int16, \n                                        'TripType': np.int16,\n                                        'VisitNumber': np.int32})\ntest = pd.read_csv('/kaggle/input/walmart-trip-type-test/test.csv', dtype={'Upc': object, \n                                      'FinelineNumber':object, \n                                      'ScanCount': np.int16, \n                                      'VisitNumber': np.int32})\ntest['TripType'] = 0\n#train.info()\n#test.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:44:32.105419Z","iopub.execute_input":"2022-07-24T04:44:32.106322Z","iopub.status.idle":"2022-07-24T04:44:33.556051Z","shell.execute_reply.started":"2022-07-24T04:44:32.106275Z","shell.execute_reply":"2022-07-24T04:44:33.554813Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Store the VistNumber for train and test datasets\ntr_VisitNumber = list(train.VisitNumber.unique())\nte_VisitNumber = list(test.VisitNumber.unique())","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:44:37.806389Z","iopub.execute_input":"2022-07-24T04:44:37.806886Z","iopub.status.idle":"2022-07-24T04:44:37.849323Z","shell.execute_reply.started":"2022-07-24T04:44:37.806841Z","shell.execute_reply":"2022-07-24T04:44:37.847556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Missing data in the features are either filled with 0 / -1 or 'NA'\ntrain.Upc.fillna('0', inplace=True)\ntrain.DepartmentDescription.fillna('NA', inplace=True)\ntrain.FinelineNumber.fillna('-1', inplace= True)\ntest.Upc.fillna('0', inplace=True)\ntest.DepartmentDescription.fillna('NA', inplace=True)\ntest.FinelineNumber.fillna('-1', inplace= True)","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:44:40.803995Z","iopub.execute_input":"2022-07-24T04:44:40.804576Z","iopub.status.idle":"2022-07-24T04:44:41.288561Z","shell.execute_reply.started":"2022-07-24T04:44:40.804515Z","shell.execute_reply":"2022-07-24T04:44:41.287301Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Change data type\ntrain.Upc = train.Upc.astype(np.int64)\ntrain.FinelineNumber = train.FinelineNumber.astype(np.int16)\ntrain.ScanCount = train.ScanCount.astype(np.int32)\ntest.Upc = test.Upc.astype(np.int64)\ntest.FinelineNumber = test.FinelineNumber.astype(np.int16)\ntest.ScanCount = test.ScanCount.astype(np.int32)","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:44:44.043171Z","iopub.execute_input":"2022-07-24T04:44:44.043741Z","iopub.status.idle":"2022-07-24T04:44:44.882472Z","shell.execute_reply.started":"2022-07-24T04:44:44.043689Z","shell.execute_reply":"2022-07-24T04:44:44.881529Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Fill some NA with educated guess\ntrain.sort_values('VisitNumber', inplace=True)\ntest.sort_values('VisitNumber', inplace=True)\ntrain.at[83042, 'DepartmentDescription'] = 'PHARMACY RX'\ntrain.at[167472, 'DepartmentDescription'] = 'PHARMACY RX'\ntest.at[282483, 'DepartmentDescription'] = 'PHARMACY RX'","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:44:47.472297Z","iopub.execute_input":"2022-07-24T04:44:47.472715Z","iopub.status.idle":"2022-07-24T04:44:47.657546Z","shell.execute_reply.started":"2022-07-24T04:44:47.472681Z","shell.execute_reply":"2022-07-24T04:44:47.655904Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create a data frame with VisitNumber and TripType only for the train set\ntrain_visitnumber_triptype = train.groupby(['VisitNumber']).agg({'TripType': 'first'}).reset_index()\ntrain_visitnumber_triptype.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:44:50.664650Z","iopub.execute_input":"2022-07-24T04:44:50.665162Z","iopub.status.idle":"2022-07-24T04:44:50.727436Z","shell.execute_reply.started":"2022-07-24T04:44:50.665119Z","shell.execute_reply":"2022-07-24T04:44:50.726496Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# The train and test dataframes are conbined together, so the features are dealt together\ntrain_test = pd.concat([train,test], ignore_index=True).sort_values('VisitNumber')\ntrain_test.TripType = train_test.TripType.astype(np.int16)\ntrain_test.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:44:53.756797Z","iopub.execute_input":"2022-07-24T04:44:53.757250Z","iopub.status.idle":"2022-07-24T04:44:54.325067Z","shell.execute_reply.started":"2022-07-24T04:44:53.757216Z","shell.execute_reply":"2022-07-24T04:44:54.323661Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the total number of items purchased per each visit\nsCountPerVisit = train_test.groupby(['VisitNumber']).agg({'ScanCount': 'sum'})\\\n.rename(columns={'ScanCount': 'SCountPerVisit'}).reset_index()\nsCountPerVisit.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:44:57.504558Z","iopub.execute_input":"2022-07-24T04:44:57.505034Z","iopub.status.idle":"2022-07-24T04:44:57.574756Z","shell.execute_reply.started":"2022-07-24T04:44:57.504994Z","shell.execute_reply":"2022-07-24T04:44:57.573488Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the total number of items per visit per Upc\nsCountPerVisitPerUpc = train_test.groupby(['VisitNumber', 'Upc']).agg({'ScanCount': 'sum'})\\\n.rename(columns={'ScanCount': 'SCountPerVisitPerUpc'}).reset_index()\nsCountPerVisitPerUpc.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:00.303907Z","iopub.execute_input":"2022-07-24T04:45:00.304369Z","iopub.status.idle":"2022-07-24T04:45:00.931652Z","shell.execute_reply.started":"2022-07-24T04:45:00.304332Z","shell.execute_reply":"2022-07-24T04:45:00.930225Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert weekday to number\nwdict = {'Monday':1,\n        'Tuesday':2,\n        'Wednesday':3,\n        'Thursday':4,\n        'Friday':5,\n        'Saturday':6,\n        'Sunday':7}\n\ntrain_test['wd'] = train_test.Weekday.apply(lambda x: wdict[x])","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:03.264876Z","iopub.execute_input":"2022-07-24T04:45:03.265388Z","iopub.status.idle":"2022-07-24T04:45:04.047016Z","shell.execute_reply.started":"2022-07-24T04:45:03.265344Z","shell.execute_reply":"2022-07-24T04:45:04.045767Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Remove some records with TripType==999, because their feature is very discernable","metadata":{}},{"cell_type":"code","source":"# netout_visits include visits which the TotalItems <= 0\nnetout_visits = list(sCountPerVisit[sCountPerVisit.SCountPerVisit <= 0]['VisitNumber'])\n# In the train set, these visits types are almost always 999\ntrain_netout_visits = train_visitnumber_triptype[train_visitnumber_triptype.VisitNumber.isin(netout_visits)]\nprint(np.count_nonzero(train_netout_visits.TripType == 999) / train_netout_visits.shape[0] *100,'% of these visits are of TripType 999')","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:07.328328Z","iopub.execute_input":"2022-07-24T04:45:07.328812Z","iopub.status.idle":"2022-07-24T04:45:07.351919Z","shell.execute_reply.started":"2022-07-24T04:45:07.328774Z","shell.execute_reply":"2022-07-24T04:45:07.350511Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# someout_visits include visits where the total ScanCount of some Upc items is less than 0\nsomeout_visits = list(sCountPerVisitPerUpc[sCountPerVisitPerUpc.SCountPerVisitPerUpc < 0]['VisitNumber'])\n# In the train set, these visits types are almost always 999\ntrain_someout_visits = train_visitnumber_triptype[train_visitnumber_triptype.VisitNumber.isin(someout_visits)]\nprint(np.count_nonzero(train_someout_visits.TripType == 999) / train_someout_visits.shape[0] *100,'% of these visits are of TripType 999')","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:09.790393Z","iopub.execute_input":"2022-07-24T04:45:09.791939Z","iopub.status.idle":"2022-07-24T04:45:09.824495Z","shell.execute_reply.started":"2022-07-24T04:45:09.791878Z","shell.execute_reply":"2022-07-24T04:45:09.823340Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Therefore it is safe to assign 999 to these visits, so they are removed for now.\ntrain_test = train_test.query('VisitNumber != @netout_visits & VisitNumber != @someout_visits')\ntrain_test.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:12.435736Z","iopub.execute_input":"2022-07-24T04:45:12.437014Z","iopub.status.idle":"2022-07-24T04:45:12.653087Z","shell.execute_reply.started":"2022-07-24T04:45:12.436962Z","shell.execute_reply":"2022-07-24T04:45:12.651629Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Add some basic features","metadata":{}},{"cell_type":"code","source":"# Add columns 'Pos' and 'Neg'.\n# They are correlated but are useful to mark those return records, before aggregating ScanCount\ntrain_test['Pos'] = (train_test.ScanCount > 0).astype(np.int16)\ntrain_test['Neg'] = (train_test.ScanCount < 0).astype(np.int16)","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:15.639379Z","iopub.execute_input":"2022-07-24T04:45:15.639848Z","iopub.status.idle":"2022-07-24T04:45:15.656839Z","shell.execute_reply.started":"2022-07-24T04:45:15.639814Z","shell.execute_reply":"2022-07-24T04:45:15.655646Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Aggregate ScanCount\ntrain_test = train_test.groupby(['VisitNumber',\n 'Upc',\n 'DepartmentDescription',\n 'Weekday',\n 'FinelineNumber',\n 'TripType'], as_index=False).sum().sort_values('VisitNumber')\n# Add column Return, which is the sign of ScanCount\ntrain_test['Return'] = train_test.ScanCount.map(lambda x: np.sign(x)).astype(np.int16)\ntrain_test.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:17.650483Z","iopub.execute_input":"2022-07-24T04:45:17.651504Z","iopub.status.idle":"2022-07-24T04:45:22.082620Z","shell.execute_reply.started":"2022-07-24T04:45:17.651460Z","shell.execute_reply":"2022-07-24T04:45:22.081211Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Add time of the day as a fraction of one day, 0 is the first visit of the day, 1 means the last visit of the day\n# 'first' is the first VisitNumber of the day\ntrain_test['first'] = (train_test\n               .groupby((train_test.Weekday != train_test.Weekday.shift()).cumsum())\n               .VisitNumber\n               .transform('first'))\n# 'last' is the last VisitNumber of the day\ntrain_test['last'] =  (train_test\n               .groupby((train_test.Weekday != train_test.Weekday.shift()).cumsum())\n               .VisitNumber\n               .transform('last'))\ntrain_test['time_of_day'] = (train_test['VisitNumber'] - train_test['first'] + 1) / (train_test['last'] - train_test['first'] + 1)","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:25.106712Z","iopub.execute_input":"2022-07-24T04:45:25.107392Z","iopub.status.idle":"2022-07-24T04:45:25.711265Z","shell.execute_reply.started":"2022-07-24T04:45:25.107357Z","shell.execute_reply":"2022-07-24T04:45:25.709637Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create a column 'day_counter'. There are 31 days in total\ntrain_test['day_counter'] = (train_test.Weekday != train_test.Weekday.shift()).cumsum()","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:28.482515Z","iopub.execute_input":"2022-07-24T04:45:28.482922Z","iopub.status.idle":"2022-07-24T04:45:28.749817Z","shell.execute_reply.started":"2022-07-24T04:45:28.482891Z","shell.execute_reply":"2022-07-24T04:45:28.748546Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the total number of visits per day, to be used later\nvisitsPerDay = train_test.groupby('day_counter').VisitNumber.apply(lambda x: len(np.unique(x))).reset_index()\\\n.rename(columns={'VisitNumber' :'VisitsPerDay'}).astype({'day_counter': np.int16, 'VisitsPerDay': np.int16})\nvisitsPerDay.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:31.397486Z","iopub.execute_input":"2022-07-24T04:45:31.397889Z","iopub.status.idle":"2022-07-24T04:45:31.487397Z","shell.execute_reply.started":"2022-07-24T04:45:31.397856Z","shell.execute_reply":"2022-07-24T04:45:31.486243Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the sum of ScanCount per day, to be used later\nsCountPerDay = train_test.groupby('day_counter').agg({'ScanCount': 'sum'}).reset_index()\\\n.rename(columns={'ScanCount': 'SCountPerDay'}).astype({'day_counter': np.int16, 'SCountPerDay': np.int32})\nsCountPerDay.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:34.906895Z","iopub.execute_input":"2022-07-24T04:45:34.907318Z","iopub.status.idle":"2022-07-24T04:45:34.951135Z","shell.execute_reply.started":"2022-07-24T04:45:34.907287Z","shell.execute_reply":"2022-07-24T04:45:34.950129Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Add sCountPerVisit to train_test\ntrain_test = pd.merge(train_test, sCountPerVisit, how='left', on=['VisitNumber'])\ntrain_test = pd.merge(train_test, sCountPerVisitPerUpc, how='left', on=['VisitNumber', 'Upc'])","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:38.049540Z","iopub.execute_input":"2022-07-24T04:45:38.049975Z","iopub.status.idle":"2022-07-24T04:45:38.901403Z","shell.execute_reply.started":"2022-07-24T04:45:38.049941Z","shell.execute_reply":"2022-07-24T04:45:38.900064Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the ScanCount/SCountPerVisit per each Upc per visit\ntrain_test['Div'] = np.where(train_test['ScanCount']==0, 0, train_test['ScanCount'] / train_test['SCountPerVisit'])","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:42.531654Z","iopub.execute_input":"2022-07-24T04:45:42.532053Z","iopub.status.idle":"2022-07-24T04:45:42.550353Z","shell.execute_reply.started":"2022-07-24T04:45:42.532022Z","shell.execute_reply":"2022-07-24T04:45:42.549230Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Use domain knowledge, split Upc into 2 parts, part1 for factory code, part2 for item code (-1 for missing Upc's)\ntrain_test['Fac_Upc'] = np.where(train_test.Upc==0,-1,train_test.Upc//100000)\ntrain_test['Item_Upc'] = np.where(train_test.Upc==0,-1,train_test.Upc%100000)\ntrain_test.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:45.117682Z","iopub.execute_input":"2022-07-24T04:45:45.118077Z","iopub.status.idle":"2022-07-24T04:45:45.158030Z","shell.execute_reply.started":"2022-07-24T04:45:45.118044Z","shell.execute_reply":"2022-07-24T04:45:45.156694Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Calculate entropy","metadata":{}},{"cell_type":"code","source":"# Calculate entropy of Upc\ntr_upc_ent = train_test[['VisitNumber', 'Upc', 'SCountPerVisit', 'ScanCount', 'DepartmentDescription']]\\\n.groupby(['VisitNumber', 'Upc'])\\\n.agg({'SCountPerVisit': 'first', 'ScanCount': 'sum'}).reset_index()\ntr_upc_ent['Div'] = tr_upc_ent['ScanCount'] / tr_upc_ent['SCountPerVisit']\nwith np.errstate(divide='ignore'):\n    tr_upc_ent['Ent_Upc'] = np.where(tr_upc_ent['Div']==0, 0, tr_upc_ent['Div'] * np.log2(tr_upc_ent['Div']) * -1)\ntr_upc_ent = tr_upc_ent.groupby('VisitNumber').agg({'Ent_Upc': np.sum}).reset_index()\ntr_upc_ent.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:48.853467Z","iopub.execute_input":"2022-07-24T04:45:48.854675Z","iopub.status.idle":"2022-07-24T04:45:49.595684Z","shell.execute_reply.started":"2022-07-24T04:45:48.854626Z","shell.execute_reply":"2022-07-24T04:45:49.593961Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate entropy of DepartmentDescription\ntr_dept_ent = train_test[['VisitNumber', 'DepartmentDescription','SCountPerVisit', 'ScanCount']]\\\n.groupby(['VisitNumber','DepartmentDescription'])\\\n.agg({'SCountPerVisit': 'first', 'ScanCount': 'sum'}).reset_index()\ntr_dept_ent['Div'] = tr_dept_ent['ScanCount'] / tr_dept_ent['SCountPerVisit']\nwith np.errstate(divide='ignore'):\n    tr_dept_ent['Ent_Dept'] = np.where(tr_dept_ent['Div']==0, 0, tr_dept_ent['Div'] * np.log2(tr_dept_ent['Div']) * -1)\ntr_dept_ent = tr_dept_ent.groupby('VisitNumber').agg({'Ent_Dept': np.sum}).reset_index()\ntr_dept_ent.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:52.362320Z","iopub.execute_input":"2022-07-24T04:45:52.363682Z","iopub.status.idle":"2022-07-24T04:45:53.006295Z","shell.execute_reply.started":"2022-07-24T04:45:52.363622Z","shell.execute_reply":"2022-07-24T04:45:53.005255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate entropy of FinelineNumber\ntr_fln_ent = train_test[['VisitNumber', 'FinelineNumber', 'SCountPerVisit', 'ScanCount', 'DepartmentDescription']]\\\n.groupby(['VisitNumber', 'FinelineNumber'])\\\n.agg({'SCountPerVisit': 'first', 'ScanCount': 'sum'}).reset_index()\ntr_fln_ent['Div'] = tr_fln_ent['ScanCount'] / tr_fln_ent['SCountPerVisit']\nwith np.errstate(divide='ignore'):\n    tr_fln_ent['Ent_Fln'] = np.where(tr_fln_ent['Div']==0, 0, tr_fln_ent['Div'] * np.log2(tr_fln_ent['Div']) * -1)\ntr_fln_ent = tr_fln_ent.groupby('VisitNumber').agg({'Ent_Fln': np.sum}).reset_index()\ntr_fln_ent.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:54.915022Z","iopub.execute_input":"2022-07-24T04:45:54.915875Z","iopub.status.idle":"2022-07-24T04:45:55.511544Z","shell.execute_reply.started":"2022-07-24T04:45:54.915836Z","shell.execute_reply":"2022-07-24T04:45:55.510347Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate entropy of Fac_Upc\ntr_fac_ent = train_test[['VisitNumber', 'Fac_Upc', 'SCountPerVisit', 'ScanCount']]\\\n.groupby(['VisitNumber', 'Fac_Upc'])\\\n.agg({'SCountPerVisit': 'first', 'ScanCount': 'sum'}).reset_index()\ntr_fac_ent['Div'] = tr_fac_ent['ScanCount'] / tr_fac_ent['SCountPerVisit']\nwith np.errstate(divide='ignore'):\n    tr_fac_ent['Ent_Fac'] = np.where(tr_fac_ent['Div']==0, 0, tr_fac_ent['Div'] * np.log2(tr_fac_ent['Div']) * -1)\ntr_fac_ent = tr_fac_ent.groupby('VisitNumber').agg({'Ent_Fac': np.sum}).reset_index()\ntr_fac_ent.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:45:58.086517Z","iopub.execute_input":"2022-07-24T04:45:58.086896Z","iopub.status.idle":"2022-07-24T04:45:58.631906Z","shell.execute_reply.started":"2022-07-24T04:45:58.086865Z","shell.execute_reply":"2022-07-24T04:45:58.630511Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Calculate the number of unique items","metadata":{}},{"cell_type":"code","source":"# Calculate the number of unique dept\ntr_uni_dept = train_test.groupby('VisitNumber')['DepartmentDescription'].apply(lambda x: len(np.unique(x))).reset_index()\ntr_uni_dept.rename(columns={'DepartmentDescription': 'Uni_Dept'}, inplace=True)\ntr_uni_dept.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:01.329316Z","iopub.execute_input":"2022-07-24T04:46:01.329807Z","iopub.status.idle":"2022-07-24T04:46:08.012141Z","shell.execute_reply.started":"2022-07-24T04:46:01.329768Z","shell.execute_reply":"2022-07-24T04:46:08.010965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the number of unique FinelineNumber\ntr_uni_fln = train_test.groupby('VisitNumber')['FinelineNumber'].apply(lambda x: len(np.unique(x))).reset_index()\ntr_uni_fln.rename(columns = {'FinelineNumber': 'Uni_Fln'}, inplace = True)\ntr_uni_fln.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:10.815663Z","iopub.execute_input":"2022-07-24T04:46:10.816285Z","iopub.status.idle":"2022-07-24T04:46:16.845023Z","shell.execute_reply.started":"2022-07-24T04:46:10.816251Z","shell.execute_reply":"2022-07-24T04:46:16.843855Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the number of unique Upc\ntr_uni_upc = train_test.groupby('VisitNumber')['Upc'].apply(lambda x: len(np.unique(x))).reset_index()\ntr_uni_upc.rename(columns = {'Upc': 'Uni_Upc'}, inplace = True)\ntr_uni_upc.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:19.089376Z","iopub.execute_input":"2022-07-24T04:46:19.089797Z","iopub.status.idle":"2022-07-24T04:46:25.123407Z","shell.execute_reply.started":"2022-07-24T04:46:19.089765Z","shell.execute_reply":"2022-07-24T04:46:25.122261Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the number of unique factory code\ntr_uni_fac = train_test.groupby('VisitNumber')['Fac_Upc'].apply(lambda x: len(np.unique(x))).reset_index()\ntr_uni_fac.rename(columns = {'Fac_Upc': 'Uni_Fac'}, inplace = True)\ntr_uni_fac.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:26.960791Z","iopub.execute_input":"2022-07-24T04:46:26.961493Z","iopub.status.idle":"2022-07-24T04:46:33.088772Z","shell.execute_reply.started":"2022-07-24T04:46:26.961458Z","shell.execute_reply":"2022-07-24T04:46:33.087624Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Create one-hot dummy variables","metadata":{}},{"cell_type":"code","source":"# Create dummy variable for DepartmentDescription\ntr_dept_dummy = train_test[['VisitNumber', 'DepartmentDescription', 'ScanCount', 'FinelineNumber']]\ntr_dept_dummy = tr_dept_dummy.query('ScanCount > 0')\ntr_dept_dummy = tr_dept_dummy.loc[np.repeat(tr_dept_dummy.index.values, tr_dept_dummy.ScanCount)]\ntr_dept_dummy.drop(['FinelineNumber', 'ScanCount'], axis=1, inplace= True)\ntr_dept_dummy = pd.get_dummies(tr_dept_dummy, prefix='dept', columns=['DepartmentDescription'])\ntr_dept_dummy = tr_dept_dummy.groupby('VisitNumber').sum().reset_index()\ntr_dept_dummy = tr_dept_dummy.astype('Sparse[int64, 0]')\ntr_dept_dummy.columns.values.__len__()","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:35.877052Z","iopub.execute_input":"2022-07-24T04:46:35.878099Z","iopub.status.idle":"2022-07-24T04:46:39.007505Z","shell.execute_reply.started":"2022-07-24T04:46:35.878059Z","shell.execute_reply":"2022-07-24T04:46:39.006465Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate top 10 popular Upc each day\ntr_pop_Upc_day = train_test.groupby(['day_counter', 'Upc']).ScanCount.agg('sum').reset_index()\ntr_pop_Upc_day.sort_values(['day_counter', 'ScanCount'], ascending = [1,0], inplace=True)\ntr_pop_Upc_day['shifted'] = tr_pop_Upc_day.day_counter.shift(10)\ntr_pop_Upc_day['keep'] = (tr_pop_Upc_day.day_counter != tr_pop_Upc_day.shifted)\ntr_pop_Upc_day = tr_pop_Upc_day.query('keep')\ntr_pop_Upc_day_list = list(tr_pop_Upc_day.Upc.unique())\ntr_pop_Upc_day_list.__len__()","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:42.916380Z","iopub.execute_input":"2022-07-24T04:46:42.916834Z","iopub.status.idle":"2022-07-24T04:46:43.285672Z","shell.execute_reply.started":"2022-07-24T04:46:42.916800Z","shell.execute_reply":"2022-07-24T04:46:43.284733Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create popular Upc dummy variable\ntr_pop_upc_dummy = train_test.query('Upc == @tr_pop_Upc_day_list')[['VisitNumber', 'Upc', 'ScanCount']]\ntr_pop_upc_dummy = tr_pop_upc_dummy.loc[np.repeat(tr_pop_upc_dummy.index.values, tr_pop_upc_dummy.ScanCount)]\ntr_pop_upc_dummy = tr_pop_upc_dummy[['VisitNumber', 'Upc']]\ntr_pop_upc_dummy = pd.get_dummies(tr_pop_upc_dummy, prefix='pop_Upc', columns=['Upc'])\ntr_pop_upc_dummy = tr_pop_upc_dummy.groupby('VisitNumber').sum().reset_index()\ntr_pop_upc_dummy = tr_pop_upc_dummy.astype('Sparse[int64, 0]')\ntr_pop_upc_dummy.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:47.295359Z","iopub.execute_input":"2022-07-24T04:46:47.295834Z","iopub.status.idle":"2022-07-24T04:46:47.475876Z","shell.execute_reply.started":"2022-07-24T04:46:47.295800Z","shell.execute_reply":"2022-07-24T04:46:47.474471Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create weekday dummy\ntr_weekday_dummy = train_test[['VisitNumber', 'Weekday']]\ntr_weekday_dummy = tr_weekday_dummy.groupby('VisitNumber').Weekday.agg('first').reset_index()\ntr_weekday_dummy = pd.get_dummies(tr_weekday_dummy, prefix='Weekday', columns=['Weekday'])\ntr_weekday_dummy = tr_weekday_dummy.astype('Sparse[int64, 0]')\ntr_weekday_dummy.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:51.176178Z","iopub.execute_input":"2022-07-24T04:46:51.176689Z","iopub.status.idle":"2022-07-24T04:46:51.415591Z","shell.execute_reply.started":"2022-07-24T04:46:51.176648Z","shell.execute_reply":"2022-07-24T04:46:51.414435Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create month day dummy\ntr_monthday_dummy = train_test[['VisitNumber', 'day_counter']]\ntr_monthday_dummy = tr_monthday_dummy.groupby('VisitNumber').day_counter.agg('first').reset_index()\ntr_monthday_dummy = pd.get_dummies(tr_monthday_dummy, prefix='mday', columns=['day_counter'])\ntr_monthday_dummy = tr_monthday_dummy.astype('Sparse[int64, 0]')\ntr_monthday_dummy.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:54.638557Z","iopub.execute_input":"2022-07-24T04:46:54.638986Z","iopub.status.idle":"2022-07-24T04:46:54.756101Z","shell.execute_reply.started":"2022-07-24T04:46:54.638951Z","shell.execute_reply":"2022-07-24T04:46:54.754803Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Calculate tf-idf for some variables","metadata":{}},{"cell_type":"code","source":"total_visits = np.unique(train_test.VisitNumber).__len__()","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:46:59.421314Z","iopub.execute_input":"2022-07-24T04:46:59.421706Z","iopub.status.idle":"2022-07-24T04:46:59.463537Z","shell.execute_reply.started":"2022-07-24T04:46:59.421676Z","shell.execute_reply":"2022-07-24T04:46:59.462255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate tf-idf for Upc\nUpc_total_visits = train_test.groupby('Upc').VisitNumber.apply(lambda x: len(np.unique(x))).reset_index()\\\n.rename(columns={'VisitNumber': 'Upc_total_visits'})\ntfidf_upc = train_test.groupby(['VisitNumber','Upc']).agg({'ScanCount': 'sum', 'SCountPerVisit': 'first'})\\\n.reset_index()\\\n.assign(tf = lambda x: x.ScanCount / x.SCountPerVisit)\\\n.merge(Upc_total_visits, how='left')\\\n.assign(idf = lambda x: np.log( total_visits/x.Upc_total_visits ))\\\n.assign(Upc_tfidf = lambda x: x.tf * x.idf)\\\n.groupby('VisitNumber').Upc_tfidf.agg([np.sum, np.std]).reset_index()\\\n.fillna(0)\\\n.rename(columns={'sum': 'Upc_tfidf_sum', 'std': 'Upc_tfidf_std'})\ntfidf_upc.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:01.970998Z","iopub.execute_input":"2022-07-24T04:47:01.971942Z","iopub.status.idle":"2022-07-24T04:47:07.276367Z","shell.execute_reply.started":"2022-07-24T04:47:01.971903Z","shell.execute_reply":"2022-07-24T04:47:07.275208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate tf-idf for Fac_Upc\nFac_Upc_total_visits = train_test.groupby('Fac_Upc').VisitNumber.apply(lambda x: len(np.unique(x))).reset_index()\\\n.rename(columns={'VisitNumber': 'Fac_Upc_total_visits'})\ntfidf_fac_upc = train_test.groupby(['VisitNumber','Fac_Upc']).agg({'ScanCount': 'sum', 'SCountPerVisit': 'first'})\\\n.reset_index()\\\n.assign(tf = lambda x: x.ScanCount / x.SCountPerVisit)\\\n.merge(Fac_Upc_total_visits, how='left')\\\n.assign(idf = lambda x: np.log( total_visits/x.Fac_Upc_total_visits ))\\\n.assign(Fac_Upc_tfidf = lambda x: x.tf * x.idf)\\\n.groupby('VisitNumber').Fac_Upc_tfidf.agg([np.sum, np.std]).reset_index()\\\n.fillna(0)\\\n.rename(columns={'sum': 'Fac_Upc_tfidf_sum', 'std': 'Fac_Upc_tfidf_std'})\ntfidf_fac_upc.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:09.916774Z","iopub.execute_input":"2022-07-24T04:47:09.917153Z","iopub.status.idle":"2022-07-24T04:47:10.862003Z","shell.execute_reply.started":"2022-07-24T04:47:09.917124Z","shell.execute_reply":"2022-07-24T04:47:10.860814Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate tf-idf for FinelineNumber\nfln_total_visits = train_test.groupby('FinelineNumber').VisitNumber.apply(lambda x: len(np.unique(x))).reset_index()\\\n.rename(columns={'VisitNumber': 'fln_total_visits'})\ntfidf_fln = train_test.groupby(['VisitNumber','FinelineNumber']).agg({'ScanCount': 'sum', 'SCountPerVisit': 'first'})\\\n.reset_index()\\\n.assign(tf = lambda x: x.ScanCount / x.SCountPerVisit)\\\n.merge(fln_total_visits, how='left')\\\n.assign(idf = lambda x: np.log( total_visits/x.fln_total_visits ))\\\n.assign(fln_tfidf = lambda x: x.tf * x.idf)\\\n.groupby('VisitNumber').fln_tfidf.agg([np.sum, np.std]).reset_index()\\\n.fillna(0)\\\n.rename(columns={'sum': 'fln_tfidf_sum', 'std': 'fln_tfidf_std'})\ntfidf_fln.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:13.988044Z","iopub.execute_input":"2022-07-24T04:47:13.988571Z","iopub.status.idle":"2022-07-24T04:47:15.063592Z","shell.execute_reply.started":"2022-07-24T04:47:13.988532Z","shell.execute_reply":"2022-07-24T04:47:15.062467Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate tf-idf for DepartmentDescription\ndept_total_visits = train_test.groupby('DepartmentDescription').VisitNumber.apply(lambda x: len(np.unique(x))).reset_index()\\\n.rename(columns={'VisitNumber': 'dept_total_visits'})\ntfidf_dept = train_test.groupby(['VisitNumber','DepartmentDescription']).agg({'ScanCount': 'sum', 'SCountPerVisit': 'first'})\\\n.reset_index()\\\n.assign(tf = lambda x: x.ScanCount / x.SCountPerVisit)\\\n.merge(dept_total_visits, how='left')\\\n.assign(idf = lambda x: np.log( total_visits/x.dept_total_visits ))\\\n.assign(dept_tfidf = lambda x: x.tf * x.idf)\\\n.groupby('VisitNumber').dept_tfidf.agg([np.sum, np.std]).reset_index()\\\n.fillna(0)\\\n.rename(columns={'sum': 'dept_tfidf_sum', 'std': 'dept_tfidf_std'})\ntfidf_dept.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:17.543652Z","iopub.execute_input":"2022-07-24T04:47:17.544060Z","iopub.status.idle":"2022-07-24T04:47:18.470152Z","shell.execute_reply.started":"2022-07-24T04:47:17.544026Z","shell.execute_reply":"2022-07-24T04:47:18.469388Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Merge into one data frame","metadata":{}},{"cell_type":"code","source":"tr_base = train_test.groupby('VisitNumber').agg({'time_of_day': 'first',\n                                                 'TripType' : 'first',\n                                                 'SCountPerVisit': 'first',\n                                                 'day_counter': 'first',\n                                                 'wd': 'first',\n                                                 'Pos': 'sum',\n                                                 'Neg': 'sum',\n                                                 'Return': 'sum'}).reset_index()\ntr_base = pd.merge(tr_base, tr_dept_ent, how='left')\ntr_base = pd.merge(tr_base, tr_fln_ent, how='left')\ntr_base = pd.merge(tr_base, tr_upc_ent, how='left')\ntr_base = pd.merge(tr_base, tr_fac_ent, how='left')\ntr_base = pd.merge(tr_base, tr_uni_dept, how='left')\ntr_base = pd.merge(tr_base, tr_uni_fln, how='left')\ntr_base = pd.merge(tr_base, tr_uni_upc, how='left')\ntr_base = pd.merge(tr_base, tr_uni_fac, how='left')\ntr_base = pd.merge(tr_base, tr_dept_dummy.sparse.to_dense(), how='left')\ntr_base = pd.merge(tr_base, tr_pop_upc_dummy.sparse.to_dense(), how='left')\n#tr_base = pd.merge(tr_base, tr_pop_fac_dummy.sparse.to_dense(), how='left')\n#tr_base = pd.merge(tr_base, tr_pop_fln_dummy.sparse.to_dense(), how='left')\ntr_base = pd.merge(tr_base, tr_weekday_dummy.sparse.to_dense(), how='left')\ntr_base = pd.merge(tr_base, tr_monthday_dummy.sparse.to_dense(), how='left')\ntr_base = pd.merge(tr_base, visitsPerDay, how='left')\ntr_base = pd.merge(tr_base, sCountPerDay, how='left')\ntr_base = pd.merge(tr_base, tfidf_upc)\ntr_base = pd.merge(tr_base, tfidf_fac_upc)\ntr_base = pd.merge(tr_base, tfidf_fln)\ntr_base = pd.merge(tr_base, tfidf_dept)#.fillna(0).astype('Sparse[int64, 0]')\ntr_base.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:22.790860Z","iopub.execute_input":"2022-07-24T04:47:22.791283Z","iopub.status.idle":"2022-07-24T04:47:24.745955Z","shell.execute_reply.started":"2022-07-24T04:47:22.791248Z","shell.execute_reply":"2022-07-24T04:47:24.744726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 2. Prepare and run the Xgboost model","metadata":{}},{"cell_type":"code","source":"# Helper function to create sparse pivot table\ndef to_sparse_pivot(fln, idd, item, count):\n    visit_u = list(fln[idd].unique())\n    fln_u = list(np.sort(fln[item].unique()))\n    data = fln[count].tolist()\n    visit_type = CategoricalDtype(categories=visit_u, ordered=True)\n    row = fln[idd].astype(visit_type).cat.codes\n    fln_type = CategoricalDtype(categories=fln_u, ordered=True)\n    col = fln[item].astype(fln_type).cat.codes\n    return csr_matrix((data, (row, col)), shape=(len(visit_u), len(fln_u)))","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:29.317835Z","iopub.execute_input":"2022-07-24T04:47:29.318568Z","iopub.status.idle":"2022-07-24T04:47:29.327002Z","shell.execute_reply.started":"2022-07-24T04:47:29.318531Z","shell.execute_reply":"2022-07-24T04:47:29.325980Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Seperate feature and labels\ntr_df = tr_base.query('TripType != 0')\nte_df = tr_base.query('TripType == 0')\nte_df = te_df.drop('TripType', axis=1)\ntr_df_labels = tr_df.TripType\ntr_df = tr_df.drop('TripType', axis=1)\nfeat = pd.concat([tr_df,te_df], ignore_index=True)\nfeat.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:31.701700Z","iopub.execute_input":"2022-07-24T04:47:31.702102Z","iopub.status.idle":"2022-07-24T04:47:32.198583Z","shell.execute_reply.started":"2022-07-24T04:47:31.702070Z","shell.execute_reply":"2022-07-24T04:47:32.197697Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tr_agg = pd.concat([train_test.query('VisitNumber == @tr_VisitNumber'), \n                    train_test.query('VisitNumber != @tr_VisitNumber')], \n                   ignore_index=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:35.067368Z","iopub.execute_input":"2022-07-24T04:47:35.067853Z","iopub.status.idle":"2022-07-24T04:47:35.472556Z","shell.execute_reply.started":"2022-07-24T04:47:35.067815Z","shell.execute_reply":"2022-07-24T04:47:35.471296Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fln_sparse = to_sparse_pivot(tr_agg[['VisitNumber', 'ScanCount', 'FinelineNumber']], \n                             'VisitNumber', 'FinelineNumber', 'ScanCount')\nfac_sparse = to_sparse_pivot(tr_agg[['VisitNumber', 'ScanCount', 'Fac_Upc']], \n                             'VisitNumber', 'Fac_Upc', 'ScanCount')\ndept_fac_uni = tr_agg.groupby(['VisitNumber', 'DepartmentDescription'])\\\n                .Fac_Upc.agg(lambda x: len(np.unique(x))).reset_index()\ndept_fac_uni_sparse = to_sparse_pivot(dept_fac_uni, 'VisitNumber', 'DepartmentDescription', 'Fac_Upc')\ndept_fln_uni = tr_agg.groupby(['VisitNumber', 'DepartmentDescription'])\\\n                .FinelineNumber.agg(lambda x: len(np.unique(x))).reset_index()\ndept_fln_uni_sparse = to_sparse_pivot(dept_fln_uni, 'VisitNumber', 'DepartmentDescription', 'FinelineNumber')\ndept_upc_uni = tr_agg.groupby(['VisitNumber', 'DepartmentDescription'])\\\n                .Upc.agg(lambda x: len(np.unique(x))).reset_index()\ndept_upc_uni_sparse = to_sparse_pivot(dept_upc_uni, 'VisitNumber', 'DepartmentDescription', 'Upc')","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:47:38.221611Z","iopub.execute_input":"2022-07-24T04:47:38.222039Z","iopub.status.idle":"2022-07-24T04:48:24.420007Z","shell.execute_reply.started":"2022-07-24T04:47:38.222007Z","shell.execute_reply":"2022-07-24T04:48:24.419043Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Construct feature matrix\nfeat_mtx= hstack([csr_matrix(feat.values), fln_sparse, fac_sparse, dept_fac_uni_sparse, dept_fln_uni_sparse,\n                     dept_upc_uni_sparse])\nfeat_mtx.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:48:27.727710Z","iopub.execute_input":"2022-07-24T04:48:27.728459Z","iopub.status.idle":"2022-07-24T04:48:29.103592Z","shell.execute_reply.started":"2022-07-24T04:48:27.728394Z","shell.execute_reply":"2022-07-24T04:48:29.102479Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Sepearte train and test matrix\ntr_df_features = feat_mtx.tocsr()[:len(tr_df),:]\nte_df_features = feat_mtx.tocsr()[len(tr_df):,:]","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:48:32.345873Z","iopub.execute_input":"2022-07-24T04:48:32.346286Z","iopub.status.idle":"2022-07-24T04:48:32.823503Z","shell.execute_reply.started":"2022-07-24T04:48:32.346250Z","shell.execute_reply":"2022-07-24T04:48:32.822292Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Prepare train labels\nle = preprocessing.LabelEncoder()\ntrain_labels = le.fit_transform(tr_df_labels)","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:48:35.583463Z","iopub.execute_input":"2022-07-24T04:48:35.583852Z","iopub.status.idle":"2022-07-24T04:48:35.594922Z","shell.execute_reply.started":"2022-07-24T04:48:35.583820Z","shell.execute_reply":"2022-07-24T04:48:35.593152Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Split into train and validation set\nsss = StratifiedShuffleSplit(1, test_size=0.1, random_state=0)\nfor train_id, val_id in sss.split(tr_df_features,train_labels):\n    xgtrain = xgb.DMatrix(tr_df_features[train_id], label=train_labels[train_id])\n    xgval = xgb.DMatrix(tr_df_features[val_id ], label=train_labels[val_id])","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:48:38.395833Z","iopub.execute_input":"2022-07-24T04:48:38.397172Z","iopub.status.idle":"2022-07-24T04:48:38.625104Z","shell.execute_reply.started":"2022-07-24T04:48:38.397114Z","shell.execute_reply":"2022-07-24T04:48:38.622210Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Set parameters\nparam = {'objective': 'multi:softprob', \n         'num_class': 38, \n         'eta': 0.0795628155661, \n         \"eval_metric\": \"mlogloss\",\n         'subsample': 0.929631734622,\n         'colsample_bytree': 0.538628701606,\n         'gamma' : 0,\n         'min_child_weight': 4,\n         'max_depth': 10,\n         'max_delta_step': 3,\n         'nthread': 8}","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:48:41.037277Z","iopub.execute_input":"2022-07-24T04:48:41.037733Z","iopub.status.idle":"2022-07-24T04:48:41.044325Z","shell.execute_reply.started":"2022-07-24T04:48:41.037702Z","shell.execute_reply":"2022-07-24T04:48:41.043232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Run the model\nwatchlist = [(xgtrain, 'train'), (xgval, 'val')]\nnum_rounds = 2000\nmodel = xgb.train(param, xgtrain, num_rounds, watchlist, early_stopping_rounds=50)","metadata":{"execution":{"iopub.status.busy":"2022-07-24T04:48:43.732119Z","iopub.execute_input":"2022-07-24T04:48:43.732546Z","iopub.status.idle":"2022-07-24T05:37:14.465601Z","shell.execute_reply.started":"2022-07-24T04:48:43.732513Z","shell.execute_reply":"2022-07-24T05:37:14.464607Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Make prediction for the test set\npred = model.predict(xgb.DMatrix(te_df_features), iteration_range = (model.best_iteration-1 ,model.best_iteration))","metadata":{"execution":{"iopub.status.busy":"2022-07-24T05:41:14.807547Z","iopub.execute_input":"2022-07-24T05:41:14.808092Z","iopub.status.idle":"2022-07-24T05:41:15.041514Z","shell.execute_reply.started":"2022-07-24T05:41:14.808050Z","shell.execute_reply":"2022-07-24T05:41:15.040039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create and save submission file\nresult = pd.DataFrame(pred)\nsamsub = pd.read_csv('/kaggle/input/sample-submission/sample_submission.csv')\nresult.columns = samsub.columns.values[1:]\nresult['VisitNumber'] = te_df['VisitNumber']\nresult = result[samsub.columns.values]\nsubmission = pd.merge(pd.DataFrame(samsub.VisitNumber), result, how='left')\nsubmission.fillna(value=0, inplace=True)\nresult_visitnumber_list = list(result.VisitNumber)\nsubmission.loc[~submission.VisitNumber.isin(result_visitnumber_list), 'TripType_999'] = 1\nsubmission.to_csv(f'submission_{datetime.datetime.now().strftime(\"%d%m%Y_%H%M%S\")}.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-24T05:41:17.463379Z","iopub.execute_input":"2022-07-24T05:41:17.464561Z","iopub.status.idle":"2022-07-24T05:41:21.651090Z","shell.execute_reply.started":"2022-07-24T05:41:17.464500Z","shell.execute_reply":"2022-07-24T05:41:21.649831Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}