nb3-pandas-with-large-csvs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# \"Big Pandas\" - Dask from the Inside\n",
    "## Part 3 - Pandas with many large csvs \n",
    "## PyData Berlin tutorial, 30 June 2017\n",
    "## Stephen Simmons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('1.11.3', '0.19.2', '0.14.1')"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Standard modules\n",
    "import io\n",
    "import logging\n",
    "import lzma\n",
    "import multiprocessing\n",
    "import os\n",
    "import ssl\n",
    "import time\n",
    "import urllib.request\n",
    "import zipfile\n",
    "\n",
    "# Third-party modules\n",
    "import fastparquet      # Needs python-snappy\n",
    "import graphviz         # To visualize Dask graphs \n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import psutil           # Memory stats\n",
    "import dask\n",
    "import dask.dataframe as dd\n",
    "\n",
    "# Support multiple lines of output in each cell\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "# Don't wrap tables\n",
    "pd.options.display.max_rows = 20\n",
    "pd.options.display.max_columns = 20\n",
    "pd.options.display.width = 300\n",
    "\n",
    "# Show matplotlib graphs inline in Jupyter notebook\n",
    "%matplotlib inline\n",
    "\n",
    "np.__version__, pd.__version__, dask.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[Process 8921 uses 123.9MB]'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def memory_usage():\n",
    "    \"\"\"String with current memory usage in MB. Requires `psutil` package.\"\"\"\n",
    "    pid = os.getpid()\n",
    "    mem_bytes = psutil.Process(pid).memory_info().rss\n",
    "    return \"[Process %s uses %0.1fMB]\" % (pid, mem_bytes/1024.0/1024.0)\n",
    "\n",
    "memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 12 ms, sys: 0 ns, total: 12 ms\n",
      "Wall time: 13.2 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = d.read_csv('flights-2016-01.xz', nrows=4, dialect=\"excel\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <td>2016</td>\n",
       "      <td>2016</td>\n",
       "      <td>2016</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Quarter</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DayofMonth</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DayOfWeek</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FlightDate</th>\n",
       "      <td>2016-01-06</td>\n",
       "      <td>2016-01-07</td>\n",
       "      <td>2016-01-08</td>\n",
       "      <td>2016-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <td>AA</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AirlineID</th>\n",
       "      <td>19805</td>\n",
       "      <td>19805</td>\n",
       "      <td>19805</td>\n",
       "      <td>19805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Carrier</th>\n",
       "      <td>AA</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA</td>\n",
       "      <td>AA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TailNum</th>\n",
       "      <td>N4YBAA</td>\n",
       "      <td>N434AA</td>\n",
       "      <td>N541AA</td>\n",
       "      <td>N489AA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div4TailNum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div5Airport</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div5AirportID</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div5AirportSeqID</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div5WheelsOn</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div5TotalGTime</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div5LongestGTime</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div5WheelsOff</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Div5TailNum</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unnamed: 109</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0           1           2           3\n",
       "Year                    2016        2016        2016        2016\n",
       "Quarter                    1           1           1           1\n",
       "Month                      1           1           1           1\n",
       "DayofMonth                 6           7           8           9\n",
       "DayOfWeek                  3           4           5           6\n",
       "FlightDate        2016-01-06  2016-01-07  2016-01-08  2016-01-09\n",
       "UniqueCarrier             AA          AA          AA          AA\n",
       "AirlineID              19805       19805       19805       19805\n",
       "Carrier                   AA          AA          AA          AA\n",
       "TailNum               N4YBAA      N434AA      N541AA      N489AA\n",
       "...                      ...         ...         ...         ...\n",
       "Div4TailNum              NaN         NaN         NaN         NaN\n",
       "Div5Airport              NaN         NaN         NaN         NaN\n",
       "Div5AirportID            NaN         NaN         NaN         NaN\n",
       "Div5AirportSeqID         NaN         NaN         NaN         NaN\n",
       "Div5WheelsOn             NaN         NaN         NaN         NaN\n",
       "Div5TotalGTime           NaN         NaN         NaN         NaN\n",
       "Div5LongestGTime         NaN         NaN         NaN         NaN\n",
       "Div5WheelsOff            NaN         NaN         NaN         NaN\n",
       "Div5TailNum              NaN         NaN         NaN         NaN\n",
       "Unnamed: 109             NaN         NaN         NaN         NaN\n",
       "\n",
       "[110 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[Process 7762 uses 127.1MB]'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<string>:2: DtypeWarning: Columns (77) have mixed types. Specify dtype option on import or set low_memory=False.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.48 s, sys: 1.36 s, total: 6.83 s\n",
      "Wall time: 6.83 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = pd.read_csv('flights-2016-01.xz', dialect=\"excel\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[Process 7762 uses 855.6MB]'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 445827 entries, 0 to 445826\n",
      "Columns: 110 entries, Year to Unnamed: 109\n",
      "dtypes: float64(71), int64(21), object(18)\n",
      "memory usage: 374.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "745.24831008911133"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.memory_usage(deep=True).sum() / 1024 / 1024 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Year, Quarter, Month, DayofMonth, DayOfWeek, FlightDate,\n",
      "UniqueCarrier, AirlineID, Carrier, TailNum, FlightNum,\n",
      "OriginAirportID, OriginAirportSeqID, OriginCityMarketID,\n",
      "Origin, OriginCityName, OriginState, OriginStateFips,\n",
      "OriginStateName, OriginWac, DestAirportID, DestAirportSeqID,\n",
      "DestCityMarketID, Dest, DestCityName, DestState,\n",
      "DestStateFips, DestStateName, DestWac, CRSDepTime, DepTime,\n",
      "DepDelay, DepDelayMinutes, DepDel15, DepartureDelayGroups,\n",
      "DepTimeBlk, TaxiOut, WheelsOff, WheelsOn, TaxiIn,\n",
      "CRSArrTime, ArrTime, ArrDelay, ArrDelayMinutes, ArrDel15,\n",
      "ArrivalDelayGroups, ArrTimeBlk, Cancelled, CancellationCode,\n",
      "Diverted, CRSElapsedTime, ActualElapsedTime, AirTime,\n",
      "Flights, Distance, DistanceGroup, CarrierDelay,\n",
      "WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay,\n",
      "FirstDepTime, TotalAddGTime, LongestAddGTime,\n",
      "DivAirportLandings, DivReachedDest, DivActualElapsedTime,\n",
      "DivArrDelay, DivDistance, Div1Airport, Div1AirportID,\n",
      "Div1AirportSeqID, Div1WheelsOn, Div1TotalGTime,\n",
      "Div1LongestGTime, Div1WheelsOff, Div1TailNum, Div2Airport,\n",
      "Div2AirportID, Div2AirportSeqID, Div2WheelsOn,\n",
      "Div2TotalGTime, Div2LongestGTime, Div2WheelsOff,\n",
      "Div2TailNum, Div3Airport, Div3AirportID, Div3AirportSeqID,\n",
      "Div3WheelsOn, Div3TotalGTime, Div3LongestGTime,\n",
      "Div3WheelsOff, Div3TailNum, Div4Airport, Div4AirportID,\n",
      "Div4AirportSeqID, Div4WheelsOn, Div4TotalGTime,\n",
      "Div4LongestGTime, Div4WheelsOff, Div4TailNum, Div5Airport,\n",
      "Div5AirportID, Div5AirportSeqID, Div5WheelsOn,\n",
      "Div5TotalGTime, Div5LongestGTime, Div5WheelsOff,\n",
      "Div5TailNum, Unnamed: 109\n"
     ]
    }
   ],
   "source": [
    "import textwrap\n",
    "print('\\n'.join(textwrap.wrap(', '.join(df.columns), 60)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "def load_months(months):\n",
    "    dfs = [ \n",
    "        pd.read_csv('flights-%s.xz' % month, dialect=\"excel\")\n",
    "            for month in months \n",
    "          ]\n",
    "    return pd.concat(dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/stephen/miniconda3/envs/py36/lib/python3.6/site-packages/IPython/core/magics/execution.py:1185: DtypeWarning: Columns (48,76,77,84,85) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  exec(code, glob, local_ns)\n",
      "/home/stephen/miniconda3/envs/py36/lib/python3.6/site-packages/IPython/core/magics/execution.py:1185: DtypeWarning: Columns (77) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  exec(code, glob, local_ns)\n",
      "/home/stephen/miniconda3/envs/py36/lib/python3.6/site-packages/IPython/core/magics/execution.py:1185: DtypeWarning: Columns (77,84) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  exec(code, glob, local_ns)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 18.4 s, sys: 6.66 s, total: 25.1 s\n",
      "Wall time: 1min 26s\n"
     ]
    }
   ],
   "source": [
    "df = load_months(['2015-12','2016-01','2016-02'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[Process 7762 uses 1379.6MB]'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1348946 entries, 0 to 423888\n",
      "Columns: 110 entries, Year to Unnamed: 109\n",
      "dtypes: float64(69), int64(21), object(20)\n",
      "memory usage: 1.1+ GB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2326.9082660675049"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.memory_usage(deep=True).sum() / 1024 / 1024 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}