{"cells":[{"metadata":{"_uuid":"b6809fb7a6924db9a07b2a51ff3b7892a10ae555","collapsed":true,"_cell_guid":"8d278092-6d3f-45a2-ad13-191dce3acdff","trusted":true},"cell_type":"code","source":"# Importation\nimport pandas as pd\nimport numpy as np\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport gc","execution_count":1,"outputs":[]},{"metadata":{"_uuid":"1cabb8fdb01002ea348611381da004037c422bff","collapsed":true,"_cell_guid":"f7110341-f7d9-44e3-b6bb-a740cff2d17c","trusted":true},"cell_type":"code","source":"# Functions\ndef quantiles (x):\n    for i in range(0, 11):\n        y = i / 10\n        print(\"{0:.0f}\".format(y*100), \"quantile :\", \"{0:.4f}\".format(x.quantile(y)))","execution_count":2,"outputs":[]},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"},"cell_type":"markdown","source":"**One day targeting :**\n\nAs your know, the file is really heavy and our data are spread between 3 days. I really want to analyse a whole day, but I'm not sure about analysing **all** of those days. So, in this second part, we will make a focus on a single day at take a deeper look at our different variables. \nFirstly, we need to convert the click into a time format, then to use it as an index."},{"metadata":{"_uuid":"4b2e0dc4c7e0fda854e1936b0214a68b2a0396a2","_cell_guid":"1014372c-7717-45ee-9983-e79439b4d41e"},"cell_type":"markdown","source":"** I updated importation for a faster version :**"},{"metadata":{"_uuid":"5ecfe437c984febc8bd9c8efd7ef7269e073cc8f","_cell_guid":"ef05f462-420b-4c45-b54c-18abf2262675","trusted":true},"cell_type":"code","source":"# Rows importation\ndf = pd.read_csv('../input/train.csv', skiprows = 9308568, nrows = 59633310)\n\n# Header importation\nheader = pd.read_csv('../input/train.csv', nrows = 0) \ndf.columns = header.columns\ndf\n\n# Cleaning\ndel header\ngc.collect()\n\n# And check his size        \nprint(\"The created dataframe contains\", df.shape[0], \"rows.\")    ","execution_count":3,"outputs":[]},{"metadata":{"_uuid":"6355cc5516f8abe0b3022e121573727511a29fff","_cell_guid":"b0fc5c53-bb56-41e0-877f-9f056d653483"},"cell_type":"markdown","source":"Our dataframe is pretty big. On previous versions of this kernel, I had some problems with the RAM management. That's why, I add some optimization of variables types here. I think we could free a lot of memory with more appropriated datatypes. Thanks for this kernel : https://www.kaggle.com/arjanso/reducing-dataframe-memory-size-by-65/notebook"},{"metadata":{"_uuid":"78a9e3aa048b17670e1a9d1060bd5bdcc7d09e5e","_cell_guid":"a75bf8da-3bf7-44d7-9ec5-580ca8bdf668","trusted":true},"cell_type":"code","source":"total_before_opti = sum(df.memory_usage())\n\n# Type's conversions\ndef conversion (var):\n    if df[var].dtype != object:\n        maxi = df[var].max()\n        if maxi < 255:\n            df[var] = df[var].astype(np.uint8)\n            print(var,\"converted to uint8\")\n        elif maxi < 65535:\n            df[var] = df[var].astype(np.uint16)\n            print(var,\"converted to uint16\")\n        elif maxi < 4294967295:\n            df[var] = df[var].astype(np.uint32)\n            print(var,\"converted to uint32\")\n        else:\n            df[var] = df[var].astype(np.uint64)\n            print(var,\"converted to uint64\")\n    \nfor v in ['ip', 'app', 'device','os', 'channel', 'is_attributed'] :\n    conversion(v)\n\n# Results :    \nprint(\"Memory usage before optimization :\", str(round(total_before_opti/1000000000,2))+'GB')\nprint(\"Memory usage after optimization :\", str(round(sum(df.memory_usage())/1000000000,2))+'GB')\nprint(\"We reduced the dataframe size by\",str(100-round(sum(df.memory_usage())/total_before_opti *100,2))+'%')","execution_count":4,"outputs":[]},{"metadata":{"_uuid":"c2e4738121678bb5c3f211466e3e7960000c604c","_cell_guid":"3b8c1de5-9e2b-4b8f-a7ad-1bfedc0d4993"},"cell_type":"markdown","source":"**How many different values does our categorial variables take ?**"},{"metadata":{"_uuid":"87a468f5e5fcc6c6e66d3f7bad2d44c800325591","scrolled":true,"_cell_guid":"753f2595-9155-4129-8329-c9268e586144","trusted":false,"collapsed":true},"cell_type":"code","source":"print(\"Number of different values :\")\nprint(\"IP :\",len(df['ip'].unique()))\nprint(\"App :\", len(df['app'].unique()))\nprint(\"Device :\", len(df['device'].unique()))\nprint(\"OS :\", len(df['os'].unique()))\nprint(\"Channel :\",len(df['channel'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9b693d943b9f162a98a066cc4c87d43afad27e13","_cell_guid":"5f3f0aaf-e80b-402a-9fd0-eba620044ddd"},"cell_type":"markdown","source":"**What proportion of click generate downloads ?**"},{"metadata":{"_uuid":"6dcc34cf0867b6b38adc93cef568e2ab5356e58e","_cell_guid":"d4b0cd3a-412f-4348-82be-4035f15cba7c","trusted":false,"collapsed":true},"cell_type":"code","source":"print(\"Proportion of click which generate downloads :\")\nprint(df['is_attributed'].value_counts())\n\nplt.figure(figsize=(8,8))\nplt.title('Proportion of click which generate downloads \\n', fontsize =15)\nax =(df['is_attributed'].value_counts(normalize=True)*100).plot(kind='bar')\nfor p in ax.patches:\n    ax.annotate('{:.2f}%'.format(p.get_height()), (p.get_x()+0.15, p.get_height()+1))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"447d5176d227ed665e97f42ffc1f16702578fb28","_cell_guid":"79112098-7177-4349-9571-a3b3090d9374"},"cell_type":"markdown","source":"As we can see, only a very little proportion of clicks generate downloads."},{"metadata":{"_uuid":"fa88617bcb06e7745b8a8be8e4f02e6d1a449a1d","_cell_guid":"486c48d1-4306-407e-9d23-58114353ceb6"},"cell_type":"markdown","source":"**Zoom on this IP :**"},{"metadata":{"_uuid":"a5c44c708409c1111bbed5e0654a4d01f72320c9","_cell_guid":"342ce026-a8a7-4c65-ad2c-a4a244706ef0","trusted":false,"collapsed":true},"cell_type":"code","source":"# We create a dataframe with all IP, and the number of click from this IP\nIP = df['ip'].value_counts()\n\n# We can now take a first look at those IP\nplt.figure(figsize = [10,5])\nsns.boxplot(IP)\nplt.title('Number of click by IP', fontsize =15)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3b4dce5327cf8206d4955f07d2a008eb3155b6b8","_cell_guid":"4d76cfdd-3aee-4b57-814b-acc9a0dcbfdf"},"cell_type":"markdown","source":"Most of the IP have generate only few clicks, but few IP generated **a ton** of clicks. Let's check this in details :"},{"metadata":{"_uuid":"882e696805025a41764bc2b39f00353ed5a7adfc","_cell_guid":"ab9f8351-6e95-4819-81a5-5b193c9dedae","trusted":false,"collapsed":true},"cell_type":"code","source":"IP.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"69bf9f6a8c6fbe7971541845999c17421d318f34","_cell_guid":"ce8d6ca5-503c-44d3-84a9-0373a64b6d74"},"cell_type":"markdown","source":"I really want more informations about those suspicious IP. We're now going to zoom on the most suspicious IP (I arbitrarily set the threshold at 300 clicks)."},{"metadata":{"_uuid":"9de167908fb6017e38aa5619d1c389d212ebb3af","_cell_guid":"e6ab1cef-1bfd-47a9-9479-dc88b24d6694","trusted":false,"collapsed":true},"cell_type":"code","source":"suspicious_IP = IP[IP > 300]\nprint(\"Number of rows selected :\",suspicious_IP.shape[0])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"36567ec8ca5e06feb50e218febcdbf35d1d53cec","_cell_guid":"023a535a-68e1-46f6-81ec-45fdbb1fba07","trusted":false,"collapsed":true},"cell_type":"code","source":"plt.figure(figsize=(10,10))\nsns.distplot(suspicious_IP, hist = False)\nplt.title('Number of clicks by IP (<300 only)', fontsize = 20)\nplt.xlabel('Number of clicks', fontsize = 15)\nplt.ylabel('Frequency', fontsize = 15)\n\n# Cleaning\ndel suspicious_IP\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3a73ebfc5036437679ad3d11afdf578136e3a060","_cell_guid":"a1981a03-588c-45b7-a44e-867189738767"},"cell_type":"markdown","source":"Ok, so even with a threshold of 300 clicks, we can identify some \"big clickers\" and few \"very big clickers\". I want to go a little bit deeper and reproduce the same operation with a threshold set at 30000."},{"metadata":{"_uuid":"178fa68a554d3b4315df53cf43f1fef5b86582b0","_cell_guid":"c19c40df-4048-4af4-b3a2-ad2383f68bef","trusted":false,"collapsed":true},"cell_type":"code","source":"very_suspicious_IP = IP[IP > 30000]\nprint(\"Number of rows selected :\",very_suspicious_IP.shape[0])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0443dd5fb0d3c59a2a4e343ed5ca298262ba82e7","_cell_guid":"22c5f7e5-369d-4c4b-b2ae-b4fc5a6ccf17","trusted":false,"collapsed":true},"cell_type":"code","source":"plt.figure(figsize=(10,10))\nsns.distplot(very_suspicious_IP, hist = False)\nplt.title('Number of clicks by IP (<30 000 only)', fontsize = 20)\nplt.xlabel('Number of clicks', fontsize = 15)\nplt.ylabel('Frequency', fontsize = 15)\n\n# Cleaning\ndel very_suspicious_IP\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"709b0f73e46d943da246ec710971074f1d34ae6b","_cell_guid":"f3e5e394-5fb3-41e8-8d5b-1027cdcda768"},"cell_type":"markdown","source":"Great ! We've now a list of 36 789 suspicious IP, and another of 66 **very** suspicious ! \nNow it's time to leave the IP level and to go back to the click level to see if those suspicious IP really are."},{"metadata":{"_uuid":"0fdf8b6c83a08b58173303902e18fc657ac0202c","collapsed":true,"_cell_guid":"79183820-12f9-461f-8faf-4c6988dea0fd","trusted":false},"cell_type":"code","source":"# We prepare our IP list to the merge\nIP_ready_to_merge = pd.DataFrame(IP).reset_index()\nIP_ready_to_merge.columns=['ip', 'freq_ip']\n\n# Cleaning\ndel IP\ngc.collect()\n\n# Creation of clicker categories (we will use it later)\nIP_ready_to_merge['clicker_type'] = ''\nIP_ready_to_merge.loc[IP_ready_to_merge['freq_ip'] <= 10, 'clicker_type'] = \"very_little_clicker\"\nIP_ready_to_merge.loc[(IP_ready_to_merge['freq_ip'] >= 10) & (IP_ready_to_merge['freq_ip'] < 300), 'clicker_type'] = \"little_clicker\"\nIP_ready_to_merge.loc[(IP_ready_to_merge['freq_ip'] >= 300) & (IP_ready_to_merge['freq_ip'] < 30000), 'clicker_type'] = \"big_clicker\"\nIP_ready_to_merge.loc[IP_ready_to_merge['freq_ip'] >= 30000, 'clicker_type'] = \"huge_clicker\"\n\n# Now we can add our IP frequency to our main dataframe\ndf = pd.merge(df, IP_ready_to_merge, on ='ip')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6559587c453c8db83ff422eb260e831da6381781","_cell_guid":"397118ea-f069-45af-9523-5de5340236cb"},"cell_type":"markdown","source":"**Relation between number of click by IP and downloading the app :**\n\nGreat ! We've our number of clicks variable, then we can do our test."},{"metadata":{"_uuid":"80bdb0f553539c0cf02704e20edc1d84fc114ad3","_cell_guid":"fde226e9-9a68-413f-b86e-fff2af5692e5","trusted":false,"collapsed":true},"cell_type":"code","source":"# Frequencies\nprint('Minimum number of clicks needed to download an app :', df.freq_ip[df['is_attributed']==1].min())\nprint(\"How many IP do we have in each category ?\\n\", IP_ready_to_merge['clicker_type'].value_counts())\nprint(\"How many clicks, clickers of each caterogy have generate ?\\n\",df['clicker_type'].value_counts())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f637edc0b800e45ebac21742390d18e55a61ef40","_cell_guid":"f931b0d7-d411-44c3-b45e-535ca581bd82","trusted":false,"collapsed":true},"cell_type":"code","source":"plt.figure(figsize = (7,7))\ndf['clicker_type'].value_counts().plot(kind='pie', autopct='%1.0f%%')\nplt.title(\"Proportion of clicks generated by each categories of clickers\", fontsize =15)\nplt.ylabel(\"\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"17f37cdefea7b66c3607ce934b7d9fc349947de4","_cell_guid":"32afdb8b-6325-4b69-9034-4b9822ece282"},"cell_type":"markdown","source":"Thereforce, big clickers represents 36 764 IPs (over 145 693) and generate 85% of the clicks and huge clickers represents only 66 IPs but generated 8% of the clicks. Consequently, arround 93% of the clicks are generate by a sub-population of suspicious IPs.\nThis statistical remember what Talking Data said in the overview \"3 billion clicks per day, of which 90% are potentially fraudulent\".\n\nFinaly, it looks like watching at the number of clicks by IPs is a great way of identify bots."},{"metadata":{"_uuid":"46378af832404f8fe1474c67974689b8562df6c0","_cell_guid":"b8078cdd-9558-4e3f-9ff0-9bd47bf4208b"},"cell_type":"markdown","source":"**What proportion of IP download the app ?**"},{"metadata":{"_uuid":"460250198e724522897c0f8e84752bdf1b6794c7","_cell_guid":"f7991cb6-575a-4bb8-bdaf-fa0383becfba","trusted":true},"cell_type":"code","source":"DL_by_IP = df.groupby('ip').is_attributed.sum()\n\nplt.figure(figsize=(8,8))\nplt.title('Proportion of IP which generate downloads at least once \\n', fontsize =15)\nax =((DL_by_IP > 0).value_counts(normalize=True)*100).plot(kind='bar')\n\nfor p in ax.patches:\n    ax.annotate('{:.2f}%'.format(p.get_height()), (p.get_x()+0.15, p.get_height()+1))","execution_count":33,"outputs":[]},{"metadata":{"_uuid":"04686245f24417f03c3ed48609177dda6f738a97","_cell_guid":"58395d74-359c-4678-91ac-ee5028ffed2c"},"cell_type":"markdown","source":"**Does bots download the app ?**"},{"metadata":{"_uuid":"c9630e8bd2e01fd3b53efb12f6dbb2d820c4613f","scrolled":true,"_cell_guid":"d0d22083-d482-46c8-8950-0913ba110b6a","trusted":true},"cell_type":"code","source":"print(DL_by_IP.describe(), '\\n Quantile 99% :',DL_by_IP.quantile(0.99), \\\n      '\\n Quantile 99,9% :',DL_by_IP.quantile(0.999), \\\n      '\\n Quantile 99,999% :',DL_by_IP.quantile(0.9999))","execution_count":32,"outputs":[]},{"metadata":{"_uuid":"4b8b5caaa9b87ac901ddb11ee639f66ae1ceaaa6","_cell_guid":"64fdd633-5604-444e-8dbb-d776f56a4d7d","trusted":true},"cell_type":"code","source":"data_to_plot = DL_by_IP.nlargest(10).reset_index()\ndata_to_plot.columns=('IP', 'Downloads')\ndata_to_plot.sort_values('Downloads', ascending = False)\nplt.figure(figsize = (8,5))\nsns.barplot(x = data_to_plot['Downloads'], y = data_to_plot['IP'], orient = 'h')\nplt.title('Top 10 bigest downloader', fontsize = 15)\n\n# Cleaning\ndel data_to_plot\ngc.collect()","execution_count":31,"outputs":[]},{"metadata":{"_uuid":"88da062d8f81c56c6a9ac43a01920ee0ddb6e902","_cell_guid":"842f4e6a-395e-4be4-a0e0-7412e54a3e5d"},"cell_type":"markdown","source":"**Does standard users download the app ?**"},{"metadata":{"_uuid":"f2841dd695b61b991e74b2624d288dbe0dbdc442","_cell_guid":"5006a61a-936f-434f-a49a-2b2dd8e17fa8","trusted":false,"collapsed":true},"cell_type":"code","source":"# We need a DataFrame\ndata_to_plot2 = pd.DataFrame(DL_by_IP).reset_index()\n\n# We create some categories to plot\ndata_to_plot2[\"cat_DL\"] = ''\ndata_to_plot2.loc[data_to_plot2['is_attributed'] == 0, \"cat_DL\"] = \"No\"\ndata_to_plot2.loc[data_to_plot2['is_attributed'] == 1, \"cat_DL\"] = \"Yes, once\"\ndata_to_plot2.loc[data_to_plot2['is_attributed'] > 1, \"cat_DL\"] = \"Yes, multiple times\"\n\n# We can plot it\nplt.figure(figsize=(8,8))\ndata_to_plot2[\"cat_DL\"].value_counts().plot(kind = 'pie',autopct='%1.0f%%')\nplt.title('Does users download the app ?', fontsize=15)\nplt.ytitle=''","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1492c3660ddd2a3ed7a22c366f7d9553346bbafb","_cell_guid":"36e05d80-15bf-4cda-92cd-7123a7e3f76a","trusted":false,"collapsed":true},"cell_type":"code","source":"data_to_plot3 = data_to_plot2[data_to_plot2['is_attributed'] > 1]\ndata_to_plot4 = data_to_plot3[data_to_plot3['is_attributed'] <= 15]\n\nfig, ax = plt.subplots(1,2, figsize =(15,4))\nax[0].title.set_text(\"Number of downloads by IP (>1)\")\nsns.violinplot(x = data_to_plot3['is_attributed'], ax = ax[0] )\nax[1].title.set_text(\"Number of downloads by IP (1 to 15)\")\nsns.violinplot(x = data_to_plot4['is_attributed'], ax = ax[1])\n\n# Cleaning\ndel data_to_plot3, data_to_plot4\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"85c2944c05e14be8493978bdc135661d9a875ba2","_cell_guid":"a088f37c-f257-42de-b9fc-b882f7f4e779"},"cell_type":"markdown","source":"**How many times, each categories of clickers download the app ?**"},{"metadata":{"_uuid":"1cb1f0c88fcde039bf2f87e1d6da4fedfc1c5ef0","_cell_guid":"b5e431b2-632e-45d2-aa88-96e7cd03bc78","trusted":false,"collapsed":true},"cell_type":"code","source":"ip_level = pd.merge(IP_ready_to_merge, data_to_plot2, on='ip')\ncross_tab = pd.crosstab(ip_level['cat_DL'], ip_level['clicker_type'], normalize='columns')\n\n# We need to sort the index (because 'Yes, one time' was beofre 'Yes, multiple times')\ncross_tab.index = pd.CategoricalIndex(cross_tab.index, categories = ['Yes, multiple times', 'Yes, once', 'No'])\ncross_tab = cross_tab.sort_index(ascending = False)\n\n# Same thing for columns\ncross_tab = cross_tab[['huge_clicker', 'big_clicker', 'little_clicker', 'very_little_clicker']]\n\n# Ok we can make our graph now\nplt.figure(figsize = (8,5))\nplt.title('How many times, each categories of clickers download the app ? \\n', fontsize=15) \nsns.heatmap(cross_tab,annot=True, fmt='.0%')\nplt.xlabel('Categories of clickers ', fontsize = 10)\nplt.ylabel('Categories of downloaders', fontsize = 10)\n\n# Some cleaning\ndel IP_ready_to_merge, data_to_plot2\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a5a720a171e0158f2c8b016443ce1b9643d537e7","_cell_guid":"759f54b1-f626-4bf1-a0f5-d5524671cb27"},"cell_type":"markdown","source":"There is some very interesting information in the graph above : little, and very little clickers (humans) don't download the app, or download it only one time. More they click, more their probability of downloading the app seems high. \n\nOn  another side, big clickers have 38% of chance to download the app multiple times and huge clickers 100%. I guess that my categories are not perfect, the lower bound of 'big clicker' should probably be higher, because, IMO, some standard users (humans) are in this category, and so, have a standard behaviour.\n\nAt this point, I think we should keep in mind that the aim of the competition is to predict if the current click will download or not the app, not the user (IP). Identify suspicious IP is great, but we really need to go deeper."},{"metadata":{"_uuid":"16c419ab282ef289433f87042f19ce7de7f04b76","_cell_guid":"b1d2efc2-f1fb-49e8-b082-20bc92f0e83a"},"cell_type":"markdown","source":"**Download by click ratio :**\n\nI think this indicator could help us to understand what kind of clickers download the app the most, in poportion of there amount of clicks. "},{"metadata":{"_uuid":"0dad3a61610a598e75f7ae5dc9c51a5f53403d48","_cell_guid":"001e0e3a-3482-4d23-bb93-3d797a9b1616","trusted":false,"collapsed":true},"cell_type":"code","source":"# Ratio computation\nip_level['DL_by_click_ratio'] = ip_level['is_attributed']/ip_level['freq_ip']\n\n# Ratio global analysis\nprint(ip_level['DL_by_click_ratio'].describe())\n\nplt.figure(figsize = (6,4))\nsns.violinplot(ip_level['DL_by_click_ratio'])\nplt.title('Ratio : Download by click', fontsize=15)\nplt.xlabel('Download by click')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f65a08019d934a8daae620a2a0e24f57837d10b2","_cell_guid":"d8dba56b-9c15-45be-8049-052fa076d54a"},"cell_type":"markdown","source":"Unsurprised, most of IPs have a low ratio (50% lower than 0.0025 and 75% lower than 0.2). I guess it would be more interesting to check by category of clicker."},{"metadata":{"_uuid":"a899581417e579d27f5561f0ff7e7c4319978ace","_cell_guid":"0bdb03de-832d-4c41-b99c-72c520ce1ea5","trusted":false,"collapsed":true},"cell_type":"code","source":"plt.figure(figsize = (6,4))\nsns.boxplot(ip_level['DL_by_click_ratio'], ip_level['clicker_type'])\nplt.title('Ratio : Download by click', fontsize=15)\nplt.xlabel('Download by click')\nplt.ylabel('Category of clicker')\n\n# Cleaning\ndel ip_level\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bbce4d9c7c010673557d77cd933b41e90029e090","_cell_guid":"3722f566-cb07-4151-9570-b1140426bf89"},"cell_type":"markdown","source":"The relation looks obvious : biger the number of clicks is, lower the ratio is. In other words, bots spam clicks, download the app multiples times, but way less clicks from them lead to downloads.\n\nI think we've enough informations about the relation click / download right now. It's time to look at the 'attributed_time' column which is the time of the downloading click."},{"metadata":{"_uuid":"99235ef7de927df713a8cc96b2035542d752ad53","_cell_guid":"88a974b2-fa56-404b-973c-4fa42a5068fb"},"cell_type":"markdown","source":"**Attributed time analysis :**"},{"metadata":{"_uuid":"3839ee0c9ce0bab3bbbc055c9da09d2534e3d6a2","_cell_guid":"719ac8d9-3f1f-4e69-9157-a10f92ce112a","trusted":false,"collapsed":true},"cell_type":"code","source":"not_missing = df[df['attributed_time'].isna() == False]\nnot_missing['gap'] = pd.to_datetime(not_missing['attributed_time']).sub(pd.to_datetime(not_missing['click_time']))\n\nfor i in range(0, 11):\n        y = i / 10\n        print(\"{0:.0f}\".format(y*100), \"quantile :\", not_missing['gap'].quantile(y))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"575bb549a7c7e9e96c93ca5a93a4b1528b5c175d","_cell_guid":"5655d976-d7bb-4ad2-b9f8-427a63a6a498","trusted":false,"collapsed":true},"cell_type":"code","source":"# By clicker type\nnot_missing.groupby('clicker_type').gap.describe()\n\n# Cleaning\ndel not_missing, df['attributed_time'], df['clicker_type'], df['freq_ip']\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a148298051ac6e8decb3ddceb30eab310d423459","_cell_guid":"c3ab07e2-f06d-49da-b91a-1cad2ec9bbe8"},"cell_type":"markdown","source":"I don't really see anything interesting here."},{"metadata":{"_uuid":"462b048fae1483011a89cc88e457799b5c0d8529","_cell_guid":"dfb1c4f7-7cad-43e7-82b4-342d44962e31"},"cell_type":"markdown","source":"**Back to the click time**\n\nWe've currently analyze the amount of clicks by IP adress but not the click time. Maybe we could find some interesting information here."},{"metadata":{"_uuid":"7dd2cdeb66280aa567f58928181bca4356c98ac5","_cell_guid":"d764d869-93d1-412b-b2df-8c6703519a34","trusted":true,"collapsed":true},"cell_type":"code","source":"df.set_index(pd.to_datetime(df['click_time']), inplace = True)\nby_hour = df.resample('H').ip.count()\n\nplt.figure(figsize = (10,5))\nby_hour.plot()\nplt.title('Number of clicks over the day', fontsize = 15)\nplt.xlabel('Time')\nplt.ylabel('Number of clicks')\n\n# Cleaning\ndel by_hour\ngc.collect()","execution_count":6,"outputs":[]},{"metadata":{"_uuid":"1c2305ead22d5cdde54e9e65314e0d537aee3872","_cell_guid":"97c7135b-bc61-4693-8ae9-097e8b205878"},"cell_type":"markdown","source":"Strangly, the number of clicks decrease during between 3PM and 11PM. We should analyze another day to check this tendancy."},{"metadata":{"_uuid":"40dbf2dd5db675d5900d78defaf233f143e4eb09"},"cell_type":"markdown","source":"**Download rate by hour :**"},{"metadata":{"trusted":true,"_uuid":"f56eea417219e2ab3c4cc90cf9320612b116c868"},"cell_type":"code","source":"plt.figure(figsize = (10,5))\ndf.resample('H').is_attributed.mean().plot()\nplt.title('Download rate evolution over the day', fontsize = 15)\nplt.xlabel('Time')\nplt.ylabel('Download rate')","execution_count":9,"outputs":[]},{"metadata":{"_uuid":"0e17566d710f32d7d6cea2cdcbd2f3681aedb55a","_cell_guid":"2cb5fa23-02ee-4b2f-8262-ebe954659ce2"},"cell_type":"markdown","source":"**It's time to analyze the device : number of clicks by device**\n\nWe already know we have  2265 different devices."},{"metadata":{"_uuid":"18bab08079eccb8b4498045a7109e3f85450ee32","_cell_guid":"25e25a9b-2262-4f7d-aed2-7ac79edeef5b","trusted":false,"collapsed":true},"cell_type":"code","source":"clicks_by_device = df.groupby('device').is_attributed.count()\nquantiles(clicks_by_device)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9a7d7bc52c9455259417e8d72ef9ed95f7442482","_cell_guid":"9fb2f4ad-8fdd-40fe-91b7-56b2534840b5","trusted":false,"collapsed":true},"cell_type":"code","source":"print(\"Number of devices with a number of clicks greater than 100 :\", len(clicks_by_device[clicks_by_device >= 100 ]))\nprint(\"Number of devices with a number of clicks greater than 200 :\", len(clicks_by_device[clicks_by_device >= 200 ]))\nprint(\"Number of devices with a number of clicks greater than 300 :\", len(clicks_by_device[clicks_by_device >= 300 ]))\nprint(\"Number of devices with a number of clicks greater than 1000 :\", len(clicks_by_device[clicks_by_device >= 1000 ]))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7d681184dfe0cc7b2b8804d862cf93ba9174093b","_cell_guid":"ef5a3640-7d34-4006-bd72-e2fe76dc300a"},"cell_type":"markdown","source":"**Same thing for apps : **"},{"metadata":{"_uuid":"85fc884f60fce8a3f3b2b817c80d18d0356110e8","_cell_guid":"ae9fdab8-4627-470d-8e2d-a1fcdfc2904f","trusted":false,"collapsed":true},"cell_type":"code","source":"clicks_by_app = df.groupby('app').is_attributed.count()\nquantiles(clicks_by_app)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"180836c80cca320ea6477e020fff0eac12ce079b","_cell_guid":"0c11c039-47b2-46c9-aeb1-a36aaa8ab6e2"},"cell_type":"markdown","source":"**And OS :**"},{"metadata":{"_uuid":"7b605fe2a02dbdf80871cbe9f87abbd6fe9e5934","_cell_guid":"d18e7882-dada-47b9-b008-a549fef75276","trusted":false,"collapsed":true},"cell_type":"code","source":"clicks_by_os = df.groupby('os').is_attributed.count()\nquantiles(clicks_by_os)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f83aa733d6a5fd1d27dd6d398161344f3aa74a4b","_cell_guid":"8916ffc5-cbd2-42f5-a127-23f27c621a67"},"cell_type":"markdown","source":"**Last click from the IP analysis :**"},{"metadata":{"_uuid":"7e40fb1fc48f2786308cb17a10771019d8c2cb86","_cell_guid":"5316dd13-653e-4382-a86a-aabc06c42f39","trusted":false,"collapsed":true},"cell_type":"code","source":"df['id'] = range(1, len(df) + 1)\nlast_clicks = df.groupby('ip').id.last().reset_index()\nlast_clicks['last_click'] = 1\nlast_clicks.drop('ip', axis=1, inplace = True)\n\ndf = pd.merge(df, last_clicks, on ='id', how = 'left').set_index(pd.to_datetime(df['click_time']))\ndf['last_click'].fillna(0, inplace = True)\nconversion('last_click')\n\n# Cleaning\ndel df['id'], df['click_time'], last_clicks\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ade5769255e4a782ab2cacf46155ce8ce7c1ad8a","_cell_guid":"3b40c286-8848-449e-8f57-8211dd8c0635"},"cell_type":"markdown","source":"**Do the last click generate more download ?**"},{"metadata":{"_uuid":"ddcdcf378e293d7ae141cee39bb0bcdf35b5f1be","_cell_guid":"ecf10ebd-2e89-472c-8bd7-6dee09ff2b85","trusted":false,"collapsed":true},"cell_type":"code","source":"print(\"Download rate for last clicks :\", str(round(df[df['last_click'] == 1].is_attributed.mean()*100,2))+'%')\nprint(\"Download rate for all clicks :\", str(round(df.is_attributed.mean()*100,2))+'%')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e52c73c6aff3457f9ffeb657b1cb36b40f9b14f7","_cell_guid":"25a9cf71-f254-46a8-9a01-bf25f2dce04d"},"cell_type":"markdown","source":"Great ! I would include this variable in my features engineering for sure."},{"metadata":{"_uuid":"f02bca753d4b122178e80e607e93388a59cd0080","_cell_guid":"6e9b0e04-f3d5-45a9-ac86-ef0a77de6bd6"},"cell_type":"markdown","source":"**Number of clicks from the IP during the last minute**"},{"metadata":{"_uuid":"86474571046c9312d88757c3d2905a5ac028895e","_cell_guid":"ec1fc3f3-c366-4ea9-b78a-d49f299cd2d5","trusted":false,"collapsed":true},"cell_type":"code","source":"# We firstly need to sort our data in the right order\ndf['click_time'] = df.index\ndf.sort_values(['click_time', 'ip'], inplace = True)\n\n# We can now compute the number of clicks during the last minute\nclicks_minute = pd.DataFrame(df.groupby('ip')['app'].rolling('min').count())\n\n# We can't use a pd.merge because it takes to much memory\nclicks_minute.reset_index(inplace = True)\nclicks_minute.sort_values(['click_time', 'ip'], inplace = True)\n\n# Like our two dataset are in the same order, we can make a simple concatenation\ndel clicks_minute['click_time'], clicks_minute['ip'], df['click_time']\ngc.collect()\ndf['clicks_minute'] = clicks_minute.values\n\n# Conversion\nconversion('clicks_minute')\n\n# Cleaning\ndel clicks_minute\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"44ba4829786c1386e6589a0c0b9ed77630461360","_cell_guid":"848d8768-0dbb-4bda-aaf7-3d28f62d9634","trusted":false,"collapsed":true},"cell_type":"code","source":"df['temp_cats'] = ''\ndf.loc[df['clicks_minute'] <= 10, 'temp_cats'] = 'less than 10'\ndf.loc[(df['clicks_minute'] > 10) & (df['clicks_minute'] < 100), 'temp_cats'] = 'between 10 and 100'\ndf.loc[df['clicks_minute'] >= 100, 'temp_cats'] = 'more than 100'\n\ny = df.groupby('temp_cats').is_attributed.mean()\ny = y[['less than 10', 'between 10 and 100', 'more than 100']]\n\nplt.figure(figsize =(6,4))\nplt.title('Download rate by number of clicks during the last minute\\n', fontsize = 15)\ny.plot(kind = 'bar', width = 0.8)\nplt.xlabel('')","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}