{"cells":[{"metadata":{},"cell_type":"markdown","source":"Notebook tham khảo từ https://www.kaggle.com/yuliagm/talkingdata-eda-plus-time-patterns","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"# Overview\n\nNotebook này có 2 phần chính:\n\n**Phân tích tập mẫu 10 triệu rows đầu tiên**\n    - Số lượng các giá trị unique của mỗi feature\n    - Tỉ lệ số lần app được download\n    - Conversion tỉ lệ download trên tổng số lần click của mỗi feature\n    - Phân tích dữ liệu về ip\n**Phân tích dữ liệu thời gian**\n    - Thời gian thu thập dữ liệu\n    - Tỉ lệ app được download theo giờ\n    - Thời gian app được download sau khi được click ad","execution_count":null},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"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\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":{"_uuid":"59d54fbff98ae0df074090ea59d700bbf4580593","_cell_guid":"e25b2d7b-91d6-4adc-b14c-6138d95e2b42","trusted":true},"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_type":"markdown","source":"**Lấy 10 triệu rows đầu**","execution_count":null},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"#import first 10,000,000 rows of train and all test data\ntrain = pd.read_csv('../input/train.csv', nrows=10000000)\ntest = pd.read_csv('../input/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a43dbe3c8dfa57e10c1f0e92b179d19b0a971739","_cell_guid":"2c161eb5-7aae-4e20-9136-487c470d6c80","trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7403ec4981d3dfbe0815a063a2748536a0ff2641","_cell_guid":"31fc3607-7e48-4a7b-8ecf-a27d0e236ede","trusted":true},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5813015e82c596d437742c8e8a68a9e2fede27d6","_cell_guid":"49111dbe-0f15-4ac1-bdd3-dbcbff0b1f3a"},"cell_type":"markdown","source":"ip, app, device, os and channel are actually categorical variables encoded as integers.   Set them as categories for analysis.","execution_count":null},{"metadata":{"_uuid":"f7898f2be2dbba7e9e11edccd2df8d9f584e88e9","_cell_guid":"f4472ed2-2a98-46be-9897-d66c69fb2bc2","trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"variables = ['ip', 'app', 'device', 'os', 'channel']\nfor v in variables:\n    train[v] = train[v].astype('category')\n    test[v]=test[v].astype('category')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6afab9062d5062f1d22c89fb432d7ed7e914e4a3"},"cell_type":"markdown","source":"Convert date stamps to date/time type.","execution_count":null},{"metadata":{"_uuid":"c53619647bbc4fd807033011d8f86bf6dd04f3f3","_cell_guid":"b0b584aa-85a4-4c08-ad55-ad6de781ce4a","trusted":true},"cell_type":"code","source":"#set click_time and attributed_time as timeseries\ntrain['click_time'] = pd.to_datetime(train['click_time'])\ntrain['attributed_time'] = pd.to_datetime(train['attributed_time'])\ntest['click_time'] = pd.to_datetime(test['click_time'])\n\n#set as_attributed in train as a categorical\ntrain['is_attributed']=train['is_attributed'].astype('category')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"345337293ceef6c3ddf8bd18ac3f1adfc9e5d6a6","_cell_guid":"fb37bf89-40fa-4614-9998-db8dd11db7a8"},"cell_type":"markdown","source":"Now lets do a quick inspection of train and test data main statistics","execution_count":null},{"metadata":{"_uuid":"70ab859fa961b7ff5a145338d84aab6ce35ab9e8","_cell_guid":"e4e2c0cb-eb7c-4654-a239-02f76dc947e2"},"cell_type":"markdown","source":"*this graph is adapted from https://www.kaggle.com/anokas/talkingdata-adtracking-eda*:","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"### Table 1: Số lượng giá trị unique của mỗi feature","execution_count":null},{"metadata":{"_kg_hide-input":true,"_uuid":"6b7786295023b880edfd5a5a328d90582258d7d2","_cell_guid":"cf5a0bd4-2fb6-4878-aa11-442f09c703f5","trusted":true},"cell_type":"code","source":"plt.figure(figsize=(10, 6))\ncols = ['ip', 'app', 'device', 'os', 'channel']\nuniques = [len(train[col].unique()) for col in cols]\nsns.set(font_scale=1.2)\nax = sns.barplot(cols, uniques, log=True)\nax.set(xlabel='Feature', ylabel='log(unique count)', title='Number of unique values per feature (from 10,000,000 samples)')\nfor p, uniq in zip(ax.patches, uniques):\n    height = p.get_height()\n    ax.text(p.get_x()+p.get_width()/2.,\n            height + 10,\n            uniq,\n            ha=\"center\") \n# for col, uniq in zip(cols, uniques):\n#     ax.text(col, uniq, uniq, color='black', ha=\"center\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"298fa2a75026cad4dbe57193935aa68613991473","_cell_guid":"908d93fa-6c10-4bd0-b705-6b3b459413e3","trusted":true},"cell_type":"code","source":"#double check that 'attributed_time' is not Null for all values that resulted in download (i.e. is_attributed == 1)\ntrain[['attributed_time', 'is_attributed']][train['is_attributed']==1].describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ea3396fdfe0ab01e9ad093f8114e5c3cb52ac166","_cell_guid":"d9ccd3c5-31c5-4630-b892-4c3f8f49fce7","trusted":true},"cell_type":"code","source":"#set click_id to categorical, for cleaner statistics view\ntest['click_id']=test['click_id'].astype('category')\ntest.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cfc1e609e473b358de64f28664f026bd3ad53a20","_cell_guid":"eb352b8d-1ae2-4aae-a09c-80668ca08a30"},"cell_type":"markdown","source":"Only a small proportion of clicks were followed by a download:","execution_count":null},{"metadata":{"_uuid":"a04cb16935bb6f8005812e72fce3996017d3dfc5","_kg_hide-input":true,"_cell_guid":"62aea014-d731-4ce3-bac7-f81fb842f5bd","trusted":true},"cell_type":"code","source":"plt.figure(figsize=(6,6))\n#sns.set(font_scale=1.2)\nmean = (train.is_attributed.values == 1).mean()\nax = sns.barplot(['App Downloaded (1)', 'Not Downloaded (0)'], [mean, 1-mean])\nax.set(ylabel='Proportion', title='App Downloaded vs Not Downloaded')\nfor p, uniq in zip(ax.patches, [mean, 1-mean]):\n    height = p.get_height()\n    ax.text(p.get_x()+p.get_width()/2.,\n            height+0.01,\n            '{}%'.format(round(uniq * 100, 2)),\n            ha=\"center\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"32dd7d7fa53806f04d339a3b618e9ac0a638f78c","_cell_guid":"a8646edb-3264-4726-90af-113ef153e632"},"cell_type":"markdown","source":"### Explore ip counts.  Check if multiple ips have any downloads.\n\nAt this point I was trying to figure out what 'ip' were actually encoding.  My original understanding that ips were user specific did not hold up to scrutiny.\nIf ip repeated too many times, was it a bot?  This does not appear to be true, as repeated ips do convert.  See below:","execution_count":null},{"metadata":{"_uuid":"880ef59ebd90fb4785840f9f482194df4d167def","scrolled":true,"_cell_guid":"9f69414d-ff35-4fb0-9df8-92dd7aaeb16d","trusted":true},"cell_type":"code","source":"#temporary table to see ips with their associated count frequencies\ntemp = train['ip'].value_counts().reset_index(name='counts')\ntemp.columns = ['ip', 'counts']\ntemp[:10]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"456432e31b367d28c77cbb9cddc6ac376a54d373","_cell_guid":"d0eea3c7-8c3d-4abe-b1f3-3551e985476d","trusted":true},"cell_type":"code","source":"#add temporary counts of ip feature ('counts') to the train table, to see if IPs with high counts have conversions\ntrain= train.merge(temp, on='ip', how='left')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0dc7c9426a09a4be4c58bc40ab8b2cec3bb31b00","_cell_guid":"f5a249f2-d6b5-4c9f-b4a8-f0a476f049b6","trusted":true},"cell_type":"code","source":"#check top 10 values\ntrain[train['is_attributed']==1].sort_values('counts', ascending=False)[:10]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f207a7390e52be892713eb0464d682dc3eb1bc34","_cell_guid":"92bc4560-08e1-475f-9450-cd7a85446437","trusted":true},"cell_type":"code","source":"train[train['is_attributed']==1].ip.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1b3090e6051f51ba8e29a689be8b51a66e416066","_cell_guid":"8d4fd179-b00a-4c3e-b85a-95dd4988f2c5"},"cell_type":"markdown","source":"So high frequency ip counts do get conversions.   Up to 56 downloads for one ip.  Each IP must be for some network with many devices.","execution_count":null},{"metadata":{"_uuid":"438258a50fdfa1523ccc016108bebd71064d87cc","_cell_guid":"83ec8e6c-d362-46c3-b882-ccf446a84649","trusted":true},"cell_type":"code","source":"#convert 'is_attributed' back to numeric for proportion calculations\ntrain['is_attributed']=train['is_attributed'].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3360957051064a925ba6f212cdc4665978c44967","_cell_guid":"f680b212-6d53-475a-a53a-da126f6a99ae"},"cell_type":"markdown","source":"**Nhận xét** :\n- Có 18717 attributed_time values.  Chiếm tỉ lệ thấp hơn 0.2% !\n- Có những ip được click hơn 50 nghìn lần chỉ trong vòng vài ngày. \n- Train và Test không bị overlap bởi vì data được lấy tổng trong 5 ngày, trong đó 4 ngày đầu thuộc data train (Từ thứ 2 - thứ 5) và ngày cuối (thứ 6) thuộc data test. \n- Không có mising data trừ col attributed_time","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"## Conversion feature","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"**Nhận xét chung: \nNhìn chung mỗi feature đều có sự chênh lệch lớn về số lần click ad giữa các giá trị. Với các giá trị có số lần click cao, tỉ lệ app được tải giao động ở khoảng 0.0001-0.00015**","execution_count":null},{"metadata":{"_uuid":"7e41d332485619b2d38d048932cb5211c9d5fe73","_cell_guid":"fb29de1c-4dd9-40f7-9620-dfc2f52adf89"},"cell_type":"markdown","source":"### Conversion rates over Counts of 300 most popular IPs","execution_count":null},{"metadata":{"_kg_hide-input":true,"_uuid":"7b5a2d5b29080284249e99e54a10f0b92e54a7ee","_cell_guid":"93adbb83-9651-4fec-9829-63d302b05d43","trusted":true},"cell_type":"code","source":"proportion = train[['ip', 'is_attributed']].groupby('ip', as_index=False).mean().sort_values('is_attributed', ascending=False)\ncounts = train[['ip', 'is_attributed']].groupby('ip', as_index=False).count().sort_values('is_attributed', ascending=False)\nmerge = counts.merge(proportion, on='ip', how='left')\nmerge.columns = ['ip', 'click_count', 'prop_downloaded']\n\nax = merge[:300].plot(secondary_y='prop_downloaded')\nplt.title('Conversion Rates over Counts of 300 Most Popular IPs')\nax.set(ylabel='Count of clicks')\nplt.ylabel('Proportion Downloaded')\nplt.show()\n\nprint('Counversion Rates over Counts of Most Popular IPs')\nprint(merge[:20])\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"40238c2fbfb3ea58381713b187b94db43b337f33","_cell_guid":"93e5e6e3-32e8-49cc-9527-5cd09139b5eb"},"cell_type":"markdown","source":"Conversions are noisy and do not appear to correlate with how popular an IP is.","execution_count":null},{"metadata":{"_uuid":"cda01b2eca5a5701fd09b048ce8fb259b9a648de","_cell_guid":"9a572475-b474-422a-9b96-1cbb3b602ba1"},"cell_type":"markdown","source":"### Conversions by App\n\nCheck 100 most popular apps by click count:","execution_count":null},{"metadata":{"_kg_hide-input":true,"_uuid":"48acf222ff1c057801e3ad7680b9f8c0319acda9","_cell_guid":"c8f8e47c-f069-45a4-8478-b6eaaf09ef2a","trusted":true},"cell_type":"code","source":"proportion = train[['app', 'is_attributed']].groupby('app', as_index=False).mean().sort_values('is_attributed', ascending=False)\nprint(proportion.head())\ncounts = train[['app', 'is_attributed']].groupby('app', as_index=False).count().sort_values('is_attributed', ascending=False)\nprint(counts.head())\nmerge = counts.merge(proportion, on='app', how='left')\nmerge.columns = ['app', 'click_count', 'prop_downloaded']\n\nax = merge[:100].plot(secondary_y='prop_downloaded')\nplt.title('Conversion Rates over Counts of 100 Most Popular Apps')\nax.set(ylabel='Count of clicks')\nplt.ylabel('Proportion Downloaded')\nplt.show()\n\nprint('Counversion Rates over Counts of Most Popular Apps')\nprint(merge[:20])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"39fdc3974baf450ac61cb5bb957b804714e9b492","_cell_guid":"3df87c11-4c96-4eb3-abdb-57623dce5fa8"},"cell_type":"markdown","source":"There is a again a huge difference in clicks per app, with minimum of one click on an app and max at almost 13 million.  The proportion flucuates more as the counts go down, since each additional click has larger impact on the proportion value.  In general, for apps with counts in the thousands the ratio stays within 0.0001 - 0.0015 boundary.  For less popular apps it fluxuates more widely.  ","execution_count":null},{"metadata":{"_uuid":"6dc17b2aac96e8d190febf61c9f068df2d3068b1","_cell_guid":"f5c2fb69-0dd1-4030-bde7-990722723c02"},"cell_type":"markdown","source":"### Conversions by OS\nLook at top 100 operating systems by click count","execution_count":null},{"metadata":{"_kg_hide-input":true,"_uuid":"47b173a9303e79b9fcbdcf33ea03fa97518d648f","_cell_guid":"59878825-fd6e-4190-90e4-13f7b915c401","trusted":true},"cell_type":"code","source":"proportion = train[['os', 'is_attributed']].groupby('os', as_index=False).mean().sort_values('is_attributed', ascending=False)\ncounts = train[['os', 'is_attributed']].groupby('os', as_index=False).count().sort_values('is_attributed', ascending=False)\nmerge = counts.merge(proportion, on='os', how='left')\nmerge.columns = ['os', 'click_count', 'prop_downloaded']\n\nax = merge[:100].plot(secondary_y='prop_downloaded')\nplt.title('Conversion Rates over Counts of 100 Most Popular Operating Systems')\nax.set(ylabel='Count of clicks')\nplt.ylabel('Proportion Downloaded')\nplt.show()\n\nprint('Counversion Rates over Counts of Most Popular Operating Systems')\nprint(merge[:20])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"895d062a535dc520a3f6b7c52bd08b3cf5db142d","_cell_guid":"290fb109-ffa2-4f0a-b0d1-b9493ded19ed"},"cell_type":"markdown","source":"Same story. For values in the thousands the boundary on the ratio is very low, roughly between 0.0006 and 0.003, but as counts on OS become lower, the ratio starts fluxuating more wildely.","execution_count":null},{"metadata":{"_uuid":"9f18995506f3d791d8b939c3dbe72f2f2527daee","_cell_guid":"2f64866b-dcbd-4347-a328-e69c185227a2"},"cell_type":"markdown","source":"### Conversions by Device\n\nDevices are extremely disproportionately distributed, with number one device used almost 94% of time.  For that device proportion download was 0.001326. (0.13%)","execution_count":null},{"metadata":{"_kg_hide-input":true,"_uuid":"52c1d634ebce662b34c1a942d8e25c453e2e6079","scrolled":true,"_cell_guid":"1975c904-5338-4bef-bd2f-6cec6c9c6b62","trusted":true},"cell_type":"code","source":"proportion = train[['device', 'is_attributed']].groupby('device', as_index=False).mean().sort_values('is_attributed', ascending=False)\ncounts = train[['device', 'is_attributed']].groupby('device', as_index=False).count().sort_values('is_attributed', ascending=False)\nmerge = counts.merge(proportion, on='device', how='left')\nmerge.columns = ['device', 'click_count', 'prop_downloaded']\n\nprint('Count of clicks and proportion of downloads by device:')\nprint(merge)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"45cdd9d097d9ac6a74b697e3e59514920c277479","_cell_guid":"96bf22ab-157c-481a-b8aa-6695ff2e1230"},"cell_type":"markdown","source":"### Conversions by Channel\n","execution_count":null},{"metadata":{"_kg_hide-input":true,"_uuid":"a4717e2e181adc0ee856d1b6473f832a15f511bf","_cell_guid":"3f8bc653-3d17-456b-bfa0-3b6ce8cdc72e","trusted":true},"cell_type":"code","source":"proportion = train[['channel', 'is_attributed']].groupby('channel', as_index=False).mean().sort_values('is_attributed', ascending=False)\ncounts = train[['channel', 'is_attributed']].groupby('channel', as_index=False).count().sort_values('is_attributed', ascending=False)\nmerge = counts.merge(proportion, on='channel', how='left')\nmerge.columns = ['channel', 'click_count', 'prop_downloaded']\n\nax = merge[:100].plot(secondary_y='prop_downloaded')\nplt.title('Conversion Rates over Counts of 100 Most Popular Apps')\nax.set(ylabel='Count of clicks')\nplt.ylabel('Proportion Downloaded')\nplt.show()\n\nprint('Counversion Rates over Counts of Most Popular Channels')\nprint(merge[:20])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6b695c6da99b7c47df87e49baaeb8b6929bacadb","_cell_guid":"80ab6a53-28bf-4820-8dd0-22f523356ed1"},"cell_type":"markdown","source":"There appear to be a few peaks for channels at reasonable click quantity, but overall the pattern holds same as for categories above.  ","execution_count":null},{"metadata":{},"cell_type":"markdown","source":"## BE CAREFUL about IPs as a signal\n\nVấn đề này được rất nhiều người bàn luận https://www.kaggle.com/yuliagm/be-careful-about-ips-as-a-signal\n\n**Overall the number of IPs (test OR train): 333168**\n\n**Number of IPs that are in both (test AND train): 38164**\n\n**Number of IPs that are in Train and NOT in Test: 239232**\n\n**Number of IPs that are in Test and NOT in Train: 55772**","execution_count":null},{"metadata":{"trusted":true},"cell_type":"code","source":"dtypes = {\n        'ip'            : 'uint32',\n        'app'           : 'uint16',\n        'device'        : 'uint16',\n        'os'            : 'uint16',\n        'channel'       : 'uint16',\n        'is_attributed' : 'uint8',\n        'click_id'      : 'uint32'\n        }\n\ntrain = pd.read_csv('../input/train.csv', dtype=dtypes, usecols=['ip', 'is_attributed'])\ntest = pd.read_csv('../input/test.csv', dtype=dtypes, usecols=['ip'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df = train.groupby('ip').is_attributed.mean().to_frame().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df['roll'] = df.is_attributed.rolling(window=1000).mean()\nplt.plot(df.ip, df.roll)\nplt.xlabel(\"Ip\")\nplt.ylabel(\"Proportion Downloaded\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Có thể thấy có 2 khoảng cắt rất rõ ràng đó là ở ip từ 120000 (tỉ lệ download tăng từ 0.02 lên hơn 0.25) và 210000 (tăng từ 0.25 lên hơn 0.3)**\n\nCó thể lý giải bằng giả thuyết như sau: Trong tập train, các ip được sort theo số lượng click từ lớn đến bé, khoảng 120000 ip đầu tiên là những ip có lượt click cao nhất chính vì vậy mà tỉ lệ download cũng là thấp nhất (khoảng 0.02%), và các ip lớn hơn từ 120000 có lượt click ad thấp chính vì thế tỉ lệ download cũng cao hơn.\n\n+ Có khi nào ip ở tập train và test được mã hóa độc lập? (Bởi vì dải ip của test chỉ đến 126000) Nếu đúng như vậy thì feature này liệu có tốt?","execution_count":null},{"metadata":{"_uuid":"9c506d2b1f60d2ad4e247c19b6338000996f39b0","_cell_guid":"8ca07e88-b719-44df-a07f-6b4125b7baa6"},"cell_type":"markdown","source":"# Checking for time patterns\n\nKì lạ là thời gian click ad kéo dài từ 0h đêm đến 3h chiều ngày hôm sau => Ngày ngủ đêm bay? Hoặc có thể múi giờ bị sử dụng lệch chăng?","execution_count":null},{"metadata":{"_uuid":"aa6d5428c4f9d0310be29c102bca177472340ac7","_cell_guid":"857bf167-04aa-49cf-879f-d2404bbd064b","trusted":true},"cell_type":"code","source":"train_smp = pd.read_csv('../input/train_sample.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"77a7c527211d64123077475133c577ce564272df","_cell_guid":"e807b92f-4fd4-4cfb-b2d2-71aad7841575","trusted":true},"cell_type":"code","source":"#convert click_time and attributed_time to time series\ntrain_smp['click_time'] = pd.to_datetime(train_smp['click_time'])\ntrain_smp['attributed_time'] = pd.to_datetime(train_smp['attributed_time'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9926ff20f22935b2eb532f8456dfd4de12b8d96a","_cell_guid":"b312a990-4d01-4682-8f6b-ba498fe7cbcb","trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"#round the time to nearest hour\ntrain_smp['click_rnd']=train_smp['click_time'].dt.round('H')  \n\n#check for hourly patterns\ntrain_smp[['click_rnd','is_attributed']].groupby(['click_rnd'], as_index=True).count().plot()\nplt.title('HOURLY CLICK FREQUENCY');\nplt.ylabel('Number of Clicks');\n\ntrain_smp[['click_rnd','is_attributed']].groupby(['click_rnd'], as_index=True).mean().plot()\nplt.title('HOURLY CONVERSION RATIO');\nplt.ylabel('Converted Ratio');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6d2921079956febc64037e147bd1cb9a0a3acd78","_cell_guid":"466fddc9-b849-4fb7-9ec3-01c5be2eb2cc"},"cell_type":"markdown","source":"There is no clear hourly time pattern in ratios, however there is a definete pattern in frequency of clicks based on time of day.\n\nLets extract the hour of day from each day as a separate feature, and see combined trend (merge the 4 days together by hour).","execution_count":null},{"metadata":{"_uuid":"a990bcc90c5d929b3d26e3316bb332108558b8c9","_cell_guid":"f35bd737-baa5-4efe-b7ce-f394a49977d2","trusted":true},"cell_type":"code","source":"#extract hour as a feature\ntrain_smp['click_hour']=train_smp['click_time'].dt.hour","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4409f184454123d173aa0105205b2f34bcc4294d","_cell_guid":"10650df6-40d1-4040-9dc7-53bb3b00e210","trusted":true},"cell_type":"code","source":"train_smp.head(7)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"884bfad88b5144a138b73cf3c0eceee5d511ad46","_cell_guid":"879a1594-90c2-4b91-921d-ea8976f45c7a"},"cell_type":"markdown","source":"Let's check number of clicks by hour:","execution_count":null},{"metadata":{"_uuid":"bb18bee17285099c40d7f5d4c0cf93b0c73b3475","_kg_hide-input":true,"_cell_guid":"bdba5f32-2345-46f3-9f38-7635b7c07245","trusted":true},"cell_type":"code","source":"train_smp[['click_hour','is_attributed']].groupby(['click_hour'], as_index=True).count().plot(kind='bar', color='#a675a1')\nplt.title('HOURLY CLICK FREQUENCY Barplot');\nplt.ylabel('Number of Clicks');\n\ntrain_smp[['click_hour','is_attributed']].groupby(['click_hour'], as_index=True).count().plot(color='#a675a1')\nplt.title('HOURLY CLICK FREQUENCY Lineplot');\nplt.ylabel('Number of Clicks');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"da1d5e4700e1b2fa2f0550fa900038872b528692","_cell_guid":"68df1f7b-c69f-43a3-b1e2-1777b7474131"},"cell_type":"markdown","source":"And number of conversions by hours:","execution_count":null},{"metadata":{"_uuid":"a21e3fb00079d254c613ff7f5787cd176b836999","_cell_guid":"0377e51e-d19a-47cc-b757-87ad2eac396c"},"cell_type":"markdown","source":"let's overlay the two graphs to see if patterns correlate in any way","execution_count":null},{"metadata":{"_uuid":"adccf5af4068b4f0f6b955fea5b5dfb5c6a84130","_kg_hide-input":true,"_cell_guid":"01a8c317-15a4-4819-b205-b65a9c8793d0","trusted":true},"cell_type":"code","source":"#adapted from https://stackoverflow.com/questions/9103166/multiple-axis-in-matplotlib-with-different-scales\n#smonek's answer\n\n\ngroup = train_smp[['click_hour','is_attributed']].groupby(['click_hour'], as_index=False).mean()\nx = group['click_hour']\nymean = group['is_attributed']\ngroup = train_smp[['click_hour','is_attributed']].groupby(['click_hour'], as_index=False).count()\nycount = group['is_attributed']\n\n\nfig = plt.figure()\nhost = fig.add_subplot(111)\n\npar1 = host.twinx()\n\nhost.set_xlabel(\"Time\")\nhost.set_ylabel(\"Proportion Converted\")\npar1.set_ylabel(\"Click Count\")\n\n#color1 = plt.cm.viridis(0)\n#color2 = plt.cm.viridis(0.5)\ncolor1 = '#75a1a6'\ncolor2 = '#a675a1'\n\np1, = host.plot(x, ymean, color=color1,label=\"Proportion Converted\")\np2, = par1.plot(x, ycount, color=color2, label=\"Click Count\")\n\nlns = [p1, p2]\nhost.legend(handles=lns, loc='best')\n\nhost.yaxis.label.set_color(p1.get_color())\npar1.yaxis.label.set_color(p2.get_color())\n\nplt.savefig(\"pyplot_multiple_y-axis.png\", bbox_inches='tight')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"91fae80ac4d512ad66e86e4628ab73f782c1860f","_cell_guid":"0b0490cc-7617-477f-b263-2868f144eaf9"},"cell_type":"markdown","source":"The proportions may be more reliable if estimated on full data.  With the random sample it's  hard too tell because the variability is too high, especially for the hours with low click counts.   i.e. the fewer clicks/conversions, the wider margin of the estimated conversion ratio.  (see below)","execution_count":null},{"metadata":{"_uuid":"653cb5fe02d8958e2a8a00231653f6ea365539fd","_cell_guid":"0f201a40-1c22-4a44-a22b-1c31af19f1b0","collapsed":true},"cell_type":"markdown","source":"### Look into attributed_time\nSau cú click ad bao lâu thì app được download?","execution_count":null},{"metadata":{"_uuid":"a6e89dc832959439cc13d49cb72d05c8f4b914c2","_cell_guid":"b9754434-d148-473b-906a-85eed87106dc","trusted":true},"cell_type":"code","source":"train_smp['timePass']= train_smp['attributed_time']-train_smp['click_time']\n#check:\ntrain_smp[train_smp['is_attributed']==1][:15]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"80da5cffae779cbcf4042db15a37966a8d6ed2d9","_cell_guid":"ac59b67f-55cd-4b3b-a28e-7db45ddf1b08","trusted":true},"cell_type":"code","source":"train_smp['timePass'].describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Trong tập dữ liệu sample này có thể thấy khoảng thời gian chênh lệch lâu nhất giữa click và download là gần 13h, thấp nhất là khoảng 2s**\n\nCâu hỏi đặt ra là, liệu có ai có thể vừa xem ad được 2s thì đã download k? Có thể họ biết từ trước (thông qua việc xem cái ad này nhiều lần hoặc nghe ngóng từ nguồn nào đó) hoặc có thể đó là 1 con bot?","execution_count":null},{"metadata":{"_uuid":"6d8444d81775fd74171ac373cc38bbad403aa577","_cell_guid":"96d7a786-e53f-459f-9710-5c9de1605b03"},"cell_type":"markdown","source":"### Check actual train data (the first 10,000,000)\ndouble check the same feature on the first 10 million rows of train data:","execution_count":null},{"metadata":{"_uuid":"76d4c71b12526cc966d35f21647760e6dd087e48","_cell_guid":"b499cfa2-05a9-4e32-ad6e-3cc42250eec6","trusted":true},"cell_type":"code","source":"#check first 10,000,000 of actual train data\ntrain['timePass']= train['attributed_time']-train['click_time']\ntrain['timePass'].describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Trong tập 10 triêu dòng đầu của dữ liệu train, thời gian nhỏ nhất giữa việc click và down giảm xuống 0 và dài nhất thì tăng lên gần 24h**","execution_count":null}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}