{"cells":[{"metadata":{"_uuid":"c27ab4e86b689149c0549227c585b287d9f9fa9c"},"cell_type":"markdown","source":"**Data Description** has little information about `historical_transactions.csv` and `new_merchant_transactions.csv`.\n>  - historical_transactions.csv - up to 3 months' worth of historical transactions for each card_id\n>  - new_merchant_transactions.csv - two months' worth of data for each card_id containing ALL purchases that card_id made at merchant_ids that were not visited in the historical data.\n\nThis kernel do same statistic about this two files."},{"metadata":{"trusted":true,"_uuid":"d95b970d18ed019f8cb8bb14a1d4eff3bb8e469c"},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"hist_trans = pd.read_csv('../input/historical_transactions.csv')\nnew_trans = pd.read_csv('../input/new_merchant_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ededae072a1e5bd90c05fa81228a03e2ebdbdda4"},"cell_type":"code","source":"hist_trans.shape, new_trans.shape","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3296c1a042833551352e5698186f7daff81c0617"},"cell_type":"markdown","source":"### 1. Overall time range"},{"metadata":{"trusted":true,"_uuid":"4deecfdd6da3d5d8a48af778a22c428fbc3f68e3"},"cell_type":"code","source":"hist_trans.purchase_date = pd.to_datetime(hist_trans.purchase_date)\nnew_trans.purchase_date = pd.to_datetime(new_trans.purchase_date)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fcb67b4b8b9a61c8302d7e18887af6123b72917c"},"cell_type":"code","source":"hist_trans.purchase_date.agg([np.min, np.max])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5122b218c0c10bc2f7d8c083fe199435eae62ace"},"cell_type":"code","source":"new_trans.purchase_date.agg([np.min, np.max])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e5c2305b0492470d75849758570c2d4ca4e05d03"},"cell_type":"markdown","source":"So, we know that `historical_transactions.csv` records transactions from 2017/01/01 to 2018/02/28, and `new_merchant_transactions.csv` records transactions from 2017/03/01 to 2018/04/30."},{"metadata":{"_uuid":"cac1c9460efa5f4e29232cb81c128e50e1e8db44"},"cell_type":"markdown","source":"### 2. Time range for each `card_id`"},{"metadata":{"trusted":true,"_uuid":"715f21b18abcb2312b4cc2760820538713dba00d"},"cell_type":"code","source":"hist_time_range = hist_trans.groupby('card_id', sort=False)['purchase_date'].agg([np.min, np.max])\nnew_time_range = new_trans.groupby('card_id', sort=False)['purchase_date'].agg([np.min, np.max])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"19774353f7c28d4b260ad5295d8f5eefddce4979"},"cell_type":"code","source":"hist_time_range.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"63c9609db1d2c115636d6814c9feb8e07c710c5f"},"cell_type":"code","source":"new_time_range.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c97c84a6e24400a6ea9ac7dd59adc3c057489564"},"cell_type":"code","source":"hist_time_range.shape, new_time_range.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"af0f5cfb13271e2ef7b31c2975d887f6b9932e39"},"cell_type":"code","source":"set(hist_time_range.index.tolist()) - set(new_time_range.index.tolist())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"301fa12b47e06953a46c487ee759a4107b33bf20"},"cell_type":"code","source":"set(new_time_range.index.tolist()) - set(hist_time_range.index.tolist())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"532d8c1d762f66fff2f7bcbc3926c7d11e0f3858"},"cell_type":"markdown","source":"`card_id` in `new_merchant_transactions.csv` also in `historical_transactions.csv`,  but the reverse is not the case."},{"metadata":{"trusted":true,"_uuid":"1d98b45b31ffd9c5742d70fdb1f8b1cd8861d3f7"},"cell_type":"code","source":"time_range = hist_time_range.join(new_time_range, how='right', lsuffix='_hist', rsuffix='_new')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"15a8fe3e25aa96dc9b0f53e18fe1491d40e942b7"},"cell_type":"code","source":"time_range.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"69dfbcac839c886ed3b25d8de185798bc5372cba"},"cell_type":"code","source":"time_range.shape","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"759799bd388bd6197559896aef72617d81bdcc6a"},"cell_type":"markdown","source":"Does historical_transactions early than new_transactions for all `card_id`?"},{"metadata":{"trusted":true,"_uuid":"a2f123e0d8be3d2a6c024d4eb6eec771524057c8"},"cell_type":"code","source":"all(time_range.amax_hist < time_range.amin_new)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"64593d90a362fdb267c845517a25220eb69e935a"},"cell_type":"markdown","source":"How long is `historical_transactions/new_merchant_transactions` for each `card_id`?"},{"metadata":{"trusted":true,"_uuid":"0a8d4e8e113b6ad6d5d82c5f0ade6df4a5fa2b67"},"cell_type":"code","source":"hist_month_num = (time_range.amax_hist - time_range.amin_hist).apply(lambda x: x.days / 30)\nnew_month_num = (time_range.amax_new - time_range.amin_new).apply(lambda x: x.days / 30)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a9b7d9ab27f88f79bed7da0202293f250477851d"},"cell_type":"code","source":"plt.hist(hist_month_num.values, bins=50)\nplt.title('Historical transactions time range')\nplt.xlabel('Month')\nplt.ylabel('Counts')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c6f1c18acd523dfc85c0376b03645d5c62c17707"},"cell_type":"code","source":"plt.hist(new_month_num.values, bins=50)\nplt.title('New merchant transactions time range')\nplt.xlabel('Month')\nplt.ylabel('Counts')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"73b95a156f16cc417a0ba16337d7a22b1b6ef5d7"},"cell_type":"markdown","source":"Well, historical transactions lasts more than 3 months, which is not consistent with **Data Description**.(am I wrong? if someone know the reason, please tell me.). new merchant transactions did last up to 2 months."},{"metadata":{"_uuid":"289459dd0555a01e9b7f4c5a307dc9c47377faec"},"cell_type":"markdown","source":"### 3. New merchant"},{"metadata":{"_uuid":"b3df988c869f64a6e3b8823d68cb868e05a43ec9"},"cell_type":"markdown","source":"`new_merchant_transactions`: ALL purchases that card_id made at `merchant_id`s that were not visited in the historical data. Let's check it."},{"metadata":{"_uuid":"96e13818d847b2d4074e1c5faeaf4b02bdcf1d24"},"cell_type":"markdown","source":"We'd better remove NA merchant_id"},{"metadata":{"trusted":true,"_uuid":"888ae0e4a2f242bcd680f2d962c900cd4f962734"},"cell_type":"code","source":"hist_trans.dropna(axis=0, how='any', inplace=True)\nnew_trans.dropna(axis=0, how='any', inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c8ce26ea1918ce554d675ae169e0e85675099b83"},"cell_type":"code","source":"hist_trans.shape, new_trans.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4c57356b6fde6b3908923d2a779fe8b36fa66a23"},"cell_type":"code","source":"hist_merchant_set = hist_trans.groupby('card_id')['merchant_id'].apply(lambda x: set(x.tolist()))\nnew_merchant_set = new_trans.groupby('card_id')['merchant_id'].apply(lambda x: set(x.tolist()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3ac0d17d48a39327659a8065d664a52b74ff66cf"},"cell_type":"code","source":"hist_merchant_set.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f39b2c669689cec9898ebb5d15cd1a422f66c0d4"},"cell_type":"code","source":"merchant_set = pd.concat([hist_merchant_set, new_merchant_set], axis=1, join='inner')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f29ebf16f6a79b4481953e54799ccbcf79384d49"},"cell_type":"code","source":"merchant_set.columns = ['hist', 'new']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"9049835272587ff72b5a9afbcf7e13c821cbcf77"},"cell_type":"code","source":"merchant_set.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"22869609d0f710ad1a83ebfde4d162518a246176"},"cell_type":"code","source":"intersection = merchant_set.apply(lambda x: len(set.intersection(x['hist'], x['new'])), axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ca438944d21e6b847e70e01c94add6f2882bf1a5"},"cell_type":"code","source":"intersection.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6a44708a0311c0299d3fb434033825f67451d086"},"cell_type":"code","source":"any(intersection.values)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"98493a53be05c11fa3acc72b10af82cf8b884e76"},"cell_type":"markdown","source":"There is no intersections between `historical_transactions.merchant_id` and `new_merchant_transactions.merchant_id` for every `card_id`."},{"metadata":{"_uuid":"1def02b05493c81c5e01dd3dd64ac4b31cc3babe"},"cell_type":"markdown","source":"Hope this will help, thanks!"},{"metadata":{"trusted":true,"_uuid":"0df7182ff2add720efbb539e8583b2b656abee81"},"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}