Data Wrangling with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Wrangling with Pandas\n",
    "\n",
    "Now that we have been exposed to the basic functionality of Pandas, lets explore some more advanced features that will be useful when addressing more complex data management tasks.\n",
    "\n",
    "As most statisticians/data analysts will admit, often the lion's share of the time spent implementing an analysis is devoted to preparing the data itself, rather than to coding or running a particular model that uses the data. This is where Pandas and  Python's standard library are beneficial, providing high-level, flexible, and efficient tools for manipulating your data as needed.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Set some Pandas options\n",
    "pd.set_option('display.notebook_repr_html', False)\n",
    "pd.set_option('display.max_columns', 20)\n",
    "pd.set_option('display.max_rows', 25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Date/Time data handling\n",
    "\n",
    "Date and time data are inherently problematic. There are an unequal number of days in every month, an unequal number of days in a year (due to leap years), and time zones that vary over space. Yet information about time is essential in many analyses, particularly in the case of time series analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `datetime` built-in library handles temporal information down to the nanosecond."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2019, 9, 24, 17, 0, 3, 688451)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "now = datetime.now()\n",
    "now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "now.day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "now.weekday()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition to `datetime` there are simpler objects for date and time information only, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date, time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.time(3, 24)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time(3, 24)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(1970, 9, 3)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date(1970, 9, 3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Having a custom data type for dates and times is convenient because we can perform operations on them easily. For example, we may want to calculate the difference between two times:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.timedelta(15994, 38400, 248479)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_age = now - datetime(1970, 9, 3)\n",
    "my_age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43.81917808219178"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_age.days/365."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, we will manipulate data collected from ocean-going vessels on the eastern seaboard. Vessel operations are monitored using the Automatic Identification System (AIS), a safety at sea navigation technology which vessels are required to maintain and that uses transponders to transmit very high frequency (VHF) radio signals containing static information including ship name, call sign, and country of origin, as well as dynamic information unique to a particular voyage such as vessel location, heading, and speed. \n",
    "\n",
    "The International Maritime Organization’s (IMO) International Convention for the Safety of Life at Sea requires functioning AIS capabilities on all vessels 300 gross tons or greater and the US Coast Guard requires AIS on nearly all vessels sailing in U.S. waters. The Coast Guard has established a national network of AIS receivers that provides coverage of nearly all U.S. waters. AIS signals are transmitted several times each minute and the network is capable of handling thousands of reports per minute and updates as often as every two seconds. Therefore, a typical voyage in our study might include the transmission of hundreds or thousands of AIS encoded signals. This provides a rich source of spatial data that includes both spatial and temporal information.\n",
    "\n",
    "For our purposes, we will use summarized data that describes the transit of a given vessel through a particular administrative area. The data includes the start and end time of the transit segment, as well as information about the speed of the vessel, how far it travelled, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   mmsi               name  transit  segment  seg_length  avg_sog  min_sog  \\\n",
       "0     1        Us Govt Ves        1        1         5.1     13.2      9.2   \n",
       "1     1  Dredge Capt Frank        1        1        13.5     18.6     10.4   \n",
       "2     1      Us Gov Vessel        1        1         4.3     16.2     10.3   \n",
       "3     1      Us Gov Vessel        2        1         9.2     15.4     14.5   \n",
       "4     1  Dredge Capt Frank        2        1         9.2     15.4     14.6   \n",
       "\n",
       "   max_sog  pdgt10        st_time       end_time  \n",
       "0     14.5    96.5  2/10/09 16:03  2/10/09 16:27  \n",
       "1     20.6   100.0   4/6/09 14:31   4/6/09 15:20  \n",
       "2     20.5   100.0   4/6/09 14:36   4/6/09 14:55  \n",
       "3     16.1   100.0  4/10/09 17:58  4/10/09 18:34  \n",
       "4     16.2   100.0  4/10/09 17:59  4/10/09 18:35  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segments = pd.read_csv(\"data/AIS/transit_segments.csv\")\n",
    "segments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For example, we might be interested in the distribution of transit lengths, so we can plot them as a histogram:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x117716bd0>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYsAAAEACAYAAABCl1qQAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAGs1JREFUeJzt3X2MneWZ3/HvL3Fg8+JiTJB5Z9yuU7BwReKCt8omO+HF\nJVXLi4TASIs8uzRSmRKCVm0Zp2rtf+oaS6sEVG2kLc6OiYoXWlJwdsGxIZxoV62ZhmWIE8f1WIqJ\n7eAh4A1suhXF4tc/zj32YTL2mfE8PvP4nN9HGs1zX+d+ztzPpcO5/NzXzEG2iYiIOJkPzfUCIiKi\n/lIsIiKirRSLiIhoK8UiIiLaSrGIiIi2UiwiIqKttsVC0hpJP5a0S9Ljks6WtFDSDkl7JW2XtGDS\n/DFJeyStbIkvL88xJunhlvjZkp4o8Z2SLq/+MiMiYjZOWiwk9QFfAj5jexnwYWAVMATssP0p4IUy\nRtJS4E5gKXAT8EeSVJ7uG8A9tpcASyTdVOL3AG+V+NeAhyq7uoiIqES7O4t3gPeAj0maB3wM+Dlw\nM7C5zNkM3FqObwG22H7P9n5gH7BC0oXAfNsjZd5jLee0PtdTwPWzuqKIiKjcSYuF7SPAHwI/o1kk\nfml7B7DI9niZNg4sKscXAQdbnuIgcPEU8UMlTvl+oPy8o8Dbkhae6gVFRET12m1D/T3gAaCP5hv+\nJyT9buscNz8vJJ8ZEhHRxea1efwfAv/D9lsAkr4N/CPgsKQLbB8uW0xvlPmHgEtbzr+E5h3FoXI8\nOT5xzmXAz8tW1znljuYDJKUgRUScAttqP+vk2vUs9gC/JemjpVF9A7Ab+A6wusxZDTxdjrcCqySd\nJWkxsAQYsX0YeEfSivI8dwPPtJwz8Vy302yYT8l2vir6Wrt27ZyvoVu+ksvks85fVTnpnYXtVyU9\nBvwAeB/4K+CPgfnAk5LuAfYDd5T5uyU9SbOgHAUGfXy1g8Aw8FHgWdvbSnwT8C1JY8BbNH/bKk6z\n/fv3z/USukZyWa3ks57abUNheyOwcVL4CM27jKnmrwfWTxF/GVg2RfxdSrGJiIh6yl9w96iBgYG5\nXkLXSC6rlXzWk6rc0zqdJPlMWWtERF1Iwh1ocEeXajQac72ErpFcViv5rKcUi4iIaCvbUBERXSzb\nUBER0TEpFj0q+8LVSS6rlXzWU4pFRES0lZ5FREQXS88iIiI6JsWiR2VfuDrJZbWSz3pKsYiIiLbS\ns4iI6GLpWURERMekWPSo7AtXJ7msVvJZTykWERHRVnoWERFdrKd7FpJo/q+8IyKiE87IYhGzl33h\n6iSX1Uo+66ltsZD09yW90vL1tqT7JS2UtEPSXknbJS1oOWeNpDFJeyStbIkvl7SrPPZwS/xsSU+U\n+E5Jl1d/qRERcapm1LOQ9CHgEHAt8GXgTdsbJT0InGt7SNJS4HHgGuBi4HlgiW1LGgHusz0i6Vng\nEdvbJA0CV9kelHQncJvtVZN+9rGexcQWVHoYEREnN1c9ixuAfbYPADcDm0t8M3BrOb4F2GL7Pdv7\ngX3ACkkXAvNtj5R5j7Wc0/pcTwHXz/RCIiLi9JlpsVgFbCnHi2yPl+NxYFE5vgg42HLOQZp3GJPj\nh0qc8v0AgO2jwNuSFs5wbTED2ReuTnJZreSznqZdLCSdBfwz4L9OfqzsD2VPKCKiS82bwdwvAi/b\n/kUZj0u6wPbhssX0RokfAi5tOe8SmncUh8rx5PjEOZcBP5c0DzjH9pHJCxgYGKCvr+/YuNFo0N/f\nf+wYyHia44lYXdZzJo/7+/trtZ4zfZx8zm7caDQYHh4G+MD75WxNu8Et6U+B52xvLuONwFu2H5I0\nBCyY1OC+luMN7t8sDe6XgPuBEeDP+WCDe5nteyWtAm5NgzsiYvY62uCW9HGaze1vt4Q3ADdK2gtc\nV8bY3g08CewGngMGW/70ehB4FBij2SjfVuKbgPMkjQEPAEOzuahob+JfIjF7yWW1ks96mtY2lO3/\nA3xyUuwIzQIy1fz1wPop4i8Dy6aIvwvcMZ21RERE552Rnw2VbaiIiOnp6c+GioiIzkqx6FHZF65O\nclmt5LOeUiwiIqKt9CwiIrpYehYREdExKRY9KvvC1Ukuq5V81lOKRUREtJWeRUREF0vPIiIiOibF\nokdlX7g6yWW1ks96SrGIiIi20rOIiOhi6VlERETHpFj0qOwLVye5rFbyWU8pFhER0VZ6FhERXSw9\ni4iI6JgUix6VfeHqJJfVSj7raVrFQtICSf9N0k8k7Za0QtJCSTsk7ZW0XdKClvlrJI1J2iNpZUt8\nuaRd5bGHW+JnS3qixHdKurzay4yIiNmYVs9C0mbg+7a/KWke8HHg3wJv2t4o6UHgXNtDkpYCjwPX\nABcDzwNLbFvSCHCf7RFJzwKP2N4maRC4yvagpDuB22yvmrSG9CwiImaoYz0LSecAn7P9TQDbR22/\nDdwMbC7TNgO3luNbgC2237O9H9gHrJB0ITDf9kiZ91jLOa3P9RRw/ayuKiIiKjWdbajFwC8k/Ymk\nv5L0nyV9HFhke7zMGQcWleOLgIMt5x+keYcxOX6oxCnfD0CzGAFvS1p4KhcU05N94eokl9VKPutp\n3jTnfIbm9tH/kvR1YKh1QtliOu17QgMDA/T19R0bNxoN+vv7jx0DGU9zPDo6Wqv1ZJxxxtWMG40G\nw8PDAB94v5yttj0LSRcA/9P24jL+bWAN8HeBL9g+XLaYXrR9haQhANsbyvxtwFrgtTLnyhK/C/i8\n7XvLnHW2d5aeyOu2z5+0jvQsIiJmqGM9C9uHgQOSPlVCNwA/Br4DrC6x1cDT5XgrsErSWZIWA0uA\nkfI875TfpBJwN/BMyzkTz3U78MLsLisiIqo03b+z+DLwXyS9CvwD4D8AG4AbJe0FritjbO8GngR2\nA88Bgz5+CzAIPAqMAftsbyvxTcB5ksaAB5i0zRXVm7htjdlLLquVfNbTdHoW2H6V5q/CTnbDCeav\nB9ZPEX8ZWDZF/F3gjumsJSIiOi+fDRUR0cXy2VAREdExKRY9KvvC1Ukuq5V81lOKRUREtJWeRURE\nF0vPIiIiOibFokdlX7g6yWW1ks96SrGIiIi20rOIiOhi6VlERETHpFj0qOwLVye5rFbyWU8pFhER\n0VZ6FhERXSw9i4iI6JgUix6VfeHqJJfVSj7rKcUiIiLaSs8iIqKLpWcREREdk2LRo7IvXJ3kslrJ\nZz1Nq1hI2i/ph5JekTRSYgsl7ZC0V9J2SQta5q+RNCZpj6SVLfHlknaVxx5uiZ8t6YkS3ynp8iov\nMiIiZmdaPQtJPwWW2z7SEtsIvGl7o6QHgXNtD0laCjwOXANcDDwPLLHtUmjusz0i6VngEdvbJA0C\nV9kelHQncJvtVZPWkJ5FRMQMzUXPYvIPuxnYXI43A7eW41uALbbfs70f2AeskHQhMN/2SJn3WMs5\nrc/1FHD9DNYVERGn2XSLhYHnJf1A0pdKbJHt8XI8DiwqxxcBB1vOPUjzDmNy/FCJU74fALB9FHhb\n0sKZXEjMTPaFq5NcViv5rKd505z3WduvSzof2CFpT+uDZYvptO8JDQwM0NfXd2zcaDTo7+8/dgxk\nPM3x6OhordaTccYZVzNuNBoMDw8DfOD9crZm/HcWktYCvwK+BPTbPly2mF60fYWkIQDbG8r8bcBa\n4LUy58oSvwv4vO17y5x1tndKmge8bvv8ST83PYuIiBnqWM9C0sckzS/HHwdWAruArcDqMm018HQ5\n3gqsknSWpMXAEmDE9mHgHUkr1Hy3vxt4puWciee6HXhhthcWERHVmU7PYhHwF5JGgZeAP7O9HdgA\n3ChpL3BdGWN7N/AksBt4Dhj08VuAQeBRYAzYZ3tbiW8CzpM0BjwADFVxcXFiE7etMXvJZbWSz3pq\n27Ow/VPg6iniR4AbTnDOemD9FPGXgWVTxN8F7pjGeiMiYg7ks6EiIrpYPhsqIiI6JsWiR2VfuDrJ\nZbWSz3pKsYiIiLbSs4iI6GLpWURERMekWPSo7AtXJ7msVvJZTykWERHRVnoWERFdLD2LiIjomBSL\nHpV94eokl9VKPuspxSIiItpKzyIiooulZxERER2TYtGjsi9cneSyWslnPaVYREREW+lZRER0sfQs\nIiKiY1IselT2hauTXFYr+aynaRULSR+W9Iqk75TxQkk7JO2VtF3Sgpa5aySNSdojaWVLfLmkXeWx\nh1viZ0t6osR3Srq8yguMiIjZm1bPQtIfAMuB+bZvlrQReNP2RkkPAufaHpK0FHgcuAa4GHgeWGLb\nkkaA+2yPSHoWeMT2NkmDwFW2ByXdCdxme9UUa0jPIiJihjrWs5B0CfBPgEeBiR94M7C5HG8Gbi3H\ntwBbbL9nez+wD1gh6UKahWakzHus5ZzW53oKuP6UryYiIk6L6WxDfQ3418D7LbFFtsfL8TiwqBxf\nBBxsmXeQ5h3G5PihEqd8PwBg+yjwtqSFM7iGOAXZF65Oclmt5LOe5p3sQUn/FHjD9iuS+qeaU7aY\nOrIfNDAwQF9f37Fxo9Ggv7//2DGQ8TTHo6OjtVpPxhlnXM240WgwPDwM8IH3y9k6ac9C0nrgbuAo\n8BvA3wG+TbMn0W/7cNlietH2FZKGAGxvKOdvA9YCr5U5V5b4XcDnbd9b5qyzvVPSPOB12+dPsZb0\nLCIiZqgjPQvbX7V9qe3FwCrge7bvBrYCq8u01cDT5XgrsErSWZIWA0uAEduHgXckrVDznf5u4JmW\ncyae63bghdleVEREVGumf2cx8U/5DcCNkvYC15UxtncDTwK7geeAQR//5/8gzSb5GLDP9rYS3wSc\nJ2kMeAAYOsVriRmYuG2N2Usuq5V81tNJexatbH8f+H45PgLccIJ564H1U8RfBpZNEX8XuGO664iI\niM7LZ0NFRHSxfDZURER0TIpFj8q+cHWSy2oln/WUYhEREW2lZxER0cXSs4iIiI5JsehR2ReuTnJZ\nreSznlIsIiKirfQsIiK6WHoWERHRMSkWPSr7wtVJLquVfNZTikVERLSVnkVERBdLzyIiIjomxaJH\nZV+4OslltZLPekqxiIiIttKziIjoYulZREREx6RY9KjsC1cnuaxW8llPJy0Wkn5D0kuSRiXtlvQf\nS3yhpB2S9kraLmlByzlrJI1J2iNpZUt8uaRd5bGHW+JnS3qixHdKuvx0XGhERJy6tj0LSR+z/beS\n5gF/Cfwr4GbgTdsbJT0InGt7SNJS4HHgGuBi4HlgiW1LGgHusz0i6VngEdvbJA0CV9kelHQncJvt\nVVOsIz2LiIgZ6ljPwvbflsOzgA8Df02zWGwu8c3AreX4FmCL7fds7wf2ASskXQjMtz1S5j3Wck7r\ncz0FXH/KVxMREadF22Ih6UOSRoFx4EXbPwYW2R4vU8aBReX4IuBgy+kHad5hTI4fKnHK9wMAto8C\nb0taeGqXE9OVfeHqJJfVSj7raV67CbbfB66WdA7wXUlfmPS4JXVkP2hgYIC+vr5j40ajQX9//7Fj\nIONpjkdHR2u1nowzzriacaPRYHh4GOAD75ezNaO/s5D074D/C/xzoN/24bLF9KLtKyQNAdjeUOZv\nA9YCr5U5V5b4XcDnbd9b5qyzvbP0RV63ff4UPzs9i4iIGepIz0LSJyd+00nSR4EbgVeArcDqMm01\n8HQ53gqsknSWpMXAEmDE9mHgHUkr1Hynvxt4puWciee6HXhhthcVERHVatezuBD4XulZvAR8x/YL\nwAbgRkl7gevKGNu7gSeB3cBzwKCP//N/EHgUGAP22d5W4puA8ySNAQ8AQ1VdXJzYxG1rzF5yWa3k\ns55O2rOwvQv4zBTxI8ANJzhnPbB+ivjLwLIp4u8Cd0xzvRERMQfy2VAREV0snw0VEREdk2LRo7Iv\nXJ3kslrJZz2lWERERFvpWUREdLH0LCIiomNSLHpU9oWrk1xWK/mspxSLiIhoKz2LiIgulp5FRER0\nTIpFj8q+cHWSy2oln/WUYhEREW2lZxER0cXSs4iIiI5JsehR2ReuTnJZreSznlIsIiKirTO6ZwHp\nW0REnEx6FhER0TEpFj0q+8LVSS6rlXzWU9tiIelSSS9K+rGkH0m6v8QXStohaa+k7ZIWtJyzRtKY\npD2SVrbEl0vaVR57uCV+tqQnSnynpMurvtCIiDh1bXsWki4ALrA9KukTwMvArcDvAW/a3ijpQeBc\n20OSlgKPA9cAFwPPA0tsW9IIcJ/tEUnPAo/Y3iZpELjK9qCkO4HbbK+atI70LCIiZqhjPQvbh22P\nluNfAT+hWQRuBjaXaZtpFhCAW4Attt+zvR/YB6yQdCEw3/ZImfdYyzmtz/UUcP1sLioiIqo1o56F\npD7g08BLwCLb4+WhcWBROb4IONhy2kGaxWVy/FCJU74fALB9FHhb0sKZrC1mJvvC1Ukuq5V81tO8\n6U4sW1BPAV+x/TeTtoIs6bTvBw0MDNDX1zflYxMvsP7+/oynMR4dHa3VejLOOONqxo1Gg+HhYYAT\nvl+eimn9nYWkjwB/Bjxn++sltgfot324bDG9aPsKSUMAtjeUeduAtcBrZc6VJX4X8Hnb95Y562zv\nlDQPeN32+ZPWkJ5FRMQMdaxnoeY78yZg90ShKLYCq8vxauDplvgqSWdJWgwsAUZsHwbekbSiPOfd\nwDNTPNftwAuzuKaIiKjYdHoWnwV+F/iCpFfK103ABuBGSXuB68oY27uBJ4HdwHPAoI//838QeBQY\nA/bZ3lbim4DzJI0BDwBDlVxdnNDEbWvMXnJZreSzntr2LGz/JScuKjec4Jz1wPop4i8Dy6aIvwvc\n0W4tERExN/LZUBERXSyfDRURER2TYtGjsi9cneSyWslnPaVYREREW+lZRER0sfQsIiKiY1IselT2\nhauTXFYr+aynFIuIiGjrjO9ZQPoWEREnkp5FRER0TIpFj8q+cHWSy2oln/WUYhEREW2lZxER0cXS\ns4iIiI5JsehR2ReuTnJZreSznlIsIiKirfQsIiK6WHoWERHRMW2LhaRvShqXtKsltlDSDkl7JW2X\ntKDlsTWSxiTtkbSyJb5c0q7y2MMt8bMlPVHiOyVd3mY9M7/K+DXZF65Oclmt5LOepnNn8SfATZNi\nQ8AO258CXihjJC0F7gSWlnP+SMff3b8B3GN7CbBE0sRz3gO8VeJfAx6axfVERMRpMK2ehaQ+4Du2\nl5XxHuB3bI9LugBo2L5C0hrgfdsPlXnbgHXAa8D3bF9Z4quAftv/osxZa/slSfOA122fP8UabHvK\nO4v0LCIipjbXPYtFtsfL8TiwqBxfBBxsmXcQuHiK+KESp3w/AGD7KPC2pIWnuK6IiDgNZt3gLr+i\nlH/an2GyL1yd5LJayWc9zTvF88YlXWD7sKQLgTdK/BBwacu8S2jeURwqx5PjE+dcBvy8bEOdY/vI\nVD90YGDghAuaeIH19/dnPI3x6OhordaTccYZVzNuNBoMDw8D0NfXR1VOtWexkWZT+iFJQ8AC20Ol\nwf04cC3N7aXngd+0bUkvAfcDI8CfA4/Y3iZpEFhm+97Sy7jV9qop1pCeRUTEDFXVs2hbLCRtAX4H\n+CTN/sS/B54BnqR5R7AfuMP2L8v8rwK/DxwFvmL7uyW+HBgGPgo8a/v+Ej8b+BbwaeAtYJXt/VOs\nI8UiImKGOlYs6iLFolqNRuPYLWzMTnJZreSzWnP921AREdFDzqg7i5/97Gdcdtllv/bYmXINERGd\n1pN3FkeOTPlLUhERcZqdUcUiqjPxq3Yxe8lltZLPejqjisXVV18910uIiOhJZ1TP4kSPnSnXEBHR\naT3Zs4iIiLmRYtGjsi9cneSyWslnPaVYREREW+lZRER0sfQsIiKiY1IselT2hauTXFYr+aynrigW\nU324YEREVKcrehYAa9euZd26dR1aTUTEmaEnP6K83Zwz5VoiIjolDe6YlewLVye5rFbyWU8pFhER\n0Va2oSIiuli2oSIiomNqUywk3SRpj6QxSQ/O9Xq6XfaFq5NcViv5rKdaFAtJHwb+E3ATsBS4S9KV\nc7uq7jY6OjrXS+gayWW1ks96qkWxAK4F9tneb/s94E+BW2b6JJLyB3rT9Mtf/nKul9A1kstqJZ/1\nVJdicTFwoGV8sMROSQpGRES15s31AorKf43pZAUjvzUF+/fvn+sldI3kslrJZz3V4ldnJf0WsM72\nTWW8Bnjf9kMtc+Z+oRERZ6Cu+bgPSfOA/w1cD/wcGAHusv2TOV1YREQANdmGsn1U0n3Ad4EPA5tS\nKCIi6qMWdxYREVFvdfltqBPKH+udGkn7Jf1Q0iuSRkpsoaQdkvZK2i5pQcv8NSXHeyStnLuVzz1J\n35Q0LmlXS2zGuZO0XNKu8tjDnb6OujhBPtdJOlhen69I+mLLY8nnSUi6VNKLkn4s6UeS7i/x0/sa\ntV3bL5pbUvuAPuAjwChw5Vyv60z4An4KLJwU2wj8m3L8ILChHC8tuf1IyfU+4ENzfQ1zmLvPAZ8G\ndp1i7ibu2EeAa8vxs8BNc31tNcrnWuAPppibfLbP5wXA1eX4EzT7vVee7tdo3e8sKvljvR42+Tcg\nbgY2l+PNwK3l+BZgi+33bO+n+WK6tiMrrCHbfwH89aTwTHK3QtKFwHzbI2XeYy3n9JQT5BN+/fUJ\nyWdbtg/bHi3HvwJ+QvPv0k7ra7TuxaLSP9brMQael/QDSV8qsUW2x8vxOLCoHF9EM7cTkudfN9Pc\nTY4fIjmd7MuSXpW0qWXLJPmcAUl9NO/aXuI0v0brXizSfT91n7X9aeCLwL+U9LnWB9287zxZfpP7\nE5hG7qK9bwCLgauB14E/nNvlnHkkfQJ4CviK7b9pfex0vEbrXiwOAZe2jC/lg5UwTsD26+X7L4D/\nTnNbaVzSBQDlFvSNMn1yni8psThuJrk7WOKXTIonp4XtN1wAj3J82zP5nAZJH6FZKL5l++kSPq2v\n0boXix8ASyT1SToLuBPYOsdrqj1JH5M0vxx/HFgJ7KKZu9Vl2mpg4kW2FVgl6SxJi4ElNBtfcdyM\ncmf7MPCOpBVqfvbM3S3n9LzyZjbhNpqvT0g+2yrXvwnYbfvrLQ+d3tfoXHf2p9H5/yLNbv8+YM1c\nr+dM+KJ5ez9avn40kTdgIfA8sBfYDixoOeerJcd7gH8819cwx/nbQvOTBP4fzZ7Z751K7oDlNN8E\n9wGPzPV11Sifv0+zmfpD4NXyBrUo+Zx2Pn8beL/89/1K+brpdL9G80d5ERHRVt23oSIiogZSLCIi\noq0Ui4iIaCvFIiIi2kqxiIiItlIsIiKirRSLiIhoK8UiIiLa+v8VMFz1HZqOwAAAAABJRU5ErkJg\ngg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x1104583d0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "segments.seg_length.hist(bins=500)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Though most of the transits appear to be short, there are a few longer distances that make the plot difficult to read. This is where a transformation is useful:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x11382bb90>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYEAAAEACAYAAABVtcpZAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAGGBJREFUeJzt3W2MXGd5xvH/lZhAAilLWsl5hY2KU+KKyuAIR7Rptm2I\nDCpJPlR5UaEY3EpVaBP3FZsPeP2FAlKV0FZJ1YawDg0pBgoFEUxM8JailjhAXALGJUFsG5vaQQSH\nvkhVTO5+mGfj6XpmvDt7dp9z71w/aeRzzpzZufbx7Ln3PPeZWUUEZmY2mk6rHcDMzOpxETAzG2Eu\nAmZmI8xFwMxshLkImJmNMBcBM7MRNrAISLpb0lFJj3ZtO0fSHknflvSApLGu+7ZJekzSQUlXd21f\nL+nRct/7u7Y/X9JHyvYvS3pZ09+gmZn1d6ozgQ8CG+ds2wrsiYhLgAfLOpLWAjcAa8tj7pCk8pg7\ngc0RsQZYI2n2a24GflC23wa8d5Hfj5mZLcDAIhAR/wj8cM7ma4CdZXkncF1Zvha4LyKeiYgZ4HFg\ng6TzgLMjYl/Z756ux3R/rY8DvzLk92FmZkMYpiewOiKOluWjwOqyfD5wqGu/Q8AFPbYfLtsp/z4B\nEBHHgaclnTNEJjMzG8KiGsPR+cwJf+6EmVlSq4Z4zFFJ50bEkTLV82TZfhi4qGu/C+mcARwuy3O3\nzz7mpcD3JK0CXhwRT819QkkuNGZmQ4gIDbp/mDOBTwFvKctvAT7Ztf1GSWdIuhhYA+yLiCPAjyRt\nKI3iNwN/3+Nr/RqdRnO/b6T1t+3bt1fP4JzOmTWjczZ/m4+BZwKS7gOuBH5K0hPAu4D3ALskbQZm\ngOvLQfqApF3AAeA4cHOcSHEzMAWcCdwfEbvL9g8AH5L0GPAD4MZ5pW6pmZmZ2hHmxTmblSFnhozg\nnDUMLAIRcVOfu67qs/+7gXf32P5V4JU9tv8vpYiYmdny8zuGG7Rp06baEebFOZuVIWeGjOCcNWi+\n80Y1SYoMOc3M2kQSsQSNYetjenq6doR5cc5mZciZISM4Zw0uAmZmI8zTQWZmK5Sng8zMbCAXgQZl\nmSd0zmZlyJkhIzhnDS4CZmYjzD0BM7MVyj0BS0sSJ/4mkZktFReBBmWZJ8ySM4sM45khIzhnDS4C\nZmYjzD0Ba6XZqSD/v5sNzz0BMzMbyEWgQVnmCbPkzCLDeGbICM5Zg4uAmdkIc0/AWsk9AbPFc0/A\nzMwGchFoUJZ5wiw5s8gwnhkygnPW4CJgZjbC3BOwVnJPwGzx3BMwM7OBXAQalGWeMEvOLDKMZ4aM\n4Jw1uAiYmY0w9wSsldwTMFs89wTMzGwgF4EGZZknzJIziwzjmSEjOGcNLgJmZiPMPQFrJfcEzBbP\nPQEzMxvIRaBBWeYJs+Ts1uY/PJ9hPDNkBOeswUXAzGyEuSdgrTS3J+AegdnCuSdgZmYDuQg0KMs8\nYZacWWQYzwwZwTlrcBEwMxthQ/cEJG0D3gQ8CzwKvBV4IfAR4GXADHB9RBzr2v9twI+BWyLigbJ9\nPTAFvAC4PyJu7fFc7gmMGPcEzBZvyXoCksaB3wJeHRGvBE4HbgS2Ansi4hLgwbKOpLXADcBaYCNw\nh05c73cnsDki1gBrJG0cJpOZmS3csNNBPwKeAc6StAo4C/gecA2ws+yzE7iuLF8L3BcRz0TEDPA4\nsEHSecDZEbGv7HdP12PSyTJPmCVnFhnGM0NGcM4ahioCEfEU8KfAv9M5+B+LiD3A6og4WnY7Cqwu\ny+cDh7q+xCHggh7bD5ftZma2DFYN8yBJPw1sAcaBp4GPSnpT9z4REZIam8DdtGkT4+PjAIyNjbFu\n3TomJiaAE1XZ6/Nbn93Wljz91ruz9lqvnS/7eNbO02t9YmKiVXkGrc9qS57ZsZuamgJ47nh5KkM1\nhiXdALwuIn6zrL8ZuBz4ZeCXIuJImerZGxGvkLQVICLeU/bfDWwH/q3sc2nZfhNwZUT89pznc2N4\nxLgxbLZ4S/lmsYPA5ZLOLA3eq4ADwKeBt5R93gJ8six/CrhR0hmSLgbWAPsi4gjwI0kbytd5c9dj\n0pn7G0JbZcmZRYbxzJARnLOGoaaDIuJfJN0DfIXOJaJfA/4KOBvYJWkz5RLRsv8BSbvoFIrjwM1d\nv9rfTOcS0TPpXCK6e+jvxszMFsSfHWSt5Okgs8XzZweZmdlALgINyjJPmCVnFhnGM0NGcM4aXATM\nzEaYewLWSu4JmC2eewJmZjaQi0CDsswTZsmZRYbxzJARnLMGFwFrnbb+QXmzlcg9AWud7iLgnoDZ\n8NwTMDOzgVwEGpRlnjBLziwyjGeGjOCcNbgImJmNMPcErHXcEzBrhnsCZmY2kItAg7LME2bJmUWG\n8cyQEZyzBhcBM7MR5p6AtY57AmbNcE/AzMwGchFoUJZ5wiw5s8gwnhkygnPW4CJgZjbC3BOw1uj1\nwXHuCZgNzz0BMzMbyEWgQVnmCbPkzCLDeGbICM5Zg4uAmdkIc0/AWsM9AbNmuSdgZmYDuQg0KMs8\nYZacWWQYzwwZwTlrcBEwMxth7glYa7gnYNYs9wTMzGwgF4EGZZknzJIziwzjmSEjOGcNLgK2IkxO\nTtaOYJaSewLWGovpCZS5z6ULZ5bQfHoCq5YrjNkwehUGM2uOp4MalGWeMEvOLDKMZ4aM4Jw1uAiY\nmY2woXsCksaAu4CfBQJ4K/AY8BHgZcAMcH1EHCv7bwPeBvwYuCUiHijb1wNTwAuA+yPi1h7P5Z7A\nCJjP1I97Ambzt9TvE3g/nYP2pcDPAQeBrcCeiLgEeLCsI2ktcAOwFtgI3KETP/F3ApsjYg2wRtLG\nRWQyM7MFGKoISHoxcEVE3A0QEccj4mngGmBn2W0ncF1Zvha4LyKeiYgZ4HFgg6TzgLMjYl/Z756u\nx6STZZ4wS84sMoxnhozgnDUMeyZwMfB9SR+U9DVJfy3phcDqiDha9jkKrC7L5wOHuh5/CLigx/bD\nZbuZmS2DoXoCki4D/hl4bUQ8LOl24D+B34mIl3Tt91REnCPpz4EvR8S9ZftdwGfp9A3eExGvK9uv\nAP44It445/ncExgB7gmYNWsp3ydwCDgUEQ+X9Y8B24Ajks6NiCNlqufJcv9h4KKux19Yvsbhsty9\n/XCvJ9y0aRPj4+MAjI2NsW7dOiYmJoATp2Zez70+X6d6fFu+H697fbnXp6enmZqaAnjueHlKETHU\nDfgicElZngTeV27vKNu20vktHzoN4f3AGXSmkr7DibOQh4ANgID7gY09nisy2Lt3b+0I89LWnHSu\nMht4G/TYWto6nt0yZIxwzqaVn4uBx/LFvGP4d4F7JZ1RDupvBU4HdknaTLlEtBzBD0jaBRwAjgM3\nl4AAN9O5RPRMOlcb7V5EJjMzWwB/dpC1hnsCZs3y3xMwM7OBXAQatNAGZy1ZcmaRYTwzZATnrMFF\nwMxshLknYK3hnoBZs9wTMDOzgVwEGpRlnjBLziwyjGeGjOCcNbgIWCr+W8JmzXJPwFpjvn9Kstdr\nwT0Bs5O5J2BmZgO5CDQoyzxhlpxZZBjPDBnBOWtwETAzG2HuCVhruCdg1iz3BGykzLeImNkJLgIN\nyjJPmCVnFhnGM0NGcM4aXASsFfxbvFkd7glYKyykCPTrCfS7z2xUuSdgZmYDuQg0KMs8YZacWWQY\nzwwZwTlrcBEwMxth7glYK7gnYNY89wTMzGwgF4EGZZknzJIziwzjmSEjOGcNLgJmZiPMPQFrBfcE\nzJrnnoCZmQ3kItCgLPOEWXJmkWE8M2QE56zBRcDMbIS5J2Ct4J6AWfPcEzAzs4FcBBqUZZ4wS84s\nMoxnhozgnDW4CJiZjTD3BKwV3BMwa557AmZmNpCLQIOyzBNmyZlFhvHMkBGcswYXATOzEeaegLWC\newJmzVvynoCk0yU9IunTZf0cSXskfVvSA5LGuvbdJukxSQclXd21fb2kR8t9719MHjMzW5jFTgfd\nChwAZn/92grsiYhLgAfLOpLWAjcAa4GNwB068avfncDmiFgDrJG0cZGZqskyT5glZxYZxjNDRnDO\nGoYuApIuBN4A3AXMHtCvAXaW5Z3AdWX5WuC+iHgmImaAx4ENks4Dzo6IfWW/e7oeYzYv3VNJkhY0\ntWQ26obuCUj6KPBu4CeAP4yIN0r6YUS8pNwv4KmIeImkPwe+HBH3lvvuAj4LzADviYjXle1XAH8c\nEW+c81zuCaxwi+kJ9HqsXy9mS9gTkPSrwJMR8QgnzgL+n3LU9k+imVmLrRryca8FrpH0BuAFwE9I\n+hBwVNK5EXGkTPU8WfY/DFzU9fgLgUNl+4Vzth/u9YSbNm1ifHwcgLGxMdatW8fExARwYn6u9vrs\ntrbk6bd+++23t3b85mM+VwJNT0+P/Hh2r+/fv58tW7a0Jk+/9bk/S7Xz9Ftv63hOT08zNTUF8Nzx\n8pQiYlE34Erg02X5fcA7yvJWOlM90GkI7wfOAC4GvsOJqaiHgA10zijuBzb2eI7IYO/evbUjzEsb\nc9I5a1zQbdBjl1Mbx3OuDBkjnLNp5Wdh4DF80e8TkHQl8AcRcY2kc4BdwEvpzPdfHxHHyn7vBN4G\nHAdujYjPle3rgSngTOD+iLilx3PEYnNauw3TzJ19TbgnYNbbfHoCfrOYtYKLgFnz/AFyy2yhc9u1\nZMmZRYbxzJARnLMGFwEzsxHm6SBrBU8HmTXP00FmZjaQi0CDsswTZsmZRYbxzJARnLMGFwEzsxHm\nnoC1gnsCZs1zT8DMzAZyEWhQlnnCLDmzyDCeGTKCc9bgImCpTU5O1o5glpp7AtYKTf8hGL9ezNwT\nMDOzU3ARaFCWecIsObPIMJ4ZMoJz1uAiYGY2wtwTsFZwT8Csee4JmJnZQC4CDcoyT5glZxYZxjND\nRnDOGlwEzMxGmHsC1gruCZg1zz0BMzMbyEWgQVnmCbPkzCLDeGbICM5Zg4uAmdkIc0/AWmEpegKz\nX9OvHRtV7gmYmdlALgINyjJPmCVnFhnGM0NGcM4aXASsuqangsxs/twTsOqWowj49WOjyD0BMzMb\nyEWgQVnmCbPkzCLDeGbICM5Zg4uAmdkIc0/AqnNPwGxpuCdgVvgKJLPeXAQalGWeMEvOLDKMZ4aM\n4Jw1uAiYmY0w9wSsuuWaqvFryEaNewJmZjbQUEVA0kWS9kr6pqRvSLqlbD9H0h5J35b0gKSxrsds\nk/SYpIOSru7avl7So+W+9y/+W6onyzxhlpxNk7QkZx0ZxjNDRnDOGoY9E3gG+L2I+FngcuDtki4F\ntgJ7IuIS4MGyjqS1wA3AWmAjcIdO/DTeCWyOiDXAGkkbh/5uLB1ftWNWVyM9AUmfBP6i3K6MiKOS\nzgWmI+IVkrYBz0bEe8v+u4FJ4N+AL0TEpWX7jcBERPz2nK/vnsAKVaMI+LVko2JZegKSxoFXAQ8B\nqyPiaLnrKLC6LJ8PHOp62CHggh7bD5ftZma2DFYt5sGSXgR8HLg1Iv6z+7e6iAhJjf3KtWnTJsbH\nxwEYGxtj3bp1TExMACfm52qvz25rS55+67fffnurxm+5rfTx7LW+f/9+tmzZ0po8/dbn/izVztNv\nva3jOT09zdTUFMBzx8tTioihbsDzgM8BW7q2HQTOLcvnAQfL8lZga9d+u4ENwLnAt7q23wT8ZY/n\nigz27t1bO8K8tCknsOy3prVpPPvJkDHCOZtWXu8Dj+VD9QRKU3cn8IOI+L2u7e8r294raSswFhFb\nS2P4w8Br6Ez3fB54eUSEpIeAW4B9wGeAP4uI3XOeL4bJae3nnoDZ0plPT2DYIvALwBeBr9P57Qpg\nG50D+S7gpcAMcH1EHCuPeSfwNuA4nemjz5Xt64Ep4Ezg/oi4pcfzuQisULWuDvLryUbBkjWGI+JL\nEXFaRKyLiFeV2+6IeCoiroqISyLi6tkCUB7z7oh4eUS8YrYAlO1fjYhXlvtOKgCZdM9ntlmWnEtp\ncnKysa+VYTwzZATnrMHvGLaRtGPHjtoRzFrBnx1kVdV8s5hfU7bS+bODrNX8bmGz+lwEGpRlnjBL\nziwyjGeGjOCcNbgImJmNMPcErJra00F+TdlK556AmZkN5CLQoCzzhFlyZpFhPDNkBOeswUXAqqg9\nFWRmHe4JWBVtKAJ+TdlK556AmZkN5CLQoCzzhFlyZpFhPDNkBOeswUXAll2TH95mZovjnoAtuzb0\nA2b5dWUrmXsCZmY2kItAg7LME2bJ2Tb9prEyjGeGjOCcNbgI2LLJ3AuYnJz03yCwFck9AVs2ZX4y\nZU9gNrNfh5aJewJmZjaQi0CDsswTZsm5HCQt+swkw3hmyAjOWYOLgJnZCHNPwJbU5OTkcw3hNvYE\nug16jbknYBnNpyfgImBLavbA373sImC2PNwYXmZZ5glr5mxrAYD+2U6VOcP/e4aM4Jw1uAiYdZlt\nFGd+T4PZQng6yBrTa8pk7nRQJr1y+3VomXg6yFojWwEwGxUuAg3KMk/YdM5e19p3b8tcAOaTPcP/\ne4aM4Jw1uAhY4zIf9Lv1+j5WyvdmNss9AVu0UTsw+rVoWbgnYEtu1AqA2UqTsgjMnYNuy+V8WeYJ\n55tz7rx+95g38Zk7K0WG//cMGcE5a0hZBObq/px3H5z6G1Qsex3k+y17fM1WjpQ9gbnXo/e6Fr37\n4wki4v99hs2o6h6nWbPj4gP7wmT4uTFbUZ8d9PDDD3PZZZfNrgMLKwLd+/Q78GUYi8XoVQTa/nk+\nbdbr9eLPGLI2SdMYlrRR0kFJj0l6R699vvvd7zb2fIP+TODs2UK/s4ZB18Q3eSA9VY6Ffq3ubNPT\n0yN/VtSEuf/v/abQ2iDLHLZzVhARVW/A6cDjwDjwPGA/cOmcfWLXrl0xC4hO9BPrve6buzx3/9n7\ne+3Xb5/Z2/bt2/ve171PRMT27dufWx72a/mW+1bbbbfdVjvCvDhns8prb+AxuA1nAq8BHo+ImYh4\nBvhb4NrKmU5pPn90fHafHTt2sGPHjr6/HfoPmK98vc4a5r4eZs/YejXiF3tmcezYsUU9frk45/Jb\nVTsAcAHwRNf6IWBDpSxmS24+00anerdyuOdgDWlDEfCr2WyBhjkzGHTGGXMupBg2z2KL08zMzJJ9\n7Sb1yplV9auDJF0OTEbExrK+DXg2It7btU97/vfNzBKJtl8iKmkV8K/ArwDfA/YBN0XEt6oGMzMb\nAdWngyLiuKTfAT5H50qhD7gAmJktj+pnAmZmVk8bLhEdaD5vJKtN0t2Sjkp6tHaWQSRdJGmvpG9K\n+oakW2pnmkvSCyQ9JGm/pAOS/qR2pkEknS7pEUmfrp2lH0kzkr5ecu6rnacfSWOSPibpW+X//vLa\nmeaS9DNlHGdvT7fx5wg6/dXys/6opA9Len7P/dp8JiDpdDr9gquAw8DDtLBfIOkK4L+AeyLilbXz\n9CPpXODciNgv6UXAV4HrWjieZ0XE/5R+0ZeAP4yIL9XO1Yuk3wfWA2dHxDW18/Qi6bvA+oh4qnaW\nQSTtBP4hIu4u//cvjIina+fqR9JpdI5Lr4mIJ061/3KSNA58gc4bb/9X0keA+yNi59x9234mkOKN\nZBHxj8APa+c4lYg4EhH7y/J/Ad8Czq+b6mQR8T9l8Qw6faJWHrwkXQi8AbgLaNfnRJys1fkkvRi4\nIiLuhk6vsM0FoLgK+E7bCkDxI+AZ4KxSUM+iU7BO0vYi0OuNZBdUyrKilN8UXgU8VDfJySSdJmk/\ncBTYGxEHamfq4zbgj4Bnawc5hQA+L+krkn6rdpg+Lga+L+mDkr4m6a8lnVU71CncCHy4doheylnf\nnwL/Tueqy2MR8fle+7a9CLR3riqxMhX0MeDWckbQKhHxbESsAy4EflHSROVIJ5H0q8CTEfEILf8t\nG/j5iHgV8Hrg7WX6sm1WAa8G7oiIVwP/DWytG6k/SWcAbwQ+WjtLL5J+GthC5zPZzgdeJOnXe+3b\n9iJwGLioa/0iOmcDNiRJzwM+DvxNRHyydp5BynTAZ4DLamfp4bXANWW+/T7glyXdUzlTTxHxH+Xf\n7wOfoDPN2jaHgEMR8XBZ/xidotBWrwe+Wsa0jS4D/ikifhARx4G/o/OaPUnbi8BXgDWSxkvlvQH4\nVOVMaanz/vsPAAci4vbaeXqR9FOSxsrymcDrgEfqpjpZRLwzIi6KiIvpTAt8ISJ+o3auuSSdJens\nsvxC4GqgdVexRcQR4AlJl5RNVwHfrBjpVG6iU/zb6iBwuaQzy8/9VUDPadXqbxYbJMsbySTdB1wJ\n/KSkJ4B3RcQHK8fq5eeBNwFflzR7YN0WEbsrZprrPGBnufLiNOBDEfFg5Uzz0dapy9XAJ8rn76wC\n7o2IB+pG6ut3gXvLL3zfAd5aOU9PpZheBbS1v0JE/Es5M/0KnZ7V14C/6rVvqy8RNTOzpdX26SAz\nM1tCLgJmZiPMRcDMbIS5CJiZjTAXATOzEeYiYGY2wlwEzMxGmIuAmdkI+z9v8LeJ3GwnTgAAAABJ\nRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x11382b510>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "segments.seg_length.apply(np.log).hist(bins=500)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that although there are date/time fields in the dataset, they are not in any specialized format, such as `datetime`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segments.st_time.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our first order of business will be to convert these data to `datetime`. The `strptime` method parses a string representation of a date and/or time field, according to the expected format of this information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2009, 2, 10, 16, 3)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.strptime(segments.st_time.ix[0], '%m/%d/%y %H:%M')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `dateutil` package includes a parser that attempts to detect the format of the date strings, and convert them automatically."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dateutil.parser import parse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2009, 2, 10, 16, 3)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parse(segments.st_time.ix[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can convert all the dates in a particular column by using the `apply` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2009-02-10 16:03:00\n",
       "1   2009-04-06 14:31:00\n",
       "2   2009-04-06 14:36:00\n",
       "3   2009-04-10 17:58:00\n",
       "4   2009-04-10 17:59:00\n",
       "5   2010-03-20 16:06:00\n",
       "6   2010-03-20 18:05:00\n",
       "7   2011-05-04 11:28:00\n",
       "8   2010-06-05 11:23:00\n",
       "9   2010-06-08 11:03:00\n",
       "...\n",
       "262515   2010-05-31 14:27:00\n",
       "262516   2010-06-05 05:25:00\n",
       "262517   2010-06-27 02:35:00\n",
       "262518   2010-07-01 03:49:00\n",
       "262519   2010-07-02 03:30:00\n",
       "262520   2010-06-13 10:32:00\n",
       "262521   2010-06-15 12:49:00\n",
       "262522   2010-06-15 21:32:00\n",
       "262523   2010-06-17 19:16:00\n",
       "262524   2010-06-18 02:52:00\n",
       "262525   2010-06-18 10:19:00\n",
       "Name: st_time, Length: 262526, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segments.st_time.apply(lambda d: datetime.strptime(d, '%m/%d/%y %H:%M'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a convenience, Pandas has a `to_datetime` method that will parse and convert an entire Series of formatted strings into `datetime` objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2009-02-10 16:03:00\n",
       "1   2009-04-06 14:31:00\n",
       "2   2009-04-06 14:36:00\n",
       "3   2009-04-10 17:58:00\n",
       "4   2009-04-10 17:59:00\n",
       "5   2010-03-20 16:06:00\n",
       "6   2010-03-20 18:05:00\n",
       "7   2011-05-04 11:28:00\n",
       "8   2010-06-05 11:23:00\n",
       "9   2010-06-08 11:03:00\n",
       "...\n",
       "262515   2010-05-31 14:27:00\n",
       "262516   2010-06-05 05:25:00\n",
       "262517   2010-06-27 02:35:00\n",
       "262518   2010-07-01 03:49:00\n",
       "262519   2010-07-02 03:30:00\n",
       "262520   2010-06-13 10:32:00\n",
       "262521   2010-06-15 12:49:00\n",
       "262522   2010-06-15 21:32:00\n",
       "262523   2010-06-17 19:16:00\n",
       "262524   2010-06-18 02:52:00\n",
       "262525   2010-06-18 10:19:00\n",
       "Name: st_time, Length: 262526, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime(segments.st_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas also has a custom NA value for missing datetime objects, `NaT`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<class 'pandas.tseries.index.DatetimeIndex'>\n",
       "[NaT]\n",
       "Length: 1, Freq: None, Timezone: None"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime([None])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, if `to_datetime()` has problems parsing any particular date/time format, you can pass the spec in using the `format=` argument."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging and joining DataFrame objects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the vessel transit information as we need it, we may want a little more information regarding the vessels themselves. In the `data/AIS` folder there is a second table that contains information about each of the ships that traveled the segments in the `segments` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      num_names                                              names sov  \\\n",
       "mmsi                                                                     \n",
       "1             8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y   \n",
       "9             3                         000000009/Raven/Shearwater   N   \n",
       "21            1                                      Us Gov Vessel   Y   \n",
       "74            2                                  Mcfaul/Sarah Bell   N   \n",
       "103           3           Ron G/Us Navy Warship 103/Us Warship 103   Y   \n",
       "\n",
       "         flag flag_type  num_loas                                    loa  \\\n",
       "mmsi                                                                       \n",
       "1     Unknown   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0   \n",
       "9     Unknown   Unknown         2                              50.0/62.0   \n",
       "21    Unknown   Unknown         1                                  208.0   \n",
       "74    Unknown   Unknown         1                                  155.0   \n",
       "103   Unknown   Unknown         2                             26.0/155.0   \n",
       "\n",
       "      max_loa  num_types                             type  \n",
       "mmsi                                                       \n",
       "1         156          4  Dredging/MilOps/Reserved/Towing  \n",
       "9          62          2                     Pleasure/Tug  \n",
       "21        208          1                          Unknown  \n",
       "74        155          1                          Unknown  \n",
       "103       155          2                   Tanker/Unknown  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vessels = pd.read_csv(\"data/AIS/vessel_information.csv\", index_col='mmsi')\n",
    "vessels.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Unknown',\n",
       " 'Other',\n",
       " 'Tug',\n",
       " 'Towing',\n",
       " 'Pleasure',\n",
       " 'Cargo',\n",
       " 'WIG',\n",
       " 'Fishing',\n",
       " 'BigTow',\n",
       " 'MilOps',\n",
       " 'Tanker',\n",
       " 'Passenger',\n",
       " 'SAR',\n",
       " 'Sailing',\n",
       " 'Reserved',\n",
       " 'Law',\n",
       " 'Dredging',\n",
       " 'AntiPol',\n",
       " 'Pilot',\n",
       " 'HSC',\n",
       " 'Diving',\n",
       " 'Resol-18',\n",
       " 'Tender',\n",
       " 'Spare',\n",
       " 'Medical']"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[v for v in vessels.type.unique() if v.find('/')==-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Cargo        5622\n",
       "Tanker       2440\n",
       "Pleasure      601\n",
       "Tug           221\n",
       "Sailing       205\n",
       "Fishing       200\n",
       "Other         178\n",
       "Passenger     150\n",
       "Towing        117\n",
       "Unknown       106\n",
       "...\n",
       "BigTow/Tanker/Towing/Tug          1\n",
       "Fishing/SAR/Unknown               1\n",
       "BigTow/Reserved/Towing/Tug/WIG    1\n",
       "Reserved/Tanker/Towing/Tug        1\n",
       "Cargo/Reserved/Unknown            1\n",
       "Reserved/Towing/Tug               1\n",
       "BigTow/Unknown                    1\n",
       "Fishing/Law                       1\n",
       "BigTow/Towing/WIG                 1\n",
       "Towing/Unknown/WIG                1\n",
       "AntiPol/Fishing/Pleasure          1\n",
       "Length: 206, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vessels.type.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The challenge, however, is that several ships have travelled multiple segments, so there is not a one-to-one relationship between the rows of the two tables. The table of vessel information has a *one-to-many* relationship with the segments.\n",
    "\n",
    "In Pandas, we can combine tables according to the value of one or more *keys* that are used to identify rows, much like an index. Using a trivial example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(   age  id\n",
       " 0   24   0\n",
       " 1   25   1\n",
       " 2   25   2\n",
       " 3   30   3,    id     score\n",
       " 0   0  0.068840\n",
       " 1   1  0.784715\n",
       " 2   2  0.590726\n",
       " 3   0  0.892117\n",
       " 4   1  0.506912\n",
       " 5   2  0.721110)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame(dict(id=range(4), age=np.random.randint(18, 31, size=4)))\n",
    "df2 = pd.DataFrame(dict(id=range(3)+range(3), score=np.random.random(size=6)))\n",
    "\n",
    "df1, df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   age  id     score\n",
       "0   24   0  0.068840\n",
       "1   24   0  0.892117\n",
       "2   25   1  0.784715\n",
       "3   25   1  0.506912\n",
       "4   25   2  0.590726\n",
       "5   25   2  0.721110"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that without any information about which column to use as a key, Pandas did the right thing and used the `id` column in both tables. Unless specified otherwise, `merge` will used any common column names as keys for merging the tables. \n",
    "\n",
    "Notice also that `id=3` from `df1` was omitted from the merged table. This is because, by default, `merge` performs an **inner join** on the tables, meaning that the merged table represents an intersection of the two tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   age  id     score\n",
       "0   24   0  0.068840\n",
       "1   24   0  0.892117\n",
       "2   25   1  0.784715\n",
       "3   25   1  0.506912\n",
       "4   25   2  0.590726\n",
       "5   25   2  0.721110\n",
       "6   30   3       NaN"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **outer join** above yields the union of the two tables, so all rows are represented, with missing values inserted as appropriate. One can also perform **right** and **left** joins to include all rows of the right or left table (*i.e.* first or second argument to `merge`), but not necessarily the other."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at the two datasets that we wish to merge:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   mmsi         name  transit  segment  seg_length  avg_sog  min_sog  max_sog  \\\n",
       "0     1  Us Govt Ves        1        1         5.1     13.2      9.2     14.5   \n",
       "\n",
       "   pdgt10        st_time       end_time  \n",
       "0    96.5  2/10/09 16:03  2/10/09 16:27  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segments.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      num_names                                              names sov  \\\n",
       "mmsi                                                                     \n",
       "1             8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y   \n",
       "\n",
       "         flag flag_type  num_loas                                    loa  \\\n",
       "mmsi                                                                       \n",
       "1     Unknown   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0   \n",
       "\n",
       "      max_loa  num_types                             type  \n",
       "mmsi                                                       \n",
       "1         156          4  Dredging/MilOps/Reserved/Towing  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vessels.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we see that there is a `mmsi` value (a vessel identifier) in each table, but it is used as an index for the `vessels` table. In this case, we have to specify to join on the index for this table, and on the `mmsi` column for the other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "segments_merged = pd.merge(vessels, segments, left_index=True, right_on='mmsi')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   num_names                                              names sov     flag  \\\n",
       "0          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "1          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "2          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "3          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "4          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "\n",
       "  flag_type  num_loas                                    loa  max_loa  \\\n",
       "0   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "1   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "2   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "3   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "4   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "\n",
       "   num_types                             type  \\\n",
       "0          4  Dredging/MilOps/Reserved/Towing   \n",
       "1          4  Dredging/MilOps/Reserved/Towing   \n",
       "2          4  Dredging/MilOps/Reserved/Towing   \n",
       "3          4  Dredging/MilOps/Reserved/Towing   \n",
       "4          4  Dredging/MilOps/Reserved/Towing   \n",
       "\n",
       "                ...                              name transit  segment  \\\n",
       "0               ...                       Us Govt Ves       1        1   \n",
       "1               ...                 Dredge Capt Frank       1        1   \n",
       "2               ...                     Us Gov Vessel       1        1   \n",
       "3               ...                     Us Gov Vessel       2        1   \n",
       "4               ...                 Dredge Capt Frank       2        1   \n",
       "\n",
       "   seg_length  avg_sog  min_sog  max_sog  pdgt10        st_time       end_time  \n",
       "0         5.1     13.2      9.2     14.5    96.5  2/10/09 16:03  2/10/09 16:27  \n",
       "1        13.5     18.6     10.4     20.6   100.0   4/6/09 14:31   4/6/09 15:20  \n",
       "2         4.3     16.2     10.3     20.5   100.0   4/6/09 14:36   4/6/09 14:55  \n",
       "3         9.2     15.4     14.5     16.1   100.0  4/10/09 17:58  4/10/09 18:34  \n",
       "4         9.2     15.4     14.6     16.2   100.0  4/10/09 17:59  4/10/09 18:35  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segments_merged.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this case, the default inner join is suitable; we are not interested in observations from either table that do not have corresponding entries in the other. \n",
    "\n",
    "Notice that `mmsi` field that was an index on the `vessels` table is no longer an index on the merged table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we used the `merge` function to perform the merge; we could also have used the `merge` method for either of the tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   num_names                                              names sov     flag  \\\n",
       "0          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "1          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "2          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "3          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "4          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "\n",
       "  flag_type  num_loas                                    loa  max_loa  \\\n",
       "0   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "1   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "2   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "3   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "4   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "\n",
       "   num_types                             type  \\\n",
       "0          4  Dredging/MilOps/Reserved/Towing   \n",
       "1          4  Dredging/MilOps/Reserved/Towing   \n",
       "2          4  Dredging/MilOps/Reserved/Towing   \n",
       "3          4  Dredging/MilOps/Reserved/Towing   \n",
       "4          4  Dredging/MilOps/Reserved/Towing   \n",
       "\n",
       "                ...                              name transit  segment  \\\n",
       "0               ...                       Us Govt Ves       1        1   \n",
       "1               ...                 Dredge Capt Frank       1        1   \n",
       "2               ...                     Us Gov Vessel       1        1   \n",
       "3               ...                     Us Gov Vessel       2        1   \n",
       "4               ...                 Dredge Capt Frank       2        1   \n",
       "\n",
       "   seg_length  avg_sog  min_sog  max_sog  pdgt10        st_time       end_time  \n",
       "0         5.1     13.2      9.2     14.5    96.5  2/10/09 16:03  2/10/09 16:27  \n",
       "1        13.5     18.6     10.4     20.6   100.0   4/6/09 14:31   4/6/09 15:20  \n",
       "2         4.3     16.2     10.3     20.5   100.0   4/6/09 14:36   4/6/09 14:55  \n",
       "3         9.2     15.4     14.5     16.1   100.0  4/10/09 17:58  4/10/09 18:34  \n",
       "4         9.2     15.4     14.6     16.2   100.0  4/10/09 17:59  4/10/09 18:35  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vessels.merge(segments, left_index=True, right_on='mmsi').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Occasionally, there will be fields with the same in both tables that we do not wish to use to join the tables; they may contain different information, despite having the same name. In this case, Pandas will by default append suffixes `_x` and `_y` to the columns to uniquely identify them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   num_names                                              names sov     flag  \\\n",
       "0          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "1          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "2          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "3          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "4          8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y  Unknown   \n",
       "\n",
       "  flag_type  num_loas                                    loa  max_loa  \\\n",
       "0   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "1   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "2   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "3   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "4   Unknown         7  42.0/48.0/57.0/90.0/138.0/154.0/156.0      156   \n",
       "\n",
       "   num_types                           type_x  \\\n",
       "0          4  Dredging/MilOps/Reserved/Towing   \n",
       "1          4  Dredging/MilOps/Reserved/Towing   \n",
       "2          4  Dredging/MilOps/Reserved/Towing   \n",
       "3          4  Dredging/MilOps/Reserved/Towing   \n",
       "4          4  Dredging/MilOps/Reserved/Towing   \n",
       "\n",
       "                ...                 transit segment  seg_length  avg_sog  \\\n",
       "0               ...                       1       1         5.1     13.2   \n",
       "1               ...                       1       1        13.5     18.6   \n",
       "2               ...                       1       1         4.3     16.2   \n",
       "3               ...                       2       1         9.2     15.4   \n",
       "4               ...                       2       1         9.2     15.4   \n",
       "\n",
       "   min_sog  max_sog  pdgt10        st_time       end_time type_y  \n",
       "0      9.2     14.5    96.5  2/10/09 16:03  2/10/09 16:27    foo  \n",
       "1     10.4     20.6   100.0   4/6/09 14:31   4/6/09 15:20    foo  \n",
       "2     10.3     20.5   100.0   4/6/09 14:36   4/6/09 14:55    foo  \n",
       "3     14.5     16.1   100.0  4/10/09 17:58  4/10/09 18:34    foo  \n",
       "4     14.6     16.2   100.0  4/10/09 17:59  4/10/09 18:35    foo  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segments['type'] = 'foo'\n",
    "pd.merge(vessels, segments, left_index=True, right_on='mmsi').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This behavior can be overridden by specifying a `suffixes` argument, containing a list of the suffixes to be used for the columns of the left and right columns, respectively."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concatenation\n",
    "\n",
    "A common data manipulation is appending rows or columns to a dataset that already conform to the dimensions of the exsiting rows or colums, respectively. In NumPy, this is done either with `concatenate` or the convenience functions `c_` and `r_`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.95922038,  0.39563796,  0.13163705,  0.94429727,  0.01549658,\n",
       "        0.12911028,  0.75731272,  0.01583536,  0.56940704,  0.34045001])"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.concatenate([np.random.random(5), np.random.random(5)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.80586099,  0.34971419,  0.39173205,  0.61992008,  0.64169057,\n",
       "        0.31249921,  0.97907553,  0.18102001,  0.67703841,  0.25629399])"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.r_[np.random.random(5), np.random.random(5)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 0.40759603,  0.66875812],\n",
       "       [ 0.75010699,  0.64254533],\n",
       "       [ 0.19492038,  0.04028105],\n",
       "       [ 0.139481  ,  0.96799951],\n",
       "       [ 0.81245418,  0.83274143]])"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.c_[np.random.random(5), np.random.random(5)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This operation is also called *binding* or *stacking*.\n",
    "\n",
    "With Pandas' indexed data structures, there are additional considerations as the overlap in index values between two data structures affects how they are concatenate.\n",
    "\n",
    "Lets import two microbiome datasets, each consisting of counts of microorganiams from a particular patient. We will use the first column of each dataset as the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((272, 1), (288, 1))"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb1 = pd.read_excel('data/microbiome/MID1.xls', 'Sheet 1', index_col=0, header=None)\n",
    "mb2 = pd.read_excel('data/microbiome/MID2.xls', 'Sheet 1', index_col=0, header=None)\n",
    "mb1.shape, mb2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                                         1\n",
       "0                                                                                         \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera    7\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Pyrodictiaceae Pyrolobus          2\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Sulfolobales Sulfolobaceae Stygiolobus              3\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Thermoproteales Thermofilaceae Thermofilum          3\n",
       "Archaea \"Euryarchaeota\" \"Methanomicrobia\" Methanocellales Methanocellaceae Methanocella  7"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's give the index and columns meaningful labels:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "mb1.columns = mb2.columns = ['Count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "mb1.index.name = mb2.index.name = 'Taxon'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                                         Count\n",
       "Taxon                                                                                         \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera        7\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Pyrodictiaceae Pyrolobus              2\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Sulfolobales Sulfolobaceae Stygiolobus                  3\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Thermoproteales Thermofilaceae Thermofilum              3\n",
       "Archaea \"Euryarchaeota\" \"Methanomicrobia\" Methanocellales Methanocellaceae Methanocella      7"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The index of these data is the unique biological classification of each organism, beginning with *domain*, *phylum*, *class*, and for some organisms, going all the way down to the genus level.\n",
    "\n",
    "![classification](http://upload.wikimedia.org/wikipedia/commons/thumb/a/a5/Biological_classification_L_Pengo_vflip.svg/150px-Biological_classification_L_Pengo_vflip.svg.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera', u'Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Pyrodictiaceae Pyrolobus', u'Archaea \"Crenarchaeota\" Thermoprotei Sulfolobales Sulfolobaceae Stygiolobus'], dtype='object')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb1.index[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb1.index.is_unique"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we concatenate along `axis=0` (the default), we will obtain another data frame with the the rows concatenated:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(560, 1)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([mb1, mb2], axis=0).shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, the index is no longer unique, due to overlap between the two DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([mb1, mb2], axis=0).index.is_unique"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Concatenating along `axis=1` will concatenate column-wise, but respecting the indices of the two DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(438, 2)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([mb1, mb2], axis=1).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                                            Count  \\\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Acidilobaceae Acidilobus                    NaN   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Caldisphaeraceae Caldisphaera               NaN   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera           7   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Sulfophobococcus    NaN   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Thermosphaera       NaN   \n",
       "\n",
       "                                                                                            Count  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Acidilobaceae Acidilobus                      2  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Caldisphaeraceae Caldisphaera                14  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera          23  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Sulfophobococcus      1  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Thermosphaera         2  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([mb1, mb2], axis=1).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ nan,   2.],\n",
       "       [ nan,  14.],\n",
       "       [  7.,  23.],\n",
       "       [ nan,   1.],\n",
       "       [ nan,   2.]])"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([mb1, mb2], axis=1).values[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we are only interested in taxa that are included in both DataFrames, we can specify a `join=inner` argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                                         Count  \\\n",
       "Taxon                                                                                            \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera        7   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Pyrodictiaceae Pyrolobus              2   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Sulfolobales Sulfolobaceae Stygiolobus                  3   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Thermoproteales Thermofilaceae Thermofilum              3   \n",
       "Archaea \"Euryarchaeota\" \"Methanomicrobia\" Methanocellales Methanocellaceae Methanocella      7   \n",
       "\n",
       "                                                                                         Count  \n",
       "Taxon                                                                                           \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera       23  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Pyrodictiaceae Pyrolobus              2  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Sulfolobales Sulfolobaceae Stygiolobus                 10  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Thermoproteales Thermofilaceae Thermofilum              9  \n",
       "Archaea \"Euryarchaeota\" \"Methanomicrobia\" Methanocellales Methanocellaceae Methanocella      9  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([mb1, mb2], axis=1, join='inner').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we wanted to use the second table to fill values absent from the first table, we could use `combine_first`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                                            Count\n",
       "Taxon                                                                                            \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Acidilobaceae Acidilobus                      2\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Caldisphaeraceae Caldisphaera                14\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera           7\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Sulfophobococcus      1\n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Thermosphaera         2"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb1.combine_first(mb2).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also create a hierarchical index based on keys identifying the original tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                                                  Count\n",
       "         Taxon                                                                                         \n",
       "patient1 Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera        7\n",
       "         Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Pyrodictiaceae Pyrolobus              2\n",
       "         Archaea \"Crenarchaeota\" Thermoprotei Sulfolobales Sulfolobaceae Stygiolobus                  3\n",
       "         Archaea \"Crenarchaeota\" Thermoprotei Thermoproteales Thermofilaceae Thermofilum              3\n",
       "         Archaea \"Euryarchaeota\" \"Methanomicrobia\" Methanocellales Methanocellaceae Methanocella      7"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([mb1, mb2], keys=['patient1', 'patient2']).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([mb1, mb2], keys=['patient1', 'patient2']).index.is_unique"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, you can pass keys to the concatenation by supplying the DataFrames (or Series) as a dict."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                                                            patient1  \\\n",
       "                                                                                               Count   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Acidilobaceae Acidilobus                       NaN   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Caldisphaeraceae Caldisphaera                  NaN   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera              7   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Sulfophobococcus       NaN   \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Thermosphaera          NaN   \n",
       "\n",
       "                                                                                            patient2  \n",
       "                                                                                               Count  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Acidilobaceae Acidilobus                         2  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Acidilobales Caldisphaeraceae Caldisphaera                   14  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera             23  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Sulfophobococcus         1  \n",
       "Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Thermosphaera            2  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(dict(patient1=mb1, patient2=mb2), axis=1).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want `concat` to work like `numpy.concatanate`, you may provide the `ignore_index=True` argument."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "In the *data/microbiome* subdirectory, there are 9 spreadsheets of microbiome data that was acquired from high-throughput RNA sequencing procedures, along with a 10th file that describes the content of each. Write code that imports each of the data spreadsheets and combines them into a single `DataFrame`, adding the identifying information from the metadata spreadsheet as columns in the combined `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshaping DataFrame objects\n",
    "\n",
    "In the context of a single DataFrame, we are often interested in re-arranging the layout of our data. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dataset in from Table 6.9 of [Statistical Methods for the Analysis of Repeated Measurements](http://www.amazon.com/Statistical-Methods-Analysis-Repeated-Measurements/dp/0387953701) by Charles S. Davis, pp. 161-163 (Springer, 2002). These data are from a multicenter, randomized controlled trial of botulinum toxin type B (BotB) in patients with cervical dystonia from nine U.S. sites.\n",
    "\n",
    "* Randomized to placebo (N=36), 5000 units of BotB (N=36), 10,000 units of BotB (N=37)\n",
    "* Response variable: total score on Toronto Western Spasmodic Torticollis Rating Scale (TWSTRS), measuring severity, pain, and disability of cervical dystonia (high scores mean more impairment)\n",
    "* TWSTRS measured at baseline (week 0) and weeks 2, 4, 8, 12, 16 after treatment began"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   patient  obs  week  site  id  treat  age sex  twstrs\n",
       "0        1    1     0     1   1  5000U   65   F      32\n",
       "1        1    2     2     1   1  5000U   65   F      30\n",
       "2        1    3     4     1   1  5000U   65   F      24\n",
       "3        1    4     8     1   1  5000U   65   F      37\n",
       "4        1    5    12     1   1  5000U   65   F      39"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia = pd.read_csv(\"data/cdystonia.csv\", index_col=None)\n",
    "cdystonia.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dataset includes repeated measurements of the same individuals (longitudinal data). Its possible to present such information in (at least) two ways: showing each repeated measurement in their own row, or in multiple columns representing mutliple measurements.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `stack` method rotates the data frame so that columns are represented in rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0  patient        1\n",
       "   obs            1\n",
       "   week           0\n",
       "   site           1\n",
       "   id             1\n",
       "   treat      5000U\n",
       "   age           65\n",
       "   sex            F\n",
       "   twstrs        32\n",
       "1  patient        1\n",
       "...\n",
       "629  sex            M\n",
       "     twstrs        36\n",
       "630  patient      109\n",
       "     obs            6\n",
       "     week          16\n",
       "     site           9\n",
       "     id            11\n",
       "     treat      5000U\n",
       "     age           57\n",
       "     sex            M\n",
       "     twstrs        51\n",
       "Length: 5679, dtype: object"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked = cdystonia.stack()\n",
    "stacked"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To complement this, `unstack` pivots from rows back to columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  patient obs week site id  treat age sex twstrs\n",
       "0       1   1    0    1  1  5000U  65   F     32\n",
       "1       1   2    2    1  1  5000U  65   F     30\n",
       "2       1   3    4    1  1  5000U  65   F     24\n",
       "3       1   4    8    1  1  5000U  65   F     37\n",
       "4       1   5   12    1  1  5000U  65   F     39"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked.unstack().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this dataset, it makes sense to create a hierarchical index based on the patient and observation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "             week  site  id  treat  age sex  twstrs\n",
       "patient obs                                        \n",
       "1       1       0     1   1  5000U   65   F      32\n",
       "        2       2     1   1  5000U   65   F      30\n",
       "        3       4     1   1  5000U   65   F      24\n",
       "        4       8     1   1  5000U   65   F      37\n",
       "        5      12     1   1  5000U   65   F      39"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia2 = cdystonia.set_index(['patient','obs'])\n",
    "cdystonia2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia2.index.is_unique"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to transform this data so that repeated measurements are in columns, we can `unstack` the `twstrs` measurements according to `obs`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "obs       1   2   3   4   5   6\n",
       "patient                        \n",
       "1        32  30  24  37  39  36\n",
       "2        60  26  27  41  65  67\n",
       "3        44  20  23  26  35  35\n",
       "4        53  61  64  62 NaN NaN\n",
       "5        53  35  48  49  41  51"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twstrs_wide = cdystonia2['twstrs'].unstack('obs')\n",
    "twstrs_wide.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "    patient  site  id    treat  age sex   1   2   3   4   5   6\n",
       "0         1     1   1    5000U   65   F  32  30  24  37  39  36\n",
       "6         2     1   2   10000U   70   F  60  26  27  41  65  67\n",
       "12        3     1   3    5000U   64   F  44  20  23  26  35  35\n",
       "18        4     1   4  Placebo   59   F  53  61  64  62 NaN NaN\n",
       "22        5     1   5   10000U   76   F  53  35  48  49  41  51"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia_long = cdystonia[['patient','site','id','treat','age','sex']].drop_duplicates().merge(\n",
    "                    twstrs_wide, right_index=True, left_on='patient', how='inner').head()\n",
    "cdystonia_long"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A slightly cleaner way of doing this is to set the patient-level information as an index before unstacking:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "week                             0   2   4   8   12  16\n",
       "patient site id treat   age sex                        \n",
       "1       1    1  5000U   65  F    32  30  24  37  39  36\n",
       "2       1    2  10000U  70  F    60  26  27  41  65  67\n",
       "3       1    3  5000U   64  F    44  20  23  26  35  35\n",
       "4       1    4  Placebo 59  F    53  61  64  62 NaN NaN\n",
       "5       1    5  10000U  76  F    53  35  48  49  41  51"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia.set_index(['patient','site','id','treat','age','sex','week'])['twstrs'].unstack('week').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To convert our \"wide\" format back to long, we can use the `melt` function, appropriately parameterized:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   patient  site  id    treat  age sex obs  twsters\n",
       "0        1     1   1    5000U   65   F   1       32\n",
       "1        2     1   2   10000U   70   F   1       60\n",
       "2        3     1   3    5000U   64   F   1       44\n",
       "3        4     1   4  Placebo   59   F   1       53\n",
       "4        5     1   5   10000U   76   F   1       53"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(cdystonia_long, id_vars=['patient','site','id','treat','age','sex'], \n",
    "        var_name='obs', value_name='twsters').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This illustrates the two formats for longitudinal data: **long** and **wide** formats. Its typically better to store data in long format because additional data can be included as additional rows in the database, while wide format requires that the entire database schema be altered by adding columns to every row as data are collected.\n",
    "\n",
    "The preferable format for analysis depends entirely on what is planned for the data, so it is imporant to be able to move easily between them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivoting\n",
    "\n",
    "The `pivot` method allows a DataFrame to be transformed easily between long and wide formats in the same way as a pivot table is created in a spreadsheet. It takes three arguments: `index`, `columns` and `values`, corresponding to the DataFrame index (the row headers), columns and cell values, respectively.\n",
    "\n",
    "For example, we may want the `twstrs` variable (the response variable) in wide format according to patient:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "obs       1   2   3   4   5   6\n",
       "patient                        \n",
       "1        32  30  24  37  39  36\n",
       "2        60  26  27  41  65  67\n",
       "3        44  20  23  26  35  35\n",
       "4        53  61  64  62 NaN NaN\n",
       "5        53  35  48  49  41  51"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia.pivot(index='patient', columns='obs', values='twstrs').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we omit the `values` argument, we get a `DataFrame` with hierarchical columns, just as when we applied `unstack` to the hierarchically-indexed table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         week                      site             ...  sex                 \\\n",
       "obs         1   2   3   4   5   6     1   2   3   4 ...    3    4    5    6   \n",
       "patient                                             ...                       \n",
       "1           0   2   4   8  12  16     1   1   1   1 ...    F    F    F    F   \n",
       "2           0   2   4   8  12  16     1   1   1   1 ...    F    F    F    F   \n",
       "3           0   2   4   8  12  16     1   1   1   1 ...    F    F    F    F   \n",
       "4           0   2   4   8 NaN NaN     1   1   1   1 ...    F    F  NaN  NaN   \n",
       "5           0   2   4   8  12  16     1   1   1   1 ...    F    F    F    F   \n",
       "6           0   2   4   8  12  16     1   1   1   1 ...    F    F    F    F   \n",
       "7           0   2   4   8  12  16     1   1   1   1 ...    M    M    M    M   \n",
       "8           0   2   4   8  12  16     1   1   1   1 ...    M    M    M    M   \n",
       "9           0   2   4   8  12  16     1   1   1   1 ...    F    F    F    F   \n",
       "10          0   2   4   8  12  16     1   1   1   1 ...    M    M    M    M   \n",
       "11          0   2   4   8  12  16     1   1   1   1 ...    F    F    F    F   \n",
       "12          0   2   4   8  12  16     1   1   1   1 ...    F    F    F    F   \n",
       "...       ...  ..  ..  ..  ..  ..   ...  ..  ..  .. ...  ...  ...  ...  ...   \n",
       "98          0   2   4   8  12  16     8   8   8   8 ...    F    F    F    F   \n",
       "99          0   2   4   8  12  16     9   9   9   9 ...    M    M    M    M   \n",
       "100         0   2   4   8  12  16     9   9   9   9 ...    M    M    M    M   \n",
       "101         0   2   4   8  12  16     9   9   9   9 ...    M    M    M    M   \n",
       "102         0   2   4   8  12  16     9   9   9   9 ...    F    F    F    F   \n",
       "103         0   2   4 NaN  12  16     9   9   9 NaN ...    F  NaN    F    F   \n",
       "104         0   2   4 NaN  12  16     9   9   9 NaN ...    F  NaN    F    F   \n",
       "105         0   2   4   8  12  16     9   9   9   9 ...    F    F    F    F   \n",
       "106         0   2   4   8  12  16     9   9   9   9 ...    M    M    M    M   \n",
       "107         0 NaN   4   8 NaN  16     9 NaN   9   9 ...    M    M  NaN    M   \n",
       "108         0   2   4   8  12  16     9   9   9   9 ...    F    F    F    F   \n",
       "109         0   2 NaN   8  12  16     9   9 NaN   9 ...  NaN    M    M    M   \n",
       "\n",
       "         twstrs             twstrs      \n",
       "obs           1   2   3   4      5   6  \n",
       "patient                                 \n",
       "1            32  30  24  37     39  36  \n",
       "2            60  26  27  41     65  67  \n",
       "3            44  20  23  26     35  35  \n",
       "4            53  61  64  62    NaN NaN  \n",
       "5            53  35  48  49     41  51  \n",
       "6            49  34  43  48     48  51  \n",
       "7            42  32  32  43     42  46  \n",
       "8            34  33  21  27     32  38  \n",
       "9            41  32  34  35     37  36  \n",
       "10           27  10  31  32      6  14  \n",
       "11           48  41  32  35     57  51  \n",
       "12           34  19  21  24     28  28  \n",
       "...         ...  ..  ..  ..    ...  ..  \n",
       "98           40  28  29  30     37  44  \n",
       "99           40  16  18  25     33  48  \n",
       "100          61  52  61  68     59  71  \n",
       "101          35  21  29  30     35  48  \n",
       "102          58  38  50  53     47  59  \n",
       "103          49  45  36 NaN     40  52  \n",
       "104          52  46  36 NaN     45  54  \n",
       "105          45  46  33  44     46  48  \n",
       "106          67  63  71  66     68  71  \n",
       "107          57 NaN  36  23    NaN  52  \n",
       "108          63  51  46  50     50  54  \n",
       "109          53  38 NaN  33     36  51  \n",
       "\n",
       "[109 rows x 42 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia.pivot('patient', 'obs')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A related method, `pivot_table`, creates a spreadsheet-like table with a hierarchical index, and allows the values of the table to be populated using an arbitrary aggregation function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/site-packages/pandas/util/decorators.py:53: FutureWarning: cols is deprecated, use columns instead\n",
      "  warnings.warn(msg, FutureWarning)\n",
      "/usr/local/lib/python2.7/site-packages/pandas/util/decorators.py:53: FutureWarning: rows is deprecated, use index instead\n",
      "  warnings.warn(msg, FutureWarning)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "week          0   2   4   8   12  16\n",
       "site treat                          \n",
       "1    10000U   60  41  48  49  65  67\n",
       "     5000U    44  32  34  43  42  46\n",
       "     Placebo  53  61  64  62  32  38\n",
       "2    10000U   65  60  60  64  67  66\n",
       "     5000U    67  64  65  64  62  64\n",
       "     Placebo  53  56  52  57  61  54\n",
       "3    10000U   50  43  51  46  49  56\n",
       "     5000U    52  44  47  50  50  49\n",
       "     Placebo  43  38  40  48  49  44\n",
       "4    10000U   54  52  52  54  51  57\n",
       "     5000U    52  34  43  45  47  46\n",
       "     Placebo  52  55  51  52  54  57\n",
       "5    10000U   50  50  32  46  54  57\n",
       "     5000U    60  53  55  62  67  26\n",
       "     Placebo  60  57  53  52  53  58\n",
       "6    10000U   55  56  47  53  51  51\n",
       "     5000U    59  55  50  56  59  53\n",
       "     Placebo  54  53  51  57  57  57\n",
       "7    10000U   53  47  45  45  50  53\n",
       "     5000U    53  45  52  51  52  53"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia.pivot_table(rows=['site', 'treat'], cols='week', values='twstrs', aggfunc=max).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a simple cross-tabulation of group frequencies, the `crosstab` function (not a method) aggregates counts of data according to factors in rows and columns. The factors may be hierarchical if desired."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "site   1   2   3   4   5   6   7   8   9\n",
       "sex                                     \n",
       "F     52  53  42  30  22  54  66  48  28\n",
       "M     18  29  30  18  11  33   6  58  33"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(cdystonia.sex, cdystonia.site)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data transformation\n",
    "\n",
    "There are a slew of additional operations for DataFrames that we would collectively refer to as \"transformations\" that include tasks such as removing duplicate values, replacing values, and grouping values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dealing with duplicates\n",
    "\n",
    "We can easily identify and remove duplicate values from `DataFrame` objects. For example, say we want to removed ships from our `vessels` dataset that have the same name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/site-packages/pandas/util/decorators.py:53: FutureWarning: cols is deprecated, use subset instead\n",
      "  warnings.warn(msg, FutureWarning)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "mmsi\n",
       "1        False\n",
       "9        False\n",
       "21       False\n",
       "74       False\n",
       "103      False\n",
       "310      False\n",
       "3011     False\n",
       "4731     False\n",
       "15151    False\n",
       "46809    False\n",
       "...\n",
       "812719000    False\n",
       "857632392    False\n",
       "866946820     True\n",
       "888888882     True\n",
       "888888888    False\n",
       "900000000    False\n",
       "919191919    False\n",
       "967191190     True\n",
       "975318642     True\n",
       "987654321    False\n",
       "999999999     True\n",
       "Length: 10771, dtype: bool"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vessels.duplicated(cols='names')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           num_names                                              names sov  \\\n",
       "mmsi                                                                          \n",
       "1                  8  Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   Y   \n",
       "9                  3                         000000009/Raven/Shearwater   N   \n",
       "21                 1                                      Us Gov Vessel   Y   \n",
       "74                 2                                  Mcfaul/Sarah Bell   N   \n",
       "103                3           Ron G/Us Navy Warship 103/Us Warship 103   Y   \n",
       "310                1                                           Arabella   N   \n",
       "3011               1                                         Charleston   N   \n",
       "4731               1                                          000004731   N   \n",
       "15151              2                             R L Enterkin/Us Vessel   N   \n",
       "46809              1                                      Island Trader   N   \n",
       "80404              1                                         Donnamarie   N   \n",
       "82003              1                                             Alexis   N   \n",
       "...              ...                                                ...  ..   \n",
       "730026000          1                                        Pensilvania   N   \n",
       "730031000          1                                            Macondo   N   \n",
       "735057548          1                                         Chimborazo   N   \n",
       "735059037          1                                         B.e Guayas   N   \n",
       "760101000          1                                          Yacu Puma   N   \n",
       "770576100          1                                    Capitan Miranda   N   \n",
       "812719000          1                                   Ocean     Trader   N   \n",
       "857632392          1                                           Ct Pilot   N   \n",
       "888888888          1                                         Earl Jones   N   \n",
       "900000000          3      Capt.webster      Pc/Elk River/Roger Binsfeld   N   \n",
       "919191919          1                                                 Oi   N   \n",
       "987654321          2                         Island Lookout/Island Tide   N   \n",
       "\n",
       "                                     flag flag_type  num_loas  \\\n",
       "mmsi                                                            \n",
       "1                                 Unknown   Unknown         7   \n",
       "9                                 Unknown   Unknown         2   \n",
       "21                                Unknown   Unknown         1   \n",
       "74                                Unknown   Unknown         1   \n",
       "103                               Unknown   Unknown         2   \n",
       "310                              Bermuda    Foreign         1   \n",
       "3011                            Anguilla    Foreign         1   \n",
       "4731                 Yemen (Republic of)    Foreign         1   \n",
       "15151                             Unknown   Unknown         2   \n",
       "46809               Syrian Arab Republic    Foreign         1   \n",
       "80404                             Unknown   Unknown         1   \n",
       "82003                             Unknown   Unknown         1   \n",
       "...                                   ...       ...       ...   \n",
       "730026000         Colombia (Republic of)    Foreign         1   \n",
       "730031000         Colombia (Republic of)    Foreign         1   \n",
       "735057548                        Ecuador    Foreign         1   \n",
       "735059037                        Ecuador    Foreign         2   \n",
       "760101000                           Peru    Foreign         2   \n",
       "770576100  Uruguay (Eastern Republic of)    Foreign         1   \n",
       "812719000                         Unknown   Unknown         1   \n",
       "857632392                         Unknown   Unknown         1   \n",
       "888888888                         Unknown   Unknown         1   \n",
       "900000000                         Unknown   Unknown         3   \n",
       "919191919                         Unknown   Unknown         1   \n",
       "987654321                         Unknown   Unknown         2   \n",
       "\n",
       "                                             loa  max_loa  num_types  \\\n",
       "mmsi                                                                   \n",
       "1          42.0/48.0/57.0/90.0/138.0/154.0/156.0      156          4   \n",
       "9                                      50.0/62.0       62          2   \n",
       "21                                         208.0      208          1   \n",
       "74                                         155.0      155          1   \n",
       "103                                   26.0/155.0      155          2   \n",
       "310                                         47.0       47          1   \n",
       "3011                                       160.0      160          1   \n",
       "4731                                        30.0       30          1   \n",
       "15151                                 60.0/175.0      175          1   \n",
       "46809                                       22.0       22          1   \n",
       "80404                                       29.0       29          1   \n",
       "82003                                       29.0       29          2   \n",
       "...                                          ...      ...        ...   \n",
       "730026000                                  119.0      119          1   \n",
       "730031000                                  120.0      120          1   \n",
       "735057548                                  228.0      228          1   \n",
       "735059037                              44.0/78.0       78          1   \n",
       "760101000                            142.0/148.0      148          1   \n",
       "770576100                                   60.0       60          1   \n",
       "812719000                                   52.0       52          1   \n",
       "857632392                                   20.0       20          1   \n",
       "888888888                                   40.0       40          1   \n",
       "900000000                        22.0/38.0/351.0      351          3   \n",
       "919191919                                   20.0       20          1   \n",
       "987654321                              22.0/23.0       23          2   \n",
       "\n",
       "                                      type  \n",
       "mmsi                                        \n",
       "1          Dredging/MilOps/Reserved/Towing  \n",
       "9                             Pleasure/Tug  \n",
       "21                                 Unknown  \n",
       "74                                 Unknown  \n",
       "103                         Tanker/Unknown  \n",
       "310                                Unknown  \n",
       "3011                                 Other  \n",
       "4731                               Unknown  \n",
       "15151                                  Tug  \n",
       "46809                               Towing  \n",
       "80404                             Pleasure  \n",
       "82003                     Fishing/Pleasure  \n",
       "...                                    ...  \n",
       "730026000                            Cargo  \n",
       "730031000                            Cargo  \n",
       "735057548                           Tanker  \n",
       "735059037                          Sailing  \n",
       "760101000                            Cargo  \n",
       "770576100                          Sailing  \n",
       "812719000                           Tanker  \n",
       "857632392                           Diving  \n",
       "888888888                           Towing  \n",
       "900000000          Fishing/Reserved/Towing  \n",
       "919191919                         Pleasure  \n",
       "987654321                   Fishing/Towing  \n",
       "\n",
       "[10253 rows x 10 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vessels.drop_duplicates(['names'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Value replacement\n",
    "\n",
    "Frequently, we get data columns that are encoded as strings that we wish to represent numerically for the purposes of including it in a quantitative analysis. For example, consider the treatment variable in the cervical dystonia dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10000U     213\n",
       "5000U      211\n",
       "Placebo    207\n",
       "dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia.treat.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A logical way to specify these numerically is to change them to integer values, perhaps using \"Placebo\" as a baseline value. If we create a dict with the original values as keys and the replacements as values, we can pass it to the `map` method to implement the changes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "treatment_map = {'Placebo': 0, '5000U': 1, '10000U': 2}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1\n",
       "1    1\n",
       "2    1\n",
       "3    1\n",
       "4    1\n",
       "5    1\n",
       "6    2\n",
       "7    2\n",
       "8    2\n",
       "9    2\n",
       "...\n",
       "620    2\n",
       "621    2\n",
       "622    2\n",
       "623    2\n",
       "624    2\n",
       "625    2\n",
       "626    1\n",
       "627    1\n",
       "628    1\n",
       "629    1\n",
       "630    1\n",
       "Name: treatment, Length: 631, dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia['treatment'] = cdystonia.treat.map(treatment_map)\n",
    "cdystonia.treatment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternately, if we simply want to replace particular values in a `Series` or `DataFrame`, we can use the `replace` method. \n",
    "\n",
    "An example where replacement is useful is dealing with zeros in certain transformations. For example, if we try to take the log of a set of values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0             0\n",
       "1             1\n",
       "2          1024\n",
       "3         59049\n",
       "4       1048576\n",
       "5       9765625\n",
       "6      60466176\n",
       "7     282475249\n",
       "8    1073741824\n",
       "9    3486784401\n",
       "dtype: float64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vals = pd.Series([float(i)**10 for i in range(10)])\n",
    "vals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         -inf\n",
       "1     0.000000\n",
       "2     6.931472\n",
       "3    10.986123\n",
       "4    13.862944\n",
       "5    16.094379\n",
       "6    17.917595\n",
       "7    19.459101\n",
       "8    20.794415\n",
       "9    21.972246\n",
       "dtype: float64"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.log(vals)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In such situations, we can replace the zero with a value so small that it makes no difference to the ensuing analysis. We can do this with `replace`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   -13.815511\n",
       "1     0.000000\n",
       "2     6.931472\n",
       "3    10.986123\n",
       "4    13.862944\n",
       "5    16.094379\n",
       "6    17.917595\n",
       "7    19.459101\n",
       "8    20.794415\n",
       "9    21.972246\n",
       "dtype: float64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vals = vals.replace(0, 1e-6)\n",
    "np.log(vals)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also perform the same replacement that we used `map` for with `replace`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "patient  obs\n",
       "1        1      1\n",
       "         2      1\n",
       "         3      1\n",
       "         4      1\n",
       "         5      1\n",
       "         6      1\n",
       "2        1      2\n",
       "         2      2\n",
       "         3      2\n",
       "         4      2\n",
       "...\n",
       "108      1      2\n",
       "         2      2\n",
       "         3      2\n",
       "         4      2\n",
       "         5      2\n",
       "         6      2\n",
       "109      1      1\n",
       "         2      1\n",
       "         4      1\n",
       "         5      1\n",
       "         6      1\n",
       "Name: treat, Length: 631, dtype: int64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia2.treat.replace({'Placebo': 0, '5000U': 1, '10000U': 2})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inidcator variables\n",
    "\n",
    "For some statistical analyses (*e.g.* regression models or analyses of variance), categorical or group variables need to be converted into columns of indicators--zeros and ones--to create a so-called **design matrix**. The Pandas function `get_dummies` (indicator variables are also known as *dummy variables*) makes this transformation straightforward.\n",
    "\n",
    "Let's consider the DataFrame containing the ships corresponding to the transit segments on the eastern seaboard. The `type` variable denotes the class of vessel; we can create a matrix of indicators for this. For simplicity, lets filter out the 5 most common types of ships:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "top5 = vessels.type.apply(lambda s: s in vessels.type.value_counts().index[:5])\n",
    "vessels5 = vessels[top5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         Cargo  Pleasure  Sailing  Tanker  Tug\n",
       "mmsi                                          \n",
       "15151        0         0        0       0    1\n",
       "80404        0         1        0       0    0\n",
       "366235       1         0        0       0    0\n",
       "587370       0         0        0       0    1\n",
       "693559       0         0        0       0    1\n",
       "1233916      0         1        0       0    0\n",
       "3041300      1         0        0       0    0\n",
       "3663760      1         0        0       0    0\n",
       "3688360      1         0        0       0    0\n",
       "7718175      1         0        0       0    0"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.get_dummies(vessels5.type).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Discretization\n",
    "\n",
    "Pandas' `cut` function can be used to group continuous or countable data in to bins. Discretization is generally a very **bad idea** for statistical analysis, so use this function responsibly!\n",
    "\n",
    "Lets say we want to bin the ages of the cervical dystonia patients into a smaller number of groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    631.000000\n",
       "mean      55.616482\n",
       "std       12.123910\n",
       "min       26.000000\n",
       "25%       46.000000\n",
       "50%       56.000000\n",
       "75%       65.000000\n",
       "max       83.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia.age.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's transform these data into decades, beginnnig with individuals in their 20's and ending with those in their 90's:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " (60, 70]\n",
       " (60, 70]\n",
       " (60, 70]\n",
       " (60, 70]\n",
       " (60, 70]\n",
       " (60, 70]\n",
       " (60, 70]\n",
       " (60, 70]\n",
       " (60, 70]\n",
       " (60, 70]\n",
       "...\n",
       " (50, 60]\n",
       " (50, 60]\n",
       " (50, 60]\n",
       " (70, 80]\n",
       " (70, 80]\n",
       " (70, 80]\n",
       " (70, 80]\n",
       " (70, 80]\n",
       " (70, 80]\n",
       " (50, 60]\n",
       " (50, 60]\n",
       "Levels (7): Index(['(20, 30]', '(30, 40]', '(40, 50]', '(50, 60]',\n",
       "                   '(60, 70]', '(70, 80]', '(80, 90]'], dtype=object)\n",
       "Length: 30"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.cut(cdystonia.age, [20,30,40,50,60,70,80,90])[:30]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The parentheses indicate an open interval, meaning that the interval includes values up to but *not including* the endpoint, whereas the square bracket is a closed interval, where the endpoint is included in the interval. We can switch the closure to the left side by setting the `right` flag to `False`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " [60, 70)\n",
       " [60, 70)\n",
       " [60, 70)\n",
       " [60, 70)\n",
       " [60, 70)\n",
       " [60, 70)\n",
       " [70, 80)\n",
       " [70, 80)\n",
       " [70, 80)\n",
       " [70, 80)\n",
       "...\n",
       " [50, 60)\n",
       " [50, 60)\n",
       " [50, 60)\n",
       " [70, 80)\n",
       " [70, 80)\n",
       " [70, 80)\n",
       " [70, 80)\n",
       " [70, 80)\n",
       " [70, 80)\n",
       " [50, 60)\n",
       " [50, 60)\n",
       "Levels (7): Index(['[20, 30)', '[30, 40)', '[40, 50)', '[50, 60)',\n",
       "                   '[60, 70)', '[70, 80)', '[80, 90)'], dtype=object)\n",
       "Length: 30"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.cut(cdystonia.age, [20,30,40,50,60,70,80,90], right=False)[:30]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the data are now **ordinal**, rather than numeric, we can give them labels:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " old\n",
       " old\n",
       " old\n",
       " old\n",
       " old\n",
       " old\n",
       " old\n",
       " old\n",
       " old\n",
       " old\n",
       "...\n",
       " middle-aged\n",
       " middle-aged\n",
       " middle-aged\n",
       "         old\n",
       "         old\n",
       "         old\n",
       "         old\n",
       "         old\n",
       "         old\n",
       " middle-aged\n",
       " middle-aged\n",
       "Levels (4): Index(['young', 'middle-aged', 'old', 'ancient'], dtype=object)\n",
       "Length: 30"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.cut(cdystonia.age, [20,40,60,80,90], labels=['young','middle-aged','old','ancient'])[:30]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A related function `qcut` uses empirical quantiles to divide the data. If, for example, we want the quartiles -- (0-25%], (25-50%], (50-70%], (75-100%] -- we can just specify 4 intervals, which will be equally-spaced by default:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " (56, 65]\n",
       " (56, 65]\n",
       " (56, 65]\n",
       " (56, 65]\n",
       " (56, 65]\n",
       " (56, 65]\n",
       " (65, 83]\n",
       " (65, 83]\n",
       " (65, 83]\n",
       " (65, 83]\n",
       "...\n",
       " (56, 65]\n",
       " (56, 65]\n",
       " (56, 65]\n",
       " (65, 83]\n",
       " (65, 83]\n",
       " (65, 83]\n",
       " (65, 83]\n",
       " (65, 83]\n",
       " (65, 83]\n",
       " (56, 65]\n",
       " (56, 65]\n",
       "Levels (4): Index(['[26, 46]', '(46, 56]', '(56, 65]', '(65, 83]'], dtype=object)\n",
       "Length: 30"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.qcut(cdystonia.age, 4)[:30]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, one can specify custom quantiles to act as cut points:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   (1.8, 7.8]\n",
       "  (7.8, 45.4]\n",
       "   (1.8, 7.8]\n",
       "  (7.8, 45.4]\n",
       "  (7.8, 45.4]\n",
       "  (7.8, 45.4]\n",
       " (45.4, 89.7]\n",
       "  (7.8, 45.4]\n",
       "  (7.8, 45.4]\n",
       "  (7.8, 45.4]\n",
       "...\n",
       " (7.8, 45.4]\n",
       "  (1.8, 7.8]\n",
       "  (1.8, 7.8]\n",
       " (7.8, 45.4]\n",
       " (7.8, 45.4]\n",
       " (7.8, 45.4]\n",
       " (7.8, 45.4]\n",
       " (7.8, 45.4]\n",
       " (7.8, 45.4]\n",
       " (7.8, 45.4]\n",
       " (7.8, 45.4]\n",
       "Levels (5): Index(['[1, 1.8]', '(1.8, 7.8]', '(7.8, 45.4]',\n",
       "                   '(45.4, 89.7]', '(89.7, 1882]'], dtype=object)\n",
       "Length: 30"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "quantiles = pd.qcut(segments.seg_length, [0, 0.01, 0.05, 0.95, 0.99, 1])\n",
    "quantiles[:30]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that you can easily combine discretiztion with the generation of indicator variables shown above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   (1.8, 7.8]  (45.4, 89.7]  (7.8, 45.4]  (89.7, 1882]  [1, 1.8]\n",
       "0           1             0            0             0         0\n",
       "1           0             0            1             0         0\n",
       "2           1             0            0             0         0\n",
       "3           0             0            1             0         0\n",
       "4           0             0            1             0         0\n",
       "5           0             0            1             0         0\n",
       "6           0             1            0             0         0\n",
       "7           0             0            1             0         0\n",
       "8           0             0            1             0         0\n",
       "9           0             0            1             0         0"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.get_dummies(quantiles).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Permutation and sampling\n",
    "\n",
    "For some data analysis tasks, such as simulation, we need to be able to randomly reorder our data, or draw random values from it. Calling NumPy's `permutation` function with the length of the sequence you want to permute generates an array with a permuted sequence of integers, which can be used to re-order the sequence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([183316, 108225,  92326,  60611, 134460, 170622, 117439,  13979,\n",
       "       243115,  69470,  13594, 160103,  73039, 158974, 220079,  20341,\n",
       "        87882,   4430, 139083,  23082,  18110, 169543, 169578, 119516,\n",
       "       227857, 123216, 252938,  52744,  88292, 208914])"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_order = np.random.permutation(len(segments))\n",
    "new_order[:30]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using this sequence as an argument to the `take` method results in a reordered DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "             mmsi           name  transit  segment  seg_length  avg_sog  \\\n",
       "183316  367839000    Us Epa Bold       14        1        17.9      6.0   \n",
       "108225  366739960       Mckinley      135        1        26.9      8.1   \n",
       "92326   354591000   Pyxis Leader       30        1        30.3     14.9   \n",
       "60611   309942000     Dole Chile      178        1        18.5     19.0   \n",
       "134460  366997360  Norwegian Sea      201        1        25.9      6.9   \n",
       "\n",
       "        min_sog  max_sog  pdgt10         st_time       end_time type  \n",
       "183316      5.3      7.1     0.0    7/19/09 7:00   7/19/09 9:23  foo  \n",
       "108225      7.7      8.7     0.0  10/13/10 22:06  10/14/10 1:23  foo  \n",
       "92326       7.2     17.2    94.4    8/3/10 22:58    8/4/10 0:59  foo  \n",
       "60611      15.5     19.8   100.0     5/8/12 4:27    5/8/12 5:26  foo  \n",
       "134460      6.5      7.1     0.0    4/3/12 10:40   4/3/12 14:26  foo  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segments.take(new_order).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compare this ordering with the original:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   mmsi               name  transit  segment  seg_length  avg_sog  min_sog  \\\n",
       "0     1        Us Govt Ves        1        1         5.1     13.2      9.2   \n",
       "1     1  Dredge Capt Frank        1        1        13.5     18.6     10.4   \n",
       "2     1      Us Gov Vessel        1        1         4.3     16.2     10.3   \n",
       "3     1      Us Gov Vessel        2        1         9.2     15.4     14.5   \n",
       "4     1  Dredge Capt Frank        2        1         9.2     15.4     14.6   \n",
       "\n",
       "   max_sog  pdgt10        st_time       end_time type  \n",
       "0     14.5    96.5  2/10/09 16:03  2/10/09 16:27  foo  \n",
       "1     20.6   100.0   4/6/09 14:31   4/6/09 15:20  foo  \n",
       "2     20.5   100.0   4/6/09 14:36   4/6/09 14:55  foo  \n",
       "3     16.1   100.0  4/10/09 17:58  4/10/09 18:34  foo  \n",
       "4     16.2   100.0  4/10/09 17:59  4/10/09 18:35  foo  "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "segments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Its easy to see how this permutation approach allows us to draw a random sample **without replacement**. How would you sample **with replacement**? Generate a random sample of 5 ships from the `vessels` DataFrame using this scheme."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write your answer here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data aggregation and GroupBy operations\n",
    "\n",
    "One of the most powerful features of Pandas is its **GroupBy** functionality. On occasion we may want to perform operations on *groups* of observations within a dataset. For exmaple:\n",
    "\n",
    "* **aggregation**, such as computing the sum of mean of each group, which involves applying a function to each group and returning the aggregated results\n",
    "* **slicing** the DataFrame into groups and then doing something with the resulting slices (*e.g.* plotting)\n",
    "* group-wise **transformation**, such as standardization/normalization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "cdystonia_grouped = cdystonia.groupby(cdystonia.patient)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This *grouped* dataset is hard to visualize\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.DataFrameGroupBy object at 0x118e293d0>"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, the grouping is only an intermediate step; for example, we may want to **iterate** over each of the patient groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "   patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "0        1    1     0     1   1  5000U   65   F      32          1\n",
      "1        1    2     2     1   1  5000U   65   F      30          1\n",
      "2        1    3     4     1   1  5000U   65   F      24          1\n",
      "3        1    4     8     1   1  5000U   65   F      37          1\n",
      "4        1    5    12     1   1  5000U   65   F      39          1\n",
      "5        1    6    16     1   1  5000U   65   F      36          1\n",
      "\n",
      "2\n",
      "    patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "6         2    1     0     1   2  10000U   70   F      60          2\n",
      "7         2    2     2     1   2  10000U   70   F      26          2\n",
      "8         2    3     4     1   2  10000U   70   F      27          2\n",
      "9         2    4     8     1   2  10000U   70   F      41          2\n",
      "10        2    5    12     1   2  10000U   70   F      65          2\n",
      "11        2    6    16     1   2  10000U   70   F      67          2\n",
      "\n",
      "3\n",
      "    patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "12        3    1     0     1   3  5000U   64   F      44          1\n",
      "13        3    2     2     1   3  5000U   64   F      20          1\n",
      "14        3    3     4     1   3  5000U   64   F      23          1\n",
      "15        3    4     8     1   3  5000U   64   F      26          1\n",
      "16        3    5    12     1   3  5000U   64   F      35          1\n",
      "17        3    6    16     1   3  5000U   64   F      35          1\n",
      "\n",
      "4\n",
      "    patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "18        4    1     0     1   4  Placebo   59   F      53          0\n",
      "19        4    2     2     1   4  Placebo   59   F      61          0\n",
      "20        4    3     4     1   4  Placebo   59   F      64          0\n",
      "21        4    4     8     1   4  Placebo   59   F      62          0\n",
      "\n",
      "5\n",
      "    patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "22        5    1     0     1   5  10000U   76   F      53          2\n",
      "23        5    2     2     1   5  10000U   76   F      35          2\n",
      "24        5    3     4     1   5  10000U   76   F      48          2\n",
      "25        5    4     8     1   5  10000U   76   F      49          2\n",
      "26        5    5    12     1   5  10000U   76   F      41          2\n",
      "27        5    6    16     1   5  10000U   76   F      51          2\n",
      "\n",
      "6\n",
      "    patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "28        6    1     0     1   6  10000U   59   F      49          2\n",
      "29        6    2     2     1   6  10000U   59   F      34          2\n",
      "30        6    3     4     1   6  10000U   59   F      43          2\n",
      "31        6    4     8     1   6  10000U   59   F      48          2\n",
      "32        6    5    12     1   6  10000U   59   F      48          2\n",
      "33        6    6    16     1   6  10000U   59   F      51          2\n",
      "\n",
      "7\n",
      "    patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "34        7    1     0     1   7  5000U   72   M      42          1\n",
      "35        7    2     2     1   7  5000U   72   M      32          1\n",
      "36        7    3     4     1   7  5000U   72   M      32          1\n",
      "37        7    4     8     1   7  5000U   72   M      43          1\n",
      "38        7    5    12     1   7  5000U   72   M      42          1\n",
      "39        7    6    16     1   7  5000U   72   M      46          1\n",
      "\n",
      "8\n",
      "    patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "40        8    1     0     1   8  Placebo   40   M      34          0\n",
      "41        8    2     2     1   8  Placebo   40   M      33          0\n",
      "42        8    3     4     1   8  Placebo   40   M      21          0\n",
      "43        8    4     8     1   8  Placebo   40   M      27          0\n",
      "44        8    5    12     1   8  Placebo   40   M      32          0\n",
      "45        8    6    16     1   8  Placebo   40   M      38          0\n",
      "\n",
      "9\n",
      "    patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "46        9    1     0     1   9  5000U   52   F      41          1\n",
      "47        9    2     2     1   9  5000U   52   F      32          1\n",
      "48        9    3     4     1   9  5000U   52   F      34          1\n",
      "49        9    4     8     1   9  5000U   52   F      35          1\n",
      "50        9    5    12     1   9  5000U   52   F      37          1\n",
      "51        9    6    16     1   9  5000U   52   F      36          1\n",
      "\n",
      "10\n",
      "    patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "52       10    1     0     1  10  Placebo   47   M      27          0\n",
      "53       10    2     2     1  10  Placebo   47   M      10          0\n",
      "54       10    3     4     1  10  Placebo   47   M      31          0\n",
      "55       10    4     8     1  10  Placebo   47   M      32          0\n",
      "56       10    5    12     1  10  Placebo   47   M       6          0\n",
      "57       10    6    16     1  10  Placebo   47   M      14          0\n",
      "\n",
      "11\n",
      "    patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "58       11    1     0     1  11  10000U   57   F      48          2\n",
      "59       11    2     2     1  11  10000U   57   F      41          2\n",
      "60       11    3     4     1  11  10000U   57   F      32          2\n",
      "61       11    4     8     1  11  10000U   57   F      35          2\n",
      "62       11    5    12     1  11  10000U   57   F      57          2\n",
      "63       11    6    16     1  11  10000U   57   F      51          2\n",
      "\n",
      "12\n",
      "    patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "64       12    1     0     1  12  Placebo   47   F      34          0\n",
      "65       12    2     2     1  12  Placebo   47   F      19          0\n",
      "66       12    3     4     1  12  Placebo   47   F      21          0\n",
      "67       12    4     8     1  12  Placebo   47   F      24          0\n",
      "68       12    5    12     1  12  Placebo   47   F      28          0\n",
      "69       12    6    16     1  12  Placebo   47   F      28          0\n",
      "\n",
      "13\n",
      "    patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "70       13    1     0     2   1  Placebo   70   F      49          0\n",
      "71       13    2     2     2   1  Placebo   70   F      47          0\n",
      "72       13    3     4     2   1  Placebo   70   F      44          0\n",
      "73       13    4     8     2   1  Placebo   70   F      48          0\n",
      "74       13    5    12     2   1  Placebo   70   F      44          0\n",
      "75       13    6    16     2   1  Placebo   70   F      44          0\n",
      "\n",
      "14\n",
      "    patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "76       14    1     0     2   2  5000U   49   F      46          1\n",
      "77       14    2     2     2   2  5000U   49   F      35          1\n",
      "78       14    3     4     2   2  5000U   49   F      45          1\n",
      "79       14    4     8     2   2  5000U   49   F      49          1\n",
      "80       14    5    12     2   2  5000U   49   F      53          1\n",
      "81       14    6    16     2   2  5000U   49   F      56          1\n",
      "\n",
      "15\n",
      "    patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "82       15    1     0     2   3  10000U   59   F      56          2\n",
      "83       15    2     2     2   3  10000U   59   F      44          2\n",
      "84       15    3     4     2   3  10000U   59   F      48          2\n",
      "85       15    4     8     2   3  10000U   59   F      54          2\n",
      "86       15    5    12     2   3  10000U   59   F      49          2\n",
      "87       15    6    16     2   3  10000U   59   F      60          2\n",
      "\n",
      "16\n",
      "    patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "88       16    1     0     2   4  5000U   64   M      59          1\n",
      "89       16    2     2     2   4  5000U   64   M      48          1\n",
      "90       16    3     4     2   4  5000U   64   M      56          1\n",
      "91       16    4     8     2   4  5000U   64   M      55          1\n",
      "92       16    5    12     2   4  5000U   64   M      57          1\n",
      "93       16    6    16     2   4  5000U   64   M      58          1\n",
      "\n",
      "17\n",
      "    patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "94       17    1     0     2   5  10000U   45   F      62          2\n",
      "95       17    2     2     2   5  10000U   45   F      60          2\n",
      "96       17    3     4     2   5  10000U   45   F      60          2\n",
      "97       17    4     8     2   5  10000U   45   F      64          2\n",
      "98       17    5    12     2   5  10000U   45   F      67          2\n",
      "99       17    6    16     2   5  10000U   45   F      66          2\n",
      "\n",
      "18\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "100       18    1     0     2   6  Placebo   66   F      50          0\n",
      "101       18    2     2     2   6  Placebo   66   F      53          0\n",
      "102       18    3     4     2   6  Placebo   66   F      52          0\n",
      "103       18    4     8     2   6  Placebo   66   F      57          0\n",
      "104       18    5    12     2   6  Placebo   66   F      61          0\n",
      "105       18    6    16     2   6  Placebo   66   F      54          0\n",
      "\n",
      "19\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "106       19    1     0     2   7  10000U   49   F      42          2\n",
      "107       19    2     2     2   7  10000U   49   F      42          2\n",
      "108       19    3     4     2   7  10000U   49   F      43          2\n",
      "109       19    4     8     2   7  10000U   49   F      33          2\n",
      "110       19    5    12     2   7  10000U   49   F      37          2\n",
      "111       19    6    16     2   7  10000U   49   F      43          2\n",
      "\n",
      "20\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "112       20    1     0     2   8  Placebo   54   F      53          0\n",
      "113       20    2     2     2   8  Placebo   54   F      56          0\n",
      "114       20    3     4     2   8  Placebo   54   F      52          0\n",
      "115       20    4     8     2   8  Placebo   54   F      54          0\n",
      "116       20    5    12     2   8  Placebo   54   F      55          0\n",
      "117       20    6    16     2   8  Placebo   54   F      51          0\n",
      "\n",
      "21\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "118       21    1     0     2   9  5000U   47   F      67          1\n",
      "119       21    2     2     2   9  5000U   47   F      64          1\n",
      "120       21    3     4     2   9  5000U   47   F      65          1\n",
      "121       21    4     8     2   9  5000U   47   F      64          1\n",
      "122       21    5    12     2   9  5000U   47   F      62          1\n",
      "123       21    6    16     2   9  5000U   47   F      64          1\n",
      "\n",
      "22\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "124       22    1     0     2  10  Placebo   31   M      44          0\n",
      "125       22    2     2     2  10  Placebo   31   M      40          0\n",
      "126       22    3     4     2  10  Placebo   31   M      32          0\n",
      "127       22    4     8     2  10  Placebo   31   M      36          0\n",
      "128       22    5    12     2  10  Placebo   31   M      42          0\n",
      "129       22    6    16     2  10  Placebo   31   M      43          0\n",
      "\n",
      "23\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "130       23    1     0     2  11  10000U   53   F      65          2\n",
      "131       23    2     2     2  11  10000U   53   F      58          2\n",
      "132       23    3     4     2  11  10000U   53   F      55          2\n",
      "133       23    5    12     2  11  10000U   53   F      56          2\n",
      "134       23    6    16     2  11  10000U   53   F      60          2\n",
      "\n",
      "24\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "135       24    1     0     2  12  5000U   61   M      56          1\n",
      "136       24    2     2     2  12  5000U   61   M      54          1\n",
      "137       24    3     4     2  12  5000U   61   M      52          1\n",
      "138       24    4     8     2  12  5000U   61   M      48          1\n",
      "139       24    5    12     2  12  5000U   61   M      52          1\n",
      "140       24    6    16     2  12  5000U   61   M      53          1\n",
      "\n",
      "25\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "141       25    1     0     2  13  Placebo   40   M      30          0\n",
      "142       25    2     2     2  13  Placebo   40   M      33          0\n",
      "143       25    3     4     2  13  Placebo   40   M      25          0\n",
      "144       25    4     8     2  13  Placebo   40   M      29          0\n",
      "145       25    5    12     2  13  Placebo   40   M      32          0\n",
      "146       25    6    16     2  13  Placebo   40   M      32          0\n",
      "\n",
      "26\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "147       26    1     0     2  14  5000U   67   M      47          1\n",
      "148       26    3     4     2  14  5000U   67   M      54          1\n",
      "149       26    4     8     2  14  5000U   67   M      43          1\n",
      "150       26    5    12     2  14  5000U   67   M      46          1\n",
      "151       26    6    16     2  14  5000U   67   M      50          1\n",
      "\n",
      "27\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "152       27    1     0     3   1  10000U   54   F      50          2\n",
      "153       27    2     2     3   1  10000U   54   F      43          2\n",
      "154       27    3     4     3   1  10000U   54   F      51          2\n",
      "155       27    4     8     3   1  10000U   54   F      46          2\n",
      "156       27    5    12     3   1  10000U   54   F      49          2\n",
      "157       27    6    16     3   1  10000U   54   F      53          2\n",
      "\n",
      "28\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "158       28    1     0     3   2  Placebo   41   F      34          0\n",
      "159       28    2     2     3   2  Placebo   41   F      29          0\n",
      "160       28    3     4     3   2  Placebo   41   F      27          0\n",
      "161       28    4     8     3   2  Placebo   41   F      21          0\n",
      "162       28    5    12     3   2  Placebo   41   F      22          0\n",
      "163       28    6    16     3   2  Placebo   41   F      22          0\n",
      "\n",
      "29\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "164       29    1     0     3   3  5000U   66   M      39          1\n",
      "165       29    2     2     3   3  5000U   66   M      41          1\n",
      "166       29    3     4     3   3  5000U   66   M      33          1\n",
      "167       29    4     8     3   3  5000U   66   M      39          1\n",
      "168       29    5    12     3   3  5000U   66   M      37          1\n",
      "169       29    6    16     3   3  5000U   66   M      37          1\n",
      "\n",
      "30\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "170       30    1     0     3   4  Placebo   68   F      43          0\n",
      "171       30    2     2     3   4  Placebo   68   F      31          0\n",
      "172       30    3     4     3   4  Placebo   68   F      29          0\n",
      "173       30    4     8     3   4  Placebo   68   F      28          0\n",
      "174       30    5    12     3   4  Placebo   68   F      33          0\n",
      "175       30    6    16     3   4  Placebo   68   F      38          0\n",
      "\n",
      "31\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "176       31    1     0     3   5  10000U   41   F      46          2\n",
      "177       31    2     2     3   5  10000U   41   F      26          2\n",
      "178       31    3     4     3   5  10000U   41   F      29          2\n",
      "179       31    4     8     3   5  10000U   41   F      33          2\n",
      "180       31    5    12     3   5  10000U   41   F      45          2\n",
      "181       31    6    16     3   5  10000U   41   F      56          2\n",
      "\n",
      "32\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "182       32    1     0     3   6  5000U   77   M      52          1\n",
      "183       32    2     2     3   6  5000U   77   M      44          1\n",
      "184       32    3     4     3   6  5000U   77   M      47          1\n",
      "185       32    4     8     3   6  5000U   77   M      50          1\n",
      "186       32    5    12     3   6  5000U   77   M      50          1\n",
      "187       32    6    16     3   6  5000U   77   M      49          1\n",
      "\n",
      "33\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "188       33    1     0     3   7  10000U   41   M      38          2\n",
      "189       33    2     2     3   7  10000U   41   M      19          2\n",
      "190       33    3     4     3   7  10000U   41   M      20          2\n",
      "191       33    4     8     3   7  10000U   41   M      27          2\n",
      "192       33    5    12     3   7  10000U   41   M      29          2\n",
      "193       33    6    16     3   7  10000U   41   M      32          2\n",
      "\n",
      "34\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "194       34    1     0     3   8  Placebo   56   M      33          0\n",
      "195       34    2     2     3   8  Placebo   56   M      38          0\n",
      "196       34    3     4     3   8  Placebo   56   M      40          0\n",
      "197       34    4     8     3   8  Placebo   56   M      48          0\n",
      "198       34    5    12     3   8  Placebo   56   M      49          0\n",
      "199       34    6    16     3   8  Placebo   56   M      44          0\n",
      "\n",
      "35\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "200       35    1     0     3   9  5000U   46   F      28          1\n",
      "201       35    2     2     3   9  5000U   46   F      16          1\n",
      "202       35    3     4     3   9  5000U   46   F      11          1\n",
      "203       35    4     8     3   9  5000U   46   F       7          1\n",
      "204       35    5    12     3   9  5000U   46   F      13          1\n",
      "205       35    6    16     3   9  5000U   46   F      21          1\n",
      "\n",
      "36\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "206       36    1     0     3  10  10000U   46   F      34          2\n",
      "207       36    2     2     3  10  10000U   46   F      23          2\n",
      "208       36    3     4     3  10  10000U   46   F      16          2\n",
      "209       36    4     8     3  10  10000U   46   F      15          2\n",
      "210       36    5    12     3  10  10000U   46   F      17          2\n",
      "211       36    6    16     3  10  10000U   46   F      29          2\n",
      "\n",
      "37\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "212       37    1     0     3  11  Placebo   47   F      39          0\n",
      "213       37    2     2     3  11  Placebo   47   F      37          0\n",
      "214       37    3     4     3  11  Placebo   47   F      39          0\n",
      "215       37    4     8     3  11  Placebo   47   F      39          0\n",
      "216       37    5    12     3  11  Placebo   47   F      45          0\n",
      "217       37    6    16     3  11  Placebo   47   F      43          0\n",
      "\n",
      "38\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "218       38    1     0     3  12  5000U   35   M      29          1\n",
      "219       38    2     2     3  12  5000U   35   M      42          1\n",
      "220       38    3     4     3  12  5000U   35   M      35          1\n",
      "221       38    4     8     3  12  5000U   35   M      24          1\n",
      "222       38    5    12     3  12  5000U   35   M      29          1\n",
      "223       38    6    16     3  12  5000U   35   M      42          1\n",
      "\n",
      "39\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "224       39    1     0     4   1  Placebo   58   M      52          0\n",
      "225       39    2     2     4   1  Placebo   58   M      55          0\n",
      "226       39    3     4     4   1  Placebo   58   M      51          0\n",
      "227       39    4     8     4   1  Placebo   58   M      52          0\n",
      "228       39    5    12     4   1  Placebo   58   M      54          0\n",
      "229       39    6    16     4   1  Placebo   58   M      57          0\n",
      "\n",
      "40\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "230       40    1     0     4   2  5000U   62   F      52          1\n",
      "231       40    2     2     4   2  5000U   62   F      30          1\n",
      "232       40    3     4     4   2  5000U   62   F      43          1\n",
      "233       40    4     8     4   2  5000U   62   F      45          1\n",
      "234       40    5    12     4   2  5000U   62   F      47          1\n",
      "235       40    6    16     4   2  5000U   62   F      46          1\n",
      "\n",
      "41\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "236       41    1     0     4   3  10000U   73   F      54          2\n",
      "237       41    2     2     4   3  10000U   73   F      52          2\n",
      "238       41    3     4     4   3  10000U   73   F      52          2\n",
      "239       41    4     8     4   3  10000U   73   F      54          2\n",
      "240       41    5    12     4   3  10000U   73   F      51          2\n",
      "241       41    6    16     4   3  10000U   73   F      57          2\n",
      "\n",
      "42\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "242       42    1     0     4   4  10000U   52   F      52          2\n",
      "243       42    2     2     4   4  10000U   52   F      44          2\n",
      "244       42    3     4     4   4  10000U   52   F      33          2\n",
      "245       42    4     8     4   4  10000U   52   F      54          2\n",
      "246       42    5    12     4   4  10000U   52   F      46          2\n",
      "247       42    6    16     4   4  10000U   52   F      47          2\n",
      "\n",
      "43\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "248       43    1     0     4   5  Placebo   53   F      47          0\n",
      "249       43    2     2     4   5  Placebo   53   F      45          0\n",
      "250       43    3     4     4   5  Placebo   53   F      41          0\n",
      "251       43    4     8     4   5  Placebo   53   F      45          0\n",
      "252       43    5    12     4   5  Placebo   53   F      43          0\n",
      "253       43    6    16     4   5  Placebo   53   F      41          0\n",
      "\n",
      "44\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "254       44    1     0     4   6  5000U   69   M      44          1\n",
      "255       44    2     2     4   6  5000U   69   M      34          1\n",
      "256       44    3     4     4   6  5000U   69   M      29          1\n",
      "257       44    4     8     4   6  5000U   69   M      28          1\n",
      "258       44    5    12     4   6  5000U   69   M      35          1\n",
      "259       44    6    16     4   6  5000U   69   M      41          1\n",
      "\n",
      "45\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "260       45    1     0     4   7  Placebo   55   M      42          0\n",
      "261       45    2     2     4   7  Placebo   55   M      39          0\n",
      "262       45    3     4     4   7  Placebo   55   M      38          0\n",
      "263       45    4     8     4   7  Placebo   55   M      47          0\n",
      "264       45    5    12     4   7  Placebo   55   M      39          0\n",
      "265       45    6    16     4   7  Placebo   55   M      39          0\n",
      "\n",
      "46\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "266       46    1     0     4   8  10000U   52   F      42          2\n",
      "267       46    2     2     4   8  10000U   52   F      14          2\n",
      "268       46    3     4     4   8  10000U   52   F       9          2\n",
      "269       46    4     8     4   8  10000U   52   F       9          2\n",
      "270       46    5    12     4   8  10000U   52   F      16          2\n",
      "271       46    6    16     4   8  10000U   52   F      33          2\n",
      "\n",
      "47\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "272       47    1     0     5   1  10000U   51   F      44          2\n",
      "273       47    2     2     5   1  10000U   51   F      34          2\n",
      "274       47    3     4     5   1  10000U   51   F      32          2\n",
      "275       47    4     8     5   1  10000U   51   F      35          2\n",
      "276       47    5    12     5   1  10000U   51   F      54          2\n",
      "277       47    6    16     5   1  10000U   51   F      53          2\n",
      "\n",
      "48\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "278       48    1     0     5   2  Placebo   56   F      60          0\n",
      "279       48    2     2     5   2  Placebo   56   F      57          0\n",
      "280       48    3     4     5   2  Placebo   56   F      53          0\n",
      "281       48    4     8     5   2  Placebo   56   F      52          0\n",
      "282       48    5    12     5   2  Placebo   56   F      53          0\n",
      "283       48    6    16     5   2  Placebo   56   F      58          0\n",
      "\n",
      "49\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "284       49    1     0     5   3  5000U   65   F      60          1\n",
      "285       49    2     2     5   3  5000U   65   F      53          1\n",
      "286       49    3     4     5   3  5000U   65   F      55          1\n",
      "287       49    4     8     5   3  5000U   65   F      62          1\n",
      "288       49    5    12     5   3  5000U   65   F      67          1\n",
      "\n",
      "50\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "289       50    1     0     5   4  10000U   35   F      50          2\n",
      "290       50    2     2     5   4  10000U   35   F      50          2\n",
      "291       50    4     8     5   4  10000U   35   F      46          2\n",
      "292       50    5    12     5   4  10000U   35   F      50          2\n",
      "293       50    6    16     5   4  10000U   35   F      57          2\n",
      "\n",
      "51\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "294       51    1     0     5   5  5000U   43   M      38          1\n",
      "295       51    2     2     5   5  5000U   43   M      27          1\n",
      "296       51    3     4     5   5  5000U   43   M      16          1\n",
      "297       51    4     8     5   5  5000U   43   M      19          1\n",
      "298       51    5    12     5   5  5000U   43   M      23          1\n",
      "299       51    6    16     5   5  5000U   43   M      26          1\n",
      "\n",
      "52\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "300       52    1     0     5   6  Placebo   61   M      44          0\n",
      "301       52    3     4     5   6  Placebo   61   M      46          0\n",
      "302       52    4     8     5   6  Placebo   61   M      26          0\n",
      "303       52    5    12     5   6  Placebo   61   M      30          0\n",
      "304       52    6    16     5   6  Placebo   61   M      34          0\n",
      "\n",
      "53\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "305       53    1     0     6   1  Placebo   43   M      54          0\n",
      "306       53    2     2     6   1  Placebo   43   M      53          0\n",
      "307       53    3     4     6   1  Placebo   43   M      51          0\n",
      "308       53    4     8     6   1  Placebo   43   M      56          0\n",
      "309       53    5    12     6   1  Placebo   43   M      39          0\n",
      "310       53    6    16     6   1  Placebo   43   M       9          0\n",
      "\n",
      "54\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "311       54    1     0     6   2  10000U   64   F      54          2\n",
      "312       54    2     2     6   2  10000U   64   F      32          2\n",
      "313       54    3     4     6   2  10000U   64   F      40          2\n",
      "314       54    4     8     6   2  10000U   64   F      52          2\n",
      "315       54    5    12     6   2  10000U   64   F      42          2\n",
      "316       54    6    16     6   2  10000U   64   F      47          2\n",
      "\n",
      "55\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "317       55    1     0     6   3  5000U   57   M      56          1\n",
      "318       55    2     2     6   3  5000U   57   M      55          1\n",
      "319       55    3     4     6   3  5000U   57   M      44          1\n",
      "320       55    4     8     6   3  5000U   57   M      50          1\n",
      "321       55    5    12     6   3  5000U   57   M      53          1\n",
      "322       55    6    16     6   3  5000U   57   M      52          1\n",
      "\n",
      "56\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "323       56    1     0     6   4  5000U   60   F      51          1\n",
      "324       56    2     2     6   4  5000U   60   F      50          1\n",
      "325       56    3     4     6   4  5000U   60   F      50          1\n",
      "326       56    4     8     6   4  5000U   60   F      56          1\n",
      "327       56    5    12     6   4  5000U   60   F      59          1\n",
      "328       56    6    16     6   4  5000U   60   F      53          1\n",
      "\n",
      "57\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "329       57    1     0     6   5  10000U   44   F      53          2\n",
      "330       57    2     2     6   5  10000U   44   F      56          2\n",
      "331       57    3     4     6   5  10000U   44   F      47          2\n",
      "332       57    4     8     6   5  10000U   44   F      53          2\n",
      "333       57    5    12     6   5  10000U   44   F      51          2\n",
      "334       57    6    16     6   5  10000U   44   F      51          2\n",
      "\n",
      "58\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "335       58    1     0     6   6  Placebo   41   F      36          0\n",
      "336       58    2     2     6   6  Placebo   41   F      29          0\n",
      "337       58    3     4     6   6  Placebo   41   F      24          0\n",
      "338       58    4     8     6   6  Placebo   41   F      32          0\n",
      "339       58    5    12     6   6  Placebo   41   F      45          0\n",
      "340       58    6    16     6   6  Placebo   41   F      36          0\n",
      "\n",
      "59\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "341       59    1     0     6   7  5000U   51   F      59          1\n",
      "342       59    2     2     6   7  5000U   51   F      53          1\n",
      "343       59    3     4     6   7  5000U   51   F      45          1\n",
      "344       59    4     8     6   7  5000U   51   F      44          1\n",
      "345       59    5    12     6   7  5000U   51   F      50          1\n",
      "346       59    6    16     6   7  5000U   51   F      48          1\n",
      "\n",
      "60\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "347       60    1     0     6   8  Placebo   57   F      49          0\n",
      "348       60    2     2     6   8  Placebo   57   F      50          0\n",
      "349       60    3     4     6   8  Placebo   57   F      48          0\n",
      "350       60    4     8     6   8  Placebo   57   F      56          0\n",
      "351       60    5    12     6   8  Placebo   57   F      49          0\n",
      "352       60    6    16     6   8  Placebo   57   F      57          0\n",
      "\n",
      "61\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "353       61    1     0     6   9  10000U   42   F      50          2\n",
      "354       61    2     2     6   9  10000U   42   F      38          2\n",
      "355       61    3     4     6   9  10000U   42   F      42          2\n",
      "356       61    4     8     6   9  10000U   42   F      43          2\n",
      "357       61    5    12     6   9  10000U   42   F      42          2\n",
      "358       61    6    16     6   9  10000U   42   F      46          2\n",
      "\n",
      "62\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "359       62    1     0     6  10  Placebo   48   F      46          0\n",
      "360       62    2     2     6  10  Placebo   48   F      48          0\n",
      "361       62    3     4     6  10  Placebo   48   F      46          0\n",
      "362       62    4     8     6  10  Placebo   48   F      57          0\n",
      "363       62    5    12     6  10  Placebo   48   F      57          0\n",
      "364       62    6    16     6  10  Placebo   48   F      49          0\n",
      "\n",
      "63\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "365       63    1     0     6  11  10000U   57   M      55          2\n",
      "366       63    2     2     6  11  10000U   57   M      34          2\n",
      "367       63    3     4     6  11  10000U   57   M      26          2\n",
      "368       63    4     8     6  11  10000U   57   M      40          2\n",
      "369       63    5    12     6  11  10000U   57   M      49          2\n",
      "370       63    6    16     6  11  10000U   57   M      47          2\n",
      "\n",
      "64\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "371       64    1     0     6  12  5000U   39   M      46          1\n",
      "372       64    2     2     6  12  5000U   39   M      44          1\n",
      "373       64    3     4     6  12  5000U   39   M      47          1\n",
      "374       64    4     8     6  12  5000U   39   M      50          1\n",
      "375       64    5    12     6  12  5000U   39   M      46          1\n",
      "376       64    6    16     6  12  5000U   39   M      51          1\n",
      "\n",
      "65\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "377       65    1     0     6  13  10000U   67   M      34          2\n",
      "378       65    2     2     6  13  10000U   67   M      31          2\n",
      "379       65    3     4     6  13  10000U   67   M      25          2\n",
      "\n",
      "66\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "380       66    1     0     6  14  5000U   39   F      57          1\n",
      "381       66    2     2     6  14  5000U   39   F      48          1\n",
      "382       66    3     4     6  14  5000U   39   F      50          1\n",
      "383       66    4     8     6  14  5000U   39   F      50          1\n",
      "384       66    5    12     6  14  5000U   39   F      50          1\n",
      "385       66    6    16     6  14  5000U   39   F      49          1\n",
      "\n",
      "67\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "386       67    1     0     6  15  Placebo   69   M      41          0\n",
      "387       67    2     2     6  15  Placebo   69   M      40          0\n",
      "388       67    3     4     6  15  Placebo   69   M      42          0\n",
      "389       67    4     8     6  15  Placebo   69   M      38          0\n",
      "390       67    5    12     6  15  Placebo   69   M      50          0\n",
      "391       67    6    16     6  15  Placebo   69   M      56          0\n",
      "\n",
      "68\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "392       68    1     0     7   1  5000U   54   F      49          1\n",
      "393       68    2     2     7   1  5000U   54   F      25          1\n",
      "394       68    3     4     7   1  5000U   54   F      30          1\n",
      "395       68    4     8     7   1  5000U   54   F      41          1\n",
      "396       68    5    12     7   1  5000U   54   F      41          1\n",
      "397       68    6    16     7   1  5000U   54   F      31          1\n",
      "\n",
      "69\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "398       69    1     0     7   2  Placebo   67   F      42          0\n",
      "399       69    2     2     7   2  Placebo   67   F      30          0\n",
      "400       69    3     4     7   2  Placebo   67   F      40          0\n",
      "401       69    4     8     7   2  Placebo   67   F      43          0\n",
      "402       69    5    12     7   2  Placebo   67   F      36          0\n",
      "403       69    6    16     7   2  Placebo   67   F      45          0\n",
      "\n",
      "70\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "404       70    1     0     7   3  10000U   58   F      31          2\n",
      "405       70    2     2     7   3  10000U   58   F      18          2\n",
      "406       70    3     4     7   3  10000U   58   F      23          2\n",
      "407       70    4     8     7   3  10000U   58   F      26          2\n",
      "408       70    5    12     7   3  10000U   58   F      33          2\n",
      "409       70    6    16     7   3  10000U   58   F      41          2\n",
      "\n",
      "71\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "410       71    1     0     7   4  Placebo   72   F      50          0\n",
      "411       71    2     2     7   4  Placebo   72   F      27          0\n",
      "412       71    3     4     7   4  Placebo   72   F      43          0\n",
      "413       71    4     8     7   4  Placebo   72   F      32          0\n",
      "414       71    5    12     7   4  Placebo   72   F      40          0\n",
      "415       71    6    16     7   4  Placebo   72   F      47          0\n",
      "\n",
      "72\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "416       72    1     0     7   5  10000U   65   F      35          2\n",
      "417       72    2     2     7   5  10000U   65   F      24          2\n",
      "418       72    3     4     7   5  10000U   65   F      34          2\n",
      "419       72    4     8     7   5  10000U   65   F      28          2\n",
      "420       72    5    12     7   5  10000U   65   F      34          2\n",
      "421       72    6    16     7   5  10000U   65   F      28          2\n",
      "\n",
      "73\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "422       73    1     0     7   6  5000U   68   F      38          1\n",
      "423       73    2     2     7   6  5000U   68   F      25          1\n",
      "424       73    3     4     7   6  5000U   68   F      21          1\n",
      "425       73    4     8     7   6  5000U   68   F      33          1\n",
      "426       73    5    12     7   6  5000U   68   F      42          1\n",
      "427       73    6    16     7   6  5000U   68   F      53          1\n",
      "\n",
      "74\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "428       74    1     0     7   7  10000U   75   F      53          2\n",
      "429       74    2     2     7   7  10000U   75   F      40          2\n",
      "430       74    3     4     7   7  10000U   75   F      38          2\n",
      "431       74    4     8     7   7  10000U   75   F      44          2\n",
      "432       74    5    12     7   7  10000U   75   F      47          2\n",
      "433       74    6    16     7   7  10000U   75   F      53          2\n",
      "\n",
      "75\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "434       75    1     0     7   8  Placebo   26   F      42          0\n",
      "435       75    2     2     7   8  Placebo   26   F      48          0\n",
      "436       75    3     4     7   8  Placebo   26   F      26          0\n",
      "437       75    4     8     7   8  Placebo   26   F      37          0\n",
      "438       75    5    12     7   8  Placebo   26   F      37          0\n",
      "439       75    6    16     7   8  Placebo   26   F      43          0\n",
      "\n",
      "76\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "440       76    1     0     7   9  5000U   36   F      53          1\n",
      "441       76    2     2     7   9  5000U   36   F      45          1\n",
      "442       76    3     4     7   9  5000U   36   F      52          1\n",
      "443       76    4     8     7   9  5000U   36   F      51          1\n",
      "444       76    5    12     7   9  5000U   36   F      52          1\n",
      "445       76    6    16     7   9  5000U   36   F      53          1\n",
      "\n",
      "77\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "446       77    1     0     7  10  10000U   72   M      46          2\n",
      "447       77    2     2     7  10  10000U   72   M      47          2\n",
      "448       77    3     4     7  10  10000U   72   M      45          2\n",
      "449       77    4     8     7  10  10000U   72   M      45          2\n",
      "450       77    5    12     7  10  10000U   72   M      50          2\n",
      "451       77    6    16     7  10  10000U   72   M      52          2\n",
      "\n",
      "78\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "452       78    1     0     7  11  Placebo   54   F      50          0\n",
      "453       78    2     2     7  11  Placebo   54   F      42          0\n",
      "454       78    3     4     7  11  Placebo   54   F      52          0\n",
      "455       78    4     8     7  11  Placebo   54   F      60          0\n",
      "456       78    5    12     7  11  Placebo   54   F      54          0\n",
      "457       78    6    16     7  11  Placebo   54   F      59          0\n",
      "\n",
      "79\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "458       79    1     0     7  12  5000U   64   F      43          1\n",
      "459       79    2     2     7  12  5000U   64   F      24          1\n",
      "460       79    3     4     7  12  5000U   64   F      17          1\n",
      "461       79    4     8     7  12  5000U   64   F      37          1\n",
      "462       79    5    12     7  12  5000U   64   F      36          1\n",
      "463       79    6    16     7  12  5000U   64   F      38          1\n",
      "\n",
      "80\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "464       80    1     0     8   1  Placebo   39   F      46          0\n",
      "465       80    2     2     8   1  Placebo   39   F      39          0\n",
      "466       80    3     4     8   1  Placebo   39   F      25          0\n",
      "467       80    4     8     8   1  Placebo   39   F      15          0\n",
      "468       80    5    12     8   1  Placebo   39   F      21          0\n",
      "469       80    6    16     8   1  Placebo   39   F      25          0\n",
      "\n",
      "81\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "470       81    1     0     8   2  10000U   54   M      41          2\n",
      "471       81    2     2     8   2  10000U   54   M      30          2\n",
      "472       81    3     4     8   2  10000U   54   M      44          2\n",
      "473       81    4     8     8   2  10000U   54   M      46          2\n",
      "474       81    5    12     8   2  10000U   54   M      46          2\n",
      "475       81    6    16     8   2  10000U   54   M      44          2\n",
      "\n",
      "82\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "476       82    1     0     8   3  5000U   48   M      33          1\n",
      "477       82    2     2     8   3  5000U   48   M      27          1\n",
      "478       82    3     4     8   3  5000U   48   M      25          1\n",
      "479       82    4     8     8   3  5000U   48   M      30          1\n",
      "480       82    5    12     8   3  5000U   48   M      28          1\n",
      "481       82    6    16     8   3  5000U   48   M      30          1\n",
      "\n",
      "83\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "482       83    1     0     8   4  5000U   83   F      36          1\n",
      "483       83    2     2     8   4  5000U   83   F      15          1\n",
      "484       83    3     4     8   4  5000U   83   F      16          1\n",
      "485       83    4     8     8   4  5000U   83   F      17          1\n",
      "486       83    5    12     8   4  5000U   83   F      22          1\n",
      "487       83    6    16     8   4  5000U   83   F      41          1\n",
      "\n",
      "84\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "488       84    1     0     8   5  10000U   74   M      33          2\n",
      "489       84    2     2     8   5  10000U   74   M      32          2\n",
      "490       84    3     4     8   5  10000U   74   M      31          2\n",
      "491       84    4     8     8   5  10000U   74   M      27          2\n",
      "492       84    5    12     8   5  10000U   74   M      49          2\n",
      "493       84    6    16     8   5  10000U   74   M      60          2\n",
      "\n",
      "85\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "494       85    1     0     8   6  Placebo   41   M      37          0\n",
      "\n",
      "86\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "495       86    1     0     8   7  10000U   65   F      24          2\n",
      "496       86    2     2     8   7  10000U   65   F      29          2\n",
      "497       86    3     4     8   7  10000U   65   F      18          2\n",
      "498       86    4     8     8   7  10000U   65   F      20          2\n",
      "499       86    5    12     8   7  10000U   65   F      25          2\n",
      "500       86    6    16     8   7  10000U   65   F      41          2\n",
      "\n",
      "87\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "501       87    1     0     8   8  5000U   79   M      42          1\n",
      "502       87    2     2     8   8  5000U   79   M      23          1\n",
      "503       87    3     4     8   8  5000U   79   M      30          1\n",
      "504       87    4     8     8   8  5000U   79   M      36          1\n",
      "505       87    5    12     8   8  5000U   79   M      41          1\n",
      "506       87    6    16     8   8  5000U   79   M      43          1\n",
      "\n",
      "88\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "507       88    1     0     8   9  Placebo   63   M      30          0\n",
      "508       88    2     2     8   9  Placebo   63   M      22          0\n",
      "509       88    3     4     8   9  Placebo   63   M      21          0\n",
      "510       88    4     8     8   9  Placebo   63   M      25          0\n",
      "511       88    5    12     8   9  Placebo   63   M      26          0\n",
      "512       88    6    16     8   9  Placebo   63   M      33          0\n",
      "\n",
      "89\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "513       89    1     0     8  10  Placebo   63   F      42          0\n",
      "514       89    2     2     8  10  Placebo   63   F      46          0\n",
      "515       89    3     4     8  10  Placebo   63   F      41          0\n",
      "516       89    4     8     8  10  Placebo   63   F      43          0\n",
      "517       89    5    12     8  10  Placebo   63   F      49          0\n",
      "518       89    6    16     8  10  Placebo   63   F      54          0\n",
      "\n",
      "90\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "519       90    1     0     8  11  10000U   34   F      49          2\n",
      "520       90    2     2     8  11  10000U   34   F      25          2\n",
      "521       90    3     4     8  11  10000U   34   F      30          2\n",
      "522       90    4     8     8  11  10000U   34   F      49          2\n",
      "523       90    5    12     8  11  10000U   34   F      55          2\n",
      "524       90    6    16     8  11  10000U   34   F      58          2\n",
      "\n",
      "91\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "525       91    1     0     8  12  5000U   42   M      58          1\n",
      "526       91    2     2     8  12  5000U   42   M      46          1\n",
      "527       91    3     4     8  12  5000U   42   M      46          1\n",
      "528       91    4     8     8  12  5000U   42   M      50          1\n",
      "529       91    5    12     8  12  5000U   42   M      56          1\n",
      "530       91    6    16     8  12  5000U   42   M      60          1\n",
      "\n",
      "92\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "531       92    1     0     8  13  Placebo   57   M      26          0\n",
      "532       92    2     2     8  13  Placebo   57   M      26          0\n",
      "533       92    3     4     8  13  Placebo   57   M      27          0\n",
      "534       92    4     8     8  13  Placebo   57   M      22          0\n",
      "535       92    5    12     8  13  Placebo   57   M      38          0\n",
      "536       92    6    16     8  13  Placebo   57   M      35          0\n",
      "\n",
      "93\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "537       93    1     0     8  14  5000U   68   M      37          1\n",
      "538       93    3     4     8  14  5000U   68   M      23          1\n",
      "539       93    4     8     8  14  5000U   68   M      18          1\n",
      "540       93    5    12     8  14  5000U   68   M      34          1\n",
      "541       93    6    16     8  14  5000U   68   M      36          1\n",
      "\n",
      "94\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "542       94    1     0     8  15  10000U   51   M      40          2\n",
      "543       94    2     2     8  15  10000U   51   M      24          2\n",
      "544       94    3     4     8  15  10000U   51   M      25          2\n",
      "545       94    4     8     8  15  10000U   51   M      37          2\n",
      "546       94    6    16     8  15  10000U   51   M      38          2\n",
      "\n",
      "95\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "547       95    1     0     8  16  5000U   51   F      33          1\n",
      "548       95    2     2     8  16  5000U   51   F      10          1\n",
      "549       95    3     4     8  16  5000U   51   F      13          1\n",
      "550       95    4     8     8  16  5000U   51   F      16          1\n",
      "551       95    5    12     8  16  5000U   51   F      32          1\n",
      "552       95    6    16     8  16  5000U   51   F      16          1\n",
      "\n",
      "96\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "553       96    1     0     8  17  10000U   61   F      41          2\n",
      "554       96    2     2     8  17  10000U   61   F      50          2\n",
      "555       96    3     4     8  17  10000U   61   F      22          2\n",
      "556       96    4     8     8  17  10000U   61   F      28          2\n",
      "557       96    5    12     8  17  10000U   61   F      34          2\n",
      "558       96    6    16     8  17  10000U   61   F      36          2\n",
      "\n",
      "97\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "559       97    1     0     8  18  Placebo   42   M      46          0\n",
      "560       97    3     4     8  18  Placebo   42   M      41          0\n",
      "561       97    4     8     8  18  Placebo   42   M      41          0\n",
      "562       97    5    12     8  18  Placebo   42   M      58          0\n",
      "563       97    6    16     8  18  Placebo   42   M      53          0\n",
      "\n",
      "98\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "564       98    1     0     8  19  10000U   73   F      40          2\n",
      "565       98    2     2     8  19  10000U   73   F      28          2\n",
      "566       98    3     4     8  19  10000U   73   F      29          2\n",
      "567       98    4     8     8  19  10000U   73   F      30          2\n",
      "568       98    5    12     8  19  10000U   73   F      37          2\n",
      "569       98    6    16     8  19  10000U   73   F      44          2\n",
      "\n",
      "99\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "570       99    1     0     9   1  10000U   57   M      40          2\n",
      "571       99    2     2     9   1  10000U   57   M      16          2\n",
      "572       99    3     4     9   1  10000U   57   M      18          2\n",
      "573       99    4     8     9   1  10000U   57   M      25          2\n",
      "574       99    5    12     9   1  10000U   57   M      33          2\n",
      "575       99    6    16     9   1  10000U   57   M      48          2\n",
      "\n",
      "100\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "576      100    1     0     9   2  Placebo   59   M      61          0\n",
      "577      100    2     2     9   2  Placebo   59   M      52          0\n",
      "578      100    3     4     9   2  Placebo   59   M      61          0\n",
      "579      100    4     8     9   2  Placebo   59   M      68          0\n",
      "580      100    5    12     9   2  Placebo   59   M      59          0\n",
      "581      100    6    16     9   2  Placebo   59   M      71          0\n",
      "\n",
      "101\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "582      101    1     0     9   3  5000U   57   M      35          1\n",
      "583      101    2     2     9   3  5000U   57   M      21          1\n",
      "584      101    3     4     9   3  5000U   57   M      29          1\n",
      "585      101    4     8     9   3  5000U   57   M      30          1\n",
      "586      101    5    12     9   3  5000U   57   M      35          1\n",
      "587      101    6    16     9   3  5000U   57   M      48          1\n",
      "\n",
      "102\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "588      102    1     0     9   4  Placebo   68   F      58          0\n",
      "589      102    2     2     9   4  Placebo   68   F      38          0\n",
      "590      102    3     4     9   4  Placebo   68   F      50          0\n",
      "591      102    4     8     9   4  Placebo   68   F      53          0\n",
      "592      102    5    12     9   4  Placebo   68   F      47          0\n",
      "593      102    6    16     9   4  Placebo   68   F      59          0\n",
      "\n",
      "103\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "594      103    1     0     9   5  5000U   55   F      49          1\n",
      "595      103    2     2     9   5  5000U   55   F      45          1\n",
      "596      103    3     4     9   5  5000U   55   F      36          1\n",
      "597      103    5    12     9   5  5000U   55   F      40          1\n",
      "598      103    6    16     9   5  5000U   55   F      52          1\n",
      "\n",
      "104\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "599      104    1     0     9   6  10000U   46   F      52          2\n",
      "600      104    2     2     9   6  10000U   46   F      46          2\n",
      "601      104    3     4     9   6  10000U   46   F      36          2\n",
      "602      104    5    12     9   6  10000U   46   F      45          2\n",
      "603      104    6    16     9   6  10000U   46   F      54          2\n",
      "\n",
      "105\n",
      "     patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
      "604      105    1     0     9   7  Placebo   79   F      45          0\n",
      "605      105    2     2     9   7  Placebo   79   F      46          0\n",
      "606      105    3     4     9   7  Placebo   79   F      33          0\n",
      "607      105    4     8     9   7  Placebo   79   F      44          0\n",
      "608      105    5    12     9   7  Placebo   79   F      46          0\n",
      "609      105    6    16     9   7  Placebo   79   F      48          0\n",
      "\n",
      "106\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "610      106    1     0     9   8  5000U   43   M      67          1\n",
      "611      106    2     2     9   8  5000U   43   M      63          1\n",
      "612      106    3     4     9   8  5000U   43   M      71          1\n",
      "613      106    4     8     9   8  5000U   43   M      66          1\n",
      "614      106    5    12     9   8  5000U   43   M      68          1\n",
      "615      106    6    16     9   8  5000U   43   M      71          1\n",
      "\n",
      "107\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "616      107    1     0     9   9  10000U   50   M      57          2\n",
      "617      107    3     4     9   9  10000U   50   M      36          2\n",
      "618      107    4     8     9   9  10000U   50   M      23          2\n",
      "619      107    6    16     9   9  10000U   50   M      52          2\n",
      "\n",
      "108\n",
      "     patient  obs  week  site  id   treat  age sex  twstrs  treatment\n",
      "620      108    1     0     9  10  10000U   39   F      63          2\n",
      "621      108    2     2     9  10  10000U   39   F      51          2\n",
      "622      108    3     4     9  10  10000U   39   F      46          2\n",
      "623      108    4     8     9  10  10000U   39   F      50          2\n",
      "624      108    5    12     9  10  10000U   39   F      50          2\n",
      "625      108    6    16     9  10  10000U   39   F      54          2\n",
      "\n",
      "109\n",
      "     patient  obs  week  site  id  treat  age sex  twstrs  treatment\n",
      "626      109    1     0     9  11  5000U   57   M      53          1\n",
      "627      109    2     2     9  11  5000U   57   M      38          1\n",
      "628      109    4     8     9  11  5000U   57   M      33          1\n",
      "629      109    5    12     9  11  5000U   57   M      36          1\n",
      "630      109    6    16     9  11  5000U   57   M      51          1\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for patient, group in cdystonia_grouped:\n",
    "    print patient\n",
    "    print group\n",
    "    print"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A common data analysis procedure is the **split-apply-combine** operation, which groups subsets of data together, applies a function to each of the groups, then recombines them into a new data table.\n",
    "\n",
    "For example, we may want to aggregate our data with with some function.\n",
    "\n",
    "![split-apply-combine](http://f.cl.ly/items/0s0Z252j0X0c3k3P1M47/Screen%20Shot%202013-06-02%20at%203.04.04%20PM.png)\n",
    "\n",
    "<div align=\"right\">*(figure taken from \"Python for Data Analysis\", p.251)*</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can aggregate in Pandas using the `aggregate` (or `agg`, for short) method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         patient  obs  week  site  id  age     twstrs  treatment\n",
       "patient                                                         \n",
       "1              1  3.5   7.0     1   1   65  33.000000          1\n",
       "2              2  3.5   7.0     1   2   70  47.666667          2\n",
       "3              3  3.5   7.0     1   3   64  30.500000          1\n",
       "4              4  2.5   3.5     1   4   59  60.000000          0\n",
       "5              5  3.5   7.0     1   5   76  46.166667          2"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia_grouped.agg(np.mean).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the `treat` and `sex` variables are not included in the aggregation. Since it does not make sense to aggregate non-string variables, these columns are simply ignored by the method.\n",
    "\n",
    "Some aggregation functions are so common that Pandas has a convenience method for them, such as `mean`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         patient  obs  week  site  id  age     twstrs  treatment\n",
       "patient                                                         \n",
       "1              1  3.5   7.0     1   1   65  33.000000          1\n",
       "2              2  3.5   7.0     1   2   70  47.666667          2\n",
       "3              3  3.5   7.0     1   3   64  30.500000          1\n",
       "4              4  2.5   3.5     1   4   59  60.000000          0\n",
       "5              5  3.5   7.0     1   5   76  46.166667          2"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia_grouped.mean().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `add_prefix` and `add_suffix` methods can be used to give the columns of the resulting table labels that reflect the transformation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         patient_mean  obs_mean  week_mean  site_mean  id_mean  age_mean  \\\n",
       "patient                                                                    \n",
       "1                   1       3.5        7.0          1        1        65   \n",
       "2                   2       3.5        7.0          1        2        70   \n",
       "3                   3       3.5        7.0          1        3        64   \n",
       "4                   4       2.5        3.5          1        4        59   \n",
       "5                   5       3.5        7.0          1        5        76   \n",
       "\n",
       "         twstrs_mean  treatment_mean  \n",
       "patient                               \n",
       "1          33.000000               1  \n",
       "2          47.666667               2  \n",
       "3          30.500000               1  \n",
       "4          60.000000               0  \n",
       "5          46.166667               2  "
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia_grouped.mean().add_suffix('_mean').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "patient\n",
       "1          34.0\n",
       "2          50.5\n",
       "3          30.5\n",
       "4          61.5\n",
       "5          48.5\n",
       "6          48.0\n",
       "7          42.0\n",
       "8          32.5\n",
       "9          35.5\n",
       "10         20.5\n",
       "...\n",
       "99         29.0\n",
       "100        61.0\n",
       "101        32.5\n",
       "102        51.5\n",
       "103        45.0\n",
       "104        46.0\n",
       "105        45.5\n",
       "106        67.5\n",
       "107        44.0\n",
       "108        50.5\n",
       "109        38.0\n",
       "Name: twstrs, Length: 109, dtype: float64"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The median of the `twstrs` variable\n",
    "cdystonia_grouped['twstrs'].quantile(0.5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we wish, we can easily aggregate according to multiple keys:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           patient  obs   id        age     twstrs  treatment\n",
       "week site                                                    \n",
       "0    1         6.5    1  6.5  59.000000  43.083333   1.000000\n",
       "     2        19.5    1  7.5  53.928571  51.857143   0.928571\n",
       "     3        32.5    1  6.5  51.500000  38.750000   1.000000\n",
       "     4        42.5    1  4.5  59.250000  48.125000   1.000000\n",
       "     5        49.5    1  3.5  51.833333  49.333333   1.000000"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia.groupby(['week','site']).mean().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternately, we can **transform** the data, using a function of our choice with the `transform` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   patient       obs      week  site  id  age    twstrs  treatment\n",
       "0      NaN -1.336306 -1.135550   NaN NaN  NaN -0.181369        NaN\n",
       "1      NaN -0.801784 -0.811107   NaN NaN  NaN -0.544107        NaN\n",
       "2      NaN -0.267261 -0.486664   NaN NaN  NaN -1.632322        NaN\n",
       "3      NaN  0.267261  0.162221   NaN NaN  NaN  0.725476        NaN\n",
       "4      NaN  0.801784  0.811107   NaN NaN  NaN  1.088214        NaN"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "normalize = lambda x: (x - x.mean())/x.std()\n",
    "\n",
    "cdystonia_grouped.transform(normalize).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is easy to do column selection within `groupby` operations, if we are only interested split-apply-combine operations on a subset of columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "patient\n",
       "1          33.000000\n",
       "2          47.666667\n",
       "3          30.500000\n",
       "4          60.000000\n",
       "5          46.166667\n",
       "Name: twstrs, dtype: float64"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia_grouped['twstrs'].mean().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            twstrs\n",
       "patient           \n",
       "1        33.000000\n",
       "2        47.666667\n",
       "3        30.500000\n",
       "4        60.000000\n",
       "5        46.166667"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This gives the same result as a DataFrame\n",
    "cdystonia_grouped[['twstrs']].mean().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you simply want to divide your DataFrame into chunks for later use, its easy to convert them into a dict so that they can be easily indexed out as needed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "chunks = dict(list(cdystonia_grouped))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "    patient  obs  week  site  id    treat  age sex  twstrs  treatment\n",
       "18        4    1     0     1   4  Placebo   59   F      53          0\n",
       "19        4    2     2     1   4  Placebo   59   F      61          0\n",
       "20        4    3     4     1   4  Placebo   59   F      64          0\n",
       "21        4    4     8     1   4  Placebo   59   F      62          0"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chunks[4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, `groupby` groups by row, but we can specify the `axis` argument to change this. For example, we can group our columns by type this way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{dtype('int64'):      patient  obs  week  site  id  age  twstrs  treatment\n",
       " 0          1    1     0     1   1   65      32          1\n",
       " 1          1    2     2     1   1   65      30          1\n",
       " 2          1    3     4     1   1   65      24          1\n",
       " 3          1    4     8     1   1   65      37          1\n",
       " 4          1    5    12     1   1   65      39          1\n",
       " 5          1    6    16     1   1   65      36          1\n",
       " 6          2    1     0     1   2   70      60          2\n",
       " 7          2    2     2     1   2   70      26          2\n",
       " 8          2    3     4     1   2   70      27          2\n",
       " 9          2    4     8     1   2   70      41          2\n",
       " 10         2    5    12     1   2   70      65          2\n",
       " 11         2    6    16     1   2   70      67          2\n",
       " ..       ...  ...   ...   ...  ..  ...     ...        ...\n",
       " 619      107    6    16     9   9   50      52          2\n",
       " 620      108    1     0     9  10   39      63          2\n",
       " 621      108    2     2     9  10   39      51          2\n",
       " 622      108    3     4     9  10   39      46          2\n",
       " 623      108    4     8     9  10   39      50          2\n",
       " 624      108    5    12     9  10   39      50          2\n",
       " 625      108    6    16     9  10   39      54          2\n",
       " 626      109    1     0     9  11   57      53          1\n",
       " 627      109    2     2     9  11   57      38          1\n",
       " 628      109    4     8     9  11   57      33          1\n",
       " 629      109    5    12     9  11   57      36          1\n",
       " 630      109    6    16     9  11   57      51          1\n",
       " \n",
       " [631 rows x 8 columns], dtype('O'):       treat sex\n",
       " 0     5000U   F\n",
       " 1     5000U   F\n",
       " 2     5000U   F\n",
       " 3     5000U   F\n",
       " 4     5000U   F\n",
       " 5     5000U   F\n",
       " 6    10000U   F\n",
       " 7    10000U   F\n",
       " 8    10000U   F\n",
       " 9    10000U   F\n",
       " 10   10000U   F\n",
       " 11   10000U   F\n",
       " ..      ...  ..\n",
       " 619  10000U   M\n",
       " 620  10000U   F\n",
       " 621  10000U   F\n",
       " 622  10000U   F\n",
       " 623  10000U   F\n",
       " 624  10000U   F\n",
       " 625  10000U   F\n",
       " 626   5000U   M\n",
       " 627   5000U   M\n",
       " 628   5000U   M\n",
       " 629   5000U   M\n",
       " 630   5000U   M\n",
       " \n",
       " [631 rows x 2 columns]}"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict(list(cdystonia.groupby(cdystonia.dtypes, axis=1)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Its also possible to group by one or more levels of a hierarchical index. Recall `cdystonia2`, which we created with a hierarchical index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "             week  site  id   treat  age sex  twstrs\n",
       "patient obs                                         \n",
       "1       1       0     1   1   5000U   65   F      32\n",
       "        2       2     1   1   5000U   65   F      30\n",
       "        3       4     1   1   5000U   65   F      24\n",
       "        4       8     1   1   5000U   65   F      37\n",
       "        5      12     1   1   5000U   65   F      39\n",
       "        6      16     1   1   5000U   65   F      36\n",
       "2       1       0     1   2  10000U   70   F      60\n",
       "        2       2     1   2  10000U   70   F      26\n",
       "        3       4     1   2  10000U   70   F      27\n",
       "        4       8     1   2  10000U   70   F      41"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia2.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "obs\n",
       "1      45.651376\n",
       "2      37.611650\n",
       "3      37.066038\n",
       "4      39.807692\n",
       "5      42.913462\n",
       "6      45.628571\n",
       "Name: twstrs, dtype: float64"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdystonia2.groupby(level='obs', axis=0)['twstrs'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Apply\n",
    "\n",
    "We can generalize the split-apply-combine methodology by using `apply` function. This allows us to invoke any function we wish on a grouped dataset and recombine them into a DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function below takes a DataFrame and a column name, sorts by the column, and takes the `n` largest values of that column. We can use this with `apply` to return the largest values from every group in a DataFrame in a single call. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "def top(df, column, n=5):\n",
    "    return df.sort_index(by=column, ascending=False)[:n]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To see this in action, consider the vessel transit segments dataset (which we merged with the vessel information to yield `segments_merged`). Say we wanted to return the 3 longest segments travelled by each ship:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                              names  \\\n",
       "mmsi                                                                  \n",
       "1         6       Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   \n",
       "          5       Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   \n",
       "          7       Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...   \n",
       "9         15                             000000009/Raven/Shearwater   \n",
       "          14                             000000009/Raven/Shearwater   \n",
       "          13                             000000009/Raven/Shearwater   \n",
       "21        16                                          Us Gov Vessel   \n",
       "          25                                          Us Gov Vessel   \n",
       "          30                                          Us Gov Vessel   \n",
       "74        35                                      Mcfaul/Sarah Bell   \n",
       "          34                                      Mcfaul/Sarah Bell   \n",
       "103       37               Ron G/Us Navy Warship 103/Us Warship 103   \n",
       "...                                                             ...   \n",
       "967191190 262409                                         Pathfinder   \n",
       "          262412                                         Pathfinder   \n",
       "          262423                                         Pathfinder   \n",
       "975318642 262478                                     Island Express   \n",
       "          262482                                     Island Express   \n",
       "          262481                                     Island Express   \n",
       "987654321 262498                         Island Lookout/Island Tide   \n",
       "          262507                         Island Lookout/Island Tide   \n",
       "          262510                         Island Lookout/Island Tide   \n",
       "999999999 262520                                  Triple Attraction   \n",
       "          262524                                  Triple Attraction   \n",
       "          262525                                  Triple Attraction   \n",
       "\n",
       "                  seg_length  \n",
       "mmsi                          \n",
       "1         6             76.0  \n",
       "          5             17.4  \n",
       "          7             13.7  \n",
       "9         15            47.2  \n",
       "          14            31.4  \n",
       "          13            19.3  \n",
       "21        16            48.7  \n",
       "          25            25.3  \n",
       "          30            21.7  \n",
       "74        35             7.4  \n",
       "          34             1.4  \n",
       "103       37            87.5  \n",
       "...                      ...  \n",
       "967191190 262409        32.7  \n",
       "          262412        30.6  \n",
       "          262423        30.4  \n",
       "975318642 262478        85.8  \n",
       "          262482        29.5  \n",
       "          262481        27.7  \n",
       "987654321 262498        22.5  \n",
       "          262507        20.9  \n",
       "          262510        18.6  \n",
       "999999999 262520        65.0  \n",
       "          262524        31.5  \n",
       "          262525        19.8  \n",
       "\n",
       "[29464 rows x 2 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top3segments = segments_merged.groupby('mmsi').apply(top, column='seg_length', n=3)[['names', 'seg_length']]\n",
    "top3segments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that additional arguments for the applied function can be passed via `apply` after the function name. It assumes that the DataFrame is the first argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                                     names  seg_length\n",
       "mmsi                                                                  \n",
       "1    6   Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...        76.0\n",
       "     5   Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...        17.4\n",
       "     7   Bil Holman Dredge/Dredge Capt Frank/Emo/Offsho...        13.7\n",
       "9    15                         000000009/Raven/Shearwater        47.2\n",
       "     14                         000000009/Raven/Shearwater        31.4\n",
       "     13                         000000009/Raven/Shearwater        19.3\n",
       "21   16                                      Us Gov Vessel        48.7\n",
       "     25                                      Us Gov Vessel        25.3\n",
       "     30                                      Us Gov Vessel        21.7\n",
       "74   35                                  Mcfaul/Sarah Bell         7.4\n",
       "     34                                  Mcfaul/Sarah Bell         1.4\n",
       "103  37           Ron G/Us Navy Warship 103/Us Warship 103        87.5\n",
       "     41           Ron G/Us Navy Warship 103/Us Warship 103        62.6\n",
       "     43           Ron G/Us Navy Warship 103/Us Warship 103        59.1\n",
       "310  51                                           Arabella        77.4\n",
       "     58                                           Arabella        30.7\n",
       "     49                                           Arabella        30.4\n",
       "3011 74                                         Charleston       121.6\n",
       "     69                                         Charleston        89.7\n",
       "     77                                         Charleston        59.7"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top3segments.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall the microbiome data sets that we used previously for the concatenation example. Suppose that we wish to aggregate the data at a higher biological classification than genus. For example, we can identify samples down to *class*, which is the 3rd level of organization in each index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera', u'Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Pyrodictiaceae Pyrolobus', u'Archaea \"Crenarchaeota\" Thermoprotei Sulfolobales Sulfolobaceae Stygiolobus'], dtype='object')"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb1.index[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the string methods `split` and `join` we can create an index that just uses the first three classifications: domain, phylum and class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "class_index = mb1.index.map(lambda x: ' '.join(x.split(' ')[:3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "mb_class = mb1.copy()\n",
    "mb_class.index = class_index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, since there are multiple taxonomic units with the same class, our index is no longer unique:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                           Count\n",
       "Archaea \"Crenarchaeota\" Thermoprotei           7\n",
       "Archaea \"Crenarchaeota\" Thermoprotei           2\n",
       "Archaea \"Crenarchaeota\" Thermoprotei           3\n",
       "Archaea \"Crenarchaeota\" Thermoprotei           3\n",
       "Archaea \"Euryarchaeota\" \"Methanomicrobia\"      7"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb_class.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can re-establish a unique index by summing all rows with the same class, using `groupby`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                           Count\n",
       "Archaea \"Crenarchaeota\" Thermoprotei          15\n",
       "Archaea \"Euryarchaeota\" \"Methanomicrobia\"      9\n",
       "Archaea \"Euryarchaeota\" Archaeoglobi           2\n",
       "Archaea \"Euryarchaeota\" Halobacteria          12\n",
       "Archaea \"Euryarchaeota\" Methanococci           1\n",
       "Archaea \"Euryarchaeota\" Methanopyri           12\n",
       "Archaea \"Euryarchaeota\" Thermoplasmata         2\n",
       "Bacteria \"Actinobacteria\" Actinobacteria    1740\n",
       "Bacteria \"Aquificae\" Aquificae                11\n",
       "Bacteria \"Bacteroidetes\" \"Bacteroidia\"         1"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mb_class.groupby(level=0).sum().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "Load the dataset in `titanic.xls`. It contains data on all the passengers that travelled on the Titanic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<hr><h2>Data frame:titanic3</h2>1309 observations and 14 variables, maximum # NAs:1188<hr>\n",
       "<TABLE BORDER>\n",
       "<tr><td>Name</td><td>Labels</td><td>Units</td><td>Levels</td><td>Storage</td><td>NAs</td></tr>\n",
       "<tr><td>pclass</td><td></td><td></td><td><a href=\"#pclass\"><div align=right>  3</div></a></td><td>integer</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>survived</td><td>Survived</td><td></td><td><div align=right></div></td><td>double</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>name</td><td>Name</td><td></td><td><div align=right></div></td><td>character</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>sex</td><td></td><td></td><td><a href=\"#sex\"><div align=right>  2</div></a></td><td>integer</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>age</td><td>Age</td><td>Year</td><td><div align=right></div></td><td>double</td><td><div align=right> 263</div></td></tr>\n",
       "<tr><td>sibsp</td><td>Number of Siblings/Spouses Aboard</td><td></td><td><div align=right></div></td><td>double</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>parch</td><td>Number of Parents/Children Aboard</td><td></td><td><div align=right></div></td><td>double</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>ticket</td><td>Ticket Number</td><td></td><td><div align=right></div></td><td>character</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>fare</td><td>Passenger Fare</td><td>British Pound (\\243)</td><td><div align=right></div></td><td>double</td><td><div align=right>   1</div></td></tr>\n",
       "<tr><td>cabin</td><td></td><td></td><td><a href=\"#cabin\"><div align=right>187</div></a></td><td>integer</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>embarked</td><td></td><td></td><td><a href=\"#embarked\"><div align=right>  3</div></a></td><td>integer</td><td><div align=right>   2</div></td></tr>\n",
       "<tr><td>boat</td><td></td><td></td><td><a href=\"#boat\"><div align=right> 28</div></a></td><td>integer</td><td><div align=right>   0</div></td></tr>\n",
       "<tr><td>body</td><td>Body Identification Number</td><td></td><td><div align=right></div></td><td>double</td><td><div align=right>1188</div></td></tr>\n",
       "<tr><td>home.dest</td><td>Home/Destination</td><td></td><td><div align=right></div></td><td>character</td><td><div align=right>   0</div></td></tr>\n",
       "</TABLE>\n",
       "<hr>\n",
       "<TABLE BORDER>\n",
       "<tr><td>Variable</td><td>Levels</td></tr>\n",
       "<tr><td><a name=\"pclass\">pclass</a></td><td>1st</td></tr>\n",
       "<tr><td></td><td>2nd</td></tr>\n",
       "<tr><td></td><td>3rd</td></tr>\n",
       "<tr><td><a name=\"sex\">sex</a></td><td>female</td></tr>\n",
       "<tr><td></td><td>male</td></tr>\n",
       "<tr><td><a name=\"cabin\">cabin</a></td><td></td></tr>\n",
       "<tr><td></td><td>A10</td></tr>\n",
       "<tr><td></td><td>A11</td></tr>\n",
       "<tr><td></td><td>A14</td></tr>\n",
       "<tr><td></td><td>A16</td></tr>\n",
       "<tr><td></td><td>A18</td></tr>\n",
       "<tr><td></td><td>A19</td></tr>\n",
       "<tr><td></td><td>A20</td></tr>\n",
       "<tr><td></td><td>A21</td></tr>\n",
       "<tr><td></td><td>A23</td></tr>\n",
       "<tr><td></td><td>A24</td></tr>\n",
       "<tr><td></td><td>A26</td></tr>\n",
       "<tr><td></td><td>A29</td></tr>\n",
       "<tr><td></td><td>A31</td></tr>\n",
       "<tr><td></td><td>A32</td></tr>\n",
       "<tr><td></td><td>A34</td></tr>\n",
       "<tr><td></td><td>A36</td></tr>\n",
       "<tr><td></td><td>A5</td></tr>\n",
       "<tr><td></td><td>A6</td></tr>\n",
       "<tr><td></td><td>A7</td></tr>\n",
       "<tr><td></td><td>A9</td></tr>\n",
       "<tr><td></td><td>B10</td></tr>\n",
       "<tr><td></td><td>B101</td></tr>\n",
       "<tr><td></td><td>B102</td></tr>\n",
       "<tr><td></td><td>B11</td></tr>\n",
       "<tr><td></td><td>B18</td></tr>\n",
       "<tr><td></td><td>B19</td></tr>\n",
       "<tr><td></td><td>B20</td></tr>\n",
       "<tr><td></td><td>B22</td></tr>\n",
       "<tr><td></td><td>B24</td></tr>\n",
       "<tr><td></td><td>B26</td></tr>\n",
       "<tr><td></td><td>B28</td></tr>\n",
       "<tr><td></td><td>B3</td></tr>\n",
       "<tr><td></td><td>B30</td></tr>\n",
       "<tr><td></td><td>B35</td></tr>\n",
       "<tr><td></td><td>B36</td></tr>\n",
       "<tr><td></td><td>B37</td></tr>\n",
       "<tr><td></td><td>B38</td></tr>\n",
       "<tr><td></td><td>B39</td></tr>\n",
       "<tr><td></td><td>B4</td></tr>\n",
       "<tr><td></td><td>B41</td></tr>\n",
       "<tr><td></td><td>B42</td></tr>\n",
       "<tr><td></td><td>B45</td></tr>\n",
       "<tr><td></td><td>B49</td></tr>\n",
       "<tr><td></td><td>B5</td></tr>\n",
       "<tr><td></td><td>B50</td></tr>\n",
       "<tr><td></td><td>B51 B53 B55</td></tr>\n",
       "<tr><td></td><td>B52 B54 B56</td></tr>\n",
       "<tr><td></td><td>B57 B59 B63 B66</td></tr>\n",
       "<tr><td></td><td>B58 B60</td></tr>\n",
       "<tr><td></td><td>B61</td></tr>\n",
       "<tr><td></td><td>B69</td></tr>\n",
       "<tr><td></td><td>B71</td></tr>\n",
       "<tr><td></td><td>B73</td></tr>\n",
       "<tr><td></td><td>B77</td></tr>\n",
       "<tr><td></td><td>B78</td></tr>\n",
       "<tr><td></td><td>B79</td></tr>\n",
       "<tr><td></td><td>B80</td></tr>\n",
       "<tr><td></td><td>B82 B84</td></tr>\n",
       "<tr><td></td><td>B86</td></tr>\n",
       "<tr><td></td><td>B94</td></tr>\n",
       "<tr><td></td><td>B96 B98</td></tr>\n",
       "<tr><td></td><td>C101</td></tr>\n",
       "<tr><td></td><td>C103</td></tr>\n",
       "<tr><td></td><td>C104</td></tr>\n",
       "<tr><td></td><td>C105</td></tr>\n",
       "<tr><td></td><td>C106</td></tr>\n",
       "<tr><td></td><td>C110</td></tr>\n",
       "<tr><td></td><td>C111</td></tr>\n",
       "<tr><td></td><td>C116</td></tr>\n",
       "<tr><td></td><td>C118</td></tr>\n",
       "<tr><td></td><td>C123</td></tr>\n",
       "<tr><td></td><td>C124</td></tr>\n",
       "<tr><td></td><td>C125</td></tr>\n",
       "<tr><td></td><td>C126</td></tr>\n",
       "<tr><td></td><td>C128</td></tr>\n",
       "<tr><td></td><td>C130</td></tr>\n",
       "<tr><td></td><td>C132</td></tr>\n",
       "<tr><td></td><td>C148</td></tr>\n",
       "<tr><td></td><td>C2</td></tr>\n",
       "<tr><td></td><td>C22 C26</td></tr>\n",
       "<tr><td></td><td>C23 C25 C27</td></tr>\n",
       "<tr><td></td><td>C28</td></tr>\n",
       "<tr><td></td><td>C30</td></tr>\n",
       "<tr><td></td><td>C31</td></tr>\n",
       "<tr><td></td><td>C32</td></tr>\n",
       "<tr><td></td><td>C39</td></tr>\n",
       "<tr><td></td><td>C45</td></tr>\n",
       "<tr><td></td><td>C46</td></tr>\n",
       "<tr><td></td><td>C47</td></tr>\n",
       "<tr><td></td><td>C49</td></tr>\n",
       "<tr><td></td><td>C50</td></tr>\n",
       "<tr><td></td><td>C51</td></tr>\n",
       "<tr><td></td><td>C52</td></tr>\n",
       "<tr><td></td><td>C53</td></tr>\n",
       "<tr><td></td><td>C54</td></tr>\n",
       "<tr><td></td><td>C55 C57</td></tr>\n",
       "<tr><td></td><td>C6</td></tr>\n",
       "<tr><td></td><td>C62 C64</td></tr>\n",
       "<tr><td></td><td>C65</td></tr>\n",
       "<tr><td></td><td>C68</td></tr>\n",
       "<tr><td></td><td>C7</td></tr>\n",
       "<tr><td></td><td>C70</td></tr>\n",
       "<tr><td></td><td>C78</td></tr>\n",
       "<tr><td></td><td>C80</td></tr>\n",
       "<tr><td></td><td>C82</td></tr>\n",
       "<tr><td></td><td>C83</td></tr>\n",
       "<tr><td></td><td>C85</td></tr>\n",
       "<tr><td></td><td>C86</td></tr>\n",
       "<tr><td></td><td>C87</td></tr>\n",
       "<tr><td></td><td>C89</td></tr>\n",
       "<tr><td></td><td>C90</td></tr>\n",
       "<tr><td></td><td>C91</td></tr>\n",
       "<tr><td></td><td>C92</td></tr>\n",
       "<tr><td></td><td>C93</td></tr>\n",
       "<tr><td></td><td>C95</td></tr>\n",
       "<tr><td></td><td>C97</td></tr>\n",
       "<tr><td></td><td>C99</td></tr>\n",
       "<tr><td></td><td>D</td></tr>\n",
       "<tr><td></td><td>D10 D12</td></tr>\n",
       "<tr><td></td><td>D11</td></tr>\n",
       "<tr><td></td><td>D15</td></tr>\n",
       "<tr><td></td><td>D17</td></tr>\n",
       "<tr><td></td><td>D19</td></tr>\n",
       "<tr><td></td><td>D20</td></tr>\n",
       "<tr><td></td><td>D21</td></tr>\n",
       "<tr><td></td><td>D22</td></tr>\n",
       "<tr><td></td><td>D26</td></tr>\n",
       "<tr><td></td><td>D28</td></tr>\n",
       "<tr><td></td><td>D30</td></tr>\n",
       "<tr><td></td><td>D33</td></tr>\n",
       "<tr><td></td><td>D34</td></tr>\n",
       "<tr><td></td><td>D35</td></tr>\n",
       "<tr><td></td><td>D36</td></tr>\n",
       "<tr><td></td><td>D37</td></tr>\n",
       "<tr><td></td><td>D38</td></tr>\n",
       "<tr><td></td><td>D40</td></tr>\n",
       "<tr><td></td><td>D43</td></tr>\n",
       "<tr><td></td><td>D45</td></tr>\n",
       "<tr><td></td><td>D46</td></tr>\n",
       "<tr><td></td><td>D47</td></tr>\n",
       "<tr><td></td><td>D48</td></tr>\n",
       "<tr><td></td><td>D49</td></tr>\n",
       "<tr><td></td><td>D50</td></tr>\n",
       "<tr><td></td><td>D56</td></tr>\n",
       "<tr><td></td><td>D6</td></tr>\n",
       "<tr><td></td><td>D7</td></tr>\n",
       "<tr><td></td><td>D9</td></tr>\n",
       "<tr><td></td><td>E10</td></tr>\n",
       "<tr><td></td><td>E101</td></tr>\n",
       "<tr><td></td><td>E12</td></tr>\n",
       "<tr><td></td><td>E121</td></tr>\n",
       "<tr><td></td><td>E17</td></tr>\n",
       "<tr><td></td><td>E24</td></tr>\n",
       "<tr><td></td><td>E25</td></tr>\n",
       "<tr><td></td><td>E31</td></tr>\n",
       "<tr><td></td><td>E33</td></tr>\n",
       "<tr><td></td><td>E34</td></tr>\n",
       "<tr><td></td><td>E36</td></tr>\n",
       "<tr><td></td><td>E38</td></tr>\n",
       "<tr><td></td><td>E39 E41</td></tr>\n",
       "<tr><td></td><td>E40</td></tr>\n",
       "<tr><td></td><td>E44</td></tr>\n",
       "<tr><td></td><td>E45</td></tr>\n",
       "<tr><td></td><td>E46</td></tr>\n",
       "<tr><td></td><td>E49</td></tr>\n",
       "<tr><td></td><td>E50</td></tr>\n",
       "<tr><td></td><td>E52</td></tr>\n",
       "<tr><td></td><td>E58</td></tr>\n",
       "<tr><td></td><td>E60</td></tr>\n",
       "<tr><td></td><td>E63</td></tr>\n",
       "<tr><td></td><td>E67</td></tr>\n",
       "<tr><td></td><td>E68</td></tr>\n",
       "<tr><td></td><td>E77</td></tr>\n",
       "<tr><td></td><td>E8</td></tr>\n",
       "<tr><td></td><td>F</td></tr>\n",
       "<tr><td></td><td>F E46</td></tr>\n",
       "<tr><td></td><td>F E57</td></tr>\n",
       "<tr><td></td><td>F E69</td></tr>\n",
       "<tr><td></td><td>F G63</td></tr>\n",
       "<tr><td></td><td>F G73</td></tr>\n",
       "<tr><td></td><td>F2</td></tr>\n",
       "<tr><td></td><td>F33</td></tr>\n",
       "<tr><td></td><td>F38</td></tr>\n",
       "<tr><td></td><td>F4</td></tr>\n",
       "<tr><td></td><td>G6</td></tr>\n",
       "<tr><td></td><td>T</td></tr>\n",
       "<tr><td><a name=\"embarked\">embarked</a></td><td>Cherbourg</td></tr>\n",
       "<tr><td></td><td>Queenstown</td></tr>\n",
       "<tr><td></td><td>Southampton</td></tr>\n",
       "<tr><td><a name=\"boat\">boat</a></td><td></td></tr>\n",
       "<tr><td></td><td>1</td></tr>\n",
       "<tr><td></td><td>10</td></tr>\n",
       "<tr><td></td><td>11</td></tr>\n",
       "<tr><td></td><td>12</td></tr>\n",
       "<tr><td></td><td>13</td></tr>\n",
       "<tr><td></td><td>13 15</td></tr>\n",
       "<tr><td></td><td>13 15 B</td></tr>\n",
       "<tr><td></td><td>14</td></tr>\n",
       "<tr><td></td><td>15</td></tr>\n",
       "<tr><td></td><td>15 16</td></tr>\n",
       "<tr><td></td><td>16</td></tr>\n",
       "<tr><td></td><td>2</td></tr>\n",
       "<tr><td></td><td>3</td></tr>\n",
       "<tr><td></td><td>4</td></tr>\n",
       "<tr><td></td><td>5</td></tr>\n",
       "<tr><td></td><td>5 7</td></tr>\n",
       "<tr><td></td><td>5 9</td></tr>\n",
       "<tr><td></td><td>6</td></tr>\n",
       "<tr><td></td><td>7</td></tr>\n",
       "<tr><td></td><td>8</td></tr>\n",
       "<tr><td></td><td>8 10</td></tr>\n",
       "<tr><td></td><td>9</td></tr>\n",
       "<tr><td></td><td>A</td></tr>\n",
       "<tr><td></td><td>B</td></tr>\n",
       "<tr><td></td><td>C</td></tr>\n",
       "<tr><td></td><td>C D</td></tr>\n",
       "<tr><td></td><td>D</td></tr>\n",
       "</TABLE>\n",
       "<hr>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.core.display import HTML\n",
    "HTML(filename='data/titanic.html')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Women and children first?\n",
    "\n",
    "1. Use the `groupby` method to calculate the proportion of passengers that survived by sex.\n",
    "2. Calculate the same proportion, but by class and sex.\n",
    "3. Create age categories: children (under 14 years), adolescents (14-20), adult (21-64), and senior(65+), and calculate survival proportions by age category, class and sex."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write your answer here"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}