0002 Using Python pandas DataFrame.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:17c87b4b9899d561140aa682ac1b9ae10d2652457c9e784ac16eeb7acb10ce68"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Setup"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as np\n",
      "import datetime\n",
      "\n",
      "# turn of data table rendering (of the 90s)\n",
      "pd.set_option('display.notebook_repr_html', False)   "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Creating a DataFrame"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Collection of data columns\n",
      "s1 = np.random.randn(5)\n",
      "s2 = [True, True, False, True, False]\n",
      "s3 = ['Apple', 'Banana', 'Tomato', 'Bean', 'Rice']\n",
      "\n",
      "# Dict with added column names\n",
      "data = {'Randnum': s1, 'IsBool': s2, 'Name': s3}\n",
      "df = pd.DataFrame(data)\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 2,
       "text": [
        "  IsBool    Name   Randnum\n",
        "0   True   Apple -0.155137\n",
        "1   True  Banana -0.191264\n",
        "2  False  Tomato  0.764165\n",
        "3   True    Bean  1.818205\n",
        "4  False    Rice -0.618209"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print df.index\n",
      "print df.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Int64Index([0, 1, 2, 3, 4], dtype='int64')\n",
        "Index([u'IsBool', u'Name', u'Randnum'], dtype='object')\n"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Create a new column and assign it all 127\n",
      "df['New'] = 127\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 4,
       "text": [
        "  IsBool    Name   Randnum  New\n",
        "0   True   Apple -0.155137  127\n",
        "1   True  Banana -0.191264  127\n",
        "2  False  Tomato  0.764165  127\n",
        "3   True    Bean  1.818205  127\n",
        "4  False    Rice -0.618209  127"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Data retrieval"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Select the Randnum column\n",
      "df.Randnum"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "0   -0.155137\n",
        "1   -0.191264\n",
        "2    0.764165\n",
        "3    1.818205\n",
        "4   -0.618209\n",
        "Name: Randnum, dtype: float64"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Select the first three rows\n",
      "df[:3]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 6,
       "text": [
        "  IsBool    Name   Randnum  New\n",
        "0   True   Apple -0.155137  127\n",
        "1   True  Banana -0.191264  127\n",
        "2  False  Tomato  0.764165  127"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Select the second row\n",
      "df.ix[1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "IsBool          True\n",
        "Name          Banana\n",
        "Randnum   -0.1912644\n",
        "New              127\n",
        "Name: 1, dtype: object"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Select the Randnum value of the second row\n",
      "df.ix[1, 2] # or\n",
      "df.ix[1, 'Randnum']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "-0.19126442079621636"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Selecting specific rows and columns\n",
      "df.ix[[0, 2, 6], ['Name', 'Randnum', 'Unknown']]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "     Name   Randnum  Unknown\n",
        "0   Apple -0.155137      NaN\n",
        "2  Tomato  0.764165      NaN\n",
        "6     NaN       NaN      NaN"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Conditions"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Retrieve boolean Series, True if Randnum is smaller than zero\n",
      "belowzero = df.Randnum < 0\n",
      "belowzero"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "0     True\n",
        "1     True\n",
        "2    False\n",
        "3    False\n",
        "4     True\n",
        "Name: Randnum, dtype: bool"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Selects all rows meeting the belowzero condition\n",
      "df[belowzero]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 11,
       "text": [
        "  IsBool    Name   Randnum  New\n",
        "0   True   Apple -0.155137  127\n",
        "1   True  Banana -0.191264  127\n",
        "4  False    Rice -0.618209  127"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Retrieve boolean Series, True if Randnum is smaller than zero\n",
      "isapple = df['Name'] == 'Apple'\n",
      "isapple"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 12,
       "text": [
        "0     True\n",
        "1    False\n",
        "2    False\n",
        "3    False\n",
        "4    False\n",
        "Name: Name, dtype: bool"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Select belowzero AND isapple conditions\n",
      "df[belowzero & isapple]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 13,
       "text": [
        "  IsBool   Name   Randnum  New\n",
        "0   True  Apple -0.155137  127"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Select belowzero OR isapple conditions\n",
      "df[belowzero | isapple]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 14,
       "text": [
        "  IsBool    Name   Randnum  New\n",
        "0   True   Apple -0.155137  127\n",
        "1   True  Banana -0.191264  127\n",
        "4  False    Rice -0.618209  127"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Date range as an index"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Set the index to a date range\n",
      "df.index = pd.date_range('1-1-2015', periods=5, freq='d')\n",
      "df.index.name = 'Date'\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "           IsBool    Name   Randnum  New\n",
        "Date                                    \n",
        "2015-01-01   True   Apple -0.155137  127\n",
        "2015-01-02   True  Banana -0.191264  127\n",
        "2015-01-03  False  Tomato  0.764165  127\n",
        "2015-01-04   True    Bean  1.818205  127\n",
        "2015-01-05  False    Rice -0.618209  127"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Nested dictionary to DataFrame"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Create a nested dictionary of equal inner value-count\n",
      "data = {'Paris': {'N': 1.2, 'E': 4, 'S': 2.9, 'W': 0.8},\n",
      "        'Amsterdam': {'N': 2.3, 'E': 1.7, 'S': 2.1, 'W': 7.2},\n",
      "        'London': {'N': 9.7, 'E': 3.1, 'S': 7.2, 'W': 2}}\n",
      "\n",
      "df2 = pd.DataFrame(data)\n",
      "df2"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 16,
       "text": [
        "   Amsterdam  London  Paris\n",
        "E        1.7     3.1    4.0\n",
        "N        2.3     9.7    1.2\n",
        "S        2.1     7.2    2.9\n",
        "W        7.2     2.0    0.8"
       ]
      }
     ],
     "prompt_number": 16
    }
   ],
   "metadata": {}
  }
 ]
}