{"cells":[{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"# ELO Merchant Category Recommendation\n\nThe goal of this kernel is to get the data in a suitable form for analysis. This is the first kernel in a series which will follow the typical steps in any data science project:\n- get the data (data wrangling)\n- clean the data (data cleaning)\n- explore the data and engineer features (exploratory data analysis)\n- model the data (data modeling)\n- interpret the results and recommend actions\n\nThis is a flexible framework I recommend everyone to follow. In this kernel, we will focus on the first two steps of this analysis."},{"metadata":{"trusted":true,"_uuid":"ffbf409ffde8f3ee4174926572d251c5472d9cf5"},"cell_type":"code","source":"# Import necessary libraries\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\n\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8b9eb5ec29642294bb5830683e5403f50a78265d"},"cell_type":"markdown","source":"## Data Wrangling\n\nThe first step is to get all our data in one place. We use Pandas' functionalities to load our data."},{"metadata":{"trusted":true,"_uuid":"06426be3e9b81cf1af2f901c9f105abf87b51ad4"},"cell_type":"code","source":"# Load the data\ndata_dict = pd.read_excel('../input/Data_Dictionary.xlsx')\nhistorical_transactions = pd.read_csv('../input/historical_transactions.csv')\nmerchants = pd.read_csv('../input/merchants.csv')\nnew_transactions = pd.read_csv('../input/new_merchant_transactions.csv')\ntrain_data = pd.read_csv('../input/train.csv')\ntest_data = pd.read_csv('../input/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"04abdf5605b2a5834f19f3e6b268b9b36d7506dc"},"cell_type":"markdown","source":"Then we can examine the first few rows of each dataset in turn to check we have everything in the right format."},{"metadata":{"trusted":true,"_uuid":"626944a3f9d40c9a175d819be9bb555a374624ac"},"cell_type":"code","source":"print(data_dict)\n\n# The Excel documents actually has several sheets, which we load in turn and append\nexcel_doc = pd.ExcelFile('../input/Data_Dictionary.xlsx')\ndata_dict_train  = pd.read_excel(excel_doc, 'train', skiprows=2)\ndata_dict_historical = pd.read_excel(excel_doc, 'history', skiprows=2)\ndata_dict_new_merchants = pd.read_excel(excel_doc, 'new_merchant_period', skiprows=2)\ndata_dict_merchant = pd.read_excel(excel_doc, 'merchant', skiprows=2)\n\ndata_dict = data_dict_train.append(data_dict_historical) \\\n                            .append(data_dict_new_merchants).append(data_dict_merchant)\n\nprint(data_dict)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c6e37fc511c10923eafd145e5440ed79355f8ae0"},"cell_type":"code","source":"historical_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3f9d9bfb013f945023ee517619742c852bb402d0"},"cell_type":"code","source":"merchants.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"586967d05793da8f9adbefbffa139e4330585420"},"cell_type":"code","source":"new_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4ece4f67fd6c5a805c2aba6eacb376b7da4c2855"},"cell_type":"code","source":"train_data.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d3f6f479500641e838841981f2c918b2d5ed2f56"},"cell_type":"markdown","source":"All of the individual datasets have been loaded in their respective dataframes. Let's clean each in turn, before engineering features a way that makes sense for analysis."},{"metadata":{"_uuid":"7844bd33a976fdc9c99d10e3aedba92b11872684"},"cell_type":"markdown","source":"## Data Cleaning"},{"metadata":{"_uuid":"79a99be9beec5cef0bce77e1adb5f64295b7e48b"},"cell_type":"markdown","source":"We'll go through the dataframes in turn and check for classical examples of dirty data, such as missing values or duplicate rows."},{"metadata":{"_uuid":"849e08b1fc45f8ddd26cf9072ae740beda961342"},"cell_type":"markdown","source":"### Data Dictionary"},{"metadata":{"trusted":true,"_uuid":"fb2bb077340996da3fa18176590bbaba2f501a92"},"cell_type":"code","source":"# Check for null values\ndata_dict.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5c31a2a28fb8d46edd7d38221764e66462c58a09"},"cell_type":"code","source":"# Check for duplicates\ndata_dict.duplicated().sum()\n\n# Remove duplicates\ndata_dict.drop_duplicates(inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"29682899ae507a8f8f3ab7a1fc1af2e01525fd60"},"cell_type":"markdown","source":"We remove duplicates which arose from the appending of individual data dictionaries.\n\n### Historical Transactions"},{"metadata":{"trusted":true,"_uuid":"65516114bb9d61c88560a1e20010ec277e73a234"},"cell_type":"code","source":"# Check for null values\nhistorical_transactions.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8943c151ab07ed346aeda6b6c1f585038b026dd"},"cell_type":"code","source":"# Let's examine what categories 2 and 3 represent, from the data dictionary\ndata_dict.loc[(data_dict.Columns == 'category_3') | (data_dict.Columns == 'category_2'),]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"01e1b65b13e2e7796bea2bde9a569e861bca0411"},"cell_type":"code","source":"# Let's examine a few rows with missing merchant ID's\nhistorical_transactions.loc[historical_transactions.merchant_id.isnull(),].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"394e5503111f54ab7a002b82d8e3e3f786ae6f3a"},"cell_type":"code","source":"# Let's drop rows with missing merchant ID's, but keep those with missing categories 2 and 3\nhistorical_transactions.dropna(subset=['merchant_id'], axis=0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ded3b0c4960af1549fc1d668666dec161ca03178"},"cell_type":"code","source":"# Check for duplicates\nhistorical_transactions.duplicated().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"697b540cf9b703dda9ae8b79b39a6c568fe5a3f5"},"cell_type":"code","source":"# Visualize possible values for categories 1, 2 and 3 to check for invalid data\nprint(historical_transactions.category_1.unique(),\n      historical_transactions.category_2.unique(),\n      historical_transactions.category_3.unique())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"891c8afc636fae8f45eb416058486d17e0df75fb"},"cell_type":"code","source":"# Definition of purchase amount\nprint(data_dict.loc[data_dict.Columns == 'purchase_amount',])\n\n# Boxplot\nplt.boxplot(historical_transactions.purchase_amount)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5308b3befd20b9b0040e0f054e2cca48a051952f"},"cell_type":"code","source":"# Remove 1st outlier at 600,000 (probably was not normalized), and visualize again\nhistorical_transactions = historical_transactions.loc[historical_transactions.purchase_amount < 500000,]\nplt.boxplot(historical_transactions.purchase_amount)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8946f477000e356cd7e84945314613c04f639511"},"cell_type":"code","source":"# Remove more outliers, greater than 2\nhistorical_transactions = historical_transactions.loc[historical_transactions.purchase_amount < 2,]\nplt.boxplot(historical_transactions.purchase_amount)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aa4ec67ac50121c3e80f9165364fbe3f30e822bf"},"cell_type":"code","source":"# Boxplot for month lag\nplt.boxplot(historical_transactions.month_lag)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e9d46bc900f1cf2a36a6984a1ec7cfe0f1c3df43"},"cell_type":"code","source":"# Boxplot for installments\nplt.boxplot(historical_transactions.installments)\nplt.show();\n\n# Remove the outlier at 1000 installments\nhistorical_transactions = historical_transactions.loc[historical_transactions.installments < 900,]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9db7eba0c591992c32f38169666c3b9fd630369a"},"cell_type":"markdown","source":"Looking at the missing values, we decide to drop the rows with missing merchant ID's, because it will be impossible for us to match this back to individual merchant information, and hence to compute a loyalty score in later steps. There are missing values in the category_2 and category_3, but we leave them as is, and will create a dummy variable for this later: it is indeed possible for a merchant to only be in one category.\n\nThere are no duplicates, but several rows with outlier values, in particular in the normalized purchase amount columns. My interpretation for the latter is that the amount was not normalized, but lacking the original mean and standard deviation we cannot make the transformation ourselves and hence choose to simply drop these rows."},{"metadata":{"_uuid":"5b05eb735f4d0f11af0ce7e99c9d817b78a30096"},"cell_type":"markdown","source":"### Merchants"},{"metadata":{"trusted":true,"_uuid":"9dbe1569fff8603d77733c9fafc2bc68e50077f6"},"cell_type":"code","source":"# Examine null values\nmerchants.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e9fdc59575fa0f863d5e554d1e169f398a92d402"},"cell_type":"code","source":"# Define the lagged sales\nprint(data_dict.loc[data_dict.Columns == 'avg_sales_lag3','Description'])\n\n# Drop rows with missing values of lagged sales\nmerchants.dropna(subset=['avg_sales_lag3', 'avg_sales_lag6', 'avg_sales_lag12'], axis=0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cfaf966c1cce7087725a23766715e4c522a41075"},"cell_type":"code","source":"# Create boxplots for numerical columns\nmerchants.boxplot()\nplt.xticks(rotation='vertical')\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8356087a47c68a416aaf95583cf9cc7f3bc1f30f"},"cell_type":"markdown","source":"We keep the rows with missing values in the category_2 column (for a similar reason as the previous dataset) but drop missing values of lagged sales (only 13 rows so it will not make a huge difference). Looking at boxplots for numerical columns we can spot a lot of outliers, but they seem more likely to be valid data than for the previous dataset. Hence we keep these rows for future analysis."},{"metadata":{"_uuid":"d07ea25f48874a252fda7ea3f13e2f5ad87b930d"},"cell_type":"markdown","source":"### New Transactions"},{"metadata":{"trusted":true,"_uuid":"a293012c1111c99c1fa78d3f8e2668be6475f9fd"},"cell_type":"code","source":"# Check for missing values\nnew_transactions.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b4c54c6306d0a314f48cb478e1e118adf4027702"},"cell_type":"code","source":"# Similar to the historical transactions, we drop missing merchant ID's\nnew_transactions.dropna(subset = ['merchant_id'], axis=0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a27bbb9312724fd8b854646d382da69620926310"},"cell_type":"code","source":"# Check for duplicates\nnew_transactions.duplicated().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc9cf85407959f47680954e1ecb503565d7cd969"},"cell_type":"code","source":"# Check for outliers\nnew_transactions.boxplot()\nplt.xticks(rotation='vertical')\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0bc1a65cda37615af53b7b67412b2f9f4dc5ba9c"},"cell_type":"code","source":"# Similarly to historical transactions, we remove outliers for the purchase amount and installments\nnew_transactions = new_transactions.loc[(new_transactions.purchase_amount < 2) & (new_transactions.installments < 900),]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bb989ad5b33cdaec5ce526c70dd70bd6b95e322d"},"cell_type":"markdown","source":"We conduct a very similar data cleaning to historical transactions, removing outliers and missing merchant ID's."},{"metadata":{"_uuid":"db6cc98097cef96f263f00972442e017add3b0f8"},"cell_type":"markdown","source":"### Train"},{"metadata":{"trusted":true,"_uuid":"6cc5e388c9c85088de563d748174fcdc5d71731c"},"cell_type":"code","source":"# Check for null values\ntrain_data.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d5ddc0364dbbbefef6b9bc9ee4331c0a786c13c2"},"cell_type":"code","source":"# Check for duplicates\ntrain_data.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aca0ea074d204e8efe82be944ec506b12f4e46de"},"cell_type":"code","source":"# Visualize the range of values for the target\nplt.boxplot(train_data.target)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fce8903941dcb05ab6028d463d154c4947e4bbf0"},"cell_type":"markdown","source":"Let's save the data we have cleaned."},{"metadata":{"trusted":true,"_uuid":"56759b1a2829354d4ea08c45856c1f9e035af851"},"cell_type":"code","source":"historical_transactions.to_csv('historical_transactions_clean.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"de0fe22db6dfb39778a057cd572beb9395d4038a"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}