{"cells":[{"metadata":{"_uuid":"25610ebacee15901a6f3c6e04e8d37245fde72b0"},"cell_type":"markdown","source":"# Customer Loyalty Prediction 1: Data Cleaning and EDA\n\n[Elo](https://elo.com.br/) is a Brazillian debit and credit card brand.  They offer credit and prepaid transactions, and have paired up with merchants in order offer promotions to cardholders.  In order to offer more relevant and personalized promotions, in a [recent Kaggle competition](https://www.kaggle.com/c/elo-merchant-category-recommendation), Elo challenged Kagglers to predict customer loyalty based on transaction history.  Presumably they plan to use a loyalty-predicting model in order to determine what promotions to offer to customers based on how certain offers are predicted to effect card owners' card loyalty.\n\nIn this post, we'll load, clean, and explore the raw data from the [Elo Merchant Category Recommendation](https://www.kaggle.com/c/elo-merchant-category-recommendation) challenge.  The name of the challenge is a bit misleading, seeing as the immediate goal is to predict customer loyalty - not to recommend merchants to card owners.  In future posts, we'll work on [feature engineering, feature selection](http://www.kaggle.com/brendanhasz/elo-feature-engineering-and-feature-selection), [modeling](https://www.kaggle.com/brendanhasz/elo-modeling), and understanding our model's predictions.\n\n**Outline**\n\n- [Data Loading and Cleaning](#data-loading-and-cleaning)\n  - [Cards Data](#cards-data)\n  - [Mismatch between Merchants and Transactions Data](#mismatch-between-merchants-and-transactions-data)\n  - [Merchants Data](#merchants-data)\n  - [Transactions Data](#transactions-data)\n- [Exploratory Data Analysis](#exploratory-data-analysis)\n  - [Cards](#cards)\n  - [Merchants](#merchants)\n  - [Transactions](#transactions)\n- [Conclusion](#conclusion)\n\nLet's first load the packages we'll use:"},{"metadata":{"trusted":true,"_uuid":"efdac6e476cd0ee6d0a9c895dc0796afd191cbe5"},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nfrom scipy.stats import spearmanr\nimport matplotlib.pyplot as plt\nfrom matplotlib.ticker import FormatStrFormatter\nimport seaborn as sns\nimport gc\n\n# Plot settings\n%matplotlib inline\n%config InlineBackend.figure_format = 'svg'\nsns.set()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"86367d00b61c5324c017d15cf18cbcb993a70639"},"cell_type":"code","source":"!pip install git+http://github.com/brendanhasz/dsutils.git","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"44d40dc4e5d3000ffc7aed2aeb20183824a52a8b"},"cell_type":"code","source":"from dsutils.printing import print_table, describe_df\nfrom dsutils.eda import countplot2d","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"72ba0aeb6b355a7296005dfa409d2f7723607b6f"},"cell_type":"markdown","source":"<a id='data-loading-and-cleaning'></a>\n## Data Loading and Cleaning\n\nThere are 5 different data tables in the Elo dataset:\n\n* `train.csv`\n* `test.csv`\n* `merchants.csv`\n* `historical_transactions.csv`\n* `new_merchant_transactions.csv`\n\nThe first file (`train.csv`) contains information about the credit card accounts and the loyalty score for each.  `test.csv` contains the same information, but about different accounts, and it does not contain loyalty scores - because those are the accounts for which we are trying to predict the loyalty!  `merchants.csv` has information about the merchants where the credit cards are being used to make transactions.  Finally, the `historical_transactions.csv` and `new_merchant_transactions.csv` files contain lists of all the individual transactions which were made between the credit cards and the merchants, and some features of those transactions (time and date, purchase amount, etc).\n\nEventually, we'll want to build a predictive model, train it on the cards data in `train.csv` and the information in the merchants and transactions datasets associated with those cards, and then predict the loyalty scores for the cards in `test.csv` (using the data in the merchants and transactions datasets associated with cards in `test.csv`).  But first, we need to load and clean the data!"},{"metadata":{"_uuid":"0a7574474775b85ee88943d6870560a3548cbaf5"},"cell_type":"markdown","source":"<a id='cards-data'></a>\n### Cards Data\n\nLet's start by taking a look at the cards dataset, which contains information about the credit card accounts for which we'll be predicting the loyalty score.  There are two files (`test.csv` and `train.csv`) which contain information about the cards - the only difference is that `train.csv` has the loyalty score for each card, while `test.csv` doesn't (because we are going to try to predict the loyalty score for those accounts).  Let's load both the files:"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# Load card data\ndtypes = {\n  'card_id':            'str',\n  'target':             'float32',\n  'first_active_month': 'str',\n  'feature_1':          'uint8',\n  'feature_2':          'uint8',\n  'feature_3':          'uint8',\n}\ntrain = pd.read_csv('../input/train.csv',\n                    usecols=dtypes.keys(),\n                    dtype=dtypes)\ndel dtypes['target']\ntest = pd.read_csv('../input/test.csv',\n                   usecols=dtypes.keys(),\n                   dtype=dtypes)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7f08a91038f7bb72bc76a08ba42c3e2aa8109824"},"cell_type":"markdown","source":"There are no nulls anywhere in `train.csv`:"},{"metadata":{"trusted":true,"_uuid":"43935a4fae050935ff51ec26501baef407a67a6b"},"cell_type":"code","source":"describe_df(train)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2dd0744fb9d12d82122241f58c8b125bd06c08da"},"cell_type":"markdown","source":"But, there is a *single row* in `test.csv` which has a null value in the `first_active_month` column:"},{"metadata":{"trusted":true,"_uuid":"efdef6f53b5f912184b15d91089bb2a49adc2278"},"cell_type":"code","source":"describe_df(test)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"41f7499a6648c5ffc1ba7703fb026ccce1011d0d"},"cell_type":"markdown","source":"To ensure we process the test and training data identically, we'll merge them into a single dataframe called `cards`.  Since the test dataset has no `target` values (the loyaly score we are trying to predict), we'll set the target to NaN for the test data."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"# Add target col to test\ntest['target'] = np.nan\n\n# Merge test and train\ncards = pd.concat([train, test])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"377642ec48267f18e34f9e6aa536e1edd6ca53b4"},"cell_type":"markdown","source":"As a quick sanity check, let's make sure there aren't any `card_id`s which are in both test and train:"},{"metadata":{"trusted":true,"_uuid":"2d8933a1f0e3b21f19f3b31be34a71d8f6d7badd"},"cell_type":"code","source":"print('Num unique in train:  ', test['card_id'].nunique())\nprint('Num unique in test:   ', train['card_id'].nunique())\nprint('The sum:              ', test['card_id'].nunique()+train['card_id'].nunique())\nprint('Num unique in merged: ', cards['card_id'].nunique())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cd4669663cb2238fbd84c52227042aa0495e84fa"},"cell_type":"markdown","source":"OK good, there aren't. Now we can delete the original dataframes."},{"metadata":{"trusted":true,"_uuid":"bd4be7e49793d6310786e43787be8310b82ebe8a"},"cell_type":"code","source":"del train, test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"23a8972147cd66feb3e07acc68c1122dde13516a"},"cell_type":"markdown","source":"Let's take a look at the cards data."},{"metadata":{"trusted":true,"_uuid":"cd400c1150621f201f72658972a25c3fb9589cf0"},"cell_type":"code","source":"cards.sample(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6944634535e62fe283a2d5ab3542cac1d18d843b"},"cell_type":"markdown","source":"The `card_id`s always start with `C_ID_`. That's kind of a waste of space... All the `card_id`s are the same length:"},{"metadata":{"trusted":true,"_uuid":"38a02e594488a5593e0548657fffa0c5c1f45f3a"},"cell_type":"code","source":"cards['card_id'].apply(len).unique()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a3920908560d3fc74153fe76a0d20c09fe61b09f"},"cell_type":"markdown","source":"If we cut off the prefix, the remaining strings appear to be all hexidecimal (represented by values 0-9 and a-f):"},{"metadata":{"trusted":true,"_uuid":"4084f625998832e7fd192a89e406972f6dda5d25"},"cell_type":"code","source":"cards['card_id'].str.slice(5, 15).sample(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f1abf76ec2878defc25923c8d60d051f388c72d9"},"cell_type":"markdown","source":"But let's check that this is the case for every entry:"},{"metadata":{"trusted":true,"_uuid":"3d4204862407f44132f269040de0cb6ebc7080c4"},"cell_type":"code","source":"(cards['card_id']\n .str.slice(5, 15)\n .apply(lambda x: all(e in '0123456789abcdef' for e in x))\n .all())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d7cd4c2163c3f72a2c952ab84cc8e8c5bcdfa38c"},"cell_type":"markdown","source":"Indeed all the `card_id`s are hexidecimal.  To save space, we could convert the `card_id`s to integers like this:"},{"metadata":{"trusted":true,"_uuid":"f0ffd1331210e9397e4f2cf8dd615e53e772bd38"},"cell_type":"code","source":"#cards['card_id'] = cards['card_id'].apply(lambda x: int(x, 16))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"41fc3691cff7e8fead066a9f938863b693595c5a"},"cell_type":"markdown","source":"There are 5 bytes worth of hex info in the card ids (each hexideximal digit represents 4 bits, so the 10 hexideximal digits in the card ids represent 40 bits = 5 bytes of information).  Inconveniently, the values span the full 5-byte range - so, we'd have to use a 64-bit integer to represent them (which is 8 bytes, instead of a 32-bit int, which is only 4 bytes).  However, that would be a waste of space, seeing as there are only 325,540 unique card_ids, and could easily be represented in 4 bytes by a uint32 (which stores values up to ~4 billion). The inneficiency of using a 64-bit representation for something where a 32-bit reprentation would do... Bothers me. *eye twitches*\n\nTo use a 32-bit integer, we'll create a map between the card_id and a unique integer which identifies it, and then map the string values to integer values."},{"metadata":{"trusted":true,"_uuid":"dbf013da67e1d41cd5455c40f09bfc053410a4c3"},"cell_type":"code","source":"# Create a map from card_id to unique int\ncard_id_map = dict(zip(\n    cards['card_id'].values,\n    cards['card_id'].astype('category').cat.codes.values\n))\n\n# Map the values\ncards['card_id'] = cards['card_id'].map(card_id_map).astype('uint32')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0d2490b46e921340fe6f7a3c04102aa19f92af4f"},"cell_type":"markdown","source":"Now our `card_id`s are 32-bit integers:"},{"metadata":{"trusted":true,"_uuid":"d4acd24bbb48531abf4526e7f615cc933799731e"},"cell_type":"code","source":"cards.sample(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"35824824a885cf986abe50e70e0b01cd613fdd51"},"cell_type":"markdown","source":"Next, we'll convert the `first_active_month` from a string to a datetime."},{"metadata":{"trusted":true,"_uuid":"9df89b5b73bb28dea2c788b075ac450aecd92d2a"},"cell_type":"code","source":"# Convert first_active_month to datetime\ncards['first_active_month'] = pd.to_datetime(cards['first_active_month'],\n                                             format='%Y-%m')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"88833c2303d7cb94d2cacd690b19d5097b499f6a"},"cell_type":"markdown","source":"Finally, we'll set the index to be the `card_id`."},{"metadata":{"trusted":true,"_uuid":"e898718c70989be19b57d8f8b60cd86d1a278b7c"},"cell_type":"code","source":"# Make card_id the index\ncards.set_index('card_id', inplace=True)\ngc.collect()\ncards.sample(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a66a3a9cd7e4808d90d4573f095e3239e1753e0a"},"cell_type":"markdown","source":"<a id='mismatch-between-merchants-and-transactions-data'></a>\n### Mismatch between Merchants and Transactions Data\n\nBoth the transactions data (`historical_transactions.csv` and `new_merchant_transactions.csv`) and the merchants data (`merchants.csv`)  have the following columns:\n\n* `city_id`\n* `state_id`\n* `category_1`\n* `category_2`\n* `subsector_id`\n* `merchant_category_id`, and\n* `merchant_id`\n\nObviously we want them both to have `merchant_id` so that we can connect the tables, but it's unclear whether the other columns in the transactions table represent information about the *merchant* or the *transaction*.  It seems that `merchant_category_id` and `subsector_id` at least should definitely represent information about the merchant, and therefore should be the same between transactions involving the same `merchant_id`, and should also match the information in `merchants.csv`.  If `city_id` and `state_id` represent, say, the corporate mailing address of the merchant, then those should also remain constant.  On the other hand, if they represent the city and state where the transaction occurred, there may be many different values for the same `merchant_id`.  Since `category_1` and `category_2` are in both datasets, it seems likely that they also represent information about the merchant.\n\nHowever, let's verify whether that's true.  First let's load only the columns of interest:"},{"metadata":{"trusted":true,"_uuid":"58df1c662bafe4ca984476865bb1f42847896a09"},"cell_type":"code","source":"# Datatypes of each column\ndtypes = {\n    'city_id':              'int16', \n    'category_1':           'str',\n    'merchant_category_id': 'int16',\n    'merchant_id':          'str',\n    'category_2':           'float16',\n    'state_id':             'int8',\n    'subsector_id':         'int8'\n}\n\n# Load the data\nhist_trans = pd.read_csv('../input/historical_transactions.csv', \n                         usecols=dtypes.keys(),\n                         dtype=dtypes)\nnew_trans = pd.read_csv('../input/new_merchant_transactions.csv', \n                        usecols=dtypes.keys(),\n                        dtype=dtypes)\nmerchants = pd.read_csv('../input/merchants.csv', \n                        usecols=dtypes.keys(),\n                        dtype=dtypes)\n\n# Merge new_merchant and historical transactions\ntrans = pd.concat([hist_trans, new_trans])\ndel hist_trans\ndel new_trans\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"873c4d9e95b3ea1ff0885a527e76139ac111911d"},"cell_type":"markdown","source":"Then we can check that the values of `city_id`, `state_id`, `category_1`, `category_2`, `subsector_id`, and `merchant_category_id` match between transactions which have the same `merchant_id`."},{"metadata":{"trusted":true,"_uuid":"f10fb284e40211b1680f63d9799dc65e61a85304"},"cell_type":"code","source":"# For each column, count merchant_ids w/ >1 unique vals\ngbo = trans.groupby('merchant_id')\nnuniques = []\ncols = []\nfor col in trans:\n    if col == 'merchant_id': continue\n    nuniques.append((gbo[col].nunique() > 1).sum())\n    cols.append(col)\nprint_table(['Column', 'Number unique'], \n            [cols, nuniques])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2a7eaf3f3b2b764111e14e2f98054d91d779ec95"},"cell_type":"markdown","source":"Hmm, there's a bunch of mismatches.  A sizeable chunk of the merchants (there are only ~335,000) have features which change in the transactions data.  How many of the transactions have merchant-specific values which don't match the data in the merchants table?"},{"metadata":{"trusted":true,"_uuid":"fd4d3b35a6295e5857db6ab5bb61f785b09d964b"},"cell_type":"code","source":"# Join trans w/ merchants on merchant_id\n\n# Check that all feature_transactions==feature_merchants\ndf = trans.merge(merchants, how='outer', on='merchant_id', \n                 suffixes=('', '_merchants'))\ncols = []\nmismatches = []\nfor col in trans:\n    if 'merchant_id' in col: continue\n    sames = ((df[col] == df[col+'_merchants']) | \n             (df[col].isnull() & df[col+'_merchants'].isnull())).sum()\n    cols.append(col)\n    mismatches.append(df.shape[0]-sames)\n\n# Print the number of mismatches\nprint_table(['Column', 'Num mismatches'],\n            [cols, mismatches])\nprint('Total number of transactions: ', df.shape[0])\n\n# Clean up\ndel trans, merchants, df, sames\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fbf79aa3b29763f8dac0a56198f6c1ea6d1e21bd"},"cell_type":"markdown","source":"So, it appears that there is a pretty respectable mismatch between the merchant-specific values in the transactions data and in the merchants data.  This is probably because the merchant's properties may have *changed* in the time between the transaction and the time the merchants dataset was compiled.  Therefore, we'll use the values in the transactions table (and not the values in the merchants table) when creating features for our predictive model, because the values in the transactions table are more likely to reflect the merchant at the time when it was important - when the transaction occurred."},{"metadata":{"_uuid":"a808f67b0354fa478263ccb0eeee6a7f97489c2b"},"cell_type":"markdown","source":"<a id='merchants-data'></a>\n### Merchants Data\n\nAgain, the merchants dataset (in `merchants.csv`) contains information about the merchants which card owners are making transactions with.  Let's load the data, but because of the overlap with the transactions data just discussed, we won't load the columns which are in the transactions datasets."},{"metadata":{"trusted":true,"_uuid":"bd592670fcc2430424809ec3ea0eb7add5ab7552"},"cell_type":"code","source":"# Datatypes of each column\n# (don't load cols which are in transactions data)\ndtypes = {\n  'merchant_id':                 'str',\n  'merchant_group_id':           'uint32',\n  'numerical_1':                 'float32',\n  'numerical_2':                 'float32',\n  'most_recent_sales_range':     'str',\n  'most_recent_purchases_range': 'str',\n  'avg_sales_lag3':              'float32',\n  'avg_purchases_lag3':          'float32',\n  'active_months_lag3':          'uint8',\n  'avg_sales_lag6':              'float32',\n  'avg_purchases_lag6':          'float32',\n  'active_months_lag6':          'uint8',\n  'avg_sales_lag12':             'float32',\n  'avg_purchases_lag12':         'float32',\n  'active_months_lag12':         'uint8',\n  'category_4':                  'str',\n}\n\n# Load the data\nmerchants = pd.read_csv('../input/merchants.csv',\n                        usecols=dtypes.keys(),\n                        dtype=dtypes)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"81209d57f26caf95449a3d0d5124b0a48d2c0e09"},"cell_type":"markdown","source":"Let's take a look at the merchants data."},{"metadata":{"trusted":true,"_uuid":"2a079323e29a328061b95cf9d1dfe1f13043a0d2"},"cell_type":"code","source":"merchants.sample(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9434e6f0f9523b27ba848ca3b6fcce1987901242"},"cell_type":"markdown","source":"We'll map the `merchant_id`s to integers like we did for the `card_id`s.  We'll also transform some columns to be more manageable, and encode categorical columns to integers."},{"metadata":{"trusted":true,"_uuid":"d424c364bbd4e6a22064812281b7fc3d46054235"},"cell_type":"code","source":"# Map merchant_id to integer\nmerch_id_map = dict(zip(\n    merchants['merchant_id'].values,\n    merchants['merchant_id'].astype('category').cat.codes.values\n))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"254fab54cd1f52a75ee8d72dd9f25a77e8d3c6c4"},"cell_type":"code","source":"def preprocess_merch_data(df):\n    \n    # Convert merchant ID to numbers\n    df['merchant_id'] = df['merchant_id'].map(merch_id_map).astype('float32')\n\n    # Inverse transforms\n    inversions = [\n        'avg_sales_lag3',\n        'avg_sales_lag6',\n        'avg_sales_lag12',\n        'avg_purchases_lag3',\n        'avg_purchases_lag6',\n        'avg_purchases_lag12',\n    ]\n    for col in inversions:\n        df[col] = 1.0/df[col]\n\n    # Encode categorical columns\n    bool_map = {'Y': 1, 'N': 0}\n    five_map = {'A': 0, 'B': 1, 'C': 2, 'D': 3, 'E': 4}\n    conversions = [\n        ('category_4', bool_map, 'uint8'),\n        ('most_recent_sales_range', five_map, 'uint8'),\n        ('most_recent_purchases_range', five_map, 'uint8')\n    ]\n    for col, mapper, new_type in conversions:\n        df[col] = df[col].map(mapper).astype(new_type)\n        \n    # Clean up\n    gc.collect()\n\n# Preprocess the merchants data\npreprocess_merch_data(merchants)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ac7a88f9100ed78b48a67e77a04253bb6c3ba00c"},"cell_type":"markdown","source":"There are no duplicate rows in the merchants dataset:"},{"metadata":{"trusted":true,"_uuid":"f8485ad735f07db6494b5e4cf9103b5bd6acef21"},"cell_type":"code","source":"print(\"Number of duplicate rows in merchants.csv: %d\" \n      % merchants.duplicated().sum())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ce5813dd5dc77b225f8313841f8b89a5c188caf7"},"cell_type":"markdown","source":"But, there are duplicate `merchant_id`s:"},{"metadata":{"trusted":true,"_uuid":"344cea971cbf7dd7c7f2ad8f2f420a29c903a1f2"},"cell_type":"code","source":"print(\"Number of duplicate merchant_ids: %d\" % \n      merchants['merchant_id'].duplicated().sum())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"52e7a5ec37a43761378f9cf8b7bcb5e212f3d40f"},"cell_type":"markdown","source":"Let's take a look at some of the duplicate entries to see which ones look like they should be removed."},{"metadata":{"trusted":true,"_uuid":"8617f81a360f2ed59dd98edc4f19fd8f270c5e0b"},"cell_type":"code","source":"# Show some of the duplicates\nduplicates = merchants['merchant_id'].duplicated(keep=False)\nmerchants[duplicates].sort_values('merchant_id').head(6)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0c302045193a16e1ac6a107917083d57c8b669cc"},"cell_type":"markdown","source":"Unfortunately it's not super clear which of the entries is invalid, outdated, or incomplete.  If one entry had empty values and another didn't then we could just remove the entry with empty values.  As it is, we'll just remove all duplicate entries but the first."},{"metadata":{"trusted":true,"_uuid":"6f64a90d1d3f9920fb67c86ef6235b02835bff3a"},"cell_type":"code","source":"# Drop duplicate entries\nmerchants.drop_duplicates(subset='merchant_id',\n                          keep='first', inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"15aaf57d44547fdd580365c8f053c7c778716848"},"cell_type":"markdown","source":"<a id='transactions-data'></a>\n### Transactions Data\n\nThe transactions datasets contain information about transactions which card owners have made with the merchants in the merchants dataset.  `historical_transactions.csv` contains historical transactions (transactions since the card account was opened), while `new_merchant_transactions.csv` contains information about transactions during the period before the loyalty score was calculated.\n\nIn order to make predictions about the loyalty of card owners, we'll have to use their transaction histories to come up with informative features about each customer.  But, before we can do that, we have to load and clean the data!  \n\nSince there are many transactions per customer (or per merchant), the transactions datasets are much larger than the cards and merchants datasets.  We've been doing this the whole time, but this is really where it helps a lot - specifying the datatypes to use for each column when calling panda's `read_csv` really speeds up the loading of the data.  Let's load the historical and new transactions using this method."},{"metadata":{"trusted":true,"_uuid":"e91f87045313bdedd4cbd9af0e11a82647a48473"},"cell_type":"code","source":"# Datatypes of each column\ndtypes = {\n    'authorized_flag':      'str',\n    'card_id':              'str',\n    'city_id':              'int16',\n    'category_1':           'str',\n    'installments':         'int8',\n    'category_3':           'str',\n    'merchant_category_id': 'int16',\n    'merchant_id':          'str',\n    'month_lag':            'int8',\n    'purchase_amount':      'float32',\n    'purchase_date':        'str',\n    'category_2':           'float32',\n    'state_id':             'int8',\n    'subsector_id':         'int8',\n}\n\n# Load the data\nhist_trans = pd.read_csv('../input/historical_transactions.csv', \n                         usecols=dtypes.keys(),\n                         dtype=dtypes)\nnew_trans = pd.read_csv('../input/new_merchant_transactions.csv', \n                        usecols=dtypes.keys(),\n                        dtype=dtypes)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"48363df3f93d4ab6888710285021f82bf91d835a"},"cell_type":"markdown","source":"We also need to convert the `card_id` to an integer as before, `merchant_id` to an integer in the same way, convert the `purchase_date` column (which has been loaded as a string) to datetime format, and encode the categorical columns."},{"metadata":{"trusted":true,"_uuid":"ac1163369509c7baf8083c108265708bdb17b835"},"cell_type":"code","source":"def preprocess_trans_data(df):\n    \n    # Convert card_id and merchant_id to numbers\n    df['card_id'] = df['card_id'].map(card_id_map).astype('uint32')\n    df['merchant_id'] = df['merchant_id'].map(merch_id_map).astype('float32')\n\n    # Convert purchase_date to datetime\n    df['purchase_date'] = df['purchase_date'].str.slice(0, 19)\n    df['purchase_date'] = pd.to_datetime(df['purchase_date'],\n                                         format='%Y-%m-%d %H:%M:%S')\n\n    # Encode categorical columns\n    bool_map = {'Y': 1, 'N': 0}\n    three_map = {'A': 0, 'B': 1, 'C': 2}\n    conversions = [\n        ('authorized_flag', bool_map, 'uint8'),\n        ('category_1', bool_map, 'uint8'),\n        ('category_3', three_map, 'float32'), #has NaNs so have to use float\n    ]\n    for col, mapper, new_type in conversions:\n        df[col] = df[col].map(mapper).astype(new_type)\n        \n    # Clean up\n    gc.collect()\n\n# Preprocess the transactions data\npreprocess_trans_data(hist_trans)\npreprocess_trans_data(new_trans)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5b0096cfc474f616d38dc5896f0da210b7b64657"},"cell_type":"markdown","source":"However, there are a few funky (yet valid) values in the dataset.  Taking a quick view at the dataframe, there are a  few value which are odd:"},{"metadata":{"trusted":true,"_uuid":"88b453b5ce70acece642202cd66af829011217f4"},"cell_type":"code","source":"describe_df(hist_trans)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9450b8623909dbdcab4bc9b31c63d812cc60f1ab"},"cell_type":"markdown","source":"There are values of -1 in the `city_id`, `installments`, and `state_id` columns, while the vast majority of the features' ranges are non-negative.  Especially for `installments`: it makes no sense to have a negative number of payment installments.  Let's look at the distribution of these column individually.  Starting with `installments`:"},{"metadata":{"trusted":true,"_uuid":"91cd6a9b0847001ffb8d6f2c412bdf10b30fde51"},"cell_type":"code","source":"# Histogram of installments\nplt.hist(hist_trans['installments'],\n         bins=np.arange(-25.5, 12.5, 1.0))\nplt.axvline(x=0, color='k', linestyle='--')\nplt.ylabel('Count')\nplt.xlabel('installments')\nplt.yscale('log', nonposy='clip')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6e849d952093ca59597a0b9b937eed756a627e50"},"cell_type":"markdown","source":"Hmm, values of -1 and -25?  Those are probably to indicate missing values, so we'll set the installments with negative numbers to NaN:"},{"metadata":{"trusted":true,"_uuid":"b2fbd15f23efcc643dba6ae4c69966a09deab36a"},"cell_type":"code","source":"# Set negative installments to nan\nhist_trans.loc[hist_trans['installments']<0, 'installments'] = np.nan\nnew_trans.loc[new_trans['installments']<0, 'installments'] = np.nan","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fbf93ffe6fc8b316aca6b3c4f3ebce14f6c1dd2d"},"cell_type":"markdown","source":"There's a similar problem for `city_id` and `state_id`:"},{"metadata":{"trusted":true,"_uuid":"029a617bab5aa48a1606706445ad733143aa7dad"},"cell_type":"code","source":"# Histogram of city_id and state_id\nplt.subplot(121)\nplt.hist(hist_trans['city_id'],\n         bins=np.arange(-10.5, 12.5, 1.0))\nplt.axvline(x=0, color='k', linestyle='--')\nplt.ylabel('Count')\nplt.xlabel('city_id')\nplt.yscale('log', nonposy='clip')\n\nplt.subplot(122)\nplt.hist(hist_trans['state_id'],\n         bins=np.arange(-10.5, 12.5, 1.0))\nplt.axvline(x=0, color='k', linestyle='--')\nplt.ylabel('Count')\nplt.xlabel('state_id')\nplt.yscale('log', nonposy='clip')\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f43fa1375a7171368d411a33d3a44706b102435c"},"cell_type":"markdown","source":"We'll set those to NaN too, seeing as they likely indicate missing values."},{"metadata":{"trusted":true,"_uuid":"7202bdcd13e799acb170369e26e8c37675122f2c"},"cell_type":"code","source":"# Set negative ids to nan\nhist_trans.loc[hist_trans['city_id']<0, 'city_id'] = np.nan\nnew_trans.loc[new_trans['city_id']<0, 'city_id'] = np.nan\nhist_trans.loc[hist_trans['state_id']<0, 'state_id'] = np.nan\nnew_trans.loc[new_trans['state_id']<0, 'state_id'] = np.nan","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"40bc052ac170b40b332940bb9017c51d3f70ca55"},"cell_type":"markdown","source":"<a id='exploratory-data-analysis'></a>\n## Exploratory Data Analysis\n\nNow that we've loaded and cleaned the data, let's do some EDA!"},{"metadata":{"_uuid":"a03e674cbed3d29d273bdf20dff5f19d93335600"},"cell_type":"markdown","source":"<a id='cards'></a>\n### Cards\n\nThe cards dataset (split into `test.csv` and `train.csv`) contain information about the credit card accounts for which we're trying to predict the customer loyalty.  Pretty much all this dataset contains is the card ID, the loyalty score, the month the account was first active, and three anonymized categorical features."},{"metadata":{"trusted":true,"_uuid":"66567ce79d0f9c04ca13cb2a0eeb89b62202c505"},"cell_type":"code","source":"describe_df(cards)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e008da120a288506f9d7b87f9b8ad74f64522baa"},"cell_type":"markdown","source":"Let's start by taking a look at the distribution of loyalty scores, which is the target variable we'll be trying to predict when we build a predictive model."},{"metadata":{"trusted":true,"_uuid":"b64d4fc4c9a1b5ada2d7d45c889fdc39b69ecab9"},"cell_type":"code","source":"# Loyalty score (aka the target)\ncards['target'].hist(bins=50)\nplt.xlabel('Loyalty score')\nplt.ylabel('Number of cards')\nplt.title('Loyalty Score Distribution')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c26ae8bbcca0171a14c4d4aa29561081dbab8eb3"},"cell_type":"markdown","source":"Yikes.  The first, most obvious thing is those outliers at around -32.  About 1% of the training data have these outlier values:"},{"metadata":{"trusted":true,"_uuid":"bc41e87b34c91bcd8b4908d7524177689185f57c"},"cell_type":"code","source":"print('Percent of cards with values <20: %0.3f'\n      % (100*(cards['target']<-20).sum() /\n              cards['target'].notnull().sum()))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"58e6fb3882e29d8ac179519ee451d23f0060e3a6"},"cell_type":"markdown","source":"And all of these outlier values are exactly the same:"},{"metadata":{"trusted":true,"_uuid":"b0491963f5d5f55a0e09381809a36874af1ae203"},"cell_type":"code","source":"cards.loc[cards['target']<-20, 'target'].unique()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"efacb6a05bf0dcfb9941028bbaa75d8592941fed"},"cell_type":"markdown","source":"Those outliers are definitely something we'll have to pay attention to when building a predictive model.  With a normal linear model (like, say, a non-robust linear regression) the outliers would have an enormus effect on the model.  We might want to perform some kind of outlier detection and train a separate model only on non-outlier data.\n\nAnother thing to note is the shape of the distribution - it's not normally distributed (the tails are too heavy and the central part is too sharp).  To me, it looks more like a log ratio distribution:"},{"metadata":{"trusted":true,"_uuid":"c91ef674851527cfe1e0693b3185aba8738af170"},"cell_type":"code","source":"# Show a log ratio distribution\na = np.random.rand(10000)\nb = np.random.rand(10000)\nc = np.log(a/b)\nplt.hist(c, bins=np.linspace(-35, 20, 50))\nplt.title('Log Ratio Distribution')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9559ab5d871fc3ef891e73465b8fd658a2ea90e0"},"cell_type":"markdown","source":"This suggests that the loyalty score may actually be the log of the ratio of two numbers, for example \"number of new purchases\" to \"number of historical purchases\".  The outlier values then may be cases when the denominator was zero.  Since the log of zero is negative infinity, a small positive number may have been added to the denominator to prevent infinite loyalty scores.  A small number like `1e-14` ends up being pretty close to the outlier values when the log is taken:"},{"metadata":{"trusted":true,"_uuid":"ac6d86573e39f5799eb1855bc8e203ece223a194"},"cell_type":"code","source":"np.log(1e-14)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"899ca767407a49a2b87ad4b7242be5f2ea08d913"},"cell_type":"markdown","source":"The cards dataset also contains the month in which the account was first active.  Looking at the distribution for that variable, we can see a progressive ramping up of the number of cards, with peaks just before the new year."},{"metadata":{"trusted":true,"_uuid":"657cc8d6101859d26ffc813fc0abdb82d8a177d0"},"cell_type":"code","source":"# Plot first_active_month distribution\nfam = cards['first_active_month'].value_counts()\nfam.plot()\nplt.ylabel('Number of Cards')\nplt.xlabel('first_active_month')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7816d5c2fa06059fc8e097ee3a3fa9510fb7ac18"},"cell_type":"markdown","source":"We can also look at the loyalty score (our target variable) as a function of the month the account was first active.  The confidence intervals in the plot below shrink the closer we get to 2018, because there are more samples at later timepoints (which you can see in the plot above).  There doesn't seem to be a huge change in the loyalty score as a function of first active month, except perhaps a slight uptick in loyalty scores over the course of 2017."},{"metadata":{"trusted":true,"_uuid":"94ae294cd39ae2871e6b7d32e99a479c23930417"},"cell_type":"code","source":"# first_active_month vs loyalty score\nsns.lineplot(x='first_active_month', y='target', data=cards)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3b259518e3e067ba3f23a9fe0d9347f85be59659"},"cell_type":"markdown","source":"Lastly, the cards dataset contains three categorical columns (with intentionally obfuscated titles): `feature_1`, `feature_2`, and `feature_3`."},{"metadata":{"trusted":true,"_uuid":"b4471bedfdb095d3c0b0ac4179034fa73aa30538"},"cell_type":"code","source":"# category counts\nplt.figure(figsize=(6.4, 10))\nplt.subplot(311)\nsns.countplot(x='feature_1', data=cards)\nplt.subplot(312)\nsns.countplot(x='feature_2', data=cards)\nplt.subplot(313)\nsns.countplot(x='feature_3', data=cards)\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c7e7007308080f9b0fd99fb66ec5d3c32e4c4c46"},"cell_type":"markdown","source":"The three categorical distributions are reasonably well-balanced (at least, the categories all have within an order of magnitude of occurrences).  \n\nIf we look at the loyalty score (the target variable) as a function of these categories, it appears that there are small differences in the loyalty score as all three of the category values increase (except perhaps when `feature_2`=2)."},{"metadata":{"trusted":true,"_uuid":"c6925c513cbd147daad28448373fb5ad930abcf0"},"cell_type":"code","source":"# Features vs loyalty score with std dev\nplt.figure(figsize=(6.4, 10))\nplt.subplot(311)\nsns.barplot(x='feature_1', y='target', data=cards)\nplt.subplot(312)\nsns.barplot(x='feature_2', y='target', data=cards)\nplt.subplot(313)\nsns.barplot(x='feature_3', y='target', data=cards)\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"71071b9aa2441a492fd50377e3a18c08c58c2cde"},"cell_type":"markdown","source":"However, the error bars plotted above are the estimated confidence intervals of the mean - the standard deviations are much larger.  If we set the error bars to show the standard deviation, we can see the raw variance in the data is huge compared to the differences between categories:"},{"metadata":{"trusted":true,"_uuid":"b2a3b44fe53bf9a7706d13fe1f1e382135e60a7c"},"cell_type":"code","source":"# Features vs loyalty score with std dev\nplt.figure(figsize=(6.4, 10))\nplt.subplot(311)\nsns.barplot(x='feature_1', y='target',\n            data=cards, ci='sd')\nplt.subplot(312)\nsns.barplot(x='feature_2', y='target',\n            data=cards, ci='sd')\nplt.subplot(313)\nsns.barplot(x='feature_3', y='target',\n            data=cards, ci='sd')\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"59472e832f4c09b33cc27a093960022de2e8f3ed"},"cell_type":"markdown","source":"<a id='merchants'></a>\n### Merchants\n\nThe merchants dataset has information about every merchant that any credit card account made a transaction with.  Now that we've cleaned and loaded that dataset, let's take a look again at the basic information about each column."},{"metadata":{"trusted":true,"_uuid":"7e68e84ff0a7bb449ad4a5687862b9b0ce7a2e57"},"cell_type":"code","source":"describe_df(merchants)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"45456e0783e72efedb7ade9bb972edc3e5a1d8c9"},"cell_type":"markdown","source":"The `merchant_group_id` column contains what group the merchant belongs to.  Presumably this corresponds to a business group (e.g. \"Walmart\", and individual merchants are individual stores), and not some sort of business sector identifier.  One single group has around 50,000 merchants in it, and around 3,000 have >10 merchants, but most have less than 10:"},{"metadata":{"trusted":true,"_uuid":"38616c336c19210be13dcdaf077938adfa986114"},"cell_type":"code","source":"# Show number of merchants per merchant group\nmpmg = merchants['merchant_group_id'].value_counts().values\nplt.plot(np.arange(len(mpmg))+1, mpmg)\nplt.xscale('log')\nplt.yscale('log')\nplt.xlabel('Unique merchant group')\nplt.ylabel('Number of merchants')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9fc40e12ca6f39d3b230093da89101305794a5d8"},"cell_type":"markdown","source":"The merchants dataset also contains two anonymized features: `numerical_1` and `numerical_2`.  The two distributions are very similar and are both very skewed (notice the log Y axis)."},{"metadata":{"trusted":true,"_uuid":"4124277bd33b75646fb47a15adc434243fb23115"},"cell_type":"code","source":"# Raw distributions for numerical cols\nplt.figure()\nplt.subplot(121)\nmerchants['numerical_1'].hist()\nplt.yscale('log')\nplt.ylabel('Number of merchants')\nplt.xlabel('numerical_1')\nplt.subplot(122)\nmerchants['numerical_2'].hist()\nplt.yscale('log')\nplt.xlabel('numerical_2')\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"da9f4f553d9e2acec5c68cdfcc0a8270f5c23233"},"cell_type":"markdown","source":"Not only do the two distributions look very similar, but the two variables are highly correlated:"},{"metadata":{"trusted":true,"_uuid":"ad8bc21c0853376d485f443556a57314f74d8b44"},"cell_type":"code","source":"rho, pv = spearmanr(merchants['numerical_1'].values,\n                    merchants['numerical_2'].values)\nprint('Correlation coefficient = %0.3f' % rho)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"11270825e1c0c0145d06cf8df9e6cd4c58879508"},"cell_type":"markdown","source":"In fact, for 90% of the merchants, the two values are exactly the same!"},{"metadata":{"trusted":true,"_uuid":"eb7c2e45d867797459c082d82bdb6b88552ec921"},"cell_type":"code","source":"(merchants['numerical_1'] == merchants['numerical_2']).mean()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f8e2d9fbf8161778a382c0c5e8952480f54ca845"},"cell_type":"markdown","source":"The `most_recent_sales_range` and `most_recent_purchases_range` contain what bin the merchant falls into in terms of recent sale amounts and purchase amounts, respectively.  There are a lot more merchants with higher category values here, suggesting the lower the category value, the higher the dollar amount (assuming the merchant-sales relationship is a log-like relationship, which is common)."},{"metadata":{"trusted":true,"_uuid":"3e86b36cfc9a9bc2ab4280eedba8579ea0dfcb18"},"cell_type":"code","source":"# most_recent_sales_range and most_recent_purchases_range\nplt.figure()\nplt.subplot(121)\nsns.countplot(x='most_recent_sales_range',\n              data=merchants)\nplt.yscale('log')\nplt.subplot(122)\nsns.countplot(x='most_recent_purchases_range',\n              data=merchants)\nplt.yscale('log')\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"47fa05d2dea0a2f0462e3f15e5effd2cc98e68b6"},"cell_type":"markdown","source":"Not suprisingly, the two categorical columns are correlated - a merchant's profits likely correspond at least somewhat closely to their expenses."},{"metadata":{"trusted":true,"_uuid":"461b3e8e776b7eb0d1e5172cd49707dc7d33eca4"},"cell_type":"code","source":"rho, pv = spearmanr(merchants['most_recent_sales_range'].values,\n                    merchants['most_recent_purchases_range'].values)\nprint('Correlation coefficient = %0.3f' % rho)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"520a8a2c454c2ebba75e00b44dec645c2ebc59df"},"cell_type":"markdown","source":"But, the categories aren't perfectly correlated:"},{"metadata":{"trusted":true,"_uuid":"7da5a76446998dad9a3b68fa4b351aac8b2ee9af"},"cell_type":"code","source":"# Show joint counts\ncountplot2d('most_recent_sales_range',\n            'most_recent_purchases_range',\n            merchants, log=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"36f1758c38799e253909b708c37944e67e687ac8"},"cell_type":"markdown","source":"The `avg_{sales,purchases}_lag{3,6,12}` columns store normalized sales and purchases in the past 3, 6, and 12 months.  During the data loading and cleaning, we took the inverse of these columns so they'd be less ridiculously skewed.  So, in the plots below, values closer to 0 indicate larger average purchases or sales."},{"metadata":{"trusted":true,"_uuid":"75a4e14d1c5c8cf9f8744bc5741223b4ee1971da"},"cell_type":"code","source":"def plothist3(df, cols, bins=30):\n    plt.figure(figsize=(6.4, 10))\n    for i, lab in enumerate(cols):\n        plt.subplot(3, 1, i+1)\n        merchants[lab].hist(bins=bins)\n        plt.xlabel(lab)\n    plt.tight_layout()\n    plt.show()\n    \nplothist3(merchants, \n          ['avg_sales_lag3', 'avg_sales_lag6', 'avg_sales_lag12'],\n          bins=np.linspace(-1, 3, 41))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f4dd9c85e1685ef7649fdb33da167924b3dd8cac"},"cell_type":"code","source":"plothist3(merchants, \n          ['avg_purchases_lag3',\n           'avg_purchases_lag6',\n           'avg_purchases_lag12'],\n          bins=np.linspace(-1, 3, 41))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3ae943648dffeb86018732fcaf6a924877fe358d"},"cell_type":"markdown","source":"The `active_months_lag` columns contain how many months in the past 3, 6, or 12 months the merchant has been active.  The vast majority of merchants are active every month (notice the log Y axis in the plots below), though there are arount 1,000 merchants who are only active during one or two months in the past year!"},{"metadata":{"trusted":true,"_uuid":"a225a440f4546f4c09320762696644c325a7496c"},"cell_type":"code","source":"plothist3(merchants, \n          ['active_months_lag3',\n           'active_months_lag6',\n           'active_months_lag12'],\n          bins=np.linspace(0.5, 12.5, 13))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f3d003041f706c53c3338db7088645b6383ca5ca"},"cell_type":"markdown","source":"The last column in the dataset is an anonymized binary column called `category_4`."},{"metadata":{"trusted":true,"_uuid":"bac1b8aaa36401f4a336c112b33f7df493d05368"},"cell_type":"code","source":"# category_4\nsns.countplot(x='category_4', data=merchants)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f99e1da8f3d316a09ab7063007430ff022474fc5"},"cell_type":"markdown","source":"<a id='transactions'></a>\n### Transactions\n\nAgain, the transactions datasets contain records of each transaction made with a card in the cards dataset and a merchant in the merchants dataset.  They are split into two separate datasets: historical transactions and \"new merchant\" transactions. The \"new\" transactions are transactions which occurred in a period leading up to the time the loyalty score was calculated, and the historical transactions are all transactions before that (back to the start date of the datset, at least).  \n\nTo explore the transactions in the dataset collectively, here we'll merge historical and new transactions.  However, when building a predictive model, we'll want to keep them separate, so that our model is given information as to when the loyalty score was calculated."},{"metadata":{"trusted":true,"_uuid":"6bb07013e0c064b9378cffaf432a26eb9939c301"},"cell_type":"code","source":"# Merge new and historical transactions\ntrans = pd.concat([hist_trans, new_trans])\n\n# Show info about the merged table\ndescribe_df(trans)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"959d9d6440b86d6144b3299e3d74aaeba921ef89"},"cell_type":"markdown","source":"We'll take a look at the distribution of the values for each feature.  First off, the `authorized_flag` column, which stores whether the transaction was authorized or rejected."},{"metadata":{"trusted":true,"_uuid":"ee9938412097060b47d4e8ffd15fc2daa96f88ab"},"cell_type":"code","source":"# authorized_flag\nsns.countplot(x='authorized_flag', data=trans)\nplt.title('Number of authorized transactions')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d0411aee421e18f7d5eea7a5c6ceef869dc9889d"},"cell_type":"markdown","source":"Most transactions were authorized, but there's a sizeable chunk of transactions which were declined.  The proportion of authorized transactions may end up being an important feature in our predictive model, seeing as card owners who are having their transactions repeatedly declined may be less \"loyal\" to the card company.  If there were an insignificant number of declined transactions we might just want to toss the column entirely - but, seeing as there are so many, we should keep it in case it ends up being an important predictor of loyalty.\n\nNext, we'll look at `card_id`.  This is essentially the index of the cards dataset, so by counting the number of transactions per unique `card_id`, we can see how many transactions were made on each card.  This too seems likely to be an important feature for predicting customer loyalty."},{"metadata":{"trusted":true,"_uuid":"cf46b24d8b1485d49fb37122c878ce9c350bac56"},"cell_type":"code","source":"# card_id\nplt.plot(trans['card_id'].value_counts().values)\nplt.xlabel('Unique card_id')\nplt.ylabel('Number of transactions')\nplt.title('Number of transactions per card_id')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"027feaa2e5a6fc6a6d21bfc72d8a8580e356ce2f"},"cell_type":"markdown","source":"It looks like there are a few customers (`card_id`s) with a very high number of transactions but most of the `card_id`s have less than 500 or so transactions.\n\nLet's take a look at the number of transactions per city and state.  There's one city with a very high number of transactions, but most have between 10 thousand an a million transactions (except for a few with a very low number of transactions)."},{"metadata":{"trusted":true,"_uuid":"8ffb36f49d1f264ea9b7640c3b91895f5ef8cce4"},"cell_type":"code","source":"# city_id\nplt.figure(figsize=(5, 40))\nsns.countplot(y='city_id', data=trans,\n              order=trans['city_id'].value_counts().index)\nplt.yticks(fontsize=8)\nplt.xscale('log')\nplt.gca().yaxis.set_major_formatter(FormatStrFormatter('%.0f'))\nplt.title('Number of transactions per City')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e565caafb8fe48efa152b678596aed145c68f975"},"cell_type":"markdown","source":"The number of transactions per state show a similar relationship: one state has a little over ten million transactions, and the rest have between 100 thousand and one million (except for one which has less than 10,000!)."},{"metadata":{"trusted":true,"_uuid":"84660202740bbbe560b2342d554fddad7d96f71e"},"cell_type":"code","source":"# state_id\nplt.figure(figsize=(8, 4))\nsns.countplot(x='state_id', data=trans,\n              order=trans['state_id'].value_counts().index)\nplt.ylabel('Count')\nplt.xlabel('state_id')\nplt.yscale('log')\nplt.gca().xaxis.set_major_formatter(FormatStrFormatter('%.0f'))\nplt.title('Number of transactions per State')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"02c24c5690e4297ebc685df761bb6485da032168"},"cell_type":"markdown","source":"There are three anonymized categorical variables included in the transactions dataset: `category_1`, `category_2`, and `category_3`.  The category names are intentionally completely noninformative presumably in order to preserve either anonymity or proprietary information.  However, we can still look at the distributions for each of these three variables"},{"metadata":{"trusted":true,"_uuid":"2f6b15bda2ce1dd82e792ac7259b28a0a5de8d9f"},"cell_type":"code","source":"# category_1\nsns.countplot(x='category_1', data=trans)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6d854d14415e0fa2f85fce0dbf65e2524f6993d0"},"cell_type":"code","source":"# category_2\nsns.countplot(x='category_2', data=hist_trans)\nplt.gca().xaxis.set_major_formatter(FormatStrFormatter('%.0f'))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a1be2e4ecc4f2554ea80e015c184096911828eb8"},"cell_type":"code","source":"# category_3\nsns.countplot(x='category_3', data=trans)\nplt.gca().xaxis.set_major_formatter(FormatStrFormatter('%.0f'))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1c3c161219effe8f538b8ba8021b581a95ed6c36"},"cell_type":"markdown","source":"Hmm, the distribution for `category_1` looks a lot like the `authorized_flag` distribution, just flipped (i.e. 1 instead of 0 and vice-versa).  Are the two features redundant?"},{"metadata":{"trusted":true,"_uuid":"705048553963ca35fad294ec306a311c079aa350"},"cell_type":"code","source":"# Joint distribution of authorized_flag vs category_1\nN, e1, e2 = np.histogram2d(trans['authorized_flag'], \n                           trans['category_1'], \n                           bins=[[-0.5, 0.5, 1.5], [-0.5, 0.5, 1.5]])\nsns.heatmap(N.astype('int64'), annot=True, fmt='d')\nplt.xlabel('authorized_flag')\nplt.ylabel('category_1')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c23964ac975d2082f7f7c952e32b2900e10e5f4e"},"cell_type":"markdown","source":"OK, `authorized_flag` and `category_1` aren't identical, just imbalanced in a similar way.  If they had been identical we would have wanted to remove one or the other feature.  As is, we'll keep both - especially considering we don't know what `category_1` is representing.\n\nNext up, we'll look at the number of installments for each transaction - that is, how many payments were made in order to pay off a given purchase."},{"metadata":{"trusted":true,"_uuid":"c448fa68c4a464637a6766ac83f27d0e7bb12a20"},"cell_type":"code","source":"# installments\nplt.hist(trans['installments'], bins=np.arange(-0.5, 12.5, 1.0))\nplt.axvline(x=0, color='k', linestyle='--')\nplt.ylabel('Count')\nplt.xlabel('installments')\nplt.yscale('log', nonposy='clip')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3c8cf61e6a43be396019aa0b4a647671ae3554f5"},"cell_type":"markdown","source":"The maximum number of installments is only 11 because the dataset spans only one year - presumably installments are made on a monthly basis, and so no customers have made more than 11 installments over the course of the year.\n\nThe `merchant_category_id` column contains categorical information as to the type of merchant with which the transaction was made.  These categories have also been anonymized, so we have only integer values in this column, but presumably these integers correspond to merchant categories such as \"fast food\", \"general contracting\", \"telecommunication\", or \"deparment stores\".  The ids may even correspond to [merchant category codes](https://en.wikipedia.org/wiki/ISO_18245).  Let's take a look at the number of transactions per merchant category:"},{"metadata":{"trusted":true,"_uuid":"670e839edd3413a579487caefa491b750cc1112a"},"cell_type":"code","source":"# merchant_category_id\nplt.figure(figsize=(5, 40))\nsns.countplot(y='merchant_category_id', data=trans,\n              order=trans['merchant_category_id'].value_counts().index)\nplt.yticks(fontsize=8)\nplt.gca().set(xscale='log')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"508a07b1d3c7590fcd27d1cacbe87527a3240ecf"},"cell_type":"markdown","source":"Similarly, the `subsector_id` likely contains information about the type of merchant with which the transaction occurred.  The value may be an anonymized version of the Brazillian equivalent of [NAICS codes](https://www.bls.gov/iag/tgs/iag_index_naics.htm)."},{"metadata":{"trusted":true,"_uuid":"f4df38bc241e2fd019d48cb59b198c0c5802ef5a"},"cell_type":"code","source":"# subsector_id\nplt.hist(hist_trans['subsector_id'], bins=np.arange(0.5, 41.5, 1.0))\nplt.ylabel('Count')\nplt.xlabel('subsector_id')\nplt.yscale('log')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cae45bbaf92ee2797bd422a10b2cef55d13221df"},"cell_type":"markdown","source":"The `merchant_id` column contains the ID of the merchant with which the transaction occurred.  The number of transactions per merchant show a pretty clean log-log relationship (untill we get to merchants with below ~30 transactions ever):"},{"metadata":{"trusted":true,"_uuid":"25700b91794958d11c5379870bbf9ac812ab993e"},"cell_type":"code","source":"# merchant_id\nplt.plot(np.arange(1, trans['merchant_id'].nunique()+1),\n         trans['merchant_id'].value_counts().values)\nplt.xlabel('Unique merchant_id')\nplt.ylabel('Number of transactions')\nplt.title('Number of transactions per merchant_id')\nplt.xscale('log')\nplt.yscale('log')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5017f2e3eed0285e98d2460b02cbed0d2a9f38d2"},"cell_type":"markdown","source":"There's also a pretty clear log-log relationship between the number of purchases and the purchase amount, until we get to purchases with below ~10 purchases of that amount (the dollar units are normalized, so some are negative, and so I've offset the x-axis below to allow for a log x scale, and binned the purchase amounts)."},{"metadata":{"trusted":true,"_uuid":"83350e93c4be04d83dd0c34c8c1a7a62d059a253"},"cell_type":"code","source":"# purchase_amount\ncounts, be = np.histogram(hist_trans['purchase_amount'],\n                          bins=np.arange(-1, 6010605, 5))\nplt.plot(be[:-1]-min(be[:-1])+1, counts)\nplt.ylabel('Count')\nplt.xlabel('purchase_amount')\nplt.xscale('log')\nplt.yscale('log')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ea34488241ef7edcb2643272d20ce8f4f0a19556"},"cell_type":"markdown","source":"The `purchase_date` column contains when the purchase occurred.  This will likely be an important feature for our predictive model.  Card owners who use their cards regularly (or whose use is increasing) are probably more likely to have a higher loyalty score.  Also, in general, our model will need to know when each transaction occurred!  Let's take a look at how many transactions occurred as a function of the date:"},{"metadata":{"trusted":true,"_uuid":"43d7e6664a433096658c14156e4a5601e3f5b24a"},"cell_type":"code","source":"# Function to plot transactions over time\ndef transactions_over_time(df):\n    tpd = pd.DataFrame()\n    tpd['Transactions'] = (\n        df['purchase_date'].dt.year*10000 +                    \n        df['purchase_date'].dt.month*100 +                    \n        df['purchase_date'].dt.day\n    ).value_counts()\n    tpd['Date'] = pd.to_datetime(tpd.index, format='%Y%m%d')\n    tpd.plot('Date', 'Transactions')\n    plt.ylabel('Number of transactions')\n    plt.show()\n    \n# purchase_date\ntransactions_over_time(trans)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f6d38c5bf08cf3f687efa9e5249071c34da535e0"},"cell_type":"markdown","source":"Huh.  The most obvious thing is the sudden dropoff in March 2018, and perhaps a smaller one in January 2018. This is actually just due to how the data has been split up in to new and historical transactions.  The historical transactions simply end mid-march:"},{"metadata":{"trusted":true,"_uuid":"7f65dcd9b1a58604f6ce84ee8be1fbd2ce3e049f"},"cell_type":"code","source":"# purchase_date\ntransactions_over_time(hist_trans)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4b509c2cf9deb782eeb5d7f27dcc9bf84e334d5f"},"cell_type":"markdown","source":"And the new transactions don't start (for most cards) until mid-march, but the number of transactions is far less.  I'm not actually sure why that is - it could be that not all new transactions were included in the dataset."},{"metadata":{"trusted":true,"_uuid":"346617338558c90f6c20de2eb5b7bb7c7786195c"},"cell_type":"code","source":"# purchase_date\ntransactions_over_time(new_trans)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0bf346cda02f565ca06185bb674d555d2dd93637"},"cell_type":"markdown","source":"The second interesting thing to notice is that there's a pretty significant weekly cycle (creating the high-frequency ups and downs in the plots above).  Looking at the number of transactions as a function of day of the week, we can see the the number of transactions ramps up over the course of the week, plummets on Sunday, and then starts climbing again on Monday."},{"metadata":{"trusted":true,"_uuid":"12e3caf5b00f2dbc0103c4411d6905c175ee55d3"},"cell_type":"code","source":"counts = (trans['purchase_date']\n          .dt.dayofweek.value_counts())\nplt.bar(x=counts.index, height=counts.values)\nplt.ylabel('Number of transactions')\nplt.xticks(range(7), ['M', 'T', 'W', 'Th', 'F', 'Sa', 'Su'])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f6f36c1eb7cb3f499d8d9dd070db4762e52f0a5a"},"cell_type":"markdown","source":"Finally, the `month_lag` column contains the number of months between the transaction date and the time when the loyalty score was calculated.  Again, because the dataset spans only about a year, we don't see that many`month_lag`s less than -12, and none greater than 0, because the dataset doesn't include data after the loyalty score was calculated."},{"metadata":{"trusted":true,"_uuid":"c5d47b9d9d5c07c456af067401549d8746cf86e7"},"cell_type":"code","source":"# month_lag\nplt.hist(trans['month_lag'], bins=np.arange(-13.5, 0.5, 1.0))\nplt.ylabel('Count')\nplt.xlabel('month_lag')\nplt.yscale('log')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1f2db7e52191c2bcf58b4175ddd46b41faf3532b"},"cell_type":"markdown","source":"<a id='conclusion'></a>\n## Conclusion\n\nNow that we've cleaned and gotten an understanding of the data, the next step is to connect the various data tables together and create features which a predictive model can use to predict customer loyalty.  In [the next kernel](http://www.kaggle.com/brendanhasz/elo-feature-engineering-and-feature-selection), we'll focus on feature engineering!"}],"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}