3_intro_to_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# ICS 434: DATA SCIENCE FUNDAMENTALS\n",
    "\n",
    "## Introduction to Pandas: Series and DataFrame classes\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Brief Introduction to Pandas\n",
    "\n",
    "* `Pandas` is the de facto package for working with tabular data.\n",
    "  * Think of it as `Excel` on steroids (without the graphical interface and the menus).\n",
    "* Supports a plethora of file formats (`Excel`, `CSV`, `TSV`, `JSON`, `hdf5`, ....)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Brief Introduction to Pandas -- Cont'd\n",
    "\n",
    "<img src=\"https://www.dropbox.com/s/vi451zvf9sw8jfr/pandas_architecture.png?dl=1\" width=\"800\">\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Pandas `Series` and `DataFrames`\n",
    "\n",
    "* Pandas relies principally on two types of data structures:\n",
    "\n",
    "1. `Series`: those are list-like objects that store data in a given order.\n",
    "    * It helps to think of a `Series` as a column (or row) in `Excel`.\n",
    "\n",
    "2. `DataFrames`: those are spreadsheet-like tables that contain one or more `Series`.\n",
    "    * It helps to think of a `DataFrame` as a table (or worksheet) in `Excel`.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### About the Data\n",
    "\n",
    "- The examples below use the cost of drugs prescribed to Medicare patients.\n",
    "\n",
    "- The complete dataset is publicly available on the Centers for Medicare & Medicaid Services ([`CMS` website](https://www.cms.gov/OpenPayments/Explore-the-Data/Dataset-Downloads.html)):\n",
    "\n",
    "- This toy dataset contains the following columns:\n",
    "\n",
    "\n",
    "| Column | Description | \n",
    "|:----------|-----------|\n",
    "| `unique_id`| A unique identifier for a Medicare claim to CMS |\n",
    "| `doctor_id` | The Unique Identifier of the doctor who <br/> prescribed the medicine |\n",
    "| `specialty` | The specialty of the doctor who prescribed the medicine |\n",
    "| `medication` | The medication prescribed |\n",
    "| `nb_beneficiaries` | The number of beneficiaries the <br/> medicine was prescribed to |\n",
    "| `spending` | The total cost of the medicine prescribed|\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "<img src=\"https://www.dropbox.com/s/ha5s9t1n7yl0yx4/medicare_data.png?dl=1\" alt=\"drawing\" style=\"width:800px\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### `Series`\n",
    "\n",
    "\n",
    "* A `Series` is conceptually \"similar\" to a python `list`\n",
    "  * An ordered collection of items\n",
    "\n",
    "* All the items in a `Series` have the **same datatype**.\n",
    "    * Items are converted to `Object` datatype to maintain this condition\n",
    "      * Very common source of bugs or error\n",
    "\n",
    "* Each data entry in a *Series* is associated with a label.\n",
    "  * Those labels are the `index` of the data.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Example of Series as Rows and Columns\n",
    "\n",
    "* It helps to think of pandas `Series` as either a row (or a column) of an Excel worksheet\n",
    "\n",
    "\n",
    "\n",
    "<img src=\"https://www.dropbox.com/s/imt11t2xnw4f2d0/possible_series.png?dl=1\" alt=\"drawing\" style=\"width:500px\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Creating a Panda `Series`\n",
    "\n",
    "\n",
    "\n",
    "```python\n",
    "import pandas as pd\n",
    "```\n",
    "\n",
    "* To create a pandas `Series`, you can call the `Series` function and pass it a `list` of values and a `list` of labels (an `index`)\n",
    "  * The `index` is optional and automatically set to `RangeIndex` (simple range) by default\n",
    "\n",
    "```python\n",
    "\n",
    ">>> s =  pd.Series(data = [1234, 'DIAZEPAM', 3, '$32'],\n",
    "                   index = ['doctor_id', 'medication', 'nb_beneficiaries', 'spending'])\n",
    "\n",
    ">>> s\n",
    "doctor_id               1234\n",
    "medication          DIAZEPAM\n",
    "nb_beneficiaries           3\n",
    "spending                 $32\n",
    "dtype: object\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "doctor_id               1234\n",
       "medication          DIAZEPAM\n",
       "nb_beneficiaries           3\n",
       "spending                 $32\n",
       "dtype: object"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "s =  pd.Series(data = [1234, 'DIAZEPAM', 3, '$32'], \n",
    "               index = ['doctor_id', 'medication', 'nb_beneficiaries', 'spending'])\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        1234\n",
       "1    DIAZEPAM\n",
       "2           3\n",
       "3         $32\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "s = pd.Series(data = [1234, 'DIAZEPAM', 3, '$32'])\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=4, step=1)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['label_1', 'label_2', 'label_3', 'label_4'], dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.index = [\"label_1\", \"label_2\", \"label_3\", \"label_4\"]\n",
    "s.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "label_1        1234\n",
       "label_2    DIAZEPAM\n",
       "label_3           3\n",
       "label_4         $32\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.index = [\"label_1\", \"label_2\", \"label_3\", \"label_4\"]\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1234, 'DIAZEPAM', 3, '$32'], dtype=object)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "doctor_id               1234\n",
       "medication          DIAZEPAM\n",
       "nb_beneficiaries           3\n",
       "spending                 $32\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s =  pd.Series( data= [1234, 'DIAZEPAM', 3, '$32'], \n",
    "               index= ['doctor_id', 'medication', 'nb_beneficiaries', 'spending'])\n",
    "s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Indexing `Series`\n",
    "\n",
    "\n",
    "\n",
    "* You can access the data in `pandas` by index (position of the object) using the same approach seen in list indexing\n",
    "\n",
    "```python\n",
    ">>> s[1]\n",
    "'DIAZEPAM'\n",
    "\n",
    ">>> s[-1]\n",
    "'$32'\n",
    "```\n",
    "\n",
    "* You can also access a value in the array using the data index\n",
    "\n",
    "```python\n",
    ">>> s[\"medication\"]\n",
    "'DIAZEPAM'\n",
    "\n",
    ">>> s[\"spending\"]\n",
    "'$32'\n",
    "```\n",
    "* Based on  the above, it is fair to think of a `Series` as a hybrid between lists an dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DIAZEPAM\n",
      "DIAZEPAM\n"
     ]
    }
   ],
   "source": [
    "print(s[1])\n",
    "print(s[\"medication\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Subsetting `Series`\n",
    "\n",
    "- Subsetting *Series* can be also done using the range operator (`\":\"`)\n",
    "\n",
    "```python\n",
    ">>> s[0:3]\n",
    "doctor_id               1234\n",
    "medication          DIAZEPAM\n",
    "nb_beneficiaries           3\n",
    "dtype: object\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Indexing `Series` \n",
    "\n",
    "* We can also index a subset of elements from a *Series* using a list of indexes\n",
    "\n",
    "```python\n",
    ">>> s[[0,2,1]]\n",
    "doctor_id               1234\n",
    "nb_beneficiaries           3\n",
    "medication          DIAZEPAM\n",
    "dtype: object\n",
    "```\n",
    "\n",
    "- Note that the line above contains two sets of square brackets `[[ ]]`, the first is the indexing operator, the second (inner) is the delimiter for the list.\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "doctor_id               1234\n",
       "medication          DIAZEPAM\n",
       "nb_beneficiaries           3\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[0:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "doctor_id           1234\n",
       "nb_beneficiaries       3\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[[0,2]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Subsetting Series -- Cont'd\n",
    "\n",
    "* Subsetting *Series* can also be done using lists of labels.\n",
    "\n",
    "```python\n",
    ">>> s[[\"doctor_id\", \"nb_beneficiaries\"]]\n",
    "doctor_id           1234\n",
    "nb_beneficiaries       3\n",
    "dtype: object\n",
    "```\n",
    "\n",
    "* Note that line above also contains two sets of square brackets `[[ ]]`, the first is the indexing operator, the second, inner, set is the delimiter for the list.\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "doctor_id           1234\n",
       "nb_beneficiaries       3\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[[\"doctor_id\", \"nb_beneficiaries\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### `DataFrames`\n",
    "\n",
    "* It helps to think of `DataFrames` are spreadsheet-like tables made of a set of `Series`\n",
    "  * The *Series* share the same index\n",
    "  \n",
    "<img src=\"https://www.dropbox.com/s/cc6037l3lz0bc03/df_as_series.png?dl=1\" alt=\"drawing\" style=\"width:800px\">\n",
    "\n",
    "* The example above illustrates a collection of column `Series` as a `DataFrame`, but the analogy holds for row `Series`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Reading a `DataFrame` from a File\n",
    "\n",
    "* A `DataFrame` can be created by reading data from a file\n",
    "\n",
    "  * `Pandas` supports many input formats, including `Excel`, `CSV`, `TSV`, `JSON`, `SAS`, etc.\n",
    "\n",
    "* We can read the example comma-delimited (CSV) spending table using:\n",
    "    \n",
    "```python\n",
    ">>> spending_df  = pd.read_csv(\"data/spending.csv\")\n",
    "```\n",
    "\n",
    "* Jupyter beautifies `DataFrames` by printing them  as `HTML` tables.\n",
    "  * Using `print` renders them as text-based output instead. \n",
    "  * Try to avoid the explicit `print` by including the name as the last statement of the cell, or use `display`.\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_id</th>\n",
       "      <th>doctor_id</th>\n",
       "      <th>specialty</th>\n",
       "      <th>medication</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "      <th>spending</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AB789982</td>\n",
       "      <td>1952310666</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>CLONAZEPAM</td>\n",
       "      <td>226</td>\n",
       "      <td>$1,848.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AV967778</td>\n",
       "      <td>1952310666</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>DIAZEPAM</td>\n",
       "      <td>103</td>\n",
       "      <td>$662.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CC128705</td>\n",
       "      <td>1298765423</td>\n",
       "      <td>Cardiology</td>\n",
       "      <td>NADOLOL</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GH890091</td>\n",
       "      <td>1346358827</td>\n",
       "      <td>Family</td>\n",
       "      <td>HYDROCODONE</td>\n",
       "      <td>331</td>\n",
       "      <td>$8,511.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>YY219322</td>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>ALPRAZOLAM</td>\n",
       "      <td>28</td>\n",
       "      <td>$1,964.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>YY190561</td>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>GABAPENTIN</td>\n",
       "      <td>86</td>\n",
       "      <td>$1,807.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>YY572610</td>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>MIRTAZAPINE</td>\n",
       "      <td>191</td>\n",
       "      <td>$3,131.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>PL346720</td>\n",
       "      <td>1326175365</td>\n",
       "      <td>Family</td>\n",
       "      <td>OXYCODONE HCL</td>\n",
       "      <td>87</td>\n",
       "      <td>$12,881.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>GZ129032</td>\n",
       "      <td>1518970284</td>\n",
       "      <td>Hemato-oncology</td>\n",
       "      <td>DIGOXIN</td>\n",
       "      <td>54</td>\n",
       "      <td>$3,766.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_id   doctor_id        specialty     medication  nb_beneficiaries  \\\n",
       "0  AB789982  1952310666       Psychiatry     CLONAZEPAM               226   \n",
       "1  AV967778  1952310666       Psychiatry       DIAZEPAM               103   \n",
       "2  CC128705  1298765423       Cardiology        NADOLOL                13   \n",
       "3  GH890091  1346358827           Family    HYDROCODONE               331   \n",
       "4  YY219322  1548247315       Psychiatry     ALPRAZOLAM                28   \n",
       "5  YY190561  1548247315       Psychiatry     GABAPENTIN                86   \n",
       "6  YY572610  1548247315       Psychiatry    MIRTAZAPINE               191   \n",
       "7  PL346720  1326175365           Family  OXYCODONE HCL                87   \n",
       "8  GZ129032  1518970284  Hemato-oncology        DIGOXIN                54   \n",
       "\n",
       "     spending  \n",
       "0   $1,848.88  \n",
       "1     $662.87  \n",
       "2         NaN  \n",
       "3   $8,511.14  \n",
       "4   $1,964.49  \n",
       "5   $1,807.16  \n",
       "6   $3,131.96  \n",
       "7  $12,881.04  \n",
       "8   $3,766.34  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df  = pd.read_csv(\"data/spending.csv\")\n",
    "\n",
    "spending_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Dimensions of the `DataFrame`\n",
    "\n",
    "* A useful property of the `DataFrame` is the `shape`\n",
    "\n",
    "  * This property describes the number of rows and the number of columns in your `DataFrame`\n",
    "  \n",
    "* `shape` returns a tuple\n",
    "  * a list of elements describing the number of rows and the number of columns in an array\n",
    "\n",
    "  * Tuples are delimited by `( )` rather `[ ]`\n",
    "\n",
    "```python\n",
    ">>>  spending_df.shape\n",
    "(9, 6)\n",
    "```\n",
    "\n",
    "* The above indicates that the `spending_df`  `DataFrame` has 9 rows (table entries) and 6 columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "### `DataFrame`, Indexes and Columns\n",
    "\n",
    "* As opposed to `Series` which only have indexes, `DataFrames` have `Indexes` (labels and positions) and `Column` (labels and positions).\n",
    "  * In my opinion, `Column` is not an ideal name for it... \n",
    "\n",
    "* A `DataFrame` can, therefore, be indexed by row index, row label, column index, column label, or by a combination of row and column index/label.\n",
    "\n",
    "* In the example below, since a row index was not explicitly provided, the labels for the rows are the same as the indices, i.e., 0 through 8.\n",
    "\n",
    "\n",
    "<img src=\"https://www.dropbox.com/s/fi9elbiyaealjt3/df_index_cols.png?dl=1\" alt=\"drawing\" style=\"width:500px;\"/>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Index Location\n",
    "\n",
    "- Row indexing can be carried out by passing the `iloc` (index location) operator a single index or a list of indexes.\n",
    "\n",
    "  -  The `iloc` operator uses `[]` instead of the `()` that methods and functions use.\n",
    "\n",
    "\n",
    "```python    \n",
    ">>> spending_df.iloc[3]\n",
    "```\n",
    "\n",
    "or\n",
    "\n",
    "```python    \n",
    ">>> spending_df.iloc[[1,5]]\n",
    "```\n",
    "\n",
    "- When a single index is given, `pandas` returns a `Series`\n",
    "  - Remember that a row is merely a `Series`\n",
    "  \n",
    "- When a list of indices is given, `pandas` returns another `DataFrame` \n",
    "  - The returned `DataFrame` is a subset of the original\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unique_id              GH890091\n",
       "doctor_id            1346358827\n",
       "specialty                Family\n",
       "medication          HYDROCODONE\n",
       "nb_beneficiaries            331\n",
       "spending              $8,511.14\n",
       "Name: 3, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Returns row, or Series\n",
    "spending_df.iloc[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_id</th>\n",
       "      <th>doctor_id</th>\n",
       "      <th>specialty</th>\n",
       "      <th>medication</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "      <th>spending</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AV967778</td>\n",
       "      <td>1952310666</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>DIAZEPAM</td>\n",
       "      <td>103</td>\n",
       "      <td>$662.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>YY190561</td>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>GABAPENTIN</td>\n",
       "      <td>86</td>\n",
       "      <td>$1,807.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  unique_id   doctor_id   specialty  medication  nb_beneficiaries   spending\n",
       "1  AV967778  1952310666  Psychiatry    DIAZEPAM               103    $662.87\n",
       "5  YY190561  1548247315  Psychiatry  GABAPENTIN                86  $1,807.16"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Returns two rows as a DataFrame\n",
    "\n",
    "spending_df.iloc[[1,5]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Indexing by Row and Column Indexes \n",
    "\n",
    "* You can pass `iloc` a combination of row and column indexes using the following construct\n",
    "\n",
    "```python\n",
    " data_frame.iloc[row_index_info, column_index_info]\n",
    "```\n",
    "* `row_index_info` and `column_index_info` can be either a single index or a list of indexes\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Example:\n",
    "\n",
    "```python\n",
    ">>>  spending_df.iloc[3, 3]\n",
    "'HYDROCODONE'\n",
    "\n",
    ">> spending_df.iloc[2, [1,3]]\n",
    "doctor_id     1298765423\n",
    "medication       NADOLOL\n",
    "Name: 2, dtype: object\n",
    "\n",
    ">>>  spending_df.iloc[[2,4], [1,3]]\n",
    "```\n",
    "|  |      doctor_id |   medication |\n",
    "|:--|:-----------------|:------------|\n",
    "|2 | 1298765423  | NADOLOL  |\n",
    "|4 | 1548247315  | ALPRAZOLAM |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "### Reading Tables with Index Labels\n",
    "\n",
    "*  Rather than using the default integer index label, a `DataFrame` can be indexed using one or more columns of data.\n",
    "  * We will cover complex indexes (more than one column) later.\n",
    "\n",
    "*  The index may or may not be a column from the data\n",
    "\n",
    "* Recall that we imported the file using:\n",
    "\n",
    "\n",
    "```python\n",
    "spending_df  = pd.read_csv(\"data/spending.csv\")\n",
    "```\n",
    "\n",
    "<img src=\"https://www.dropbox.com/s/qb62v2iov9frp38/default_index.png?dl=1\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Reading Tables with Index Labels\n",
    "\n",
    "*  Rather than using the default integer index label, a `DataFrame` can be indexed using one or more columns of data.\n",
    "  * We will cover complex indexes (more than one column) later.\n",
    "\n",
    "*  The index may or may not be a column from the data.\n",
    "\n",
    "* Recall that we imported the file using:\n",
    "\n",
    "\n",
    "```python\n",
    "spending_df  = pd.read_csv(\"data/spending.csv\")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Specifying the Index Labels\n",
    "\n",
    "* Specify the column name when reading the table\n",
    "  * E.g.: using the `read_csv()` function.\n",
    "  * For example, we can use the `unique_id` column as index labels \n",
    "\n",
    "\n",
    "```python\n",
    "spending_df  = pd.read_csv( \"data/spending.csv\", index_col=\"unique_id\")\n",
    "```\n",
    "\n",
    "<img src=\"https://www.dropbox.com/s/wmixxjn6dol14b6/custom_index.png?dl=1\" alt=\"drawing\" style=\"width:600px\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doctor_id</th>\n",
       "      <th>specialty</th>\n",
       "      <th>medication</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "      <th>spending</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</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>AB789982</th>\n",
       "      <td>1952310666</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>CLONAZEPAM</td>\n",
       "      <td>226</td>\n",
       "      <td>$1,848.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AV967778</th>\n",
       "      <td>1952310666</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>DIAZEPAM</td>\n",
       "      <td>103</td>\n",
       "      <td>$662.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CC128705</th>\n",
       "      <td>1298765423</td>\n",
       "      <td>Cardiology</td>\n",
       "      <td>NADOLOL</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GH890091</th>\n",
       "      <td>1346358827</td>\n",
       "      <td>Family</td>\n",
       "      <td>HYDROCODONE</td>\n",
       "      <td>331</td>\n",
       "      <td>$8,511.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY219322</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>ALPRAZOLAM</td>\n",
       "      <td>28</td>\n",
       "      <td>$1,964.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY190561</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>GABAPENTIN</td>\n",
       "      <td>86</td>\n",
       "      <td>$1,807.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY572610</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>MIRTAZAPINE</td>\n",
       "      <td>191</td>\n",
       "      <td>$3,131.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PL346720</th>\n",
       "      <td>1326175365</td>\n",
       "      <td>Family</td>\n",
       "      <td>OXYCODONE HCL</td>\n",
       "      <td>87</td>\n",
       "      <td>$12,881.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GZ129032</th>\n",
       "      <td>1518970284</td>\n",
       "      <td>Hemato-oncology</td>\n",
       "      <td>DIGOXIN</td>\n",
       "      <td>54</td>\n",
       "      <td>$3,766.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            doctor_id        specialty     medication  nb_beneficiaries  \\\n",
       "unique_id                                                                 \n",
       "AB789982   1952310666       Psychiatry     CLONAZEPAM               226   \n",
       "AV967778   1952310666       Psychiatry       DIAZEPAM               103   \n",
       "CC128705   1298765423       Cardiology        NADOLOL                13   \n",
       "GH890091   1346358827           Family    HYDROCODONE               331   \n",
       "YY219322   1548247315       Psychiatry     ALPRAZOLAM                28   \n",
       "YY190561   1548247315       Psychiatry     GABAPENTIN                86   \n",
       "YY572610   1548247315       Psychiatry    MIRTAZAPINE               191   \n",
       "PL346720   1326175365           Family  OXYCODONE HCL                87   \n",
       "GZ129032   1518970284  Hemato-oncology        DIGOXIN                54   \n",
       "\n",
       "             spending  \n",
       "unique_id              \n",
       "AB789982    $1,848.88  \n",
       "AV967778      $662.87  \n",
       "CC128705          NaN  \n",
       "GH890091    $8,511.14  \n",
       "YY219322    $1,964.49  \n",
       "YY190561    $1,807.16  \n",
       "YY572610    $3,131.96  \n",
       "PL346720   $12,881.04  \n",
       "GZ129032    $3,766.34  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df  = pd.read_csv(\"data/spending.csv\", index_col='unique_id')\n",
    "spending_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>specialty</th>\n",
       "      <th>medication</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "      <th>spending</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</th>\n",
       "      <th>doctor_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AB789982</th>\n",
       "      <th>1952310666</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>CLONAZEPAM</td>\n",
       "      <td>226</td>\n",
       "      <td>$1,848.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AV967778</th>\n",
       "      <th>1952310666</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>DIAZEPAM</td>\n",
       "      <td>103</td>\n",
       "      <td>$662.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CC128705</th>\n",
       "      <th>1298765423</th>\n",
       "      <td>Cardiology</td>\n",
       "      <td>NADOLOL</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GH890091</th>\n",
       "      <th>1346358827</th>\n",
       "      <td>Family</td>\n",
       "      <td>HYDROCODONE</td>\n",
       "      <td>331</td>\n",
       "      <td>$8,511.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY219322</th>\n",
       "      <th>1548247315</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>ALPRAZOLAM</td>\n",
       "      <td>28</td>\n",
       "      <td>$1,964.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY190561</th>\n",
       "      <th>1548247315</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>GABAPENTIN</td>\n",
       "      <td>86</td>\n",
       "      <td>$1,807.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY572610</th>\n",
       "      <th>1548247315</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>MIRTAZAPINE</td>\n",
       "      <td>191</td>\n",
       "      <td>$3,131.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PL346720</th>\n",
       "      <th>1326175365</th>\n",
       "      <td>Family</td>\n",
       "      <td>OXYCODONE HCL</td>\n",
       "      <td>87</td>\n",
       "      <td>$12,881.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GZ129032</th>\n",
       "      <th>1518970284</th>\n",
       "      <td>Hemato-oncology</td>\n",
       "      <td>DIGOXIN</td>\n",
       "      <td>54</td>\n",
       "      <td>$3,766.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            specialty     medication  nb_beneficiaries  \\\n",
       "unique_id doctor_id                                                      \n",
       "AB789982  1952310666       Psychiatry     CLONAZEPAM               226   \n",
       "AV967778  1952310666       Psychiatry       DIAZEPAM               103   \n",
       "CC128705  1298765423       Cardiology        NADOLOL                13   \n",
       "GH890091  1346358827           Family    HYDROCODONE               331   \n",
       "YY219322  1548247315       Psychiatry     ALPRAZOLAM                28   \n",
       "YY190561  1548247315       Psychiatry     GABAPENTIN                86   \n",
       "YY572610  1548247315       Psychiatry    MIRTAZAPINE               191   \n",
       "PL346720  1326175365           Family  OXYCODONE HCL                87   \n",
       "GZ129032  1518970284  Hemato-oncology        DIGOXIN                54   \n",
       "\n",
       "                        spending  \n",
       "unique_id doctor_id               \n",
       "AB789982  1952310666   $1,848.88  \n",
       "AV967778  1952310666     $662.87  \n",
       "CC128705  1298765423         NaN  \n",
       "GH890091  1346358827   $8,511.14  \n",
       "YY219322  1548247315   $1,964.49  \n",
       "YY190561  1548247315   $1,807.16  \n",
       "YY572610  1548247315   $3,131.96  \n",
       "PL346720  1326175365  $12,881.04  \n",
       "GZ129032  1518970284   $3,766.34  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df_two_idx  = pd.read_csv(\"data/spending.csv\", index_col=['unique_id', \"doctor_id\"])\n",
    "spending_df_two_idx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Inspecting DataFrames\n",
    "\n",
    "* When a `DataFrame` contains a large number of rows it's common to use the method `head` or `tail` to display the first or the last five entries, respectively, of the `DataFrame`.\n",
    "\n",
    "  * `head(n=5)` shows the top n (5 by default) lines of DataFrame\n",
    "  * `tail(n=5)` shows the bottom n (5 by default) lines of DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doctor_id</th>\n",
       "      <th>specialty</th>\n",
       "      <th>medication</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "      <th>spending</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</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>AB789982</th>\n",
       "      <td>1952310666</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>CLONAZEPAM</td>\n",
       "      <td>226</td>\n",
       "      <td>$1,848.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AV967778</th>\n",
       "      <td>1952310666</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>DIAZEPAM</td>\n",
       "      <td>103</td>\n",
       "      <td>$662.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CC128705</th>\n",
       "      <td>1298765423</td>\n",
       "      <td>Cardiology</td>\n",
       "      <td>NADOLOL</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GH890091</th>\n",
       "      <td>1346358827</td>\n",
       "      <td>Family</td>\n",
       "      <td>HYDROCODONE</td>\n",
       "      <td>331</td>\n",
       "      <td>$8,511.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY219322</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>ALPRAZOLAM</td>\n",
       "      <td>28</td>\n",
       "      <td>$1,964.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            doctor_id   specialty   medication  nb_beneficiaries   spending\n",
       "unique_id                                                                  \n",
       "AB789982   1952310666  Psychiatry   CLONAZEPAM               226  $1,848.88\n",
       "AV967778   1952310666  Psychiatry     DIAZEPAM               103    $662.87\n",
       "CC128705   1298765423  Cardiology      NADOLOL                13        NaN\n",
       "GH890091   1346358827      Family  HYDROCODONE               331  $8,511.14\n",
       "YY219322   1548247315  Psychiatry   ALPRAZOLAM                28  $1,964.49"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doctor_id</th>\n",
       "      <th>specialty</th>\n",
       "      <th>medication</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "      <th>spending</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</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>YY219322</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>ALPRAZOLAM</td>\n",
       "      <td>28</td>\n",
       "      <td>$1,964.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY190561</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>GABAPENTIN</td>\n",
       "      <td>86</td>\n",
       "      <td>$1,807.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY572610</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>MIRTAZAPINE</td>\n",
       "      <td>191</td>\n",
       "      <td>$3,131.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PL346720</th>\n",
       "      <td>1326175365</td>\n",
       "      <td>Family</td>\n",
       "      <td>OXYCODONE HCL</td>\n",
       "      <td>87</td>\n",
       "      <td>$12,881.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GZ129032</th>\n",
       "      <td>1518970284</td>\n",
       "      <td>Hemato-oncology</td>\n",
       "      <td>DIGOXIN</td>\n",
       "      <td>54</td>\n",
       "      <td>$3,766.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            doctor_id        specialty     medication  nb_beneficiaries  \\\n",
       "unique_id                                                                 \n",
       "YY219322   1548247315       Psychiatry     ALPRAZOLAM                28   \n",
       "YY190561   1548247315       Psychiatry     GABAPENTIN                86   \n",
       "YY572610   1548247315       Psychiatry    MIRTAZAPINE               191   \n",
       "PL346720   1326175365           Family  OXYCODONE HCL                87   \n",
       "GZ129032   1518970284  Hemato-oncology        DIGOXIN                54   \n",
       "\n",
       "             spending  \n",
       "unique_id              \n",
       "YY219322    $1,964.49  \n",
       "YY190561    $1,807.16  \n",
       "YY572610    $3,131.96  \n",
       "PL346720   $12,881.04  \n",
       "GZ129032    $3,766.34  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Indexing `DataFrame` Columns Using Labels\n",
    "\n",
    "* Accessing data in the `DataFrame` can also be done using indexes or column label\n",
    "\n",
    "\n",
    "* Indexing columns takes a column's label or a list of column labels\n",
    "\n",
    "```python\n",
    "spending_df[\"nb_beneficiaries\"]\n",
    "```\n",
    "or \n",
    "```python\n",
    "spending_df[[\"doctor_id\", \"nb_beneficiaries\"]]\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Indexing `DataFrame` Columns Using Labels -- Cont'd\n",
    "\n",
    "* When a single label is given, `pandas` returns a `Series`\n",
    "  * Remember that a column is simply a `Series`\n",
    "\n",
    "* When a list of labels is given, `pandas` returns another `DataFrame` \n",
    "  * The returned `DataFrame` is a subset of the original"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unique_id\n",
       "AB789982    226\n",
       "AV967778    103\n",
       "CC128705     13\n",
       "GH890091    331\n",
       "YY219322     28\n",
       "YY190561     86\n",
       "YY572610    191\n",
       "PL346720     87\n",
       "GZ129032     54\n",
       "Name: nb_beneficiaries, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df[\"nb_beneficiaries\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doctor_id</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AB789982</th>\n",
       "      <td>1952310666</td>\n",
       "      <td>226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AV967778</th>\n",
       "      <td>1952310666</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CC128705</th>\n",
       "      <td>1298765423</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GH890091</th>\n",
       "      <td>1346358827</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY219322</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY190561</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY572610</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PL346720</th>\n",
       "      <td>1326175365</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GZ129032</th>\n",
       "      <td>1518970284</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            doctor_id  nb_beneficiaries\n",
       "unique_id                              \n",
       "AB789982   1952310666               226\n",
       "AV967778   1952310666               103\n",
       "CC128705   1298765423                13\n",
       "GH890091   1346358827               331\n",
       "YY219322   1548247315                28\n",
       "YY190561   1548247315                86\n",
       "YY572610   1548247315               191\n",
       "PL346720   1326175365                87\n",
       "GZ129032   1518970284                54"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df[[\"doctor_id\", \"nb_beneficiaries\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Indexing `DataFrame` Rows Using Labels\n",
    "\n",
    "* Row indexing using labels can be carried out using the `loc` (location) operator.\n",
    "  * Similar to `iloc` The `loc` operator uses `[]` instead of the `()` that methods and functions use. \n",
    "\n",
    "*  `loc` can take a single label or a list of labels.\n",
    "\n",
    "```python    \n",
    "spending_df.loc[\"AV967778\"]\n",
    "```\n",
    "\n",
    "or\n",
    "\n",
    "```python    \n",
    "spending_df.loc[[\"AV967778\",\"YY219322\"]]\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Indexing `DataFrame` Rows Using Labels -- Cont'd\n",
    "\n",
    "* When a single index is given, `pandas` returns a `Series`.\n",
    "  - Remember that a row is simply a `Series`.\n",
    "  \n",
    "* When a list of indices is given, `pandas` returns another `DataFrame`.\n",
    "  - The returned `DataFrame` is a subset of the original."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "doctor_id           1952310666\n",
       "specialty           Psychiatry\n",
       "medication            DIAZEPAM\n",
       "nb_beneficiaries           103\n",
       "spending               $662.87\n",
       "Name: AV967778, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df.loc[\"AV967778\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doctor_id</th>\n",
       "      <th>specialty</th>\n",
       "      <th>medication</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "      <th>spending</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</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>AV967778</th>\n",
       "      <td>1952310666</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>DIAZEPAM</td>\n",
       "      <td>103</td>\n",
       "      <td>$662.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY219322</th>\n",
       "      <td>1548247315</td>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>ALPRAZOLAM</td>\n",
       "      <td>28</td>\n",
       "      <td>$1,964.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            doctor_id   specialty  medication  nb_beneficiaries   spending\n",
       "unique_id                                                                 \n",
       "AV967778   1952310666  Psychiatry    DIAZEPAM               103    $662.87\n",
       "YY219322   1548247315  Psychiatry  ALPRAZOLAM                28  $1,964.49"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df.loc[[\"AV967778\",\"YY219322\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Subsetting by Range on Labels\n",
    "\n",
    "* Although not useful or intuitive in this case,  the range operator also works with the index and column labels.\n",
    "\n",
    "\n",
    "* Therefore, the following lines are all valid lines.\n",
    "\n",
    "```python    \n",
    "spending_df.loc[\"AB789982\", \"specialty\":\"spending\"]    \n",
    "\n",
    "spending_df.loc[\"AB789982\":\"GZ129032\", \"specialty\"]    \n",
    "\n",
    "spending_df.loc[\"AB789982\":\"GZ129032\", \"specialty\":\"nb_beneficiaries\"]    \n",
    "```\n",
    "\n",
    "* As opposed to ranges in lists, the upper limit of the range is included  in the result.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>specialty</th>\n",
       "      <th>medication</th>\n",
       "      <th>nb_beneficiaries</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AB789982</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>CLONAZEPAM</td>\n",
       "      <td>226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AV967778</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>DIAZEPAM</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CC128705</th>\n",
       "      <td>Cardiology</td>\n",
       "      <td>NADOLOL</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GH890091</th>\n",
       "      <td>Family</td>\n",
       "      <td>HYDROCODONE</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY219322</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>ALPRAZOLAM</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY190561</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>GABAPENTIN</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YY572610</th>\n",
       "      <td>Psychiatry</td>\n",
       "      <td>MIRTAZAPINE</td>\n",
       "      <td>191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PL346720</th>\n",
       "      <td>Family</td>\n",
       "      <td>OXYCODONE HCL</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GZ129032</th>\n",
       "      <td>Hemato-oncology</td>\n",
       "      <td>DIGOXIN</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 specialty     medication  nb_beneficiaries\n",
       "unique_id                                                  \n",
       "AB789982        Psychiatry     CLONAZEPAM               226\n",
       "AV967778        Psychiatry       DIAZEPAM               103\n",
       "CC128705        Cardiology        NADOLOL                13\n",
       "GH890091            Family    HYDROCODONE               331\n",
       "YY219322        Psychiatry     ALPRAZOLAM                28\n",
       "YY190561        Psychiatry     GABAPENTIN                86\n",
       "YY572610        Psychiatry    MIRTAZAPINE               191\n",
       "PL346720            Family  OXYCODONE HCL                87\n",
       "GZ129032   Hemato-oncology        DIGOXIN                54"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spending_df.loc[\"AB789982\":\"GZ129032\", \"specialty\":\"nb_beneficiaries\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "\n",
    "### Indexing Review\n",
    "\n",
    "| Syntax                |   Meaning    |\n",
    "|:--------------------------|:-------------------|\n",
    "| DataFrame[\"col_name\"]    |  Returns `Series` of \"col_name\" |\n",
    "| DataFrame[ [\"col_name_1\", \"col_name_2\"] ]    |  Returns `DataFrame` with  \"col_name_1\" and \"col_name_2\"|\n",
    "| DataFrame.loc[\"label\"] | returns entry indexed by \"label\" |\n",
    "| DataFrame.loc[\"label\", [\"col_3\", 'col_5'] ] | returns entry indexed by \"label\", subsets columns to only \"col_3\" and \"col_5\" |\n",
    "| DataFrame.loc[ [\"label_1\", \"label_2\"], ['col_3', 'col_5'] ] | returns lines with indices \"label_1\" and \"label_2\", subsets columns to only \"col_3\" and \"col_5\" |\n",
    "| DataFrame.iloc[23, [0, 1] ] | returns line of index 23, and only values of columns 0 and 1 |\n",
    "| DataFrame.iloc[[1, 2], [0, 1] ] | returns lines of indices 1 and 2, and only values of columns 0 and 1 |\n"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}