Lec 45 - Splitting, Applying and Combining.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:d74866ca3f084397f416e3d6f18a192e95ea96e24d66d54d34f1eb92a60d9bc0"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import numpy as np\n",
      "import pandas as pd\n",
      "from pandas import Series, DataFrame"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Let's grab the wine data again\n",
      "dframe_wine = pd.read_csv('winequality_red.csv',sep=';')\n",
      "\n",
      "#Preview\n",
      "dframe_wine.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>fixed acidity</th>\n",
        "      <th>volatile acidity</th>\n",
        "      <th>citric acid</th>\n",
        "      <th>residual sugar</th>\n",
        "      <th>chlorides</th>\n",
        "      <th>free sulfur dioxide</th>\n",
        "      <th>total sulfur dioxide</th>\n",
        "      <th>density</th>\n",
        "      <th>pH</th>\n",
        "      <th>sulphates</th>\n",
        "      <th>alcohol</th>\n",
        "      <th>quality</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>  7.4</td>\n",
        "      <td> 0.70</td>\n",
        "      <td> 0.00</td>\n",
        "      <td> 1.9</td>\n",
        "      <td> 0.076</td>\n",
        "      <td> 11</td>\n",
        "      <td> 34</td>\n",
        "      <td> 0.9978</td>\n",
        "      <td> 3.51</td>\n",
        "      <td> 0.56</td>\n",
        "      <td> 9.4</td>\n",
        "      <td> 5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td>  7.8</td>\n",
        "      <td> 0.88</td>\n",
        "      <td> 0.00</td>\n",
        "      <td> 2.6</td>\n",
        "      <td> 0.098</td>\n",
        "      <td> 25</td>\n",
        "      <td> 67</td>\n",
        "      <td> 0.9968</td>\n",
        "      <td> 3.20</td>\n",
        "      <td> 0.68</td>\n",
        "      <td> 9.8</td>\n",
        "      <td> 5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td>  7.8</td>\n",
        "      <td> 0.76</td>\n",
        "      <td> 0.04</td>\n",
        "      <td> 2.3</td>\n",
        "      <td> 0.092</td>\n",
        "      <td> 15</td>\n",
        "      <td> 54</td>\n",
        "      <td> 0.9970</td>\n",
        "      <td> 3.26</td>\n",
        "      <td> 0.65</td>\n",
        "      <td> 9.8</td>\n",
        "      <td> 5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 11.2</td>\n",
        "      <td> 0.28</td>\n",
        "      <td> 0.56</td>\n",
        "      <td> 1.9</td>\n",
        "      <td> 0.075</td>\n",
        "      <td> 17</td>\n",
        "      <td> 60</td>\n",
        "      <td> 0.9980</td>\n",
        "      <td> 3.16</td>\n",
        "      <td> 0.58</td>\n",
        "      <td> 9.8</td>\n",
        "      <td> 6</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td>  7.4</td>\n",
        "      <td> 0.70</td>\n",
        "      <td> 0.00</td>\n",
        "      <td> 1.9</td>\n",
        "      <td> 0.076</td>\n",
        "      <td> 11</td>\n",
        "      <td> 34</td>\n",
        "      <td> 0.9978</td>\n",
        "      <td> 3.51</td>\n",
        "      <td> 0.56</td>\n",
        "      <td> 9.4</td>\n",
        "      <td> 5</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 2,
       "text": [
        "   fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
        "0            7.4              0.70         0.00             1.9      0.076   \n",
        "1            7.8              0.88         0.00             2.6      0.098   \n",
        "2            7.8              0.76         0.04             2.3      0.092   \n",
        "3           11.2              0.28         0.56             1.9      0.075   \n",
        "4            7.4              0.70         0.00             1.9      0.076   \n",
        "\n",
        "   free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
        "0                   11                    34   0.9978  3.51       0.56   \n",
        "1                   25                    67   0.9968  3.20       0.68   \n",
        "2                   15                    54   0.9970  3.26       0.65   \n",
        "3                   17                    60   0.9980  3.16       0.58   \n",
        "4                   11                    34   0.9978  3.51       0.56   \n",
        "\n",
        "   alcohol  quality  \n",
        "0      9.4        5  \n",
        "1      9.8        5  \n",
        "2      9.8        5  \n",
        "3      9.8        6  \n",
        "4      9.4        5  "
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      " What if we wanted to know the highest alcohol content for each quality range?\n",
      " \n",
      " We can use groupby mechanics to split-apply-combine"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Create a function that assigns a rank to each wine based on alcohol content, with 1 being the highest alcohol content\n",
      "def ranker(df):\n",
      "    df['alc_content_rank'] = np.arange(len(df)) + 1\n",
      "    return df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Now sort the dframe by alcohol in ascending order\n",
      "dframe_wine.sort('alcohol',ascending=False,inplace=True)\n",
      "\n",
      "# Now we'll group by quality and apply our ranking function\n",
      "dframe_wine = dframe_wine.groupby('quality').apply(ranker)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Preview\n",
      "dframe_wine.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>fixed acidity</th>\n",
        "      <th>volatile acidity</th>\n",
        "      <th>citric acid</th>\n",
        "      <th>residual sugar</th>\n",
        "      <th>chlorides</th>\n",
        "      <th>free sulfur dioxide</th>\n",
        "      <th>total sulfur dioxide</th>\n",
        "      <th>density</th>\n",
        "      <th>pH</th>\n",
        "      <th>sulphates</th>\n",
        "      <th>alcohol</th>\n",
        "      <th>quality</th>\n",
        "      <th>alc_content_rank</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>652 </th>\n",
        "      <td> 15.9</td>\n",
        "      <td> 0.36</td>\n",
        "      <td> 0.65</td>\n",
        "      <td> 7.5</td>\n",
        "      <td> 0.096</td>\n",
        "      <td> 22</td>\n",
        "      <td> 71</td>\n",
        "      <td> 0.99760</td>\n",
        "      <td> 2.98</td>\n",
        "      <td> 0.84</td>\n",
        "      <td> 14.9</td>\n",
        "      <td> 5</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>588 </th>\n",
        "      <td>  5.0</td>\n",
        "      <td> 0.42</td>\n",
        "      <td> 0.24</td>\n",
        "      <td> 2.0</td>\n",
        "      <td> 0.060</td>\n",
        "      <td> 19</td>\n",
        "      <td> 50</td>\n",
        "      <td> 0.99170</td>\n",
        "      <td> 3.72</td>\n",
        "      <td> 0.74</td>\n",
        "      <td> 14.0</td>\n",
        "      <td> 8</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>142 </th>\n",
        "      <td>  5.2</td>\n",
        "      <td> 0.34</td>\n",
        "      <td> 0.00</td>\n",
        "      <td> 1.8</td>\n",
        "      <td> 0.050</td>\n",
        "      <td> 27</td>\n",
        "      <td> 63</td>\n",
        "      <td> 0.99160</td>\n",
        "      <td> 3.68</td>\n",
        "      <td> 0.79</td>\n",
        "      <td> 14.0</td>\n",
        "      <td> 6</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>144 </th>\n",
        "      <td>  5.2</td>\n",
        "      <td> 0.34</td>\n",
        "      <td> 0.00</td>\n",
        "      <td> 1.8</td>\n",
        "      <td> 0.050</td>\n",
        "      <td> 27</td>\n",
        "      <td> 63</td>\n",
        "      <td> 0.99160</td>\n",
        "      <td> 3.68</td>\n",
        "      <td> 0.79</td>\n",
        "      <td> 14.0</td>\n",
        "      <td> 6</td>\n",
        "      <td> 2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1270</th>\n",
        "      <td>  5.0</td>\n",
        "      <td> 0.38</td>\n",
        "      <td> 0.01</td>\n",
        "      <td> 1.6</td>\n",
        "      <td> 0.048</td>\n",
        "      <td> 26</td>\n",
        "      <td> 60</td>\n",
        "      <td> 0.99084</td>\n",
        "      <td> 3.70</td>\n",
        "      <td> 0.75</td>\n",
        "      <td> 14.0</td>\n",
        "      <td> 6</td>\n",
        "      <td> 3</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "      fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
        "652            15.9              0.36         0.65             7.5      0.096   \n",
        "588             5.0              0.42         0.24             2.0      0.060   \n",
        "142             5.2              0.34         0.00             1.8      0.050   \n",
        "144             5.2              0.34         0.00             1.8      0.050   \n",
        "1270            5.0              0.38         0.01             1.6      0.048   \n",
        "\n",
        "      free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
        "652                    22                    71  0.99760  2.98       0.84   \n",
        "588                    19                    50  0.99170  3.72       0.74   \n",
        "142                    27                    63  0.99160  3.68       0.79   \n",
        "144                    27                    63  0.99160  3.68       0.79   \n",
        "1270                   26                    60  0.99084  3.70       0.75   \n",
        "\n",
        "      alcohol  quality  alc_content_rank  \n",
        "652      14.9        5                 1  \n",
        "588      14.0        8                 1  \n",
        "142      14.0        6                 1  \n",
        "144      14.0        6                 2  \n",
        "1270     14.0        6                 3  "
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Now finally we can just call for the dframe where the alc_content_rank == 1\n",
      "\n",
      "# Get the numebr of quality counts\n",
      "num_of_qual = dframe_wine['quality'].value_counts()\n",
      "\n",
      "#Show\n",
      "num_of_qual"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 13,
       "text": [
        "5    681\n",
        "6    638\n",
        "7    199\n",
        "4     53\n",
        "8     18\n",
        "3     10\n",
        "dtype: int64"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Now we'll show the combined info for teh wines that had the highest alcohol content for their respective rank!\n",
      "dframe_wine[dframe_wine.alc_content_rank == 1].head(len(num_of_qual))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>fixed acidity</th>\n",
        "      <th>volatile acidity</th>\n",
        "      <th>citric acid</th>\n",
        "      <th>residual sugar</th>\n",
        "      <th>chlorides</th>\n",
        "      <th>free sulfur dioxide</th>\n",
        "      <th>total sulfur dioxide</th>\n",
        "      <th>density</th>\n",
        "      <th>pH</th>\n",
        "      <th>sulphates</th>\n",
        "      <th>alcohol</th>\n",
        "      <th>quality</th>\n",
        "      <th>alc_content_rank</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>652</th>\n",
        "      <td> 15.9</td>\n",
        "      <td> 0.36</td>\n",
        "      <td> 0.65</td>\n",
        "      <td> 7.5</td>\n",
        "      <td> 0.096</td>\n",
        "      <td> 22</td>\n",
        "      <td> 71</td>\n",
        "      <td> 0.99760</td>\n",
        "      <td> 2.98</td>\n",
        "      <td> 0.84</td>\n",
        "      <td> 14.9</td>\n",
        "      <td> 5</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>588</th>\n",
        "      <td>  5.0</td>\n",
        "      <td> 0.42</td>\n",
        "      <td> 0.24</td>\n",
        "      <td> 2.0</td>\n",
        "      <td> 0.060</td>\n",
        "      <td> 19</td>\n",
        "      <td> 50</td>\n",
        "      <td> 0.99170</td>\n",
        "      <td> 3.72</td>\n",
        "      <td> 0.74</td>\n",
        "      <td> 14.0</td>\n",
        "      <td> 8</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>142</th>\n",
        "      <td>  5.2</td>\n",
        "      <td> 0.34</td>\n",
        "      <td> 0.00</td>\n",
        "      <td> 1.8</td>\n",
        "      <td> 0.050</td>\n",
        "      <td> 27</td>\n",
        "      <td> 63</td>\n",
        "      <td> 0.99160</td>\n",
        "      <td> 3.68</td>\n",
        "      <td> 0.79</td>\n",
        "      <td> 14.0</td>\n",
        "      <td> 6</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>821</th>\n",
        "      <td>  4.9</td>\n",
        "      <td> 0.42</td>\n",
        "      <td> 0.00</td>\n",
        "      <td> 2.1</td>\n",
        "      <td> 0.048</td>\n",
        "      <td> 16</td>\n",
        "      <td> 42</td>\n",
        "      <td> 0.99154</td>\n",
        "      <td> 3.71</td>\n",
        "      <td> 0.74</td>\n",
        "      <td> 14.0</td>\n",
        "      <td> 7</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>45 </th>\n",
        "      <td>  4.6</td>\n",
        "      <td> 0.52</td>\n",
        "      <td> 0.15</td>\n",
        "      <td> 2.1</td>\n",
        "      <td> 0.054</td>\n",
        "      <td>  8</td>\n",
        "      <td> 65</td>\n",
        "      <td> 0.99340</td>\n",
        "      <td> 3.90</td>\n",
        "      <td> 0.56</td>\n",
        "      <td> 13.1</td>\n",
        "      <td> 4</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>899</th>\n",
        "      <td>  8.3</td>\n",
        "      <td> 1.02</td>\n",
        "      <td> 0.02</td>\n",
        "      <td> 3.4</td>\n",
        "      <td> 0.084</td>\n",
        "      <td>  6</td>\n",
        "      <td> 11</td>\n",
        "      <td> 0.99892</td>\n",
        "      <td> 3.48</td>\n",
        "      <td> 0.49</td>\n",
        "      <td> 11.0</td>\n",
        "      <td> 3</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "     fixed acidity  volatile acidity  citric acid  residual sugar  chlorides  \\\n",
        "652           15.9              0.36         0.65             7.5      0.096   \n",
        "588            5.0              0.42         0.24             2.0      0.060   \n",
        "142            5.2              0.34         0.00             1.8      0.050   \n",
        "821            4.9              0.42         0.00             2.1      0.048   \n",
        "45             4.6              0.52         0.15             2.1      0.054   \n",
        "899            8.3              1.02         0.02             3.4      0.084   \n",
        "\n",
        "     free sulfur dioxide  total sulfur dioxide  density    pH  sulphates  \\\n",
        "652                   22                    71  0.99760  2.98       0.84   \n",
        "588                   19                    50  0.99170  3.72       0.74   \n",
        "142                   27                    63  0.99160  3.68       0.79   \n",
        "821                   16                    42  0.99154  3.71       0.74   \n",
        "45                     8                    65  0.99340  3.90       0.56   \n",
        "899                    6                    11  0.99892  3.48       0.49   \n",
        "\n",
        "     alcohol  quality  alc_content_rank  \n",
        "652     14.9        5                 1  \n",
        "588     14.0        8                 1  \n",
        "142     14.0        6                 1  \n",
        "821     14.0        7                 1  \n",
        "45      13.1        4                 1  \n",
        "899     11.0        3                 1  "
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Awesome! Ask yourself if there are any trends you would like to find in this data?\n",
      "# Is there a relationship between wine ranking and alcohol content?"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}