election_results_seat_percentages.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/IPython/core/interactiveshell.py:2723: DtypeWarning: Columns (1,5,7,9) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ST_NAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>AC_NO</th>\n",
       "      <th>#</th>\n",
       "      <th>AC_NAME</th>\n",
       "      <th>AC_TYPE</th>\n",
       "      <th>NAME</th>\n",
       "      <th>SEX</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CATEGORY</th>\n",
       "      <th>PARTY</th>\n",
       "      <th>VOTES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>ICHCHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>UPPADA RANGABABU</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KLP</td>\n",
       "      <td>14565.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>ICHCHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>HARIHARA PATNAIK</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>7408.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>ICHCHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>PUDI LOKANADHAM</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>6508.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>ICHCHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>KALLA BALARAMA SWAMY</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>3002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>ICHCHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>BANIKINKARA SHARMA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>682.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ST_NAME  YEAR  AC_NO  #      AC_NAME AC_TYPE                  NAME  \\\n",
       "0  Andhra Pradesh  1955      1  1  ICHCHAPURAM     GEN      UPPADA RANGABABU   \n",
       "1  Andhra Pradesh  1955      1  2  ICHCHAPURAM     GEN      HARIHARA PATNAIK   \n",
       "2  Andhra Pradesh  1955      1  3  ICHCHAPURAM     GEN       PUDI LOKANADHAM   \n",
       "3  Andhra Pradesh  1955      1  4  ICHCHAPURAM     GEN  KALLA BALARAMA SWAMY   \n",
       "4  Andhra Pradesh  1955      1  5  ICHCHAPURAM     GEN    BANIKINKARA SHARMA   \n",
       "\n",
       "  SEX  AGE CATEGORY PARTY    VOTES  \n",
       "0   M  NaN      NaN   KLP  14565.0  \n",
       "1   M  NaN      NaN   IND   7408.0  \n",
       "2   M  NaN      NaN   IND   6508.0  \n",
       "3   M  NaN      NaN   IND   3002.0  \n",
       "4   M  NaN      NaN   IND    682.0  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('assembly.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ST_NAME      object\n",
       "YEAR         object\n",
       "AC_NO         int64\n",
       "#             int64\n",
       "AC_NAME      object\n",
       "AC_TYPE      object\n",
       "NAME         object\n",
       "SEX          object\n",
       "AGE         float64\n",
       "CATEGORY     object\n",
       "PARTY        object\n",
       "VOTES       float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Andhra Pradesh', 'Arunachal Pradesh', 'Assam', 'Bihar',\n",
       "       'Chhattisgarh', 'Delhi', 'Goa', 'Gujarat', 'Haryana',\n",
       "       'Himachal Pradesh', 'Jammu & Kashmir', 'Jharkhand', 'Karnataka',\n",
       "       'Kerala', 'Madhya Pradesh', 'Maharashtra', 'Manipur', 'Meghalaya',\n",
       "       'Mizoram', 'Nagaland', 'Orissa', 'Pondicherry', 'Punjab',\n",
       "       'Rajasthan', 'Sikkim', 'Tamil Nadu', 'Tripura', 'Uttar Pradesh',\n",
       "       'Uttarakhand', 'West Bengal'], dtype=object)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.ST_NAME.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ST_NAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>AC_NO</th>\n",
       "      <th>#</th>\n",
       "      <th>AC_NAME</th>\n",
       "      <th>AC_TYPE</th>\n",
       "      <th>NAME</th>\n",
       "      <th>SEX</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CATEGORY</th>\n",
       "      <th>PARTY</th>\n",
       "      <th>VOTES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>88849</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>PERNEM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>K . B . LAXMAN</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MAG</td>\n",
       "      <td>4499.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88857</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>MANDREM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>A . J . DSOUZA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MAG</td>\n",
       "      <td>6907.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88867</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>SIOLIM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>A . P . PANDURANG</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MAG</td>\n",
       "      <td>5175.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88873</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>CALANGUTE</td>\n",
       "      <td>GEN</td>\n",
       "      <td>S . VALENTE</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UGS</td>\n",
       "      <td>6329.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88881</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>ALDONA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>L . S . O . FERNANDO</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UGS</td>\n",
       "      <td>6084.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      ST_NAME  YEAR  AC_NO  #    AC_NAME AC_TYPE                  NAME SEX  \\\n",
       "88849     Goa  1967      1  1     PERNEM     GEN        K . B . LAXMAN   M   \n",
       "88857     Goa  1967      2  1    MANDREM     GEN        A . J . DSOUZA   M   \n",
       "88867     Goa  1967      3  1     SIOLIM     GEN     A . P . PANDURANG   M   \n",
       "88873     Goa  1967      4  1  CALANGUTE     GEN           S . VALENTE   M   \n",
       "88881     Goa  1967      5  1     ALDONA     GEN  L . S . O . FERNANDO   M   \n",
       "\n",
       "       AGE CATEGORY PARTY   VOTES  \n",
       "88849  NaN      NaN   MAG  4499.0  \n",
       "88857  NaN      NaN   MAG  6907.0  \n",
       "88867  NaN      NaN   MAG  5175.0  \n",
       "88873  NaN      NaN   UGS  6329.0  \n",
       "88881  NaN      NaN   UGS  6084.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df[(df['ST_NAME'].isin(['Goa','Manipur','Punjab','Uttar Pradesh','Uttarakhand']))  & (df['#']== 1)]\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ST_NAME        YEAR  PARTY \n",
       "Goa            1967  IND         2\n",
       "                     MAG        16\n",
       "                     UGS        12\n",
       "               1972  INC         1\n",
       "                     IND         1\n",
       "                     MAG        18\n",
       "                     UGS        10\n",
       "               1977  INC        10\n",
       "                     IND         2\n",
       "                     JNP         3\n",
       "                     MAG        15\n",
       "               1980  INC(U)     20\n",
       "                     IND         3\n",
       "                     MAG         7\n",
       "               1984  INC        18\n",
       "                     IND         4\n",
       "                     MAG         8\n",
       "               1989  INC        20\n",
       "                     IND         2\n",
       "                     MAG        18\n",
       "               1994  BJP         4\n",
       "                     INC        18\n",
       "                     IND         3\n",
       "                     MAG        12\n",
       "                     UGDP        3\n",
       "               1999  BJP        10\n",
       "                     GRCP        2\n",
       "                     INC        21\n",
       "                     IND         1\n",
       "                     MAG         4\n",
       "                              ... \n",
       "Uttar Pradesh  2007  RSBP        1\n",
       "                     SP         97\n",
       "                     UPUDF       1\n",
       "               2012  AD          1\n",
       "                     BJP        47\n",
       "                     BSP        80\n",
       "                     IEMC        1\n",
       "                     INC        28\n",
       "                     IND         6\n",
       "                     NCP         1\n",
       "                     PECP        4\n",
       "                     QED         2\n",
       "                     RLD         9\n",
       "                     SP        224\n",
       "Uttarakhand    2002  BJP        19\n",
       "                     BSP         7\n",
       "                     INC        36\n",
       "                     IND         3\n",
       "                     NCP         1\n",
       "                     UKKD        4\n",
       "               2007  BJP        34\n",
       "                     BSP         8\n",
       "                     INC        21\n",
       "                     IND         3\n",
       "                     UKKD        3\n",
       "               2012  BJP        31\n",
       "                     BSP         3\n",
       "                     INC        32\n",
       "                     IND         3\n",
       "                     UKDP        1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.groupby(['ST_NAME','YEAR','PARTY'])['PARTY'].size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ST_NAME        YEAR\n",
       "Goa            1967     30\n",
       "               1972     30\n",
       "               1977     30\n",
       "               1980     30\n",
       "               1984     30\n",
       "               1989     40\n",
       "               1994     40\n",
       "               1999     40\n",
       "               2002     40\n",
       "               2007     40\n",
       "               2012     40\n",
       "Manipur        1967     30\n",
       "               1972     60\n",
       "               1974     60\n",
       "               1980     60\n",
       "               1984     60\n",
       "               1990     54\n",
       "               1995     60\n",
       "               2000     60\n",
       "               2002     60\n",
       "               2007     60\n",
       "               2012     60\n",
       "Punjab         1951    105\n",
       "               1957    121\n",
       "               1962    154\n",
       "               1967    104\n",
       "               1969    104\n",
       "               1972    104\n",
       "               1977    117\n",
       "               1980    117\n",
       "               1985    117\n",
       "               1992    117\n",
       "               1997    117\n",
       "               2002    117\n",
       "               2007    116\n",
       "               2012    117\n",
       "Uttar Pradesh  1951    347\n",
       "               1957    341\n",
       "               1962    430\n",
       "               1967    425\n",
       "               1969    425\n",
       "               1974    424\n",
       "               1977    425\n",
       "               1980    425\n",
       "               1985    425\n",
       "               1989    425\n",
       "               1991    419\n",
       "               1993    422\n",
       "               1996    424\n",
       "               2002    403\n",
       "               2007    403\n",
       "               2012    403\n",
       "Uttarakhand    2002     70\n",
       "               2007     69\n",
       "               2012     70\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.groupby(['ST_NAME','YEAR'])['YEAR'].size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2.groupby(['ST_NAME','YEAR','PARTY'])['PARTY'].size().to_frame().to_csv('trial.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ST_NAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>PARTY</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>IND</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>MAG</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>UGS</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>INC</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>IND</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ST_NAME  YEAR PARTY   0\n",
       "0     Goa  1967   IND   2\n",
       "1     Goa  1967   MAG  16\n",
       "2     Goa  1967   UGS  12\n",
       "3     Goa  1972   INC   1\n",
       "4     Goa  1972   IND   1"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.read_csv('trial.csv')\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ST_NAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>PARTY</th>\n",
       "      <th>SEATS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>IND</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>MAG</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>UGS</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>INC</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>IND</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ST_NAME  YEAR PARTY  SEATS\n",
       "0     Goa  1967   IND      2\n",
       "1     Goa  1967   MAG     16\n",
       "2     Goa  1967   UGS     12\n",
       "3     Goa  1972   INC      1\n",
       "4     Goa  1972   IND      1"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3=df3.rename(columns = {'0':'SEATS'})\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ST_NAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>PARTY</th>\n",
       "      <th>SEATS</th>\n",
       "      <th>SEATS_YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>IND</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>MAG</td>\n",
       "      <td>16</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>UGS</td>\n",
       "      <td>12</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>INC</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>IND</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ST_NAME  YEAR PARTY  SEATS  SEATS_YEAR\n",
       "0     Goa  1967   IND      2          30\n",
       "1     Goa  1967   MAG     16          30\n",
       "2     Goa  1967   UGS     12          30\n",
       "3     Goa  1972   INC      1          30\n",
       "4     Goa  1972   IND      1          30"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3['SEATS_YEAR'] = df3.groupby(['ST_NAME','YEAR'])['SEATS'].transform(sum)\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ST_NAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>PARTY</th>\n",
       "      <th>SEATS</th>\n",
       "      <th>SEATS_YEAR</th>\n",
       "      <th>PARTY_YEAR_PERCENT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>IND</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>6.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>MAG</td>\n",
       "      <td>16</td>\n",
       "      <td>30</td>\n",
       "      <td>53.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>UGS</td>\n",
       "      <td>12</td>\n",
       "      <td>30</td>\n",
       "      <td>40.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>INC</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>3.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>IND</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>3.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ST_NAME  YEAR PARTY  SEATS  SEATS_YEAR  PARTY_YEAR_PERCENT\n",
       "0     Goa  1967   IND      2          30            6.666667\n",
       "1     Goa  1967   MAG     16          30           53.333333\n",
       "2     Goa  1967   UGS     12          30           40.000000\n",
       "3     Goa  1972   INC      1          30            3.333333\n",
       "4     Goa  1972   IND      1          30            3.333333"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3['PARTY_YEAR_PERCENT']=(df3['SEATS']/df3['SEATS_YEAR'])*100\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ST_NAME                object\n",
       "YEAR                    int64\n",
       "PARTY                  object\n",
       "SEATS                   int64\n",
       "SEATS_YEAR              int64\n",
       "PARTY_YEAR_PERCENT    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df3['PARTY_YEAR_PERCENT']=df3['PARTY_YEAR_PERCENT'].round(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ST_NAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>PARTY</th>\n",
       "      <th>SEATS</th>\n",
       "      <th>SEATS_YEAR</th>\n",
       "      <th>PARTY_YEAR_PERCENT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>IND</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>6.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>MAG</td>\n",
       "      <td>16</td>\n",
       "      <td>30</td>\n",
       "      <td>53.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1967</td>\n",
       "      <td>UGS</td>\n",
       "      <td>12</td>\n",
       "      <td>30</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>INC</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1972</td>\n",
       "      <td>IND</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ST_NAME  YEAR PARTY  SEATS  SEATS_YEAR  PARTY_YEAR_PERCENT\n",
       "0     Goa  1967   IND      2          30                 6.7\n",
       "1     Goa  1967   MAG     16          30                53.3\n",
       "2     Goa  1967   UGS     12          30                40.0\n",
       "3     Goa  1972   INC      1          30                 3.3\n",
       "4     Goa  1972   IND      1          30                 3.3"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ST_NAME        PARTY  \n",
       "Goa            BJP                                 [1999, 2002, 2007, 2012]\n",
       "               INC         [1977, 1984, 1989, 1994, 1999, 2002, 2007, 2012]\n",
       "               INC(U)                                                [1980]\n",
       "               IND                                             [1984, 2012]\n",
       "               MAG               [1967, 1972, 1977, 1980, 1984, 1989, 1994]\n",
       "               UGS                                             [1967, 1972]\n",
       "Manipur        AITC                                                  [2012]\n",
       "               FPM                                                   [2002]\n",
       "               INC        [1967, 1972, 1974, 1980, 1984, 1990, 1995, 200...\n",
       "               IND                           [1967, 1972, 1980, 1984, 2007]\n",
       "               JD                                              [1990, 1995]\n",
       "               JNP                                                   [1980]\n",
       "               MHU                                                   [1974]\n",
       "               MPP                                             [1990, 1995]\n",
       "               MRP                                             [1972, 1974]\n",
       "               MSCP                                            [2000, 2002]\n",
       "               SSP                                                   [1967]\n",
       "Punjab         AD                                                    [1962]\n",
       "               ADS                                                   [1967]\n",
       "               BJP                                       [1997, 2007, 2012]\n",
       "               INC        [1951, 1957, 1962, 1967, 1969, 1972, 1977, 198...\n",
       "               IND                                                   [1962]\n",
       "               JNP                                                   [1977]\n",
       "               SAD        [1969, 1972, 1977, 1980, 1985, 1997, 2002, 200...\n",
       "Uttar Pradesh  BJP               [1989, 1991, 1993, 1996, 2002, 2007, 2012]\n",
       "               BJS                                       [1967, 1969, 1974]\n",
       "               BKD                                             [1969, 1974]\n",
       "               BSP                           [1993, 1996, 2002, 2007, 2012]\n",
       "               INC        [1951, 1957, 1962, 1967, 1969, 1974, 1977, 198...\n",
       "               IND                                                   [1957]\n",
       "               JD                                              [1989, 1991]\n",
       "               JNP                                                   [1977]\n",
       "               JNP(SC)                                               [1980]\n",
       "               JS                                                    [1962]\n",
       "               LKD                                                   [1985]\n",
       "               PSP                                                   [1957]\n",
       "               SP                            [1993, 1996, 2002, 2007, 2012]\n",
       "               SSP                                                   [1967]\n",
       "Uttarakhand    BJP                                       [2002, 2007, 2012]\n",
       "               BSP                                                   [2007]\n",
       "               INC                                       [2002, 2007, 2012]\n",
       "Name: YEAR, dtype: object"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3[df3['PARTY_YEAR_PERCENT'] > 10].groupby(['ST_NAME','PARTY'])['YEAR'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method DataFrame.head of            ST_NAME  YEAR   PARTY  SEATS  SEATS_YEAR  PARTY_YEAR_PERCENT\n",
       "0              Goa  1967     IND      2          30                 6.7\n",
       "1              Goa  1967     MAG     16          30                53.3\n",
       "2              Goa  1967     UGS     12          30                40.0\n",
       "3              Goa  1972     INC      1          30                 3.3\n",
       "4              Goa  1972     IND      1          30                 3.3\n",
       "5              Goa  1972     MAG     18          30                60.0\n",
       "6              Goa  1972     UGS     10          30                33.3\n",
       "7              Goa  1977     INC     10          30                33.3\n",
       "8              Goa  1977     IND      2          30                 6.7\n",
       "9              Goa  1977     JNP      3          30                10.0\n",
       "10             Goa  1977     MAG     15          30                50.0\n",
       "11             Goa  1980  INC(U)     20          30                66.7\n",
       "12             Goa  1980     IND      3          30                10.0\n",
       "13             Goa  1980     MAG      7          30                23.3\n",
       "14             Goa  1984     INC     18          30                60.0\n",
       "15             Goa  1984     IND      4          30                13.3\n",
       "16             Goa  1984     MAG      8          30                26.7\n",
       "17             Goa  1989     INC     20          40                50.0\n",
       "18             Goa  1989     IND      2          40                 5.0\n",
       "19             Goa  1989     MAG     18          40                45.0\n",
       "20             Goa  1994     BJP      4          40                10.0\n",
       "21             Goa  1994     INC     18          40                45.0\n",
       "22             Goa  1994     IND      3          40                 7.5\n",
       "23             Goa  1994     MAG     12          40                30.0\n",
       "24             Goa  1994    UGDP      3          40                 7.5\n",
       "25             Goa  1999     BJP     10          40                25.0\n",
       "26             Goa  1999    GRCP      2          40                 5.0\n",
       "27             Goa  1999     INC     21          40                52.5\n",
       "28             Goa  1999     IND      1          40                 2.5\n",
       "29             Goa  1999     MAG      4          40                10.0\n",
       "..             ...   ...     ...    ...         ...                 ...\n",
       "371  Uttar Pradesh  2007    RSBP      1         403                 0.2\n",
       "372  Uttar Pradesh  2007      SP     97         403                24.1\n",
       "373  Uttar Pradesh  2007   UPUDF      1         403                 0.2\n",
       "374  Uttar Pradesh  2012      AD      1         403                 0.2\n",
       "375  Uttar Pradesh  2012     BJP     47         403                11.7\n",
       "376  Uttar Pradesh  2012     BSP     80         403                19.9\n",
       "377  Uttar Pradesh  2012    IEMC      1         403                 0.2\n",
       "378  Uttar Pradesh  2012     INC     28         403                 6.9\n",
       "379  Uttar Pradesh  2012     IND      6         403                 1.5\n",
       "380  Uttar Pradesh  2012     NCP      1         403                 0.2\n",
       "381  Uttar Pradesh  2012    PECP      4         403                 1.0\n",
       "382  Uttar Pradesh  2012     QED      2         403                 0.5\n",
       "383  Uttar Pradesh  2012     RLD      9         403                 2.2\n",
       "384  Uttar Pradesh  2012      SP    224         403                55.6\n",
       "385    Uttarakhand  2002     BJP     19          70                27.1\n",
       "386    Uttarakhand  2002     BSP      7          70                10.0\n",
       "387    Uttarakhand  2002     INC     36          70                51.4\n",
       "388    Uttarakhand  2002     IND      3          70                 4.3\n",
       "389    Uttarakhand  2002     NCP      1          70                 1.4\n",
       "390    Uttarakhand  2002    UKKD      4          70                 5.7\n",
       "391    Uttarakhand  2007     BJP     34          69                49.3\n",
       "392    Uttarakhand  2007     BSP      8          69                11.6\n",
       "393    Uttarakhand  2007     INC     21          69                30.4\n",
       "394    Uttarakhand  2007     IND      3          69                 4.3\n",
       "395    Uttarakhand  2007    UKKD      3          69                 4.3\n",
       "396    Uttarakhand  2012     BJP     31          70                44.3\n",
       "397    Uttarakhand  2012     BSP      3          70                 4.3\n",
       "398    Uttarakhand  2012     INC     32          70                45.7\n",
       "399    Uttarakhand  2012     IND      3          70                 4.3\n",
       "400    Uttarakhand  2012    UKDP      1          70                 1.4\n",
       "\n",
       "[401 rows x 6 columns]>"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df3.to_csv('for_openrefine.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_goa = df3[(df3['ST_NAME']== 'Goa') & (df3['PARTY'].isin(['BJP', 'INC', 'MAG']))]\n",
    "df_goa.to_csv('goa_graph.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_uttarakhand = df3[(df3['ST_NAME']== 'Uttarakhand') & (df3['PARTY'].isin(['BJP', 'INC', 'BSP']))]\n",
    "df_uttarakhand.to_csv('uttarakhand_graph.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_uttar_pradesh = df3[(df3['ST_NAME']== 'Uttar Pradesh') &\\\n",
    "                       (df3['PARTY'].isin(['AD', 'BJP', 'BSP', 'INC', 'PECP', 'RLD', 'SP']))]\n",
    "df_uttar_pradesh.to_csv('uttar_pradesh_graph.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_punjab = df3[(df3['ST_NAME']== 'Punjab') & (df3['PARTY'].isin(['BJP', 'INC', 'SAD', 'AAP']))]\n",
    "df_punjab.to_csv('punjab_graph.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_manipur = df3[(df3['ST_NAME']== 'Manipur') & (df3['PARTY'].isin(['AITC', 'INC', 'MSCP', 'CPI', 'NPF', 'BJP']))]\n",
    "df_manipur.to_csv('manipur_graph.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11+"
  },
  "widgets": {
   "state": {},
   "version": "1.1.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}