{"cells":[{"metadata":{"collapsed":true,"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":false},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport datetime\nimport os\nimport gc\n#print(os.listdir(\"../input\"))\n\nimport time\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"e25b2d7b-91d6-4adc-b14c-6138d95e2b42","_uuid":"59d54fbff98ae0df074090ea59d700bbf4580593","trusted":false},"cell_type":"code","source":"#make wider graphs\nsns.set(rc={'figure.figsize':(12,5)});\nplt.figure(figsize=(12,5));","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"b013d100-9ea1-44bd-bcc1-cecf530e1530","_uuid":"570e8e53b45e4d825ddabfb3d5f237640efbce51"},"cell_type":"markdown","source":"The delay between click and download in the train data is quite interesting , but it has note been explored before."},{"metadata":{"collapsed":true,"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"code","source":"#import first24-hour of train data\ntrain = pd.read_csv('../input/train.csv', parse_dates=['click_time','attributed_time'], nrows=55442161, usecols=['app','click_time', 'attributed_time','is_attributed'])","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"b0b584aa-85a4-4c08-ad55-ad6de781ce4a","_uuid":"c53619647bbc4fd807033011d8f86bf6dd04f3f3","trusted":false},"cell_type":"code","source":"#set click_time and attributed_time as timeseries\ndownload = train[train['is_attributed']==1]\ndownload['click_time'] = pd.to_datetime(download['click_time'])\ndownload['attributed_time'] = pd.to_datetime(download['attributed_time'])\ndownload['download_delay_time'] = (download['attributed_time'] - download['click_time']).dt.total_seconds() / 3600","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"908d93fa-6c10-4bd0-b705-6b3b459413e3","_uuid":"298fa2a75026cad4dbe57193935aa68613991473","trusted":false},"cell_type":"code","source":"#double check that 'attributed_time' is not Null for all values that resulted in download (i.e. is_attributed == 1)\ndownload['click_hour'] = pd.to_datetime(download.click_time).dt.hour.astype('uint8')\ndownload['attributed_hour'] = pd.to_datetime(download.attributed_time).dt.hour.astype('uint8')\nsns.barplot('click_hour','download_delay_time', data=download)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"f680b212-6d53-475a-a53a-da126f6a99ae","_uuid":"3360957051064a925ba6f212cdc4665978c44967"},"cell_type":"markdown","source":"**Quick Notes/Observations** :\n- There is an interesting pattern in the download delay time in the first  24-hour. It peaks at the 15th hour and goes down steadily."},{"metadata":{"_cell_guid":"a8646edb-3264-4726-90af-113ef153e632","_uuid":"32dd7d7fa53806f04d339a3b618e9ac0a638f78c"},"cell_type":"markdown","source":"The download delay time vs attributed_time has a different pattern. The download delay time is increasing after the 15th hour."},{"metadata":{"collapsed":true,"_cell_guid":"f5a249f2-d6b5-4c9f-b4a8-f0a476f049b6","_uuid":"0dc7c9426a09a4be4c58bc40ab8b2cec3bb31b00","trusted":false},"cell_type":"code","source":"sns.barplot('attributed_hour','download_delay_time', data=download)","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"83ec8e6c-d362-46c3-b882-ccf446a84649","_uuid":"438258a50fdfa1523ccc016108bebd71064d87cc","trusted":false},"cell_type":"code","source":"feature = 'app'\ngk = train[[feature,'is_attributed']].groupby(by=[feature])[['is_attributed']].mean().reset_index().rename(index=str, columns={'is_attributed': 'download_rate'})\ngp = download[[feature,'download_delay_time']].groupby(by=[feature])[['download_delay_time']].mean().reset_index().rename(index=str, columns={'download_delay_time': 'mean_download_delay_time'})","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"fb29de1c-4dd9-40f7-9620-dfc2f52adf89","_uuid":"7e41d332485619b2d38d048932cb5211c9d5fe73"},"cell_type":"markdown","source":"### Does long download delay time afftect download rate"},{"metadata":{"_kg_hide-input":true,"_uuid":"7b5a2d5b29080284249e99e54a10f0b92e54a7ee","_cell_guid":"93adbb83-9651-4fec-9829-63d302b05d43","collapsed":true,"trusted":false},"cell_type":"code","source":"merge = gp.merge(gk, on=[feature], how='left')\nsns.lmplot(x=\"mean_download_delay_time\", y=\"download_rate\", data=merge)\nplt.title('Download-rate vs. Download_delay_time')\nplt.ylim(0,1)\nplt.xlim(0,24)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"93e5e6e3-32e8-49cc-9527-5cd09139b5eb","_uuid":"40238c2fbfb3ea58381713b187b94db43b337f33"},"cell_type":"markdown","source":"Apparaently, a long delay time yields lower download rates."},{"metadata":{"_kg_hide-input":true,"_uuid":"72ee00cf294793cfe49fe463518015df66829a64","_cell_guid":"fa4f4c74-636a-4eb7-a630-5e1a3045d2ae","collapsed":true,"trusted":false},"cell_type":"code","source":"gp = gp.sort_values(by='mean_download_delay_time',ascending=True).reset_index()\ngp.drop('index',axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"67e11675337a182b2909f223e02a1189671669e9","_cell_guid":"a759bfee-2b68-4af0-a587-2ca4c42481eb","collapsed":true,"trusted":false},"cell_type":"code","source":"print(gp.head())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4029823f9e4da2ffb181cdd2f516f60fd5125c08","_cell_guid":"7e9cd4fe-97cc-42a8-9244-7506a1da2bb2","collapsed":true,"trusted":false},"cell_type":"code","source":"print(gp.tail())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"69fd034b4e29acb7b8afdbe98e9d44902d2b4ac2","_cell_guid":"d082c022-3c7e-4ca7-8bd6-7d3c200d3a07","collapsed":true,"trusted":false},"cell_type":"code","source":"#20 apps with longest download time\nsns.barplot(feature,'mean_download_delay_time', data=gp.tail(20))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3dc53c8e4dbbc82b974d07e11621e973330c47a4","_cell_guid":"6e1bc5d0-d600-4ea4-8da8-aca28c913d54","collapsed":true,"trusted":false},"cell_type":"code","source":"#20 apps with shorest download time\nsns.barplot(feature,'mean_download_delay_time', data=gp.head(20))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7990d63999ccf14369cbe0c4fb8aa82c0e11b707","_cell_guid":"5a08da8e-d601-4720-ac76-054f9745e2ad","collapsed":true,"trusted":false},"cell_type":"code","source":"del train, download, gk, gp\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"0f201a40-1c22-4a44-a22b-1c31af19f1b0","_uuid":"653cb5fe02d8958e2a8a00231653f6ea365539fd"},"cell_type":"markdown","source":"### The second 24-hour"},{"metadata":{"collapsed":true,"_cell_guid":"b9754434-d148-473b-906a-85eed87106dc","_uuid":"a6e89dc832959439cc13d49cb72d05c8f4b914c2","trusted":false},"cell_type":"code","source":"train = pd.read_csv('../input/train.csv', parse_dates=['click_time','attributed_time'],skiprows=list(range(1,55442161)),nrows=61618817, usecols=['click_time', 'attributed_time','is_attributed'])","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"ac59b67f-55cd-4b3b-a28e-7db45ddf1b08","_uuid":"80da5cffae779cbcf4042db15a37966a8d6ed2d9","trusted":false},"cell_type":"code","source":"download = train[train['is_attributed']==1]\ndownload['click_time'] = pd.to_datetime(download['click_time'])\ndownload['attributed_time'] = pd.to_datetime(download['attributed_time'])\ndownload['download_delay_time'] = (download['attributed_time'] - download['click_time']).dt.total_seconds() / 3600","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"c047a916-daa4-4c07-bc0d-434e65d757a3","_uuid":"977f3438b4acd66812a939ac3a66a992d540ea17"},"cell_type":"markdown","source":"A similar pattern is found in the second and third 24-hour in the train dataset."},{"metadata":{"collapsed":true,"_cell_guid":"b499cfa2-05a9-4e32-ad6e-3cc42250eec6","_uuid":"76d4c71b12526cc966d35f21647760e6dd087e48","trusted":false},"cell_type":"code","source":"download['click_hour'] = pd.to_datetime(download.click_time).dt.hour.astype('uint8')\nsns.barplot('click_hour','download_delay_time', data=download)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.5","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}