1st Exercise.ipynb

{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Data Analysis"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "1st Exercise"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "**Count the number of lines in Python for each file.**\n",
      "\n",
      "As it's for me the first time working with pandas, I want to compare results and performance too"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Solution in linux terminal"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!head -n 2 bookings.csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "act_date           ^source^pos_ctry^pos_iata^pos_oid  ^rloc          ^cre_date           ^duration^distance^dep_port^dep_city^dep_ctry^arr_port^arr_city^arr_ctry^lst_port^lst_city^lst_ctry^brd_port^brd_city^brd_ctry^off_port^off_city^off_ctry^mkt_port^mkt_city^mkt_ctry^intl^route          ^carrier^bkg_class^cab_class^brd_time           ^off_time           ^pax^year^month^oid      \r\n",
        "2013-03-05 00:00:00^1A    ^DE      ^a68dd7ae953c8acfb187a1af2dcbe123^1a11ae49fcbf545fd2afc1a24d88d2b7^ea65900e72d71f4626378e2ebd298267^2013-02-22 00:00:00^1708^0^ZRH     ^ZRH     ^CH      ^LHR     ^LON     ^GB      ^ZRH     ^ZRH     ^CH      ^LHR     ^LON     ^GB      ^ZRH     ^ZRH     ^CH      ^LHRZRH  ^LONZRH  ^CHGB    ^1^LHRZRH         ^VI^T        ^Y        ^2013-03-07 08:50:00^2013-03-07 11:33:37^-1^2013^3^NULL     \r\n"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!head -n 2 searches.csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Date^Time^TxnCode^OfficeID^Country^Origin^Destination^RoundTrip^NbSegments^Seg1Departure^Seg1Arrival^Seg1Date^Seg1Carrier^Seg1BookingCode^Seg2Departure^Seg2Arrival^Seg2Date^Seg2Carrier^Seg2BookingCode^Seg3Departure^Seg3Arrival^Seg3Date^Seg3Carrier^Seg3BookingCode^Seg4Departure^Seg4Arrival^Seg4Date^Seg4Carrier^Seg4BookingCode^Seg5Departure^Seg5Arrival^Seg5Date^Seg5Carrier^Seg5BookingCode^Seg6Departure^Seg6Arrival^Seg6Date^Seg6Carrier^Seg6BookingCode^From^IsPublishedForNeg^IsFromInternet^IsFromVista^TerminalID^InternetOffice\r\n",
        "2013-01-01^20:25:57^MPT^624d8c3ac0b3a7ca03e3c167e0f48327^DE^TXL^AUH^1^2^TXL^AUH^2013-01-26^D2^^AUH^TXL^2013-02-02^D2^^^^^^^^^^^^^^^^^^^^^^1ASIWS^0^0^0^d41d8cd98f00b204e9800998ecf8427e^FRA\r\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%time !wc -l bookings.csv searches.csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "  10000011 bookings.csv\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "  20390198 searches.csv\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "  30390209 total\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "CPU times: user 267 ms, sys: 35.1 ms, total: 302 ms\n",
        "Wall time: 1min 28s\n"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Solution:"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "As the first line of each file refers to the names of every column, we can say that there are:\n",
      "\n",
      "|              | lines    | registers |\n",
      "|--------------|----------|-----------|\n",
      "| bookings.csv | 10000011 | 10000010  |\n",
      "| searches.csv | 20390198 | 20390197  |"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Solution in pure python"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def count_lines(fname):\n",
      "    \"\"\"\n",
      "    Count the number of lines of text in a file\n",
      "    \"\"\"\n",
      "    count = 0\n",
      "    try:\n",
      "        f = open(fname, 'r')\n",
      "        while(f.next()):  #this can be optimized\n",
      "            count+=1\n",
      "    except:\n",
      "        pass\n",
      "    #crashing when EOF ...?\n",
      "    #with open(fname, 'r') as f:\n",
      "    #    #this way not all the file is kept in memory\n",
      "    #    while(f.next()):\n",
      "    #        count+=1\n",
      "    return count"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%time bks_lines = count_lines(\"bookings.csv\")\n",
      "print bks_lines"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "CPU times: user 2.27 s, sys: 3.48 s, total: 5.75 s\n",
        "Wall time: 37.6 s\n",
        "10000011\n"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%time srch_lines = count_lines(\"searches.csv\")\n",
      "print srch_lines"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "CPU times: user 3.83 s, sys: 4.1 s, total: 7.93 s\n",
        "Wall time: 46.7 s\n",
        "20390199\n"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Solution with Python Pandas"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "I paginate the file just in case. Also I load only pos_ctry field that is 2 digits long."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def row_count_pd(fname):\n",
      "    \"\"\"\n",
      "    count number of rows in a file with pandas\n",
      "    \"\"\"\n",
      "    bks_chunks = pd.read_table(fname,sep='\\s*\\^', usecols=['pos_ctry'], chunksize=100000)\n",
      "    rows = 0 \n",
      "    for chk in bks_chunks:\n",
      "        rows+=len(chk.index)\n",
      "    return rows\n",
      "\n",
      "%time row_count_pd('bookings.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "CPU times: user 3min 32s, sys: 11.3 s, total: 3min 44s\n",
        "Wall time: 3min 47s\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "/usr/local/lib/python2.7/dist-packages/pandas/io/parsers.py:639: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators; you can avoid this warning by specifying engine='python'.\n",
        "  ParserWarning)\n"
       ]
      },
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 3,
       "text": [
        "10000010"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "MEMORY PROBLEM:"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "There seems to be a problem parsing with regular expressions. The previous pagination algorithm had to be implemented because memory exploded in my system.\n",
      "\n",
      "BUT as there are ~10M rows, loading only 2 chars per row, it should not overload the memory.\n",
      "\n",
      "Although, parsing with only a \"^\" separator (no regex) works fine and the memory footprint is minimal (< 500MB)\n",
      "\n",
      "Here the solution parsing with separator:"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%time bks = pd.read_table('bookings.csv',sep='^', usecols=['pos_ctry'])\n",
      "print len(bks.index)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "CPU times: user 23 s, sys: 5.23 s, total: 28.2 s\n",
        "Wall time: 53.7 s\n",
        "10000010\n"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%time srchs = pd.read_table('searches.csv',sep='^', usecols=['Country'])\n",
      "print len(srchs.index)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "CPU times: user 17.8 s, sys: 4.12 s, total: 21.9 s\n",
        "Wall time: 47 s\n",
        "20390198\n"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "For this exercise there is no problem in loading data that might be badly formatted due to whitespaces, but for the following exercises, it might pose a problem. A possible solution is with converters parameters on the read_table"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "1st Exercise Solution:"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The results are the same as above and there is almost no difference in performance\n",
      "\n",
      "|              |  lines   |   rows    |\n",
      "|--------------|----------|-----------|\n",
      "| bookings.csv | 10000011 | 10000010  |\n",
      "| searches.csv | 20390198 | 20390197  |\n",
      "\n",
      "|                        | Time All   | Time  bookings  | Time  searches  |\n",
      "|------------------------|------------|-----------------|-----------------|\n",
      "| wc -l                  | 88s        |       -         |       -         |\n",
      "| pure python            | 84.7s      |      37.6s      |      46.7       |\n",
      "| pandas (no pagination) | 100.7s     |      53.7s      |      47s        |\n",
      "| pandas (pagination)    |     -      |      227s       |       -         |\n",
      "\n",
      "In particular for this problem, Pandas solution (loading the data) is slower than the other solutions. Even without pagination.\n",
      "As the pagination solution is the slowest by a factor of 4, no need to compare more than one file\n"
     ]
    }
   ],
   "metadata": {}
  }
 ]
}