{"nbformat_minor":1,"nbformat":4,"metadata":{"language_info":{"mimetype":"text/x-python","version":"3.6.4","name":"python","codemirror_mode":{"version":3,"name":"ipython"},"nbconvert_exporter":"python","file_extension":".py","pygments_lexer":"ipython3"},"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"}},"cells":[{"cell_type":"markdown","source":["# TalkingData AdTracking Fraud Detection"],"metadata":{"_uuid":"195a3c0895111f2cca750bb5d46eaaf94dc97863","_cell_guid":"ed22dbd4-2f32-44a7-83e3-83bf7fe01797"}},{"cell_type":"markdown","source":["In this below notebook we will try to analyze the TalkingData AdTracking Fraud Detection Challenge data using SQLite. This method of using SQLite will help us to get an understanding of the data without requiring to load all the data in to the memory. Instead, with SQLite we will loa dchunks of data and create a database file and load all the data in to tables. Then, we can use SQL queries to get answers to our questions. With this method, we can use our good old Latops or Desktops with very less Memory to analyze the data."],"metadata":{"_uuid":"60d654282097ba48129c684f235926aab85320d9","_cell_guid":"e599e7c3-d41d-4017-b597-4fa56347f323"}},{"cell_type":"markdown","source":["<img src=\"https://upload.wikimedia.org/wikipedia/commons/thumb/3/38/SQLite370.svg/640px-SQLite370.svg.png\" />"],"metadata":{"_uuid":"8c329a57f11068ae8afb017347ca2ad8b92901f1","_cell_guid":"6793ea59-e130-46c4-88c1-c4eb0a27163f"}},{"cell_type":"markdown","source":["### Load Libraries"],"metadata":{"_uuid":"6c9515988b70bbee372b3dc8a3ab33aaf7278ca0","_cell_guid":"5ec8d186-9ab2-47fb-be2b-948ba4b07ed8"}},{"execution_count":null,"cell_type":"code","source":["import pandas as pd\n","import numpy as np\n","import sqlite3\n","import zipfile\n","import subprocess\n","import gc"],"metadata":{"collapsed":true,"_cell_guid":"9101996e-fd23-476f-bceb-e568a5a759d0","_uuid":"ed3e4cadfec7dea23e6fae943dae65d59ead5934"},"outputs":[]},{"cell_type":"markdown","source":["### Gather Train and Test File Details"],"metadata":{"_uuid":"8917a04459208f83ccab28400f5d44a294cfafe4","_cell_guid":"452e3296-bfe2-4ab3-8a6b-e91acb250e07"}},{"cell_type":"markdown","source":["You can uncomment the below blocks of markdown to use the ZIP files directly on your local machines."],"metadata":{"_uuid":"5e93485c98649c55e7f1977480183cd460b99855","_cell_guid":"d115847c-ab94-4371-90dd-84a256d08451"}},{"cell_type":"markdown","source":["```\n","zf_train = zipfile.ZipFile('train.csv.zip', mode='r')\n","zf_test = zipfile.ZipFile('test.csv.zip', mode='r')\n","```"],"metadata":{"collapsed":true,"_cell_guid":"40ef047a-a8a0-4ec6-880c-a585f95a5d42","_uuid":"83fea32a481effd6825512fc6af292005b714ce3"}},{"cell_type":"markdown","source":["```\n","for info in zf_train.infolist():\n","    print(\"File Name         -> {}\".format(info.filename))\n","    print(\"Compressed Size   -> {:.2f} {}\".format(info.compress_size/(1024*1024), \"MB\"))\n","    print(\"UnCompressed Size -> {:.2f} {}\".format(info.file_size/(1024*1024), \"MB\"))\n","```"],"metadata":{"_uuid":"d6ffc89ec7d412089623da5cc0c22444c4f2b0b9","_cell_guid":"6c25a75f-bdc4-46b4-9956-4905f5ac9f74"}},{"cell_type":"markdown","source":["**Output**\n","```\n","File Name         -> mnt/ssd/kaggle-talkingdata2/competition_files/train.csv\n","Compressed Size   -> 1238.43 MB\n","UnCompressed Size -> 7188.46 MB\n","```"],"metadata":{"_uuid":"fc2d83f07c525a5f8705921f54ae01079fd69c15","_cell_guid":"6461c49a-5376-4e0c-97e8-fa90ad5debdc"}},{"cell_type":"markdown","source":["```\n","for info in zf_test.infolist():\n","    print(\"File Name         -> {}\".format(info.filename))\n","    print(\"Compressed Size   -> {:.2f} {}\".format(info.compress_size/(1024*1024), \"MB\"))\n","    print(\"UnCompressed Size -> {:.2f} {}\".format(info.file_size/(1024*1024), \"MB\"))\n","```"],"metadata":{"_uuid":"22535caebe979a9c6da98a7c355956f0680804cf","_cell_guid":"d689e1fc-9329-41d3-b362-073f392f93e2"}},{"cell_type":"markdown","source":["**Output**\n","```\n","File Name         -> test.csv\n","Compressed Size   -> 161.93 MB\n","UnCompressed Size -> 823.28 MB\n","```"],"metadata":{"_uuid":"5f419950d3fbba922792f7ebee1ccdcb5352245b","_cell_guid":"6eacb66d-df39-4454-826e-9cb9a3768178"}},{"cell_type":"markdown","source":["As we see above, we have a very huge train file that has an UnCompressed Size of around 7.2GB and compressed file is around 1.2GB. Also the test file is around 823MB uncompressed and after compression comes to around 162MB."],"metadata":{"_uuid":"2aeb0a1c77a9d4fe0039fecd12519bb1e3029eaa","_cell_guid":"53d5296c-a968-462e-b36a-abbc1d446415"}},{"cell_type":"markdown","source":["### Load Sample Data From Train and Test Files"],"metadata":{"_uuid":"f1143061a1ed3748bf217fc2db31a4f6228f4cd3","_cell_guid":"5b43513a-5468-4438-a31a-da8b8b5587f4"}},{"execution_count":null,"cell_type":"code","source":["df_train_sample = pd.read_csv('../input/train.csv', nrows=10000) #10k\n","df_train_sample.info()"],"metadata":{"_uuid":"d0392da8261380ba462c11bddf42e177ddb9e55a","_cell_guid":"e468af3e-257d-4d42-a87b-00f9253754c8"},"outputs":[]},{"execution_count":null,"cell_type":"code","source":["df_train_sample.head()"],"metadata":{"_uuid":"97c2a2a2ede29f2715d79662047b4b985b160b35","_cell_guid":"28040adb-d433-4b33-bf86-8c3d0c092d2d"},"outputs":[]},{"execution_count":null,"cell_type":"code","source":["df_test_sample = pd.read_csv('../input/test.csv', nrows=10000) #10k\n","df_test_sample.info()"],"metadata":{"_uuid":"2e6b9d90135f8e6e227a2142318ee5c0e09348c7","_cell_guid":"1ac3e383-6af0-4bf8-a899-86603469b871"},"outputs":[]},{"execution_count":null,"cell_type":"code","source":["df_test_sample.head()"],"metadata":{"_uuid":"a698ff0e7f8dc677cd3f47fb207f96d7e7f9ee04","_cell_guid":"bd9dabd2-21a1-4ce9-a507-485244bacdd1"},"outputs":[]},{"cell_type":"markdown","source":["## Build a Database for Test Data"],"metadata":{"_uuid":"e0261ad52552ff2d172393817ff295fe3d14ec34","_cell_guid":"cae44f80-e6e7-409e-9d3f-df89674772e5"}},{"cell_type":"markdown","source":["As we have a very huge dataset that may not always fit in memory we will try to build a database table out of it for efficient processing. The database file will be stored on the disk and we can use SQL queries to get answers to our questions. BTW, the database file is created only the first time and you can use it for subsequent queries."],"metadata":{"_uuid":"a2469571755e01e5761a6bee80cbbde0d95179a8","_cell_guid":"841792e5-8fb6-4b4c-8c72-d3f286e3d158"}},{"cell_type":"markdown","source":["Create a the database file or use it if it was already created."],"metadata":{"_uuid":"068b65393ebfb1072c2e398405167ad7ee4a6fe6","_cell_guid":"0ecdc856-b6e7-4730-96f7-196082a24c25"}},{"execution_count":null,"cell_type":"code","source":["con = sqlite3.connect(\"talkingdata_test.db\")  # Opens file if exists, else creates file\n","cur = con.cursor()  # This object lets us actually send messages to our DB and receive results"],"metadata":{"collapsed":true,"_cell_guid":"07e14035-aaa8-48e4-bac0-17da7da09b6c","_uuid":"83a9102d23c64e56f99118c6052dd6de56613436"},"outputs":[]},{"cell_type":"markdown","source":["Run the below SQL query to check if there is already a table in the database.\n","If there is no table then create it and read the data from the csv/zip file and load it in to the table."],"metadata":{"_uuid":"38dde9b97626eddc0f87f6ef013ec26683892af6","_cell_guid":"b13fd939-c824-4838-91ad-a431c8d21489"}},{"execution_count":null,"cell_type":"code","source":["sql = \"SELECT sql FROM sqlite_master WHERE name='test_data'\"\n","cur.execute(sql)\n","\n","if not cur.fetchall():\n","    # In the below call you can use the actual test.csv.zip file when running it on a local machine\n","    for chunk in pd.read_csv(\"../input/test_supplement.csv\", nrows=10000, chunksize=500):\n","        chunk.to_sql(name=\"test_data\", con=con, if_exists=\"append\", index=False)  #\"name\" is name of table\n","        gc.collect()"],"metadata":{"collapsed":true,"_cell_guid":"d08fc559-588d-4b7d-a3c2-498c6ec7f8c3","_uuid":"47e58507878185611cde4c23a64f1e137fc0bce0"},"outputs":[]},{"cell_type":"markdown","source":["Get the schema of the table to get a detail of the columns and their data type."],"metadata":{"_uuid":"2197965a914f2de1052e7fe19aa79bc0c9440606","_cell_guid":"537481ef-053c-4477-8a4d-6bca8a47490e"}},{"execution_count":null,"cell_type":"code","source":["sql = \"SELECT sql FROM sqlite_master WHERE name='test_data'\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"1b81fef4a6aa0bd0f342643ffd3954268eafec22","_cell_guid":"b36272ac-ec91-4146-9fa3-861a24590f8c"},"outputs":[]},{"cell_type":"markdown","source":["Below we will get the total number of records in the test_data table."],"metadata":{"_uuid":"8f75e1e29c9b5cc9368e96bab3274f359bb76973","_cell_guid":"360aadc4-14a6-4eb1-a2ce-76e530fc9378"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select count(*) from test_data\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"db5d480e308ef83146f5f70544c40e8edaa1ef72","_cell_guid":"d5c72ce3-0c9b-4b39-8211-9795d46abefc"},"outputs":[]},{"cell_type":"markdown","source":["Below we will select 10 records from the table."],"metadata":{"_uuid":"64edb63eca58682b993f97bb9bbf008a9786157b","_cell_guid":"a6574e1e-dbcf-4a53-8c40-b4d388251942"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select * from test_data limit 10\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"9dd902ff4a90d2cbc50f6229876971dc850116c9","_cell_guid":"f0bf5fb2-d3d9-444a-97a8-6e889a6cc0ca"},"outputs":[]},{"cell_type":"markdown","source":["## Build a Database for Test Data"],"metadata":{"_uuid":"2a3e4b959166d8c437505c7277bd9b1fbb716500","_cell_guid":"27438a89-d1f0-48fc-8395-354b140c7b19"}},{"cell_type":"markdown","source":["Below we will create a new database for the training data. If the database was already created, we will use the same without creating it again. So, with the actaul data we will just create the database once and load all the data. From then on we will use the same database to run our queries."],"metadata":{"_uuid":"36bcec46da553e38bd7f00a7ed31d1dcecb6c026","_cell_guid":"72c4fec1-c314-41a3-91e3-ae8e97177482"}},{"execution_count":null,"cell_type":"code","source":["con = sqlite3.connect(\"talkingdata_train.db\")  # Opens file if exists, else creates file\n","cur = con.cursor()  # This object lets us actually send messages to our DB and receive results"],"metadata":{"collapsed":true,"_cell_guid":"ae08977f-a5a7-4b89-9ea1-9909b6ccb2f1","_uuid":"af67c136be6ca3ab0fb52a936890ce6b1ec6c496"},"outputs":[]},{"cell_type":"markdown","source":["Below we will check if we have the train_data table, otherwise we will create it and load the data from the train zip file."],"metadata":{"_uuid":"b81e3593e4e9bf8968df4ea75dfac75667ce5cbe","_cell_guid":"8680aafc-ba3f-44ed-bb19-d49c7c763d74"}},{"execution_count":null,"cell_type":"code","source":["sql = \"SELECT sql FROM sqlite_master WHERE name='train_data'\"\n","cur.execute(sql)\n","\n","if not cur.fetchall():\n","    # In the below call you can use the actual train.csv.zip file when running it on a local machine\n","    for chunk in pd.read_csv(\"../input/train_sample.csv\", nrows= 10000, chunksize=500):\n","        chunk.to_sql(name=\"train_data\", con=con, if_exists=\"append\", index=False)  #\"name\" is name of table\n","        gc.collect()"],"metadata":{"collapsed":true,"_cell_guid":"fb354f2c-15f1-4b75-8b2d-985d8b681480","_uuid":"51d2ff95a5cfabbab813c547bff456ab013e1718"},"outputs":[]},{"cell_type":"markdown","source":["We will do a select to get the count of records in our table. In the actual run this return **184903890** rows."],"metadata":{"_uuid":"43ddc02d0d613ad5ce7dc60e4ae2174cdf41563e","_cell_guid":"85d0d82f-b741-4aff-86cb-a16495fff8ac"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select count(*) from train_data\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"61f23df06063d1650de4feb31d7b6c0087d1580a","_cell_guid":"f20ac8e4-9f0c-48ef-81d9-a2f2005c7b5d"},"outputs":[]},{"cell_type":"markdown","source":["Below we will print the schema of the train_data table."],"metadata":{"_uuid":"818cabb4bcc792e4187b57251aa0aa7399b64fec","_cell_guid":"4bc333cf-0fc8-488e-b121-643ba8a195e0"}},{"execution_count":null,"cell_type":"code","source":["sql = \"SELECT sql FROM sqlite_master WHERE name='train_data'\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"643a26d57557d464ed2f20ce2b25ff92a164ef6d","_cell_guid":"9483e735-50c0-4a86-9b33-41698b94e3b2"},"outputs":[]},{"cell_type":"markdown","source":["Below we will print 10 records from the train_data table."],"metadata":{"_uuid":"48690510ffa02a762eba9f2ade9a3d0d259a09f4","_cell_guid":"709a6df4-813e-485d-8702-6dbc156dbf0e"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select * from train_data limit 10\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"702ee19226a78046fb02f19be4d031eac11f574d","_cell_guid":"ffb69184-5352-424e-8c40-f19c44ae93b8"},"outputs":[]},{"cell_type":"markdown","source":["Below we will print the max and min values for the **ip** column in the train_data table.\n","The actul results returned for this query on the complete train data is **(1, 364778)**. This values will help us to choose a datatype for this column when loading the data in to a pandas DataFrame. So, for **ip** we can choose **uint32**"],"metadata":{"_uuid":"13c238d394b173c1bc84c4d97da57a0eb703e0a2","_cell_guid":"fb119497-f1bc-48a2-8ffe-9a1e8e181645"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select min(ip), max(ip) from train_data\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"09b16066d3e2a72418d267c0f55ba82963a34741","_cell_guid":"9798adb2-0dc5-40ea-b1f8-7b8138ec7c17"},"outputs":[]},{"cell_type":"markdown","source":["Below we will print the max and min values for the **app** column in the train_data table.\n","The actul results returned for this query on the complete train data is **(0, 768)**. This values will help us to choose a datatype for this column when loading the data in to a pandas DataFrame. So, for **app** we can choose **uint16**"],"metadata":{"_uuid":"7b1e82362f9bf4f7992c9babd24146a3afbe0fa9","_cell_guid":"e0e9290d-67f0-4206-bcd7-0de93fcc7ca0"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select min(app), max(app) from train_data\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"fe47f0420ad207fcd3ba0a1b4d87ffc0af758421","_cell_guid":"2bb90556-8574-442b-a69c-a0a275f7c671"},"outputs":[]},{"cell_type":"markdown","source":["Below we will print the max and min values for the **device** column in the train_data table.\n","The actul results returned for this query on the complete train data is **(0, 4227)**. This values will help us to choose a datatype for this column when loading the data in to a pandas DataFrame. So, for **device** we can choose **uint16**"],"metadata":{"_uuid":"56981f09beadb7861b01cf664289abc02faee4cf","_cell_guid":"cfdba94b-1719-43c7-a08b-5951ac80ad34"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select min(device), max(device) from train_data\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"ec3ac1235fb809de39f89add9082f9917c82ce20","_cell_guid":"b3d5b54d-1f4c-4d9e-a65d-5c6d1e89078e"},"outputs":[]},{"cell_type":"markdown","source":["Below we will print the max and min values for the **os** column in the train_data table.\n","The actul results returned for this query on the complete train data is **(0, 956)**. This values will help us to choose a datatype for this column when loading the data in to a pandas DataFrame. So, for **os** we can choose **uint16**"],"metadata":{"_uuid":"4a0c5bca1111788228326d5290818ecdc235be34","_cell_guid":"6dd30f7b-b9db-4252-a6fc-6418434f8e38"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select min(os), max(os) from train_data\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"1f5ee31b1860d30be617b73f6811bad46da779db","_cell_guid":"8f9d6b8e-cd86-41e6-a9b4-0913aee0545d"},"outputs":[]},{"cell_type":"markdown","source":["Below we will print the max and min values for the **channel** column in the train_data table.\n","The actul results returned for this query on the complete train data is **(0, 500)**. This values will help us to choose a datatype for this column when loading the data in to a pandas DataFrame. So, for **channel** we can choose **uint16**"],"metadata":{"_uuid":"88f8f3b6bf3dc1667630c36ce8e72e444f21687d","_cell_guid":"3aab48ea-52e2-4634-aa05-6cedc87f0fc0"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select min(channel), max(channel) from train_data\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"062d8208b3431bb4dc8cbcc0987329ecf7f8b03a","_cell_guid":"e592aec2-381a-4d8d-93b4-94d1f0981847"},"outputs":[]},{"cell_type":"markdown","source":["Below we will print the max and min values for the **is_attributed** column in the train_data table.\n","The actul results returned for this query on the complete train data is **(0, 1)**. This values will help us to choose a datatype for this column when loading the data in to a pandas DataFrame. So, for **is_attributed** we can choose **uint8**"],"metadata":{"_uuid":"9d2004d30789695c44b27a5243dbbc5b18a06953","_cell_guid":"bb673e09-9012-4700-aa5c-157cde626fa5"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select min(is_attributed), max(is_attributed) from train_data\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"6dce59f99b06a3133c025c3323dfe43fe2477691","_cell_guid":"e9ac4101-4240-45ae-8edc-75aec2c85816"},"outputs":[]},{"cell_type":"markdown","source":["Below we will get the records where the value of is_attributed is 1.\n","For the actual data we can see that this value is **456846** which means that we have only **0.247 %** of records where is_attributed is 1."],"metadata":{"_uuid":"36b3f1c46ca61401858ab2cc0b85eab2a1136a35","_cell_guid":"0f429241-9047-44ca-bae9-b23109efe314"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select count(*) from train_data where is_attributed=1\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"57a5dddb8303fa594f94922c950a22b90054413b","_cell_guid":"2891d199-32fa-4491-b6fc-23828fb19b1a"},"outputs":[]},{"cell_type":"markdown","source":["Below is the query which will allow us to show the top 25 IP Address by their activity."],"metadata":{"_uuid":"9ac96a88d1d9c7209c74b84b270cacf9efe26c7a","_cell_guid":"6139691d-b702-4644-8de2-a19f33782714"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select ip, count(ip) from train_data group by ip order by 2 desc limit 25\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"6b4b676c2a7976c377bdf155b5f07405e9d34c60","_cell_guid":"a03b56ca-9ef3-4a9a-9e07-c53158320c23"},"outputs":[]},{"cell_type":"markdown","source":["Below is the query which shows the IP addresses with highest activity when is_attributed is 1."],"metadata":{"_uuid":"3c8558553a9c1c5f44b9b71e3b32693d9913f748","_cell_guid":"b608f080-d0b9-44ee-abf9-08195bfc9c68"}},{"execution_count":null,"cell_type":"code","source":["sql = \"select ip, count(ip) from train_data where is_attributed=1 group by ip order by 2 desc limit 20\"\n","cur.execute(sql)\n","cur.fetchall()"],"metadata":{"_uuid":"0034743b2bf13fbec6ebd9a7a12b2e3d4a63e104","_cell_guid":"76af4cdc-9469-4b2a-b56f-fbbd1a4a1c5a"},"outputs":[]},{"cell_type":"markdown","source":["# Work in progress.... Please upvote if the notebook is helpful"],"metadata":{"_uuid":"203f0393429b516dcfea7114cd6f165606f49e9c","_cell_guid":"7f44d5bf-b113-4dd4-9593-26ca9ff80726"}}]}