{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"# Different time points for card observations"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"I saw most of the kernels using *magic* 2018-02 month as a reference point for calculating date features. However, when I looked at the data it was obvious that this doesn't hold for each `card_id`. \n\nLet's try to extract the correct reference point (let's call it `observation_date`) for each `card_id`."},{"metadata":{"trusted":true,"_uuid":"57f1b518c42ba8caeb2c0a5a5619604c029c6ee0"},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\npd.set_option('display.float_format', '{:.10f}'.format)\n\n# read the data\ntrain = pd.read_csv('../input/train.csv')\nhistorical_transactions = pd.read_csv('../input/historical_transactions.csv')\nnew_merchant_transactions = pd.read_csv('../input/new_merchant_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cbb1c235c0167490038fbdb1bed12405f1bda319"},"cell_type":"code","source":"# fast way to get last historic transaction / first new transaction\nlast_hist_transaction = historical_transactions.groupby('card_id').agg({'month_lag' : 'max', 'purchase_date' : 'max'}).reset_index()\nlast_hist_transaction.columns = ['card_id', 'hist_month_lag', 'hist_purchase_date']\nfirst_new_transaction = new_merchant_transactions.groupby('card_id').agg({'month_lag' : 'min', 'purchase_date' : 'min'}).reset_index()\nfirst_new_transaction.columns = ['card_id', 'new_month_lag', 'new_purchase_date']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3070b6e4aba233e3fd14c52cc9f4837039892ec0"},"cell_type":"code","source":"# converting to datetime\nlast_hist_transaction['hist_purchase_date'] = pd.to_datetime(last_hist_transaction['hist_purchase_date']) \nfirst_new_transaction['new_purchase_date'] = pd.to_datetime(first_new_transaction['new_purchase_date']) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"92886ee5b7c85b49c0d0ae045cccaa78ac5d78b2"},"cell_type":"code","source":"# substracting month_lag for each row\nlast_hist_transaction['observation_date'] = \\\n    last_hist_transaction.apply(lambda x: x['hist_purchase_date']  - pd.DateOffset(months=x['hist_month_lag']), axis=1)\n\nfirst_new_transaction['observation_date'] = \\\n    first_new_transaction.apply(lambda x: x['new_purchase_date']  - pd.DateOffset(months=x['new_month_lag']-1), axis=1)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"432ccfcf3bf340da4a00dc88ee66a07048b115e9"},"cell_type":"markdown","source":"At this point we just reversed month lag function to get a rought estimate of the `observation_date` to be used for specific `card_id`. As you can see below, the `observation_date` is already different for many cards!"},{"metadata":{"trusted":true,"_uuid":"97f3a6ba6ed3eacf34eda08d410686dfb81b9566"},"cell_type":"code","source":"last_hist_transaction.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4c45ee24d24068b4c185d8461c97a0fe6219f83c"},"cell_type":"code","source":"first_new_transaction.head(20)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d3df71350e85b21fec5ed16589d1bdc0142f5bd0"},"cell_type":"markdown","source":"First thing you may notice is that historical transactions tend to be at the end of month, and for future transactions tend to be close to the start of the month. At this point it is safe to assume, that observation date is in format 2017-10-01, 2017-11-01, 2018-02-01, etc.\n\nLet's do that!"},{"metadata":{"trusted":true,"_uuid":"bde2f5c1b02992d38c76562858f0fce9bab9e53b"},"cell_type":"code","source":"last_hist_transaction['observation_date'] = last_hist_transaction['observation_date'].dt.to_period('M').dt.to_timestamp() + pd.DateOffset(months=1)\nfirst_new_transaction['observation_date'] = first_new_transaction['observation_date'].dt.to_period('M').dt.to_timestamp()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ac68b832bb6f8642d66d6c2473f6c880c00d7a86"},"cell_type":"code","source":"last_hist_transaction.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"77270b18c80285299d10d518e79c06430c47589a"},"cell_type":"code","source":"first_new_transaction.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"1cb70077b94395e3a8168733bf0747a17f5719f6"},"cell_type":"markdown","source":"All we need to do is to validate if `observation_date` matches both `historical_transactions` and `new_merchant_transactions` table information:"},{"metadata":{"trusted":true,"_uuid":"5b4a08a5b744e20b8dde65d73e7ab221585c557f"},"cell_type":"code","source":"validate = last_hist_transaction.merge(first_new_transaction, on = 'card_id')\nall(validate['observation_date_x'] == validate['observation_date_y'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8fad43633086d4a99c16f4ea68e2fa4184c8e50b"},"cell_type":"markdown","source":"So using two different tables we were able to reach the same observation date for each `card_id`. Now let's take a look how `observation_date` behaves together with `target` information:"},{"metadata":{"trusted":true,"_uuid":"9ac286bfceec1ed0326836865125b9537f51329b"},"cell_type":"code","source":"train = train.merge(last_hist_transaction, on = 'card_id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5f8031b5cc59939c6af4dca13d9e954419f681e9"},"cell_type":"code","source":"train.groupby('observation_date').agg({'target': ['mean','count']})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c73c5e37c7cd954c7d5d7f5f519466c2ed37b663"},"cell_type":"markdown","source":"So... there is definetly different behavior for how cards behave in different observation dates! \n\nNow there is some interesting brainstorming you can do... the lowest target ratio is for `observation_date` at 2017-11/12 - right before Christmas season. So it seems people are spending more money on presents from new merchants, therefore the \"loyal\" merchants suffered from that during Christmas shopping season..."},{"metadata":{"_uuid":"65d33d278520207be8946daf50b7c841d310cd55"},"cell_type":"markdown","source":"`observation_date` could be a good feature candidate... or maybe not, as kernels are already exploiting dates too much... :)"},{"metadata":{"_uuid":"81ccc4da96b97a169fee12a2e9784a9e6e51c04f"},"cell_type":"markdown","source":"## Thanks for reading, hope you find this useful!"},{"metadata":{"_uuid":"b02d5f8bc5dc239063767419099e725cb830814e"},"cell_type":"markdown","source":""}],"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}