{"cells":[{"metadata":{"_cell_guid":"e9eb71f1-aae8-49fe-b59d-50e52f48044b","_uuid":"b5af7f263ddbd7e5a2c7bb0cfc0c49fa12b20348"},"cell_type":"markdown","source":"# How to Work with BIG Datasets on Kaggle Kernels (16G RAM)\n\nThis particular competition is asking us to look and analyze some really big data sets.  In its given form, it won't even load into pandas on the kaggle kernels.  If you don't have a very fancy computer, it probably won't load on yours either.  At least on my laptop (macbook, 16G RAM) it won't.\n\nI am assuming I'm not the only one with limited computing power, and limited budget for running paid cloud setup.  So I've been looking into different methods to work with big data on limited resources.\n\nBelow are some tips I  collected while learning to get by with mostly Kaggle kernels while not overloading its allocated RAM.  \n\nThese tips are probably more useful for  begginners and intermediate users, but if you are an expert and know of better ways, please share and I'll be happy to update the book!"},{"metadata":{"_cell_guid":"36f6eddb-e7ab-4a13-b7d3-11434e68b27f","_uuid":"753f13eb4da48bac5b3f74bb0d1ee60a3e0622da"},"cell_type":"markdown","source":"# OUTLINE\n(i do intend to make this a linked heading at some point...)\n\n- ** TIP 1  - Deleting unused variables and gc.collect() **\n- **TIP 2 - Presetting the datatypes**\n- ** TIP 3 - Importing selected rows of the a file (including generating your own subsamples)**\n- **TIP 4 - Importing in batches and processing each individually**\n- **TIP 5 - Importing just selected columns**\n- ** TIP 6 - Creative data processing**\n- ** TIP 7 - Using Dask **\n"},{"metadata":{"collapsed":true,"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":false},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport datetime\nimport os\nimport time\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport gc\n%matplotlib inline\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"b2765539-66bc-4ab3-bde3-5352efc4398d","_uuid":"f4be5b408aade243a05d1027af762bedb1731d8e","trusted":false,"collapsed":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":{"collapsed":true,"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"},"cell_type":"markdown","source":"## TIP # 1 Deleting unused variables and gc.collect() \n\nThe thing about python is that once it loads something into RAM it doesn't really get rid of it effectively.  So if you load a huge dataframe into pandas, and then make a copy of it and never use it again, that original dataframe will still be in your RAM.  Eating away at your memory.   Same goes for any other variables you create.\n\nTherefore if you used up a dataframe (or other variable), get in the habit of deleting it.  \n\nFor example, if you create a dataframe  `temp`, extract some features and merge results to your main training set, `temp` will still be eating up space.  You need to explicitely delete it by stating `del temp`.  You also need to make sure that nothing else is referring to `temp` (you don't have any other variables bound to it).\n\nEven after doing so there may still be residual memory usage going on.\n\nThat's where the garbage collection module comes in.   `import gc` at the beginning of your project, and then each time you want to clear up space put command `gc.collect()` .  \n\nIt also helps to run `gc.collect()` after multiple transformations/functions/copying etc...  as all the little references/values accumulate."},{"metadata":{"collapsed":true,"_cell_guid":"63cf8776-769f-45e8-bfa6-262168ee64e2","_uuid":"c2dbb1b50a7049b8be5c4cc343dcc389fb80f25d","trusted":false},"cell_type":"code","source":"# eg:\n#import some file\ntemp = pd.read_csv('../input/train_sample.csv')\n\n#do something to the file\ntemp['os'] = temp['os'].astype('str')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"5367107a-e89f-4d87-8558-896d1704818a","_uuid":"0a03d4eff80f1885b66705637e3bace9fc338264","trusted":false,"collapsed":true},"cell_type":"code","source":"#delete when no longer needed\ndel temp\n#collect residual garbage\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"19291259-d7a4-435a-8342-0fb6eb2bb7ab","_uuid":"ebc75a51ba874f915d2f309f09a86c5b02390ca7"},"cell_type":"markdown","source":"## TIP # 2   Presetting the datatypes\nIf you import data into CSV, python will do it's best to guess the datatypes, but it will tend to error on the side of allocating more space than necessary.\nSo if you know in advance that your numbers are integers, and don't get bigger than certain values, set the datatypes at minimum requirements before importing."},{"metadata":{"_cell_guid":"935e2756-4179-47f7-9de7-9502f31c7236","_uuid":"d2f41f2d57258e6ee0c79696e80c4345fc8dc8b9","trusted":false,"collapsed":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        }\n\ntrain = pd.read_csv('../input/train_sample.csv', dtype=dtypes)\n\n#check datatypes:\ntrain.info()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"d57f1236-1018-48e1-ac0f-4c7df17826f6","_uuid":"02ab26221208d2dd0ad80520ebc80ebea94ec7e6"},"cell_type":"markdown","source":"## TIP # 3 Importing selected rows of a csv file"},{"metadata":{"_cell_guid":"79076b96-264c-4e9e-9ac0-213d7cf95045","_uuid":"cb80d6e0ee7e5ad61fbc07d9650d2cc12b1cd002"},"cell_type":"markdown","source":"### a) Select number of rows to import\nInstead of the default  `pd.read_csv('filename') ` you can use parameter `nrows` to specify number of rows to import.  For exampe:\n`train = pd.read_csv('../input/train.csv', nrows=10000)` will only read the first 10000 rows (including the heading).."},{"metadata":{"_cell_guid":"91adac02-6b53-4c23-9b62-f79d0fd755d2","_uuid":"24e49a60b2a7301c3964dcb8d32576665a35caa9","trusted":false,"collapsed":true},"cell_type":"code","source":"train = pd.read_csv('../input/train.csv', nrows=10000, dtype=dtypes)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"2b072d2a-5da9-40ac-9d7b-a1de2bde20ea","_uuid":"9b0afc1c942caecfd601ea42231549934d6ab3be"},"cell_type":"markdown","source":"### b)  Simple row skip (with or without headings)\nYou can also specify number of rows to skip (`skiprows`) , if you, for example want 1 million rows after the first 5 million:\n`train = pd.read_csv('../input/train.csv', skiprows=5000000, nrows=1000000)`.  This however will ignore the first line with headers.  Instead you can pass in range of rows to skip, that will not include the first row  (indexed `[0]`)."},{"metadata":{"_cell_guid":"339c4b5f-603e-47a4-ba62-e8ee67b81aa6","_uuid":"4a3bfba6413f48b1515ca2e037133592388929b9","trusted":false,"collapsed":true},"cell_type":"code","source":"#plain skipping looses heading info.  It's OK for files that don't have headings, \n#or dataframes you'll be linking together, or where you make your own custom headings...\ntrain = pd.read_csv('../input/train.csv', skiprows=5000000, nrows=1000000, header = None, dtype=dtypes)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"392e3877-c489-4219-9e89-48d8a59b87f8","_uuid":"ce0057d9030e527655ebee51234b1c949a0a6fbf","trusted":false,"collapsed":true},"cell_type":"code","source":"#but if you want to import the headings from the original file\n#skip first 5mil rows, but use the first row for heading:\ntrain = pd.read_csv('../input/train.csv', skiprows=range(1, 5000000), nrows=1000000, dtype=dtypes)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"209f52fa-535c-4761-bcc1-2bbae81dba0f","_uuid":"5a7ffeecf1467c8b3abe9a9a0f82c9597170e895"},"cell_type":"markdown","source":"### c) Picking wich rows to skip  (Make a list of what you DON'T want)\n"},{"metadata":{"_cell_guid":"46ac626a-1cea-40f7-aff1-139a2876ea57","_uuid":"04b18c8a6e5f3b81a9babad7ae032fcfec3a6cca"},"cell_type":"markdown","source":"** This is how you can do your own random sampling**\n\nSince 'skiprows' can take in a list of rows you want to skip, you can make a list of random rows you want to input.   I.e. you can sample your data anyway you like!\n\nRecall how many rows the train set in TalkingData has:"},{"metadata":{"_kg_hide-input":true,"_cell_guid":"70e1e1b0-4fef-4d13-a72d-b78bbe657d13","_uuid":"8f5b155550176d0bfd5609555a6d4ce3e3dbfe70","trusted":false,"collapsed":true},"cell_type":"code","source":"import subprocess\n#from https://stackoverflow.com/questions/845058/how-to-get-line-count-cheaply-in-python , Olafur's answer\ndef file_len(fname):\n    p = subprocess.Popen(['wc', '-l', fname], stdout=subprocess.PIPE, \n                                              stderr=subprocess.PIPE)\n    result, err = p.communicate()\n    if p.returncode != 0:\n        raise IOError(err)\n    return int(result.strip().split()[0])\n\nlines = file_len('../input/train.csv')\nprint('Number of lines in \"train.csv\" is:', lines)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"0c6877ed-9e05-4bc8-a6df-51a8ee99d0ad","_uuid":"08d20a10063ae4ea0ad353709ab314b537de9b19"},"cell_type":"markdown","source":"Let's say you want to pull a random sample of 1 million lines out of the total dataset.  That means that you want a list of `lines - 1 - 1000000` random numbers ranging from 1 to 184903891. \n\nNote: generating such long list also takes a lot of space and  some time.  Be patient and make sure to use del and gc.collect() when done!"},{"metadata":{"collapsed":true,"_cell_guid":"283331fb-7a08-4257-899a-76f65571235b","_uuid":"45000c9a9c140b21849a12e9fc1ea5e156afa8dd","trusted":false},"cell_type":"code","source":"#generate list of lines to skip\nskiplines = np.random.choice(np.arange(1, lines), size=lines-1-1000000, replace=False)\n\n#sort the list\nskiplines=np.sort(skiplines)\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"173201b4-d0f8-40ca-9efc-0f4decad6d57","_uuid":"cd466bfb7b88992dc1930b827e5c7b0dd4d9459e","trusted":false,"collapsed":true},"cell_type":"code","source":"#check our list\nprint('lines to skip:', len(skiplines))\nprint('remaining lines in sample:', lines-len(skiplines), '(remember that it includes the heading!)')\n\n###################SANITY CHECK###################\n#find lines that weren't skipped by checking difference between each consecutive line\n#how many out of first 100000 will be imported into the csv?\ndiff = skiplines[1:100000]-skiplines[2:100001]\nremain = sum(diff!=-1)\nprint('Ratio of lines from first 100000 lines:',  '{0:.5f}'.format(remain/100000) ) \nprint('Ratio imported from all lines:', '{0:.5f}'.format((lines-len(skiplines))/lines) )","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"373aa6b0-9e60-457b-a04c-838be0bff094","_uuid":"b00e013fdf4bf723f5530e49cb62588232ed3115"},"cell_type":"markdown","source":"Now let's import the randomly selected 1million rows"},{"metadata":{"_cell_guid":"7b3d8e2c-0630-4a07-881a-24ecbb568ccf","_uuid":"42bf3f4b5ec677dcf13f514b9cca49e077f7b975","trusted":false,"collapsed":true},"cell_type":"code","source":"train = pd.read_csv('../input/train.csv', skiprows=skiplines, dtype=dtypes)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"59c037e8-5195-4f2f-bcf6-a51b5fa533c3","_uuid":"742f03f5a642367af9922cd554911d4642c70e79"},"cell_type":"markdown","source":"Delete the now useless list and any other garbaged generated along the way"},{"metadata":{"_cell_guid":"5f2dfe64-13c4-4ec8-a8a5-284780737bfc","_uuid":"62e658698f7830f171307f3df838fa26761a819d","trusted":false,"collapsed":true},"cell_type":"code","source":"del skiplines\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"32f6eb7e-ea00-4d13-8ca5-458ef9d3a04d","_uuid":"c1ff26e6684019501e73d1bd9b17e8d317d2f5f5"},"cell_type":"markdown","source":"And inspect our resulting table.  But first convert time stamps to timeseries type."},{"metadata":{"collapsed":true,"_cell_guid":"6857a43f-0fa6-4fd1-800d-24bbdc9f01f1","_uuid":"8e4aaa351ea664becd24e1d1595958e0201c0f7f","trusted":false},"cell_type":"code","source":"train['click_time'] = pd.to_datetime(train['click_time'])\ntrain['attributed_time'] = pd.to_datetime(train['attributed_time'])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"4d230f6e-c506-4494-8c1c-9c4cebc76c81","_uuid":"480bc69122696c8eec864f4d10979fb887db2f71","trusted":false,"collapsed":true},"cell_type":"code","source":"train.describe(include='all')","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"61b4646f-5f0c-4856-be87-736f4f8cadcf","_uuid":"b250c720275f8a2b22d0ada78088feda9f55e249"},"cell_type":"markdown","source":"In my previous notebook (https://www.kaggle.com/yuliagm/talkingdata-eda-plus-time-patterns) we found that the data is organized by click time.  Therefore if our random sampling went according to plan, the resulting set should roughly span the full time period and mimick the click pattern. \n\nWe see from above that first and last click span the 4 day period.\n\nLet's try a chart to see if the pattern looks consistent:"},{"metadata":{"_cell_guid":"6e0fa69f-4b92-42d7-9fd6-de37e6629280","_uuid":"7c506d29b7cece20c737d9ebc1421d163343dfb4","trusted":false,"collapsed":true},"cell_type":"code","source":"#round the time to nearest hour\ntrain['click_rnd']=train['click_time'].dt.round('H')  \n\n#check for hourly patterns\ntrain[['click_rnd','is_attributed']].groupby(['click_rnd'], as_index=True).count().plot()\nplt.title('HOURLY CLICK FREQUENCY');\nplt.ylabel('Number of Clicks');\n\ntrain[['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":{"_cell_guid":"397d0432-b7b2-4425-982b-d9d6afcbec94","_uuid":"a9454b7649b956574d2f86e795efe379e60b38a1"},"cell_type":"markdown","source":"Looks all-right!  \n\nNow you can analyze your own subsample and run models on it."},{"metadata":{"_cell_guid":"2df3c47f-56e9-4260-8399-389022d22438","_uuid":"b591d03631e8c84a9b70afdc39c3bfadeab24564"},"cell_type":"markdown","source":"## TIP #4   Importing in batches and processing each individually"},{"metadata":{"_cell_guid":"5952b89c-d478-4b7c-9c48-272b4f8ed5b3","_uuid":"7469b61f83cac91aa83b0d742d6722bf86e5a7f2"},"cell_type":"markdown","source":"We know that the proportion of clicks that was attributed is very low.  So let's say we want to look at all of them at the same time.  We don't know what rows they are, and we can't load the whole data and filter.  But we can load in chuncks, extract from each chunk what we need and get rid of everything else!\n\nThe idea is simple.  You specify size of chunk (number of lines) you want pandas to import at a time.  Then you do some kind of processing on it.  Then pandas imports the next chunk, untill there are no more lines left.\n\nSo below I import one million rows, extract only rows that have 'is_attributed'==1 (i.e. app was downloaded) and then merge these results into common dataframe for further inspection."},{"metadata":{"collapsed":true,"_cell_guid":"2b694655-096b-49e9-b021-1026c67e5db3","_uuid":"24cb40e94eee91bcad060e151bf93a4e60e15963","trusted":false},"cell_type":"code","source":"#set up an empty dataframe\ndf_converted = pd.DataFrame()\n\n#we are going to work with chunks of size 1 million rows\nchunksize = 10 ** 6\n\n#in each chunk, filter for values that have 'is_attributed'==1, and merge these values into one dataframe\nfor chunk in pd.read_csv('../input/train.csv', chunksize=chunksize, dtype=dtypes):\n    filtered = (chunk[(np.where(chunk['is_attributed']==1, True, False))])\n    df_converted = pd.concat([df_converted, filtered], ignore_index=True, )\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"d2128f49-f618-4fd2-8399-b96308a868e7","_uuid":"cf9dd50755e3d9404da6c4529a0dfdc79d5d75ed"},"cell_type":"markdown","source":"Let's see what we've got:"},{"metadata":{"_cell_guid":"1d79eca7-d7f2-49a9-95bc-2278cbbb2c0f","_uuid":"0f2e06b5a1e6e64ce168de2198e21520fff7c622","trusted":false,"collapsed":true},"cell_type":"code","source":"df_converted.info()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"55aa3d09-19b2-4684-9e1a-a57475a0b1f2","_uuid":"5005e936491291e5f3c8aa96361aa0d98e21c7ea","trusted":false,"collapsed":true},"cell_type":"code","source":"df_converted.head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"28cd51a5-94ed-4b49-b2e2-4a3c77229380","_uuid":"600490c3f39f6a5340ea1843add3c4bf2b47d808"},"cell_type":"markdown","source":"Perfect!  Now we know that in our entire dataset there are 456846 samples of conversions.  We can explore this subset  for patterns, anomalies, etc...  \n\nUsing analogous method you can explore specific ips, apps, devices, etc  combinations, devices, etc...   "},{"metadata":{"_cell_guid":"c71c3095-293d-43c1-b84a-dff6cf413ab3","_uuid":"7ecec250187bdf751f5d047d97af031d590f57f4"},"cell_type":"markdown","source":"## TIP #5 Importing just selected columns\n\nIf you want to analyze just some specific feature, you can import just the selected columns.\n\nFor example, lets say we want to analyze clicks by ips.  Or conversions by ips.\n\nImporting just 2 fields as opposed to full table just may fit in our RAM"},{"metadata":{"collapsed":true,"_cell_guid":"7cf6aac7-d1a9-416b-ba6d-a9462fc66c2e","_uuid":"6c3897a04337c56c4bd69ff4c6f32395e3859e04","trusted":false},"cell_type":"code","source":"#wanted columns\ncolumns = ['ip', 'click_time', 'is_attributed']\ndtypes = {\n        'ip'            : 'uint32',\n        'is_attributed' : 'uint8',\n        }\n\nips_df = pd.read_csv('../input/train.csv', usecols=columns, dtype=dtypes)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"40bd13ff-287b-4314-8fe0-512d2726a52d","_uuid":"eccc04f7a52f3da76b3e82936c6368501128b09c"},"cell_type":"markdown","source":"Let's see what we've got."},{"metadata":{"_cell_guid":"36a2a32d-eedf-46c8-a84a-b3ebfb103943","_uuid":"48d33f68920165d13f3c19645aca6ae19d8f0675","trusted":false,"collapsed":true},"cell_type":"code","source":"print(ips_df.info())\nips_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"3e00092a-79b6-484a-825d-730f6a02fe88","_uuid":"e9a4c89913ab44297cb557c939a32e62f3d46b75"},"cell_type":"markdown","source":"The dataframe is big (over 2G, but manageable).\n\nNow let's say you want to generate counts of ips frequencies (maybe to use as a feature in a model).\n\nThe regular groupby method will crush the system.  So you'd have to be creative about how to do it.  \n\nWhich is what the next section is about:"},{"metadata":{"_cell_guid":"2223ad08-0702-4eab-b3d4-41f3773b8314","_uuid":"3e4208e31e88d119fd3260e842c3fbf10803122f"},"cell_type":"markdown","source":"## Tip #6  Creative data processing\n\nThe kernel cannot handle groupby on the whole dataframe.  But it can do it in sections.  For example:"},{"metadata":{"_cell_guid":"40414f7a-e228-42e3-9c96-eb2368444ef9","_uuid":"57b2d88e00d9ceed860ca8b7799b4d465d6270dc","trusted":false,"collapsed":true},"cell_type":"code","source":"#processing part of the table is not a problem\nips_df[0:100][['ip', 'is_attributed']].groupby('ip', as_index=False).count()[:10]","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"9354ef08-eaad-4c6f-8889-b5e6a9eb86f5","_uuid":"842fa5d188a3387c66402be897f408146317ac09"},"cell_type":"markdown","source":"So you can calculate counts in batches, merge them and sum up to total counts.\n\n(Takes a bit of  time, but works)"},{"metadata":{"collapsed":true,"_cell_guid":"75a3ce6a-2db6-49e6-82cc-bf359c77fbc9","_uuid":"52cfecdd3a6bae5916e746d4941c7a9155bf3e94","trusted":false},"cell_type":"code","source":"size=100000\nall_rows = len(ips_df)\nnum_parts = all_rows//size\n\n#generate the first batch\nip_counts = ips_df[0:size][['ip', 'is_attributed']].groupby('ip', as_index=False).count()\n\n#add remaining batches\nfor p in range(1,num_parts):\n    start = p*size\n    end = p*size + size\n    if end < all_rows:\n        group = ips_df[start:end][['ip', 'is_attributed']].groupby('ip', as_index=False).count()\n    else:\n        group = ips_df[start:][['ip', 'is_attributed']].groupby('ip', as_index=False).count()\n    ip_counts = ip_counts.merge(group, on='ip', how='outer')\n    ip_counts.columns = ['ip', 'count1','count2']\n    ip_counts['counts'] = np.nansum((ip_counts['count1'], ip_counts['count2']), axis = 0)\n    ip_counts.drop(columns=['count1', 'count2'], axis = 0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"b4237f5b-aaf1-4b6c-af7c-3beb1eb0bc58","_uuid":"c41cf0cfed5bef12237aef1a0a58a5882b5c34b9","trusted":false,"collapsed":true},"cell_type":"code","source":"#see what we've got:\nip_counts.head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"15382393-3271-4a0f-a8b6-4370cc4bd487","_uuid":"1cb7b74c48e26b901873bd89a0fd13c435fcb7a8"},"cell_type":"markdown","source":"Sort highest to lowest"},{"metadata":{"_cell_guid":"88254bd5-6180-4c33-a5de-f158edde82d4","_uuid":"41f9ba3596f037909bca711bf9042d657410069b","trusted":false,"collapsed":true},"cell_type":"code","source":"ip_counts.sort_values('counts', ascending=False)[:20]","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"249c80ee-096e-4f87-92a6-0767f2571320","_uuid":"a79159f77797a841174aaf541164659352e2875e"},"cell_type":"markdown","source":"Check that the sum of counts adds up to the total number of values:"},{"metadata":{"_cell_guid":"6ae28653-e532-47f1-847f-9a7ca7d08eb6","_uuid":"dd010559740f39a7da7ff4dda0f681224f2c9a8f","trusted":false,"collapsed":true},"cell_type":"code","source":"np.sum(ip_counts['counts'])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"d4086363-6c11-484f-8719-12eba74c12c7","_uuid":"3abb4532d0ec8095aaebd5249e99150f603c92a7"},"cell_type":"markdown","source":"Let's say you wanted proportion of conversions by ip, get the sums of conversions and then devide them by counts..."},{"metadata":{"_cell_guid":"3ee866a6-7dac-41ad-af29-bb2fb648d1bb","_uuid":"550c51acbc7f2186637fa1dfc5a89f820d6cd6f4","trusted":false,"collapsed":true},"cell_type":"code","source":"size=100000\nall_rows = len(ips_df)\nnum_parts = all_rows//size\n\n#generate the first batch\nip_sums = ips_df[0:size][['ip', 'is_attributed']].groupby('ip', as_index=False).sum()\n\n#add remaining batches\nfor p in range(1,num_parts):\n    start = p*size\n    end = p*size + size\n    if end < all_rows:\n        group = ips_df[start:end][['ip', 'is_attributed']].groupby('ip', as_index=False).sum()\n    else:\n        group = ips_df[start:][['ip', 'is_attributed']].groupby('ip', as_index=False).sum()\n    ip_sums = ip_sums.merge(group, on='ip', how='outer')\n    ip_sums.columns = ['ip', 'sum1','sum2']\n    ip_sums['conversions_per_ip'] = np.nansum((ip_sums['sum1'], ip_sums['sum2']), axis = 0)\n    ip_sums.drop(columns=['sum1', 'sum2'], axis = 0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"5318bc19-b41a-42fd-af17-790eeb362769","_uuid":"c7290da4fd7bf2cc0045e0736dd4cdd504a585a5","trusted":false},"cell_type":"code","source":"ip_sums.head(10)","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"c8401bb7-50bc-4d20-bf53-6f5c1eac0876","_uuid":"af2767c6f1e6feb812d88bbe8c3f750d64a0930a","trusted":false},"cell_type":"code","source":"#check proportion (we calculated earlier how many rows of data had conversions)\nnp.sum(ip_sums['conversions_per_ip'])/184900000","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"60340ac9-2edf-439f-9a87-db8af261bc3b","_uuid":"f36e4f67c80e3bfe774c788192e83f7130846c45"},"cell_type":"markdown","source":"What if we want proportion of conversions to click per ip?  "},{"metadata":{"collapsed":true,"_cell_guid":"e3a78df8-6372-482e-b80f-822afb9a6238","_uuid":"78fab0a6eb67fbe257bbab0b9fa3bab088d2da85","trusted":false},"cell_type":"code","source":"ip_conversions=ip_counts.merge(ip_sums, on='ip', how='left')\nip_conversions.head()","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"b401e611-a08d-4319-907f-71c266c8a8cf","_uuid":"69e60438ed4c4ecd23cb91a53d32beca083a2496","trusted":false},"cell_type":"code","source":"ip_conversions['converted_ratio']=ip_conversions['conversions_per_ip']/ip_conversions['counts']\nip_conversions[:10]","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"08eaedf2-96d1-49f2-9d70-568e60b9bc3b","_uuid":"e5874774cb5c71aa64899e0aad28738bca95d1d8","trusted":false},"cell_type":"code","source":"#some cleanup\ndel ip_conversions\ndel ip_sums\ndel ips_df\ndel df_converted\ndel train\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"7b229eb0-4c7c-43b7-8440-2fd4af103823","_uuid":"5ebb7ad763feb03a857859296bbee49eadbf3f88"},"cell_type":"markdown","source":"## TIP #7 Using Dask\n\n### DASK\n\n**What is it?**\n- A python library for parallel computing that can work on a single notebook or large cluster.\n\n**What does it do?**\n- it parallizes NumPy and Pandas\n- makes it possible to work on larger-than-memory datasets\n- in case of single machine uses its own task scheduler to get the most out of your machine  (kaggle kernels are multicore, so there is definetly room for improvement)\n- BASICALLY IT WILL MAKE SOME COMPUTATIONS FIT RAM, AND WILL DO IT FASTER\n\n**Its limitations?**\n- it's still relativelty early in development\n- it doesn't have all of Panda's options/functions/features. Only the most common ones.\n- many operations that require setting the index are still computationally expensive \n\nFirst you'll need to import the library."},{"metadata":{"collapsed":true,"_cell_guid":"17b203d7-39ae-4fa0-954c-be7214f84695","_uuid":"3e397ebf9d0fba30c447effcf8d46218f142ec12","trusted":false},"cell_type":"code","source":"import dask\nimport dask.dataframe as dd","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"16259391-90c2-4b1c-99df-12ecada1704c","_uuid":"755f6e6847d144d22d05600653d058a8a2f6b91a"},"cell_type":"markdown","source":"There are different sections to Dask, but for this case you'll likely just use** Dask DataFrames**.\n\nHere are some basics from the developers:\n\n> A Dask DataFrame is a large parallel dataframe composed of many smaller Pandas dataframes, split along the index. These pandas dataframes may live on disk for larger-than-memory computing on a single machine, or on many different machines in a cluster. One Dask dataframe operation triggers many operations on the constituent Pandas dataframes.\n\n(https://dask.pydata.org/en/latest/dataframe.html)\n\nFor convenience and Dask.dataframe copies the Pandas API.  Thus commands look and feel familiar.\n\n**What DaskDataframes can do?**\n-they are very fast on most commonly used set of Pandas API\n<br><br>\n*below is taken directly from: https://dask.pydata.org/en/latest/dataframe.html *\n<br>\n\n### Trivially parallelizable operations (fast):\n- Elementwise operations: `df.x + df.y, df * df`\n- Row-wise selections: `df[df.x > 0]`\n- Loc: `df.loc[4.0:10.5]`\n- Common aggregations: `df.x.max(), df.max()`\n- Is in: `df[df.x.isin([1, 2, 3])]`\n- Datetime/string accessors: `df.timestamp.month`\n \n<br>\n### Cleverly parallelizable operations(fast):\n- groupby-aggregate (with common aggregations): `df.groupby(df.x).y.max(), df.groupby('x').max()`\n- groupby-apply on index: `df.groupby(['idx', 'x']).apply(myfunc)`, where `idx` is the index level name\n- value_counts: `df.x.value_counts()`\n- Drop duplicates: `df.x.drop_duplicates()`\n- Join on index: `dd.merge(df1, df2, left_index=True, right_index=True`) or `dd.merge(df1, df2, on=['idx', 'x'])` where `idx` is the index name for both `df1` and `df2`\n- Join with Pandas DataFrames: `dd.merge(df1, df2, on='id')`\n- Elementwise operations with different partitions / divisions:` df1.x + df2.y`\n- Datetime resampling: `df.resample(...)`\n- Rolling averages: `df.rolling(...)`\n- Pearson Correlations: `df[['col1', 'col2']].corr()`\n\n<br>\n### Notes/observations:\n- To actually get results of many of the above functions you have to add `.compute()` at the end.  eg, for value_counts would be: `df.x.value_counts().compute()`.  This hikes up RAM use a lot.  I believe it's because `.compute()` gets the data into pandas format, with all the accompanying overhead.  (Please correct me if wrong).\n\n- I've been playing with dask for the past little while here on Kaggle Kernels, and while they can load full data and do some nice filtering, many actual operations do hike up RAM to extreme and even crush the system.  For example, after loading 'train' dataframe, just getting `len(train)` hiked RAM up to 9GB.  So be careful...  Use a lot of `gc.collect()` and other techniques for making data smaller.  So far I find dask most useful for filtering (selecting rows with specified features).\n\n<br><br>**Now let's see some examples.**\n\nFirst, let's load the big train data:"},{"metadata":{"collapsed":true,"_cell_guid":"0e5a31f0-59b7-4110-84da-e9fa73c5074f","_uuid":"cee2630dee44b41bef64a34c9039f467be3b8dcf","trusted":false},"cell_type":"code","source":"# Loading in the train data\ndtypes = {'ip':'uint32',\n          'app': 'uint16',\n          'device': 'uint16',\n          'os': 'uint16',\n          'channel': 'uint16',\n          'is_attributed': 'uint8'}\n\ntrain = dd.read_csv(\"../input/train.csv\", dtype=dtypes, parse_dates=['click_time', 'attributed_time'])\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"fe5d99cd-78ea-4638-b561-f06ae9853b85","_uuid":"1af2b0dcc6541a6e95711a2efb411e4bc8497189","trusted":false},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"f1482f88-1348-4ad0-acbe-aa7acf7f3340","_uuid":"9d26f9732470a000c60b7c08305a0ddbe676e5ec","trusted":false},"cell_type":"code","source":"len(train)","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"93bd413b-f2ea-4f42-93de-0b87c46534be","_uuid":"3301e4412fb6c088328dd604ef7ed2bfa583e476","trusted":false},"cell_type":"code","source":"train.columns","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"2f50e9be-c210-4803-a2d0-2828fcaa1efd","_uuid":"2006b4c74ab0fef2b821c0e327c4b47b6dacdc98"},"cell_type":"markdown","source":"Let's see how it works for selecting data subsets:"},{"metadata":{"collapsed":true,"_cell_guid":"1e6e1c1a-9896-45db-8ead-d7f3a1679787","_uuid":"4c43e18de7af75e2bff9b2b42a718220504ea529","trusted":false},"cell_type":"code","source":"#select only rows 'is_attributed'==1\ntrain[train['is_attributed']==1].head()","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"88521f45-1e8a-49ea-82b7-371efc3f7125","_uuid":"4945bec1e7bd19a3b20f9a836af76833ee99a8d7","trusted":false},"cell_type":"code","source":"#select only data attributed after 2017-11-06 \ntrain[train['attributed_time']>='2017-11-07 00:00:00'].head()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"42310d6b-b4c5-498d-999c-4d7e9216da17","_uuid":"842dff0b2b2dd583d714d9da4ebec8a34bd0fedd"},"cell_type":"markdown","source":"Now we'll do some heavier lifting.<br>\n\nLet's get counts by ip"},{"metadata":{"collapsed":true,"_cell_guid":"cbe333f5-3d2f-4787-a522-5607ac48f93e","_uuid":"6de4211324b06363ec374424bdb7476291ccc6d9","trusted":false},"cell_type":"code","source":"ip_counts = train.ip.value_counts().compute()\nip_counts[:20]","execution_count":null,"outputs":[]},{"metadata":{"collapsed":true,"_cell_guid":"8be53884-150c-4f14-acbe-d3b5c144e508","_uuid":"d9e7e11ed18ca3f2a382281171ea72dd3912f1d4","trusted":false},"cell_type":"code","source":"#clean up to free up space\n#for future work, you can export data you generated to CSVs so you don't have to make it\n#all over again\ndel ip_counts\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"74ad501d-8424-4a29-874d-f2a4069cea05","_uuid":"7121601301013bc4426058bd1a4bc27fde8049f3"},"cell_type":"markdown","source":"Let's now try to get mean conversion by channel:"},{"metadata":{"collapsed":true,"_cell_guid":"7d419c1a-c101-487a-877f-628b69c95610","_uuid":"bda8d8f7222cca17565849cb914800e13f5d40de","trusted":false},"cell_type":"code","source":"channel_means = train[['channel','is_attributed']].groupby('channel').mean().compute()\nchannel_means[:20]","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"7e64bf08-db2f-4b74-8991-a97512572a0c","_uuid":"fd9baa6bd2c59304c3028cf445dbd49992158873"},"cell_type":"markdown","source":"Unfortunately `as_index=False` does not appear to be implemented in Dask yet...  You'd have to do manual manipulation to get the channel into a column...  For example like this:\n"},{"metadata":{"collapsed":true,"_cell_guid":"8ec60ba1-4e3a-4052-af62-c8426d5d5572","_uuid":"ee673ea2c1a528e3bf4d3f07d81c956d95b85889","trusted":false},"cell_type":"code","source":"channel_means=channel_means.reset_index()\nchannel_means[:20]","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"951aafc7-6111-4264-9f06-8fd18380ab01","_uuid":"98f379a13ae49e1163f8245c56bda2162ea6ff91"},"cell_type":"markdown","source":"*** To be continued....***"},{"metadata":{"_cell_guid":"1d1ac995-99b6-4337-a61c-796c808171b5","_uuid":"1e234e32ae17e4271d85c0436d55af413db63b3c"},"cell_type":"markdown","source":"As you can see, not all is lost if your computing resources are limited..  By combining/manipulating these and other methods, you can get quite a lot out of this data.  You can run models on subsamples, generate features for categories, etc...\n\nYou can also do analysis and preprocessing prep here, and then run the real big models on a cloud.  It will save you money, not having to do all the work on a dollar counter.\n\nOf course all these tricks are time consuming and require lots of extra effort.  Naturally life would be easier if you had a fancy computer, or endless free cloud access.  But the reality for some of us is that we just don't...   \n\nI hope this helps those of you who are limited in resources, but still want to learn and explore the data."}],"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}