pandas_practice.ipynb

{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Load libraries"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as np"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Load data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.read_excel(\"/Users/admin/Downloads/med_remit.xlsx\",'Sheet1')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.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>id</th>\n",
        "      <th>med</th>\n",
        "      <th>amount</th>\n",
        "      <th>date1</th>\n",
        "      <th>date2</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 74517321</td>\n",
        "      <td> Alprazolam</td>\n",
        "      <td> 3.50</td>\n",
        "      <td>2010-12-07</td>\n",
        "      <td>2011-06-15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 73273394</td>\n",
        "      <td> Alprazolam</td>\n",
        "      <td> 5.25</td>\n",
        "      <td>2012-08-14</td>\n",
        "      <td>2013-02-08</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 73273394</td>\n",
        "      <td> Alprazolam</td>\n",
        "      <td> 7.00</td>\n",
        "      <td>2012-08-14</td>\n",
        "      <td>2013-02-08</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 73273394</td>\n",
        "      <td> Alprazolam</td>\n",
        "      <td> 5.25</td>\n",
        "      <td>2012-08-14</td>\n",
        "      <td>2013-02-08</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 73273394</td>\n",
        "      <td> Alprazolam</td>\n",
        "      <td> 3.50</td>\n",
        "      <td>2012-08-14</td>\n",
        "      <td>2013-02-08</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 6,
       "text": [
        "         id         med  amount      date1      date2\n",
        "0  74517321  Alprazolam    3.50 2010-12-07 2011-06-15\n",
        "1  73273394  Alprazolam    5.25 2012-08-14 2013-02-08\n",
        "2  73273394  Alprazolam    7.00 2012-08-14 2013-02-08\n",
        "3  73273394  Alprazolam    5.25 2012-08-14 2013-02-08\n",
        "4  73273394  Alprazolam    3.50 2012-08-14 2013-02-08"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "prev\n",
      "\n",
      "    df = pd.read_excel(\"/Users/admin/Downloads/Untitled.xlsx\",'Sheet1')"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "prac = df.groupby(['id','med'])\n"
     ],
     "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></th>\n",
        "      <th>amount</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>id</th>\n",
        "      <th>med</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th rowspan=\"5\" valign=\"top\">34657339</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td>   245.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Clonazepam</th>\n",
        "      <td>    47.25</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Mirtazapine</th>\n",
        "      <td>   840.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Paroxetine</th>\n",
        "      <td>  3325.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Ziprasidone</th>\n",
        "      <td>   280.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"3\" valign=\"top\">39896937</th>\n",
        "      <th>Amisulpride</th>\n",
        "      <td>  5950.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Venlafaxine</th>\n",
        "      <td> 12600.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>methylPHENI</th>\n",
        "      <td>   980.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"3\" valign=\"top\">39913584</th>\n",
        "      <th>Escitalopram</th>\n",
        "      <td>   875.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lamotrigine</th>\n",
        "      <td>  6562.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Quetiapine</th>\n",
        "      <td> 10700.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"4\" valign=\"top\">40206022</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td>   122.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Escitalopram</th>\n",
        "      <td>   700.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Fluoxetin</th>\n",
        "      <td>   490.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td>    14.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"4\" valign=\"top\">40244839</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td>  1942.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Naltrexone</th>\n",
        "      <td>  3150.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidon</th>\n",
        "      <td>    35.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidone</th>\n",
        "      <td>    14.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"2\" valign=\"top\">40347219</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td>   815.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Escitalopram</th>\n",
        "      <td>  3100.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>40359010</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td>   122.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"3\" valign=\"top\">40388823</th>\n",
        "      <th>Escitalopram</th>\n",
        "      <td>   980.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidon</th>\n",
        "      <td>    57.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidone</th>\n",
        "      <td>    42.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>40406985</th>\n",
        "      <th>Escitalopram</th>\n",
        "      <td>  1120.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"4\" valign=\"top\">40484978</th>\n",
        "      <th>Clonazepam</th>\n",
        "      <td>   157.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Paroxetine</th>\n",
        "      <td>  7262.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Propranolol</th>\n",
        "      <td>  9520.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Quetiapine</th>\n",
        "      <td> 39900.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>...</th>\n",
        "      <th>...</th>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"2\" valign=\"top\">72667794</th>\n",
        "      <th>Sulpiride</th>\n",
        "      <td>  1050.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Trihexyphenidyl</th>\n",
        "      <td>    36.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"2\" valign=\"top\">72687053</th>\n",
        "      <th>Lorazepam</th>\n",
        "      <td>     7.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Quetiapine</th>\n",
        "      <td>   525.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"9\" valign=\"top\">73273394</th>\n",
        "      <th>Alprazolam</th>\n",
        "      <td>   170.75</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Clonazepam</th>\n",
        "      <td>    14.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Divalproex</th>\n",
        "      <td> 20000.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Fluoxetin</th>\n",
        "      <td>  8580.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lamotrigine</th>\n",
        "      <td>  1575.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lithium</th>\n",
        "      <td>  4200.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Propranolol</th>\n",
        "      <td>  6690.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Quetiapine</th>\n",
        "      <td> 36600.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Topiramate</th>\n",
        "      <td>  5225.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>73887306</th>\n",
        "      <th>Escitalopram</th>\n",
        "      <td>    70.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"9\" valign=\"top\">74517321</th>\n",
        "      <th>Alprazolam</th>\n",
        "      <td>     3.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td>   897.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Benztropine</th>\n",
        "      <td>    28.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Fluoxetin</th>\n",
        "      <td>  1820.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td>   322.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Olanzapine</th>\n",
        "      <td>   157.50</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Paliperidone</th>\n",
        "      <td>   126.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Propranolol</th>\n",
        "      <td>   840.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Ziprasidone</th>\n",
        "      <td>   420.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"7\" valign=\"top\">74595857</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td>   300.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Fluoxetin</th>\n",
        "      <td>  5230.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td>   204.25</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Olanzapine</th>\n",
        "      <td>    25.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Propranolol</th>\n",
        "      <td>  2400.00</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidon</th>\n",
        "      <td>    14.75</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidone</th>\n",
        "      <td>    19.00</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>164 rows \u00d7 1 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 4,
       "text": [
        "                            amount\n",
        "id       med                      \n",
        "34657339 Aripiprazole       245.00\n",
        "         Clonazepam          47.25\n",
        "         Mirtazapine        840.00\n",
        "         Paroxetine        3325.00\n",
        "         Ziprasidone        280.00\n",
        "39896937 Amisulpride       5950.00\n",
        "         Venlafaxine      12600.00\n",
        "         methylPHENI        980.00\n",
        "39913584 Escitalopram       875.00\n",
        "         Lamotrigine       6562.50\n",
        "         Quetiapine       10700.00\n",
        "40206022 Aripiprazole       122.50\n",
        "         Escitalopram       700.00\n",
        "         Fluoxetin          490.00\n",
        "         Lorazepam           14.00\n",
        "40244839 Aripiprazole      1942.50\n",
        "         Naltrexone        3150.00\n",
        "         Risperidon          35.00\n",
        "         Risperidone         14.00\n",
        "40347219 Aripiprazole       815.00\n",
        "         Escitalopram      3100.00\n",
        "40359010 Aripiprazole       122.50\n",
        "40388823 Escitalopram       980.00\n",
        "         Risperidon          57.00\n",
        "         Risperidone         42.00\n",
        "40406985 Escitalopram      1120.00\n",
        "40484978 Clonazepam         157.50\n",
        "         Paroxetine        7262.50\n",
        "         Propranolol       9520.00\n",
        "         Quetiapine       39900.00\n",
        "...                            ...\n",
        "72667794 Sulpiride         1050.00\n",
        "         Trihexyphenidyl     36.00\n",
        "72687053 Lorazepam            7.00\n",
        "         Quetiapine         525.00\n",
        "73273394 Alprazolam         170.75\n",
        "         Clonazepam          14.00\n",
        "         Divalproex       20000.00\n",
        "         Fluoxetin         8580.00\n",
        "         Lamotrigine       1575.00\n",
        "         Lithium           4200.00\n",
        "         Propranolol       6690.00\n",
        "         Quetiapine       36600.00\n",
        "         Topiramate        5225.00\n",
        "73887306 Escitalopram        70.00\n",
        "74517321 Alprazolam           3.50\n",
        "         Aripiprazole       897.50\n",
        "         Benztropine         28.00\n",
        "         Fluoxetin         1820.00\n",
        "         Lorazepam          322.50\n",
        "         Olanzapine         157.50\n",
        "         Paliperidone       126.00\n",
        "         Propranolol        840.00\n",
        "         Ziprasidone        420.00\n",
        "74595857 Aripiprazole       300.00\n",
        "         Fluoxetin         5230.00\n",
        "         Lorazepam          204.25\n",
        "         Olanzapine          25.00\n",
        "         Propranolol       2400.00\n",
        "         Risperidon          14.75\n",
        "         Risperidone         19.00\n",
        "\n",
        "[164 rows x 1 columns]"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sumTable = pd.pivot_table(df, values='amount', index='id',columns='med',aggfunc=np.sum)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sumTable.to_excel(\"/Users/admin/Downloads/haha.xlsx\",\"Sheet1\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!open /Users/admin/Downloads/haha.xlsx -a mail"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 10
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Look at the data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print len(df),'lines of medication information'\n",
      "df.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "12976 lines of medication information\n"
       ]
      },
      {
       "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>id</th>\n",
        "      <th>med</th>\n",
        "      <th>amount</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 16857249</td>\n",
        "      <td>   Lorazepam</td>\n",
        "      <td> 21</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 16857249</td>\n",
        "      <td>  Quetiapine</td>\n",
        "      <td> 14</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 16857249</td>\n",
        "      <td> Risperidone</td>\n",
        "      <td> 14</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 16857249</td>\n",
        "      <td>   Lorazepam</td>\n",
        "      <td> 15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 16857249</td>\n",
        "      <td> Risperidone</td>\n",
        "      <td> 42</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 69,
       "text": [
        "         id          med  amount\n",
        "0  16857249    Lorazepam      21\n",
        "1  16857249   Quetiapine      14\n",
        "2  16857249  Risperidone      14\n",
        "3  16857249    Lorazepam      15\n",
        "4  16857249  Risperidone      42"
       ]
      }
     ],
     "prompt_number": 69
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Pivot tables"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.pivot_table(df, values='amount',index='id',columns='med',fill_value='missing',dropna=False,margins=True).head().ix[:,-10:]"
     ],
     "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>Risperidone</th>\n",
        "      <th>Sertraline</th>\n",
        "      <th>Topiramate</th>\n",
        "      <th>Trazodone</th>\n",
        "      <th>Trihexyphenidyl</th>\n",
        "      <th>Valproate</th>\n",
        "      <th>Venlafaxine</th>\n",
        "      <th>Ziprasidone</th>\n",
        "      <th>Zolpidem</th>\n",
        "      <th>All</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>id</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>16857249</th>\n",
        "      <td> 18.80556</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td>  missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> 18.716667</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>30276738</th>\n",
        "      <td>  missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td>  missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> 17.500000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>31596341</th>\n",
        "      <td> 16.95192</td>\n",
        "      <td> missing</td>\n",
        "      <td>   12.95</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td>  20.125</td>\n",
        "      <td> missing</td>\n",
        "      <td>  missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> 17.720486</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>33068859</th>\n",
        "      <td>  missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td>  missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> 15.910000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>33319982</th>\n",
        "      <td>  missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> missing</td>\n",
        "      <td> 23.21429</td>\n",
        "      <td> missing</td>\n",
        "      <td> 23.034483</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 43,
       "text": [
        "         Risperidone Sertraline Topiramate Trazodone Trihexyphenidyl  \\\n",
        "id                                                                     \n",
        "16857249    18.80556    missing    missing   missing         missing   \n",
        "30276738     missing    missing    missing   missing         missing   \n",
        "31596341    16.95192    missing      12.95   missing         missing   \n",
        "33068859     missing    missing    missing   missing         missing   \n",
        "33319982     missing    missing    missing   missing         missing   \n",
        "\n",
        "         Valproate Venlafaxine Ziprasidone Zolpidem        All  \n",
        "id                                                              \n",
        "16857249   missing     missing     missing  missing  18.716667  \n",
        "30276738   missing     missing     missing  missing  17.500000  \n",
        "31596341    20.125     missing     missing  missing  17.720486  \n",
        "33068859   missing     missing     missing  missing  15.910000  \n",
        "33319982   missing     missing    23.21429  missing  23.034483  "
       ]
      }
     ],
     "prompt_number": 43
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.pivot_table(df, values='amount',index='id',columns='med',aggfunc=np.sum,dropna=False,margins=True).head().ix[:,-10:]"
     ],
     "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>Risperidone</th>\n",
        "      <th>Sertraline</th>\n",
        "      <th>Topiramate</th>\n",
        "      <th>Trazodone</th>\n",
        "      <th>Trihexyphenidyl</th>\n",
        "      <th>Valproate</th>\n",
        "      <th>Venlafaxine</th>\n",
        "      <th>Ziprasidone</th>\n",
        "      <th>Zolpidem</th>\n",
        "      <th>All</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>id</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>16857249</th>\n",
        "      <td>  338.50</td>\n",
        "      <td>NaN</td>\n",
        "      <td>   NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> 1123.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>30276738</th>\n",
        "      <td>     NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>   NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>  315.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>31596341</th>\n",
        "      <td> 1322.25</td>\n",
        "      <td>NaN</td>\n",
        "      <td> 129.5</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> 161</td>\n",
        "      <td>NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> 5103.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>33068859</th>\n",
        "      <td>     NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>   NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>  795.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>33319982</th>\n",
        "      <td>     NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>   NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> NaN</td>\n",
        "      <td>NaN</td>\n",
        "      <td> 325</td>\n",
        "      <td>NaN</td>\n",
        "      <td> 1336.0</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 54,
       "text": [
        "          Risperidone  Sertraline  Topiramate  Trazodone  Trihexyphenidyl  \\\n",
        "id                                                                          \n",
        "16857249       338.50         NaN         NaN        NaN              NaN   \n",
        "30276738          NaN         NaN         NaN        NaN              NaN   \n",
        "31596341      1322.25         NaN       129.5        NaN              NaN   \n",
        "33068859          NaN         NaN         NaN        NaN              NaN   \n",
        "33319982          NaN         NaN         NaN        NaN              NaN   \n",
        "\n",
        "          Valproate  Venlafaxine  Ziprasidone  Zolpidem     All  \n",
        "id                                                               \n",
        "16857249        NaN          NaN          NaN       NaN  1123.0  \n",
        "30276738        NaN          NaN          NaN       NaN   315.0  \n",
        "31596341        161          NaN          NaN       NaN  5103.5  \n",
        "33068859        NaN          NaN          NaN       NaN   795.5  \n",
        "33319982        NaN          NaN          325       NaN  1336.0  "
       ]
      }
     ],
     "prompt_number": 54
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "'dropna' option check"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dropTrue=pd.pivot_table(df, values='amount',index='id',columns='med',aggfunc=np.sum,dropna=True,margins=True)\n",
      "dropFalse=pd.pivot_table(df, values='amount',index='id',columns='med',aggfunc=np.sum,dropna=False,margins=True)\n",
      "\n",
      "print len(dropTrue),len(dropFalse)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "80 80\n"
       ]
      }
     ],
     "prompt_number": 58
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print len(dropTrue.columns),len(dropFalse.columns)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "42 42\n"
       ]
      }
     ],
     "prompt_number": 59
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Groupby function"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print len(df)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "12976\n"
       ]
      }
     ],
     "prompt_number": 70
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.groupby('med').size().head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 71,
       "text": [
        "med\n",
        "Alprazolam       145\n",
        "Amisulpride      186\n",
        "Amitriptyline      4\n",
        "Aripiprazole     972\n",
        "Benztropine       84\n",
        "dtype: int64"
       ]
      }
     ],
     "prompt_number": 71
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "gb = df.groupby('med')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 72
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "gb.agg(np.sum).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>id</th>\n",
        "      <th>amount</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>med</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>Alprazolam</th>\n",
        "      <td>  8172999103</td>\n",
        "      <td>  2601.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Amisulpride</th>\n",
        "      <td> 13062115298</td>\n",
        "      <td>  5396.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Amitriptyline</th>\n",
        "      <td>   171628112</td>\n",
        "      <td>    98.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td> 47061567951</td>\n",
        "      <td> 22636.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Benztropine</th>\n",
        "      <td>  3447025896</td>\n",
        "      <td>  1808.0</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 80,
       "text": [
        "                        id   amount\n",
        "med                                \n",
        "Alprazolam      8172999103   2601.5\n",
        "Amisulpride    13062115298   5396.0\n",
        "Amitriptyline    171628112     98.0\n",
        "Aripiprazole   47061567951  22636.0\n",
        "Benztropine     3447025896   1808.0"
       ]
      }
     ],
     "prompt_number": 80
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Group by other categories"
     ]
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Number of medication types"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(df.med.drop_duplicates())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 95,
       "text": [
        "41"
       ]
      }
     ],
     "prompt_number": 95
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Random criteria make up"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "numCategory = np.random.randint(1,5,size=41)\n",
      "numCategory"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 192,
       "text": [
        "array([3, 4, 3, 3, 1, 1, 3, 1, 4, 1, 1, 3, 1, 2, 3, 4, 4, 1, 2, 4, 1, 1, 4,\n",
        "       1, 3, 3, 2, 2, 4, 4, 3, 1, 2, 3, 3, 1, 3, 4, 2, 2, 3])"
       ]
      }
     ],
     "prompt_number": 192
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Price tag"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "numToStrength={1:'high',\n",
      "               2:'medium',\n",
      "               3:'low',\n",
      "               4:'zero'}"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 193
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Make into dictionary"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "map_dictionary = dict(zip(df.med.drop_duplicates().tolist(),[numToStrength[x] for x in numCategory]))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 194
    },
    {
     "cell_type": "heading",
     "level": 4,
     "metadata": {},
     "source": [
      "Groupby the category"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "gb = df.set_index('med').groupby(map_dictionary)\n",
      "gb.sum()"
     ],
     "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>id</th>\n",
        "      <th>amount</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>high</th>\n",
        "      <td> 197726831078</td>\n",
        "      <td>  87846.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>low</th>\n",
        "      <td> 277840111105</td>\n",
        "      <td> 130155.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>medium</th>\n",
        "      <td>  19611607026</td>\n",
        "      <td>   9883.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>zero</th>\n",
        "      <td> 154958308882</td>\n",
        "      <td>  70066.0</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 195,
       "text": [
        "                  id    amount\n",
        "high    197726831078   87846.5\n",
        "low     277840111105  130155.5\n",
        "medium   19611607026    9883.5\n",
        "zero    154958308882   70066.0"
       ]
      }
     ],
     "prompt_number": 195
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Groupby other technique"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "gb = df.groupby(['id','med'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 197
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Pivot tables"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "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></th>\n",
        "      <th>amount</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>id</th>\n",
        "      <th>med</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th rowspan=\"5\" valign=\"top\">16857249</th>\n",
        "      <th>Escitalopram</th>\n",
        "      <td> 20.571429</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 21.937500</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Olanzapine</th>\n",
        "      <td> 17.500000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Quetiapine</th>\n",
        "      <td> 11.050000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidone</th>\n",
        "      <td> 18.805556</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>30276738</th>\n",
        "      <th>Quetiapine</th>\n",
        "      <td> 17.500000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"9\" valign=\"top\">31596341</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td> 20.150000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Benztropine</th>\n",
        "      <td> 13.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Clonazepam</th>\n",
        "      <td> 22.604167</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Escitalopram</th>\n",
        "      <td> 18.562500</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 16.018382</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Propranolol</th>\n",
        "      <td> 17.125000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidone</th>\n",
        "      <td> 16.951923</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Topiramate</th>\n",
        "      <td> 12.950000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Valproate</th>\n",
        "      <td> 20.125000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"2\" valign=\"top\">33068859</th>\n",
        "      <th>Escitalopram</th>\n",
        "      <td> 15.750000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Quetiapine</th>\n",
        "      <td> 15.923913</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"4\" valign=\"top\">33319982</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td> 28.250000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Fluoxetine</th>\n",
        "      <td> 20.678571</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 23.403846</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Ziprasidone</th>\n",
        "      <td> 23.214286</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"3\" valign=\"top\">33364292</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td> 29.900000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 16.287500</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Olanzapine</th>\n",
        "      <td> 19.821429</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"5\" valign=\"top\">33674829</th>\n",
        "      <th>Benztropine</th>\n",
        "      <td> 24.916667</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Clonazepam</th>\n",
        "      <td> 16.350000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 14.850000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidone</th>\n",
        "      <td> 16.818182</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Trihexyphenidyl</th>\n",
        "      <td> 18.178571</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>33795447</th>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 17.226415</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>...</th>\n",
        "      <th>...</th>\n",
        "      <td>...</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"3\" valign=\"top\">73854205</th>\n",
        "      <th>Olanzapine</th>\n",
        "      <td> 42.500000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Quetiapine</th>\n",
        "      <td> 37.870968</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Ziprasidone</th>\n",
        "      <td> 30.833333</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>73887306</th>\n",
        "      <th>Escitalopram</th>\n",
        "      <td> 56.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"9\" valign=\"top\">74306545</th>\n",
        "      <th>Clonazepam</th>\n",
        "      <td> 25.333333</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Diazepam</th>\n",
        "      <td> 27.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Escitalopram</th>\n",
        "      <td> 41.659091</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Fluoxetine</th>\n",
        "      <td> 37.625000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 23.156250</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Modafinil</th>\n",
        "      <td> 17.500000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Olanzapine</th>\n",
        "      <td> 24.941667</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Sertraline</th>\n",
        "      <td> 19.272727</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Zolpidem</th>\n",
        "      <td> 17.750000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"10\" valign=\"top\">74517321</th>\n",
        "      <th>Alprazolam</th>\n",
        "      <td>  8.250000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td> 27.677632</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Benztropine</th>\n",
        "      <td> 31.500000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Fluoxetine</th>\n",
        "      <td> 22.841667</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 19.616935</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Olanzapine</th>\n",
        "      <td> 17.916667</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Paliperidone</th>\n",
        "      <td> 14.250000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Propranolol</th>\n",
        "      <td> 24.220000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Trihexyphenidyl</th>\n",
        "      <td> 11.857143</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Ziprasidone</th>\n",
        "      <td> 23.500000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"7\" valign=\"top\">74595857</th>\n",
        "      <th>Aripiprazole</th>\n",
        "      <td> 20.555556</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Clonazepam</th>\n",
        "      <td> 16.950000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Fluoxetine</th>\n",
        "      <td> 17.580000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Lorazepam</th>\n",
        "      <td> 17.375000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Olanzapine</th>\n",
        "      <td> 10.500000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Propranolol</th>\n",
        "      <td> 12.878788</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Risperidone</th>\n",
        "      <td> 11.625000</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>419 rows \u00d7 1 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 212,
       "text": [
        "                             amount\n",
        "id       med                       \n",
        "16857249 Escitalopram     20.571429\n",
        "         Lorazepam        21.937500\n",
        "         Olanzapine       17.500000\n",
        "         Quetiapine       11.050000\n",
        "         Risperidone      18.805556\n",
        "30276738 Quetiapine       17.500000\n",
        "31596341 Aripiprazole     20.150000\n",
        "         Benztropine      13.000000\n",
        "         Clonazepam       22.604167\n",
        "         Escitalopram     18.562500\n",
        "         Lorazepam        16.018382\n",
        "         Propranolol      17.125000\n",
        "         Risperidone      16.951923\n",
        "         Topiramate       12.950000\n",
        "         Valproate        20.125000\n",
        "33068859 Escitalopram     15.750000\n",
        "         Quetiapine       15.923913\n",
        "33319982 Aripiprazole     28.250000\n",
        "         Fluoxetine       20.678571\n",
        "         Lorazepam        23.403846\n",
        "         Ziprasidone      23.214286\n",
        "33364292 Aripiprazole     29.900000\n",
        "         Lorazepam        16.287500\n",
        "         Olanzapine       19.821429\n",
        "33674829 Benztropine      24.916667\n",
        "         Clonazepam       16.350000\n",
        "         Lorazepam        14.850000\n",
        "         Risperidone      16.818182\n",
        "         Trihexyphenidyl  18.178571\n",
        "33795447 Lorazepam        17.226415\n",
        "...                             ...\n",
        "73854205 Olanzapine       42.500000\n",
        "         Quetiapine       37.870968\n",
        "         Ziprasidone      30.833333\n",
        "73887306 Escitalopram     56.000000\n",
        "74306545 Clonazepam       25.333333\n",
        "         Diazepam         27.000000\n",
        "         Escitalopram     41.659091\n",
        "         Fluoxetine       37.625000\n",
        "         Lorazepam        23.156250\n",
        "         Modafinil        17.500000\n",
        "         Olanzapine       24.941667\n",
        "         Sertraline       19.272727\n",
        "         Zolpidem         17.750000\n",
        "74517321 Alprazolam        8.250000\n",
        "         Aripiprazole     27.677632\n",
        "         Benztropine      31.500000\n",
        "         Fluoxetine       22.841667\n",
        "         Lorazepam        19.616935\n",
        "         Olanzapine       17.916667\n",
        "         Paliperidone     14.250000\n",
        "         Propranolol      24.220000\n",
        "         Trihexyphenidyl  11.857143\n",
        "         Ziprasidone      23.500000\n",
        "74595857 Aripiprazole     20.555556\n",
        "         Clonazepam       16.950000\n",
        "         Fluoxetine       17.580000\n",
        "         Lorazepam        17.375000\n",
        "         Olanzapine       10.500000\n",
        "         Propranolol      12.878788\n",
        "         Risperidone      11.625000\n",
        "\n",
        "[419 rows x 1 columns]"
       ]
      }
     ],
     "prompt_number": 212
    }
   ],
   "metadata": {}
  }
 ]
}