{"metadata": {"language_info": {"file_extension": ".py", "version": "3.6.3", "pygments_lexer": "ipython3", "name": "python", "mimetype": "text/x-python", "nbconvert_exporter": "python", "codemirror_mode": {"name": "ipython", "version": 3}}, "kernelspec": {"display_name": "Python 3", "name": "python3", "language": "python"}}, "nbformat_minor": 1, "cells": [{"metadata": {"_uuid": "6596effad5048ef94dc4455e3eec2c0bef3d8cfc", "_cell_guid": "b0e009da-ba56-482c-8c5d-28f36d24098a"}, "cell_type": "markdown", "source": ["# Introduction\n", "This kernel will not present new ML techniques, at least not now, but it will focus on different methods to reduce dataframe memory.\n", "Since this is my first kernel and moreover, and since english is not my mother tongue, please be indulgent. But any remarks/comments are welcome and appreciated.\n", "\n", "This notebook will described step-by-step the methods i have use to reduce dataframe memory and in particular focusing on this \"famous to be so big\" user_logs.csv. All methods presented here can be coupled with a \"transformation\" of the dataframe in an SQL DB or HDF-Storage.\n"]}, {"metadata": {"_uuid": "efd3dccb7209b9dd514a4d60477a2927fc58bf4f", "_cell_guid": "1c603984-20a4-4748-8bac-e7f6710ccbae"}, "execution_count": null, "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", "\n", "import numpy as np # linear algebra\n", "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n", "from time import time # code performance benchmark\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", "\n", "from subprocess import check_output\n", "print(check_output([\"ls\", \"../input\"]).decode(\"utf8\"))\n", "\n", "\n", "# Any results you write to the current directory are saved as output."], "outputs": []}, {"metadata": {"_uuid": "7e1b57261a4eee37a5b3e5294ebf86e5072293ff", "_cell_guid": "275bd611-b5dc-4bd6-a6ba-02094d8407c7"}, "cell_type": "markdown", "source": ["First step, we will read the file by chuncks and generate a dataframe from the different chuncks. In the aim to obtain the best performance on your station, it's better to increase chuncksize than increasing the number of chuncks used. Optimal values will depends on your hardware configuration."]}, {"metadata": {"_uuid": "5685d7b191b9f40eff70df358cb89a7b415ebe1d", "_cell_guid": "ce20b6c2-b744-471b-ad7c-9564128f4232", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["# variables used in all parts\n", "userfile = '../input/user_logs.csv'\n", "chuncksize = 2*10**6 #a chuncksize of 2M rows as a starting point\n", "chuncknumbers_max = 20 # we will not read all the file, only 20 chuncks, enough for the demonstration\n"], "outputs": []}, {"metadata": {"_uuid": "045b88736bc57a49d7851dd6a6e354c2270979b7", "_cell_guid": "323f0452-037a-4c78-b036-6523e3186121"}, "execution_count": null, "cell_type": "code", "source": ["chunck_number = 0\n", "user_df = pd.DataFrame()\n", "t = time()\n", "for df in pd.read_csv(userfile, chunksize=chuncksize, iterator=True, header=0):\n", "    user_df = user_df.append(df, ignore_index=True)\n", "    chunck_number += 1\n", "    if chunck_number == chuncknumbers_max :\n", "        break\n", "INITIAL_TIME = int(time()-t)\n", "print('done in '+ str(INITIAL_TIME)+'s')\n", "\n", "print('memory usage (MB) : ')\n", "INITIAL_MEM = int(user_df.memory_usage(deep=True).sum()/1024**2)\n", "print(INITIAL_MEM)\n", "print('dataframe details')\n", "print(user_df.info(memory_usage='deep'))"], "outputs": []}, {"metadata": {"_uuid": "dcf7bfd8f9891d39e430fcfc3ed579bbc907eab0", "_cell_guid": "25fba24c-9128-4281-ba27-081df6f542a2"}, "cell_type": "markdown", "source": ["Before the memory optimisations, there is a better way to create the dataframe, leading, as consequence, to increased performances. DataFrame.append is quite ineffective in fact, as it will copy old DataFrame to a new memory place each time and after the append will be made. \n", "As example, if your df uses 9GB of memory and you want to add for 1MB values, pandas will copy your 9GB df to another memory place of size 9.001 GB and delete your old df after. If you are making a lot of append (lot = more thant 2/3) with an appended df smaller than the initial one, this is not the best way.\n", "Another way is to copy each partial DataFrame obtained at each iteration to a list and at the end, concat all the elements together. This way, for sure you will have to copy df to new memory places, but you will move df of smaller sizes ;) "]}, {"metadata": {"_uuid": "9397410cc5aa39228f3ff01cbde926434644c8b4", "_cell_guid": "a975e492-ed87-42a4-859b-4c261902812b"}, "execution_count": null, "cell_type": "code", "source": ["chunck_number = 0\n", "user_df = None\n", "list_of_df = []\n", "t = time()\n", "for df in pd.read_csv(userfile, chunksize=chuncksize, iterator=True, header=0):\n", "    # this is a list().append function which is called here, not a Dataframe.append function\n", "    list_of_df.append(df)\n", "    chunck_number += 1\n", "    if chunck_number == chuncknumbers_max :\n", "        break\n", "user_df = pd.concat(list_of_df, ignore_index=True)\n", "# we don't need this list anymore so we suppress it (since it has almost the same size as the obtained dataframe )\n", "del list_of_df\n", "current_time = int(time()-t)\n", "print('done in '+ str(current_time)+'s')\n", "print('performance increase : '+ str(int(100*(1-current_time/INITIAL_TIME))) + '%')\n", "print('memory usage (MB) : ')\n", "current_mem = int(user_df.memory_usage(deep=True).sum()/1024**2)\n", "print(current_mem)\n"], "outputs": []}, {"metadata": {"_uuid": "08ae6fb1a10fc08e5d6599748738efc7059f72b3", "_cell_guid": "7c9a75c2-df5e-4842-92d7-037309d313c7", "collapsed": true}, "cell_type": "markdown", "source": ["Nice, almost 35% faster this way :) Moreover, compared to user_df.append function, this \n", "Finding the best datatypes for the different columns has been detailed in other kernels presented here, as consequence, i will use directly the optimal values to read the csv file."]}, {"metadata": {"_uuid": "34429a56b6e406218f76613345337b7e576c95c5", "_cell_guid": "3b5f70e7-b478-438d-9b06-927c6c1d17b0"}, "execution_count": null, "cell_type": "code", "source": ["# specify dtype associated with each columns of the csv, string dtype correspond also to object dtype\n", "dtype_cols = {'msno': object, 'date':np.int64, 'num_25': np.int32, 'num_50': np.int32, \n", "             'num_75': np.int32, 'num_985': np.int32, 'num_100': np.int32, \n", "              'num_unq': np.int32, 'total_secs': np.float32}\n", "user_df = None\n", "chunck_number = 0\n", "list_of_df = []\n", "t = time()\n", "for df in pd.read_csv(userfile, chunksize=chuncksize, iterator=True, header=0, dtype=dtype_cols):\n", "    list_of_df.append(df)\n", "    chunck_number += 1\n", "    if chunck_number == chuncknumbers_max :\n", "        break\n", "user_df = pd.concat(list_of_df, ignore_index=True)\n", "print('done in '+ str(int(time()-t))+'s')\n", "print('memory usage (MB) : ')\n", "current_mem = int(user_df.memory_usage(deep=True).sum()/1024**2)\n", "print(current_mem)\n", "gain = int(100*(1-current_mem/INITIAL_MEM))\n", "print('gain :' + str(gain) + '%')"], "outputs": []}, {"metadata": {"_uuid": "217e76699a0ab9fc28f6c30e6dd5317ad720ac84", "_cell_guid": "8957f4ec-07c2-4d89-8c51-9864f4c478d5"}, "cell_type": "markdown", "source": ["Already a reduction of 16% on final size, let's continue...\n", "Looking more precisely to each columns :"]}, {"metadata": {"_uuid": "ef4238d1654fb42ce12c5a7caa0ac4fd64884d8e", "_cell_guid": "9b2242cb-5f71-48ea-b0ab-ddf47e943efe"}, "execution_count": null, "cell_type": "code", "source": ["print('memory usage (MB) : ')\n", "user_df.memory_usage(deep=True)/1024**2"], "outputs": []}, {"metadata": {"_uuid": "9d4824f93be9a77af8b51442f5eeaf9f7008d6d6", "_cell_guid": "26754cc5-29a9-44b9-acc7-2efe61f40b7c"}, "cell_type": "markdown", "source": ["We can remark that date used twice as other columns. But but but... msno uses almost 50% ot total dataframe size. We will as consequence, concentrate on this particular column.  \n", "\n", "## MSNO column\n", "\n", "Msno's dtype is \"object\" (object can be seen as string dtype) which means it's not possible to reduce associated size by changing the dtype : string is a string. But there is an alternative. First of all,  we check how many different values are present in this column :"]}, {"metadata": {"_uuid": "356398f4136850ef777ece50068fc5873541bdb7", "_cell_guid": "0c216456-5b00-484c-b2d6-0f183f4d2cf2"}, "execution_count": null, "cell_type": "code", "source": ["print('different msno numbers :')\n", "print(len(user_df.msno.unique()))\n", "print('ratio of unique msno :')\n", "print(str(100*len(user_df.msno.unique())/user_df.shape[0])+'%')"], "outputs": []}, {"metadata": {"_uuid": "2f04d0b2840bd39b3dd23bd3a1e0fe560b559933", "_cell_guid": "79c43ec1-17f0-4986-bcd1-4eb003982965"}, "cell_type": "markdown", "source": ["Only 5% of unique values present in the current dataframe (this ratio will decrease if we use more chuncks), as consequence, 'category' datatype can be efficient in that case. Category dtype is said to be efficient for series which have a lot of repetiting values. So the more data you load, the more this change will become interesting ;) "]}, {"metadata": {"_uuid": "faa56cf9bb93ef1b30995baf51e890451314102b", "_cell_guid": "3e7fe107-836d-4a02-a8f2-90569109b6c5"}, "execution_count": null, "cell_type": "code", "source": ["user_df['msno'] = user_df['msno'].astype('category')\n", "print(user_df.info(memory_usage='deep'))\n", "current_mem = int(user_df.memory_usage(deep=True).sum()/1024**2)\n", "print(current_mem)\n", "gain = int(100*(1-current_mem/INITIAL_MEM))\n", "print('gain :' + str(gain) + '%')"], "outputs": []}, {"metadata": {"_uuid": "9f6a352ec5366c116fc1bfec64f1d84392a12dbb", "_cell_guid": "d093f8b9-6886-4e67-9886-6c408ea720f4"}, "cell_type": "markdown", "source": ["A case for which category allows a huge memory reduction....**71%** size reduction in that case. You know what i'm happy...or at least, I start to be happy. \n", "Let's continue with the date column... Do we really need an int64?\n", "## date column\n", " Transactions in user_logs started on 2015/01/01, so we can just memorize number of days after this date and change the associated datatype to int16 (down from int64). For that purpose we will create a function to computer number of days between a given date and 2015/01/01\n"]}, {"metadata": {"_uuid": "6ef9b5a3f86099f8b5908764816818bbcfaa983a", "_cell_guid": "a1cd3139-4c07-4037-bf54-2b97a69de067", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["from datetime import datetime as dt\n", "STARTDATE = dt(2015, 1, 1)\n", "def intdate_as_days(intdate):\n", "    return (dt.strptime(str(intdate), '%Y%m%d') - STARTDATE).days"], "outputs": []}, {"metadata": {"_uuid": "96a44c00db400aa15aae992db262964a67c2ec5c", "_cell_guid": "7f0aa03c-5584-409d-8f51-a3b7604611ed"}, "execution_count": null, "cell_type": "code", "source": ["# remark you need to use pandas > 0.19.1 to be able to use category dtype here \n", "dtype_cols = {'msno': 'category', 'date':np.int64, 'num_25': np.int32, 'num_50': np.int32, \n", "             'num_75': np.int32, 'num_985': np.int32, 'num_100': np.int32, \n", "              'num_unq': np.int32, 'total_secs': np.float32}\n", "user_df = None\n", "chunck_number = 0\n", "list_of_df = []\n", "t = time()\n", "for df in pd.read_csv(userfile, chunksize=chuncksize, iterator=True, header=0, dtype=dtype_cols):\n", "    df['date'] = df['date'].map(lambda x:intdate_as_days(x))\n", "    df['date'] = df['date'].astype(np.int16)\n", "    list_of_df.append(df)\n", "    chunck_number += 1\n", "    if chunck_number == chuncknumbers_max :\n", "        break\n", "user_df = pd.concat(list_of_df, ignore_index=True)\n", "# if you use pandas<0.19, uncomment next line\n", "# user_df['msno'] = user_df['msno'].astype('category')\n", "print('done in '+ str(int(time()-t))+'s')\n", "print('memory usage (MB) : ')\n", "current_mem = int(user_df.memory_usage(deep=True).sum()/1024**2)\n", "print(current_mem)\n", "gain = int(100*(1-current_mem/INITIAL_MEM))\n", "print('gain :' + str(gain) + '%')"], "outputs": []}, {"metadata": {"_uuid": "c965283cb0d8a704d8dcc8a4316f93780a98e186", "_cell_guid": "c5ad001a-7724-48f1-a92f-05f861c20440", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["print(user_df.info(memory_usage='deep'))"], "outputs": []}, {"metadata": {"_uuid": "f4621dbc2c0f22d162454f1f4c9253167827381b", "_cell_guid": "80a7c380-74e4-4dc2-a756-fdc87869b7ce"}, "cell_type": "markdown", "source": ["And we can remark another 3% reduction for dataframe memory size, \n", "Finally, compared to initial method, memory size has been reduced by **75%** and dataframe creation speed increased by **35%**. It's not so bad.\n", "It has to be noticed that on that docker image provided by kaggle, using map on dataframe is quite slow, which leads to this huge loss of performances for this step."]}, {"metadata": {"_uuid": "f84a342a89c9e841978c9770a226fb7ee5bebb52", "_cell_guid": "c30aaff1-6815-4331-8a4e-d8aef48add78"}, "cell_type": "markdown", "source": ["# Application (/extension) of this loader\n", "Even in that case, you will need a lot or memory (quite more than 10 GB of free ram) to load the full file :( \n", "An interesting subsampling can be to use only data corresponding to users in *train.csv* which can de done this way :"]}, {"metadata": {"_uuid": "8af60288f402e70c4a3b208f3b6e45559a4ab18a", "_cell_guid": "822085cb-55bd-4dd8-8518-1e1b68275faa", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["dtype_cols = {'msno': object, 'date':np.int64, 'num_25': np.int32, 'num_50': np.int32, \n", "             'num_75': np.int32, 'num_985': np.int32, 'num_100': np.int32, \n", "              'num_unq': np.int32, 'total_secs': np.float32}\n", "user_df = None\n", "\n", "# loading train.csv into another dataframe\n", "train_df = pd.read_csv('../input/train.csv', dtype={'msno': object, 'is_churn': np.int8})\n", "\n", "# we compute only unique values of msno, just in case....\n", "cols_msno = train_df['msno'].unique()\n", "\n", "chunck_number = 0\n", "list_of_df = []\n", "t = time()\n", "for df in pd.read_csv(userfile, chunksize=chuncksize, iterator=True, header=0, dtype=dtype_cols):\n", "    # addition to previous script, we will look only to dataframe's msno which are present in train_df\n", "    # only save msno which are already in train_df, \n", "    append_cond = df['msno'].isin(cols_msno)\n", "    df = df[append_cond]\n", "    \n", "    # as previously...\n", "    df['date'] = df['date'].map(lambda x:intdate_as_days(x))\n", "    df['date'] = df['date'].astype(np.int16)    \n", "    list_of_df.append(df)\n", "    chunck_number += 1\n", "    if chunck_number == chuncknumbers_max :\n", "        break\n", "user_df = pd.concat(list_of_df, ignore_index=True)\n", "user_df['msno'] = user_df['msno'].astype('category')\n", "print('done in '+ str(int(time()-t))+'s')\n", "current_mem = int(user_df.memory_usage(deep=True).sum()/1024**2)\n", "print('memory usage (MB) : ' + str(current_mem))\n"], "outputs": []}, {"metadata": {"_uuid": "8ae04fc5a071ae8c575c80b2b5e9a89ff90b7ab0", "_cell_guid": "11e27c9a-0555-402c-854a-0a6b6885150e", "collapsed": true}, "cell_type": "markdown", "source": ["In the case presented here, new file is less than 1GB which is a good point. But we can remark that we have another dataframe linked to train.csv : **train_df**. Both df, train_df and user_df, share the same msno possible values. Let's try something on it...\n", "\n", "*Remark *: For sure, it's possible to make a merge of both dataframes but for this presentation, i will not do it ;-)\n", "You will see why in next part\n", "\n", "## Focus on train_df and user_df"]}, {"metadata": {"_uuid": "910874132142edc680f8e845412e5b367a33f364", "_kg_hide-input": true, "_cell_guid": "991f8abc-d804-429c-af50-3c19e2518b04", "collapsed": true, "_kg_hide-output": true}, "execution_count": null, "cell_type": "code", "source": ["train_df = pd.read_csv('../input/train.csv', dtype={'msno': object, 'is_churn': np.int8})"], "outputs": []}, {"metadata": {"_uuid": "996248f1e992c2dc72ac0e01dc4ac6cc138426b9", "_cell_guid": "d26400e4-a804-4928-887b-33bb3fa05800", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["print('Memory associated with train_df (MB): ')\n", "TRAIN_INIT_MEM = int(train_df.memory_usage(deep=True).sum()/1024**2)\n", "print(TRAIN_INIT_MEM)"], "outputs": []}, {"metadata": {"_uuid": "3816abb7c9aa266f837ca494c9d1c0aae04dddb0", "_cell_guid": "ae353023-6322-4ce7-b82d-7f7513203d7d"}, "cell_type": "markdown", "source": ["Previously I said that size of msno can be reduced through category datatype, let's do the trick again"]}, {"metadata": {"_uuid": "587ce47352d6ec56510d2752876fa7233b5a8807", "_cell_guid": "a951078d-471b-44db-bb9d-898f1274aa25", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["train_df['msno'] = train_df['msno'].astype('category')\n", "print('Memory associated with train_df (MB): ')\n", "print(int(train_df.memory_usage(deep=True).sum()/1024**2))"], "outputs": []}, {"metadata": {"_uuid": "96c93d5836a3dd20137a99cfd04ebd7437bfa2a5", "_cell_guid": "3f2439ec-b435-4204-a04a-c4bbf8e2b081"}, "cell_type": "markdown", "source": ["Hey wait...you said (and shown)  that category help to reduce memory but in that case it has increased, not as interesting. This can be explained if we compute the ratio of unique values in that dataframe."]}, {"metadata": {"_uuid": "6424917b390c25920554b57351b2ea60bcbdfbad", "_cell_guid": "e185a166-702a-4d74-b8a8-f7fb3489c4a2", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["print('different msno numbers in train :')\n", "print(len(train_df.msno.unique()))\n", "print('ratio of unique msno in train:')\n", "print(str(100*len(train_df.msno.unique())/train_df.shape[0])+'%')"], "outputs": []}, {"metadata": {"_uuid": "bf1872886fd696c3906060b811d70c932bbb8503", "_cell_guid": "1e29e09e-6c9e-4c34-bf45-ca7bacfe6089"}, "cell_type": "markdown", "source": ["All msno are unique in train_df that's why category are not efficient at all. \n", "Even in that case we can apply the principle of category, making an alternative by ourselves using a dictionnary and an hexadecimal representation of the train_df.index, or just on the row line number\n"]}, {"metadata": {"_uuid": "b0d5a541e93583e784b0222019f9c9b464aacea8", "_cell_guid": "5189d3c6-23b1-47a6-9d8e-3567c5136748", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["# generate the hash dict\n", "hashkey = {}\n", "index = 0\n", "msno_list = train_df['msno'].values\n", "for msno_idx in range(0, len(msno_list)):\n", "    msno = msno_list[msno_idx]\n", "    hashkey.update({msno : '{:09x}'.format(msno_idx)})\n", "# this dict can be saved to a csv file to use it after...\n", "csv_key_file = 'hashkey.csv'\n", "with open(csv_key_file, 'w') as f:\n", "    f.write('msno,hexid\\n')\n", "    for k,v in hashkey.items():\n", "        f.write('{0},{1}\\n'.format(k,v))\n", "        \n", "# if you want to get  back msno from dict, generate the 'inverse' dict this way\n", "hashkey_reverse = {}\n", "for k,v in hashkey.items(): hashkey_reverse.update({v:k})\n", "\n", "# apply this hash to train_df\n", "train_df['msno'] = train_df['msno'].map(lambda x:hashkey.get(x,x))\n", "train_df['msno'] = train_df['msno'].astype('str')\n", "print('Memory associated with train_df (MB): ')\n", "current_mem = int(train_df.memory_usage(deep=True).sum()/1024**2)\n", "print(current_mem)\n", "print('Reduction of (%)')\n", "print(100*(1-current_mem/TRAIN_INIT_MEM))"], "outputs": []}, {"metadata": {"_uuid": "b17791460ab3562363480f64a7620ef8aa92b65b", "_cell_guid": "2eff9e6d-4548-4442-856a-f716f51a028c"}, "cell_type": "markdown", "source": ["35% gain, even for this small DataFrame. The reason is, event if associated type is still string/object, the length of each element is now only 9 characters. As a consequence, DataFrame use less memory (msno uses 40+ characters). The previous remark concerning category still apply here for train_df : using category is still inefficent.\n", "Since this hash seems promising, we can apply it to the main dataframe, user_df. "]}, {"metadata": {"_uuid": "35ba5492268c70ea50dc8ea26a9d5e7989351cf8", "_cell_guid": "e4ad8fc5-5c81-48fb-afd7-675381d1078a", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": ["user_df['msno'] = user_df['msno'].map(lambda x:hashkey.get(x,x))\n", "user_df['msno'] = user_df['msno'].astype('category')\n", "#user_df['msno'] = user_df['msno'].astype('category')\n", "print('Memory associated with final version of user_df (MB): ')\n", "current_mem = int(user_df.memory_usage(deep=True).sum()/1024**2)\n", "print('Reduction of (%)')\n", "print(100*(1-current_mem/INITIAL_MEM))"], "outputs": []}, {"metadata": {"_uuid": "3fa9f512808ad70e1d243852dad1f91575f440c1", "_cell_guid": "0f1f99d9-23ad-49c0-9195-b1a6e0051d01"}, "cell_type": "markdown", "source": ["## Conclusion\n", "Mixing usage of adapted pandas function, with choice of good datatype, with or without transformations before/after,  permits to reduce memory size by a factor of **85%** here.\n", "In conclusion, where you have columns with (long) strings don't hesitate to use hash methods, coupled with category.\n", "\n", "I hope this kernel will help you in current (and others) competitions since methods presented here are quite generic. And, as said in the introduction, feel free to use it, share it and ask any questions. I will (try to) answers the best I can."]}, {"metadata": {"_uuid": "7a1aa8835aecbcf7577c170b15c2ead5f6d1561a", "_cell_guid": "a30bef23-deb3-447b-99e3-46e46535b303", "collapsed": true}, "execution_count": null, "cell_type": "code", "source": [], "outputs": []}], "nbformat": 4}