{"cells":[{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":false,"_kg_hide-output":false,"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","collapsed":true,"trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport gc\nimport seaborn as sns\nimport matplotlib.pyplot as plt\n%matplotlib inline\n\nsns.set(rc={'figure.figsize':(14,6)});\nplt.figure(figsize=(14,6));\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"27d1401f-2a47-45e2-b9e7-7aadcc9656f4","_uuid":"6b69a1f35c596f73baf96d4c4e055add2d77fc1f","collapsed":true},"cell_type":"markdown","source":"The datasets are very huge in this competition (184.9m rows in the training dataset and 18.7m rows in the testing dataset) so i'll only use some chunks of the dataset.\nI am reading the dataset in chunks of 4m (46 chunks) then taking 1 chunk every 6 chunks. This means i'll only use 8 chunks from the training (32m rows)."},{"metadata":{"_cell_guid":"6f32cce3-8ec8-4643-98f8-e57afaa8f36c","_uuid":"cdbad2b6bf3be135406e2950604a2f06a43d76df","collapsed":true,"trusted":true},"cell_type":"code","source":"# Manually setting the types of columns reduces the memory usage by ~x2.7\ndtypes = {\n    'ip': 'uint32',\n    'app': 'uint16',\n    'device': 'uint16',\n    'os': 'uint16',\n    'channel': 'uint16',\n    'is_attributed': 'uint8',\n    'click_id': 'uint32' # for test data\n}\n\n# Read the training data as chunks of 4m\nprint('Reading the train.csv..')\nreader = pd.read_csv('../input/train.csv', dtype=dtypes, chunksize=4000000,\n                     usecols=['ip', 'app', 'device', 'os', 'channel', 'click_time', 'is_attributed'],\n                     parse_dates=['click_time'])\nchunks = [chunk for chunk in reader]\n\nprint('Selecting the train chunks to use..')\nchunks_to_use = [chunks[x] for x in np.arange(0, len(chunks), 6)]\nprint('Selected {} chunks.'.format(len(chunks_to_use)))\ntrain_df = pd.concat(chunks_to_use, ignore_index=True)\nprint('train_df created.')\nprint(train_df.info())\n\ndel reader, chunks, chunks_to_use\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"f9112ae9-94dc-453b-82c2-fa2913d02b89","_uuid":"aa42185ba41356aa0fcf47a1a7060df8cf5f326b"},"cell_type":"markdown","source":"**Percentage of attributed clicks**"},{"metadata":{"_cell_guid":"b816e6f4-420f-4596-918b-0444979db6c3","_uuid":"48c948b0b1c37d33c98269521910b321bd7aa80d","collapsed":true,"trusted":true},"cell_type":"code","source":"print('~{:.2f}%'.format(len(train_df[train_df['is_attributed'] == 1]) * 100 / len(train_df)))","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"7c4f3abe-24ec-44e1-a935-d68b58fbd35d","_uuid":"549ea5584fbf73ef69a98a416d0e4a2c8c9b4e8c"},"cell_type":"markdown","source":"Only ~0.24% of clicks were attributed, this is very very low."},{"metadata":{"_cell_guid":"2c5b7afa-85e2-4749-be1f-cc28ca251082","_uuid":"e14a964ca2aa9e3a91568e8b6a58789cdbfd16c1"},"cell_type":"markdown","source":"**Merging the test dataset with the train dataset**"},{"metadata":{"_cell_guid":"cbf298e8-a910-4c38-a9ff-b507f4d5bfb5","_uuid":"18bb4df4e5aa8913e57eceeb09c70d799d69d037"},"cell_type":"markdown","source":"I saw a lot of kernels merging the test dataset with the train dataset so i'll do that too.\n*I read some disscusions about this and they say its \"okay\" (unless it's not and somebody can explain why please).*"},{"metadata":{"_cell_guid":"53246dc3-80f7-4d72-9594-4c1659fb0b58","_uuid":"0014435e9300cc0cd0c7fb737f4cf82bd31a55d6","collapsed":true,"trusted":true},"cell_type":"code","source":"test_df = pd.read_csv('../input/test.csv', dtype=dtypes, parse_dates=['click_time'])\ndata = train_df.append(test_df)\nprint('data created.')\n\ntrain_df_len = len(train_df)\ndel train_df, test_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"1a166a61-7516-4f75-b8a9-8b3905e2204b","_uuid":"e6d44e21ba55e94b6e3efef5b9fb34e0372967d1","collapsed":true,"scrolled":true,"trusted":true},"cell_type":"code","source":"data.info()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"e7f4301f-ca62-4a1b-b531-94d2b529f598","_uuid":"a4a4117250c0b7b6f71e67540b1f3d3fb52e3ade"},"cell_type":"markdown","source":"**Missing values**"},{"metadata":{"_cell_guid":"70be5316-7306-48cd-8ab3-f2ca158fa5b4","_uuid":"b0ca5c6f6f944a13e8d4a1adf46ada9a80917645","collapsed":true,"trusted":true},"cell_type":"code","source":"data[['app', 'channel', 'click_time', 'device', 'ip', 'os']].isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"cfe5b2a9-a656-437f-aa8b-ce0e360e84ef","_uuid":"06c1f094fcac0136e8d18b381be6f19b292ddca9"},"cell_type":"markdown","source":"There is no missing values in our data, this is good!"},{"metadata":{"_cell_guid":"4676c45d-e0e0-49bb-a6d5-35af8ef24393","_uuid":"1c080e6a6249ff6c6bcb78297a7d0846ddd77291"},"cell_type":"markdown","source":"**Unique values count**"},{"metadata":{"_cell_guid":"ef1da166-1fd3-4907-b9f9-ba6731b971ee","_uuid":"1dec6da58ef3774fe395e23030385c901a6f5672"},"cell_type":"markdown","source":"Calculate the count of unique values "},{"metadata":{"_cell_guid":"7b34fbad-4dbf-443a-8f89-bf7726f7db45","_uuid":"b9e5b79df478a052e9462db38c6959bf3451cdc8","collapsed":true,"trusted":true},"cell_type":"code","source":"unique_counts = data[['ip', 'app', 'channel', 'device', 'os']].apply(lambda x: x.unique().shape[0])\nprint(unique_counts)\nplt.bar(unique_counts.index.values, unique_counts)\n\ndel unique_counts\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"efd7d545-6add-4a4f-b89c-fc62948d6a21","_uuid":"5e7b2e4825023cab4de168ffc74a065d957a1b5f"},"cell_type":"markdown","source":"There are only 210217 ips, this means that a lot of clicks come from the same ip. This shows the possiblity that some ips were used to make fraudulant clicks, but it's not a good evidence since multiple people can have the same IP (for example in the same house with the same box)."},{"metadata":{"_cell_guid":"e05f75be-296c-4d92-b1ea-8d21e54c2ef8","_uuid":"e0e1dfdb09938c96f6e155559ad72a5bb2dca19f"},"cell_type":"markdown","source":"**Extracting time features**"},{"metadata":{"_cell_guid":"56c70ecc-c3f9-41e4-84e7-ac80ba0f1193","_uuid":"0f720d79e5486cdb03f976280ba92ac0eef8ecbf"},"cell_type":"markdown","source":"Before we begin analyzing the data, i'll extract some time features from click_time (taking into account the local time)."},{"metadata":{"_cell_guid":"e0cb47d3-cf8b-40b0-bc46-96be74335d57","_uuid":"556d362def02f4aa5312c617b00fec2977924458","collapsed":true,"trusted":true},"cell_type":"code","source":"import pytz\ncst = pytz.timezone('Asia/Shanghai')\ndata['local_click_time'] = data['click_time'].dt.tz_localize(pytz.utc).dt.tz_convert(cst)\ndata['click_day'] = data['local_click_time'].dt.day.astype('uint8')\ndata['click_hour'] = data['local_click_time'].dt.hour.astype('uint8')\ndata.drop(['click_time', 'local_click_time'], axis=1, inplace=True)\nprint('Extracted time features.')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"e85602f9-3775-4196-9e92-de56d6fecf0d","_uuid":"635f6b2d96ea48df6a6d889b4e1bffd16a5a2f59"},"cell_type":"markdown","source":"**Number of clicks per ip**"},{"metadata":{"_cell_guid":"be3153f9-e1d2-4e3a-a6e7-a18f5c6ed8ae","_uuid":"03486c6a8e3525896a9adaf272cbf07941dc872b"},"cell_type":"markdown","source":"Let's see if there is anything weird about the ips."},{"metadata":{"_cell_guid":"9877877f-c0e7-4b93-a917-a9c03da8464d","_uuid":"f968db8cbd2e837fca0d0c56636bd2fd5ff5ed4a","collapsed":true,"scrolled":true,"trusted":true},"cell_type":"code","source":"clicks_per_ip = data['ip'].value_counts()[:20]\nsns.barplot(clicks_per_ip.index.values, clicks_per_ip.values)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"b345353a-02f2-47b9-b0b9-b3748f87753b","_uuid":"942bcd9764cf1f9b10f1e13a4886c524dbc9c3e2"},"cell_type":"markdown","source":"Ips 5348 and 5314 have a huge number of clicks (350k+), let's see how they are distributed throughout the hours."},{"metadata":{"_cell_guid":"2c0cb25b-f4ba-4c4a-9e78-7ad145d5d481","_uuid":"3db66f227147fd0e3df5ffb16122967abdf98227","collapsed":true,"trusted":true},"cell_type":"code","source":"most_clicked_ips = clicks_per_ip[:2].index.values\nfig, axes = plt.subplots(1, 2)\n\nfor i in range(len(most_clicked_ips)):\n    temp_df = data[['ip', 'click_hour']][data['ip'] == most_clicked_ips[i]]\n    sns.countplot(x='click_hour', data=temp_df, ax=axes[i])\n    axes[i].set_title(most_clicked_ips[i])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"15cfcd0f-9aef-4173-b5be-ee9bc1d14140","_uuid":"435fdd3dac16ed603ae9d73a6172a725ce832066"},"cell_type":"markdown","source":"These ips are generating a lot of clicks almost every hour (*they also seem to have almost the same number of clicks per hour, weird..*). "},{"metadata":{"_cell_guid":"9d17e736-e3ba-4967-9919-3a98c6421038","_uuid":"276ff8d54395d060a53602e7de7d1bbeb5568d05"},"cell_type":"markdown","source":"**Downloads count for ips 5348 and 5314**"},{"metadata":{"_cell_guid":"e4fcee8d-2bc7-41fd-b7ac-1e06e17e7310","_uuid":"7224476be9d8d764015a9fb763efd31204d6a68c"},"cell_type":"markdown","source":"Since the ips that generate the most clicks, let's see how many times they actually downloaded the app."},{"metadata":{"_cell_guid":"726b1a0e-da0e-49e0-8848-6d5578167146","_uuid":"13d73537f9a1834dcb6bf2e6520a91c36c1ca191","collapsed":true,"scrolled":false,"trusted":true},"cell_type":"code","source":"ips_download_counts = data[['ip', 'app', 'is_attributed']][data['ip'].isin(most_clicked_ips)].groupby('ip').agg({ 'app': 'count', 'is_attributed': 'sum'})\nips_download_counts.rename(columns={'app': 'click_count', 'is_attributed': 'download_count'}, inplace=True)\nips_download_counts['download_rate'] = ips_download_counts['download_count'] * 100 / ips_download_counts['click_count']\nips_download_counts","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"82ddff0f-f3df-482a-b1c3-19a3de8ac3a8","_uuid":"573a55b88d48d496d33594dc901790ab23b64334"},"cell_type":"markdown","source":"So out of 374k and 406k clicks, they only downloaded the app 4xx times. This doesn't look legit. How many devices did these ips use?"},{"metadata":{"_cell_guid":"ed8b1cfc-c1ca-482f-bcaa-be182d6d7c6c","_uuid":"e0a169a1168b72452e9baa84246f164c18c45f89","collapsed":true,"trusted":true},"cell_type":"code","source":"data[['ip', 'device']][data['ip'].isin(most_clicked_ips)].groupby('ip')['device'].nunique()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"836ed551-946b-456b-be8f-b2338ffcf65e","_uuid":"e84fa6ef16a85a19b3c9e92eccd5a551d2f4ada6"},"cell_type":"markdown","source":"They only use 303 and 289 devices respectively..."},{"metadata":{"_cell_guid":"ed1cebf5-5f6d-4aaf-b12d-a5bac83e9c48","_uuid":"f08d053d3566be0b81dec9d6c169f7f4e8d66332"},"cell_type":"markdown","source":"**[★ New Feature] Number of clicks per ip**"},{"metadata":{"_cell_guid":"065c2aee-1bbd-41f2-a8ad-ac04173fac8f","_uuid":"0c73ec1c2ef40dc41958f122ac8ba247be416e50","collapsed":true,"trusted":true},"cell_type":"code","source":"temp_col = data[['ip', 'channel']].groupby('ip').count().reset_index().rename(columns={'channel': 'ip_count'}).astype('uint32')\ndata = data.merge(temp_col, on='ip', how='left')\n\ndel temp_col\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"627c179b-10fc-4b72-b51d-ae079252485b","_uuid":"ef557954a9c6a904c16d2f1276b52ec59e19d4fd"},"cell_type":"markdown","source":"**Number of clicks per day per ip**"},{"metadata":{"_cell_guid":"e2b8efe1-9a47-44ac-b60c-d2a4b5fcd0f9","_uuid":"7736d2a06e7efc79fad24458a1f1a21ba3d714f8","collapsed":true,"trusted":true},"cell_type":"code","source":"clicks_per_day_per_ip = data[['click_day', 'ip', 'channel']][data['ip'].isin(most_clicked_ips)].groupby(['click_day', 'ip']).count().rename(columns={'channel': 'count'})\nclicks_per_day_per_ip.unstack().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"9efe0b8f-15fb-4be3-b796-c031e540c4b8","_uuid":"4de51a13eddf67272b6aeb1e2fac4ac795bfe40e"},"cell_type":"markdown","source":"**Number of clicks per hour per ip**"},{"metadata":{"_cell_guid":"842daeec-7a0a-439e-af70-3e3061c7ab86","_uuid":"4789a510b5c081ccc5f40f20cd6a30201e3fcff1","collapsed":true,"trusted":true},"cell_type":"code","source":"clicks_per_hour_per_ip = data[['click_hour', 'ip', 'channel']][data['ip'].isin(most_clicked_ips)].groupby(['click_hour', 'ip']).count().rename(columns={'channel': 'count'})\nclicks_per_hour_per_ip.unstack().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"59b4c23b-12ca-4fd5-a375-e99abac64de8","_uuid":"2543be799a555b56819dc0d7c2e95b1fe50dc4ab"},"cell_type":"markdown","source":"**[★ New Feature] Number of clicks per day per hour per ip**"},{"metadata":{"_cell_guid":"597c6c38-cff6-45f3-ae39-ea3e73d72b00","_uuid":"e80ec3370fe0277417178da3e91ce368a348c868","collapsed":true,"trusted":true},"cell_type":"code","source":"temp_col = data[['click_day', 'click_hour', 'ip', 'channel']].groupby(['click_day', 'click_hour', 'ip']).count().reset_index().rename(columns={'channel': 'day_hour_ip_count'}).astype('uint32')\ndata = data.merge(temp_col, on=['click_day', 'click_hour', 'ip'], how='left')\n\ndel temp_col\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"59133852-e32f-4e0f-beb3-3c6e30e0bff5","_uuid":"f7fafe1656931141ea13843d2b0aa25a031938e2","collapsed":true,"trusted":true},"cell_type":"code","source":"del clicks_per_ip, ips_download_counts, clicks_per_day_per_ip, clicks_per_hour_per_ip\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"95eefce6-7171-4600-8b95-da0406c5861c","_uuid":"30a978af33c4b24c2afd4922d89fd51d78303e98"},"cell_type":"markdown","source":"**What about the devices?**"},{"metadata":{"_cell_guid":"1af4b201-fe32-4504-8e11-c19a7962ab23","_uuid":"c12989a992787247fe23353908ff09bc76ffbbcb"},"cell_type":"markdown","source":"As we saw, the two most used ips (5348 and 5314) only use 4xx devices out of 2552. Considering the number of clicks they generated, it doesn't look trustworthy. Let's look into the devices closer."},{"metadata":{"_cell_guid":"0a731513-6ec5-4eb7-ae4a-53063ab99a41","_uuid":"b2ec0285b7c863bb0439e3fed317e81f5e5fd52e"},"cell_type":"markdown","source":"**Number of clicks per device**"},{"metadata":{"_cell_guid":"3aac35d8-76aa-4560-a814-62e67a8e0fbb","_uuid":"ba41ddcbbffd315f52e487f2c41436a6e39906fc","collapsed":true,"trusted":true},"cell_type":"code","source":"clicks_per_device = data['device'].value_counts()[:10]\nprint(clicks_per_device)\nsns.barplot(clicks_per_device.index.values, clicks_per_device.values)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"20868f21-47c2-4e2d-9f65-d22e11e81fab","_uuid":"ac494f2dd49b073d13cf68d932ce9652aa563ef3"},"cell_type":"markdown","source":"Device 1 is the most used (47.4m) followed by Device 2 (2.5m)."},{"metadata":{"_cell_guid":"2d96ae28-ca14-4f0d-ab4e-6641a8e84011","_uuid":"f65a12903865cca29a2c5bdf4dcaa657ec0c254f"},"cell_type":"markdown","source":"**Devices used by the most used ips**"},{"metadata":{"_cell_guid":"1fa2b1cc-6625-4968-9fea-040e0a0f4868","_uuid":"24d4504008eac2b933a4983492c8c064b8cb5687","collapsed":true,"trusted":true},"cell_type":"code","source":"most_used_devices = clicks_per_device.index.values\nfig, axes = plt.subplots(2, 1)\n\nfor i in range(len(most_clicked_ips)):   \n    temp_df = data[['ip', 'device']][data['ip'] == most_clicked_ips[i]]\n    sns.countplot(x='device', data=temp_df, ax=axes[i], order=most_used_devices)\n    axes[i].set_title(most_clicked_ips[i])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"05af7afe-8e0e-4cbd-90a3-a14ba690c261","_uuid":"16276bdbf5ed4475964ff21da8edd03774c3da5b"},"cell_type":"markdown","source":"Almost all their clicks are done using Device 1 and Device 2. I'll go ahead and add a feature representing the number of clicks per ip per device."},{"metadata":{"_cell_guid":"b956642f-c566-4104-b5a0-36cd8c01035f","_uuid":"5f99c5bfd46a9206f40e5a9b5794e6c0e0f5ec84","collapsed":true,"trusted":true},"cell_type":"code","source":"data[['ip', 'device', 'channel']].groupby(['ip', 'device'])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"4bb29094-933a-47a7-a021-edecb67e0cdf","_uuid":"f2d11fce28399a5dea410573531eaa20d6dadcf1"},"cell_type":"markdown","source":"**Download counts for the top 10 devices**"},{"metadata":{"_cell_guid":"1c7f36b5-a08d-41af-a6b2-f172595a3ab1","_uuid":"717dd4722c6f51d2bafc23df0e7beb0096c55973","collapsed":true,"trusted":true},"cell_type":"code","source":"devices_download_counts = data[['device', 'ip', 'is_attributed']][data['device'].isin(most_used_devices)].groupby('device').agg({ 'ip': 'count', 'is_attributed': 'sum'})\ndevices_download_counts.rename(columns={'ip': 'click_count', 'is_attributed': 'download_count'}, inplace=True)\ndevices_download_counts['download_rate'] = devices_download_counts['download_count'] * 100 / devices_download_counts['click_count']\ndevices_download_counts","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"c5f90d03-ed2b-42cf-93bd-56e6fe61c8d8","_uuid":"4ef438e2b52520a976750c1fbd8d5c4c78e109cd"},"cell_type":"markdown","source":"47.4m clicks on Device 1 but only 0.1% downloads, 2.5m clicks on Device 2 but only 0.01% downloads. Not only the ips we were suspecting are using these devices a lot, but also the download rate is very low.."},{"metadata":{"_cell_guid":"b1b7376a-afe8-439d-9a78-4c72c343312a","_uuid":"173fb3428def79da2ad54c079843314c5a02a5d0","collapsed":true,"trusted":true},"cell_type":"code","source":"del clicks_per_device, devices_download_counts\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"1073a331-f379-45e2-b6a1-8f607e83b02e","_uuid":"dbdc13f9a2efb90e98d17eaa512e5282926836a2"},"cell_type":"markdown","source":"**[★ New Feature] Number of clicks per ip and device**"},{"metadata":{"_cell_guid":"81f3fd14-4adb-4217-8e80-c89785712d36","_uuid":"acd7db9808a35e7f428fe4f9199f6118da238c1f","collapsed":true,"trusted":true},"cell_type":"code","source":"temp_col = data[['ip', 'device', 'channel']].groupby(['ip', 'device']).count().reset_index().rename(columns={'channel': 'ip_device_count'}).astype('uint32')\ndata = data.merge(temp_col, on=['ip', 'device'], how='left')\n\ndel temp_col\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"f0fc6d9a-3050-45c9-8f86-4dd673d0cac9","_uuid":"2ccc8a15203295997de5c6de78bbb37c019d0acf","collapsed":true},"cell_type":"markdown","source":"**What about the apps?**"},{"metadata":{"_cell_guid":"bb0cf2b7-0e3a-48cb-99d3-21014d18ae8b","_uuid":"e487bb3cc89ea7142d9228418db4085febb99fe4","collapsed":true},"cell_type":"markdown","source":"If there is someone who's generating fraudulent clicks, usually they'll focus on one app, let's see if that's true."},{"metadata":{"_cell_guid":"1f2fcec1-0165-4163-b0c6-8d10d50671ee","_uuid":"6b3fa236881c09a45c2ccfa318d2fbba5ce88f3d","collapsed":true,"trusted":true},"cell_type":"code","source":"clicks_per_app = data[['app', 'channel']].groupby('app').count().sort_values('channel', ascending=False)['channel']\nprint('Top 10', clicks_per_app[:10])\nplt.scatter(clicks_per_app.index, clicks_per_app)\nplt.xlabel('app')\nplt.ylabel('count')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"0f1610cd-d390-4051-92c4-b00d39f0e50c","_uuid":"bbf9cc43da7231ee5c23d677aeebc8ce7ab998aa"},"cell_type":"markdown","source":"As we can see, some of the apps have a lot more clicks than the others. Either they are very popular apps or targeted apps."},{"metadata":{"_cell_guid":"ce92ad6d-daed-4b99-8868-90042ad324c8","_uuid":"bbcf8248c57facaa7ab8d8ab0c830009743ea0ce"},"cell_type":"markdown","source":"**Number of clicks per day per app ( top 10 apps)**"},{"metadata":{"_cell_guid":"df43f911-749c-4cd4-9df0-f6a8e993d044","_uuid":"39d101b57f52682f05bed9f18dadec2d53d556af","collapsed":true,"trusted":true},"cell_type":"code","source":"most_used_apps = clicks_per_app[:10].index.values\nclicks_per_day_per_app = data[['click_day', 'app', 'ip']][data['app'].isin(most_used_apps)].groupby(['click_day', 'app']).count().rename(columns={'ip': 'count'})\nclicks_per_day_per_app.unstack().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"8c50827a-a0b4-4141-9808-e3fd3d3e35b1","_uuid":"de846140dd04cf50d47dbc9483a998f490feafdd"},"cell_type":"markdown","source":"**Number of clicks per hour per app ( top 10 apps)**"},{"metadata":{"_cell_guid":"5e825a84-11d7-46b3-b9ff-b82f1f6c263b","_uuid":"9f5fdefda715f9bd6730be93f757beedae52edea","collapsed":true,"trusted":true},"cell_type":"code","source":"clicks_per_hour_per_app = data[['click_hour', 'app', 'ip']][data['app'].isin(most_used_apps[:6])].groupby(['click_hour', 'app']).count().rename(columns={'ip': 'count'})\nclicks_per_hour_per_app.unstack().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"87452712-e98c-4dc2-bcf1-9efbda12ccbc","_uuid":"7283ec1cf9501cb61f37b6899c8800c28b053d6b"},"cell_type":"markdown","source":"**Download counts for the top 10 apps**"},{"metadata":{"_cell_guid":"3c87fe36-6370-43ff-91de-b85be2286a85","_uuid":"2f3801a01c9a6e522f4c0dc86d067cc5f24d9d9e","collapsed":true,"trusted":true},"cell_type":"code","source":"apps_download_counts = data[['app', 'ip', 'is_attributed']][data['app'].isin(most_used_apps)].groupby('app').agg({ 'ip': 'count', 'is_attributed': 'sum'})\napps_download_counts.rename(columns={'ip': 'click_count', 'is_attributed': 'download_count'}, inplace=True)\napps_download_counts['download_rate'] = apps_download_counts['download_count'] * 100 / apps_download_counts['click_count']\napps_download_counts.sort_values('click_count', ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"c00abd3d-1134-4049-b718-0fcbffe8d5d7","_uuid":"ff49ea61c98484e543faf321395766108d300e2b"},"cell_type":"markdown","source":"The most used apps have a very low download rate (especially App 12 with only 0.006% out of 6.5m clicks). This enforces the guess of targeted apps for fraud."},{"metadata":{"_cell_guid":"a8138212-292f-43b6-84b3-93dc4e515ab8","_uuid":"bb1dacc240a64d928eb2e4e7f142d2ceb7cde07a"},"cell_type":"markdown","source":"**[★ New Feature] Number of clicks per day per hour**"},{"metadata":{"_cell_guid":"69e2cdca-0f3e-4c78-84f4-47d404829ff1","_uuid":"c61ad0b4c3a1cfa2b269e3c057e1f22525b35ed1","collapsed":true,"trusted":true},"cell_type":"code","source":"temp_col = data[['click_day', 'click_hour', 'app', 'channel']].groupby(['click_day', 'click_hour', 'app']).count().reset_index().rename(columns={'channel': 'day_hour_app_count'}).astype('uint32')\ndata = data.merge(temp_col, on=['click_day', 'click_hour', 'app'], how='left')\n\ndel temp_col\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"d0a95541-87d8-4f40-a7d3-ee5032e52d45","_uuid":"ab8d3969ad418e19b17f0fc6c36001ac46f2b234","collapsed":true,"trusted":true},"cell_type":"code","source":"del clicks_per_app, clicks_per_day_per_app, clicks_per_hour_per_app, apps_download_counts\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"df750a79-bfe3-4e10-b9a6-c90bbfbc2d02","_uuid":"ef4a32f65ff60bbb3f1d3886e01c9defb0531927"},"cell_type":"markdown","source":"**Are the channels the same as apps? Are there channels way more used than the rest?**"},{"metadata":{"_cell_guid":"a0206eec-aced-47b6-9155-d6e14383c2b6","_uuid":"0ce2f63b4b560e1211e731b8c3b09788fc7d9e7c","collapsed":true,"trusted":true},"cell_type":"code","source":"clicks_per_channel = data[['app', 'channel']].groupby('channel').count().sort_values('app', ascending=False)['app']\nprint('Top 10', clicks_per_channel[:10])\nplt.scatter(clicks_per_channel.index, clicks_per_channel)\nplt.xlabel('channel')\nplt.ylabel('count')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"03559f4a-7c8e-41fa-8417-0b11dcc02f29","_uuid":"383c2266cb0628239768760637c97839e893135e"},"cell_type":"markdown","source":"Some of the channels have more clicks than the others (mainly 280, 107)."},{"metadata":{"_cell_guid":"e4fc2220-0513-4e09-8c07-23c26588d6e6","_uuid":"473a2aee6fb63aaec9f554a9487c9c7200f27eba"},"cell_type":"markdown","source":"**Number of clicks per app per channel**"},{"metadata":{"_cell_guid":"97a2a4a5-1797-4ac9-bed6-d987fcf9a44a","_uuid":"b58ff56fa5f9db9f1f802d10daa2be4df1289954","collapsed":true,"trusted":true},"cell_type":"code","source":"most_used_channels = clicks_per_channel[:10].index.values\nclicks_per_app_per_channel = data[['app', 'channel', 'ip']][data['channel'].isin(most_used_channels[:6])][data['app'].isin(most_used_apps)].groupby(['app', 'channel']).count().rename(columns={'ip': 'count'})\nclicks_per_app_per_channel.unstack().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"8cb3106d-bbf9-46ee-b3c8-964c8a9e0554","_uuid":"4f0c946f38da70ea12945d9fd9e69ac10061a3c5"},"cell_type":"markdown","source":"Looks like some channels are only used by some apps."},{"metadata":{"_cell_guid":"7ef19048-106b-44d1-896d-09f9596a607f","_uuid":"86c27defbe859200d9a29bf8ee6e8e313927823b"},"cell_type":"markdown","source":"**Number of downloads per channel**"},{"metadata":{"_cell_guid":"b9ab97af-823e-4396-aead-2f0fff04ff51","_uuid":"b75dc42fc2f1f1274c75416a63cd0806de275500","collapsed":true,"trusted":true},"cell_type":"code","source":"channel_download_counts = data[['channel', 'ip', 'is_attributed']][data['channel'].isin(most_used_channels)].groupby('channel').agg({ 'ip': 'count', 'is_attributed': 'sum'})\nchannel_download_counts.rename(columns={'ip': 'click_count', 'is_attributed': 'download_count'}, inplace=True)\nchannel_download_counts['download_rate'] = channel_download_counts['download_count'] * 100 / channel_download_counts['click_count']\nchannel_download_counts.sort_values('click_count', ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"8602e720-b380-4dfa-b504-64ead88b7e08","_uuid":"f10bf6593684bcb64421960a033fea2853e32a1d"},"cell_type":"markdown","source":"**[★ New Feature] Number of clicks per app per channel**"},{"metadata":{"_cell_guid":"631f9fcf-180e-4c49-9bea-1876ea0fbb59","_uuid":"3aae0a6dd7510b1fad89092b97da0d56f8b36950","collapsed":true,"trusted":true},"cell_type":"code","source":"temp_col = data[['app', 'channel', 'ip']].groupby(['app', 'channel']).count().reset_index().rename(columns={'ip': 'app_channel_count'}).astype('uint32')\ndata = data.merge(temp_col, on=['app', 'channel'], how='left')\n\ndel temp_col\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"3161a4d7-d59f-40a8-abde-ca932bd1f2a7","_uuid":"f9665696fc71164ed241143139e322a443626b60","collapsed":true,"trusted":true},"cell_type":"code","source":"del clicks_per_channel, clicks_per_app_per_channel, channel_download_counts\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"396fa8e5-9511-4ca0-a3c8-c5b7b85d22a3","_uuid":"dab501a027ef4f0df0f0cdbf89a62e1251d38ead"},"cell_type":"markdown","source":"**Number of clicks per os**"},{"metadata":{"_cell_guid":"249e9a15-5239-4880-b8de-7223ab3d4538","_uuid":"87ba0a14bc000beb4482d8639e41bef46e5810b7","collapsed":true,"trusted":true},"cell_type":"code","source":"clicks_per_os = data[['os', 'channel']].groupby('os').count().sort_values('channel', ascending=False)['channel']\nprint('Top 10', clicks_per_os[:10])\nplt.scatter(clicks_per_os.index, clicks_per_os)\nplt.xlabel('os')\nplt.ylabel('count')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"69cb00f3-5631-4713-9425-0f13167df47c","_uuid":"46d7289c1f2d858466dd1b10d6b3cb3fb59566c3"},"cell_type":"markdown","source":"Right off the bat we see 2 os having way more clicks than all the rest (13 and 19)."},{"metadata":{"_cell_guid":"888a61d0-7235-4935-abd2-5fc306de2eb7","_uuid":"80ac84aa309777ec4838b71417692e8958e53ace"},"cell_type":"markdown","source":"**Number of clicks per os (top 2) per device**"},{"metadata":{"_cell_guid":"399d61ed-d1b1-4c35-89a6-8cfcd03adad0","_uuid":"1b302b5b7e8bb0420f0c60b38740ec840c824dca","collapsed":true,"trusted":true},"cell_type":"code","source":"most_used_os = clicks_per_os[:2].index.values\nfig, axes = plt.subplots(2, 1)\nfor i in range(2):\n    temp_df = data[['os', 'device']][data['os'] == most_used_os[i]]\n    sns.countplot(x='device', data=temp_df, ax=axes[i], order=most_used_devices)\n    axes[i].set_title(most_used_os[i])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"0e5fbea2-e6ea-45ea-8015-a3ce1a4ccbe9","_uuid":"e510d2400c06fdfc18c0ea662704cb609a37a14d"},"cell_type":"markdown","source":"These are the same devices (1 and 2) used A LOT by the ips we suspect are generating fradulent clicks (5348 and 5314). They also seem to use one of these os (13 or 19)."},{"metadata":{"_cell_guid":"f15c86aa-7d65-4492-8f96-dc0cb47eafc4","_uuid":"0d11fb9cae829f5b4cb7d6cf587f49a5b5025fd9"},"cell_type":"markdown","source":"**[★ New Feature] Number of clicks per os per device**"},{"metadata":{"_cell_guid":"e1bb6f2b-aa51-4957-8fa3-db6c66bddc0f","_uuid":"aa8167cec8dbcdb9e57a407f02839a3899b21263","collapsed":true,"trusted":true},"cell_type":"code","source":"temp_col = data[['os', 'device', 'channel']].groupby(['os', 'device']).count().reset_index().rename(columns={'channel': 'os_device_count'}).astype('uint32')\ndata = data.merge(temp_col, on=['os', 'device'], how='left')\n\ndel temp_col\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"8e0ff549-71b4-4dc8-83f3-a8e5eb80b485","_uuid":"a1e2de23447ce3a6317d7234712a5e363c3e7086"},"cell_type":"markdown","source":"**What os are the most used apps on?**"},{"metadata":{"_cell_guid":"6f6e2f58-36fa-4db0-9196-679592ecf14f","_uuid":"9b8bcbc267125fee3171cdd25666bb1027199dd8","collapsed":true,"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(2, 1)\nfor i in range(2):\n    temp_df = data[['os', 'app']][data['os'] == most_used_os[i]][data['app'].isin(most_used_apps)]\n    sns.countplot(x='app', data=temp_df, ax=axes[i], order=most_used_apps)\n    axes[i].set_title(most_used_os[i])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"7a2a6ceb-c6a9-4aea-a65c-a0bda08c01c5","_uuid":"8d83d20cd8267b0b5d8a122e39147131bf43483b","collapsed":true},"cell_type":"markdown","source":"**[★ New Feature] Number of clicks per os per app per channel**"},{"metadata":{"_cell_guid":"10637b0e-f31d-447a-80fb-7afb50da147c","_uuid":"66cf0f132ae364cfb8d298cbc427c2d64c441c0f","collapsed":true,"trusted":true},"cell_type":"code","source":"temp_col = data[['os', 'app', 'channel', 'ip']].groupby(['os', 'app', 'channel']).count().reset_index().rename(columns={'ip': 'os_app_channel_count'}).astype('uint32')\ndata = data.merge(temp_col, on=['os', 'app', 'channel'], how='left')\n\ndel temp_col\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"30cf0bfe-9a20-4ea2-923c-ad67741ab531","_uuid":"19167043ff658d10410751b8560516a97a300244","collapsed":true,"trusted":true},"cell_type":"code","source":"data[['ip', 'os']][data['os'].isin(most_used_os)].groupby('os').count()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"f8b52cd2-b0a2-4807-a3d1-a19b40800310","_uuid":"71c77c67ce68d0228675563be70a919f6de3f1f0","collapsed":true,"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(2, 1)\nfor i in range(2):\n    temp_df = data[['os', 'ip']][data['ip'] == most_clicked_ips[i]]\n    sns.countplot(x='os', data=temp_df, ax=axes[i], order=clicks_per_os[:10].index.values)\n    axes[i].set_title(most_clicked_ips[i])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"11427cc3-c11c-4d14-8ef2-de6049062e6d","_uuid":"b366173b11a98f9faa005f7515064a48bc72718b","collapsed":true,"trusted":true},"cell_type":"code","source":"clicks_per_app_per_ip = data[['app', 'channel', 'ip']][data['ip'].isin(most_clicked_ips)][data['app'].isin(most_used_apps)].groupby(['app', 'ip']).count().rename(columns={'channel': 'count'})\nclicks_per_app_per_ip = clicks_per_app_per_ip.reindex(most_used_apps, level='app')\nclicks_per_app_per_ip.unstack().plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"054eb430-67dd-45ae-8ac8-914c2591627e","_uuid":"5c5073665f6f8d2e7a303927343763c56fb971f8"},"cell_type":"markdown","source":"**Conclusion**"},{"metadata":{"_cell_guid":"a2719a4c-3d40-4ebd-92a3-b519801df31b","_uuid":"04feeefb38dba6579e3a15bd0de1658e994ba893"},"cell_type":"markdown","source":"This was a wonderful oppurtunity for me to learn about how EDA works, how competitions in Kaggle work and most importantly how awesome Kaggle's community is, how everyone helps each other. I appreciate every author of every kernel I have read. \nThe best result I got is 0.9682 (LB) and 0.9692 (PB)."}],"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}