scripts/05_compare_secc_clean.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare Final SECC Data to Aggregated Output Posted on the SECC site\n",
    "\n",
    "We compare the final processed data to state total obtained from https://secc.gov.in/welcome. The state total CSV is posted [here](../data/secc_state.csv)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from io import StringIO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "our = \"\"\"state  hh\n",
    "ARUNACHAL PRADESH      396532\n",
    "ASSAM                 2371410\n",
    "BIHAR                18062667\n",
    "CHHATTISGARH          2845416\n",
    "GUJARAT               6824687\n",
    "HARYANA               2642841\n",
    "KERALA                5935301\n",
    "MADHYA PRADESH       11264200\n",
    "MAHARASHTRA          13729467\n",
    "MIZORAM                 40190\n",
    "NAGALAND               284991\n",
    "ODISHA               10522478\n",
    "PUNJAB                2949448\n",
    "RAJASTHAN             9451669\n",
    "SIKKIM                  88828\n",
    "TAMILNADU            10224155\n",
    "UTTAR PRADESH        24810878\n",
    "UTTARAKHAND           1475188\n",
    "WEST BENGAL          16277886\n",
    "\"\"\""
   ]
  },
  {
   "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>state</th>\n",
       "      <th>hh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ARUNACHAL PRADESH</td>\n",
       "      <td>396532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASSAM</td>\n",
       "      <td>2371410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BIHAR</td>\n",
       "      <td>18062667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CHHATTISGARH</td>\n",
       "      <td>2845416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GUJARAT</td>\n",
       "      <td>6824687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HARYANA</td>\n",
       "      <td>2642841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>KERALA</td>\n",
       "      <td>5935301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MADHYA PRADESH</td>\n",
       "      <td>11264200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MAHARASHTRA</td>\n",
       "      <td>13729467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MIZORAM</td>\n",
       "      <td>40190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NAGALAND</td>\n",
       "      <td>284991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ODISHA</td>\n",
       "      <td>10522478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PUNJAB</td>\n",
       "      <td>2949448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>RAJASTHAN</td>\n",
       "      <td>9451669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>SIKKIM</td>\n",
       "      <td>88828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>TAMILNADU</td>\n",
       "      <td>10224155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>UTTAR PRADESH</td>\n",
       "      <td>24810878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>UTTARAKHAND</td>\n",
       "      <td>1475188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>16277886</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                state        hh\n",
       "0   ARUNACHAL PRADESH    396532\n",
       "1               ASSAM   2371410\n",
       "2               BIHAR  18062667\n",
       "3        CHHATTISGARH   2845416\n",
       "4             GUJARAT   6824687\n",
       "5             HARYANA   2642841\n",
       "6              KERALA   5935301\n",
       "7      MADHYA PRADESH  11264200\n",
       "8         MAHARASHTRA  13729467\n",
       "9             MIZORAM     40190\n",
       "10           NAGALAND    284991\n",
       "11             ODISHA  10522478\n",
       "12             PUNJAB   2949448\n",
       "13          RAJASTHAN   9451669\n",
       "14             SIKKIM     88828\n",
       "15          TAMILNADU  10224155\n",
       "16      UTTAR PRADESH  24810878\n",
       "17        UTTARAKHAND   1475188\n",
       "18        WEST BENGAL  16277886"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv(StringIO(our), delimiter='\\s{2,}', engine=\"python\")\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "secc = \"\"\"state\thh\n",
    "01 - JAMMU & KASHMIR\t2094081\n",
    "02 - HIMACHAL PRADESH\t1427365\n",
    "03 - PUNJAB\t5032199\n",
    "04 - CHANDIGARH\t214233\n",
    "05 - UTTARAKHAND\t1968773\n",
    "06 - HARYANA\t4630959\n",
    "07 - NCT OF DELHI\t3391313\n",
    "08 - RAJASTHAN\t13136591\n",
    "09 - UTTAR PRADESH\t32475784\n",
    "10 - BIHAR\t20074242\n",
    "11 - SIKKIM\t120014\n",
    "12 - ARUNACHAL PRADESH\t260217\n",
    "13 - NAGALAND\t379164\n",
    "14 - MANIPUR\t578939\n",
    "15 - MIZORAM\t226147\n",
    "16 - TRIPURA\t875621\n",
    "17 - MEGHALAYA\t554131\n",
    "18 - ASSAM\t6427614\n",
    "19 - WEST BENGAL\t20367144\n",
    "20 - JHARKHAND\t6041931\n",
    "21 - ODISHA\t9942101\n",
    "22 - CHHATTISGARH\t5714798\n",
    "23 - MADHYA PRADESH\t14723864\n",
    "24 - GUJARAT\t11629409\n",
    "25 - DAMAN AND DIU\t44968\n",
    "26 - DADRA & NAGAR HAVELI\t66571\n",
    "27 - MAHARASHTRA\t22962600\n",
    "28 - ANDHRA PRADESH\t12270164\n",
    "28 - TELANGANA\t8244441\n",
    "29 - KARNATAKA\t13139063\n",
    "30 - GOA\t302950\n",
    "31 - LAKSHADWEEP\t10929\n",
    "32 - KERALA\t7698556\n",
    "33 - TAMILNADU\t17521956\n",
    "34 - PUDUCHERRY\t279857\n",
    "35 - ANDAMAN & NICOBAR ISLANDS\t92717\"\"\""
   ]
  },
  {
   "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>state</th>\n",
       "      <th>hh</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01 - jammu &amp; kashmir</td>\n",
       "      <td>2094081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02 - himachal pradesh</td>\n",
       "      <td>1427365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>03 - punjab</td>\n",
       "      <td>5032199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>04 - chandigarh</td>\n",
       "      <td>214233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>05 - uttarakhand</td>\n",
       "      <td>1968773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>06 - haryana</td>\n",
       "      <td>4630959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>07 - nct of delhi</td>\n",
       "      <td>3391313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>08 - rajasthan</td>\n",
       "      <td>13136591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>09 - uttar pradesh</td>\n",
       "      <td>32475784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10 - bihar</td>\n",
       "      <td>20074242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11 - sikkim</td>\n",
       "      <td>120014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12 - arunachal pradesh</td>\n",
       "      <td>260217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13 - nagaland</td>\n",
       "      <td>379164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14 - manipur</td>\n",
       "      <td>578939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15 - mizoram</td>\n",
       "      <td>226147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16 - tripura</td>\n",
       "      <td>875621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17 - meghalaya</td>\n",
       "      <td>554131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18 - assam</td>\n",
       "      <td>6427614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19 - west bengal</td>\n",
       "      <td>20367144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20 - jharkhand</td>\n",
       "      <td>6041931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21 - odisha</td>\n",
       "      <td>9942101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22 - chhattisgarh</td>\n",
       "      <td>5714798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23 - madhya pradesh</td>\n",
       "      <td>14723864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24 - gujarat</td>\n",
       "      <td>11629409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25 - daman and diu</td>\n",
       "      <td>44968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26 - dadra &amp; nagar haveli</td>\n",
       "      <td>66571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27 - maharashtra</td>\n",
       "      <td>22962600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28 - andhra pradesh</td>\n",
       "      <td>12270164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28 - telangana</td>\n",
       "      <td>8244441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29 - karnataka</td>\n",
       "      <td>13139063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>30 - goa</td>\n",
       "      <td>302950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31 - lakshadweep</td>\n",
       "      <td>10929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>32 - kerala</td>\n",
       "      <td>7698556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>33 - tamilnadu</td>\n",
       "      <td>17521956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>34 - puducherry</td>\n",
       "      <td>279857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>35 - andaman &amp; nicobar islands</td>\n",
       "      <td>92717</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             state        hh\n",
       "0             01 - jammu & kashmir   2094081\n",
       "1            02 - himachal pradesh   1427365\n",
       "2                      03 - punjab   5032199\n",
       "3                  04 - chandigarh    214233\n",
       "4                 05 - uttarakhand   1968773\n",
       "5                     06 - haryana   4630959\n",
       "6                07 - nct of delhi   3391313\n",
       "7                   08 - rajasthan  13136591\n",
       "8               09 - uttar pradesh  32475784\n",
       "9                       10 - bihar  20074242\n",
       "10                     11 - sikkim    120014\n",
       "11          12 - arunachal pradesh    260217\n",
       "12                   13 - nagaland    379164\n",
       "13                    14 - manipur    578939\n",
       "14                    15 - mizoram    226147\n",
       "15                    16 - tripura    875621\n",
       "16                  17 - meghalaya    554131\n",
       "17                      18 - assam   6427614\n",
       "18                19 - west bengal  20367144\n",
       "19                  20 - jharkhand   6041931\n",
       "20                     21 - odisha   9942101\n",
       "21               22 - chhattisgarh   5714798\n",
       "22             23 - madhya pradesh  14723864\n",
       "23                    24 - gujarat  11629409\n",
       "24              25 - daman and diu     44968\n",
       "25       26 - dadra & nagar haveli     66571\n",
       "26                27 - maharashtra  22962600\n",
       "27             28 - andhra pradesh  12270164\n",
       "28                  28 - telangana   8244441\n",
       "29                  29 - karnataka  13139063\n",
       "30                        30 - goa    302950\n",
       "31                31 - lakshadweep     10929\n",
       "32                     32 - kerala   7698556\n",
       "33                  33 - tamilnadu  17521956\n",
       "34                 34 - puducherry    279857\n",
       "35  35 - andaman & nicobar islands     92717"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_csv(StringIO(secc.lower()), delimiter='\\t')\n",
    "df2"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "!pip install fuzzywuzzy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/venv37/lib/python3.7/site-packages/fuzzywuzzy/fuzz.py:11: UserWarning: Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning\n",
      "  warnings.warn('Using slow pure-python SequenceMatcher. Install python-Levenshtein to remove this warning')\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>state_df1</th>\n",
       "      <th>hh_df1</th>\n",
       "      <th>df2_state</th>\n",
       "      <th>state_df2</th>\n",
       "      <th>hh_df2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ARUNACHAL PRADESH</td>\n",
       "      <td>396532.0</td>\n",
       "      <td>12 - arunachal pradesh</td>\n",
       "      <td>12 - arunachal pradesh</td>\n",
       "      <td>260217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASSAM</td>\n",
       "      <td>2371410.0</td>\n",
       "      <td>18 - assam</td>\n",
       "      <td>18 - assam</td>\n",
       "      <td>6427614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BIHAR</td>\n",
       "      <td>18062667.0</td>\n",
       "      <td>10 - bihar</td>\n",
       "      <td>10 - bihar</td>\n",
       "      <td>20074242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CHHATTISGARH</td>\n",
       "      <td>2845416.0</td>\n",
       "      <td>22 - chhattisgarh</td>\n",
       "      <td>22 - chhattisgarh</td>\n",
       "      <td>5714798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GUJARAT</td>\n",
       "      <td>6824687.0</td>\n",
       "      <td>24 - gujarat</td>\n",
       "      <td>24 - gujarat</td>\n",
       "      <td>11629409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HARYANA</td>\n",
       "      <td>2642841.0</td>\n",
       "      <td>06 - haryana</td>\n",
       "      <td>06 - haryana</td>\n",
       "      <td>4630959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>KERALA</td>\n",
       "      <td>5935301.0</td>\n",
       "      <td>32 - kerala</td>\n",
       "      <td>32 - kerala</td>\n",
       "      <td>7698556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MADHYA PRADESH</td>\n",
       "      <td>11264200.0</td>\n",
       "      <td>23 - madhya pradesh</td>\n",
       "      <td>23 - madhya pradesh</td>\n",
       "      <td>14723864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MAHARASHTRA</td>\n",
       "      <td>13729467.0</td>\n",
       "      <td>27 - maharashtra</td>\n",
       "      <td>27 - maharashtra</td>\n",
       "      <td>22962600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MIZORAM</td>\n",
       "      <td>40190.0</td>\n",
       "      <td>15 - mizoram</td>\n",
       "      <td>15 - mizoram</td>\n",
       "      <td>226147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NAGALAND</td>\n",
       "      <td>284991.0</td>\n",
       "      <td>13 - nagaland</td>\n",
       "      <td>13 - nagaland</td>\n",
       "      <td>379164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ODISHA</td>\n",
       "      <td>10522478.0</td>\n",
       "      <td>21 - odisha</td>\n",
       "      <td>21 - odisha</td>\n",
       "      <td>9942101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PUNJAB</td>\n",
       "      <td>2949448.0</td>\n",
       "      <td>03 - punjab</td>\n",
       "      <td>03 - punjab</td>\n",
       "      <td>5032199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>RAJASTHAN</td>\n",
       "      <td>9451669.0</td>\n",
       "      <td>08 - rajasthan</td>\n",
       "      <td>08 - rajasthan</td>\n",
       "      <td>13136591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>SIKKIM</td>\n",
       "      <td>88828.0</td>\n",
       "      <td>11 - sikkim</td>\n",
       "      <td>11 - sikkim</td>\n",
       "      <td>120014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>TAMILNADU</td>\n",
       "      <td>10224155.0</td>\n",
       "      <td>33 - tamilnadu</td>\n",
       "      <td>33 - tamilnadu</td>\n",
       "      <td>17521956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>UTTAR PRADESH</td>\n",
       "      <td>24810878.0</td>\n",
       "      <td>09 - uttar pradesh</td>\n",
       "      <td>09 - uttar pradesh</td>\n",
       "      <td>32475784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>UTTARAKHAND</td>\n",
       "      <td>1475188.0</td>\n",
       "      <td>05 - uttarakhand</td>\n",
       "      <td>05 - uttarakhand</td>\n",
       "      <td>1968773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>16277886.0</td>\n",
       "      <td>19 - west bengal</td>\n",
       "      <td>19 - west bengal</td>\n",
       "      <td>20367144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01 - jammu &amp; kashmir</td>\n",
       "      <td>2094081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02 - himachal pradesh</td>\n",
       "      <td>1427365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>04 - chandigarh</td>\n",
       "      <td>214233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07 - nct of delhi</td>\n",
       "      <td>3391313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14 - manipur</td>\n",
       "      <td>578939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16 - tripura</td>\n",
       "      <td>875621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17 - meghalaya</td>\n",
       "      <td>554131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20 - jharkhand</td>\n",
       "      <td>6041931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25 - daman and diu</td>\n",
       "      <td>44968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26 - dadra &amp; nagar haveli</td>\n",
       "      <td>66571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28 - andhra pradesh</td>\n",
       "      <td>12270164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28 - telangana</td>\n",
       "      <td>8244441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29 - karnataka</td>\n",
       "      <td>13139063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30 - goa</td>\n",
       "      <td>302950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31 - lakshadweep</td>\n",
       "      <td>10929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34 - puducherry</td>\n",
       "      <td>279857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35 - andaman &amp; nicobar islands</td>\n",
       "      <td>92717</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state_df1      hh_df1               df2_state  \\\n",
       "0   ARUNACHAL PRADESH    396532.0  12 - arunachal pradesh   \n",
       "1               ASSAM   2371410.0              18 - assam   \n",
       "2               BIHAR  18062667.0              10 - bihar   \n",
       "3        CHHATTISGARH   2845416.0       22 - chhattisgarh   \n",
       "4             GUJARAT   6824687.0            24 - gujarat   \n",
       "5             HARYANA   2642841.0            06 - haryana   \n",
       "6              KERALA   5935301.0             32 - kerala   \n",
       "7      MADHYA PRADESH  11264200.0     23 - madhya pradesh   \n",
       "8         MAHARASHTRA  13729467.0        27 - maharashtra   \n",
       "9             MIZORAM     40190.0            15 - mizoram   \n",
       "10           NAGALAND    284991.0           13 - nagaland   \n",
       "11             ODISHA  10522478.0             21 - odisha   \n",
       "12             PUNJAB   2949448.0             03 - punjab   \n",
       "13          RAJASTHAN   9451669.0          08 - rajasthan   \n",
       "14             SIKKIM     88828.0             11 - sikkim   \n",
       "15          TAMILNADU  10224155.0          33 - tamilnadu   \n",
       "16      UTTAR PRADESH  24810878.0      09 - uttar pradesh   \n",
       "17        UTTARAKHAND   1475188.0        05 - uttarakhand   \n",
       "18        WEST BENGAL  16277886.0        19 - west bengal   \n",
       "19                NaN         NaN                     NaN   \n",
       "20                NaN         NaN                     NaN   \n",
       "21                NaN         NaN                     NaN   \n",
       "22                NaN         NaN                     NaN   \n",
       "23                NaN         NaN                     NaN   \n",
       "24                NaN         NaN                     NaN   \n",
       "25                NaN         NaN                     NaN   \n",
       "26                NaN         NaN                     NaN   \n",
       "27                NaN         NaN                     NaN   \n",
       "28                NaN         NaN                     NaN   \n",
       "29                NaN         NaN                     NaN   \n",
       "30                NaN         NaN                     NaN   \n",
       "31                NaN         NaN                     NaN   \n",
       "32                NaN         NaN                     NaN   \n",
       "33                NaN         NaN                     NaN   \n",
       "34                NaN         NaN                     NaN   \n",
       "35                NaN         NaN                     NaN   \n",
       "\n",
       "                         state_df2    hh_df2  \n",
       "0           12 - arunachal pradesh    260217  \n",
       "1                       18 - assam   6427614  \n",
       "2                       10 - bihar  20074242  \n",
       "3                22 - chhattisgarh   5714798  \n",
       "4                     24 - gujarat  11629409  \n",
       "5                     06 - haryana   4630959  \n",
       "6                      32 - kerala   7698556  \n",
       "7              23 - madhya pradesh  14723864  \n",
       "8                 27 - maharashtra  22962600  \n",
       "9                     15 - mizoram    226147  \n",
       "10                   13 - nagaland    379164  \n",
       "11                     21 - odisha   9942101  \n",
       "12                     03 - punjab   5032199  \n",
       "13                  08 - rajasthan  13136591  \n",
       "14                     11 - sikkim    120014  \n",
       "15                  33 - tamilnadu  17521956  \n",
       "16              09 - uttar pradesh  32475784  \n",
       "17                05 - uttarakhand   1968773  \n",
       "18                19 - west bengal  20367144  \n",
       "19            01 - jammu & kashmir   2094081  \n",
       "20           02 - himachal pradesh   1427365  \n",
       "21                 04 - chandigarh    214233  \n",
       "22               07 - nct of delhi   3391313  \n",
       "23                    14 - manipur    578939  \n",
       "24                    16 - tripura    875621  \n",
       "25                  17 - meghalaya    554131  \n",
       "26                  20 - jharkhand   6041931  \n",
       "27              25 - daman and diu     44968  \n",
       "28       26 - dadra & nagar haveli     66571  \n",
       "29             28 - andhra pradesh  12270164  \n",
       "30                  28 - telangana   8244441  \n",
       "31                  29 - karnataka  13139063  \n",
       "32                        30 - goa    302950  \n",
       "33                31 - lakshadweep     10929  \n",
       "34                 34 - puducherry    279857  \n",
       "35  35 - andaman & nicobar islands     92717  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import fuzzywuzzy.process as fwp\n",
    "\n",
    "choices = list(df2.state)\n",
    "\n",
    "def fmatch(row): \n",
    "    minscore=85 #or whatever score works for you\n",
    "    choice,score = fwp.extractOne(row.state,choices)\n",
    "    return choice if score > minscore else None\n",
    "\n",
    "df1['df2_state'] = df1.apply(fmatch,axis=1)\n",
    "mdf = pd.merge(df1, \n",
    "                  df2,\n",
    "                  left_on='df2_state',\n",
    "                  right_on='state',\n",
    "                  suffixes=['_df1','_df2'],\n",
    "                  how = 'outer') # assuming you want to keep unmatched records\n",
    "mdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>state_df1</th>\n",
       "      <th>hh_df1</th>\n",
       "      <th>df2_state</th>\n",
       "      <th>state_df2</th>\n",
       "      <th>hh_df2</th>\n",
       "      <th>diff(Million)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ARUNACHAL PRADESH</td>\n",
       "      <td>396532.0</td>\n",
       "      <td>12 - arunachal pradesh</td>\n",
       "      <td>12 - arunachal pradesh</td>\n",
       "      <td>260217</td>\n",
       "      <td>0.136315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASSAM</td>\n",
       "      <td>2371410.0</td>\n",
       "      <td>18 - assam</td>\n",
       "      <td>18 - assam</td>\n",
       "      <td>6427614</td>\n",
       "      <td>-4.056204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BIHAR</td>\n",
       "      <td>18062667.0</td>\n",
       "      <td>10 - bihar</td>\n",
       "      <td>10 - bihar</td>\n",
       "      <td>20074242</td>\n",
       "      <td>-2.011575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CHHATTISGARH</td>\n",
       "      <td>2845416.0</td>\n",
       "      <td>22 - chhattisgarh</td>\n",
       "      <td>22 - chhattisgarh</td>\n",
       "      <td>5714798</td>\n",
       "      <td>-2.869382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GUJARAT</td>\n",
       "      <td>6824687.0</td>\n",
       "      <td>24 - gujarat</td>\n",
       "      <td>24 - gujarat</td>\n",
       "      <td>11629409</td>\n",
       "      <td>-4.804722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HARYANA</td>\n",
       "      <td>2642841.0</td>\n",
       "      <td>06 - haryana</td>\n",
       "      <td>06 - haryana</td>\n",
       "      <td>4630959</td>\n",
       "      <td>-1.988118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>KERALA</td>\n",
       "      <td>5935301.0</td>\n",
       "      <td>32 - kerala</td>\n",
       "      <td>32 - kerala</td>\n",
       "      <td>7698556</td>\n",
       "      <td>-1.763255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MADHYA PRADESH</td>\n",
       "      <td>11264200.0</td>\n",
       "      <td>23 - madhya pradesh</td>\n",
       "      <td>23 - madhya pradesh</td>\n",
       "      <td>14723864</td>\n",
       "      <td>-3.459664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MAHARASHTRA</td>\n",
       "      <td>13729467.0</td>\n",
       "      <td>27 - maharashtra</td>\n",
       "      <td>27 - maharashtra</td>\n",
       "      <td>22962600</td>\n",
       "      <td>-9.233133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MIZORAM</td>\n",
       "      <td>40190.0</td>\n",
       "      <td>15 - mizoram</td>\n",
       "      <td>15 - mizoram</td>\n",
       "      <td>226147</td>\n",
       "      <td>-0.185957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NAGALAND</td>\n",
       "      <td>284991.0</td>\n",
       "      <td>13 - nagaland</td>\n",
       "      <td>13 - nagaland</td>\n",
       "      <td>379164</td>\n",
       "      <td>-0.094173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ODISHA</td>\n",
       "      <td>10522478.0</td>\n",
       "      <td>21 - odisha</td>\n",
       "      <td>21 - odisha</td>\n",
       "      <td>9942101</td>\n",
       "      <td>0.580377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PUNJAB</td>\n",
       "      <td>2949448.0</td>\n",
       "      <td>03 - punjab</td>\n",
       "      <td>03 - punjab</td>\n",
       "      <td>5032199</td>\n",
       "      <td>-2.082751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>RAJASTHAN</td>\n",
       "      <td>9451669.0</td>\n",
       "      <td>08 - rajasthan</td>\n",
       "      <td>08 - rajasthan</td>\n",
       "      <td>13136591</td>\n",
       "      <td>-3.684922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>SIKKIM</td>\n",
       "      <td>88828.0</td>\n",
       "      <td>11 - sikkim</td>\n",
       "      <td>11 - sikkim</td>\n",
       "      <td>120014</td>\n",
       "      <td>-0.031186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>TAMILNADU</td>\n",
       "      <td>10224155.0</td>\n",
       "      <td>33 - tamilnadu</td>\n",
       "      <td>33 - tamilnadu</td>\n",
       "      <td>17521956</td>\n",
       "      <td>-7.297801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>UTTAR PRADESH</td>\n",
       "      <td>24810878.0</td>\n",
       "      <td>09 - uttar pradesh</td>\n",
       "      <td>09 - uttar pradesh</td>\n",
       "      <td>32475784</td>\n",
       "      <td>-7.664906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>UTTARAKHAND</td>\n",
       "      <td>1475188.0</td>\n",
       "      <td>05 - uttarakhand</td>\n",
       "      <td>05 - uttarakhand</td>\n",
       "      <td>1968773</td>\n",
       "      <td>-0.493585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>16277886.0</td>\n",
       "      <td>19 - west bengal</td>\n",
       "      <td>19 - west bengal</td>\n",
       "      <td>20367144</td>\n",
       "      <td>-4.089258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01 - jammu &amp; kashmir</td>\n",
       "      <td>2094081</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02 - himachal pradesh</td>\n",
       "      <td>1427365</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>04 - chandigarh</td>\n",
       "      <td>214233</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07 - nct of delhi</td>\n",
       "      <td>3391313</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14 - manipur</td>\n",
       "      <td>578939</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16 - tripura</td>\n",
       "      <td>875621</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17 - meghalaya</td>\n",
       "      <td>554131</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20 - jharkhand</td>\n",
       "      <td>6041931</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25 - daman and diu</td>\n",
       "      <td>44968</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26 - dadra &amp; nagar haveli</td>\n",
       "      <td>66571</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28 - andhra pradesh</td>\n",
       "      <td>12270164</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28 - telangana</td>\n",
       "      <td>8244441</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29 - karnataka</td>\n",
       "      <td>13139063</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30 - goa</td>\n",
       "      <td>302950</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31 - lakshadweep</td>\n",
       "      <td>10929</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34 - puducherry</td>\n",
       "      <td>279857</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35 - andaman &amp; nicobar islands</td>\n",
       "      <td>92717</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state_df1      hh_df1               df2_state  \\\n",
       "0   ARUNACHAL PRADESH    396532.0  12 - arunachal pradesh   \n",
       "1               ASSAM   2371410.0              18 - assam   \n",
       "2               BIHAR  18062667.0              10 - bihar   \n",
       "3        CHHATTISGARH   2845416.0       22 - chhattisgarh   \n",
       "4             GUJARAT   6824687.0            24 - gujarat   \n",
       "5             HARYANA   2642841.0            06 - haryana   \n",
       "6              KERALA   5935301.0             32 - kerala   \n",
       "7      MADHYA PRADESH  11264200.0     23 - madhya pradesh   \n",
       "8         MAHARASHTRA  13729467.0        27 - maharashtra   \n",
       "9             MIZORAM     40190.0            15 - mizoram   \n",
       "10           NAGALAND    284991.0           13 - nagaland   \n",
       "11             ODISHA  10522478.0             21 - odisha   \n",
       "12             PUNJAB   2949448.0             03 - punjab   \n",
       "13          RAJASTHAN   9451669.0          08 - rajasthan   \n",
       "14             SIKKIM     88828.0             11 - sikkim   \n",
       "15          TAMILNADU  10224155.0          33 - tamilnadu   \n",
       "16      UTTAR PRADESH  24810878.0      09 - uttar pradesh   \n",
       "17        UTTARAKHAND   1475188.0        05 - uttarakhand   \n",
       "18        WEST BENGAL  16277886.0        19 - west bengal   \n",
       "19                NaN         NaN                     NaN   \n",
       "20                NaN         NaN                     NaN   \n",
       "21                NaN         NaN                     NaN   \n",
       "22                NaN         NaN                     NaN   \n",
       "23                NaN         NaN                     NaN   \n",
       "24                NaN         NaN                     NaN   \n",
       "25                NaN         NaN                     NaN   \n",
       "26                NaN         NaN                     NaN   \n",
       "27                NaN         NaN                     NaN   \n",
       "28                NaN         NaN                     NaN   \n",
       "29                NaN         NaN                     NaN   \n",
       "30                NaN         NaN                     NaN   \n",
       "31                NaN         NaN                     NaN   \n",
       "32                NaN         NaN                     NaN   \n",
       "33                NaN         NaN                     NaN   \n",
       "34                NaN         NaN                     NaN   \n",
       "35                NaN         NaN                     NaN   \n",
       "\n",
       "                         state_df2    hh_df2  diff(Million)  \n",
       "0           12 - arunachal pradesh    260217       0.136315  \n",
       "1                       18 - assam   6427614      -4.056204  \n",
       "2                       10 - bihar  20074242      -2.011575  \n",
       "3                22 - chhattisgarh   5714798      -2.869382  \n",
       "4                     24 - gujarat  11629409      -4.804722  \n",
       "5                     06 - haryana   4630959      -1.988118  \n",
       "6                      32 - kerala   7698556      -1.763255  \n",
       "7              23 - madhya pradesh  14723864      -3.459664  \n",
       "8                 27 - maharashtra  22962600      -9.233133  \n",
       "9                     15 - mizoram    226147      -0.185957  \n",
       "10                   13 - nagaland    379164      -0.094173  \n",
       "11                     21 - odisha   9942101       0.580377  \n",
       "12                     03 - punjab   5032199      -2.082751  \n",
       "13                  08 - rajasthan  13136591      -3.684922  \n",
       "14                     11 - sikkim    120014      -0.031186  \n",
       "15                  33 - tamilnadu  17521956      -7.297801  \n",
       "16              09 - uttar pradesh  32475784      -7.664906  \n",
       "17                05 - uttarakhand   1968773      -0.493585  \n",
       "18                19 - west bengal  20367144      -4.089258  \n",
       "19            01 - jammu & kashmir   2094081            NaN  \n",
       "20           02 - himachal pradesh   1427365            NaN  \n",
       "21                 04 - chandigarh    214233            NaN  \n",
       "22               07 - nct of delhi   3391313            NaN  \n",
       "23                    14 - manipur    578939            NaN  \n",
       "24                    16 - tripura    875621            NaN  \n",
       "25                  17 - meghalaya    554131            NaN  \n",
       "26                  20 - jharkhand   6041931            NaN  \n",
       "27              25 - daman and diu     44968            NaN  \n",
       "28       26 - dadra & nagar haveli     66571            NaN  \n",
       "29             28 - andhra pradesh  12270164            NaN  \n",
       "30                  28 - telangana   8244441            NaN  \n",
       "31                  29 - karnataka  13139063            NaN  \n",
       "32                        30 - goa    302950            NaN  \n",
       "33                31 - lakshadweep     10929            NaN  \n",
       "34                 34 - puducherry    279857            NaN  \n",
       "35  35 - andaman & nicobar islands     92717            NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mdf['diff(Million)'] = (mdf.hh_df1 - mdf.hh_df2)/1e6\n",
    "mdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "hh_df1    140198232.0\n",
       "hh_df2    244921406.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mdf[['hh_df1', 'hh_df2']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}