class7 - Pandas - Grouping, Merging & Reshaping Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping\n",
    "* groupby()\n",
    "* out = df.groupby('')\n",
    "* out.groups\n",
    "* out.size\n",
    "use these func to access output\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv(r'C:\\Users\\omc\\Desktop\\Data Science\\Populationoutput.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>State</th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "      <th>Total</th>\n",
       "      <th>Male .1</th>\n",
       "      <th>Female .1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>Male_female</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>88695</td>\n",
       "      <td>85828</td>\n",
       "      <td>174523</td>\n",
       "      <td>39439</td>\n",
       "      <td>38352</td>\n",
       "      <td>77791</td>\n",
       "      <td>174523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                      State    Male   Female     Total  Male .1  \\\n",
       "0           0  Andaman & Nicobar Islands    17547    17768    35315    11034   \n",
       "1           1             Andhra Pradesh  2165879  2032435  4198320  1309000   \n",
       "2           2          Arunachal Pradesh    88695    85828   174523    39439   \n",
       "3           3                      Assam  1792270  1714270  3506540   944482   \n",
       "4           0  Andaman & Nicobar Islands    17547    17768    35315    11034   \n",
       "5           1             Andhra Pradesh  2165879  2032435  4198320  1309000   \n",
       "6           3                      Assam  1792270  1714270  3506540   944482   \n",
       "\n",
       "   Female .1  Total.1  Male_female  \n",
       "0      11198    22232        35315  \n",
       "1    1229445  2538440      4198314  \n",
       "2      38352    77791       174523  \n",
       "3     894428  1838910      3506540  \n",
       "4      11198    22232        35315  \n",
       "5    1229445  2538440      4198314  \n",
       "6     894428  1838910      3506540  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "del data['Unnamed: 0']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "datagb = data.groupby('State')\n",
    "#returns grouby object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State\n",
       "Andaman & Nicobar Islands    2\n",
       "Andhra Pradesh               2\n",
       "Arunachal Pradesh            1\n",
       "Assam                        2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datagb.size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Andaman & Nicobar Islands': Int64Index([0, 4], dtype='int64'),\n",
       " 'Andhra Pradesh': Int64Index([1, 5], dtype='int64'),\n",
       " 'Arunachal Pradesh': Int64Index([2], dtype='int64'),\n",
       " 'Assam': Int64Index([3, 6], dtype='int64')}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datagb.groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "l = ['01/01/2017','02/01/2017','23/04/']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "out = lambda l: l.split('/')[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<function <lambda> at 0x00000000092F0730>\n"
     ]
    }
   ],
   "source": [
    "print(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State\n",
       "Andaman & Nicobar Islands    2\n",
       "Andhra Pradesh               2\n",
       "Arunachal Pradesh            1\n",
       "Assam                        2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datagb.size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#groupby ->date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#to fetch months from all years\n",
    "l = ['01/01/2017','02/01/2017','23/04/2017']\n",
    "s=[]\n",
    "for i in l:\n",
    "    out = lambda i :i.split('/')[1]\n",
    "    s.append(out)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "      <th>Total</th>\n",
       "      <th>Male .1</th>\n",
       "      <th>Female .1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>Male_female</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>88695</td>\n",
       "      <td>85828</td>\n",
       "      <td>174523</td>\n",
       "      <td>39439</td>\n",
       "      <td>38352</td>\n",
       "      <td>77791</td>\n",
       "      <td>174523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State    Male   Female     Total  Male .1  Female .1  \\\n",
       "0  Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "1             Andhra Pradesh  2165879  2032435  4198320  1309000    1229445   \n",
       "2          Arunachal Pradesh    88695    85828   174523    39439      38352   \n",
       "3                      Assam  1792270  1714270  3506540   944482     894428   \n",
       "4  Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "5             Andhra Pradesh  2165879  2032435  4198320  1309000    1229445   \n",
       "6                      Assam  1792270  1714270  3506540   944482     894428   \n",
       "\n",
       "   Total.1  Male_female  \n",
       "0    22232        35315  \n",
       "1  2538440      4198314  \n",
       "2    77791       174523  \n",
       "3  1838910      3506540  \n",
       "4    22232        35315  \n",
       "5  2538440      4198314  \n",
       "6  1838910      3506540  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#football example -> pls refer to www.github.com/zekelabs/data-science-ml-foundation-10 \n",
    "#file name -> New-7 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "      <th>Total</th>\n",
       "      <th>Male .1</th>\n",
       "      <th>Female .1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>Male_female</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>88695</td>\n",
       "      <td>85828</td>\n",
       "      <td>174523</td>\n",
       "      <td>39439</td>\n",
       "      <td>38352</td>\n",
       "      <td>77791</td>\n",
       "      <td>174523</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State    Male   Female     Total  Male .1  Female .1  \\\n",
       "0  Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "1             Andhra Pradesh  2165879  2032435  4198320  1309000    1229445   \n",
       "2          Arunachal Pradesh    88695    85828   174523    39439      38352   \n",
       "\n",
       "   Total.1  Male_female  \n",
       "0    22232        35315  \n",
       "1  2538440      4198314  \n",
       "2    77791       174523  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "      <th>Total</th>\n",
       "      <th>Male .1</th>\n",
       "      <th>Female .1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>Male_female</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State    Male   Female     Total  Male .1  Female .1  \\\n",
       "4  Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "5             Andhra Pradesh  2165879  2032435  4198320  1309000    1229445   \n",
       "6                      Assam  1792270  1714270  3506540   944482     894428   \n",
       "\n",
       "   Total.1  Male_female  \n",
       "4    22232        35315  \n",
       "5  2538440      4198314  \n",
       "6  1838910      3506540  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Filter function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Assam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Assam</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State\n",
       "0  Andaman & Nicobar Islands\n",
       "1             Andhra Pradesh\n",
       "2          Arunachal Pradesh\n",
       "3                      Assam\n",
       "4  Andaman & Nicobar Islands\n",
       "5             Andhra Pradesh\n",
       "6                      Assam"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.filter(items=['State'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on method filter in module pandas.core.generic:\n",
      "\n",
      "filter(items=None, like=None, regex=None, axis=None) method of pandas.core.frame.DataFrame instance\n",
      "    Subset rows or columns of dataframe according to labels in\n",
      "    the specified index.\n",
      "    \n",
      "    Note that this routine does not filter a dataframe on its\n",
      "    contents. The filter is applied to the labels of the index.\n",
      "    \n",
      "    Parameters\n",
      "    ----------\n",
      "    items : list-like\n",
      "        List of info axis to restrict to (must not all be present)\n",
      "    like : string\n",
      "        Keep info axis where \"arg in col == True\"\n",
      "    regex : string (regular expression)\n",
      "        Keep info axis with re.search(regex, col) == True\n",
      "    axis : int or string axis name\n",
      "        The axis to filter on.  By default this is the info axis,\n",
      "        'index' for Series, 'columns' for DataFrame\n",
      "    \n",
      "    Returns\n",
      "    -------\n",
      "    same type as input object\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df\n",
      "    one  two  three\n",
      "    mouse     1    2      3\n",
      "    rabbit    4    5      6\n",
      "    \n",
      "    >>> # select columns by name\n",
      "    >>> df.filter(items=['one', 'three'])\n",
      "    one  three\n",
      "    mouse     1      3\n",
      "    rabbit    4      6\n",
      "    \n",
      "    >>> # select columns by regular expression\n",
      "    >>> df.filter(regex='e$', axis=1)\n",
      "    one  three\n",
      "    mouse     1      3\n",
      "    rabbit    4      6\n",
      "    \n",
      "    >>> # select rows containing 'bbi'\n",
      "    >>> df.filter(like='bbi', axis=0)\n",
      "    one  two  three\n",
      "    rabbit    4    5      6\n",
      "    \n",
      "    See Also\n",
      "    --------\n",
      "    pandas.DataFrame.select\n",
      "    \n",
      "    Notes\n",
      "    -----\n",
      "    The ``items``, ``like``, and ``regex`` parameters are\n",
      "    enforced to be mutually exclusive.\n",
      "    \n",
      "    ``axis`` defaults to the info axis that is used when indexing\n",
      "    with ``[]``.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(data.filter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "      <th>Total</th>\n",
       "      <th>Male .1</th>\n",
       "      <th>Female .1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>Male_female</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>88695</td>\n",
       "      <td>85828</td>\n",
       "      <td>174523</td>\n",
       "      <td>39439</td>\n",
       "      <td>38352</td>\n",
       "      <td>77791</td>\n",
       "      <td>174523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State    Male   Female     Total  Male .1  Female .1  \\\n",
       "0  Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "1             Andhra Pradesh  2165879  2032435  4198320  1309000    1229445   \n",
       "2          Arunachal Pradesh    88695    85828   174523    39439      38352   \n",
       "3                      Assam  1792270  1714270  3506540   944482     894428   \n",
       "4  Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "\n",
       "   Total.1  Male_female  \n",
       "0    22232        35315  \n",
       "1  2538440      4198314  \n",
       "2    77791       174523  \n",
       "3  1838910      3506540  \n",
       "4    22232        35315  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>88695</td>\n",
       "      <td>85828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State    Male   Female \n",
       "0  Andaman & Nicobar Islands    17547    17768\n",
       "1             Andhra Pradesh  2165879  2032435\n",
       "2          Arunachal Pradesh    88695    85828\n",
       "3                      Assam  1792270  1714270\n",
       "4  Andaman & Nicobar Islands    17547    17768\n",
       "5             Andhra Pradesh  2165879  2032435\n",
       "6                      Assam  1792270  1714270"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.filter(items=['State','Male ','Female '])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "      <th>Total</th>\n",
       "      <th>Male .1</th>\n",
       "      <th>Female .1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>Male_female</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Andaman &amp; Nicobar Islands</th>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andhra Pradesh</th>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arunachal Pradesh</th>\n",
       "      <td>88695</td>\n",
       "      <td>85828</td>\n",
       "      <td>174523</td>\n",
       "      <td>39439</td>\n",
       "      <td>38352</td>\n",
       "      <td>77791</td>\n",
       "      <td>174523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assam</th>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andaman &amp; Nicobar Islands</th>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andhra Pradesh</th>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assam</th>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Male   Female     Total  Male .1  Female .1  \\\n",
       "State                                                                      \n",
       "Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "Andhra Pradesh             2165879  2032435  4198320  1309000    1229445   \n",
       "Arunachal Pradesh            88695    85828   174523    39439      38352   \n",
       "Assam                      1792270  1714270  3506540   944482     894428   \n",
       "Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "Andhra Pradesh             2165879  2032435  4198320  1309000    1229445   \n",
       "Assam                      1792270  1714270  3506540   944482     894428   \n",
       "\n",
       "                           Total.1  Male_female  \n",
       "State                                            \n",
       "Andaman & Nicobar Islands    22232        35315  \n",
       "Andhra Pradesh             2538440      4198314  \n",
       "Arunachal Pradesh            77791       174523  \n",
       "Assam                      1838910      3506540  \n",
       "Andaman & Nicobar Islands    22232        35315  \n",
       "Andhra Pradesh             2538440      4198314  \n",
       "Assam                      1838910      3506540  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.set_index('State')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ".filter(regex='')\n",
    ".filter(like='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Help in Pandas -> df.help()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Difference Between ix and loc iloc\n",
    "* loc - accessing by value\n",
    "* iloc - accessing by index\n",
    "* ix - first by value and then index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "      <th>Total</th>\n",
       "      <th>Male .1</th>\n",
       "      <th>Female .1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>Male_female</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>88695</td>\n",
       "      <td>85828</td>\n",
       "      <td>174523</td>\n",
       "      <td>39439</td>\n",
       "      <td>38352</td>\n",
       "      <td>77791</td>\n",
       "      <td>174523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Assam</td>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andaman &amp; Nicobar Islands</td>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       State    Male   Female     Total  Male .1  Female .1  \\\n",
       "0  Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "1             Andhra Pradesh  2165879  2032435  4198320  1309000    1229445   \n",
       "2          Arunachal Pradesh    88695    85828   174523    39439      38352   \n",
       "3                      Assam  1792270  1714270  3506540   944482     894428   \n",
       "4  Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "\n",
       "   Total.1  Male_female  \n",
       "0    22232        35315  \n",
       "1  2538440      4198314  \n",
       "2    77791       174523  \n",
       "3  1838910      3506540  \n",
       "4    22232        35315  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Male</th>\n",
       "      <th>Female</th>\n",
       "      <th>Total</th>\n",
       "      <th>Male .1</th>\n",
       "      <th>Female .1</th>\n",
       "      <th>Total.1</th>\n",
       "      <th>Male_female</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Andaman &amp; Nicobar Islands</th>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andhra Pradesh</th>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arunachal Pradesh</th>\n",
       "      <td>88695</td>\n",
       "      <td>85828</td>\n",
       "      <td>174523</td>\n",
       "      <td>39439</td>\n",
       "      <td>38352</td>\n",
       "      <td>77791</td>\n",
       "      <td>174523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assam</th>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andaman &amp; Nicobar Islands</th>\n",
       "      <td>17547</td>\n",
       "      <td>17768</td>\n",
       "      <td>35315</td>\n",
       "      <td>11034</td>\n",
       "      <td>11198</td>\n",
       "      <td>22232</td>\n",
       "      <td>35315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andhra Pradesh</th>\n",
       "      <td>2165879</td>\n",
       "      <td>2032435</td>\n",
       "      <td>4198320</td>\n",
       "      <td>1309000</td>\n",
       "      <td>1229445</td>\n",
       "      <td>2538440</td>\n",
       "      <td>4198314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assam</th>\n",
       "      <td>1792270</td>\n",
       "      <td>1714270</td>\n",
       "      <td>3506540</td>\n",
       "      <td>944482</td>\n",
       "      <td>894428</td>\n",
       "      <td>1838910</td>\n",
       "      <td>3506540</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Male   Female     Total  Male .1  Female .1  \\\n",
       "State                                                                      \n",
       "Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "Andhra Pradesh             2165879  2032435  4198320  1309000    1229445   \n",
       "Arunachal Pradesh            88695    85828   174523    39439      38352   \n",
       "Assam                      1792270  1714270  3506540   944482     894428   \n",
       "Andaman & Nicobar Islands    17547    17768    35315    11034      11198   \n",
       "Andhra Pradesh             2165879  2032435  4198320  1309000    1229445   \n",
       "Assam                      1792270  1714270  3506540   944482     894428   \n",
       "\n",
       "                           Total.1  Male_female  \n",
       "State                                            \n",
       "Andaman & Nicobar Islands    22232        35315  \n",
       "Andhra Pradesh             2538440      4198314  \n",
       "Arunachal Pradesh            77791       174523  \n",
       "Assam                      1838910      3506540  \n",
       "Andaman & Nicobar Islands    22232        35315  \n",
       "Andhra Pradesh             2538440      4198314  \n",
       "Assam                      1838910      3506540  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.set_index('State')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## FootBall Dataset\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "football_data = pd.read_csv(r'cl_13_14.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>home</th>\n",
       "      <th>homescore</th>\n",
       "      <th>awayscore</th>\n",
       "      <th>away</th>\n",
       "      <th>date</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Borussia Dortmund</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Zenit St Petersburg</td>\n",
       "      <td>Wednesday, March 19, 2014</td>\n",
       "      <td>UEFA CHAMPIONS LEAGUE - ROUND OF 16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Manchester United</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Olympiakos</td>\n",
       "      <td>Wednesday, March 19, 2014</td>\n",
       "      <td>UEFA CHAMPIONS LEAGUE - ROUND OF 16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chelsea</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>Galatasaray</td>\n",
       "      <td>Tuesday, March 18, 2014</td>\n",
       "      <td>UEFA CHAMPIONS LEAGUE - ROUND OF 16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Real Madrid</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Schalke 04</td>\n",
       "      <td>Tuesday, March 18, 2014</td>\n",
       "      <td>UEFA CHAMPIONS LEAGUE - ROUND OF 16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Barcelona</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Manchester City</td>\n",
       "      <td>Wednesday, March 12, 2014</td>\n",
       "      <td>UEFA CHAMPIONS LEAGUE - ROUND OF 16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                home  homescore  awayscore                 away  \\\n",
       "0  Borussia Dortmund          1          2  Zenit St Petersburg   \n",
       "1  Manchester United          3          0           Olympiakos   \n",
       "2            Chelsea          2          0          Galatasaray   \n",
       "3        Real Madrid          3          1           Schalke 04   \n",
       "4          Barcelona          2          1      Manchester City   \n",
       "\n",
       "                        date                                group  \n",
       "0  Wednesday, March 19, 2014  UEFA CHAMPIONS LEAGUE - ROUND OF 16  \n",
       "1  Wednesday, March 19, 2014  UEFA CHAMPIONS LEAGUE - ROUND OF 16  \n",
       "2    Tuesday, March 18, 2014  UEFA CHAMPIONS LEAGUE - ROUND OF 16  \n",
       "3    Tuesday, March 18, 2014  UEFA CHAMPIONS LEAGUE - ROUND OF 16  \n",
       "4  Wednesday, March 12, 2014  UEFA CHAMPIONS LEAGUE - ROUND OF 16  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "football_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1200"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "football_data.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "footballgb = football_data.groupby('home')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AC Milan': Int64Index([12, 23, 78, 102, 113], dtype='int64'),\n",
       " 'Ajax Amsterdam': Int64Index([46, 54, 94], dtype='int64'),\n",
       " 'Anderlecht': Int64Index([36, 68, 84], dtype='int64'),\n",
       " 'Apoel Nicosia': Int64Index([147], dtype='int64'),\n",
       " 'Arsenal': Int64Index([13, 42, 74, 90, 117], dtype='int64'),\n",
       " 'Atletico Madrid': Int64Index([6, 21, 53, 100], dtype='int64'),\n",
       " 'Austria Vienna': Int64Index([22, 76, 101, 118, 153], dtype='int64'),\n",
       " 'BATE Borisov': Int64Index([189], dtype='int64'),\n",
       " 'Barcelona': Int64Index([4, 24, 55, 103], dtype='int64'),\n",
       " 'Bayer Leverkusen': Int64Index([14, 32, 64, 80], dtype='int64'),\n",
       " 'Bayern Munich': Int64Index([7, 30, 71, 110], dtype='int64'),\n",
       " 'Benfica': Int64Index([28, 69, 108], dtype='int64'),\n",
       " 'Birkirkara': Int64Index([193], dtype='int64'),\n",
       " 'Borussia Dortmund': Int64Index([0, 43, 50, 91], dtype='int64'),\n",
       " 'CS Fola Esch': Int64Index([194], dtype='int64'),\n",
       " 'CSKA Moscow': Int64Index([38, 70, 86], dtype='int64'),\n",
       " 'Celtic': Int64Index([47, 79, 95, 114, 151, 174], dtype='int64'),\n",
       " 'Chelsea': Int64Index([2, 17, 48, 96], dtype='int64'),\n",
       " 'Cliftonville': Int64Index([187], dtype='int64'),\n",
       " 'Dinamo Tbilisi': Int64Index([154, 190], dtype='int64'),\n",
       " 'Dinamo Zagreb': Int64Index([122, 159, 175], dtype='int64'),\n",
       " 'EB Streymur': Int64Index([171, 196], dtype='int64'),\n",
       " 'Elfsborg': Int64Index([136, 182], dtype='int64'),\n",
       " 'FC Basel': Int64Index([40, 49, 88, 119, 156], dtype='int64'),\n",
       " 'FC Copenhagen': Int64Index([27, 58, 106], dtype='int64'),\n",
       " 'FC Daugava': Int64Index([167], dtype='int64'),\n",
       " 'FC Metalist Kharkiv': Int64Index([134], dtype='int64'),\n",
       " 'FC Nordsjaelland': Int64Index([157], dtype='int64'),\n",
       " 'FC Porto': Int64Index([45, 77, 93], dtype='int64'),\n",
       " 'FH Hafnarfjordur': Int64Index([132, 178], dtype='int64'),\n",
       " 'FK Ekranas': Int64Index([191], dtype='int64'),\n",
       " 'Fenerbahce': Int64Index([123, 144], dtype='int64'),\n",
       " 'Galatasaray': Int64Index([8, 16, 66, 107], dtype='int64'),\n",
       " 'Grasshoppers': Int64Index([146], dtype='int64'),\n",
       " 'Gyor': Int64Index([186], dtype='int64'),\n",
       " 'HJK Helsinki': Int64Index([180], dtype='int64'),\n",
       " 'Juventus': Int64Index([34, 59, 82], dtype='int64'),\n",
       " 'Legia Warsaw': Int64Index([120, 138, 164], dtype='int64'),\n",
       " 'Ludogorets Razgrad': Int64Index([124, 149, 162], dtype='int64'),\n",
       " 'Lusitanos': Int64Index([199], dtype='int64'),\n",
       " 'Lyon': Int64Index([128, 161], dtype='int64'),\n",
       " 'Maccabi Tel-Aviv': Int64Index([140, 173], dtype='int64'),\n",
       " 'Manchester City': Int64Index([15, 39, 62, 87], dtype='int64'),\n",
       " 'Manchester United': Int64Index([1, 25, 65, 104], dtype='int64'),\n",
       " 'Marseille': Int64Index([19, 75, 98], dtype='int64'),\n",
       " 'Molde': Int64Index([148, 170], dtype='int64'),\n",
       " 'NK Maribor': Int64Index([115, 142, 163], dtype='int64'),\n",
       " 'Napoli': Int64Index([20, 51, 99], dtype='int64'),\n",
       " 'Neftchi': Int64Index([181], dtype='int64'),\n",
       " 'Nomme Kalju': Int64Index([155, 168], dtype='int64'),\n",
       " 'Olympiakos': Int64Index([11, 29, 60, 109], dtype='int64'),\n",
       " 'PAOK Salonika': Int64Index([121, 158], dtype='int64'),\n",
       " 'PSV Eindhoven': Int64Index([130, 160], dtype='int64'),\n",
       " 'Paris Saint-Germain ': Int64Index([5, 37, 61, 85], dtype='int64'),\n",
       " 'Partizan Belgrade': Int64Index([145, 165], dtype='int64'),\n",
       " 'Paços de Ferreira': Int64Index([129], dtype='int64'),\n",
       " 'Real Madrid': Int64Index([3, 35, 67, 83], dtype='int64'),\n",
       " 'Real Sociedad': Int64Index([26, 56, 105, 116], dtype='int64'),\n",
       " 'SV Salzburg': Int64Index([150], dtype='int64'),\n",
       " 'Schalke 04': Int64Index([9, 18, 72, 97, 125], dtype='int64'),\n",
       " 'Shakhtar Donetsk': Int64Index([33, 57, 81], dtype='int64'),\n",
       " 'Shakhter Karagandy': Int64Index([127, 152, 166], dtype='int64'),\n",
       " 'Sheriff Tiraspol': Int64Index([135, 192], dtype='int64'),\n",
       " 'Shirak': Int64Index([179, 198], dtype='int64'),\n",
       " 'Skenderbeu': Int64Index([143, 172], dtype='int64'),\n",
       " 'Sligo': Int64Index([183], dtype='int64'),\n",
       " 'Slovan Bratislava': Int64Index([184], dtype='int64'),\n",
       " 'Steaua Bucuresti': Int64Index([41, 73, 89, 126, 141, 195], dtype='int64'),\n",
       " 'Sutjeska': Int64Index([169], dtype='int64'),\n",
       " 'T.N.S.': Int64Index([185], dtype='int64'),\n",
       " 'Tre Penne': Int64Index([197], dtype='int64'),\n",
       " 'Vardar': Int64Index([176], dtype='int64'),\n",
       " 'Viktoria Plzen': Int64Index([31, 63, 111, 131, 137, 188], dtype='int64'),\n",
       " 'Zeljeznicar Sarajevo': Int64Index([177], dtype='int64'),\n",
       " 'Zenit St Petersburg': Int64Index([10, 44, 52, 92, 112, 133], dtype='int64'),\n",
       " 'Zulte-Waregem': Int64Index([139], dtype='int64')}"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "footballgb.groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "home\n",
       "AC Milan                5\n",
       "Ajax Amsterdam          3\n",
       "Anderlecht              3\n",
       "Apoel Nicosia           1\n",
       "Arsenal                 5\n",
       "Atletico Madrid         4\n",
       "Austria Vienna          5\n",
       "BATE Borisov            1\n",
       "Barcelona               4\n",
       "Bayer Leverkusen        4\n",
       "Bayern Munich           4\n",
       "Benfica                 3\n",
       "Birkirkara              1\n",
       "Borussia Dortmund       4\n",
       "CS Fola Esch            1\n",
       "CSKA Moscow             3\n",
       "Celtic                  6\n",
       "Chelsea                 4\n",
       "Cliftonville            1\n",
       "Dinamo Tbilisi          2\n",
       "Dinamo Zagreb           3\n",
       "EB Streymur             2\n",
       "Elfsborg                2\n",
       "FC Basel                5\n",
       "FC Copenhagen           3\n",
       "FC Daugava              1\n",
       "FC Metalist Kharkiv     1\n",
       "FC Nordsjaelland        1\n",
       "FC Porto                3\n",
       "FH Hafnarfjordur        2\n",
       "                       ..\n",
       "NK Maribor              3\n",
       "Napoli                  3\n",
       "Neftchi                 1\n",
       "Nomme Kalju             2\n",
       "Olympiakos              4\n",
       "PAOK Salonika           2\n",
       "PSV Eindhoven           2\n",
       "Paris Saint-Germain     4\n",
       "Partizan Belgrade       2\n",
       "Paços de Ferreira       1\n",
       "Real Madrid             4\n",
       "Real Sociedad           4\n",
       "SV Salzburg             1\n",
       "Schalke 04              5\n",
       "Shakhtar Donetsk        3\n",
       "Shakhter Karagandy      3\n",
       "Sheriff Tiraspol        2\n",
       "Shirak                  2\n",
       "Skenderbeu              2\n",
       "Sligo                   1\n",
       "Slovan Bratislava       1\n",
       "Steaua Bucuresti        6\n",
       "Sutjeska                1\n",
       "T.N.S.                  1\n",
       "Tre Penne               1\n",
       "Vardar                  1\n",
       "Viktoria Plzen          6\n",
       "Zeljeznicar Sarajevo    1\n",
       "Zenit St Petersburg     6\n",
       "Zulte-Waregem           1\n",
       "Length: 76, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "footballgb.size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "groupedstatsdf = pd.read_csv('goal_stats_euro_leagues_2012-13.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Month</th>\n",
       "      <th>Stat</th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>08/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>38.0</td>\n",
       "      <td>39</td>\n",
       "      <td>50.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>31.0</td>\n",
       "      <td>31</td>\n",
       "      <td>39.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>50.0</td>\n",
       "      <td>41</td>\n",
       "      <td>42.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>59.0</td>\n",
       "      <td>39</td>\n",
       "      <td>39.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>01/01/2013</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>42.0</td>\n",
       "      <td>40</td>\n",
       "      <td>40.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>02/01/2013</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>30.0</td>\n",
       "      <td>40</td>\n",
       "      <td>40.0</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>03/01/2013</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>35.0</td>\n",
       "      <td>38</td>\n",
       "      <td>39.0</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>04/01/2013</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>42.0</td>\n",
       "      <td>42</td>\n",
       "      <td>41.0</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>05/01/2013</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>33.0</td>\n",
       "      <td>40</td>\n",
       "      <td>40.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>06/02/2013</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>08/01/2012</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>57.0</td>\n",
       "      <td>60</td>\n",
       "      <td>21.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>09/01/2012</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>111.0</td>\n",
       "      <td>112</td>\n",
       "      <td>133.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>95.0</td>\n",
       "      <td>88</td>\n",
       "      <td>97.0</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>11/01/2012</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>121.0</td>\n",
       "      <td>116</td>\n",
       "      <td>120.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>12/01/2012</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>183.0</td>\n",
       "      <td>109</td>\n",
       "      <td>125.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>01/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>117.0</td>\n",
       "      <td>121</td>\n",
       "      <td>104.0</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>02/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>87.0</td>\n",
       "      <td>110</td>\n",
       "      <td>100.0</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>03/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>91.0</td>\n",
       "      <td>101</td>\n",
       "      <td>99.0</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>04/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>105.0</td>\n",
       "      <td>127</td>\n",
       "      <td>102.0</td>\n",
       "      <td>104.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>05/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>96.0</td>\n",
       "      <td>109</td>\n",
       "      <td>102.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>06/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Month           Stat    EPL  La Liga  Serie A  Bundesliga\n",
       "0   08/01/2012  MatchesPlayed   20.0       20     10.0        10.0\n",
       "1   09/01/2012  MatchesPlayed   38.0       39     50.0        44.0\n",
       "2   10/01/2012  MatchesPlayed   31.0       31     39.0        27.0\n",
       "3   11/01/2012  MatchesPlayed   50.0       41     42.0        46.0\n",
       "4   12/01/2012  MatchesPlayed   59.0       39     39.0        26.0\n",
       "5   01/01/2013  MatchesPlayed   42.0       40     40.0        18.0\n",
       "6   02/01/2013  MatchesPlayed   30.0       40     40.0        36.0\n",
       "7   03/01/2013  MatchesPlayed   35.0       38     39.0        36.0\n",
       "8   04/01/2013  MatchesPlayed   42.0       42     41.0        36.0\n",
       "9   05/01/2013  MatchesPlayed   33.0       40     40.0        27.0\n",
       "10  06/02/2013  MatchesPlayed    NaN       10      NaN         NaN\n",
       "11  08/01/2012    GoalsScored   57.0       60     21.0        23.0\n",
       "12  09/01/2012    GoalsScored  111.0      112    133.0       135.0\n",
       "13  10/01/2012    GoalsScored   95.0       88     97.0        77.0\n",
       "14  11/01/2012    GoalsScored  121.0      116    120.0       137.0\n",
       "15  12/01/2012    GoalsScored  183.0      109    125.0        72.0\n",
       "16  01/01/2013    GoalsScored  117.0      121    104.0        51.0\n",
       "17  02/01/2013    GoalsScored   87.0      110    100.0       101.0\n",
       "18  03/01/2013    GoalsScored   91.0      101     99.0       106.0\n",
       "19  04/01/2013    GoalsScored  105.0      127    102.0       104.0\n",
       "20  05/01/2013    GoalsScored   96.0      109    102.0        92.0\n",
       "21  06/01/2013    GoalsScored    NaN       80      NaN         NaN"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupedstatsdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Month</th>\n",
       "      <th>Stat</th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>08/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>38.0</td>\n",
       "      <td>39</td>\n",
       "      <td>50.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>31.0</td>\n",
       "      <td>31</td>\n",
       "      <td>39.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Month           Stat   EPL  La Liga  Serie A  Bundesliga\n",
       "0  08/01/2012  MatchesPlayed  20.0       20     10.0        10.0\n",
       "1  09/01/2012  MatchesPlayed  38.0       39     50.0        44.0\n",
       "2  10/01/2012  MatchesPlayed  31.0       31     39.0        27.0"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupedstatsdf.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Month</th>\n",
       "      <th>Stat</th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>04/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>105.0</td>\n",
       "      <td>127</td>\n",
       "      <td>102.0</td>\n",
       "      <td>104.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>05/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>96.0</td>\n",
       "      <td>109</td>\n",
       "      <td>102.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>06/01/2013</td>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Month         Stat    EPL  La Liga  Serie A  Bundesliga\n",
       "19  04/01/2013  GoalsScored  105.0      127    102.0       104.0\n",
       "20  05/01/2013  GoalsScored   96.0      109    102.0        92.0\n",
       "21  06/01/2013  GoalsScored    NaN       80      NaN         NaN"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupedstatsdf.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "#With no specific column name mentioned, groupBy function takes index\n",
    "groupedstatsdf.set_index('Month',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "#With no specific column name mentioned, groupBy function takes index\n",
    "groupedByyear = groupedstatsdf.groupby(lambda Month: Month.split('/')[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.DataFrameGroupBy object at 0x00000000065F4A58>"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupedByyear"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2012                      Stat    EPL  La Liga  Serie A  Bundesliga\n",
      "Month                                                         \n",
      "08/01/2012  MatchesPlayed   20.0       20     10.0        10.0\n",
      "09/01/2012  MatchesPlayed   38.0       39     50.0        44.0\n",
      "10/01/2012  MatchesPlayed   31.0       31     39.0        27.0\n",
      "11/01/2012  MatchesPlayed   50.0       41     42.0        46.0\n",
      "12/01/2012  MatchesPlayed   59.0       39     39.0        26.0\n",
      "08/01/2012    GoalsScored   57.0       60     21.0        23.0\n",
      "09/01/2012    GoalsScored  111.0      112    133.0       135.0\n",
      "10/01/2012    GoalsScored   95.0       88     97.0        77.0\n",
      "11/01/2012    GoalsScored  121.0      116    120.0       137.0\n",
      "12/01/2012    GoalsScored  183.0      109    125.0        72.0\n",
      "2013                      Stat    EPL  La Liga  Serie A  Bundesliga\n",
      "Month                                                         \n",
      "01/01/2013  MatchesPlayed   42.0       40     40.0        18.0\n",
      "02/01/2013  MatchesPlayed   30.0       40     40.0        36.0\n",
      "03/01/2013  MatchesPlayed   35.0       38     39.0        36.0\n",
      "04/01/2013  MatchesPlayed   42.0       42     41.0        36.0\n",
      "05/01/2013  MatchesPlayed   33.0       40     40.0        27.0\n",
      "06/02/2013  MatchesPlayed    NaN       10      NaN         NaN\n",
      "01/01/2013    GoalsScored  117.0      121    104.0        51.0\n",
      "02/01/2013    GoalsScored   87.0      110    100.0       101.0\n",
      "03/01/2013    GoalsScored   91.0      101     99.0       106.0\n",
      "04/01/2013    GoalsScored  105.0      127    102.0       104.0\n",
      "05/01/2013    GoalsScored   96.0      109    102.0        92.0\n",
      "06/01/2013    GoalsScored    NaN       80      NaN         NaN\n"
     ]
    }
   ],
   "source": [
    "for name,group in groupedByyear:\n",
    "    print(name,group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "goal_data = pd.read_csv('goal_stats_euro_leagues_2012-13.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Month</th>\n",
       "      <th>Stat</th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>08/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>38.0</td>\n",
       "      <td>39</td>\n",
       "      <td>50.0</td>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>31.0</td>\n",
       "      <td>31</td>\n",
       "      <td>39.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>50.0</td>\n",
       "      <td>41</td>\n",
       "      <td>42.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12/01/2012</td>\n",
       "      <td>MatchesPlayed</td>\n",
       "      <td>59.0</td>\n",
       "      <td>39</td>\n",
       "      <td>39.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Month           Stat   EPL  La Liga  Serie A  Bundesliga\n",
       "0  08/01/2012  MatchesPlayed  20.0       20     10.0        10.0\n",
       "1  09/01/2012  MatchesPlayed  38.0       39     50.0        44.0\n",
       "2  10/01/2012  MatchesPlayed  31.0       31     39.0        27.0\n",
       "3  11/01/2012  MatchesPlayed  50.0       41     42.0        46.0\n",
       "4  12/01/2012  MatchesPlayed  59.0       39     39.0        26.0"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goal_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Stat column contains MatchesPlayed and Goalscored, i need to fetch Goalscoredonly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "goal_data.set_index(['Month'],inplace=True)\n",
    "goalsScoredDF = goal_data[goal_data['Stat'] == 'GoalsScored']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stat</th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>08/01/2012</th>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>57.0</td>\n",
       "      <td>60</td>\n",
       "      <td>21.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09/01/2012</th>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>111.0</td>\n",
       "      <td>112</td>\n",
       "      <td>133.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10/01/2012</th>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>95.0</td>\n",
       "      <td>88</td>\n",
       "      <td>97.0</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11/01/2012</th>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>121.0</td>\n",
       "      <td>116</td>\n",
       "      <td>120.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12/01/2012</th>\n",
       "      <td>GoalsScored</td>\n",
       "      <td>183.0</td>\n",
       "      <td>109</td>\n",
       "      <td>125.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Stat    EPL  La Liga  Serie A  Bundesliga\n",
       "Month                                                       \n",
       "08/01/2012  GoalsScored   57.0       60     21.0        23.0\n",
       "09/01/2012  GoalsScored  111.0      112    133.0       135.0\n",
       "10/01/2012  GoalsScored   95.0       88     97.0        77.0\n",
       "11/01/2012  GoalsScored  121.0      116    120.0       137.0\n",
       "12/01/2012  GoalsScored  183.0      109    125.0        72.0"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goalsScoredDF.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "goalsScoredDF = goalsScoredDF.iloc[:,1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>08/01/2012</th>\n",
       "      <td>57.0</td>\n",
       "      <td>60</td>\n",
       "      <td>21.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09/01/2012</th>\n",
       "      <td>111.0</td>\n",
       "      <td>112</td>\n",
       "      <td>133.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10/01/2012</th>\n",
       "      <td>95.0</td>\n",
       "      <td>88</td>\n",
       "      <td>97.0</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11/01/2012</th>\n",
       "      <td>121.0</td>\n",
       "      <td>116</td>\n",
       "      <td>120.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12/01/2012</th>\n",
       "      <td>183.0</td>\n",
       "      <td>109</td>\n",
       "      <td>125.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01/01/2013</th>\n",
       "      <td>117.0</td>\n",
       "      <td>121</td>\n",
       "      <td>104.0</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02/01/2013</th>\n",
       "      <td>87.0</td>\n",
       "      <td>110</td>\n",
       "      <td>100.0</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03/01/2013</th>\n",
       "      <td>91.0</td>\n",
       "      <td>101</td>\n",
       "      <td>99.0</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04/01/2013</th>\n",
       "      <td>105.0</td>\n",
       "      <td>127</td>\n",
       "      <td>102.0</td>\n",
       "      <td>104.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05/01/2013</th>\n",
       "      <td>96.0</td>\n",
       "      <td>109</td>\n",
       "      <td>102.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>06/01/2013</th>\n",
       "      <td>NaN</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              EPL  La Liga  Serie A  Bundesliga\n",
       "Month                                          \n",
       "08/01/2012   57.0       60     21.0        23.0\n",
       "09/01/2012  111.0      112    133.0       135.0\n",
       "10/01/2012   95.0       88     97.0        77.0\n",
       "11/01/2012  121.0      116    120.0       137.0\n",
       "12/01/2012  183.0      109    125.0        72.0\n",
       "01/01/2013  117.0      121    104.0        51.0\n",
       "02/01/2013   87.0      110    100.0       101.0\n",
       "03/01/2013   91.0      101     99.0       106.0\n",
       "04/01/2013  105.0      127    102.0       104.0\n",
       "05/01/2013   96.0      109    102.0        92.0\n",
       "06/01/2013    NaN       80      NaN         NaN"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goalsScoredDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "goalsScored_grp = goalsScoredDF.groupby(lambda Month: Month.split('/')[2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>113.4</td>\n",
       "      <td>97</td>\n",
       "      <td>99.2</td>\n",
       "      <td>88.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>99.2</td>\n",
       "      <td>108</td>\n",
       "      <td>101.4</td>\n",
       "      <td>90.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        EPL  La Liga  Serie A  Bundesliga\n",
       "2012  113.4       97     99.2        88.8\n",
       "2013   99.2      108    101.4        90.8"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goalsScored_grp.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      EPL  La Liga  Serie A  Bundesliga\n",
       "2012    5        5        5           5\n",
       "2013    5        6        5           5"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goalsScored_grp.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2012               EPL  La Liga  Serie A  Bundesliga\n",
      "Month                                          \n",
      "08/01/2012   57.0       60     21.0        23.0\n",
      "09/01/2012  111.0      112    133.0       135.0\n",
      "10/01/2012   95.0       88     97.0        77.0\n",
      "11/01/2012  121.0      116    120.0       137.0\n",
      "12/01/2012  183.0      109    125.0        72.0\n",
      "2013               EPL  La Liga  Serie A  Bundesliga\n",
      "Month                                          \n",
      "01/01/2013  117.0      121    104.0        51.0\n",
      "02/01/2013   87.0      110    100.0       101.0\n",
      "03/01/2013   91.0      101     99.0       106.0\n",
      "04/01/2013  105.0      127    102.0       104.0\n",
      "05/01/2013   96.0      109    102.0        92.0\n",
      "06/01/2013    NaN       80      NaN         NaN\n"
     ]
    }
   ],
   "source": [
    "\n",
    "for name,group in goalsScored_grp:\n",
    "    print (name, group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "fill_func = lambda x: x.fillna(x.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "trans = goalsScored_grp.transform(fill_func)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EPL</th>\n",
       "      <th>La Liga</th>\n",
       "      <th>Serie A</th>\n",
       "      <th>Bundesliga</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>08/01/2012</th>\n",
       "      <td>57.0</td>\n",
       "      <td>60</td>\n",
       "      <td>21.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09/01/2012</th>\n",
       "      <td>111.0</td>\n",
       "      <td>112</td>\n",
       "      <td>133.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10/01/2012</th>\n",
       "      <td>95.0</td>\n",
       "      <td>88</td>\n",
       "      <td>97.0</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11/01/2012</th>\n",
       "      <td>121.0</td>\n",
       "      <td>116</td>\n",
       "      <td>120.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12/01/2012</th>\n",
       "      <td>183.0</td>\n",
       "      <td>109</td>\n",
       "      <td>125.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01/01/2013</th>\n",
       "      <td>117.0</td>\n",
       "      <td>121</td>\n",
       "      <td>104.0</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02/01/2013</th>\n",
       "      <td>87.0</td>\n",
       "      <td>110</td>\n",
       "      <td>100.0</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03/01/2013</th>\n",
       "      <td>91.0</td>\n",
       "      <td>101</td>\n",
       "      <td>99.0</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04/01/2013</th>\n",
       "      <td>105.0</td>\n",
       "      <td>127</td>\n",
       "      <td>102.0</td>\n",
       "      <td>104.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05/01/2013</th>\n",
       "      <td>96.0</td>\n",
       "      <td>109</td>\n",
       "      <td>102.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>06/01/2013</th>\n",
       "      <td>99.2</td>\n",
       "      <td>80</td>\n",
       "      <td>101.4</td>\n",
       "      <td>90.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              EPL  La Liga  Serie A  Bundesliga\n",
       "Month                                          \n",
       "08/01/2012   57.0       60     21.0        23.0\n",
       "09/01/2012  111.0      112    133.0       135.0\n",
       "10/01/2012   95.0       88     97.0        77.0\n",
       "11/01/2012  121.0      116    120.0       137.0\n",
       "12/01/2012  183.0      109    125.0        72.0\n",
       "01/01/2013  117.0      121    104.0        51.0\n",
       "02/01/2013   87.0      110    100.0       101.0\n",
       "03/01/2013   91.0      101     99.0       106.0\n",
       "04/01/2013  105.0      127    102.0       104.0\n",
       "05/01/2013   96.0      109    102.0        92.0\n",
       "06/01/2013   99.2       80    101.4        90.8"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging and Joining\n",
    "* concat\n",
    "* append"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "A = trans.ix[:4, ['EPL']]\n",
    "B = trans.ix[:4, ['Serie A']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EPL</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>08/01/2012</th>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09/01/2012</th>\n",
       "      <td>111.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10/01/2012</th>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11/01/2012</th>\n",
       "      <td>121.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              EPL\n",
       "Month            \n",
       "08/01/2012   57.0\n",
       "09/01/2012  111.0\n",
       "10/01/2012   95.0\n",
       "11/01/2012  121.0"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Serie A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>08/01/2012</th>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09/01/2012</th>\n",
       "      <td>133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10/01/2012</th>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11/01/2012</th>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Serie A\n",
       "Month              \n",
       "08/01/2012     21.0\n",
       "09/01/2012    133.0\n",
       "10/01/2012     97.0\n",
       "11/01/2012    120.0"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EPL</th>\n",
       "      <th>Serie A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>08/01/2012</th>\n",
       "      <td>57.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09/01/2012</th>\n",
       "      <td>111.0</td>\n",
       "      <td>133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10/01/2012</th>\n",
       "      <td>95.0</td>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11/01/2012</th>\n",
       "      <td>121.0</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              EPL  Serie A\n",
       "Month                     \n",
       "08/01/2012   57.0     21.0\n",
       "09/01/2012  111.0    133.0\n",
       "10/01/2012   95.0     97.0\n",
       "11/01/2012  121.0    120.0"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([A,B],axis=1)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}