P4DS4D; 06; Getting Your Data in Shape.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:180447c8c6ab529f8dcea9c20004af2c1ce63b5afdc587b27cf929a97830d33a"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Validating Your Data"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Figuring out what\u2019s in your data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from lxml import objectify\n",
      "import pandas as pd\n",
      "\n",
      "xml = objectify.parse(open('XMLData2.xml'))\n",
      "root = xml.getroot()\n",
      "df = pd.DataFrame(columns=('Number', 'String', 'Boolean'))\n",
      "\n",
      "for i in range(0,4):\n",
      "    obj = root.getchildren()[i].getchildren()\n",
      "    row = dict(zip(['Number', 'String', 'Boolean'], \n",
      "                   [obj[0].text, obj[1].text, \n",
      "                    obj[2].text]))\n",
      "    row_s = pd.Series(row)\n",
      "    row_s.name = i\n",
      "    df = df.append(row_s)\n",
      "    \n",
      "search = pd.DataFrame.duplicated(df)\n",
      "\n",
      "print df\n",
      "print\n",
      "print search[search == True]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "  Number  String Boolean\n",
        "0      1   First    True\n",
        "1      1   First    True\n",
        "2      2  Second   False\n",
        "3      3   Third    True\n",
        "\n",
        "1    True\n",
        "dtype: bool\n"
       ]
      }
     ],
     "prompt_number": 1
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Removing duplicates"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from lxml import objectify\n",
      "import pandas as pd\n",
      "\n",
      "xml = objectify.parse(open('XMLData2.xml'))\n",
      "root = xml.getroot()\n",
      "df = pd.DataFrame(columns=('Number', 'String', 'Boolean'))\n",
      "\n",
      "for i in range(0,4):\n",
      "    obj = root.getchildren()[i].getchildren()\n",
      "    row = dict(zip(['Number', 'String', 'Boolean'], \n",
      "                   [obj[0].text, obj[1].text, \n",
      "                    obj[2].text]))\n",
      "    row_s = pd.Series(row)\n",
      "    row_s.name = i\n",
      "    df = df.append(row_s)\n",
      "    \n",
      "print df.drop_duplicates()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "  Number  String Boolean\n",
        "0      1   First    True\n",
        "2      2  Second   False\n",
        "3      3   Third    True\n"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Creating a data map and data plan"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "df = pd.DataFrame({'A': [0,0,0,0,0,1,1],\n",
      "                   'B': [1,2,3,5,4,2,5],\n",
      "                   'C': [5,3,4,1,1,2,3]})\n",
      "\n",
      "a_group_desc = df.groupby('A').describe()\n",
      "print a_group_desc"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "                B         C\n",
        "A                          \n",
        "0 count  5.000000  5.000000\n",
        "  mean   3.000000  2.800000\n",
        "  std    1.581139  1.788854\n",
        "  min    1.000000  1.000000\n",
        "  25%    2.000000  1.000000\n",
        "  50%    3.000000  3.000000\n",
        "  75%    4.000000  4.000000\n",
        "  max    5.000000  5.000000\n",
        "1 count  2.000000  2.000000\n",
        "  mean   3.500000  2.500000\n",
        "  std    2.121320  0.707107\n",
        "  min    2.000000  2.000000\n",
        "  25%    2.750000  2.250000\n",
        "  50%    3.500000  2.500000\n",
        "  75%    4.250000  2.750000\n",
        "  max    5.000000  3.000000\n"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "unstacked = a_group_desc.unstack()\n",
      "print unstacked"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "      B                                             C                     \\\n",
        "  count mean       std min   25%  50%   75% max count mean       std min   \n",
        "A                                                                          \n",
        "0     5  3.0  1.581139   1  2.00  3.0  4.00   5     5  2.8  1.788854   1   \n",
        "1     2  3.5  2.121320   2  2.75  3.5  4.25   5     2  2.5  0.707107   2   \n",
        "\n",
        "                        \n",
        "    25%  50%   75% max  \n",
        "A                       \n",
        "0  1.00  3.0  4.00   5  \n",
        "1  2.25  2.5  2.75   3  \n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print unstacked.loc[:,(slice(None),['count','mean']),]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "      B          C     \n",
        "  count mean count mean\n",
        "A                      \n",
        "0     5  3.0     5  2.8\n",
        "1     2  3.5     2  2.5\n"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Manipulating Categorical Variables"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Creating categorical variables"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "car_colors = pd.Series(['Blue', 'Red', 'Green'], dtype='category')\n",
      "\n",
      "car_data = pd.Series(\n",
      "    pd.Categorical(['Yellow', 'Green', 'Red', 'Blue', 'Purple'],\n",
      "                   categories=car_colors, ordered=False))\n",
      "\n",
      "find_entries = pd.isnull(car_data)\n",
      "\n",
      "print car_colors\n",
      "print\n",
      "print car_data\n",
      "print\n",
      "print find_entries[find_entries == True]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0     Blue\n",
        "1      Red\n",
        "2    Green\n",
        "dtype: category\n",
        "Categories (3, object): [Blue < Green < Red]\n",
        "\n",
        "0      NaN\n",
        "1    Green\n",
        "2      Red\n",
        "3     Blue\n",
        "4      NaN\n",
        "dtype: category\n",
        "Categories (3, object): [Blue, Red, Green]\n",
        "\n",
        "0    True\n",
        "4    True\n",
        "dtype: bool\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Renaming levels"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "car_colors = pd.Series(['Blue', 'Red', 'Green'], \n",
      "                       dtype='category')\n",
      "car_data = pd.Series(\n",
      "    pd.Categorical(\n",
      "        ['Blue', 'Green', 'Red', 'Blue', 'Red'],\n",
      "        categories=car_colors, ordered=False))\n",
      "\n",
      "\n",
      "car_colors.cat.categories = [\"Purple\", \"Yellow\", \"Mauve\"]\n",
      "car_data.cat.categories = car_colors\n",
      "\n",
      "print car_data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0    Purple\n",
        "1    Yellow\n",
        "2     Mauve\n",
        "3    Purple\n",
        "4     Mauve\n",
        "dtype: category\n",
        "Categories (3, object): [Purple, Mauve, Yellow]\n"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Combining levels"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "car_colors = pd.Series(['Blue', 'Red', 'Green'], \n",
      "                       dtype='category')\n",
      "car_data = pd.Series(\n",
      "    pd.Categorical(\n",
      "        ['Blue', 'Green', 'Red', 'Green', 'Red', 'Green'],\n",
      "        categories=car_colors, ordered=False))\n",
      "\n",
      "car_data.cat.categories = [\"Blue_Red\", \"Red\", \"Green\"]\n",
      "print car_data.ix[car_data.isin(['Red'])]\n",
      "\n",
      "car_data.ix[car_data.isin(['Red'])] = 'Blue_Red'\n",
      "\n",
      "print\n",
      "print car_data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2    Red\n",
        "4    Red\n",
        "dtype: category\n",
        "Categories (3, object): [Blue_Red, Red, Green]\n",
        "\n",
        "0    Blue_Red\n",
        "1       Green\n",
        "2    Blue_Red\n",
        "3       Green\n",
        "4    Blue_Red\n",
        "5       Green\n",
        "dtype: category\n",
        "Categories (3, object): [Blue_Red, Red, Green]\n"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Dealing with Dates in Your Data"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Formatting time values"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import datetime as dt\n",
      "\n",
      "now = dt.datetime.now()\n",
      "\n",
      "print str(now)\n",
      "print now.strftime('%a, %d %B %Y')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2015-04-19 18:00:08.427000\n",
        "Sun, 19 April 2015\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Using the right time transformation"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import datetime as dt\n",
      "\n",
      "now = dt.datetime.now()\n",
      "timevalue = now + dt.timedelta(hours=2)\n",
      "\n",
      "print now.strftime('%H:%M:%S')\n",
      "print timevalue.strftime('%H:%M:%S')\n",
      "print timevalue - now"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "18:00:10\n",
        "20:00:10\n",
        "2:00:00\n"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Dealing with Missing Data"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Finding out missing data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as np\n",
      "\n",
      "s = pd.Series([1, 2, 3, np.NaN, 5, 6, None])\n",
      "\n",
      "print s.isnull()\n",
      "\n",
      "print\n",
      "print s[s.isnull()]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0    False\n",
        "1    False\n",
        "2    False\n",
        "3     True\n",
        "4    False\n",
        "5    False\n",
        "6     True\n",
        "dtype: bool\n",
        "\n",
        "3   NaN\n",
        "6   NaN\n",
        "dtype: float64\n"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Encoding missingness"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as np\n",
      "\n",
      "s = pd.Series([1, 2, 3, np.NaN, 5, 6, None])\n",
      "\n",
      "print s.fillna(int(s.mean()))\n",
      "print\n",
      "print s.dropna()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0    1\n",
        "1    2\n",
        "2    3\n",
        "3    3\n",
        "4    5\n",
        "5    6\n",
        "6    3\n",
        "dtype: float64\n",
        "\n",
        "0    1\n",
        "1    2\n",
        "2    3\n",
        "4    5\n",
        "5    6\n",
        "dtype: float64\n"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Imputing missing data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as np\n",
      "from sklearn.preprocessing import Imputer\n",
      "\n",
      "s = pd.Series([1, 2, 3, np.NaN, 5, 6, None])\n",
      "\n",
      "imp = Imputer(missing_values='NaN', \n",
      "              strategy='mean', axis=0)\n",
      "\n",
      "imp.fit([1, 2, 3, 4, 5, 6, 7])\n",
      "\n",
      "x = pd.Series(imp.transform(s).tolist()[0])\n",
      "\n",
      "print x"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "0    1\n",
        "1    2\n",
        "2    3\n",
        "3    4\n",
        "4    5\n",
        "5    6\n",
        "6    7\n",
        "dtype: float64\n"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Slicing and Dicing"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Slicing rows"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "x = np.array([[[1, 2, 3],  [4, 5, 6],  [7, 8, 9],],\n",
      "              [[11,12,13], [14,15,16], [17,18,19],],\n",
      "              [[21,22,23], [24,25,26], [27,28,29]]])\n",
      "\n",
      "x[1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 14,
       "text": [
        "array([[11, 12, 13],\n",
        "       [14, 15, 16],\n",
        "       [17, 18, 19]])"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Slicing columns"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "x = np.array([[[1, 2, 3],  [4, 5, 6],  [7, 8, 9],],\n",
      "              [[11,12,13], [14,15,16], [17,18,19],],\n",
      "              [[21,22,23], [24,25,26], [27,28,29]]])\n",
      "\n",
      "x[:,1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "array([[ 4,  5,  6],\n",
        "       [14, 15, 16],\n",
        "       [24, 25, 26]])"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Dicing"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "x = np.array([[[1, 2, 3],  [4, 5, 6],  [7, 8, 9],],\n",
      "              [[11,12,13], [14,15,16], [17,18,19],],\n",
      "              [[21,22,23], [24,25,26], [27,28,29]]])\n",
      "\n",
      "print x[1,1]\n",
      "print x[:,1,1]\n",
      "print x[1,:,1]\n",
      "print\n",
      "print x[1:3, 1:3]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[14 15 16]\n",
        "[ 5 15 25]\n",
        "[12 15 18]\n",
        "\n",
        "[[[14 15 16]\n",
        "  [17 18 19]]\n",
        "\n",
        " [[24 25 26]\n",
        "  [27 28 29]]]\n"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Concatenating and Transforming"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Adding new cases and variables"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "df = pd.DataFrame({'A': [2,3,1],\n",
      "                   'B': [1,2,3],\n",
      "                   'C': [5,3,4]})\n",
      "\n",
      "df1 = pd.DataFrame({'A': [4],\n",
      "                    'B': [4],\n",
      "                    'C': [4]})\n",
      "\n",
      "df = df.append(df1)\n",
      "df = df.reset_index(drop=True)\n",
      "print df\n",
      "\n",
      "df.loc[df.last_valid_index() + 1] = [5, 5, 5]\n",
      "print\n",
      "print df\n",
      "\n",
      "df2 = pd.DataFrame({'D': [1, 2, 3, 4, 5]})\n",
      "\n",
      "df = pd.DataFrame.join(df, df2)\n",
      "print\n",
      "print df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "   A  B  C\n",
        "0  2  1  5\n",
        "1  3  2  3\n",
        "2  1  3  4\n",
        "3  4  4  4\n",
        "\n",
        "   A  B  C\n",
        "0  2  1  5\n",
        "1  3  2  3\n",
        "2  1  3  4\n",
        "3  4  4  4\n",
        "4  5  5  5\n",
        "\n",
        "   A  B  C  D\n",
        "0  2  1  5  1\n",
        "1  3  2  3  2\n",
        "2  1  3  4  3\n",
        "3  4  4  4  4\n",
        "4  5  5  5  5\n"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Removing data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "df = pd.DataFrame({'A': [2,3,1],\n",
      "                   'B': [1,2,3],\n",
      "                   'C': [5,3,4]})\n",
      "\n",
      "df = df.drop(df.index[[1]])\n",
      "print df\n",
      "\n",
      "df = df.drop('B', 1)\n",
      "print\n",
      "print df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "   A  B  C\n",
        "0  2  1  5\n",
        "2  1  3  4\n",
        "\n",
        "   A  C\n",
        "0  2  5\n",
        "2  1  4\n"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Sorting and shuffling"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as np\n",
      "\n",
      "df = pd.DataFrame({'A': [2,1,2,3,3,5,4],\n",
      "                   'B': [1,2,3,5,4,2,5],\n",
      "                   'C': [5,3,4,1,1,2,3]})\n",
      "\n",
      "df = df.sort_index(by=['A', 'B'], ascending=[True, True])\n",
      "df = df.reset_index(drop=True)\n",
      "print df\n",
      "\n",
      "index = df.index.tolist()\n",
      "np.random.shuffle(index)\n",
      "df = df.ix[index]\n",
      "df = df.reset_index(drop=True)\n",
      "print\n",
      "print df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "   A  B  C\n",
        "0  1  2  3\n",
        "1  2  1  5\n",
        "2  2  3  4\n",
        "3  3  4  1\n",
        "4  3  5  1\n",
        "5  4  5  3\n",
        "6  5  2  2\n",
        "\n",
        "   A  B  C\n",
        "0  2  1  5\n",
        "1  5  2  2\n",
        "2  4  5  3\n",
        "3  3  5  1\n",
        "4  3  4  1\n",
        "5  1  2  3\n",
        "6  2  3  4\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Aggregating Data at Any Level"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "df = pd.DataFrame({'Map': [0,0,0,1,1,2,2],\n",
      "                   'Values': [1,2,3,5,4,2,5]})\n",
      "\n",
      "df['S'] = df.groupby('Map')['Values'].transform(np.sum)\n",
      "df['M'] = df.groupby('Map')['Values'].transform(np.mean)\n",
      "df['V'] = df.groupby('Map')['Values'].transform(np.var)\n",
      "\n",
      "print df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "   Map  Values  S    M    V\n",
        "0    0       1  6  2.0  1.0\n",
        "1    0       2  6  2.0  1.0\n",
        "2    0       3  6  2.0  1.0\n",
        "3    1       5  9  4.5  0.5\n",
        "4    1       4  9  4.5  0.5\n",
        "5    2       2  7  3.5  4.5\n",
        "6    2       5  7  3.5  4.5\n"
       ]
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}