{"cells":[{"metadata":{"_cell_guid":"9fd66109-60cd-4e1a-8aaa-68b083325e60","_uuid":"88dc347f004b1f54dc5bd53371bd748936563ed7"},"cell_type":"markdown","source":"# Summary\n![img](https://i.imgur.com/d0wC7Qm.png)\n### Samples in test & test supplement(short as 'sup' in the figure) are overlapping and totally time sorted\n### BUT some of them are in slightly different order within the same second\n### Overlapping samples can be replaced by those in test set by following indexes and offsets\n- offsets: [21290876, 29475763, 35793790]\n- test head indexes: [0, 6202933, 12316147]\n- test tail indexes: [6202932, 12316146, 18790468]\n\n## NOTICE: \n## The calculation for the offsets is simply based on observation of several head & tail samples of test and supplement set and if there' s anything wrong please comment!\n\n## DOWNLOAD\n\n- https://www.kaggle.com/johnfarrell/talkingdata-adtracking-test-order-supplement/data"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","collapsed":true,"trusted":true},"cell_type":"code","source":"%matplotlib inline\nimport os\nimport gc\nimport numpy as np\nimport pandas as pd\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nfrom tqdm import tqdm\ndtype = {\n        'ip'               : 'uint32',\n        'app'              : 'uint16',\n        'device'           : 'uint16',\n        'os'               : 'uint16',\n        'channel'          : 'uint16',\n        'is_attributed'    : 'uint8',\n        'click_id'         : 'uint32',\n        'click_hour'       : 'uint8',\n        'click_second'     : 'uint8',\n        'click_minute'     : 'uint8'\n        }\ntarget_col = 'is_attributed'\nkey_li = ['ip', 'app', 'device', 'os', 'channel', \n          'click_hour', 'click_minute', 'click_second']\nfrom functools import lru_cache\nget_datetime     = (lru_cache())(lambda x:pd.to_datetime(x))\nget_click_day    = (lru_cache())(lambda x:int(x[ 8:10]))\nget_click_hour   = (lru_cache())(lambda x:int(x[11:13]))\nget_click_minute = (lru_cache())(lambda x:int(x[14:16]))\nget_click_second = (lru_cache())(lambda x:int(x[17:19]))\nget_ac_delta = (lru_cache())(lambda x:int(x.total_seconds()))\n\ndef preprocess(df):\n    df['click_hour']   = df['click_time'].apply(get_click_hour)\n    df['click_minute'] = df['click_time'].apply(get_click_minute)\n    df['click_second'] = df['click_time'].apply(get_click_second)\n    return df","execution_count":1,"outputs":[]},{"metadata":{"_cell_guid":"457e6b52-3766-4414-8472-7fc88f4ffe56","_uuid":"b9522a9985f6d8191ef6a26bebdef80f5c48a4fb","trusted":true},"cell_type":"code","source":"df_sup = pd.read_csv('../input/talkingdata-adtracking-fraud-detection/test_supplement.csv', dtype=dtype)\nprint('supplement dataset loaded, shape', df_sup.shape)\ndf_test = pd.read_csv('../input/talkingdata-adtracking-fraud-detection/test.csv', dtype=dtype)\nprint('test dataset loaded, shape', df_test.shape)\ndf_sup['click_time'] = df_sup['click_time'].apply(get_datetime)\ndf_test['click_time'] = df_test['click_time'].apply(get_datetime)","execution_count":2,"outputs":[]},{"metadata":{"_cell_guid":"0acf625b-c3a8-4ff5-8bc7-d5499cabf7e9","_uuid":"b6b3698460ec53255ec5c5c7c0753c033d0f0c46","trusted":true},"cell_type":"code","source":"df_sup['click_time'].min(), df_sup['click_time'].max()","execution_count":3,"outputs":[]},{"metadata":{"_cell_guid":"e4498913-dbfb-446b-8efd-300893a958f4","_uuid":"a8b18ecbee3343061ce7addcc8b8f6fff981b996","collapsed":true,"trusted":true},"cell_type":"code","source":"test_indexes = [0, 6202933, 12316147]\noffsets = [21290876, 29475763, 35793790]","execution_count":4,"outputs":[]},{"metadata":{"_cell_guid":"bdcfb04f-6055-4299-8f15-b6130461f037","_uuid":"60d7f128366e003ebbd0210ef5ffb484eb1af641"},"cell_type":"markdown","source":"## Check head part in test & responding samples in test supplements\n### Head part: \n- 2017-11-10 04:00:00\n- 2017-11-10 09:00:00\n- 2017-11-10 13:00:00"},{"metadata":{"_cell_guid":"d362517c-2a2a-4bae-925a-7caf94abc1b3","_uuid":"dd8962e4574c78d41d4d5d0a08abf4a6bc699d10","trusted":true},"cell_type":"code","source":"i = 0\ndf_test.iloc[test_indexes[i]:].head()","execution_count":5,"outputs":[]},{"metadata":{"_cell_guid":"e25b449c-48cb-4704-9256-dd2cf5accdd9","_uuid":"90190547dcf1134d3537866952bfa4d8d9103983","trusted":true},"cell_type":"code","source":"df_sup.iloc[test_indexes[i]+offsets[i]:].head()","execution_count":6,"outputs":[]},{"metadata":{"_cell_guid":"819d46bf-5bee-4e1d-9a82-3691b6110df4","_uuid":"1c1a168e119c2d2390d5bb82f3334ee4ee605f36","trusted":true},"cell_type":"code","source":"i = 1\ndf_test.iloc[test_indexes[i]:].head()","execution_count":7,"outputs":[]},{"metadata":{"_cell_guid":"f50e4ce1-1e7e-4e72-acf2-28a2e033b60b","_uuid":"750411c0ac5190d17594cad1b84208c7bc940d66","trusted":true},"cell_type":"code","source":"df_sup.iloc[test_indexes[i]+offsets[i]:].head()","execution_count":8,"outputs":[]},{"metadata":{"_cell_guid":"9016ca0b-d5de-48c8-9970-5baec95d7573","_uuid":"615b0400dd56766b2f3c53f884ee78fcd3f3c903","trusted":true},"cell_type":"code","source":"i = 2\ndf_test.iloc[test_indexes[i]:].head()","execution_count":9,"outputs":[]},{"metadata":{"_cell_guid":"a6158b06-5665-4ac7-b8f9-0634c181e3df","_uuid":"5807692f76a3de42721e6d72cc21bbd2eec97ccf","trusted":true},"cell_type":"code","source":"df_sup.iloc[test_indexes[i]+offsets[i]:].head()","execution_count":10,"outputs":[]},{"metadata":{"_cell_guid":"33737a6f-c4a7-43c3-b3f7-e444dafdad8c","_uuid":"a800d3e79c2bd9a1c6e4de3c95c077c1df6d3ada"},"cell_type":"markdown","source":"## Check tail part in test & responding samples in test supplements\n### Tail part: \n- 2017-11-10 06:00:00\n- 2017-11-10 11:00:00\n- 2017-11-10 15:00:00"},{"metadata":{"_cell_guid":"0d751dd0-234f-4cc8-9a2a-e218540bc8a1","_uuid":"e972bdbaa77c3fedbb6bf0f8da71948fcfbaa32d","trusted":true},"cell_type":"code","source":"df_test[df_test['click_time']==pd.to_datetime('2017-11-10 06:00:00')].tail(1)","execution_count":11,"outputs":[]},{"metadata":{"_cell_guid":"582d4544-056f-4c47-a0d9-fd26b49ba358","_uuid":"e5b22eb469ac99948d3697f0b9beb62b909db202","trusted":true},"cell_type":"code","source":"df_test[df_test['click_time']==pd.to_datetime('2017-11-10 11:00:00')].tail(1)","execution_count":12,"outputs":[]},{"metadata":{"_cell_guid":"bc19031f-2653-4400-bb4e-101db4f06fde","_uuid":"54b9635c8739e2b95e6585836d3e0de354f54055","trusted":true},"cell_type":"code","source":"df_test[df_test['click_time']==pd.to_datetime('2017-11-10 15:00:00')].tail(1)","execution_count":13,"outputs":[]},{"metadata":{"_cell_guid":"7430d3e9-15d5-4bfb-85b1-d8dd6cc6641f","_uuid":"6e756773b117059a41bd49b57c62b56256b01491","collapsed":true,"trusted":true},"cell_type":"code","source":"test_tail_indexes = [6202932, 12316146, 18790468]","execution_count":14,"outputs":[]},{"metadata":{"_cell_guid":"55bf9778-4585-4e8d-a39e-13a9af040335","_uuid":"396027cef238326db37fc16965a68e0fdfe029cc","trusted":true},"cell_type":"code","source":"i = 0\ndf_test.iloc[:test_tail_indexes[i]+1].tail()","execution_count":15,"outputs":[]},{"metadata":{"_cell_guid":"cf91cb88-7b49-4322-969f-328bbf709bab","_uuid":"c63797f5885fcf85baa26c1dec06b7d39986ecb3","trusted":true},"cell_type":"code","source":"df_sup.iloc[:test_tail_indexes[i]+offsets[i]+1].tail()","execution_count":16,"outputs":[]},{"metadata":{"_cell_guid":"a8fe77bb-3f6c-42ac-b2a7-c72630fa6f23","_uuid":"51142ef8d505ce0d553b2925a7d3d1d1dce70782","trusted":true},"cell_type":"code","source":"i = 1\ndf_test.iloc[:test_tail_indexes[i]+1].tail()","execution_count":17,"outputs":[]},{"metadata":{"_cell_guid":"e0289028-18df-472e-a4e9-1d54c5a10737","_uuid":"4396229f5d7cba543bb5102e923a87bfbb3e7eba","trusted":true},"cell_type":"code","source":"df_sup.iloc[:test_tail_indexes[i]+offsets[i]+1].tail()","execution_count":18,"outputs":[]},{"metadata":{"_cell_guid":"634fde91-a795-4cd9-9ff6-5209554d2a0a","_uuid":"e3d780f9ed8d2d17969bc3c433d3f8250c3da8be","trusted":true},"cell_type":"code","source":"i = 2\ndf_test.iloc[:test_tail_indexes[i]+1].tail()","execution_count":19,"outputs":[]},{"metadata":{"_cell_guid":"66932132-fec9-42f6-92bf-39a49a3f872f","_uuid":"805f9ea3ac927a043e7933eedbab290e4c4bb944","trusted":true},"cell_type":"code","source":"df_sup.iloc[:test_tail_indexes[i]+offsets[i]+1].tail()","execution_count":20,"outputs":[]},{"metadata":{"_cell_guid":"90b48532-ca1f-4bae-b94a-7fd662bc9226","_uuid":"bd31c491f77548ec9c7ccfb41de3359f95fbeef5"},"cell_type":"markdown","source":"## Replacement"},{"metadata":{"_cell_guid":"ec205c40-cf1a-4817-86b5-2fd9180811b7","_uuid":"7e3262332a42cd922fb20c14a6a3a3e995cf2815","trusted":true},"cell_type":"code","source":"df_sup['eval_set'] = 0\ndel df_sup['click_id']\ndf_sup.head()","execution_count":21,"outputs":[]},{"metadata":{"_cell_guid":"17022018-420e-4c8b-adc6-25b3fbf58a75","_uuid":"201bb3c021fc679eed13ea05ab232ca95e9cca9f","trusted":true},"cell_type":"code","source":"df_test['eval_set'] = 1\ndel df_test['click_id']\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"f3c42cd0-d2a2-4200-b764-c5ae0ddeceb4","_uuid":"333819fb3d78de73cf8e6169cbe982e21567248a","trusted":true},"cell_type":"code","source":"for i, (head_idx, tail_idx, offset) in enumerate(zip(test_indexes, test_tail_indexes, offsets)):\n    print(i)\n    print('index:', head_idx, tail_idx, 'offset', offset)\n    df_sup.iloc[head_idx+offset:tail_idx+1+offset] = df_test.iloc[head_idx:tail_idx+1].values\ndf_sup = df_sup.reset_index(drop=True)\ndtype['eval_set'] = 'uint8'\nfor c in ['ip', 'app', 'device', 'os', 'channel', 'eval_set']:\n    df_sup[c] = df_sup[c].astype(dtype[c])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"f5039033-03ca-4514-be0c-0b24eeffdd19","_uuid":"390f5f08e118faef3d7ecf574905d0095c9c9f7d","collapsed":true,"trusted":true},"cell_type":"code","source":"df_test.head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"541631d0-3ca0-4316-b331-229e0f1a753d","_uuid":"69cad6d8e912deb19c029ae6ebd103c11d36d230","collapsed":true,"trusted":true},"cell_type":"code","source":"df_sup[df_sup['eval_set']==1].head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"32391583-a672-4bdd-b641-939d33e6f6d6","_uuid":"767a173d70cecab1b9eff662c716c0399b64c331"},"cell_type":"markdown","source":"## Here the 'click_id' is not included, please change the code if this is unexpected\n## And for the 1Gb limit of kernel, please run on local machine...\n## Or download here:\n\n- https://www.kaggle.com/johnfarrell/talkingdata-adtracking-test-order-supplement/data"},{"metadata":{"_cell_guid":"58893233-96f9-4334-984b-e7ca127fa8da","_uuid":"3b4eae95583a3fd0a59d81697de8240f018056f5","collapsed":true,"trusted":true},"cell_type":"code","source":"print('OSError: [Errno 28] No space left on device')\nprint('Please run locally...')\n#df_sup.to_csv('test_ordered_supplement.csv', index=False)\n#error...\n#OSError: [Errno 28] No space left on device","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.4","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}