scripts/04_test_clean_db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count Distinct Using SQL as Robustness Check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in /opt/venv37/lib/python3.7/site-packages (1.3.18)\r\n"
     ]
    }
   ],
   "source": [
    "!pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from glob import glob\n",
    "import libarchive # `pip install libarchive-c` and using with libarchive 3.4.x\n",
    "from io import BytesIO\n",
    "import re\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:////opt/data/secc/raw.db', echo=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "../secc/secc_01_clean.7z\n",
      "176.7793779373169\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/venv37/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (7,8,10,11,12,13,19) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "../secc/secc_02_clean.7z\n",
      "461.01110553741455\n",
      "../secc/secc_03_clean.7z\n",
      "708.2516670227051\n",
      "../secc/secc_04_clean.7z\n",
      "1086.6022787094116\n",
      "../secc/secc_05_clean.7z\n",
      "2518.407386779785\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/venv37/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "../secc/secc_06_clean.7z\n",
      "2860.2195234298706\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-4-c031bf1c859a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     13\u001b[0m                 \u001b[0mdf\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m                 \u001b[0;32mdel\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Unnamed: 0'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 15\u001b[0;31m                 \u001b[0mdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'secc'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'append'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcon\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     16\u001b[0m     \u001b[0;31m#break\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/venv37/lib/python3.7/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   2610\u001b[0m             \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2611\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2612\u001b[0;31m             \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2613\u001b[0m         )\n\u001b[1;32m   2614\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/venv37/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m    596\u001b[0m         \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    597\u001b[0m         \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 598\u001b[0;31m         \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    599\u001b[0m     )\n\u001b[1;32m    600\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/venv37/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   1396\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1397\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1398\u001b[0;31m             \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1399\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mSQLAlchemyError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1400\u001b[0m             \u001b[0;31m# GH34431\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/venv37/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36minsert\u001b[0;34m(self, chunksize, method)\u001b[0m\n\u001b[1;32m    828\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    829\u001b[0m                 \u001b[0mchunk_iter\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mzip\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0marr\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mstart_i\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0mend_i\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0marr\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdata_list\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 830\u001b[0;31m                 \u001b[0mexec_insert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkeys\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mchunk_iter\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    831\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    832\u001b[0m     def _query_iterator(\n",
      "\u001b[0;32m/opt/venv37/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36m_execute_insert\u001b[0;34m(self, conn, keys, data_iter)\u001b[0m\n\u001b[1;32m    744\u001b[0m            \u001b[0mEach\u001b[0m \u001b[0mitem\u001b[0m \u001b[0mcontains\u001b[0m \u001b[0ma\u001b[0m \u001b[0mlist\u001b[0m \u001b[0mof\u001b[0m \u001b[0mvalues\u001b[0m \u001b[0mto\u001b[0m \u001b[0mbe\u001b[0m \u001b[0minserted\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    745\u001b[0m         \"\"\"\n\u001b[0;32m--> 746\u001b[0;31m         \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mdict\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mzip\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkeys\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdata_iter\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    747\u001b[0m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    748\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/venv37/lib/python3.7/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36m<listcomp>\u001b[0;34m(.0)\u001b[0m\n\u001b[1;32m    744\u001b[0m            \u001b[0mEach\u001b[0m \u001b[0mitem\u001b[0m \u001b[0mcontains\u001b[0m \u001b[0ma\u001b[0m \u001b[0mlist\u001b[0m \u001b[0mof\u001b[0m \u001b[0mvalues\u001b[0m \u001b[0mto\u001b[0m \u001b[0mbe\u001b[0m \u001b[0minserted\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    745\u001b[0m         \"\"\"\n\u001b[0;32m--> 746\u001b[0;31m         \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mdict\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mzip\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkeys\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdata_iter\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    747\u001b[0m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    748\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "total_size = 0\n",
    "for fn in sorted(glob('../secc/secc*.7z')):\n",
    "    print(fn)\n",
    "    with libarchive.file_reader(fn) as e:\n",
    "        for entry in e:\n",
    "            total_size += entry.size\n",
    "            print(total_size / (1024*1024))\n",
    "            with BytesIO() as f:\n",
    "                for i, block in enumerate(entry.get_blocks()):\n",
    "                    f.write(block)\n",
    "                f.seek(0)\n",
    "                #df = pd.read_csv(f, nrows=10)\n",
    "                df = pd.read_csv(f)\n",
    "                del df['Unnamed: 0']\n",
    "                df.to_sql('secc', if_exists='append', con=engine)\n",
    "    #break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(11578233,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"SELECT count(*) FROM secc\").fetchall()"
   ]
  },
  {
   "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>index</th>\n",
       "      <th>state</th>\n",
       "      <th>district</th>\n",
       "      <th>tehsil</th>\n",
       "      <th>panchayat</th>\n",
       "      <th>language</th>\n",
       "      <th>auto_inclusion_deprivation_or_exclusion_or_other</th>\n",
       "      <th>head_of_hh</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>social_cat</th>\n",
       "      <th>fathers_and_mothers_name</th>\n",
       "      <th>deprivation_count</th>\n",
       "      <th>auto_inclusion_deprivation_code</th>\n",
       "      <th>total_members</th>\n",
       "      <th>hh_summary_auto_inclusion</th>\n",
       "      <th>hh_summary_auto_exclusion</th>\n",
       "      <th>hh_summary_auto_other</th>\n",
       "      <th>hh_summary_deprivation</th>\n",
       "      <th>village</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>Arong(Village)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>Arong(Village)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Auto Exclusion</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>Arong(Village)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Others</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>Big Lapati(Village)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>Big Lapati(Village)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Auto Exclusion</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>Big Lapati(Village)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Others</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>CAR NICOBAR TRIBAL COUNCIL(GP)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>CAR NICOBAR TRIBAL COUNCIL(GP)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Auto Exclusion</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>CAR NICOBAR TRIBAL COUNCIL(GP)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Others</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Nicobars</td>\n",
       "      <td>Car Nicobar</td>\n",
       "      <td>Chuckchucha(Village)</td>\n",
       "      <td>eng</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                      state  district       tehsil  \\\n",
       "0      0  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "1      1  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "2      2  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "3      3  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "4      4  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "5      5  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "6      6  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "7      7  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "8      8  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "9      9  ANDAMAN & NICOBAR ISLANDS  Nicobars  Car Nicobar   \n",
       "\n",
       "                        panchayat language  \\\n",
       "0                  Arong(Village)      eng   \n",
       "1                  Arong(Village)      eng   \n",
       "2                  Arong(Village)      eng   \n",
       "3             Big Lapati(Village)      eng   \n",
       "4             Big Lapati(Village)      eng   \n",
       "5             Big Lapati(Village)      eng   \n",
       "6  CAR NICOBAR TRIBAL COUNCIL(GP)      eng   \n",
       "7  CAR NICOBAR TRIBAL COUNCIL(GP)      eng   \n",
       "8  CAR NICOBAR TRIBAL COUNCIL(GP)      eng   \n",
       "9            Chuckchucha(Village)      eng   \n",
       "\n",
       "  auto_inclusion_deprivation_or_exclusion_or_other  head_of_hh gender   age  \\\n",
       "0                     Auto Inclusion or Deprivation       None   None  None   \n",
       "1                                    Auto Exclusion       None   None  None   \n",
       "2                                            Others       None   None  None   \n",
       "3                     Auto Inclusion or Deprivation       None   None  None   \n",
       "4                                    Auto Exclusion       None   None  None   \n",
       "5                                            Others       None   None  None   \n",
       "6                     Auto Inclusion or Deprivation       None   None  None   \n",
       "7                                    Auto Exclusion       None   None  None   \n",
       "8                                            Others       None   None  None   \n",
       "9                     Auto Inclusion or Deprivation       None   None  None   \n",
       "\n",
       "  social_cat fathers_and_mothers_name deprivation_count  \\\n",
       "0       None                     None              None   \n",
       "1       None                     None              None   \n",
       "2       None                     None              None   \n",
       "3       None                     None              None   \n",
       "4       None                     None              None   \n",
       "5       None                     None              None   \n",
       "6       None                     None              None   \n",
       "7       None                     None              None   \n",
       "8       None                     None              None   \n",
       "9       None                     None              None   \n",
       "\n",
       "  auto_inclusion_deprivation_code total_members hh_summary_auto_inclusion  \\\n",
       "0                            None          None                      None   \n",
       "1                            None          None                      None   \n",
       "2                            None          None                      None   \n",
       "3                            None          None                      None   \n",
       "4                            None          None                      None   \n",
       "5                            None          None                      None   \n",
       "6                            None          None                      None   \n",
       "7                            None          None                      None   \n",
       "8                            None          None                      None   \n",
       "9                            None          None                      None   \n",
       "\n",
       "  hh_summary_auto_exclusion hh_summary_auto_other hh_summary_deprivation  \\\n",
       "0                      None                  None                   None   \n",
       "1                      None                  None                   None   \n",
       "2                      None                  None                   None   \n",
       "3                      None                  None                   None   \n",
       "4                      None                  None                   None   \n",
       "5                      None                  None                   None   \n",
       "6                      None                  None                   None   \n",
       "7                      None                  None                   None   \n",
       "8                      None                  None                   None   \n",
       "9                      None                  None                   None   \n",
       "\n",
       "  village  \n",
       "0    None  \n",
       "1    None  \n",
       "2    None  \n",
       "3    None  \n",
       "4    None  \n",
       "5    None  \n",
       "6    None  \n",
       "7    None  \n",
       "8    None  \n",
       "9    None  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_sql('SELECT * from secc limit 10', engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['index', 'state', 'district', 'tehsil', 'panchayat', 'language',\n",
       "       'auto_inclusion_deprivation_or_exclusion_or_other ', 'head_of_hh',\n",
       "       'gender', 'age', 'social_cat', 'fathers_and_mothers_name',\n",
       "       'deprivation_count', 'auto_inclusion_deprivation_code', 'total_members',\n",
       "       'hh_summary_auto_inclusion', 'hh_summary_auto_exclusion',\n",
       "       'hh_summary_auto_other', 'hh_summary_deprivation', 'village'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>auto_inclusion_deprivation_or_exclusion_or_other</th>\n",
       "      <th>count(distinct head_of_hh)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Auto Exclusion</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANDAMAN &amp; NICOBAR ISLANDS</td>\n",
       "      <td>Others</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>Auto Exclusion</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>Others</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ARUNACHAL PRADESH</td>\n",
       "      <td>Auto Exclusion</td>\n",
       "      <td>172612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ARUNACHAL PRADESH</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>172612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ARUNACHAL PRADESH</td>\n",
       "      <td>Others</td>\n",
       "      <td>172612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ASSAM</td>\n",
       "      <td>Auto Exclusion</td>\n",
       "      <td>1352624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ASSAM</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>1352624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ASSAM</td>\n",
       "      <td>Others</td>\n",
       "      <td>1352624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>BIHAR</td>\n",
       "      <td>Auto Exclusion</td>\n",
       "      <td>568235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>BIHAR</td>\n",
       "      <td>Auto Inclusion or Deprivation</td>\n",
       "      <td>569436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>BIHAR</td>\n",
       "      <td>Others</td>\n",
       "      <td>568235</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        state  \\\n",
       "0   ANDAMAN & NICOBAR ISLANDS   \n",
       "1   ANDAMAN & NICOBAR ISLANDS   \n",
       "2   ANDAMAN & NICOBAR ISLANDS   \n",
       "3              ANDHRA PRADESH   \n",
       "4              ANDHRA PRADESH   \n",
       "5              ANDHRA PRADESH   \n",
       "6           ARUNACHAL PRADESH   \n",
       "7           ARUNACHAL PRADESH   \n",
       "8           ARUNACHAL PRADESH   \n",
       "9                       ASSAM   \n",
       "10                      ASSAM   \n",
       "11                      ASSAM   \n",
       "12                      BIHAR   \n",
       "13                      BIHAR   \n",
       "14                      BIHAR   \n",
       "\n",
       "   auto_inclusion_deprivation_or_exclusion_or_other   \\\n",
       "0                                     Auto Exclusion   \n",
       "1                      Auto Inclusion or Deprivation   \n",
       "2                                             Others   \n",
       "3                                     Auto Exclusion   \n",
       "4                      Auto Inclusion or Deprivation   \n",
       "5                                             Others   \n",
       "6                                     Auto Exclusion   \n",
       "7                      Auto Inclusion or Deprivation   \n",
       "8                                             Others   \n",
       "9                                     Auto Exclusion   \n",
       "10                     Auto Inclusion or Deprivation   \n",
       "11                                            Others   \n",
       "12                                    Auto Exclusion   \n",
       "13                     Auto Inclusion or Deprivation   \n",
       "14                                            Others   \n",
       "\n",
       "    count(distinct head_of_hh)  \n",
       "0                            0  \n",
       "1                            0  \n",
       "2                            0  \n",
       "3                            0  \n",
       "4                            0  \n",
       "5                            0  \n",
       "6                       172612  \n",
       "7                       172612  \n",
       "8                       172612  \n",
       "9                      1352624  \n",
       "10                     1352624  \n",
       "11                     1352624  \n",
       "12                      568235  \n",
       "13                      569436  \n",
       "14                      568235  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_sql('SELECT state, `auto_inclusion_deprivation_or_exclusion_or_other `, count(distinct head_of_hh) from secc group by state, `auto_inclusion_deprivation_or_exclusion_or_other `', engine)\n",
    "df"
   ]
  },
  {
   "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": 2
}