Cleansing-Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "![Cleaning data](Cleansing-Data/Cleansing-Data.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Six steps to clean data\n",
    "\n",
    "Clean the **data structure**\n",
    "\n",
    "1. **Scrape** the document (into a DataFrame)\n",
    "2. Delete extra **rows**\n",
    "3. Fix **column** structure\n",
    "\n",
    "Clean the **data content**\n",
    "\n",
    "4. Handle **missing** values\n",
    "5. **Standardize** values\n",
    "6. **Extract** unstructured data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "1. Digitize the document. (format: PDF/Word/..., encoding: UTF-8/CP1252/...)\n",
    "1. Delete extra rows (header, footer, total, subtotal, column number indicators, blank)\n",
    "1. Fix column structure (missing headers, rename columns, mis-alignment)\n",
    "1. Handle missing / duplicate values\n",
    "  - De-duplicate rows (based on subset of data)\n",
    "  - Identify (e.g. blank, NA, XX, 999, etc)\n",
    "  - Ignore missing values\n",
    "  - Remove rows with NA (subsetting) or columns with NA\n",
    "  - Fill missing values:\n",
    "    - Constant (e.g. zero, today, etc)\n",
    "    - Column (e.g. created date  updated date)\n",
    "    - Function (e.g. average of rows/columns)\n",
    "    - External data\n",
    "1. Standardize values\n",
    "  - Convert text to numbers or dates\n",
    "  - Remove extra characters (commas from numbers, prefixes, suffixes, spaces)\n",
    "  - Correct mis-spellings\n",
    "1. Extract unstructured data\n",
    "  - Extract numbers with units (lbs & kgs, kph & mph)\n",
    "  - Extract lists (e.g. multiple values in cells)\n",
    "  - Extract dates (e.g. 25/10/16 or Oct 25, 2016)\n",
    "  - Extract entities (e.g. names, places, phone numbers, PIN codes, things)\n",
    "  - Extract name structure (e.g. title, first name, last name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Let's clean this analyst report\n",
    "\n",
    "[Open file](Cleansing-Data/Cleansing-Data-ABN-AMRO-report.pdf)\n",
    "\n",
    "![ABN Amro Analyst Report](Cleansing-Data/Cleansing-Data-ABN-Maro-report.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 1. Scrape the document\n",
    "\n",
    "Convert data in formats like PDF, HTML, Word, etc into a Python DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\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>RWAs</th>\n",
       "      <th>Capital</th>\n",
       "      <th>Net profit</th>\n",
       "      <th>Net profit.1</th>\n",
       "      <th>Net profit.2</th>\n",
       "      <th>Net profit.3</th>\n",
       "      <th>As %</th>\n",
       "      <th>Peer group</th>\n",
       "      <th>Valuation</th>\n",
       "      <th>As %.1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>(2005)</td>\n",
       "      <td>allocated</td>\n",
       "      <td>2004A</td>\n",
       "      <td>2005A</td>\n",
       "      <td>2006E</td>\n",
       "      <td>2007E</td>\n",
       "      <td>2006E</td>\n",
       "      <td>PE 06E</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>47,300</td>\n",
       "      <td>2,838</td>\n",
       "      <td>229</td>\n",
       "      <td>509</td>\n",
       "      <td>707</td>\n",
       "      <td>736</td>\n",
       "      <td>16%</td>\n",
       "      <td>11.8</td>\n",
       "      <td>8,345</td>\n",
       "      <td>16%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USA - commercial</td>\n",
       "      <td>66,700</td>\n",
       "      <td>4,002</td>\n",
       "      <td>488</td>\n",
       "      <td>766</td>\n",
       "      <td>836</td>\n",
       "      <td>898</td>\n",
       "      <td>19%</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11,700</td>\n",
       "      <td>23%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USA - mortgage</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>182</td>\n",
       "      <td>105</td>\n",
       "      <td>146</td>\n",
       "      <td>160</td>\n",
       "      <td>3%</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1,364</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>14,800</td>\n",
       "      <td>888</td>\n",
       "      <td>302</td>\n",
       "      <td>644</td>\n",
       "      <td>574</td>\n",
       "      <td>601</td>\n",
       "      <td>13%</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6,336</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>New Growth Markets</td>\n",
       "      <td>6,500</td>\n",
       "      <td>390</td>\n",
       "      <td>378</td>\n",
       "      <td>213</td>\n",
       "      <td>197</td>\n",
       "      <td>215</td>\n",
       "      <td>4%</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2,171</td>\n",
       "      <td>4%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bouwfonds</td>\n",
       "      <td>25,900</td>\n",
       "      <td>1,554</td>\n",
       "      <td>265</td>\n",
       "      <td>315</td>\n",
       "      <td>129</td>\n",
       "      <td>123</td>\n",
       "      <td>3%</td>\n",
       "      <td>11.8</td>\n",
       "      <td>1,523</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Wholesale</td>\n",
       "      <td>77,000</td>\n",
       "      <td>4,620</td>\n",
       "      <td>(7)</td>\n",
       "      <td>705</td>\n",
       "      <td>550</td>\n",
       "      <td>582</td>\n",
       "      <td>12%</td>\n",
       "      <td>8.4</td>\n",
       "      <td>4,620</td>\n",
       "      <td>9%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Private Clients</td>\n",
       "      <td>8,100</td>\n",
       "      <td>368</td>\n",
       "      <td>169</td>\n",
       "      <td>255</td>\n",
       "      <td>329</td>\n",
       "      <td>385</td>\n",
       "      <td>7%</td>\n",
       "      <td>17.2</td>\n",
       "      <td>5,637</td>\n",
       "      <td>11%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Asset Management</td>\n",
       "      <td>NaN</td>\n",
       "      <td>451</td>\n",
       "      <td>97</td>\n",
       "      <td>171</td>\n",
       "      <td>180</td>\n",
       "      <td>193</td>\n",
       "      <td>4%</td>\n",
       "      <td>17.2</td>\n",
       "      <td>3,095</td>\n",
       "      <td>6%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Antonveneta</td>\n",
       "      <td>37,000</td>\n",
       "      <td>2,220</td>\n",
       "      <td>na</td>\n",
       "      <td>na</td>\n",
       "      <td>508</td>\n",
       "      <td>555</td>\n",
       "      <td>11%</td>\n",
       "      <td>12.9</td>\n",
       "      <td>6,580</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Operating businesses</td>\n",
       "      <td>283,300</td>\n",
       "      <td>17,331</td>\n",
       "      <td>2,103</td>\n",
       "      <td>3,683</td>\n",
       "      <td>4,156</td>\n",
       "      <td>4,448</td>\n",
       "      <td>93%</td>\n",
       "      <td>12.4</td>\n",
       "      <td>51,370</td>\n",
       "      <td>101%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Corporate centre</td>\n",
       "      <td>8,600</td>\n",
       "      <td>na</td>\n",
       "      <td>1,584</td>\n",
       "      <td>619</td>\n",
       "      <td>305</td>\n",
       "      <td>146</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.4</td>\n",
       "      <td>3,765</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Private equity</td>\n",
       "      <td>3,200</td>\n",
       "      <td>2,711</td>\n",
       "      <td>429</td>\n",
       "      <td>335</td>\n",
       "      <td>280</td>\n",
       "      <td>280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>na</td>\n",
       "      <td>2,711</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Capital surplus</td>\n",
       "      <td>na</td>\n",
       "      <td>(7,178)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>na</td>\n",
       "      <td>(7,178)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Group</td>\n",
       "      <td>295,100</td>\n",
       "      <td>12,864</td>\n",
       "      <td>3,865</td>\n",
       "      <td>4,386</td>\n",
       "      <td>4,489</td>\n",
       "      <td>4,623</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.3</td>\n",
       "      <td>50,669</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Shares outs.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,878</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Per share</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Unnamed: 0     RWAs    Capital Net profit Net profit.1  \\\n",
       "0                    NaN   (2005)  allocated      2004A        2005A   \n",
       "1            Netherlands   47,300      2,838        229          509   \n",
       "2       USA - commercial   66,700      4,002        488          766   \n",
       "3         USA - mortgage      NaN        NaN        182          105   \n",
       "4                 Brazil   14,800        888        302          644   \n",
       "5     New Growth Markets    6,500        390        378          213   \n",
       "6              Bouwfonds   25,900      1,554        265          315   \n",
       "7              Wholesale   77,000      4,620        (7)          705   \n",
       "8        Private Clients    8,100        368        169          255   \n",
       "9       Asset Management      NaN        451         97          171   \n",
       "10           Antonveneta   37,000      2,220         na           na   \n",
       "11  Operating businesses  283,300     17,331      2,103        3,683   \n",
       "12      Corporate centre    8,600         na      1,584          619   \n",
       "13        Private equity    3,200      2,711        429          335   \n",
       "14       Capital surplus       na    (7,178)      (251)        (251)   \n",
       "15                 Group  295,100     12,864      3,865        4,386   \n",
       "16          Shares outs.      NaN        NaN        NaN          NaN   \n",
       "17             Per share      NaN        NaN        NaN          NaN   \n",
       "\n",
       "   Net profit.2 Net profit.3   As % Peer group Valuation As %.1  \n",
       "0         2006E        2007E  2006E     PE 06E       NaN    NaN  \n",
       "1           707          736    16%       11.8     8,345    16%  \n",
       "2           836          898    19%       14.0    11,700    23%  \n",
       "3           146          160     3%        9.3     1,364     3%  \n",
       "4           574          601    13%       11.0     6,336    13%  \n",
       "5           197          215     4%       11.0     2,171     4%  \n",
       "6           129          123     3%       11.8     1,523     3%  \n",
       "7           550          582    12%        8.4     4,620     9%  \n",
       "8           329          385     7%       17.2     5,637    11%  \n",
       "9           180          193     4%       17.2     3,095     6%  \n",
       "10          508          555    11%       12.9     6,580    13%  \n",
       "11        4,156        4,448    93%       12.4    51,370   101%  \n",
       "12          305          146    NaN       12.4     3,765    NaN  \n",
       "13          280          280    NaN         na     2,711    NaN  \n",
       "14        (251)        (251)    NaN         na   (7,178)    NaN  \n",
       "15        4,489        4,623    NaN       11.3    50,669    NaN  \n",
       "16          NaN          NaN    NaN        NaN     1,878    NaN  \n",
       "17          NaN          NaN    NaN        NaN      27.0    NaN  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import tabula\n",
    "data1 = tabula.read_pdf('Cleansing-Data/Cleansing-Data-ABN-AMRO-report.pdf', pages=3)\n",
    "data1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 2. Delete extra rows\n",
    "\n",
    "Header, footer, total, subtotal, column number indicators, blank rows, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RWAs</th>\n",
       "      <th>Capital</th>\n",
       "      <th>Net profit</th>\n",
       "      <th>Net profit.1</th>\n",
       "      <th>Net profit.2</th>\n",
       "      <th>Net profit.3</th>\n",
       "      <th>As %</th>\n",
       "      <th>Peer group</th>\n",
       "      <th>Valuation</th>\n",
       "      <th>As %.1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Netherlands</th>\n",
       "      <td>47,300</td>\n",
       "      <td>2,838</td>\n",
       "      <td>229</td>\n",
       "      <td>509</td>\n",
       "      <td>707</td>\n",
       "      <td>736</td>\n",
       "      <td>16%</td>\n",
       "      <td>11.8</td>\n",
       "      <td>8,345</td>\n",
       "      <td>16%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA - commercial</th>\n",
       "      <td>66,700</td>\n",
       "      <td>4,002</td>\n",
       "      <td>488</td>\n",
       "      <td>766</td>\n",
       "      <td>836</td>\n",
       "      <td>898</td>\n",
       "      <td>19%</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11,700</td>\n",
       "      <td>23%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA - mortgage</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>182</td>\n",
       "      <td>105</td>\n",
       "      <td>146</td>\n",
       "      <td>160</td>\n",
       "      <td>3%</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1,364</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>14,800</td>\n",
       "      <td>888</td>\n",
       "      <td>302</td>\n",
       "      <td>644</td>\n",
       "      <td>574</td>\n",
       "      <td>601</td>\n",
       "      <td>13%</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6,336</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Growth Markets</th>\n",
       "      <td>6,500</td>\n",
       "      <td>390</td>\n",
       "      <td>378</td>\n",
       "      <td>213</td>\n",
       "      <td>197</td>\n",
       "      <td>215</td>\n",
       "      <td>4%</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2,171</td>\n",
       "      <td>4%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bouwfonds</th>\n",
       "      <td>25,900</td>\n",
       "      <td>1,554</td>\n",
       "      <td>265</td>\n",
       "      <td>315</td>\n",
       "      <td>129</td>\n",
       "      <td>123</td>\n",
       "      <td>3%</td>\n",
       "      <td>11.8</td>\n",
       "      <td>1,523</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wholesale</th>\n",
       "      <td>77,000</td>\n",
       "      <td>4,620</td>\n",
       "      <td>(7)</td>\n",
       "      <td>705</td>\n",
       "      <td>550</td>\n",
       "      <td>582</td>\n",
       "      <td>12%</td>\n",
       "      <td>8.4</td>\n",
       "      <td>4,620</td>\n",
       "      <td>9%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Private Clients</th>\n",
       "      <td>8,100</td>\n",
       "      <td>368</td>\n",
       "      <td>169</td>\n",
       "      <td>255</td>\n",
       "      <td>329</td>\n",
       "      <td>385</td>\n",
       "      <td>7%</td>\n",
       "      <td>17.2</td>\n",
       "      <td>5,637</td>\n",
       "      <td>11%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asset Management</th>\n",
       "      <td>NaN</td>\n",
       "      <td>451</td>\n",
       "      <td>97</td>\n",
       "      <td>171</td>\n",
       "      <td>180</td>\n",
       "      <td>193</td>\n",
       "      <td>4%</td>\n",
       "      <td>17.2</td>\n",
       "      <td>3,095</td>\n",
       "      <td>6%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Antonveneta</th>\n",
       "      <td>37,000</td>\n",
       "      <td>2,220</td>\n",
       "      <td>na</td>\n",
       "      <td>na</td>\n",
       "      <td>508</td>\n",
       "      <td>555</td>\n",
       "      <td>11%</td>\n",
       "      <td>12.9</td>\n",
       "      <td>6,580</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corporate centre</th>\n",
       "      <td>8,600</td>\n",
       "      <td>na</td>\n",
       "      <td>1,584</td>\n",
       "      <td>619</td>\n",
       "      <td>305</td>\n",
       "      <td>146</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.4</td>\n",
       "      <td>3,765</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Private equity</th>\n",
       "      <td>3,200</td>\n",
       "      <td>2,711</td>\n",
       "      <td>429</td>\n",
       "      <td>335</td>\n",
       "      <td>280</td>\n",
       "      <td>280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>na</td>\n",
       "      <td>2,711</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Capital surplus</th>\n",
       "      <td>na</td>\n",
       "      <td>(7,178)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>na</td>\n",
       "      <td>(7,178)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      RWAs  Capital Net profit Net profit.1 Net profit.2  \\\n",
       "Unnamed: 0                                                                 \n",
       "Netherlands         47,300    2,838        229          509          707   \n",
       "USA - commercial    66,700    4,002        488          766          836   \n",
       "USA - mortgage         NaN      NaN        182          105          146   \n",
       "Brazil              14,800      888        302          644          574   \n",
       "New Growth Markets   6,500      390        378          213          197   \n",
       "Bouwfonds           25,900    1,554        265          315          129   \n",
       "Wholesale           77,000    4,620        (7)          705          550   \n",
       "Private Clients      8,100      368        169          255          329   \n",
       "Asset Management       NaN      451         97          171          180   \n",
       "Antonveneta         37,000    2,220         na           na          508   \n",
       "Corporate centre     8,600       na      1,584          619          305   \n",
       "Private equity       3,200    2,711        429          335          280   \n",
       "Capital surplus         na  (7,178)      (251)        (251)        (251)   \n",
       "\n",
       "                   Net profit.3 As % Peer group Valuation As %.1  \n",
       "Unnamed: 0                                                        \n",
       "Netherlands                 736  16%       11.8     8,345    16%  \n",
       "USA - commercial            898  19%       14.0    11,700    23%  \n",
       "USA - mortgage              160   3%        9.3     1,364     3%  \n",
       "Brazil                      601  13%       11.0     6,336    13%  \n",
       "New Growth Markets          215   4%       11.0     2,171     4%  \n",
       "Bouwfonds                   123   3%       11.8     1,523     3%  \n",
       "Wholesale                   582  12%        8.4     4,620     9%  \n",
       "Private Clients             385   7%       17.2     5,637    11%  \n",
       "Asset Management            193   4%       17.2     3,095     6%  \n",
       "Antonveneta                 555  11%       12.9     6,580    13%  \n",
       "Corporate centre            146  NaN       12.4     3,765    NaN  \n",
       "Private equity              280  NaN         na     2,711    NaN  \n",
       "Capital surplus           (251)  NaN         na   (7,178)    NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data2 = (data1\n",
    "         .set_index('Unnamed: 0')\n",
    "         .drop([pd.np.nan, 'Operating businesses', 'Group', 'Shares outs.', 'Per share'])\n",
    "        )\n",
    "data2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 3. Fix column structure\n",
    "\n",
    "Missing header row, column names split across rows / columns, mis-aligned columns, etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RWAs</th>\n",
       "      <th>Capital allocated</th>\n",
       "      <th>Net profit 2004A</th>\n",
       "      <th>Net profit 2005A</th>\n",
       "      <th>Net profit 2006E</th>\n",
       "      <th>Net profit 2007E</th>\n",
       "      <th>As % 2006E</th>\n",
       "      <th>Peer group PE06E</th>\n",
       "      <th>Valuation</th>\n",
       "      <th>As %</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Business</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Netherlands</th>\n",
       "      <td>47,300</td>\n",
       "      <td>2,838</td>\n",
       "      <td>229</td>\n",
       "      <td>509</td>\n",
       "      <td>707</td>\n",
       "      <td>736</td>\n",
       "      <td>16%</td>\n",
       "      <td>11.8</td>\n",
       "      <td>8,345</td>\n",
       "      <td>16%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA - commercial</th>\n",
       "      <td>66,700</td>\n",
       "      <td>4,002</td>\n",
       "      <td>488</td>\n",
       "      <td>766</td>\n",
       "      <td>836</td>\n",
       "      <td>898</td>\n",
       "      <td>19%</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11,700</td>\n",
       "      <td>23%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA - mortgage</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>182</td>\n",
       "      <td>105</td>\n",
       "      <td>146</td>\n",
       "      <td>160</td>\n",
       "      <td>3%</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1,364</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>14,800</td>\n",
       "      <td>888</td>\n",
       "      <td>302</td>\n",
       "      <td>644</td>\n",
       "      <td>574</td>\n",
       "      <td>601</td>\n",
       "      <td>13%</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6,336</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Growth Markets</th>\n",
       "      <td>6,500</td>\n",
       "      <td>390</td>\n",
       "      <td>378</td>\n",
       "      <td>213</td>\n",
       "      <td>197</td>\n",
       "      <td>215</td>\n",
       "      <td>4%</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2,171</td>\n",
       "      <td>4%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bouwfonds</th>\n",
       "      <td>25,900</td>\n",
       "      <td>1,554</td>\n",
       "      <td>265</td>\n",
       "      <td>315</td>\n",
       "      <td>129</td>\n",
       "      <td>123</td>\n",
       "      <td>3%</td>\n",
       "      <td>11.8</td>\n",
       "      <td>1,523</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wholesale</th>\n",
       "      <td>77,000</td>\n",
       "      <td>4,620</td>\n",
       "      <td>(7)</td>\n",
       "      <td>705</td>\n",
       "      <td>550</td>\n",
       "      <td>582</td>\n",
       "      <td>12%</td>\n",
       "      <td>8.4</td>\n",
       "      <td>4,620</td>\n",
       "      <td>9%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Private Clients</th>\n",
       "      <td>8,100</td>\n",
       "      <td>368</td>\n",
       "      <td>169</td>\n",
       "      <td>255</td>\n",
       "      <td>329</td>\n",
       "      <td>385</td>\n",
       "      <td>7%</td>\n",
       "      <td>17.2</td>\n",
       "      <td>5,637</td>\n",
       "      <td>11%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asset Management</th>\n",
       "      <td>NaN</td>\n",
       "      <td>451</td>\n",
       "      <td>97</td>\n",
       "      <td>171</td>\n",
       "      <td>180</td>\n",
       "      <td>193</td>\n",
       "      <td>4%</td>\n",
       "      <td>17.2</td>\n",
       "      <td>3,095</td>\n",
       "      <td>6%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Antonveneta</th>\n",
       "      <td>37,000</td>\n",
       "      <td>2,220</td>\n",
       "      <td>na</td>\n",
       "      <td>na</td>\n",
       "      <td>508</td>\n",
       "      <td>555</td>\n",
       "      <td>11%</td>\n",
       "      <td>12.9</td>\n",
       "      <td>6,580</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corporate centre</th>\n",
       "      <td>8,600</td>\n",
       "      <td>na</td>\n",
       "      <td>1,584</td>\n",
       "      <td>619</td>\n",
       "      <td>305</td>\n",
       "      <td>146</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.4</td>\n",
       "      <td>3,765</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Private equity</th>\n",
       "      <td>3,200</td>\n",
       "      <td>2,711</td>\n",
       "      <td>429</td>\n",
       "      <td>335</td>\n",
       "      <td>280</td>\n",
       "      <td>280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>na</td>\n",
       "      <td>2,711</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Capital surplus</th>\n",
       "      <td>na</td>\n",
       "      <td>(7,178)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>na</td>\n",
       "      <td>(7,178)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      RWAs Capital allocated Net profit 2004A  \\\n",
       "Business                                                        \n",
       "Netherlands         47,300             2,838              229   \n",
       "USA - commercial    66,700             4,002              488   \n",
       "USA - mortgage         NaN               NaN              182   \n",
       "Brazil              14,800               888              302   \n",
       "New Growth Markets   6,500               390              378   \n",
       "Bouwfonds           25,900             1,554              265   \n",
       "Wholesale           77,000             4,620              (7)   \n",
       "Private Clients      8,100               368              169   \n",
       "Asset Management       NaN               451               97   \n",
       "Antonveneta         37,000             2,220               na   \n",
       "Corporate centre     8,600                na            1,584   \n",
       "Private equity       3,200             2,711              429   \n",
       "Capital surplus         na           (7,178)            (251)   \n",
       "\n",
       "                   Net profit 2005A Net profit 2006E Net profit 2007E  \\\n",
       "Business                                                                \n",
       "Netherlands                     509              707              736   \n",
       "USA - commercial                766              836              898   \n",
       "USA - mortgage                  105              146              160   \n",
       "Brazil                          644              574              601   \n",
       "New Growth Markets              213              197              215   \n",
       "Bouwfonds                       315              129              123   \n",
       "Wholesale                       705              550              582   \n",
       "Private Clients                 255              329              385   \n",
       "Asset Management                171              180              193   \n",
       "Antonveneta                      na              508              555   \n",
       "Corporate centre                619              305              146   \n",
       "Private equity                  335              280              280   \n",
       "Capital surplus               (251)            (251)            (251)   \n",
       "\n",
       "                   As % 2006E Peer group PE06E Valuation As %  \n",
       "Business                                                       \n",
       "Netherlands               16%             11.8     8,345  16%  \n",
       "USA - commercial          19%             14.0    11,700  23%  \n",
       "USA - mortgage             3%              9.3     1,364   3%  \n",
       "Brazil                    13%             11.0     6,336  13%  \n",
       "New Growth Markets         4%             11.0     2,171   4%  \n",
       "Bouwfonds                  3%             11.8     1,523   3%  \n",
       "Wholesale                 12%              8.4     4,620   9%  \n",
       "Private Clients            7%             17.2     5,637  11%  \n",
       "Asset Management           4%             17.2     3,095   6%  \n",
       "Antonveneta               11%             12.9     6,580  13%  \n",
       "Corporate centre          NaN             12.4     3,765  NaN  \n",
       "Private equity            NaN               na     2,711  NaN  \n",
       "Capital surplus           NaN               na   (7,178)  NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data3 = data2.copy()\n",
    "data3.columns = ['RWAs', 'Capital allocated', 'Net profit 2004A', 'Net profit 2005A', 'Net profit 2006E', 'Net profit 2007E', 'As % 2006E', 'Peer group PE06E', 'Valuation', 'As %']\n",
    "data3.index.name = 'Business'\n",
    "data3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 4. Handle missing values\n",
    "\n",
    "- Identify missing values (blank, NA, XX, 999, ...)\n",
    "- Plan a strategy to handle them\n",
    "  - Do nothing\n",
    "  - Remove rows / columns with missing values\n",
    "  - Fill with a constant (e.g. zero, today, etc)\n",
    "  - Fill from another column (e.g. defaults)\n",
    "  - Fill using a function (e.g. average)\n",
    "  - Fill with external data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RWAs</th>\n",
       "      <th>Capital allocated</th>\n",
       "      <th>Net profit 2004A</th>\n",
       "      <th>Net profit 2005A</th>\n",
       "      <th>Net profit 2006E</th>\n",
       "      <th>Net profit 2007E</th>\n",
       "      <th>As % 2006E</th>\n",
       "      <th>Peer group PE06E</th>\n",
       "      <th>Valuation</th>\n",
       "      <th>As %</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Business</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Netherlands</th>\n",
       "      <td>47,300</td>\n",
       "      <td>2,838</td>\n",
       "      <td>229</td>\n",
       "      <td>509</td>\n",
       "      <td>707</td>\n",
       "      <td>736</td>\n",
       "      <td>16%</td>\n",
       "      <td>11.8</td>\n",
       "      <td>8,345</td>\n",
       "      <td>16%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA - commercial</th>\n",
       "      <td>66,700</td>\n",
       "      <td>4,002</td>\n",
       "      <td>488</td>\n",
       "      <td>766</td>\n",
       "      <td>836</td>\n",
       "      <td>898</td>\n",
       "      <td>19%</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11,700</td>\n",
       "      <td>23%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA - mortgage</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>182</td>\n",
       "      <td>105</td>\n",
       "      <td>146</td>\n",
       "      <td>160</td>\n",
       "      <td>3%</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1,364</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>14,800</td>\n",
       "      <td>888</td>\n",
       "      <td>302</td>\n",
       "      <td>644</td>\n",
       "      <td>574</td>\n",
       "      <td>601</td>\n",
       "      <td>13%</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6,336</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Growth Markets</th>\n",
       "      <td>6,500</td>\n",
       "      <td>390</td>\n",
       "      <td>378</td>\n",
       "      <td>213</td>\n",
       "      <td>197</td>\n",
       "      <td>215</td>\n",
       "      <td>4%</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2,171</td>\n",
       "      <td>4%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bouwfonds</th>\n",
       "      <td>25,900</td>\n",
       "      <td>1,554</td>\n",
       "      <td>265</td>\n",
       "      <td>315</td>\n",
       "      <td>129</td>\n",
       "      <td>123</td>\n",
       "      <td>3%</td>\n",
       "      <td>11.8</td>\n",
       "      <td>1,523</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wholesale</th>\n",
       "      <td>77,000</td>\n",
       "      <td>4,620</td>\n",
       "      <td>(7)</td>\n",
       "      <td>705</td>\n",
       "      <td>550</td>\n",
       "      <td>582</td>\n",
       "      <td>12%</td>\n",
       "      <td>8.4</td>\n",
       "      <td>4,620</td>\n",
       "      <td>9%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Private Clients</th>\n",
       "      <td>8,100</td>\n",
       "      <td>368</td>\n",
       "      <td>169</td>\n",
       "      <td>255</td>\n",
       "      <td>329</td>\n",
       "      <td>385</td>\n",
       "      <td>7%</td>\n",
       "      <td>17.2</td>\n",
       "      <td>5,637</td>\n",
       "      <td>11%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asset Management</th>\n",
       "      <td>NaN</td>\n",
       "      <td>451</td>\n",
       "      <td>97</td>\n",
       "      <td>171</td>\n",
       "      <td>180</td>\n",
       "      <td>193</td>\n",
       "      <td>4%</td>\n",
       "      <td>17.2</td>\n",
       "      <td>3,095</td>\n",
       "      <td>6%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Antonveneta</th>\n",
       "      <td>37,000</td>\n",
       "      <td>2,220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>508</td>\n",
       "      <td>555</td>\n",
       "      <td>11%</td>\n",
       "      <td>12.9</td>\n",
       "      <td>6,580</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corporate centre</th>\n",
       "      <td>8,600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,584</td>\n",
       "      <td>619</td>\n",
       "      <td>305</td>\n",
       "      <td>146</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.4</td>\n",
       "      <td>3,765</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Private equity</th>\n",
       "      <td>3,200</td>\n",
       "      <td>2,711</td>\n",
       "      <td>429</td>\n",
       "      <td>335</td>\n",
       "      <td>280</td>\n",
       "      <td>280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2,711</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Capital surplus</th>\n",
       "      <td>NaN</td>\n",
       "      <td>(7,178)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>(251)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(7,178)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      RWAs Capital allocated Net profit 2004A  \\\n",
       "Business                                                        \n",
       "Netherlands         47,300             2,838              229   \n",
       "USA - commercial    66,700             4,002              488   \n",
       "USA - mortgage         NaN               NaN              182   \n",
       "Brazil              14,800               888              302   \n",
       "New Growth Markets   6,500               390              378   \n",
       "Bouwfonds           25,900             1,554              265   \n",
       "Wholesale           77,000             4,620              (7)   \n",
       "Private Clients      8,100               368              169   \n",
       "Asset Management       NaN               451               97   \n",
       "Antonveneta         37,000             2,220              NaN   \n",
       "Corporate centre     8,600               NaN            1,584   \n",
       "Private equity       3,200             2,711              429   \n",
       "Capital surplus        NaN           (7,178)            (251)   \n",
       "\n",
       "                   Net profit 2005A Net profit 2006E Net profit 2007E  \\\n",
       "Business                                                                \n",
       "Netherlands                     509              707              736   \n",
       "USA - commercial                766              836              898   \n",
       "USA - mortgage                  105              146              160   \n",
       "Brazil                          644              574              601   \n",
       "New Growth Markets              213              197              215   \n",
       "Bouwfonds                       315              129              123   \n",
       "Wholesale                       705              550              582   \n",
       "Private Clients                 255              329              385   \n",
       "Asset Management                171              180              193   \n",
       "Antonveneta                     NaN              508              555   \n",
       "Corporate centre                619              305              146   \n",
       "Private equity                  335              280              280   \n",
       "Capital surplus               (251)            (251)            (251)   \n",
       "\n",
       "                   As % 2006E Peer group PE06E Valuation As %  \n",
       "Business                                                       \n",
       "Netherlands               16%             11.8     8,345  16%  \n",
       "USA - commercial          19%             14.0    11,700  23%  \n",
       "USA - mortgage             3%              9.3     1,364   3%  \n",
       "Brazil                    13%             11.0     6,336  13%  \n",
       "New Growth Markets         4%             11.0     2,171   4%  \n",
       "Bouwfonds                  3%             11.8     1,523   3%  \n",
       "Wholesale                 12%              8.4     4,620   9%  \n",
       "Private Clients            7%             17.2     5,637  11%  \n",
       "Asset Management           4%             17.2     3,095   6%  \n",
       "Antonveneta               11%             12.9     6,580  13%  \n",
       "Corporate centre          NaN             12.4     3,765  NaN  \n",
       "Private equity            NaN              NaN     2,711  NaN  \n",
       "Capital surplus           NaN              NaN   (7,178)  NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data4 = data3.replace('na', pd.np.nan)\n",
    "data4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# 5. Standardize values\n",
    "\n",
    "- Convert text to numbers or dates\n",
    "- Remove extra characters (prefixes, suffixes, spaces)\n",
    "- Correct mis-spellings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RWAs                 295100.0\n",
       "Capital allocated     12864.0\n",
       "Net profit 2004A       3865.0\n",
       "Net profit 2005A       4386.0\n",
       "Net profit 2006E       4490.0\n",
       "Net profit 2007E       4623.0\n",
       "As % 2006E               92.0\n",
       "Peer group PE06E        137.0\n",
       "Valuation             50669.0\n",
       "As %                    101.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data5 = (data4\n",
    "        .replace(',', '', regex=True)                # Remove commas\n",
    "        .replace('\\((\\d+)\\)', r'-\\1', regex=True)    # Convert (10) to -10\n",
    "        .replace('%$', '', regex=True)               # Convert % to numbers\n",
    "        .astype(float)                               # Convert to numbers\n",
    "        )\n",
    "data5.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 6. Extract unstructured data\n",
    "\n",
    "- Extract numbers with units (lbs & kgs, kph & mph)\n",
    "- Extract lists (e.g. multiple values in cells)\n",
    "- Extract dates (e.g. 25/10/16 or Oct 25, 2016)\n",
    "- Extract entities (e.g. names, places, phone numbers, PIN codes, things)\n",
    "- Extract name structure (e.g. title, first name, last name)\n",
    "\n",
    "This dataset has none of those. Let's pick another dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Product dosage composition\n",
    "\n",
    "This is a [random dataset](http://ravenbhelpharma.com/composition.xlsx) I found yesterday.\n",
    "\n",
    "![Product dosage composition](Cleansing-Data/Cleansing-Data-Pharma.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Let's clean the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>Dosage Form</th>\n",
       "      <th>Composition</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S. No.</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Osta-GM</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Diacerein 50 mg, Glucosamine sulphate 750 mg, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Revidec + Osta-D3</td>\n",
       "      <td>Combipack of injections</td>\n",
       "      <td>Nandrolone decanoate 50 mg injection + Choleca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Osta-D Cal</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Alfacalcidol 0.25 mcg, Calcium carbonate 625 m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Osta-D Cal</td>\n",
       "      <td>Suspension</td>\n",
       "      <td>Calcium (as CaCO3) 150 mg, Magnesium 25 mg, Zi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Exudase-TH 8</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Thiocolchicoside 8 mg, Diclofenac potassium 50 mg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Product              Dosage Form  \\\n",
       "S. No.                                               \n",
       "1                 Osta-GM                   Tablet   \n",
       "2       Revidec + Osta-D3  Combipack of injections   \n",
       "3              Osta-D Cal                   Tablet   \n",
       "4              Osta-D Cal               Suspension   \n",
       "5            Exudase-TH 8                   Tablet   \n",
       "\n",
       "                                              Composition  \n",
       "S. No.                                                     \n",
       "1       Diacerein 50 mg, Glucosamine sulphate 750 mg, ...  \n",
       "2       Nandrolone decanoate 50 mg injection + Choleca...  \n",
       "3       Alfacalcidol 0.25 mcg, Calcium carbonate 625 m...  \n",
       "4       Calcium (as CaCO3) 150 mg, Magnesium 25 mg, Zi...  \n",
       "5       Thiocolchicoside 8 mg, Diclofenac potassium 50 mg  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pharma = pd.read_excel('Cleansing-Data/Cleansing-Data-composition.xlsx', skiprows=[0, 1]).set_index('S. No.')\n",
    "pharma.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 5. Standardize values\n",
    "\n",
    "The \"Dosage Form\" column is not standardized."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>Dosage Form</th>\n",
       "      <th>Composition</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S. No.</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Zopra-DSR</td>\n",
       "      <td>Capsule</td>\n",
       "      <td>Rabeprazole sodium 20 mg (enteric coated pelle...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Odpant-DSR</td>\n",
       "      <td>Capsule</td>\n",
       "      <td>Pantoprazole 40 mg, Domperidone 30 mg (sustain...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Zintra-G</td>\n",
       "      <td>Capsule</td>\n",
       "      <td>Asian Ginseng extract powder, Vitamins, Minerals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Indopra</td>\n",
       "      <td>Capsule</td>\n",
       "      <td>Indomethacin 75 mg, Omeprazole 20 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Ezycart</td>\n",
       "      <td>Capsule</td>\n",
       "      <td>Rose hip powder 700 mg, Cucumin 80 mg, Wheat g...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>SFe</td>\n",
       "      <td>Chewable tablet</td>\n",
       "      <td>Sodium feredetate 231 mg, Folic acid 1.5 mg, V...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Revidec + Osta-D3</td>\n",
       "      <td>Combipack of injections</td>\n",
       "      <td>Nandrolone decanoate 50 mg injection + Choleca...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Revicof</td>\n",
       "      <td>Cough Syrup</td>\n",
       "      <td>Dextromethorphan 10 mg, Chlorpheniramine malea...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Revibrox Plus</td>\n",
       "      <td>Cough Syrup</td>\n",
       "      <td>Bromhexine 4 mg, Terbutaline 1.25 mg, Guaiphen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Revispas</td>\n",
       "      <td>Drops</td>\n",
       "      <td>Simethicone 40 mg, Dill oil 0.005 ml, Fennel o...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Hactamoz 457</td>\n",
       "      <td>Dry syrup</td>\n",
       "      <td>Amoxycillin 400 mg and clavulanic acid 57 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Testorich injection</td>\n",
       "      <td>Each 1 ml injection contains:</td>\n",
       "      <td>Testosterone propionate 30 mg, Testosterone ph...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Testorich-100</td>\n",
       "      <td>Each 1 ml injection contains:</td>\n",
       "      <td>Testosterone propionate 20 mg, Testosterone ph...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Revibrox-LS syrup</td>\n",
       "      <td>Each 5 ml contains:</td>\n",
       "      <td>Levosalbutamol sulphate equivalent to levosalb...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Ornapreg</td>\n",
       "      <td>Each eneteric coated tablet contains:</td>\n",
       "      <td>Doxylamine succinate 10 mg, Pyridoxine hcl 10 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Hactamox-625</td>\n",
       "      <td>Each film coated Tablets contains:</td>\n",
       "      <td>Amoxycillin 500 mg and clavulanic acid 125 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Osta heal</td>\n",
       "      <td>Each hard gelatin capsule contains:</td>\n",
       "      <td>Each hard gelatin capsule contains: cissus qua...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Revibrox-LS drops</td>\n",
       "      <td>Each ml contain:</td>\n",
       "      <td>Levosalbutamol sulphate equivalent to levosalb...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Emergel</td>\n",
       "      <td>Each ml contains:</td>\n",
       "      <td>Lignocaine 2.0% w/w, Choline salicylate 8.7% w...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Dolfre</td>\n",
       "      <td>Gel</td>\n",
       "      <td>Diclofenac diethylammonium 1.16% w/w, Olium li...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Modest-45</td>\n",
       "      <td>Injection</td>\n",
       "      <td>Estradiol benzoate 1 mg, Estradiol phenylpropi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Hb-Red</td>\n",
       "      <td>Injection</td>\n",
       "      <td>Iron sorbitol citric acid complex correspondin...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Osta-D3</td>\n",
       "      <td>Massage Oil</td>\n",
       "      <td>Aloe vera oil, Olive oil, Vitamins A, D, E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Dolfre</td>\n",
       "      <td>Oil</td>\n",
       "      <td>Erand mool, Kali hare, Kuchla,Vatsnaba,  Kaner...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Acolax</td>\n",
       "      <td>Oral solution</td>\n",
       "      <td>PEG 3350 USP 13.125 g, Sodium chloride IP 0.35...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Pubicare</td>\n",
       "      <td>Perineal Wash</td>\n",
       "      <td>Lactic acid, Wheat germ oil, Tea tree oil, Alo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Zintra Powder</td>\n",
       "      <td>Powder</td>\n",
       "      <td>Whey protein, DHA, EPA, Vitamins, Minerals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>XND</td>\n",
       "      <td>Roll on for use in nasal congestion</td>\n",
       "      <td>Nilgiri ka tel, Gandhapuro ka tel, Camphor, Pi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Osta-Eve</td>\n",
       "      <td>Softgelatin Capsule</td>\n",
       "      <td>Saya isoflavonoids (40%) 60 mg, Evening primro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Osta-P</td>\n",
       "      <td>Suspension</td>\n",
       "      <td>Calcium (as Calcium phosphate) 300 mg, Phospho...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Osta-D Cal</td>\n",
       "      <td>Suspension</td>\n",
       "      <td>Calcium (as CaCO3) 150 mg, Magnesium 25 mg, Zi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Zintra-M</td>\n",
       "      <td>Syrup</td>\n",
       "      <td>Methylcobalamin 250 mcg, Thiamine 5 mg, Pyrido...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Hactaflox CF</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Ofloxacin 200 mg , cefexime 200 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Hactflox OZ</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Ofloxacin 200 mg, Ornidazole 500 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Rucadol-P</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Aceclofenac 100 mg, Paracetamol 325 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Osta-GM</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Diacerein 50 mg, Glucosamine sulphate 750 mg, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Tizalud-DP</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Tizanidine 2 mg, Diclofenac potassium 50 mg, P...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Osta-D Cal</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Alfacalcidol 0.25 mcg, Calcium carbonate 625 m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Exudase-TH 8</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Thiocolchicoside 8 mg, Diclofenac potassium 50 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Exudase-TH</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Thiocolchicoside 4 mg, Diclofenac potassium 50 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Exudase-DP</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Serratiopeptidase 15 mg, Diclofenac potassium ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Exudase-D</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Serratiopeptidase 10 mg, Diclofenac potassium ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Rucadol-RB</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Aceclofenac 200 (SR), Rabeprazole 20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Gabacom</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Gabapentin 300 mg, Mecobalamin 500 mcg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Revitrol-K2</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Vitamin k27 50 mcg, Calcitriol 0.25 mcg, Calci...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Gabacom-NT</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Gabapentin 400 mg, Nortriptyline 10 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Hb-Red</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Ferrous Ascorbate eq. to elemental iron 100 mg...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Odpant-DM</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Pantoprazole 40 mg, Domperidone 10 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Dolfre Plus</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Tramadol 37.5 mg, Paracetamol 325 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Gabacom M 100</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>Gabapentin 100 mg, Mecobalamin 500 mcg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Hactamox-DS</td>\n",
       "      <td>Tablets</td>\n",
       "      <td>Amoxycillin 200 mg and clavulanic acid 28.5 mg...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Cans-3</td>\n",
       "      <td>Vaginal Suppository</td>\n",
       "      <td>Clindamycin 100 mg, Clotrimazole 200 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Camful</td>\n",
       "      <td>capsule</td>\n",
       "      <td>Rauwolfia serpentina 50 mg, asperagus racemus ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Zopra-L</td>\n",
       "      <td>capsule</td>\n",
       "      <td>Rabeprazole 20 mg, Levosulpride 75 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Revibact forte</td>\n",
       "      <td>injection</td>\n",
       "      <td>Cefoperazone 1 gm, sulbactam 0.5 gm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Revitrol K2</td>\n",
       "      <td>suspension</td>\n",
       "      <td>Calcium citrate 150 mg, zinc 4 mg, vit.d3 200 IU</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Product                            Dosage Form  \\\n",
       "S. No.                                                                \n",
       "37                 Zopra-DSR                                Capsule   \n",
       "23                Odpant-DSR                                Capsule   \n",
       "13                  Zintra-G                                Capsule   \n",
       "33                   Indopra                               Capsule    \n",
       "47                  Ezycart                                Capsule    \n",
       "24                       SFe                        Chewable tablet   \n",
       "2          Revidec + Osta-D3                Combipack of injections   \n",
       "39                   Revicof                            Cough Syrup   \n",
       "19             Revibrox Plus                            Cough Syrup   \n",
       "18                  Revispas                                  Drops   \n",
       "45              Hactamoz 457                              Dry syrup   \n",
       "48      Testorich injection           Each 1 ml injection contains:   \n",
       "49            Testorich-100           Each 1 ml injection contains:   \n",
       "55         Revibrox-LS syrup                    Each 5 ml contains:   \n",
       "53                 Ornapreg   Each eneteric coated tablet contains:   \n",
       "44              Hactamox-625     Each film coated Tablets contains:   \n",
       "41                 Osta heal    Each hard gelatin capsule contains:   \n",
       "56         Revibrox-LS drops                       Each ml contain:   \n",
       "40                   Emergel                      Each ml contains:   \n",
       "30                    Dolfre                                  Gel     \n",
       "27                 Modest-45                              Injection   \n",
       "21                    Hb-Red                              Injection   \n",
       "17                   Osta-D3                            Massage Oil   \n",
       "31                    Dolfre                                    Oil   \n",
       "54                    Acolax                          Oral solution   \n",
       "26                  Pubicare                          Perineal Wash   \n",
       "14             Zintra Powder                                 Powder   \n",
       "38                       XND    Roll on for use in nasal congestion   \n",
       "15                  Osta-Eve                    Softgelatin Capsule   \n",
       "16                    Osta-P                             Suspension   \n",
       "4                 Osta-D Cal                             Suspension   \n",
       "12                  Zintra-M                                  Syrup   \n",
       "43              Hactaflox CF                                 Tablet   \n",
       "42               Hactflox OZ                                 Tablet   \n",
       "36                 Rucadol-P                                 Tablet   \n",
       "1                    Osta-GM                                 Tablet   \n",
       "34                Tizalud-DP                                 Tablet   \n",
       "3                 Osta-D Cal                                 Tablet   \n",
       "5               Exudase-TH 8                                 Tablet   \n",
       "6                 Exudase-TH                                 Tablet   \n",
       "7                 Exudase-DP                                 Tablet   \n",
       "8                 Exudase-D                                  Tablet   \n",
       "35                Rucadol-RB                                 Tablet   \n",
       "9                    Gabacom                                 Tablet   \n",
       "28               Revitrol-K2                                 Tablet   \n",
       "11                Gabacom-NT                                 Tablet   \n",
       "20                    Hb-Red                                 Tablet   \n",
       "22                 Odpant-DM                                 Tablet   \n",
       "32               Dolfre Plus                                 Tablet   \n",
       "10             Gabacom M 100                                 Tablet   \n",
       "46               Hactamox-DS                                Tablets   \n",
       "25                    Cans-3                    Vaginal Suppository   \n",
       "50                    Camful                                capsule   \n",
       "51                   Zopra-L                                capsule   \n",
       "52            Revibact forte                              injection   \n",
       "29              Revitrol K2                              suspension   \n",
       "\n",
       "                                              Composition  \n",
       "S. No.                                                     \n",
       "37      Rabeprazole sodium 20 mg (enteric coated pelle...  \n",
       "23      Pantoprazole 40 mg, Domperidone 30 mg (sustain...  \n",
       "13       Asian Ginseng extract powder, Vitamins, Minerals  \n",
       "33                   Indomethacin 75 mg, Omeprazole 20 mg  \n",
       "47      Rose hip powder 700 mg, Cucumin 80 mg, Wheat g...  \n",
       "24      Sodium feredetate 231 mg, Folic acid 1.5 mg, V...  \n",
       "2       Nandrolone decanoate 50 mg injection + Choleca...  \n",
       "39      Dextromethorphan 10 mg, Chlorpheniramine malea...  \n",
       "19      Bromhexine 4 mg, Terbutaline 1.25 mg, Guaiphen...  \n",
       "18      Simethicone 40 mg, Dill oil 0.005 ml, Fennel o...  \n",
       "45           Amoxycillin 400 mg and clavulanic acid 57 mg  \n",
       "48      Testosterone propionate 30 mg, Testosterone ph...  \n",
       "49      Testosterone propionate 20 mg, Testosterone ph...  \n",
       "55      Levosalbutamol sulphate equivalent to levosalb...  \n",
       "53       Doxylamine succinate 10 mg, Pyridoxine hcl 10 mg  \n",
       "44          Amoxycillin 500 mg and clavulanic acid 125 mg  \n",
       "41      Each hard gelatin capsule contains: cissus qua...  \n",
       "56      Levosalbutamol sulphate equivalent to levosalb...  \n",
       "40      Lignocaine 2.0% w/w, Choline salicylate 8.7% w...  \n",
       "30      Diclofenac diethylammonium 1.16% w/w, Olium li...  \n",
       "27      Estradiol benzoate 1 mg, Estradiol phenylpropi...  \n",
       "21      Iron sorbitol citric acid complex correspondin...  \n",
       "17             Aloe vera oil, Olive oil, Vitamins A, D, E  \n",
       "31      Erand mool, Kali hare, Kuchla,Vatsnaba,  Kaner...  \n",
       "54      PEG 3350 USP 13.125 g, Sodium chloride IP 0.35...  \n",
       "26      Lactic acid, Wheat germ oil, Tea tree oil, Alo...  \n",
       "14             Whey protein, DHA, EPA, Vitamins, Minerals  \n",
       "38      Nilgiri ka tel, Gandhapuro ka tel, Camphor, Pi...  \n",
       "15      Saya isoflavonoids (40%) 60 mg, Evening primro...  \n",
       "16      Calcium (as Calcium phosphate) 300 mg, Phospho...  \n",
       "4       Calcium (as CaCO3) 150 mg, Magnesium 25 mg, Zi...  \n",
       "12      Methylcobalamin 250 mcg, Thiamine 5 mg, Pyrido...  \n",
       "43                     Ofloxacin 200 mg , cefexime 200 mg  \n",
       "42                    Ofloxacin 200 mg, Ornidazole 500 mg  \n",
       "36                 Aceclofenac 100 mg, Paracetamol 325 mg  \n",
       "1       Diacerein 50 mg, Glucosamine sulphate 750 mg, ...  \n",
       "34      Tizanidine 2 mg, Diclofenac potassium 50 mg, P...  \n",
       "3       Alfacalcidol 0.25 mcg, Calcium carbonate 625 m...  \n",
       "5       Thiocolchicoside 8 mg, Diclofenac potassium 50 mg  \n",
       "6       Thiocolchicoside 4 mg, Diclofenac potassium 50 mg  \n",
       "7       Serratiopeptidase 15 mg, Diclofenac potassium ...  \n",
       "8       Serratiopeptidase 10 mg, Diclofenac potassium ...  \n",
       "35                   Aceclofenac 200 (SR), Rabeprazole 20  \n",
       "9                  Gabapentin 300 mg, Mecobalamin 500 mcg  \n",
       "28      Vitamin k27 50 mcg, Calcitriol 0.25 mcg, Calci...  \n",
       "11                 Gabapentin 400 mg, Nortriptyline 10 mg  \n",
       "20      Ferrous Ascorbate eq. to elemental iron 100 mg...  \n",
       "22                  Pantoprazole 40 mg, Domperidone 10 mg  \n",
       "32                  Tramadol 37.5 mg, Paracetamol 325 mg   \n",
       "10                 Gabapentin 100 mg, Mecobalamin 500 mcg  \n",
       "46      Amoxycillin 200 mg and clavulanic acid 28.5 mg...  \n",
       "25                Clindamycin 100 mg, Clotrimazole 200 mg  \n",
       "50      Rauwolfia serpentina 50 mg, asperagus racemus ...  \n",
       "51                  Rabeprazole 20 mg, Levosulpride 75 mg  \n",
       "52                    Cefoperazone 1 gm, sulbactam 0.5 gm  \n",
       "29       Calcium citrate 150 mg, zinc 4 mg, vit.d3 200 IU  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pharma.sort_values('Dosage Form', ascending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 5. Standardize values with fingerprints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'TBLT'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from jellyfish import metaphone, soundex, nysiis, match_rating_codex\n",
    "metaphone('tablot')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "S. No.\n",
       "1                          TBLT\n",
       "2              KMBPK OF INJKXNS\n",
       "3                          TBLT\n",
       "4                        SSPNXN\n",
       "5                          TBLT\n",
       "6                          TBLT\n",
       "7                          TBLT\n",
       "8                          TBLT\n",
       "9                          TBLT\n",
       "10                         TBLT\n",
       "11                         TBLT\n",
       "12                          SRP\n",
       "13                         KPSL\n",
       "14                          PTR\n",
       "15                 SFTJLTN KPSL\n",
       "16                       SSPNXN\n",
       "17                       MSJ OL\n",
       "18                         TRPS\n",
       "19                        K SRP\n",
       "20                         TBLT\n",
       "21                       INJKXN\n",
       "22                         TBLT\n",
       "23                         KPSL\n",
       "24                    XWBL TBLT\n",
       "25                   FJNL SPSTR\n",
       "26                      PRNL WX\n",
       "27                       INJKXN\n",
       "28                         TBLT\n",
       "29                       SSPNXN\n",
       "30                          JL \n",
       "31                           OL\n",
       "32                         TBLT\n",
       "33                        KPSL \n",
       "34                         TBLT\n",
       "35                         TBLT\n",
       "36                         TBLT\n",
       "37                         KPSL\n",
       "38    RL ON FR US IN NSL KNJSXN\n",
       "39                        K SRP\n",
       "40                  EX ML KNTNS\n",
       "41       EX HRT JLTN KPSL KNTNS\n",
       "42                         TBLT\n",
       "43                         TBLT\n",
       "44       EX FLM KTT TBLTS KNTNS\n",
       "45                       TR SRP\n",
       "46                        TBLTS\n",
       "47                        KPSL \n",
       "48          EX  ML INJKXN KNTNS\n",
       "49          EX  ML INJKXN KNTNS\n",
       "50                         KPSL\n",
       "51                         KPSL\n",
       "52                       INJKXN\n",
       "53      EX ENTRK KTT TBLT KNTNS\n",
       "54                     ORL SLXN\n",
       "55                 EX  ML KNTNS\n",
       "56                   EX ML KNTN\n",
       "Name: Dosage Form, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pharma['Dosage Form'].apply(metaphone)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[array(['Capsule', 'Capsule ', 'capsule'], dtype=object),\n",
       " array(['Chewable tablet'], dtype=object),\n",
       " array(['Cough Syrup'], dtype=object),\n",
       " array(['Combipack of injections'], dtype=object),\n",
       " array(['Drops'], dtype=object),\n",
       " array(['Dry syrup'], dtype=object),\n",
       " array(['Each film coated Tablets contains:'], dtype=object),\n",
       " array(['Each eneteric coated tablet contains:'], dtype=object),\n",
       " array(['Each ml contains:', 'Each 1 ml injection contains:',\n",
       "        'Each 5 ml contains:', 'Each ml contain:'], dtype=object),\n",
       " array(['Each hard gelatin capsule contains:'], dtype=object),\n",
       " array(['Gel  '], dtype=object),\n",
       " array(['Injection', 'injection'], dtype=object),\n",
       " array(['Massage Oil'], dtype=object),\n",
       " array(['Oil'], dtype=object),\n",
       " array(['Oral solution'], dtype=object),\n",
       " array(['Powder'], dtype=object),\n",
       " array(['Perineal Wash'], dtype=object),\n",
       " array(['Roll on for use in nasal congestion'], dtype=object),\n",
       " array(['Softgelatin Capsule'], dtype=object),\n",
       " array(['Suspension', 'suspension'], dtype=object),\n",
       " array(['Syrup'], dtype=object),\n",
       " array(['Tablet', 'Tablets'], dtype=object),\n",
       " array(['Vaginal Suppository'], dtype=object)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pharma.groupby(pharma['Dosage Form'].apply(soundex))['Dosage Form'].unique().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 5. Standardize values: custom fingerprints\n",
    "\n",
    "You can create your own fuzzy matching algorithms too.\n",
    "\n",
    "For example, let's listen to a few [Hindi songs from AR Rahman](http://www.s-anand.net/blog/a-r-rahman-hindi-songs/)\n",
    "\n",
    "![Hindi Songs Quiz](Cleansing-Data/Cleansing-Data-Hindi-Songs.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 5. Standardize values: custom fingerprints\n",
    "\n",
    "For this, I used a custom fingerprinting algorithm suited for Hindi movies and artistes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "def fingerprint_hindi(s):    \n",
    "        s = s + ' '\n",
    "        s = s.upper()                               # Upper case\n",
    "        s = re.sub(r'\\s+', ' ', s)                  # Single space\n",
    "        s = re.sub(r'W', 'V', s)                    # Vivaah or Wivaah?\n",
    "        s = re.sub(r'KSH', 'X', s)                  # Dixit or Deekshit?\n",
    "        s = re.sub(r'Z', 'J', s)                    # Jabardast or Zabardast?\n",
    "        s = re.sub(r'PH', 'F', s)                   # Phirangi or Firangi?\n",
    "        s = re.sub(r'([KGCJTDPBS])H', r'\\1', s)     # Dheeraj or Diraj?\n",
    "        s = re.sub(r'(.)\\1', r'\\1', s)              # Kaala or Kala?\n",
    "        s = re.sub(r'Y', '', s)                     # Koyi or Koi?\n",
    "        s = re.sub(r'([AEIOU])N ', r'\\1', s)        # Hein or Hai?\n",
    "        s = re.sub(r'[AEIOU]', '', s)               # Rehman or Rahman?\n",
    "        s = re.sub(r' ', '', s)                     # Remove spaces\n",
    "        return s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert fingerprint_hindi('Meenakshi Dixit') == fingerprint_hindi('minaxi  deekshith') == 'MNXDXT'\n",
    "assert fingerprint_hindi('Ye Dil Hai Mushkil') == fingerprint_hindi('e dil hein muskil') == 'DLHMSKL'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# Here's an example of the OpenRefine fingerprint algorithm \n",
    "# Source: https://gist.github.com/cjdd3b/0386f139bb953f046c6e\n",
    "import re\n",
    "import string\n",
    "from unidecode import unidecode    # install this\n",
    "\n",
    "PUNCTUATION = re.compile('[%s]' % re.escape(string.punctuation))\n",
    "\n",
    "def preprocess(string):\n",
    "    return PUNCTUATION.sub('', string.strip().lower())\n",
    "\n",
    "def unique_preserving_order(seq):\n",
    "    seen = set()\n",
    "    seen_add = seen.add\n",
    "    return [x for x in seq if not (x in seen or seen_add(x))]\n",
    "        \n",
    "def fingerprint(s):\n",
    "    s = preprocess(s)\n",
    "    return unidecode(' '.join(unique_preserving_order(sorted(s.split()))))\n",
    "\n",
    "def ngram_fingerprint(s, n=1):\n",
    "    s = preprocess(s)\n",
    "    return unidecode(''.join(unique_preserving_order(sorted([s[i:i + n] for i in range(len(s) - n + 1)]))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 5. Standardize values: constituencies\n",
    "\n",
    "Let's apply fingerprinting to the list of assembly constituency names across years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\anaconda\\3.6\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2785: DtypeWarning: Columns (1,8,11) 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",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\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>AC_NAME</th>\n",
       "      <th>AC_TYPE</th>\n",
       "      <th>#</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>ICHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</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</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>ICHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</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</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>ICHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</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</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>ICHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</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</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>1</td>\n",
       "      <td>ICHAPURAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>5</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</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>2</td>\n",
       "      <td>SOMPETA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>GOUTU LATCHANNA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KLP</td>\n",
       "      <td>21436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>2</td>\n",
       "      <td>SOMPETA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>MARUPPU PAMANABHAM</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CPI</td>\n",
       "      <td>9261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>2</td>\n",
       "      <td>SOMPETA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>PUDI LOKANADHAM</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>BRAHMANATARALA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>NICHARIA RAMULU</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KLP</td>\n",
       "      <td>11243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>BRAHMANATARALA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>UPPADA RAMARAO</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CPI</td>\n",
       "      <td>6034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>BRAHMANATARALA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>BAPA MALLESWARA REDDY</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>4894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>BRAHMANATARALA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</td>\n",
       "      <td>MALLAREDDI NARAYANARAO DORA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>2914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>BRAHMANATARALA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>5</td>\n",
       "      <td>KURAMANA PAPARAO</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>2450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>3</td>\n",
       "      <td>BRAHMANATARALA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>6</td>\n",
       "      <td>GARIMELLA JAGANN ADHARAO</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>1778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>4</td>\n",
       "      <td>TEKKALI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>ROKKAM LAKSHMI NARASIMHAM DORA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INC</td>\n",
       "      <td>11252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>4</td>\n",
       "      <td>TEKKALI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>BENDI KUMANNA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>10716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>4</td>\n",
       "      <td>TEKKALI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>MOSALIKANTI VENKARARAMADASU</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CPI</td>\n",
       "      <td>3949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>4</td>\n",
       "      <td>TEKKALI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</td>\n",
       "      <td>DUKKA SURYANARAYANA REDDY</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>3519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>4</td>\n",
       "      <td>TEKKALI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>5</td>\n",
       "      <td>HANUMANTA SATYANARAYANA DORA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>2902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>4</td>\n",
       "      <td>TEKKALI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>6</td>\n",
       "      <td>GARIMELLA VENKATA RAMANAMURTY</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>1369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>5</td>\n",
       "      <td>NARASANNAPETA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>SIMMA JAGANNADHAM</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KLP</td>\n",
       "      <td>9902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>5</td>\n",
       "      <td>NARASANNAPETA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>VANDANA SATYANARAYANA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CPI</td>\n",
       "      <td>6847</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>5</td>\n",
       "      <td>NARASANNAPETA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>MUDDADA LATCHUNNAIDU</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>6598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>5</td>\n",
       "      <td>NARASANNAPETA</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</td>\n",
       "      <td>PUDIPEDDI SUBBARAO</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>6</td>\n",
       "      <td>PATAPATNAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>LUKULAPU LAKSHMANADAS</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INC</td>\n",
       "      <td>24293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>6</td>\n",
       "      <td>PATAPATNAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>POTHULA GUNNAYYA</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INC</td>\n",
       "      <td>19672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>6</td>\n",
       "      <td>PATAPATNAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>GHASI RAMULU</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CPI</td>\n",
       "      <td>13680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>6</td>\n",
       "      <td>PATAPATNAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</td>\n",
       "      <td>DARAPU GOVINDARAJULU</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CPI</td>\n",
       "      <td>13189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>6</td>\n",
       "      <td>PATAPATNAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>5</td>\n",
       "      <td>MOJURU SANGAMESU</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>4545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>1955</td>\n",
       "      <td>6</td>\n",
       "      <td>PATAPATNAM</td>\n",
       "      <td>GEN</td>\n",
       "      <td>6</td>\n",
       "      <td>ALIKAN APPALASWAMY</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IND</td>\n",
       "      <td>3235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391461</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>290</td>\n",
       "      <td>MAYURESWAR</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>ASOK RAY</td>\n",
       "      <td>M</td>\n",
       "      <td>64</td>\n",
       "      <td>GEN</td>\n",
       "      <td>CPM</td>\n",
       "      <td>67478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391462</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>290</td>\n",
       "      <td>MAYURESWAR</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>JATIL MONDAL</td>\n",
       "      <td>M</td>\n",
       "      <td>47</td>\n",
       "      <td>GEN</td>\n",
       "      <td>AITC</td>\n",
       "      <td>60958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391463</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>290</td>\n",
       "      <td>MAYURESWAR</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>DUDH KUMAR MONDAL</td>\n",
       "      <td>M</td>\n",
       "      <td>55</td>\n",
       "      <td>GEN</td>\n",
       "      <td>BJP</td>\n",
       "      <td>31031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391464</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>291</td>\n",
       "      <td>RAMPURHAT</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>ASISH BANERJEE</td>\n",
       "      <td>M</td>\n",
       "      <td>59</td>\n",
       "      <td>GEN</td>\n",
       "      <td>AITC</td>\n",
       "      <td>75066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391465</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>291</td>\n",
       "      <td>RAMPURHAT</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>REBATI BHATTACHARYA</td>\n",
       "      <td>M</td>\n",
       "      <td>63</td>\n",
       "      <td>GEN</td>\n",
       "      <td>AIFB</td>\n",
       "      <td>64828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391466</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>291</td>\n",
       "      <td>RAMPURHAT</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>SUBHASIS</td>\n",
       "      <td>M</td>\n",
       "      <td>46</td>\n",
       "      <td>GEN</td>\n",
       "      <td>BJP</td>\n",
       "      <td>17815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391467</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>291</td>\n",
       "      <td>RAMPURHAT</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</td>\n",
       "      <td>MAHESH KISKU</td>\n",
       "      <td>M</td>\n",
       "      <td>27</td>\n",
       "      <td>ST</td>\n",
       "      <td>IND</td>\n",
       "      <td>3769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391468</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>291</td>\n",
       "      <td>RAMPURHAT</td>\n",
       "      <td>GEN</td>\n",
       "      <td>5</td>\n",
       "      <td>PRADYOT MUKHERJEE</td>\n",
       "      <td>M</td>\n",
       "      <td>46</td>\n",
       "      <td>GEN</td>\n",
       "      <td>CPI(ML)(L)</td>\n",
       "      <td>2427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391469</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>292</td>\n",
       "      <td>HANSAN</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>ASIT MAL</td>\n",
       "      <td>M</td>\n",
       "      <td>55</td>\n",
       "      <td>SC</td>\n",
       "      <td>INC</td>\n",
       "      <td>73370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391470</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>292</td>\n",
       "      <td>HANSAN</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>KAMAL HASAN</td>\n",
       "      <td>M</td>\n",
       "      <td>31</td>\n",
       "      <td>GEN</td>\n",
       "      <td>RCPI(R)</td>\n",
       "      <td>46918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391471</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>292</td>\n",
       "      <td>HANSAN</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>MD. RAFIUDDIN (MOMIN)</td>\n",
       "      <td>M</td>\n",
       "      <td>48</td>\n",
       "      <td>GEN</td>\n",
       "      <td>IND</td>\n",
       "      <td>27461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391472</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>292</td>\n",
       "      <td>HANSAN</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</td>\n",
       "      <td>RUPARANI MONDAL</td>\n",
       "      <td>F</td>\n",
       "      <td>43</td>\n",
       "      <td>SC</td>\n",
       "      <td>BJP</td>\n",
       "      <td>3373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391473</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>292</td>\n",
       "      <td>HANSAN</td>\n",
       "      <td>GEN</td>\n",
       "      <td>5</td>\n",
       "      <td>AMAL MONDAL</td>\n",
       "      <td>M</td>\n",
       "      <td>34</td>\n",
       "      <td>GEN</td>\n",
       "      <td>SUCI</td>\n",
       "      <td>2503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391474</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>292</td>\n",
       "      <td>HANSAN</td>\n",
       "      <td>GEN</td>\n",
       "      <td>6</td>\n",
       "      <td>RADHESHYAM SINGH</td>\n",
       "      <td>M</td>\n",
       "      <td>65</td>\n",
       "      <td>GEN</td>\n",
       "      <td>BSP</td>\n",
       "      <td>1265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391475</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>292</td>\n",
       "      <td>HANSAN</td>\n",
       "      <td>GEN</td>\n",
       "      <td>7</td>\n",
       "      <td>JIYAR ALI</td>\n",
       "      <td>M</td>\n",
       "      <td>46</td>\n",
       "      <td>GEN</td>\n",
       "      <td>JP</td>\n",
       "      <td>1081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391476</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>292</td>\n",
       "      <td>HANSAN</td>\n",
       "      <td>GEN</td>\n",
       "      <td>8</td>\n",
       "      <td>AZIZUL HAQUE(LALTU)</td>\n",
       "      <td>M</td>\n",
       "      <td>35</td>\n",
       "      <td>GEN</td>\n",
       "      <td>IND</td>\n",
       "      <td>1050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391477</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>293</td>\n",
       "      <td>NALHATI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>ABHIJIT MUKHERJEE</td>\n",
       "      <td>M</td>\n",
       "      <td>58</td>\n",
       "      <td>GEN</td>\n",
       "      <td>INC</td>\n",
       "      <td>76047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391478</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>293</td>\n",
       "      <td>NALHATI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>DIPAK CHATTERJEE</td>\n",
       "      <td>M</td>\n",
       "      <td>46</td>\n",
       "      <td>GEN</td>\n",
       "      <td>AIFB</td>\n",
       "      <td>60887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391479</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>293</td>\n",
       "      <td>NALHATI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>ANIL SINGHA</td>\n",
       "      <td>M</td>\n",
       "      <td>45</td>\n",
       "      <td>GEN</td>\n",
       "      <td>BJP</td>\n",
       "      <td>10656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391480</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>293</td>\n",
       "      <td>NALHATI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</td>\n",
       "      <td>BIKASH MONDAL</td>\n",
       "      <td>M</td>\n",
       "      <td>35</td>\n",
       "      <td>GEN</td>\n",
       "      <td>BSP</td>\n",
       "      <td>2717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391481</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>293</td>\n",
       "      <td>NALHATI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>5</td>\n",
       "      <td>RUDRADEB GHSOH</td>\n",
       "      <td>M</td>\n",
       "      <td>58</td>\n",
       "      <td>GEN</td>\n",
       "      <td>IND</td>\n",
       "      <td>1516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391482</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>293</td>\n",
       "      <td>NALHATI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>6</td>\n",
       "      <td>AJRAIL MD</td>\n",
       "      <td>M</td>\n",
       "      <td>32</td>\n",
       "      <td>GEN</td>\n",
       "      <td>IND</td>\n",
       "      <td>1258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391483</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>293</td>\n",
       "      <td>NALHATI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>7</td>\n",
       "      <td>RAFIQUL HASAN</td>\n",
       "      <td>M</td>\n",
       "      <td>59</td>\n",
       "      <td>GEN</td>\n",
       "      <td>SUCI</td>\n",
       "      <td>1123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391484</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>293</td>\n",
       "      <td>NALHATI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>8</td>\n",
       "      <td>PRABHAT KUMAR MONDAL</td>\n",
       "      <td>M</td>\n",
       "      <td>39</td>\n",
       "      <td>SC</td>\n",
       "      <td>IND</td>\n",
       "      <td>912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391485</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>294</td>\n",
       "      <td>MURARAI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>1</td>\n",
       "      <td>NURE ALAM CHOWDHURY</td>\n",
       "      <td>M</td>\n",
       "      <td>68</td>\n",
       "      <td>GEN</td>\n",
       "      <td>AITC</td>\n",
       "      <td>77817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391486</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>294</td>\n",
       "      <td>MURARAI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>2</td>\n",
       "      <td>ELAHI KAMRE DR</td>\n",
       "      <td>M</td>\n",
       "      <td>52</td>\n",
       "      <td>GEN</td>\n",
       "      <td>CPM</td>\n",
       "      <td>73414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391487</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>294</td>\n",
       "      <td>MURARAI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>3</td>\n",
       "      <td>SUDHIR RANJAN DAS</td>\n",
       "      <td>M</td>\n",
       "      <td>53</td>\n",
       "      <td>GEN</td>\n",
       "      <td>BJP</td>\n",
       "      <td>4961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391488</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>294</td>\n",
       "      <td>MURARAI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>4</td>\n",
       "      <td>SHIBLAL SARDAR</td>\n",
       "      <td>M</td>\n",
       "      <td>36</td>\n",
       "      <td>ST</td>\n",
       "      <td>BSP</td>\n",
       "      <td>3074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391489</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>294</td>\n",
       "      <td>MURARAI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>5</td>\n",
       "      <td>AJRAIL MD</td>\n",
       "      <td>M</td>\n",
       "      <td>39</td>\n",
       "      <td>GEN</td>\n",
       "      <td>IND</td>\n",
       "      <td>2471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391490</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>2011</td>\n",
       "      <td>294</td>\n",
       "      <td>MURARAI</td>\n",
       "      <td>GEN</td>\n",
       "      <td>6</td>\n",
       "      <td>MIR MUKID(HAZI REKIB)</td>\n",
       "      <td>M</td>\n",
       "      <td>63</td>\n",
       "      <td>GEN</td>\n",
       "      <td>JP</td>\n",
       "      <td>1228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>391491 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               ST_NAME  YEAR  AC_NO         AC_NAME AC_TYPE  #  \\\n",
       "0       Andhra Pradesh  1955      1       ICHAPURAM     GEN  1   \n",
       "1       Andhra Pradesh  1955      1       ICHAPURAM     GEN  2   \n",
       "2       Andhra Pradesh  1955      1       ICHAPURAM     GEN  3   \n",
       "3       Andhra Pradesh  1955      1       ICHAPURAM     GEN  4   \n",
       "4       Andhra Pradesh  1955      1       ICHAPURAM     GEN  5   \n",
       "5       Andhra Pradesh  1955      2         SOMPETA     GEN  1   \n",
       "6       Andhra Pradesh  1955      2         SOMPETA     GEN  2   \n",
       "7       Andhra Pradesh  1955      2         SOMPETA     GEN  3   \n",
       "8       Andhra Pradesh  1955      3  BRAHMANATARALA     GEN  1   \n",
       "9       Andhra Pradesh  1955      3  BRAHMANATARALA     GEN  2   \n",
       "10      Andhra Pradesh  1955      3  BRAHMANATARALA     GEN  3   \n",
       "11      Andhra Pradesh  1955      3  BRAHMANATARALA     GEN  4   \n",
       "12      Andhra Pradesh  1955      3  BRAHMANATARALA     GEN  5   \n",
       "13      Andhra Pradesh  1955      3  BRAHMANATARALA     GEN  6   \n",
       "14      Andhra Pradesh  1955      4         TEKKALI     GEN  1   \n",
       "15      Andhra Pradesh  1955      4         TEKKALI     GEN  2   \n",
       "16      Andhra Pradesh  1955      4         TEKKALI     GEN  3   \n",
       "17      Andhra Pradesh  1955      4         TEKKALI     GEN  4   \n",
       "18      Andhra Pradesh  1955      4         TEKKALI     GEN  5   \n",
       "19      Andhra Pradesh  1955      4         TEKKALI     GEN  6   \n",
       "20      Andhra Pradesh  1955      5   NARASANNAPETA     GEN  1   \n",
       "21      Andhra Pradesh  1955      5   NARASANNAPETA     GEN  2   \n",
       "22      Andhra Pradesh  1955      5   NARASANNAPETA     GEN  3   \n",
       "23      Andhra Pradesh  1955      5   NARASANNAPETA     GEN  4   \n",
       "24      Andhra Pradesh  1955      6      PATAPATNAM     GEN  1   \n",
       "25      Andhra Pradesh  1955      6      PATAPATNAM     GEN  2   \n",
       "26      Andhra Pradesh  1955      6      PATAPATNAM     GEN  3   \n",
       "27      Andhra Pradesh  1955      6      PATAPATNAM     GEN  4   \n",
       "28      Andhra Pradesh  1955      6      PATAPATNAM     GEN  5   \n",
       "29      Andhra Pradesh  1955      6      PATAPATNAM     GEN  6   \n",
       "...                ...   ...    ...             ...     ... ..   \n",
       "391461     West Bengal  2011    290      MAYURESWAR     GEN  1   \n",
       "391462     West Bengal  2011    290      MAYURESWAR     GEN  2   \n",
       "391463     West Bengal  2011    290      MAYURESWAR     GEN  3   \n",
       "391464     West Bengal  2011    291       RAMPURHAT     GEN  1   \n",
       "391465     West Bengal  2011    291       RAMPURHAT     GEN  2   \n",
       "391466     West Bengal  2011    291       RAMPURHAT     GEN  3   \n",
       "391467     West Bengal  2011    291       RAMPURHAT     GEN  4   \n",
       "391468     West Bengal  2011    291       RAMPURHAT     GEN  5   \n",
       "391469     West Bengal  2011    292          HANSAN     GEN  1   \n",
       "391470     West Bengal  2011    292          HANSAN     GEN  2   \n",
       "391471     West Bengal  2011    292          HANSAN     GEN  3   \n",
       "391472     West Bengal  2011    292          HANSAN     GEN  4   \n",
       "391473     West Bengal  2011    292          HANSAN     GEN  5   \n",
       "391474     West Bengal  2011    292          HANSAN     GEN  6   \n",
       "391475     West Bengal  2011    292          HANSAN     GEN  7   \n",
       "391476     West Bengal  2011    292          HANSAN     GEN  8   \n",
       "391477     West Bengal  2011    293         NALHATI     GEN  1   \n",
       "391478     West Bengal  2011    293         NALHATI     GEN  2   \n",
       "391479     West Bengal  2011    293         NALHATI     GEN  3   \n",
       "391480     West Bengal  2011    293         NALHATI     GEN  4   \n",
       "391481     West Bengal  2011    293         NALHATI     GEN  5   \n",
       "391482     West Bengal  2011    293         NALHATI     GEN  6   \n",
       "391483     West Bengal  2011    293         NALHATI     GEN  7   \n",
       "391484     West Bengal  2011    293         NALHATI     GEN  8   \n",
       "391485     West Bengal  2011    294         MURARAI     GEN  1   \n",
       "391486     West Bengal  2011    294         MURARAI     GEN  2   \n",
       "391487     West Bengal  2011    294         MURARAI     GEN  3   \n",
       "391488     West Bengal  2011    294         MURARAI     GEN  4   \n",
       "391489     West Bengal  2011    294         MURARAI     GEN  5   \n",
       "391490     West Bengal  2011    294         MURARAI     GEN  6   \n",
       "\n",
       "                                  NAME SEX  AGE CATEGORY       PARTY  VOTES  \n",
       "0                     UPPADA RANGABABU   M  NaN      NaN         KLP  14565  \n",
       "1                     HARIHARA PATNAIK   M  NaN      NaN         IND   7408  \n",
       "2                      PUDI LOKANADHAM   M  NaN      NaN         IND   6508  \n",
       "3                 KALLA BALARAMA SWAMY   M  NaN      NaN         IND   3002  \n",
       "4                   BANIKINKARA SHARMA   M  NaN      NaN         IND    682  \n",
       "5                      GOUTU LATCHANNA   M  NaN      NaN         KLP  21436  \n",
       "6                   MARUPPU PAMANABHAM   M  NaN      NaN         CPI   9261  \n",
       "7                      PUDI LOKANADHAM   M  NaN      NaN         IND    934  \n",
       "8                      NICHARIA RAMULU   M  NaN      NaN         KLP  11243  \n",
       "9                       UPPADA RAMARAO   M  NaN      NaN         CPI   6034  \n",
       "10               BAPA MALLESWARA REDDY   M  NaN      NaN         IND   4894  \n",
       "11         MALLAREDDI NARAYANARAO DORA   M  NaN      NaN         IND   2914  \n",
       "12                    KURAMANA PAPARAO   M  NaN      NaN         IND   2450  \n",
       "13            GARIMELLA JAGANN ADHARAO   M  NaN      NaN         IND   1778  \n",
       "14      ROKKAM LAKSHMI NARASIMHAM DORA   M  NaN      NaN         INC  11252  \n",
       "15                       BENDI KUMANNA   M  NaN      NaN         IND  10716  \n",
       "16         MOSALIKANTI VENKARARAMADASU   M  NaN      NaN         CPI   3949  \n",
       "17           DUKKA SURYANARAYANA REDDY   M  NaN      NaN         IND   3519  \n",
       "18        HANUMANTA SATYANARAYANA DORA   M  NaN      NaN         IND   2902  \n",
       "19       GARIMELLA VENKATA RAMANAMURTY   M  NaN      NaN         IND   1369  \n",
       "20                   SIMMA JAGANNADHAM   M  NaN      NaN         KLP   9902  \n",
       "21               VANDANA SATYANARAYANA   M  NaN      NaN         CPI   6847  \n",
       "22                MUDDADA LATCHUNNAIDU   M  NaN      NaN         IND   6598  \n",
       "23                  PUDIPEDDI SUBBARAO   M  NaN      NaN         IND    903  \n",
       "24               LUKULAPU LAKSHMANADAS   M  NaN      NaN         INC  24293  \n",
       "25                    POTHULA GUNNAYYA   M  NaN      NaN         INC  19672  \n",
       "26                        GHASI RAMULU   M  NaN      NaN         CPI  13680  \n",
       "27                DARAPU GOVINDARAJULU   M  NaN      NaN         CPI  13189  \n",
       "28                    MOJURU SANGAMESU   M  NaN      NaN         IND   4545  \n",
       "29                  ALIKAN APPALASWAMY   M  NaN      NaN         IND   3235  \n",
       "...                                ...  ..  ...      ...         ...    ...  \n",
       "391461                        ASOK RAY   M   64      GEN         CPM  67478  \n",
       "391462                    JATIL MONDAL   M   47      GEN        AITC  60958  \n",
       "391463               DUDH KUMAR MONDAL   M   55      GEN         BJP  31031  \n",
       "391464                  ASISH BANERJEE   M   59      GEN        AITC  75066  \n",
       "391465             REBATI BHATTACHARYA   M   63      GEN        AIFB  64828  \n",
       "391466                        SUBHASIS   M   46      GEN         BJP  17815  \n",
       "391467                    MAHESH KISKU   M   27       ST         IND   3769  \n",
       "391468               PRADYOT MUKHERJEE   M   46      GEN  CPI(ML)(L)   2427  \n",
       "391469                        ASIT MAL   M   55       SC         INC  73370  \n",
       "391470                     KAMAL HASAN   M   31      GEN     RCPI(R)  46918  \n",
       "391471           MD. RAFIUDDIN (MOMIN)   M   48      GEN         IND  27461  \n",
       "391472                 RUPARANI MONDAL   F   43       SC         BJP   3373  \n",
       "391473                     AMAL MONDAL   M   34      GEN        SUCI   2503  \n",
       "391474                RADHESHYAM SINGH   M   65      GEN         BSP   1265  \n",
       "391475                       JIYAR ALI   M   46      GEN          JP   1081  \n",
       "391476             AZIZUL HAQUE(LALTU)   M   35      GEN         IND   1050  \n",
       "391477               ABHIJIT MUKHERJEE   M   58      GEN         INC  76047  \n",
       "391478                DIPAK CHATTERJEE   M   46      GEN        AIFB  60887  \n",
       "391479                     ANIL SINGHA   M   45      GEN         BJP  10656  \n",
       "391480                   BIKASH MONDAL   M   35      GEN         BSP   2717  \n",
       "391481                  RUDRADEB GHSOH   M   58      GEN         IND   1516  \n",
       "391482                       AJRAIL MD   M   32      GEN         IND   1258  \n",
       "391483                   RAFIQUL HASAN   M   59      GEN        SUCI   1123  \n",
       "391484            PRABHAT KUMAR MONDAL   M   39       SC         IND    912  \n",
       "391485             NURE ALAM CHOWDHURY   M   68      GEN        AITC  77817  \n",
       "391486                  ELAHI KAMRE DR   M   52      GEN         CPM  73414  \n",
       "391487               SUDHIR RANJAN DAS   M   53      GEN         BJP   4961  \n",
       "391488                  SHIBLAL SARDAR   M   36       ST         BSP   3074  \n",
       "391489                       AJRAIL MD   M   39      GEN         IND   2471  \n",
       "391490           MIR MUKID(HAZI REKIB)   M   63      GEN          JP   1228  \n",
       "\n",
       "[391491 rows x 12 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This file is available at https://github.com/datameet/india-election-data\n",
    "assembly = pd.read_csv('D:/site/gramener.com/viz/india-election-data/assembly-elections/assembly.csv')\n",
    "assembly"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 5. Standardize values: constituencies\n",
    "\n",
    "This corrects phonetic spellings mistakes like:\n",
    "\n",
    "- BHADRACHALAM, BHADRACHELAM, BHADRAHCALAM\n",
    "- TUGHLAKABAD, TUGHLAQABAD, TUGHLAQUABAD, TUGLAKABAD\n",
    "- RISHIVANDIYAM, RISHIVADIYAM, RISHIVANDIAM, RISHIVANDIAM, RISHIVANDIYAM\n",
    "\n",
    "... and non-phonetic ones like:\n",
    "\n",
    "- OOTACAMUND, UDHAGAMANDALAM\n",
    "- POTTAPIDARAM, OTTAPIDARAM\n",
    "- HARASAPUR, NARSAPUR\n",
    "\n",
    "... but we were still left with a huge gap."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## A brief detour: Delimitation\n",
    "\n",
    "Constituencies are defined in rough proportion to population.\n",
    "\n",
    "- 1952: Constituencies defined using 1951 census\n",
    "- 1962: Constituencies defined using 1961 census\n",
    "- 1972: Constituencies defined using 1971 census\n",
    "- 1976: Stop delimitation until 2000. States promoting family planning don't lose\n",
    "- 2002: Extend till 2026. But apply intra-state delimitation. Preserve # seats per state\n",
    "- 2008: Intra-state delimination was actually implemented\n",
    "\n",
    "For each of these years, we have potential re-definition of constituencies.\n",
    "\n",
    "The biggest nightmare this caused me was **Machilipatnam**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## A brief detour: Machilipatnam\n",
    "\n",
    "![Machilipatnam market](https://upload.wikimedia.org/wikipedia/commons/thumb/7/70/Machilipatnam_koneru_center_3.JPG/250px-Machilipatnam_koneru_center_3.JPG)\n",
    "\n",
    "Has 2 different names:\n",
    "\n",
    "- Masulipatnam (or Machilipatnam): derived from the Greek trade of \"muslin\" (plain woven) cloth\n",
    "- Bandar: derived from the Turkish word for \"port\"\n",
    "\n",
    "In 2008, the Bandar constituency was *renamed* to Machilipatnam.\n",
    "\n",
    "- Machilipatnam was NOT a new constituency\n",
    "- Bandar was not removed from the list\n",
    "- So history needs to be preserved"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 5. Standardize values: analysis\n",
    "\n",
    "This is where the data itself came in handy.\n",
    "\n",
    "![Assembly elections constituencies](Cleansing-Data/Cleansing-Data-assembly-elections.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 6. Extract values\n",
    "\n",
    "- Extract numbers with units (lbs & kgs, kph & mph)\n",
    "- Extract lists (e.g. multiple values in cells)\n",
    "- Extract dates (e.g. 25/10/16 or Oct 25, 2016)\n",
    "- Extract entities (e.g. names, places, phone numbers, PIN codes, things)\n",
    "- Extract name structure (e.g. title, first name, last name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 6. Extract values: composition list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "S. No.\n",
       "1    Diacerein 50 mg, Glucosamine sulphate 750 mg, ...\n",
       "2    Nandrolone decanoate 50 mg injection + Choleca...\n",
       "3    Alfacalcidol 0.25 mcg, Calcium carbonate 625 m...\n",
       "4    Calcium (as CaCO3) 150 mg, Magnesium 25 mg, Zi...\n",
       "5    Thiocolchicoside 8 mg, Diclofenac potassium 50 mg\n",
       "Name: Composition, dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pharma['Composition'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "S. No.   \n",
       "1       0                                      Diacerein 50 mg\n",
       "        1                          Glucosamine sulphate 750 mg\n",
       "        2                                           MSM 250 mg\n",
       "2       0                 Nandrolone decanoate 50 mg injection\n",
       "        1                   Cholecalciferol 6 lac IU injection\n",
       "3       0                                Alfacalcidol 0.25 mcg\n",
       "        1    Calcium carbonate 625 mg eq. to Elemental calc...\n",
       "4       0                            Calcium (as CaCO3) 150 mg\n",
       "        1                                      Magnesium 25 mg\n",
       "        2                                          Zinc 1.5 mg\n",
       "dtype: object"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "components = pharma['Composition'].str.split(r'\\s*[,\\+]\\s*').apply(pd.Series).stack()\n",
    "components.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 6. Extract values: lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S. No.</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 rowspan=\"3\" valign=\"top\">1</th>\n",
       "      <th>0</th>\n",
       "      <td>Diacerein</td>\n",
       "      <td>50</td>\n",
       "      <td>mg</td>\n",
       "      <td>Diacerein 50 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Glucosamine sulphate</td>\n",
       "      <td>750</td>\n",
       "      <td>mg</td>\n",
       "      <td>Glucosamine sulphate 750 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSM</td>\n",
       "      <td>250</td>\n",
       "      <td>mg</td>\n",
       "      <td>MSM 250 mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>0</th>\n",
       "      <td>Nandrolone decanoate</td>\n",
       "      <td>50</td>\n",
       "      <td>mg</td>\n",
       "      <td>Nandrolone decanoate 50 mg injection</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cholecalciferol 6 lac IU injection</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             0    1    2                                  text\n",
       "S. No.                                                                        \n",
       "1      0             Diacerein   50   mg                       Diacerein 50 mg\n",
       "       1  Glucosamine sulphate  750   mg           Glucosamine sulphate 750 mg\n",
       "       2                   MSM  250   mg                            MSM 250 mg\n",
       "2      0  Nandrolone decanoate   50   mg  Nandrolone decanoate 50 mg injection\n",
       "       1                   NaN  NaN  NaN    Cholecalciferol 6 lac IU injection"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extracts = components.str.extract('(.*?)\\s*(\\d+)\\s*(ml|mg|mcg|g|gm).*')\n",
    "extracts['text'] = components\n",
    "extracts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Don't overdo it\n",
    "\n",
    "![Mess](https://imgs.xkcd.com/comics/mess.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Let's automate as open source\n",
    "\n",
    "We're creating an open source tool on **[Github: gramener/dataaudit](https://github.com/gramener/dataaudit)**.\n",
    "\n",
    "(See the **dev** branch for now.)\n",
    "\n",
    "We'll start with cleaning public health data at UP & Bihar.\n",
    "\n",
    "Join in!"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}