{ "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": 1, "metadata": { "collapsed": false }, "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": 2, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from datetime import datetime" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "datetime.datetime(2014, 6, 18, 10, 40, 0, 248479)" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "now = datetime.now()\n", "now" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "18" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "now.day" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "2" ] }, "execution_count": 5, "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": 6, "metadata": { "collapsed": false }, "outputs": [], "source": [ "from datetime import date, time" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "datetime.time(3, 24)" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "time(3, 24)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": 11, "metadata": { "collapsed": false }, "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": 11, "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": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": [ "iVBORw0KGgoAAAANSUhEUgAAAYsAAAEACAYAAABCl1qQAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\n", "AAALEgAACxIB0t1+/AAAGs1JREFUeJzt3X2MneWZ3/HvL3Fg8+JiTJB5Z9yuU7BwReKCt8omO+HF\n", "JVXLi4TASIs8uzRSmRKCVm0Zp2rtf+oaS6sEVG2kLc6OiYoXWlJwdsGxIZxoV62ZhmWIE8f1WIqJ\n", "7eAh4A1suhXF4tc/zj32YTL2mfE8PvP4nN9HGs1zX+d+ztzPpcO5/NzXzEG2iYiIOJkPzfUCIiKi\n", "/lIsIiKirRSLiIhoK8UiIiLaSrGIiIi2UiwiIqKttsVC0hpJP5a0S9Ljks6WtFDSDkl7JW2XtGDS\n", "/DFJeyStbIkvL88xJunhlvjZkp4o8Z2SLq/+MiMiYjZOWiwk9QFfAj5jexnwYWAVMATssP0p4IUy\n", "RtJS4E5gKXAT8EeSVJ7uG8A9tpcASyTdVOL3AG+V+NeAhyq7uoiIqES7O4t3gPeAj0maB3wM+Dlw\n", "M7C5zNkM3FqObwG22H7P9n5gH7BC0oXAfNsjZd5jLee0PtdTwPWzuqKIiKjcSYuF7SPAHwI/o1kk\n", "fml7B7DI9niZNg4sKscXAQdbnuIgcPEU8UMlTvl+oPy8o8Dbkhae6gVFRET12m1D/T3gAaCP5hv+\n", "JyT9buscNz8vJJ8ZEhHRxea1efwfAv/D9lsAkr4N/CPgsKQLbB8uW0xvlPmHgEtbzr+E5h3FoXI8\n", "OT5xzmXAz8tW1znljuYDJKUgRUScAttqP+vk2vUs9gC/JemjpVF9A7Ab+A6wusxZDTxdjrcCqySd\n", "JWkxsAQYsX0YeEfSivI8dwPPtJwz8Vy302yYT8l2vir6Wrt27ZyvoVu+ksvks85fVTnpnYXtVyU9\n", "BvwAeB/4K+CPgfnAk5LuAfYDd5T5uyU9SbOgHAUGfXy1g8Aw8FHgWdvbSnwT8C1JY8BbNH/bKk6z\n", "/fv3z/USukZyWa3ks57abUNheyOwcVL4CM27jKnmrwfWTxF/GVg2RfxdSrGJiIh6yl9w96iBgYG5\n", "XkLXSC6rlXzWk6rc0zqdJPlMWWtERF1Iwh1ocEeXajQac72ErpFcViv5rKcUi4iIaCvbUBERXSzb\n", "UBER0TEpFj0q+8LVSS6rlXzWU4pFRES0lZ5FREQXS88iIiI6JsWiR2VfuDrJZbWSz3pKsYiIiLbS\n", "s4iI6GLpWURERMekWPSo7AtXJ7msVvJZTykWERHRVnoWERFdrKd7FpJo/q+8IyKiE87IYhGzl33h\n", "6iSX1Uo+66ltsZD09yW90vL1tqT7JS2UtEPSXknbJS1oOWeNpDFJeyStbIkvl7SrPPZwS/xsSU+U\n", "+E5Jl1d/qRERcapm1LOQ9CHgEHAt8GXgTdsbJT0InGt7SNJS4HHgGuBi4HlgiW1LGgHusz0i6Vng\n", "EdvbJA0CV9kelHQncJvtVZN+9rGexcQWVHoYEREnN1c9ixuAfbYPADcDm0t8M3BrOb4F2GL7Pdv7\n", "gX3ACkkXAvNtj5R5j7Wc0/pcTwHXz/RCIiLi9JlpsVgFbCnHi2yPl+NxYFE5vgg42HLOQZp3GJPj\n", "h0qc8v0AgO2jwNuSFs5wbTED2ReuTnJZreSznqZdLCSdBfwz4L9OfqzsD2VPKCKiS82bwdwvAi/b\n", "/kUZj0u6wPbhssX0RokfAi5tOe8SmncUh8rx5PjEOZcBP5c0DzjH9pHJCxgYGKCvr+/YuNFo0N/f\n", "f+wYyHia44lYXdZzJo/7+/trtZ4zfZx8zm7caDQYHh4G+MD75WxNu8Et6U+B52xvLuONwFu2H5I0\n", "BCyY1OC+luMN7t8sDe6XgPuBEeDP+WCDe5nteyWtAm5NgzsiYvY62uCW9HGaze1vt4Q3ADdK2gtc\n", "V8bY3g08CewGngMGW/70ehB4FBij2SjfVuKbgPMkjQEPAEOzuahob+JfIjF7yWW1ks96mtY2lO3/\n", "A3xyUuwIzQIy1fz1wPop4i8Dy6aIvwvcMZ21RERE552Rnw2VbaiIiOnp6c+GioiIzkqx6FHZF65O\n", "clmt5LOeUiwiIqKt9CwiIrpYehYREdExKRY9KvvC1Ukuq5V81lOKRUREtJWeRUREF0vPIiIiOibF\n", "okdlX7g6yWW1ks96SrGIiIi20rOIiOhi6VlERETHpFj0qOwLVye5rFbyWU8pFhER0VZ6FhERXSw9\n", "i4iI6JgUix6VfeHqJJfVSj7raVrFQtICSf9N0k8k7Za0QtJCSTsk7ZW0XdKClvlrJI1J2iNpZUt8\n", "uaRd5bGHW+JnS3qixHdKurzay4yIiNmYVs9C0mbg+7a/KWke8HHg3wJv2t4o6UHgXNtDkpYCjwPX\n", "ABcDzwNLbFvSCHCf7RFJzwKP2N4maRC4yvagpDuB22yvmrSG9CwiImaoYz0LSecAn7P9TQDbR22/\n", "DdwMbC7TNgO3luNbgC2237O9H9gHrJB0ITDf9kiZ91jLOa3P9RRw/ayuKiIiKjWdbajFwC8k/Ymk\n", "v5L0nyV9HFhke7zMGQcWleOLgIMt5x+keYcxOX6oxCnfD0CzGAFvS1p4KhcU05N94eokl9VKPutp\n", "3jTnfIbm9tH/kvR1YKh1QtliOu17QgMDA/T19R0bNxoN+vv7jx0DGU9zPDo6Wqv1ZJxxxtWMG40G\n", "w8PDAB94v5yttj0LSRcA/9P24jL+bWAN8HeBL9g+XLaYXrR9haQhANsbyvxtwFrgtTLnyhK/C/i8\n", "7XvLnHW2d5aeyOu2z5+0jvQsIiJmqGM9C9uHgQOSPlVCNwA/Br4DrC6x1cDT5XgrsErSWZIWA0uA\n", "kfI875TfpBJwN/BMyzkTz3U78MLsLisiIqo03b+z+DLwXyS9CvwD4D8AG4AbJe0FritjbO8GngR2\n", "A88Bgz5+CzAIPAqMAftsbyvxTcB5ksaAB5i0zRXVm7htjdlLLquVfNbTdHoW2H6V5q/CTnbDCeav\n", "B9ZPEX8ZWDZF/F3gjumsJSIiOi+fDRUR0cXy2VAREdExKRY9KvvC1Ukuq5V81lOKRUREtJWeRURE\n", "F0vPIiIiOibFokdlX7g6yWW1ks96SrGIiIi20rOIiOhi6VlERETHpFj0qOwLVye5rFbyWU8pFhER\n", "0VZ6FhERXSw9i4iI6JgUix6VfeHqJJfVSj7rKcUiIiLaSs8iIqKLpWcREREdk2LRo7IvXJ3kslrJ\n", "Zz1Nq1hI2i/ph5JekTRSYgsl7ZC0V9J2SQta5q+RNCZpj6SVLfHlknaVxx5uiZ8t6YkS3ynp8iov\n", "MiIiZmdaPQtJPwWW2z7SEtsIvGl7o6QHgXNtD0laCjwOXANcDDwPLLHtUmjusz0i6VngEdvbJA0C\n", "V9kelHQncJvtVZPWkJ5FRMQMzUXPYvIPuxnYXI43A7eW41uALbbfs70f2AeskHQhMN/2SJn3WMs5\n", "rc/1FHD9DNYVERGn2XSLhYHnJf1A0pdKbJHt8XI8DiwqxxcBB1vOPUjzDmNy/FCJU74fALB9FHhb\n", "0sKZXEjMTPaFq5NcViv5rKd505z3WduvSzof2CFpT+uDZYvptO8JDQwM0NfXd2zcaDTo7+8/dgxk\n", "PM3x6OhordaTccYZVzNuNBoMDw8DfOD9crZm/HcWktYCvwK+BPTbPly2mF60fYWkIQDbG8r8bcBa\n", "4LUy58oSvwv4vO17y5x1tndKmge8bvv8ST83PYuIiBnqWM9C0sckzS/HHwdWAruArcDqMm018HQ5\n", "3gqsknSWpMXAEmDE9mHgHUkr1Hy3vxt4puWciee6HXhhthcWERHVmU7PYhHwF5JGgZeAP7O9HdgA\n", "3ChpL3BdGWN7N/AksBt4Dhj08VuAQeBRYAzYZ3tbiW8CzpM0BjwADFVxcXFiE7etMXvJZbWSz3pq\n", "27Ow/VPg6iniR4AbTnDOemD9FPGXgWVTxN8F7pjGeiMiYg7ks6EiIrpYPhsqIiI6JsWiR2VfuDrJ\n", "ZbWSz3pKsYiIiLbSs4iI6GLpWURERMekWPSo7AtXJ7msVvJZTykWERHRVnoWERFdLD2LiIjomBSL\n", "HpV94eokl9VKPuspxSIiItpKzyIiooulZxERER2TYtGjsi9cneSyWslnPaVYREREW+lZRER0sfQs\n", "IiKiY1IselT2hauTXFYr+aynaRULSR+W9Iqk75TxQkk7JO2VtF3Sgpa5aySNSdojaWVLfLmkXeWx\n", "h1viZ0t6osR3Srq8yguMiIjZm1bPQtIfAMuB+bZvlrQReNP2RkkPAufaHpK0FHgcuAa4GHgeWGLb\n", "kkaA+2yPSHoWeMT2NkmDwFW2ByXdCdxme9UUa0jPIiJihjrWs5B0CfBPgEeBiR94M7C5HG8Gbi3H\n", "twBbbL9nez+wD1gh6UKahWakzHus5ZzW53oKuP6UryYiIk6L6WxDfQ3418D7LbFFtsfL8TiwqBxf\n", "BBxsmXeQ5h3G5PihEqd8PwBg+yjwtqSFM7iGOAXZF65Oclmt5LOe5p3sQUn/FHjD9iuS+qeaU7aY\n", "OrIfNDAwQF9f37Fxo9Ggv7//2DGQ8TTHo6OjtVpPxhlnXM240WgwPDwM8IH3y9k6ac9C0nrgbuAo\n", "8BvA3wG+TbMn0W/7cNlietH2FZKGAGxvKOdvA9YCr5U5V5b4XcDnbd9b5qyzvVPSPOB12+dPsZb0\n", "LCIiZqgjPQvbX7V9qe3FwCrge7bvBrYCq8u01cDT5XgrsErSWZIWA0uAEduHgXckrVDznf5u4JmW\n", "cyae63bghdleVEREVGumf2cx8U/5DcCNkvYC15UxtncDTwK7geeAQR//5/8gzSb5GLDP9rYS3wSc\n", "J2kMeAAYOsVriRmYuG2N2Usuq5V81tNJexatbH8f+H45PgLccIJ564H1U8RfBpZNEX8XuGO664iI\n", "iM7LZ0NFRHSxfDZURER0TIpFj8q+cHWSy2oln/WUYhEREW2lZxER0cXSs4iIiI5JsehR2ReuTnJZ\n", "reSznlIsIiKirfQsIiK6WHoWERHRMSkWPSr7wtVJLquVfNZTikVERLSVnkVERBdLzyIiIjomxaJH\n", "ZV+4OslltZLPekqxiIiIttKziIjoYulZREREx6RY9KjsC1cnuaxW8llPJy0Wkn5D0kuSRiXtlvQf\n", "S3yhpB2S9kraLmlByzlrJI1J2iNpZUt8uaRd5bGHW+JnS3qixHdKuvx0XGhERJy6tj0LSR+z/beS\n", "5gF/Cfwr4GbgTdsbJT0InGt7SNJS4HHgGuBi4HlgiW1LGgHusz0i6VngEdvbJA0CV9kelHQncJvt\n", "VVOsIz2LiIgZ6ljPwvbflsOzgA8Df02zWGwu8c3AreX4FmCL7fds7wf2ASskXQjMtz1S5j3Wck7r\n", "cz0FXH/KVxMREadF22Ih6UOSRoFx4EXbPwYW2R4vU8aBReX4IuBgy+kHad5hTI4fKnHK9wMAto8C\n", "b0taeGqXE9OVfeHqJJfVSj7raV67CbbfB66WdA7wXUlfmPS4JXVkP2hgYIC+vr5j40ajQX9//7Fj\n", "IONpjkdHR2u1nowzzriacaPRYHh4GOAD75ezNaO/s5D074D/C/xzoN/24bLF9KLtKyQNAdjeUOZv\n", "A9YCr5U5V5b4XcDnbd9b5qyzvbP0RV63ff4UPzs9i4iIGepIz0LSJyd+00nSR4EbgVeArcDqMm01\n", "8HQ53gqsknSWpMXAEmDE9mHgHUkr1Hynvxt4puWciee6HXhhthcVERHVatezuBD4XulZvAR8x/YL\n", "wAbgRkl7gevKGNu7gSeB3cBzwKCP//N/EHgUGAP22d5W4puA8ySNAQ8AQ1VdXJzYxG1rzF5yWa3k\n", "s55O2rOwvQv4zBTxI8ANJzhnPbB+ivjLwLIp4u8Cd0xzvRERMQfy2VAREV0snw0VEREdk2LRo7Iv\n", "XJ3kslrJZz2lWERERFvpWUREdLH0LCIiomNSLHpU9oWrk1xWK/mspxSLiIhoKz2LiIgulp5FRER0\n", "TIpFj8q+cHWSy2oln/WUYhEREW2lZxER0cXSs4iIiI5JsehR2ReuTnJZreSznlIsIiKirTO6ZwHp\n", "W0REnEx6FhER0TEpFj0q+8LVSS6rlXzWU9tiIelSSS9K+rGkH0m6v8QXStohaa+k7ZIWtJyzRtKY\n", "pD2SVrbEl0vaVR57uCV+tqQnSnynpMurvtCIiDh1bXsWki4ALrA9KukTwMvArcDvAW/a3ijpQeBc\n", "20OSlgKPA9cAFwPPA0tsW9IIcJ/tEUnPAo/Y3iZpELjK9qCkO4HbbK+atI70LCIiZqhjPQvbh22P\n", "luNfAT+hWQRuBjaXaZtpFhCAW4Attt+zvR/YB6yQdCEw3/ZImfdYyzmtz/UUcP1sLioiIqo1o56F\n", "pD7g08BLwCLb4+WhcWBROb4IONhy2kGaxWVy/FCJU74fALB9FHhb0sKZrC1mJvvC1Ukuq5V81tO8\n", "6U4sW1BPAV+x/TeTtoIs6bTvBw0MDNDX1zflYxMvsP7+/oynMR4dHa3VejLOOONqxo1Gg+HhYYAT\n", "vl+eimn9nYWkjwB/Bjxn++sltgfot324bDG9aPsKSUMAtjeUeduAtcBrZc6VJX4X8Hnb95Y562zv\n", "lDQPeN32+ZPWkJ5FRMQMdaxnoeY78yZg90ShKLYCq8vxauDplvgqSWdJWgwsAUZsHwbekbSiPOfd\n", "wDNTPNftwAuzuKaIiKjYdHoWnwV+F/iCpFfK103ABuBGSXuB68oY27uBJ4HdwHPAoI//838QeBQY\n", "A/bZ3lbim4DzJI0BDwBDlVxdnNDEbWvMXnJZreSzntr2LGz/JScuKjec4Jz1wPop4i8Dy6aIvwvc\n", "0W4tERExN/LZUBERXSyfDRURER2TYtGjsi9cneSyWslnPaVYREREW+lZRER0sfQsIiKiY1IselT2\n", "hauTXFYr+aynFIuIiGjrjO9ZQPoWEREnkp5FRER0TIpFj8q+cHWSy2oln/WUYhEREW2lZxER0cXS\n", "s4iIiI5JsehR2ReuTnJZreSznlIsIiKirfQsIiK6WHoWERHRMW2LhaRvShqXtKsltlDSDkl7JW2X\n", "tKDlsTWSxiTtkbSyJb5c0q7y2MMt8bMlPVHiOyVd3mY9M7/K+DXZF65Oclmt5LOepnNn8SfATZNi\n", "Q8AO258CXihjJC0F7gSWlnP+SMff3b8B3GN7CbBE0sRz3gO8VeJfAx6axfVERMRpMK2ehaQ+4Du2\n", "l5XxHuB3bI9LugBo2L5C0hrgfdsPlXnbgHXAa8D3bF9Z4quAftv/osxZa/slSfOA122fP8UabHvK\n", "O4v0LCIipjbXPYtFtsfL8TiwqBxfBBxsmXcQuHiK+KESp3w/AGD7KPC2pIWnuK6IiDgNZt3gLr+i\n", "lH/an2GyL1yd5LJayWc9zTvF88YlXWD7sKQLgTdK/BBwacu8S2jeURwqx5PjE+dcBvy8bEOdY/vI\n", "VD90YGDghAuaeIH19/dnPI3x6OhordaTccYZVzNuNBoMDw8D0NfXR1VOtWexkWZT+iFJQ8AC20Ol\n", "wf04cC3N7aXngd+0bUkvAfcDI8CfA4/Y3iZpEFhm+97Sy7jV9qop1pCeRUTEDFXVs2hbLCRtAX4H\n", "+CTN/sS/B54BnqR5R7AfuMP2L8v8rwK/DxwFvmL7uyW+HBgGPgo8a/v+Ej8b+BbwaeAtYJXt/VOs\n", "I8UiImKGOlYs6iLFolqNRuPYLWzMTnJZreSzWnP921AREdFDzqg7i5/97Gdcdtllv/bYmXINERGd\n", "1pN3FkeOTPlLUhERcZqdUcUiqjPxq3Yxe8lltZLPejqjisXVV18910uIiOhJZ1TP4kSPnSnXEBHR\n", "aT3Zs4iIiLmRYtGjsi9cneSyWslnPaVYREREW+lZRER0sfQsIiKiY1IselT2hauTXFYr+aynrigW\n", "U324YEREVKcrehYAa9euZd26dR1aTUTEmaEnP6K83Zwz5VoiIjolDe6YlewLVye5rFbyWU8pFhER\n", "0Va2oSIiuli2oSIiomNqUywk3SRpj6QxSQ/O9Xq6XfaFq5NcViv5rKdaFAtJHwb+E3ATsBS4S9KV\n", "c7uq7jY6OjrXS+gayWW1ks96qkWxAK4F9tneb/s94E+BW2b6JJLyB3rT9Mtf/nKul9A1kstqJZ/1\n", "VJdicTFwoGV8sMROSQpGRES15s31AorKf43pZAUjvzUF+/fvn+sldI3kslrJZz3V4ldnJf0WsM72\n", "TWW8Bnjf9kMtc+Z+oRERZ6Cu+bgPSfOA/w1cD/wcGAHusv2TOV1YREQANdmGsn1U0n3Ad4EPA5tS\n", "KCIi6qMWdxYREVFvdfltqBPKH+udGkn7Jf1Q0iuSRkpsoaQdkvZK2i5pQcv8NSXHeyStnLuVzz1J\n", "35Q0LmlXS2zGuZO0XNKu8tjDnb6OujhBPtdJOlhen69I+mLLY8nnSUi6VNKLkn4s6UeS7i/x0/sa\n", "tV3bL5pbUvuAPuAjwChw5Vyv60z4An4KLJwU2wj8m3L8ILChHC8tuf1IyfU+4ENzfQ1zmLvPAZ8G\n", "dp1i7ibu2EeAa8vxs8BNc31tNcrnWuAPppibfLbP5wXA1eX4EzT7vVee7tdo3e8sKvljvR42+Tcg\n", "bgY2l+PNwK3l+BZgi+33bO+n+WK6tiMrrCHbfwH89aTwTHK3QtKFwHzbI2XeYy3n9JQT5BN+/fUJ\n", "yWdbtg/bHi3HvwJ+QvPv0k7ra7TuxaLSP9brMQael/QDSV8qsUW2x8vxOLCoHF9EM7cTkudfN9Pc\n", "TY4fIjmd7MuSXpW0qWXLJPmcAUl9NO/aXuI0v0brXizSfT91n7X9aeCLwL+U9LnWB9287zxZfpP7\n", "E5hG7qK9bwCLgauB14E/nNvlnHkkfQJ4CviK7b9pfex0vEbrXiwOAZe2jC/lg5UwTsD26+X7L4D/\n", "TnNbaVzSBQDlFvSNMn1yni8psThuJrk7WOKXTIonp4XtN1wAj3J82zP5nAZJH6FZKL5l++kSPq2v\n", "0boXix8ASyT1SToLuBPYOsdrqj1JH5M0vxx/HFgJ7KKZu9Vl2mpg4kW2FVgl6SxJi4ElNBtfcdyM\n", "cmf7MPCOpBVqfvbM3S3n9LzyZjbhNpqvT0g+2yrXvwnYbfvrLQ+d3tfoXHf2p9H5/yLNbv8+YM1c\n", "r+dM+KJ5ez9avn40kTdgIfA8sBfYDixoOeerJcd7gH8819cwx/nbQvOTBP4fzZ7Z751K7oDlNN8E\n", "9wGPzPV11Sifv0+zmfpD4NXyBrUo+Zx2Pn8beL/89/1K+brpdL9G80d5ERHRVt23oSIiogZSLCIi\n", "oq0Ui4iIaCvFIiIi2kqxiIiItlIsIiKirRSLiIhoK8UiIiLa+v8VMFz1HZqOwAAAAABJRU5ErkJg\n", "gg==\n" ], "text/plain": [ "" ] }, "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": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": [ "iVBORw0KGgoAAAANSUhEUgAAAYEAAAEACAYAAABVtcpZAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\n", "AAALEgAACxIB0t1+/AAAGGBJREFUeJzt3W2MXGd5xvH/lZhAAilLWsl5hY2KU+KKyuAIR7Rptm2I\n", "DCpJPlR5UaEY3EpVaBP3FZsPeP2FAlKV0FZJ1YawDg0pBgoFEUxM8JailjhAXALGJUFsG5vaQQSH\n", "vkhVTO5+mGfj6XpmvDt7dp9z71w/aeRzzpzZufbx7Ln3PPeZWUUEZmY2mk6rHcDMzOpxETAzG2Eu\n", "AmZmI8xFwMxshLkImJmNMBcBM7MRNrAISLpb0lFJj3ZtO0fSHknflvSApLGu+7ZJekzSQUlXd21f\n", "L+nRct/7u7Y/X9JHyvYvS3pZ09+gmZn1d6ozgQ8CG+ds2wrsiYhLgAfLOpLWAjcAa8tj7pCk8pg7\n", "gc0RsQZYI2n2a24GflC23wa8d5Hfj5mZLcDAIhAR/wj8cM7ma4CdZXkncF1Zvha4LyKeiYgZ4HFg\n", "g6TzgLMjYl/Z756ux3R/rY8DvzLk92FmZkMYpiewOiKOluWjwOqyfD5wqGu/Q8AFPbYfLtsp/z4B\n", "EBHHgaclnTNEJjMzG8KiGsPR+cwJf+6EmVlSq4Z4zFFJ50bEkTLV82TZfhi4qGu/C+mcARwuy3O3\n", "zz7mpcD3JK0CXhwRT819QkkuNGZmQ4gIDbp/mDOBTwFvKctvAT7Ztf1GSWdIuhhYA+yLiCPAjyRt\n", "KI3iNwN/3+Nr/RqdRnO/b6T1t+3bt1fP4JzOmTWjczZ/m4+BZwKS7gOuBH5K0hPAu4D3ALskbQZm\n", "gOvLQfqApF3AAeA4cHOcSHEzMAWcCdwfEbvL9g8AH5L0GPAD4MZ5pW6pmZmZ2hHmxTmblSFnhozg\n", "nDUMLAIRcVOfu67qs/+7gXf32P5V4JU9tv8vpYiYmdny8zuGG7Rp06baEebFOZuVIWeGjOCcNWi+\n", "80Y1SYoMOc3M2kQSsQSNYetjenq6doR5cc5mZciZISM4Zw0uAmZmI8zTQWZmK5Sng8zMbCAXgQZl\n", "mSd0zmZlyJkhIzhnDS4CZmYjzD0BM7MVyj0BS0sSJ/4mkZktFReBBmWZJ8ySM4sM45khIzhnDS4C\n", "ZmYjzD0Ba6XZqSD/v5sNzz0BMzMbyEWgQVnmCbPkzCLDeGbICM5Zg4uAmdkIc0/AWsk9AbPFc0/A\n", "zMwGchFoUJZ5wiw5s8gwnhkygnPW4CJgZjbC3BOwVnJPwGzx3BMwM7OBXAQalGWeMEvOLDKMZ4aM\n", "4Jw1uAiYmY0w9wSsldwTMFs89wTMzGwgF4EGZZknzJIziwzjmSEjOGcNLgJmZiPMPQFrJfcEzBbP\n", "PQEzMxvIRaBBWeYJs+Ts1uY/PJ9hPDNkBOeswUXAzGyEuSdgrTS3J+AegdnCuSdgZmYDuQg0KMs8\n", "YZacWWQYzwwZwTlrcBEwMxthQ/cEJG0D3gQ8CzwKvBV4IfAR4GXADHB9RBzr2v9twI+BWyLigbJ9\n", "PTAFvAC4PyJu7fFc7gmMGPcEzBZvyXoCksaB3wJeHRGvBE4HbgS2Ansi4hLgwbKOpLXADcBaYCNw\n", "h05c73cnsDki1gBrJG0cJpOZmS3csNNBPwKeAc6StAo4C/gecA2ws+yzE7iuLF8L3BcRz0TEDPA4\n", "sEHSecDZEbGv7HdP12PSyTJPmCVnFhnGM0NGcM4ahioCEfEU8KfAv9M5+B+LiD3A6og4WnY7Cqwu\n", "y+cDh7q+xCHggh7bD5ftZma2DFYN8yBJPw1sAcaBp4GPSnpT9z4REZIam8DdtGkT4+PjAIyNjbFu\n", "3TomJiaAE1XZ6/Nbn93Wljz91ruz9lqvnS/7eNbO02t9YmKiVXkGrc9qS57ZsZuamgJ47nh5KkM1\n", "hiXdALwuIn6zrL8ZuBz4ZeCXIuJImerZGxGvkLQVICLeU/bfDWwH/q3sc2nZfhNwZUT89pznc2N4\n", "xLgxbLZ4S/lmsYPA5ZLOLA3eq4ADwKeBt5R93gJ8six/CrhR0hmSLgbWAPsi4gjwI0kbytd5c9dj\n", "0pn7G0JbZcmZRYbxzJARnLOGoaaDIuJfJN0DfIXOJaJfA/4KOBvYJWkz5RLRsv8BSbvoFIrjwM1d\n", "v9rfTOcS0TPpXCK6e+jvxszMFsSfHWSt5Okgs8XzZweZmdlALgINyjJPmCVnFhnGM0NGcM4aXATM\n", "zEaYewLWSu4JmC2eewJmZjaQi0CDsswTZsmZRYbxzJARnLMGFwFrnbb+QXmzlcg9AWud7iLgnoDZ\n", "8NwTMDOzgVwEGpRlnjBLziwyjGeGjOCcNbgImJmNMPcErHXcEzBrhnsCZmY2kItAg7LME2bJmUWG\n", "8cyQEZyzBhcBM7MR5p6AtY57AmbNcE/AzMwGchFoUJZ5wiw5s8gwnhkygnPW4CJgZjbC3BOw1uj1\n", "wXHuCZgNzz0BMzMbyEWgQVnmCbPkzCLDeGbICM5Zg4uAmdkIc0/AWsM9AbNmuSdgZmYDuQg0KMs8\n", "YZacWWQYzwwZwTlrcBEwMxth7glYa7gnYNYs9wTMzGwgF4EGZZknzJIziwzjmSEjOGcNLgK2IkxO\n", "TtaOYJaSewLWGovpCZS5z6ULZ5bQfHoCq5YrjNkwehUGM2uOp4MalGWeMEvOLDKMZ4aM4Jw1uAiY\n", "mY2woXsCksaAu4CfBQJ4K/AY8BHgZcAMcH1EHCv7bwPeBvwYuCUiHijb1wNTwAuA+yPi1h7P5Z7A\n", "CJjP1I97Ambzt9TvE3g/nYP2pcDPAQeBrcCeiLgEeLCsI2ktcAOwFtgI3KETP/F3ApsjYg2wRtLG\n", "RWQyM7MFGKoISHoxcEVE3A0QEccj4mngGmBn2W0ncF1Zvha4LyKeiYgZ4HFgg6TzgLMjYl/Z756u\n", "x6STZZ4wS84sMoxnhozgnDUMeyZwMfB9SR+U9DVJfy3phcDqiDha9jkKrC7L5wOHuh5/CLigx/bD\n", "ZbuZmS2DoXoCki4D/hl4bUQ8LOl24D+B34mIl3Tt91REnCPpz4EvR8S9ZftdwGfp9A3eExGvK9uv\n", "AP44It445/ncExgB7gmYNWsp3ydwCDgUEQ+X9Y8B24Ajks6NiCNlqufJcv9h4KKux19Yvsbhsty9\n", "/XCvJ9y0aRPj4+MAjI2NsW7dOiYmJoATp2Zez70+X6d6fFu+H697fbnXp6enmZqaAnjueHlKETHU\n", "DfgicElZngTeV27vKNu20vktHzoN4f3AGXSmkr7DibOQh4ANgID7gY09nisy2Lt3b+0I89LWnHSu\n", "Mht4G/TYWto6nt0yZIxwzqaVn4uBx/LFvGP4d4F7JZ1RDupvBU4HdknaTLlEtBzBD0jaBRwAjgM3\n", "l4AAN9O5RPRMOlcb7V5EJjMzWwB/dpC1hnsCZs3y3xMwM7OBXAQatNAGZy1ZcmaRYTwzZATnrMFF\n", "wMxshLknYK3hnoBZs9wTMDOzgVwEGpRlnjBLziwyjGeGjOCcNbgIWCr+W8JmzXJPwFpjvn9Kstdr\n", "wT0Bs5O5J2BmZgO5CDQoyzxhlpxZZBjPDBnBOWtwETAzG2HuCVhruCdg1iz3BGykzLeImNkJLgIN\n", "yjJPmCVnFhnGM0NGcM4aXASsFfxbvFkd7glYKyykCPTrCfS7z2xUuSdgZmYDuQg0KMs8YZacWWQY\n", "zwwZwTlrcBEwMxth7glYK7gnYNY89wTMzGwgF4EGZZknzJIziwzjmSEjOGcNLgJmZiPMPQFrBfcE\n", "zJrnnoCZmQ3kItCgLPOEWXJmkWE8M2QE56zBRcDMbIS5J2Ct4J6AWfPcEzAzs4FcBBqUZZ4wS84s\n", "MoxnhozgnDW4CJiZjTD3BKwV3BMwa557AmZmNpCLQIOyzBNmyZlFhvHMkBGcswYXATOzEeaegLWC\n", "ewJmzVvynoCk0yU9IunTZf0cSXskfVvSA5LGuvbdJukxSQclXd21fb2kR8t9719MHjMzW5jFTgfd\n", "ChwAZn/92grsiYhLgAfLOpLWAjcAa4GNwB068avfncDmiFgDrJG0cZGZqskyT5glZxYZxjNDRnDO\n", "GoYuApIuBN4A3AXMHtCvAXaW5Z3AdWX5WuC+iHgmImaAx4ENks4Dzo6IfWW/e7oeYzYv3VNJkhY0\n", "tWQ26obuCUj6KPBu4CeAP4yIN0r6YUS8pNwv4KmIeImkPwe+HBH3lvvuAj4LzADviYjXle1XAH8c\n", "EW+c81zuCaxwi+kJ9HqsXy9mS9gTkPSrwJMR8QgnzgL+n3LU9k+imVmLrRryca8FrpH0BuAFwE9I\n", "+hBwVNK5EXGkTPU8WfY/DFzU9fgLgUNl+4Vzth/u9YSbNm1ifHwcgLGxMdatW8fExARwYn6u9vrs\n", "trbk6bd+++23t3b85mM+VwJNT0+P/Hh2r+/fv58tW7a0Jk+/9bk/S7Xz9Ftv63hOT08zNTUF8Nzx\n", "8pQiYlE34Erg02X5fcA7yvJWOlM90GkI7wfOAC4GvsOJqaiHgA10zijuBzb2eI7IYO/evbUjzEsb\n", "c9I5a1zQbdBjl1Mbx3OuDBkjnLNp5Wdh4DF80e8TkHQl8AcRcY2kc4BdwEvpzPdfHxHHyn7vBN4G\n", "HAdujYjPle3rgSngTOD+iLilx3PEYnNauw3TzJ19TbgnYNbbfHoCfrOYtYKLgFnz/AFyy2yhc9u1\n", "ZMmZRYbxzJARnLMGFwEzsxHm6SBrBU8HmTXP00FmZjaQi0CDsswTZsmZRYbxzJARnLMGFwEzsxHm\n", "noC1gnsCZs1zT8DMzAZyEWhQlnnCLDmzyDCeGTKCc9bgImCpTU5O1o5glpp7AtYKTf8hGL9ezNwT\n", "MDOzU3ARaFCWecIsObPIMJ4ZMoJz1uAiYGY2wtwTsFZwT8Csee4JmJnZQC4CDcoyT5glZxYZxjND\n", "RnDOGlwEzMxGmHsC1gruCZg1zz0BMzMbyEWgQVnmCbPkzCLDeGbICM5Zg4uAmdkIc0/AWmEpegKz\n", "X9OvHRtV7gmYmdlALgINyjJPmCVnFhnGM0NGcM4aXASsuqangsxs/twTsOqWowj49WOjyD0BMzMb\n", "yEWgQVnmCbPkzCLDeGbICM5Zg4uAmdkIc0/AqnNPwGxpuCdgVvgKJLPeXAQalGWeMEvOLDKMZ4aM\n", "4Jw1uAiYmY0w9wSsuuWaqvFryEaNewJmZjbQUEVA0kWS9kr6pqRvSLqlbD9H0h5J35b0gKSxrsds\n", "k/SYpIOSru7avl7So+W+9y/+W6onyzxhlpxNk7QkZx0ZxjNDRnDOGoY9E3gG+L2I+FngcuDtki4F\n", "tgJ7IuIS4MGyjqS1wA3AWmAjcIdO/DTeCWyOiDXAGkkbh/5uLB1ftWNWVyM9AUmfBP6i3K6MiKOS\n", "zgWmI+IVkrYBz0bEe8v+u4FJ4N+AL0TEpWX7jcBERPz2nK/vnsAKVaMI+LVko2JZegKSxoFXAQ8B\n", "qyPiaLnrKLC6LJ8PHOp62CHggh7bD5ftZma2DFYt5sGSXgR8HLg1Iv6z+7e6iAhJjf3KtWnTJsbH\n", "xwEYGxtj3bp1TExMACfm52qvz25rS55+67fffnurxm+5rfTx7LW+f/9+tmzZ0po8/dbn/izVztNv\n", "va3jOT09zdTUFMBzx8tTioihbsDzgM8BW7q2HQTOLcvnAQfL8lZga9d+u4ENwLnAt7q23wT8ZY/n\n", "igz27t1bO8K8tCknsOy3prVpPPvJkDHCOZtWXu8Dj+VD9QRKU3cn8IOI+L2u7e8r294raSswFhFb\n", "S2P4w8Br6Ez3fB54eUSEpIeAW4B9wGeAP4uI3XOeL4bJae3nnoDZ0plPT2DYIvALwBeBr9P57Qpg\n", "G50D+S7gpcAMcH1EHCuPeSfwNuA4nemjz5Xt64Ep4Ezg/oi4pcfzuQisULWuDvLryUbBkjWGI+JL\n", "EXFaRKyLiFeV2+6IeCoiroqISyLi6tkCUB7z7oh4eUS8YrYAlO1fjYhXlvtOKgCZdM9ntlmWnEtp\n", "cnKysa+VYTwzZATnrMHvGLaRtGPHjtoRzFrBnx1kVdV8s5hfU7bS+bODrNX8bmGz+lwEGpRlnjBL\n", "ziwyjGeGjOCcNbgImJmNMPcErJra00F+TdlK556AmZkN5CLQoCzzhFlyZpFhPDNkBOeswUXAqqg9\n", "FWRmHe4JWBVtKAJ+TdlK556AmZkN5CLQoCzzhFlyZpFhPDNkBOeswUXAll2TH95mZovjnoAtuzb0\n", "A2b5dWUrmXsCZmY2kItAg7LME2bJ2Tb9prEyjGeGjOCcNbgI2LLJ3AuYnJz03yCwFck9AVs2ZX4y\n", "ZU9gNrNfh5aJewJmZjaQi0CDsswTZsm5HCQt+swkw3hmyAjOWYOLgJnZCHNPwJbU5OTkcw3hNvYE\n", "ug16jbknYBnNpyfgImBLavbA373sImC2PNwYXmZZ5glr5mxrAYD+2U6VOcP/e4aM4Jw1uAiYdZlt\n", "FGd+T4PZQng6yBrTa8pk7nRQJr1y+3VomXg6yFojWwEwGxUuAg3KMk/YdM5e19p3b8tcAOaTPcP/\n", "e4aM4Jw1uAhY4zIf9Lv1+j5WyvdmNss9AVu0UTsw+rVoWbgnYEtu1AqA2UqTsgjMnYNuy+V8WeYJ\n", "55tz7rx+95g38Zk7K0WG//cMGcE5a0hZBObq/px3H5z6G1Qsex3k+y17fM1WjpQ9gbnXo/e6Fr37\n", "4wki4v99hs2o6h6nWbPj4gP7wmT4uTFbUZ8d9PDDD3PZZZfNrgMLKwLd+/Q78GUYi8XoVQTa/nk+\n", "bdbr9eLPGLI2SdMYlrRR0kFJj0l6R699vvvd7zb2fIP+TODs2UK/s4ZB18Q3eSA9VY6Ffq3ubNPT\n", "0yN/VtSEuf/v/abQ2iDLHLZzVhARVW/A6cDjwDjwPGA/cOmcfWLXrl0xC4hO9BPrve6buzx3/9n7\n", "e+3Xb5/Z2/bt2/ve171PRMT27dufWx72a/mW+1bbbbfdVjvCvDhns8prb+AxuA1nAq8BHo+ImYh4\n", "Bvhb4NrKmU5pPn90fHafHTt2sGPHjr6/HfoPmK98vc4a5r4eZs/YejXiF3tmcezYsUU9frk45/Jb\n", "VTsAcAHwRNf6IWBDpSxmS24+00anerdyuOdgDWlDEfCr2WyBhjkzGHTGGXMupBg2z2KL08zMzJJ9\n", "7Sb1yplV9auDJF0OTEbExrK+DXg2It7btU97/vfNzBKJtl8iKmkV8K/ArwDfA/YBN0XEt6oGMzMb\n", "AdWngyLiuKTfAT5H50qhD7gAmJktj+pnAmZmVk8bLhEdaD5vJKtN0t2Sjkp6tHaWQSRdJGmvpG9K\n", "+oakW2pnmkvSCyQ9JGm/pAOS/qR2pkEknS7pEUmfrp2lH0kzkr5ecu6rnacfSWOSPibpW+X//vLa\n", "meaS9DNlHGdvT7fx5wg6/dXys/6opA9Len7P/dp8JiDpdDr9gquAw8DDtLBfIOkK4L+AeyLilbXz\n", "9CPpXODciNgv6UXAV4HrWjieZ0XE/5R+0ZeAP4yIL9XO1Yuk3wfWA2dHxDW18/Qi6bvA+oh4qnaW\n", "QSTtBP4hIu4u//cvjIina+fqR9JpdI5Lr4mIJ061/3KSNA58gc4bb/9X0keA+yNi59x9234mkOKN\n", "ZBHxj8APa+c4lYg4EhH7y/J/Ad8Czq+b6mQR8T9l8Qw6faJWHrwkXQi8AbgLaNfnRJys1fkkvRi4\n", "IiLuhk6vsM0FoLgK+E7bCkDxI+AZ4KxSUM+iU7BO0vYi0OuNZBdUyrKilN8UXgU8VDfJySSdJmk/\n", "cBTYGxEHamfq4zbgj4Bnawc5hQA+L+krkn6rdpg+Lga+L+mDkr4m6a8lnVU71CncCHy4doheylnf\n", "nwL/Tueqy2MR8fle+7a9CLR3riqxMhX0MeDWckbQKhHxbESsAy4EflHSROVIJ5H0q8CTEfEILf8t\n", "G/j5iHgV8Hrg7WX6sm1WAa8G7oiIVwP/DWytG6k/SWcAbwQ+WjtLL5J+GthC5zPZzgdeJOnXe+3b\n", "9iJwGLioa/0iOmcDNiRJzwM+DvxNRHyydp5BynTAZ4DLamfp4bXANWW+/T7glyXdUzlTTxHxH+Xf\n", "7wOfoDPN2jaHgEMR8XBZ/xidotBWrwe+Wsa0jS4D/ikifhARx4G/o/OaPUnbi8BXgDWSxkvlvQH4\n", "VOVMaanz/vsPAAci4vbaeXqR9FOSxsrymcDrgEfqpjpZRLwzIi6KiIvpTAt8ISJ+o3auuSSdJens\n", "svxC4GqgdVexRcQR4AlJl5RNVwHfrBjpVG6iU/zb6iBwuaQzy8/9VUDPadXqbxYbJMsbySTdB1wJ\n", "/KSkJ4B3RcQHK8fq5eeBNwFflzR7YN0WEbsrZprrPGBnufLiNOBDEfFg5Uzz0dapy9XAJ8rn76wC\n", "7o2IB+pG6ut3gXvLL3zfAd5aOU9PpZheBbS1v0JE/Es5M/0KnZ7V14C/6rVvqy8RNTOzpdX26SAz\n", "M1tCLgJmZiPMRcDMbIS5CJiZjTAXATOzEeYiYGY2wlwEzMxGmIuAmdkI+z9v8LeJ3GwnTgAAAABJ\n", "RU5ErkJggg==\n" ], "text/plain": [ "" ] }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [], "source": [ "from dateutil.parser import parse" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "\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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [], "source": [ "segments_merged = pd.merge(vessels, segments, left_index=True, right_on='mmsi')" ] }, { "cell_type": "code", "execution_count": 30, "metadata": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [], "source": [ "mb1.columns = mb2.columns = ['Count']" ] }, { "cell_type": "code", "execution_count": 39, "metadata": { "collapsed": false }, "outputs": [], "source": [ "mb1.index.name = mb2.index.name = 'Taxon'" ] }, { "cell_type": "code", "execution_count": 40, "metadata": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [], "source": [ "treatment_map = {'Placebo': 0, '5000U': 1, '10000U': 2}" ] }, { "cell_type": "code", "execution_count": 70, "metadata": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "" ] }, "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": { "collapsed": false }, "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", "
*(figure taken from \"Python for Data Analysis\", p.251)*
" ] }, { "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [], "source": [ "chunks = dict(list(cdystonia_grouped))" ] }, { "cell_type": "code", "execution_count": 100, "metadata": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [], "source": [ "class_index = mb1.index.map(lambda x: ' '.join(x.split(' ')[:3]))" ] }, { "cell_type": "code", "execution_count": 109, "metadata": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "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": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "

Data frame:titanic3

1309 observations and 14 variables, maximum # NAs:1188
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
NameLabelsUnitsLevelsStorageNAs
pclass
3
integer
0
survivedSurvived
double
0
nameName
character
0
sex
2
integer
0
ageAgeYear
double
263
sibspNumber of Siblings/Spouses Aboard
double
0
parchNumber of Parents/Children Aboard
double
0
ticketTicket Number
character
0
farePassenger FareBritish Pound (\\243)
double
1
cabin
187
integer
0
embarked
3
integer
2
boat
28
integer
0
bodyBody Identification Number
double
1188
home.destHome/Destination
character
0
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
VariableLevels
pclass1st
2nd
3rd
sexfemale
male
cabin
A10
A11
A14
A16
A18
A19
A20
A21
A23
A24
A26
A29
A31
A32
A34
A36
A5
A6
A7
A9
B10
B101
B102
B11
B18
B19
B20
B22
B24
B26
B28
B3
B30
B35
B36
B37
B38
B39
B4
B41
B42
B45
B49
B5
B50
B51 B53 B55
B52 B54 B56
B57 B59 B63 B66
B58 B60
B61
B69
B71
B73
B77
B78
B79
B80
B82 B84
B86
B94
B96 B98
C101
C103
C104
C105
C106
C110
C111
C116
C118
C123
C124
C125
C126
C128
C130
C132
C148
C2
C22 C26
C23 C25 C27
C28
C30
C31
C32
C39
C45
C46
C47
C49
C50
C51
C52
C53
C54
C55 C57
C6
C62 C64
C65
C68
C7
C70
C78
C80
C82
C83
C85
C86
C87
C89
C90
C91
C92
C93
C95
C97
C99
D
D10 D12
D11
D15
D17
D19
D20
D21
D22
D26
D28
D30
D33
D34
D35
D36
D37
D38
D40
D43
D45
D46
D47
D48
D49
D50
D56
D6
D7
D9
E10
E101
E12
E121
E17
E24
E25
E31
E33
E34
E36
E38
E39 E41
E40
E44
E45
E46
E49
E50
E52
E58
E60
E63
E67
E68
E77
E8
F
F E46
F E57
F E69
F G63
F G73
F2
F33
F38
F4
G6
T
embarkedCherbourg
Queenstown
Southampton
boat
1
10
11
12
13
13 15
13 15 B
14
15
15 16
16
2
3
4
5
5 7
5 9
6
7
8
8 10
9
A
B
C
C D
D
\n", "
\n" ], "text/plain": [ "" ] }, "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": { "collapsed": false }, "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.4.2" } }, "nbformat": 4, "nbformat_minor": 0 }