{ "metadata": { "name": "", "signature": "sha256:768044f516f8493882a98cdededb6d6f0ab16e7f7f171485208eaf21d4fd2bdd" }, "nbformat": 3, "nbformat_minor": 0, "worksheets": [ { "cells": [ { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Data Wrangling with Pandas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Python is a terrific platform for statistical data analysis partly because of the features of the language itself, but also because of a rich suite of 3rd party packages that provide robust and flexible data structures, efficient implementations of mathematical and statistical functions, and facitities for generating publication-quality graphics. Pandas is at the top of the \"scientific stack\", because it allows data to be imported, manipulated and exported so easily. In contrast, NumPy supports the bottom of the stack with fundamental infrastructure for array operations, mathematical calculations, and random number generation. \n", "\n", "We will cover both of these in some detail before getting down to the business of analyzing data." ] }, { "cell_type": "code", "collapsed": false, "input": [ "%matplotlib inline\n", "import pandas as pd\n", "import numpy as np\n", "\n", "# Set some Pandas options\n", "pd.set_option('html', False)\n", "pd.set_option('max_columns', 30)\n", "pd.set_option('max_rows', 20)" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 1 }, { "cell_type": "heading", "level": 2, "metadata": {}, "source": [ "NumPy\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The most fundamental third-party package for scientific computing in Python is NumPy, which provides multidimensional array data types, along with associated functions and methods to manipulate them. While Python comes with several container types (`list`,`tuple`,`dict`), NumPy's arrays are implemented closer to the hardware, and are therefore more efficient than the built-in types. " ] }, { "cell_type": "heading", "level": 3, "metadata": {}, "source": [ "Basics of Numpy arrays" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The main object provided by numpy is a powerful array. We'll start by exploring how the numpy array differs from Python lists. We start by creating a simple list and an array with the same contents of the list:\n", "\n" ] }, { "cell_type": "code", "collapsed": false, "input": [ "a_list = range(1000)\n", "an_array = np.arange(1000)" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 2 }, { "cell_type": "markdown", "metadata": {}, "source": [ "This is what the array looks like:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "an_array[:10]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 3, "text": [ "array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9])" ] } ], "prompt_number": 3 }, { "cell_type": "code", "collapsed": false, "input": [ "type(an_array)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 4, "text": [ "numpy.ndarray" ] } ], "prompt_number": 4 }, { "cell_type": "code", "collapsed": false, "input": [ "timeit [i**2 for i in a_list]" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "10000 loops, best of 3: 115 \u00b5s per loop\n" ] } ], "prompt_number": 5 }, { "cell_type": "code", "collapsed": false, "input": [ "timeit an_array**2" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "100000 loops, best of 3: 1.95 \u00b5s per loop\n" ] } ], "prompt_number": 6 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Elements of a one-dimensional array are indexed with square brackets, as with lists:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "an_array[5:10]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 7, "text": [ "array([5, 6, 7, 8, 9])" ] } ], "prompt_number": 7 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The first difference to note between lists and arrays is that arrays are *homogeneous*; i.e. all elements of an array must be of the same type. In contrast, lists can contain elements of arbitrary type. For example, we can change the last element in our list above to be a string:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "a_list[0] = 'a string inside a list'\n", "a_list[:10]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 8, "text": [ "['a string inside a list', 1, 2, 3, 4, 5, 6, 7, 8, 9]" ] } ], "prompt_number": 8 }, { "cell_type": "code", "collapsed": false, "input": [ "an_array[0] = 'a string inside an array'" ], "language": "python", "metadata": {}, "outputs": [ { "ename": "ValueError", "evalue": "invalid literal for long() with base 10: 'a string inside an array'", "output_type": "pyerr", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mValueError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0man_array\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'a string inside an array'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;31mValueError\u001b[0m: invalid literal for long() with base 10: 'a string inside an array'" ] } ], "prompt_number": 9 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The information about the type of an array is contained in its *dtype* attribute:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "an_array.dtype" ], "language": "python", "metadata": {}, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Once an array has been created, its dtype is fixed and it can only store elements of the same type. For this example where the dtype is integer, if we store a floating point number it will be automatically converted into an integer:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "an_array[0] = 1.234\n", "an_array[:10]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 10, "text": [ "array([1, 1, 2, 3, 4, 5, 6, 7, 8, 9])" ] } ], "prompt_number": 10 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `linspace` and `logspace` functions to create linearly and logarithmically-spaced grids respectively, with a fixed number of points and including both ends of the specified interval:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "np.linspace(0, 1, num=5)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 11, "text": [ "array([ 0. , 0.25, 0.5 , 0.75, 1. ])" ] } ], "prompt_number": 11 }, { "cell_type": "code", "collapsed": false, "input": [ "np.logspace(1, 4, num=4)\n" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 12, "text": [ "array([ 10., 100., 1000., 10000.])" ] } ], "prompt_number": 12 }, { "cell_type": "markdown", "metadata": {}, "source": [ "It is often useful to create arrays with random numbers that follow a specific distribution. The `np.random` module contains a number of functions that can be used to this effect, for example this will produce an array of 5 random samples taken from a standard normal distribution (0 mean and variance 1):" ] }, { "cell_type": "code", "collapsed": false, "input": [ "np.random.randn(5)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 13, "text": [ "array([ 0.23384357, 0.42443595, 0.61751552, -0.2040506 , -0.97145269])" ] } ], "prompt_number": 13 }, { "cell_type": "markdown", "metadata": {}, "source": [ "whereas the following will also give 5 samples, but from a normal distribution with a mean of 10 and a variance of 3:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "norm_10 = np.random.normal(loc=10, scale=3, size=10)\n", "norm_10" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 14, "text": [ "array([ 7.40864972, 5.28860042, 11.4584049 , 13.22996099,\n", " 16.2038088 , 7.79999289, 15.28090008, 9.23417946,\n", " 7.68310883, 11.72980755])" ] } ], "prompt_number": 14 }, { "cell_type": "heading", "level": 3, "metadata": {}, "source": [ "Indexing with other arrays" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Above we saw how to index arrays with single numbers and slices, just like Python lists. But arrays allow for a more sophisticated kind of indexing which is very powerful: you can index an array with another array, and in particular with an array of boolean values. This is particluarly useful to extract information from an array that matches a certain condition.\n", "\n", "Consider for example that in the array `norm10` we want to replace all values above 9 with the value 0. We can do so by first finding the *mask* that indicates where this condition is `True` or `False`:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "mask = norm_10 > 9\n", "mask" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 15, "text": [ "array([False, False, True, True, True, False, True, True, False, True], dtype=bool)" ] } ], "prompt_number": 15 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now that we have this mask, we can use it to either read those values or to reset them to 0:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "norm_10[mask]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 16, "text": [ "array([ 11.4584049 , 13.22996099, 16.2038088 , 15.28090008,\n", " 9.23417946, 11.72980755])" ] } ], "prompt_number": 16 }, { "cell_type": "code", "collapsed": false, "input": [ "norm_10[mask] = 0\n", "print norm_10" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "[ 7.40864972 5.28860042 0. 0. 0. 7.79999289\n", " 0. 0. 7.68310883 0. ]\n" ] } ], "prompt_number": 17 }, { "cell_type": "code", "collapsed": false, "input": [ "norm_10[np.nonzero(norm_10)]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 18, "text": [ "array([ 7.40864972, 5.28860042, 7.79999289, 7.68310883])" ] } ], "prompt_number": 18 }, { "cell_type": "heading", "level": 3, "metadata": {}, "source": [ "Multidimensional Arrays\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Numpy can create arrays of aribtrary dimensions, and all the methods illustrated in the previous section work with more than one dimension. For example, a list of lists can be used to initialize a two dimensional array:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "array_2d = np.array([[1, 2], [3, 4]])\n", "array_2d.shape" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 19, "text": [ "(2, 2)" ] } ], "prompt_number": 19 }, { "cell_type": "markdown", "metadata": {}, "source": [ "With two-dimensional arrays we start seeing the power of numpy: while a nested list can be indexed using repeatedly the `[ ]` operator, multidimensional arrays support a much more natural indexing syntax with a single `[ ]` and a set of indices separated by commas:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "array_2d[0,1]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 20, "text": [ "2" ] } ], "prompt_number": 20 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The shape of an array can be changed at any time, as long as the total number of elements is unchanged. For example, if we want a 2x4 array with numbers increasing from 0, the easiest way to create it is via the numpy array's `reshape` method." ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array = np.arange(8).reshape(2,4)\n", "print md_array" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "text": [ "[[0 1 2 3]\n", " [4 5 6 7]]\n" ] } ], "prompt_number": 21 }, { "cell_type": "markdown", "metadata": {}, "source": [ "With multidimensional arrays, you can also use slices, and you can mix and match slices and single indices in the different dimensions (using the same array as above):" ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array[1, 2:4]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 22, "text": [ "array([6, 7])" ] } ], "prompt_number": 22 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array[:, 2]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 23, "text": [ "array([2, 6])" ] } ], "prompt_number": 23 }, { "cell_type": "markdown", "metadata": {}, "source": [ "If you only provide one index, then you will get the corresponding row." ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array[1]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 24, "text": [ "array([4, 5, 6, 7])" ] } ], "prompt_number": 24 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Arrays have a slew of useful attributes and methods:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.dtype" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 25, "text": [ "dtype('int64')" ] } ], "prompt_number": 25 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.shape" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 26, "text": [ "(2, 4)" ] } ], "prompt_number": 26 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.ndim" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 27, "text": [ "2" ] } ], "prompt_number": 27 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.nbytes" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 28, "text": [ "64" ] } ], "prompt_number": 28 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.min(), md_array.max()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 29, "text": [ "(0, 7)" ] } ], "prompt_number": 29 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.sum(), md_array.prod()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 30, "text": [ "(28, 0)" ] } ], "prompt_number": 30 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.mean(), md_array.std()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 31, "text": [ "(3.5, 2.2912878474779199)" ] } ], "prompt_number": 31 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Arrays may be summarized along specified axes:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.sum(axis=0)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 32, "text": [ "array([ 4, 6, 8, 10])" ] } ], "prompt_number": 32 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.sum(axis=1)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 33, "text": [ "array([ 6, 22])" ] } ], "prompt_number": 33 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Or, more generally:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "random_array = np.random.random((3,2,3,4))\n", "random_array" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 34, "text": [ "array([[[[ 0.46830226, 0.32731366, 0.23317943, 0.22991289],\n", " [ 0.20261665, 0.93655274, 0.17941865, 0.99712239],\n", " [ 0.5154546 , 0.69956186, 0.3515375 , 0.89186765]],\n", "\n", " [[ 0.37951754, 0.87061811, 0.52325301, 0.19753693],\n", " [ 0.716204 , 0.0015645 , 0.17882149, 0.7549386 ],\n", " [ 0.89425408, 0.41199676, 0.29458076, 0.27091564]]],\n", "\n", "\n", " [[[ 0.78263062, 0.1324776 , 0.867391 , 0.93512634],\n", " [ 0.40886267, 0.83681014, 0.62876166, 0.26180909],\n", " [ 0.17686583, 0.30163449, 0.48994319, 0.5443208 ]],\n", "\n", " [[ 0.79109438, 0.85315179, 0.27670091, 0.95031492],\n", " [ 0.40366678, 0.08635273, 0.49408251, 0.83502123],\n", " [ 0.97103849, 0.00309119, 0.45505554, 0.53595346]]],\n", "\n", "\n", " [[[ 0.85388212, 0.54293366, 0.40702197, 0.02730233],\n", " [ 0.15515379, 0.5897746 , 0.87678707, 0.611536 ],\n", " [ 0.5585089 , 0.94662013, 0.29901721, 0.47758475]],\n", "\n", " [[ 0.54216565, 0.22196223, 0.01673971, 0.65437799],\n", " [ 0.80744884, 0.19855128, 0.75430624, 0.83723579],\n", " [ 0.4197388 , 0.01611553, 0.44570697, 0.62499116]]]])" ] } ], "prompt_number": 34 }, { "cell_type": "code", "collapsed": false, "input": [ "random_array.sum(2).shape" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 35, "text": [ "(3, 2, 4)" ] } ], "prompt_number": 35 }, { "cell_type": "markdown", "metadata": {}, "source": [ "NumPy arrays support all standard arithmetic operations, which are typically applied element-wise." ] }, { "cell_type": "code", "collapsed": false, "input": [ "first_array = np.random.randn(4)\n", "second_array = np.random.randn(4)\n", "\n", "first_array, second_array" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 36, "text": [ "(array([ 0.83038148, -0.82847091, 1.23465502, 1.05221313]),\n", " array([ 0.15413213, -0.52053375, -0.78537127, -0.51907577]))" ] } ], "prompt_number": 36 }, { "cell_type": "code", "collapsed": false, "input": [ "first_array * second_array" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 37, "text": [ "array([ 0.12798847, 0.43124707, -0.96966258, -0.54617834])" ] } ], "prompt_number": 37 }, { "cell_type": "markdown", "metadata": {}, "source": [ "When operating on scalars (zero-dimensional objects), *broadcasting* is used to apply the operation to each element: " ] }, { "cell_type": "code", "collapsed": false, "input": [ "first_array * 5" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 38, "text": [ "array([ 4.15190741, -4.14235457, 6.17327509, 5.26106567])" ] } ], "prompt_number": 38 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Broadcasting also works for multidimensional arrays:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 39, "text": [ "array([[0, 1, 2, 3],\n", " [4, 5, 6, 7]])" ] } ], "prompt_number": 39 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array * first_array" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 40, "text": [ "array([[ 0. , -0.82847091, 2.46931004, 3.1566394 ],\n", " [ 3.32152593, -4.14235457, 7.40793011, 7.36549194]])" ] } ], "prompt_number": 40 }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the above, NumPy compares the trailing dimensions of each array, and adds dimsnsions of length 1 for the remaining dimensions, before multiplying. Hence, the following will not work:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array * np.array([-1, 2.3])" ], "language": "python", "metadata": {}, "outputs": [ { "ename": "ValueError", "evalue": "operands could not be broadcast together with shapes (2,4) (2,) ", "output_type": "pyerr", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mValueError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mmd_array\u001b[0m \u001b[0;34m*\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0marray\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m2.3\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;31mValueError\u001b[0m: operands could not be broadcast together with shapes (2,4) (2,) " ] } ], "prompt_number": 41 }, { "cell_type": "markdown", "metadata": {}, "source": [ "This can be made to work either by \"injecting\" an additional axis, or by transposing the first array:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array * np.array([-1, 2.3])[:, np.newaxis]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 42, "text": [ "array([[ -0. , -1. , -2. , -3. ],\n", " [ 9.2, 11.5, 13.8, 16.1]])" ] } ], "prompt_number": 42 }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.T * np.array([-1, 2.3])" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 43, "text": [ "array([[ -0. , 9.2],\n", " [ -1. , 11.5],\n", " [ -2. , 13.8],\n", " [ -3. , 16.1]])" ] } ], "prompt_number": 43 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Some may have predicted the multiply operator to perform matrix multiplication on two array arguments, rather than element-wise multiplication. NumPy includes a linear algebra library, and matrix mutliplication can be carried out using the `dot` (*i.e.* dot product) function or method:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "md_array.dot(first_array)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 44, "text": [ "array([ 4.79747853, 13.95259341])" ] } ], "prompt_number": 44 }, { "cell_type": "code", "collapsed": false, "input": [ "np.dot(md_array, first_array)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 45, "text": [ "array([ 4.79747853, 13.95259341])" ] } ], "prompt_number": 45 }, { "cell_type": "heading", "level": 2, "metadata": {}, "source": [ "Introduction to Pandas\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**pandas** is a Python package providing fast, flexible, and expressive data structures designed to work with *relational* or *labeled* data both. It is a fundamental high-level building block for doing practical, real world data analysis in Python. \n", "\n", "pandas is well suited for:\n", "\n", "- Tabular data with heterogeneously-typed columns, as in an SQL table or Excel spreadsheet\n", "- Ordered and unordered (not necessarily fixed-frequency) time series data.\n", "- Arbitrary matrix data (homogeneously typed or heterogeneous) with row and column labels\n", "- Any other form of observational / statistical data sets. The data actually need not be labeled at all to be placed into a pandas data structure\n", "\n", "\n", "Key features:\n", " \n", "- Easy handling of **missing data**\n", "- **Size mutability**: columns can be inserted and deleted from DataFrame and higher dimensional objects\n", "- Automatic and explicit **data alignment**: objects can be explicitly aligned to a set of labels, or the data can be aligned automatically\n", "- Powerful, flexible **group by functionality** to perform split-apply-combine operations on data sets\n", "- Intelligent label-based **slicing, fancy indexing, and subsetting** of large data sets\n", "- Intuitive **merging and joining** data sets\n", "- Flexible **reshaping and pivoting** of data sets\n", "- **Hierarchical labeling** of axes\n", "- Robust **IO tools** for loading data from flat files, Excel files, databases, and HDF5\n", "- **Time series functionality**: date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging, etc." ] }, { "cell_type": "code", "collapsed": false, "input": [ "from IPython.core.display import HTML\n", "HTML(\"\")" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "" ], "metadata": {}, "output_type": "pyout", "prompt_number": 46, "text": [ "" ] } ], "prompt_number": 46 }, { "cell_type": "heading", "level": 2, "metadata": {}, "source": [ "Pandas Data Structures" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Series\n", "\n", "A **Series** is a single vector of data (like a NumPy array) with an *index* that labels each element in the vector." ] }, { "cell_type": "code", "collapsed": false, "input": [ "counts = pd.Series([632, 1638, 569, 115])\n", "counts" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 47, "text": [ "0 632\n", "1 1638\n", "2 569\n", "3 115\n", "dtype: int64" ] } ], "prompt_number": 47 }, { "cell_type": "markdown", "metadata": {}, "source": [ "If an index is not specified, a default sequence of integers is assigned as the index. A NumPy array comprises the values of the `Series`, while the index is a pandas `Index` object." ] }, { "cell_type": "code", "collapsed": false, "input": [ "counts.values" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 48, "text": [ "array([ 632, 1638, 569, 115])" ] } ], "prompt_number": 48 }, { "cell_type": "code", "collapsed": false, "input": [ "counts.index" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 49, "text": [ "Int64Index([0, 1, 2, 3], dtype='int64')" ] } ], "prompt_number": 49 }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can assign meaningful labels to the index, if they are available:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria = pd.Series([632, 1638, 569, 115], \n", " index=['Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes'])\n", "\n", "bacteria" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 50, "text": [ "Firmicutes 632\n", "Proteobacteria 1638\n", "Actinobacteria 569\n", "Bacteroidetes 115\n", "dtype: int64" ] } ], "prompt_number": 50 }, { "cell_type": "markdown", "metadata": {}, "source": [ "These labels can be used to refer to the values in the `Series`." ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria['Actinobacteria']" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 51, "text": [ "569" ] } ], "prompt_number": 51 }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria[[name.endswith('bacteria') for name in bacteria.index]]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 52, "text": [ "Proteobacteria 1638\n", "Actinobacteria 569\n", "dtype: int64" ] } ], "prompt_number": 52 }, { "cell_type": "code", "collapsed": false, "input": [ "[name.endswith('bacteria') for name in bacteria.index]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 53, "text": [ "[False, True, True, False]" ] } ], "prompt_number": 53 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Notice that the indexing operation preserved the association between the values and the corresponding indices.\n", "\n", "We can still use positional indexing if we wish." ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria[0]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 54, "text": [ "632" ] } ], "prompt_number": 54 }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can give both the array of values and the index meaningful labels themselves:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria.name = 'counts'\n", "bacteria.index.name = 'phylum'\n", "bacteria" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 55, "text": [ "phylum\n", "Firmicutes 632\n", "Proteobacteria 1638\n", "Actinobacteria 569\n", "Bacteroidetes 115\n", "Name: counts, dtype: int64" ] } ], "prompt_number": 55 }, { "cell_type": "markdown", "metadata": {}, "source": [ "NumPy's math functions and other operations can be applied to Series without losing the data structure." ] }, { "cell_type": "code", "collapsed": false, "input": [ "np.log(bacteria)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 56, "text": [ "phylum\n", "Firmicutes 6.448889\n", "Proteobacteria 7.401231\n", "Actinobacteria 6.343880\n", "Bacteroidetes 4.744932\n", "Name: counts, dtype: float64" ] } ], "prompt_number": 56 }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also filter according to the values in the `Series`:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria[bacteria>1000]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 57, "text": [ "phylum\n", "Proteobacteria 1638\n", "Name: counts, dtype: int64" ] } ], "prompt_number": 57 }, { "cell_type": "markdown", "metadata": {}, "source": [ "A `Series` can be thought of as an ordered key-value store. In fact, we can create one from a `dict`:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria_dict = {'Firmicutes': 632, 'Proteobacteria': 1638, 'Actinobacteria': 569, 'Bacteroidetes': 115}\n", "pd.Series(bacteria_dict)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 58, "text": [ "Actinobacteria 569\n", "Bacteroidetes 115\n", "Firmicutes 632\n", "Proteobacteria 1638\n", "dtype: int64" ] } ], "prompt_number": 58 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Notice that the `Series` is created in key-sorted order.\n", "\n", "If we pass a custom index to `Series`, it will select the corresponding values from the dict, and treat indices without corrsponding values as missing. Pandas uses the `NaN` (not a number) type for missing values." ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria2 = pd.Series(bacteria_dict, index=['Cyanobacteria','Firmicutes','Proteobacteria','Actinobacteria'])\n", "bacteria2" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 59, "text": [ "Cyanobacteria NaN\n", "Firmicutes 632\n", "Proteobacteria 1638\n", "Actinobacteria 569\n", "dtype: float64" ] } ], "prompt_number": 59 }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria2.isnull()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 60, "text": [ "Cyanobacteria True\n", "Firmicutes False\n", "Proteobacteria False\n", "Actinobacteria False\n", "dtype: bool" ] } ], "prompt_number": 60 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Critically, the labels are used to **align data** when used in operations with other Series objects:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria + bacteria2" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 61, "text": [ "Actinobacteria 1138\n", "Bacteroidetes NaN\n", "Cyanobacteria NaN\n", "Firmicutes 1264\n", "Proteobacteria 3276\n", "dtype: float64" ] } ], "prompt_number": 61 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Contrast this with NumPy arrays, where arrays of the same length will combine values element-wise; adding Series combined values with the same label in the resulting series. Notice also that the missing values were propogated by addition." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### DataFrame\n", "\n", "Inevitably, we want to be able to store, view and manipulate data that is *multivariate*, where for every index there are multiple fields or columns of data, often of varying data type.\n", "\n", "A `DataFrame` is a tabular data structure, encapsulating multiple series like columns in a spreadsheet. Data are stored internally as a 2-dimensional object, but the `DataFrame` allows us to represent and manipulate higher-dimensional data." ] }, { "cell_type": "code", "collapsed": false, "input": [ "data = pd.DataFrame({'value':[632, 1638, 569, 115, 433, 1130, 754, 555],\n", " 'patient':[1, 1, 1, 1, 2, 2, 2, 2],\n", " 'phylum':['Firmicutes', 'Proteobacteria', 'Actinobacteria', \n", " 'Bacteroidetes', 'Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes']})\n", "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 62, "text": [ " patient phylum value\n", "0 1 Firmicutes 632\n", "1 1 Proteobacteria 1638\n", "2 1 Actinobacteria 569\n", "3 1 Bacteroidetes 115\n", "4 2 Firmicutes 433\n", "5 2 Proteobacteria 1130\n", "6 2 Actinobacteria 754\n", "7 2 Bacteroidetes 555\n", "\n", "[8 rows x 3 columns]" ] } ], "prompt_number": 62 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Notice the `DataFrame` is sorted by column name. We can change the order by indexing them in the order we desire:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "data[['phylum','value','patient']]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 63, "text": [ " phylum value patient\n", "0 Firmicutes 632 1\n", "1 Proteobacteria 1638 1\n", "2 Actinobacteria 569 1\n", "3 Bacteroidetes 115 1\n", "4 Firmicutes 433 2\n", "5 Proteobacteria 1130 2\n", "6 Actinobacteria 754 2\n", "7 Bacteroidetes 555 2\n", "\n", "[8 rows x 3 columns]" ] } ], "prompt_number": 63 }, { "cell_type": "markdown", "metadata": {}, "source": [ "A `DataFrame` has a second index, representing the columns:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "data.columns" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 64, "text": [ "Index([u'patient', u'phylum', u'value'], dtype='object')" ] } ], "prompt_number": 64 }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we wish to access columns, we can do so either by dict-like indexing or by attribute:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "data['value']" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 65, "text": [ "0 632\n", "1 1638\n", "2 569\n", "3 115\n", "4 433\n", "5 1130\n", "6 754\n", "7 555\n", "Name: value, dtype: int64" ] } ], "prompt_number": 65 }, { "cell_type": "code", "collapsed": false, "input": [ "data.value" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 66, "text": [ "0 632\n", "1 1638\n", "2 569\n", "3 115\n", "4 433\n", "5 1130\n", "6 754\n", "7 555\n", "Name: value, dtype: int64" ] } ], "prompt_number": 66 }, { "cell_type": "code", "collapsed": false, "input": [ "type(data.value)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 67, "text": [ "pandas.core.series.Series" ] } ], "prompt_number": 67 }, { "cell_type": "code", "collapsed": false, "input": [ "type(data[['value']])" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 68, "text": [ "pandas.core.frame.DataFrame" ] } ], "prompt_number": 68 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Notice this is different than with `Series`, where dict-like indexing retrieved a particular element (row). If we want access to a row in a `DataFrame`, we index its `ix` attribute.\n" ] }, { "cell_type": "code", "collapsed": false, "input": [ "data.ix[3]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 69, "text": [ "patient 1\n", "phylum Bacteroidetes\n", "value 115\n", "Name: 3, dtype: object" ] } ], "prompt_number": 69 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Its important to note that the Series returned when a DataFrame is indexted is merely a **view** on the DataFrame, and not a copy of the data itself. So you must be cautious when manipulating this data:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "vals = data.value\n", "vals" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 70, "text": [ "0 632\n", "1 1638\n", "2 569\n", "3 115\n", "4 433\n", "5 1130\n", "6 754\n", "7 555\n", "Name: value, dtype: int64" ] } ], "prompt_number": 70 }, { "cell_type": "code", "collapsed": false, "input": [ "vals[5] = 0\n", "vals" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 71, "text": [ "0 632\n", "1 1638\n", "2 569\n", "3 115\n", "4 433\n", "5 0\n", "6 754\n", "7 555\n", "Name: value, dtype: int64" ] } ], "prompt_number": 71 }, { "cell_type": "code", "collapsed": false, "input": [ "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 72, "text": [ " patient phylum value\n", "0 1 Firmicutes 632\n", "1 1 Proteobacteria 1638\n", "2 1 Actinobacteria 569\n", "3 1 Bacteroidetes 115\n", "4 2 Firmicutes 433\n", "5 2 Proteobacteria 0\n", "6 2 Actinobacteria 754\n", "7 2 Bacteroidetes 555\n", "\n", "[8 rows x 3 columns]" ] } ], "prompt_number": 72 }, { "cell_type": "code", "collapsed": false, "input": [ "vals = data.value.copy()\n", "vals[5] = 1000\n", "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 73, "text": [ " patient phylum value\n", "0 1 Firmicutes 632\n", "1 1 Proteobacteria 1638\n", "2 1 Actinobacteria 569\n", "3 1 Bacteroidetes 115\n", "4 2 Firmicutes 433\n", "5 2 Proteobacteria 0\n", "6 2 Actinobacteria 754\n", "7 2 Bacteroidetes 555\n", "\n", "[8 rows x 3 columns]" ] } ], "prompt_number": 73 }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can create or modify columns by assignment:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "data.value[3] = 14\n", "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 74, "text": [ " patient phylum value\n", "0 1 Firmicutes 632\n", "1 1 Proteobacteria 1638\n", "2 1 Actinobacteria 569\n", "3 1 Bacteroidetes 14\n", "4 2 Firmicutes 433\n", "5 2 Proteobacteria 0\n", "6 2 Actinobacteria 754\n", "7 2 Bacteroidetes 555\n", "\n", "[8 rows x 3 columns]" ] } ], "prompt_number": 74 }, { "cell_type": "code", "collapsed": false, "input": [ "data['year'] = 2013\n", "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 75, "text": [ " patient phylum value year\n", "0 1 Firmicutes 632 2013\n", "1 1 Proteobacteria 1638 2013\n", "2 1 Actinobacteria 569 2013\n", "3 1 Bacteroidetes 14 2013\n", "4 2 Firmicutes 433 2013\n", "5 2 Proteobacteria 0 2013\n", "6 2 Actinobacteria 754 2013\n", "7 2 Bacteroidetes 555 2013\n", "\n", "[8 rows x 4 columns]" ] } ], "prompt_number": 75 }, { "cell_type": "markdown", "metadata": {}, "source": [ "But note, we cannot use the attribute indexing method to add a new column:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "data.treatment = 1\n", "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 76, "text": [ " patient phylum value year\n", "0 1 Firmicutes 632 2013\n", "1 1 Proteobacteria 1638 2013\n", "2 1 Actinobacteria 569 2013\n", "3 1 Bacteroidetes 14 2013\n", "4 2 Firmicutes 433 2013\n", "5 2 Proteobacteria 0 2013\n", "6 2 Actinobacteria 754 2013\n", "7 2 Bacteroidetes 555 2013\n", "\n", "[8 rows x 4 columns]" ] } ], "prompt_number": 76 }, { "cell_type": "code", "collapsed": false, "input": [ "data.treatment" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 77, "text": [ "1" ] } ], "prompt_number": 77 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Specifying a `Series` as a new columns cause its values to be added according to the `DataFrame`'s index:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "treatment = pd.Series([0]*4 + [1]*2)\n", "treatment" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 78, "text": [ "0 0\n", "1 0\n", "2 0\n", "3 0\n", "4 1\n", "5 1\n", "dtype: int64" ] } ], "prompt_number": 78 }, { "cell_type": "code", "collapsed": false, "input": [ "data['treatment'] = treatment\n", "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 79, "text": [ " patient phylum value year treatment\n", "0 1 Firmicutes 632 2013 0\n", "1 1 Proteobacteria 1638 2013 0\n", "2 1 Actinobacteria 569 2013 0\n", "3 1 Bacteroidetes 14 2013 0\n", "4 2 Firmicutes 433 2013 1\n", "5 2 Proteobacteria 0 2013 1\n", "6 2 Actinobacteria 754 2013 NaN\n", "7 2 Bacteroidetes 555 2013 NaN\n", "\n", "[8 rows x 5 columns]" ] } ], "prompt_number": 79 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Other Python data structures (ones without an index) need to be the same length as the `DataFrame`:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "month = ['Jan', 'Feb', 'Mar', 'Apr']\n", "data['month'] = month" ], "language": "python", "metadata": {}, "outputs": [ { "ename": "ValueError", "evalue": "Length of values does not match length of index", "output_type": "pyerr", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mValueError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mmonth\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'Jan'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Feb'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Mar'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Apr'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mdata\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'month'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmonth\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;32m/Users/fonnescj/.virtualenvs/pymc2/lib/python2.7/site-packages/pandas-0.13.1_213_gc174c3d-py2.7-macosx-10.9-intel.egg/pandas/core/frame.pyc\u001b[0m in \u001b[0;36m__setitem__\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 1894\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1895\u001b[0m \u001b[0;31m# set column\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1896\u001b[0;31m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 1897\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1898\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_setitem_slice\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m/Users/fonnescj/.virtualenvs/pymc2/lib/python2.7/site-packages/pandas-0.13.1_213_gc174c3d-py2.7-macosx-10.9-intel.egg/pandas/core/frame.pyc\u001b[0m in \u001b[0;36m_set_item\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 1974\u001b[0m \u001b[0mis_existing\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mkey\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1975\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_ensure_valid_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1976\u001b[0;31m \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sanitize_column\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 1977\u001b[0m \u001b[0mNDFrame\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1978\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m/Users/fonnescj/.virtualenvs/pymc2/lib/python2.7/site-packages/pandas-0.13.1_213_gc174c3d-py2.7-macosx-10.9-intel.egg/pandas/core/frame.pyc\u001b[0m in \u001b[0;36m_sanitize_column\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m 2024\u001b[0m \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mIndex\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0m_is_sequence\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2025\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2026\u001b[0;31m raise ValueError('Length of values does not match length of '\n\u001b[0m\u001b[1;32m 2027\u001b[0m 'index')\n\u001b[1;32m 2028\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mValueError\u001b[0m: Length of values does not match length of index" ] } ], "prompt_number": 80 }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can extract the underlying data as a simple `ndarray` by accessing the `values` attribute:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "data.values" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 81, "text": [ "array([[1, 'Firmicutes', 632, 2013, 0.0],\n", " [1, 'Proteobacteria', 1638, 2013, 0.0],\n", " [1, 'Actinobacteria', 569, 2013, 0.0],\n", " [1, 'Bacteroidetes', 14, 2013, 0.0],\n", " [2, 'Firmicutes', 433, 2013, 1.0],\n", " [2, 'Proteobacteria', 0, 2013, 1.0],\n", " [2, 'Actinobacteria', 754, 2013, nan],\n", " [2, 'Bacteroidetes', 555, 2013, nan]], dtype=object)" ] } ], "prompt_number": 81 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Notice that because of the mix of string and integer (and `NaN`) values, the dtype of the array is `object`. The dtype will automatically be chosen to be as general as needed to accomodate all the columns." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas uses a custom data structure to represent the indices of Series and DataFrames." ] }, { "cell_type": "code", "collapsed": false, "input": [ "data.index" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 82, "text": [ "Int64Index([0, 1, 2, 3, 4, 5, 6, 7], dtype='int64')" ] } ], "prompt_number": 82 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Index objects are immutable:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "data.index[0] = 15" ], "language": "python", "metadata": {}, "outputs": [ { "ename": "TypeError", "evalue": "'' does not support mutable operations.", "output_type": "pyerr", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mTypeError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m15\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;32m/Users/fonnescj/.virtualenvs/pymc2/lib/python2.7/site-packages/pandas-0.13.1_213_gc174c3d-py2.7-macosx-10.9-intel.egg/pandas/core/base.pyc\u001b[0m in \u001b[0;36m_disabled\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m 178\u001b[0m \u001b[0;34m\"\"\"This method will not function because object is immutable.\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 179\u001b[0m raise TypeError(\"'%s' does not support mutable operations.\" %\n\u001b[0;32m--> 180\u001b[0;31m self.__class__)\n\u001b[0m\u001b[1;32m 181\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 182\u001b[0m \u001b[0m__setitem__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m__setslice__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m__delitem__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m__delslice__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_disabled\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mTypeError\u001b[0m: '' does not support mutable operations." ] } ], "prompt_number": 83 }, { "cell_type": "markdown", "metadata": {}, "source": [ "This is so that Index objects can be shared between data structures without fear that they will be changed." ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria2.index = bacteria.index" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 84 }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria2" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 85, "text": [ "phylum\n", "Firmicutes NaN\n", "Proteobacteria 632\n", "Actinobacteria 1638\n", "Bacteroidetes 569\n", "dtype: float64" ] } ], "prompt_number": 85 }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Importing data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "A key, but often under-appreciated, step in data analysis is importing the data that we wish to analyze. Though it is easy to load basic data structures into Python using built-in tools or those provided by packages like NumPy, it is non-trivial to import structured data well, and to easily convert this input into a robust data structure:\n", "\n", " genes = np.loadtxt(\"genes.csv\", delimiter=\",\", dtype=[('gene', '|S10'), ('value', '\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mix\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m7\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'year'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mnan\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 2\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mNameError\u001b[0m: name 'nan' is not defined" ] } ], "prompt_number": 147 }, { "cell_type": "code", "collapsed": false, "input": [ "data.dropna(thresh=4)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 148, "text": [ " patient phylum value year treatment\n", "0 1 Firmicutes 632 2013 0\n", "1 1 Proteobacteria 1638 2013 0\n", "2 1 Actinobacteria 569 2013 0\n", "3 1 Bacteroidetes 14 2013 0\n", "4 2 Firmicutes 433 2013 1\n", "5 2 Proteobacteria 0 2013 1\n", "6 2 Actinobacteria 754 2013 NaN\n", "7 2 Bacteroidetes 555 2013 NaN\n", "\n", "[8 rows x 5 columns]" ] } ], "prompt_number": 148 }, { "cell_type": "markdown", "metadata": {}, "source": [ "This is typically used in time series applications, where there are repeated measurements that are incomplete for some subjects." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we want to drop missing values column-wise instead of row-wise, we use `axis=1`." ] }, { "cell_type": "code", "collapsed": false, "input": [ "data.dropna(axis=1)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 149, "text": [ " patient phylum value year\n", "0 1 Firmicutes 632 2013\n", "1 1 Proteobacteria 1638 2013\n", "2 1 Actinobacteria 569 2013\n", "3 1 Bacteroidetes 14 2013\n", "4 2 Firmicutes 433 2013\n", "5 2 Proteobacteria 0 2013\n", "6 2 Actinobacteria 754 2013\n", "7 2 Bacteroidetes 555 2013\n", "\n", "[8 rows x 4 columns]" ] } ], "prompt_number": 149 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Rather than omitting missing data from an analysis, in some cases it may be suitable to fill the missing value in, either with a default value (such as zero) or a value that is either imputed or carried forward/backward from similar data points. We can do this programmatically in Pandas with the `fillna` argument." ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria2.fillna(0)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 150, "text": [ "phylum\n", "Firmicutes 0\n", "Proteobacteria 632\n", "Actinobacteria 1638\n", "Bacteroidetes 569\n", "dtype: float64" ] } ], "prompt_number": 150 }, { "cell_type": "code", "collapsed": false, "input": [ "data.fillna({'year': 2013, 'treatment':2})" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 151, "text": [ " patient phylum value year treatment\n", "0 1 Firmicutes 632 2013 0\n", "1 1 Proteobacteria 1638 2013 0\n", "2 1 Actinobacteria 569 2013 0\n", "3 1 Bacteroidetes 14 2013 0\n", "4 2 Firmicutes 433 2013 1\n", "5 2 Proteobacteria 0 2013 1\n", "6 2 Actinobacteria 754 2013 2\n", "7 2 Bacteroidetes 555 2013 2\n", "\n", "[8 rows x 5 columns]" ] } ], "prompt_number": 151 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Notice that `fillna` by default returns a new object with the desired filling behavior, rather than changing the `Series` or `DataFrame` in place (**in general, we like to do this, by the way!**)." ] }, { "cell_type": "code", "collapsed": false, "input": [ "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 152, "text": [ " patient phylum value year treatment\n", "0 1 Firmicutes 632 2013 0\n", "1 1 Proteobacteria 1638 2013 0\n", "2 1 Actinobacteria 569 2013 0\n", "3 1 Bacteroidetes 14 2013 0\n", "4 2 Firmicutes 433 2013 1\n", "5 2 Proteobacteria 0 2013 1\n", "6 2 Actinobacteria 754 2013 NaN\n", "7 2 Bacteroidetes 555 2013 NaN\n", "\n", "[8 rows x 5 columns]" ] } ], "prompt_number": 152 }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can alter values in-place using `inplace=True`." ] }, { "cell_type": "code", "collapsed": false, "input": [ "_ = data.year.fillna(2013, inplace=True)\n", "data" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 153, "text": [ " patient phylum value year treatment\n", "0 1 Firmicutes 632 2013 0\n", "1 1 Proteobacteria 1638 2013 0\n", "2 1 Actinobacteria 569 2013 0\n", "3 1 Bacteroidetes 14 2013 0\n", "4 2 Firmicutes 433 2013 1\n", "5 2 Proteobacteria 0 2013 1\n", "6 2 Actinobacteria 754 2013 NaN\n", "7 2 Bacteroidetes 555 2013 NaN\n", "\n", "[8 rows x 5 columns]" ] } ], "prompt_number": 153 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Missing values can also be interpolated, using any one of a variety of methods:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria2.fillna(method='bfill')" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 154, "text": [ "phylum\n", "Firmicutes 632\n", "Proteobacteria 632\n", "Actinobacteria 1638\n", "Bacteroidetes 569\n", "dtype: float64" ] } ], "prompt_number": 154 }, { "cell_type": "code", "collapsed": false, "input": [ "bacteria2.fillna(bacteria2.mean())" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 155, "text": [ "phylum\n", "Firmicutes 946.333333\n", "Proteobacteria 632.000000\n", "Actinobacteria 1638.000000\n", "Bacteroidetes 569.000000\n", "dtype: float64" ] } ], "prompt_number": 155 }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Merging and joining DataFrame objects" ] }, { "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\u2019s (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", "collapsed": false, "input": [ "segments = pd.read_csv(\"data/AIS/transit_segments.csv\")\n", "segments.head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 156, "text": [ " 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 \n", "\n", "[5 rows x 11 columns]" ] } ], "prompt_number": 156 }, { "cell_type": "markdown", "metadata": {}, "source": [ "In addition to the behavior of each vessel, 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", "collapsed": false, "input": [ "vessels = pd.read_csv(\"data/AIS/vessel_information.csv\", index_col='mmsi')\n", "vessels.head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 157, "text": [ " 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 \n", "\n", "[5 rows x 10 columns]" ] } ], "prompt_number": 157 }, { "cell_type": "code", "collapsed": false, "input": [ "vessels.type.value_counts()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 158, "text": [ "Cargo 5622\n", "Tanker 2440\n", "Pleasure 601\n", "Tug 221\n", "Sailing 205\n", "Fishing 200\n", "Other 178\n", "Passenger 150\n", "...\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" ] } ], "prompt_number": 158 }, { "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", "collapsed": false, "input": [ "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" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 159, "text": [ "( age id\n", " 0 26 0\n", " 1 20 1\n", " 2 25 2\n", " 3 24 3\n", " \n", " [4 rows x 2 columns], id score\n", " 0 0 0.176817\n", " 1 1 0.713290\n", " 2 2 0.704577\n", " 3 0 0.948222\n", " 4 1 0.974605\n", " 5 2 0.760699\n", " \n", " [6 rows x 2 columns])" ] } ], "prompt_number": 159 }, { "cell_type": "code", "collapsed": false, "input": [ "pd.merge(df1, df2)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 160, "text": [ " age id score\n", "0 26 0 0.176817\n", "1 26 0 0.948222\n", "2 20 1 0.713290\n", "3 20 1 0.974605\n", "4 25 2 0.704577\n", "5 25 2 0.760699\n", "\n", "[6 rows x 3 columns]" ] } ], "prompt_number": 160 }, { "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", "collapsed": false, "input": [ "pd.merge(df1, df2, how='outer')" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 161, "text": [ " age id score\n", "0 26 0 0.176817\n", "1 26 0 0.948222\n", "2 20 1 0.713290\n", "3 20 1 0.974605\n", "4 25 2 0.704577\n", "5 25 2 0.760699\n", "6 24 3 NaN\n", "\n", "[7 rows x 3 columns]" ] } ], "prompt_number": 161 }, { "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.\n", "\n", "Looking at the two datasets that we wish to merge:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "segments.head(1)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 162, "text": [ " 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 \n", "\n", "[1 rows x 11 columns]" ] } ], "prompt_number": 162 }, { "cell_type": "code", "collapsed": false, "input": [ "vessels.head(1)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 163, "text": [ " 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 \n", "\n", "[1 rows x 10 columns]" ] } ], "prompt_number": 163 }, { "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", "collapsed": false, "input": [ "segments_merged = pd.merge(vessels, segments, left_index=True, right_on='mmsi')" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 164 }, { "cell_type": "code", "collapsed": false, "input": [ "segments_merged.head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 165, "text": [ " 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 mmsi name \\\n", "0 4 Dredging/MilOps/Reserved/Towing 1 Us Govt Ves \n", "1 4 Dredging/MilOps/Reserved/Towing 1 Dredge Capt Frank \n", "2 4 Dredging/MilOps/Reserved/Towing 1 Us Gov Vessel \n", "3 4 Dredging/MilOps/Reserved/Towing 1 Us Gov Vessel \n", "4 4 Dredging/MilOps/Reserved/Towing 1 Dredge Capt Frank \n", "\n", " transit segment seg_length avg_sog min_sog max_sog pdgt10 \\\n", "0 1 1 5.1 13.2 9.2 14.5 96.5 \n", "1 1 1 13.5 18.6 10.4 20.6 100.0 \n", "2 1 1 4.3 16.2 10.3 20.5 100.0 \n", "3 2 1 9.2 15.4 14.5 16.1 100.0 \n", "4 2 1 9.2 15.4 14.6 16.2 100.0 \n", "\n", " st_time end_time \n", "0 2/10/09 16:03 2/10/09 16:27 \n", "1 4/6/09 14:31 4/6/09 15:20 \n", "2 4/6/09 14:36 4/6/09 14:55 \n", "3 4/10/09 17:58 4/10/09 18:34 \n", "4 4/10/09 17:59 4/10/09 18:35 \n", "\n", "[5 rows x 21 columns]" ] } ], "prompt_number": 165 }, { "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.\n", "\n", "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", "collapsed": false, "input": [ "vessels.merge(segments, left_index=True, right_on='mmsi').head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 166, "text": [ " 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 mmsi name \\\n", "0 4 Dredging/MilOps/Reserved/Towing 1 Us Govt Ves \n", "1 4 Dredging/MilOps/Reserved/Towing 1 Dredge Capt Frank \n", "2 4 Dredging/MilOps/Reserved/Towing 1 Us Gov Vessel \n", "3 4 Dredging/MilOps/Reserved/Towing 1 Us Gov Vessel \n", "4 4 Dredging/MilOps/Reserved/Towing 1 Dredge Capt Frank \n", "\n", " transit segment seg_length avg_sog min_sog max_sog pdgt10 \\\n", "0 1 1 5.1 13.2 9.2 14.5 96.5 \n", "1 1 1 13.5 18.6 10.4 20.6 100.0 \n", "2 1 1 4.3 16.2 10.3 20.5 100.0 \n", "3 2 1 9.2 15.4 14.5 16.1 100.0 \n", "4 2 1 9.2 15.4 14.6 16.2 100.0 \n", "\n", " st_time end_time \n", "0 2/10/09 16:03 2/10/09 16:27 \n", "1 4/6/09 14:31 4/6/09 15:20 \n", "2 4/6/09 14:36 4/6/09 14:55 \n", "3 4/10/09 17:58 4/10/09 18:34 \n", "4 4/10/09 17:59 4/10/09 18:35 \n", "\n", "[5 rows x 21 columns]" ] } ], "prompt_number": 166 }, { "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", "collapsed": false, "input": [ "segments['type'] = 'foo'\n", "pd.merge(vessels, segments, left_index=True, right_on='mmsi').head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 167, "text": [ " 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 mmsi name \\\n", "0 4 Dredging/MilOps/Reserved/Towing 1 Us Govt Ves \n", "1 4 Dredging/MilOps/Reserved/Towing 1 Dredge Capt Frank \n", "2 4 Dredging/MilOps/Reserved/Towing 1 Us Gov Vessel \n", "3 4 Dredging/MilOps/Reserved/Towing 1 Us Gov Vessel \n", "4 4 Dredging/MilOps/Reserved/Towing 1 Dredge Capt Frank \n", "\n", " transit segment seg_length avg_sog min_sog max_sog pdgt10 \\\n", "0 1 1 5.1 13.2 9.2 14.5 96.5 \n", "1 1 1 13.5 18.6 10.4 20.6 100.0 \n", "2 1 1 4.3 16.2 10.3 20.5 100.0 \n", "3 2 1 9.2 15.4 14.5 16.1 100.0 \n", "4 2 1 9.2 15.4 14.6 16.2 100.0 \n", "\n", " st_time end_time type_y \n", "0 2/10/09 16:03 2/10/09 16:27 foo \n", "1 4/6/09 14:31 4/6/09 15:20 foo \n", "2 4/6/09 14:36 4/6/09 14:55 foo \n", "3 4/10/09 17:58 4/10/09 18:34 foo \n", "4 4/10/09 17:59 4/10/09 18:35 foo \n", "\n", "[5 rows x 22 columns]" ] } ], "prompt_number": 167 }, { "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", "collapsed": false, "input": [ "np.concatenate([np.random.random(5), np.random.random(5)])" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 168, "text": [ "array([ 0.4917765 , 0.27676379, 0.3403297 , 0.94561572, 0.09820804,\n", " 0.89495602, 0.45218015, 0.15573864, 0.88671523, 0.98639378])" ] } ], "prompt_number": 168 }, { "cell_type": "code", "collapsed": false, "input": [ "np.r_[np.random.random(5), np.random.random(5)]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 169, "text": [ "array([ 0.61759801, 0.78852623, 0.10187625, 0.92809311, 0.5016651 ,\n", " 0.52203484, 0.58372555, 0.76932114, 0.17770492, 0.66359049])" ] } ], "prompt_number": 169 }, { "cell_type": "code", "collapsed": false, "input": [ "np.c_[np.random.random(5), np.random.random(5)]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 170, "text": [ "array([[ 0.45807129, 0.27379738],\n", " [ 0.94707839, 0.77835089],\n", " [ 0.23536909, 0.36638039],\n", " [ 0.77183685, 0.87742435],\n", " [ 0.72104924, 0.82773601]])" ] } ], "prompt_number": 170 }, { "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", "collapsed": false, "input": [ "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" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 171, "text": [ "((272, 1), (288, 1))" ] } ], "prompt_number": 171 }, { "cell_type": "code", "collapsed": false, "input": [ "mb1.head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 172, "text": [ " 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\n", "\n", "[5 rows x 1 columns]" ] } ], "prompt_number": 172 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's give the index and columns meaningful labels:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "mb1.columns = mb2.columns = ['Count']" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 173 }, { "cell_type": "code", "collapsed": false, "input": [ "mb1.index.name = mb2.index.name = 'Taxon'" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 174 }, { "cell_type": "code", "collapsed": false, "input": [ "mb1.head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 175, "text": [ " 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", "[5 rows x 1 columns]" ] } ], "prompt_number": 175 }, { "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)\n", "
*(Source: Wikipedia)*
" ] }, { "cell_type": "code", "collapsed": false, "input": [ "mb1.index[:3]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 176, "text": [ "Index([u'Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Desulfurococcaceae Ignisphaera', u'Archaea \"Crenarchaeota\" Thermoprotei Desulfurococcales Pyrodictiaceae Pyrolobus', u'Archaea \"Crenarchaeota\" Thermoprotei Sulfolobales Sulfolobaceae Stygiolobus'], dtype='object')" ] } ], "prompt_number": 176 }, { "cell_type": "code", "collapsed": false, "input": [ "mb1.index.is_unique" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 177, "text": [ "True" ] } ], "prompt_number": 177 }, { "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", "collapsed": false, "input": [ "pd.concat([mb1, mb2], axis=0).shape" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 178, "text": [ "(560, 1)" ] } ], "prompt_number": 178 }, { "cell_type": "markdown", "metadata": {}, "source": [ "However, the index is no longer unique, due to overlap between the two DataFrames." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.concat([mb1, mb2], axis=0).index.is_unique" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 179, "text": [ "False" ] } ], "prompt_number": 179 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Concatenating along `axis=1` will concatenate column-wise, but respecting the indices of the two DataFrames." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.concat([mb1, mb2], axis=1).shape" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 180, "text": [ "(438, 2)" ] } ], "prompt_number": 180 }, { "cell_type": "code", "collapsed": false, "input": [ "pd.concat([mb1, mb2], axis=1).head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 181, "text": [ " 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 \n", "\n", "[5 rows x 2 columns]" ] } ], "prompt_number": 181 }, { "cell_type": "code", "collapsed": false, "input": [ "pd.concat([mb1, mb2], axis=1).values[:5]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 182, "text": [ "array([[ nan, 2.],\n", " [ nan, 14.],\n", " [ 7., 23.],\n", " [ nan, 1.],\n", " [ nan, 2.]])" ] } ], "prompt_number": 182 }, { "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", "collapsed": false, "input": [ "pd.concat([mb1, mb2], axis=1, join='inner').head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 183, "text": [ " 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 \n", "\n", "[5 rows x 2 columns]" ] } ], "prompt_number": 183 }, { "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", "collapsed": false, "input": [ "mb1.combine_first(mb2).head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 184, "text": [ " 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\n", "\n", "[5 rows x 1 columns]" ] } ], "prompt_number": 184 }, { "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", "collapsed": false, "input": [ "pd.concat(dict(patient1=mb1, patient2=mb2), axis=1).head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 185, "text": [ " 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 \n", "\n", "[5 rows x 2 columns]" ] } ], "prompt_number": 185 }, { "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": [ "## Reshaping DataFrame objects\n", "\n", "In the context of a single DataFrame, we are often interested in re-arranging the layout of our data. \n", "\n", "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", "collapsed": false, "input": [ "cdystonia = pd.read_csv(\"data/cdystonia.csv\", index_col=None)\n", "cdystonia.head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 186, "text": [ " 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\n", "\n", "[5 rows x 9 columns]" ] } ], "prompt_number": 186 }, { "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", "\n", "The `stack` method rotates the data frame so that columns are represented in rows:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "stacked = cdystonia.stack()\n", "stacked" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 187, "text": [ "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", "...\n", "630 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" ] } ], "prompt_number": 187 }, { "cell_type": "markdown", "metadata": {}, "source": [ "To complement this, `unstack` pivots from rows back to columns." ] }, { "cell_type": "code", "collapsed": false, "input": [ "stacked.unstack().head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 188, "text": [ " 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\n", "\n", "[5 rows x 9 columns]" ] } ], "prompt_number": 188 }, { "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", "collapsed": false, "input": [ "cdystonia2 = cdystonia.set_index(['patient','obs'])\n", "cdystonia2.head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 189, "text": [ " 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", "\n", "[5 rows x 7 columns]" ] } ], "prompt_number": 189 }, { "cell_type": "code", "collapsed": false, "input": [ "cdystonia2.index.is_unique" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 190, "text": [ "True" ] } ], "prompt_number": 190 }, { "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", "collapsed": false, "input": [ "twstrs_wide = cdystonia2['twstrs'].unstack('obs')\n", "twstrs_wide.head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 191, "text": [ "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", "\n", "[5 rows x 6 columns]" ] } ], "prompt_number": 191 }, { "cell_type": "code", "collapsed": false, "input": [ "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" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 192, "text": [ " 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\n", "\n", "[5 rows x 12 columns]" ] } ], "prompt_number": 192 }, { "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", "collapsed": false, "input": [ "cdystonia.set_index(['patient','site','id','treat','age','sex','week'])['twstrs'].unstack('week').head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 193, "text": [ "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\n", "\n", "[5 rows x 6 columns]" ] } ], "prompt_number": 193 }, { "cell_type": "markdown", "metadata": {}, "source": [ "To convert our \"wide\" format back to long, we can use the `melt` function, appropriately parameterized:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.melt(cdystonia_long, id_vars=['patient','site','id','treat','age','sex'], \n", " var_name='obs', value_name='twsters').head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 194, "text": [ " 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\n", "\n", "[5 rows x 8 columns]" ] } ], "prompt_number": 194 }, { "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": [ "## 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.\n", "\n", "### 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", "collapsed": false, "input": [ "vessels.duplicated(cols='names')" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 195, "text": [ "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", "...\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" ] } ], "prompt_number": 195 }, { "cell_type": "code", "collapsed": false, "input": [ "vessels.drop_duplicates(['names'])" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 196, "text": [ " 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", "298716 1 Mitchel N \n", "366235 1 Cape Domingo N \n", "439541 2 Canadian Warship 711/L3 Y \n", "453556 1 Us Govt Vessel N \n", "505843 1 I.w.haile N \n", "527918 1 Salvage Master N \n", "565026 1 Honcho N \n", "572329 1 Alexandra N \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", "298716 Unknown Unknown 1 \n", "366235 United States of America Domestic 1 \n", "439541 Unknown Unknown 2 \n", "453556 Unknown Unknown 1 \n", "505843 Unknown Unknown 1 \n", "527918 Unknown Unknown 1 \n", "565026 Singapore (Republic of) Foreign 1 \n", "572329 Tuvalu Foreign 1 \n", " ... ... ... \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", "298716 35.0 35 1 \n", "366235 207.0 207 1 \n", "439541 0.0/55.0 55 2 \n", "453556 208.0 208 1 \n", "505843 20.0 20 1 \n", "527918 20.0 20 1 \n", "565026 32.0 32 1 \n", "572329 40.0 40 1 \n", " ... ... ... \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", "298716 Towing \n", "366235 Cargo \n", "439541 MilOps/Unknown \n", "453556 Unknown \n", "505843 WIG \n", "527918 Fishing \n", "565026 Towing \n", "572329 BigTow \n", " ... \n", "\n", "[10253 rows x 10 columns]" ] } ], "prompt_number": 196 }, { "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", "collapsed": false, "input": [ "cdystonia.treat.value_counts()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 197, "text": [ "10000U 213\n", "5000U 211\n", "Placebo 207\n", "dtype: int64" ] } ], "prompt_number": 197 }, { "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", "collapsed": false, "input": [ "treatment_map = {'Placebo': 0, '5000U': 1, '10000U': 2}" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 198 }, { "cell_type": "code", "collapsed": false, "input": [ "cdystonia['treatment'] = cdystonia.treat.map(treatment_map)\n", "cdystonia.treatment" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 199, "text": [ "0 1\n", "1 1\n", "2 1\n", "3 1\n", "4 1\n", "5 1\n", "6 2\n", "7 2\n", "...\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" ] } ], "prompt_number": 199 }, { "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. " ] }, { "cell_type": "code", "collapsed": false, "input": [ "cdystonia2.treat.replace({'Placebo': 0, '5000U': 1, '10000U': 2})" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 200, "text": [ "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", "...\n", "108 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" ] } ], "prompt_number": 200 }, { "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", "collapsed": false, "input": [ "cdystonia_grouped = cdystonia.groupby(cdystonia.patient)" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 201 }, { "cell_type": "markdown", "metadata": {}, "source": [ "This *grouped* dataset is hard to visualize\n" ] }, { "cell_type": "code", "collapsed": false, "input": [ "cdystonia_grouped" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 202, "text": [ "" ] } ], "prompt_number": 202 }, { "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", "collapsed": false, "input": [ "for patient, group in cdystonia_grouped:\n", " print patient\n", " print group\n", " print" ], "language": "python", "metadata": {}, "outputs": [ { "output_type": "stream", "stream": "stdout", "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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[4 rows x 10 columns]\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 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[5 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[5 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[5 rows x 10 columns]\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", "[5 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[5 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[3 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[1 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\n", "\n", "88" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[5 rows x 10 columns]\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", "[5 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[5 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[5 rows x 10 columns]\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", "[5 rows x 10 columns]\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", "[6 rows x 10 columns]" ] }, { "output_type": "stream", "stream": "stdout", "text": [ "\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", "[6 rows x 10 columns]\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", "[4 rows x 10 columns]\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", "[6 rows x 10 columns]\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", "[5 rows x 10 columns]\n", "\n" ] } ], "prompt_number": 203 }, { "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", "
*(Source: \"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", "collapsed": false, "input": [ "cdystonia_grouped.agg(np.mean).head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 204, "text": [ " 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\n", "\n", "[5 rows x 8 columns]" ] } ], "prompt_number": 204 }, { "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", "collapsed": false, "input": [ "cdystonia_grouped.mean().head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 205, "text": [ " 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\n", "\n", "[5 rows x 8 columns]" ] } ], "prompt_number": 205 }, { "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", "collapsed": false, "input": [ "cdystonia_grouped.mean().add_suffix('_mean').head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 206, "text": [ " 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 \n", "\n", "[5 rows x 8 columns]" ] } ], "prompt_number": 206 }, { "cell_type": "code", "collapsed": false, "input": [ "# The median of the `twstrs` variable\n", "cdystonia_grouped['twstrs'].quantile(0.5)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 207, "text": [ "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", "...\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", "Length: 109, dtype: float64" ] } ], "prompt_number": 207 }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we wish, we can easily aggregate according to multiple keys:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "cdystonia.groupby(['week','site']).mean().head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 208, "text": [ " 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\n", "\n", "[5 rows x 6 columns]" ] } ], "prompt_number": 208 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Alternately, we can **transform** the data, using a function of our choice with the `transform` method:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "normalize = lambda x: (x - x.mean())/x.std()\n", "\n", "cdystonia_grouped.transform(normalize).head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 209, "text": [ " 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\n", "\n", "[5 rows x 8 columns]" ] } ], "prompt_number": 209 }, { "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", "collapsed": false, "input": [ "cdystonia_grouped['twstrs'].mean().head()" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 210, "text": [ "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" ] } ], "prompt_number": 210 }, { "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", "collapsed": false, "input": [ "chunks = dict(list(cdystonia_grouped))\n", "chunks[4]" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 211, "text": [ " 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", "[4 rows x 10 columns]" ] } ], "prompt_number": 211 }, { "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", "collapsed": false, "input": [ "dict(list(cdystonia.groupby(cdystonia.dtypes, axis=1)))" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 212, "text": [ "{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", " 12 3 1 0 1 3 64 44 1\n", " 13 3 2 2 1 3 64 20 1\n", " 14 3 3 4 1 3 64 23 1\n", " 15 3 4 8 1 3 64 26 1\n", " 16 3 5 12 1 3 64 35 1\n", " 17 3 6 16 1 3 64 35 1\n", " 18 4 1 0 1 4 59 53 0\n", " 19 4 2 2 1 4 59 61 0\n", " ... ... ... ... ... ... ... ...\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", " 12 5000U F\n", " 13 5000U F\n", " 14 5000U F\n", " 15 5000U F\n", " 16 5000U F\n", " 17 5000U F\n", " 18 Placebo F\n", " 19 Placebo F\n", " ... ...\n", " \n", " [631 rows x 2 columns]}" ] } ], "prompt_number": 212 }, { "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.\n", "\n", "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", "collapsed": false, "input": [ "def top(df, column, n=5):\n", " return df.sort_index(by=column, ascending=False)[:n]" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 213 }, { "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", "collapsed": false, "input": [ "top3segments = segments_merged.groupby('mmsi').apply(top, column='seg_length', n=3)[['names', 'seg_length']]\n", "top3segments" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 214, "text": [ " 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\n", " ... ...\n", "\n", "[29464 rows x 2 columns]" ] } ], "prompt_number": 214 }, { "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", "collapsed": false, "input": [ "top3segments.head(20)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 215, "text": [ " 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\n", "\n", "[20 rows x 2 columns]" ] } ], "prompt_number": 215 } ], "metadata": {} } ] }