Vill_report.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyforest import *\n",
    "import string as s\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df= pd.read_csv(r'view_village.csv',encoding= 'ISO-8859-1',index_col=0)"
   ]
  },
  {
   "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>villagename</th>\n",
       "      <th>blockid</th>\n",
       "      <th>madeondate</th>\n",
       "      <th>isactive</th>\n",
       "      <th>blcname</th>\n",
       "      <th>aid</th>\n",
       "      <th>areatype</th>\n",
       "      <th>areaid</th>\n",
       "      <th>areacategoryname</th>\n",
       "      <th>distid</th>\n",
       "      <th>dname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>villageid</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Khadaka</td>\n",
       "      <td>236</td>\n",
       "      <td>9/28/2019 16:21</td>\n",
       "      <td>1</td>\n",
       "      <td>Pallahara</td>\n",
       "      <td>1</td>\n",
       "      <td>Rural</td>\n",
       "      <td>1</td>\n",
       "      <td>Block</td>\n",
       "      <td>1</td>\n",
       "      <td>Angul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Tanugula</td>\n",
       "      <td>236</td>\n",
       "      <td>2/14/2019 11:42</td>\n",
       "      <td>1</td>\n",
       "      <td>Pallahara</td>\n",
       "      <td>1</td>\n",
       "      <td>Rural</td>\n",
       "      <td>1</td>\n",
       "      <td>Block</td>\n",
       "      <td>1</td>\n",
       "      <td>Angul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Kaira</td>\n",
       "      <td>236</td>\n",
       "      <td>12/16/2018 3:35</td>\n",
       "      <td>1</td>\n",
       "      <td>Pallahara</td>\n",
       "      <td>1</td>\n",
       "      <td>Rural</td>\n",
       "      <td>1</td>\n",
       "      <td>Block</td>\n",
       "      <td>1</td>\n",
       "      <td>Angul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Paburi R.F</td>\n",
       "      <td>236</td>\n",
       "      <td>9/20/2019 17:22</td>\n",
       "      <td>1</td>\n",
       "      <td>Pallahara</td>\n",
       "      <td>1</td>\n",
       "      <td>Rural</td>\n",
       "      <td>1</td>\n",
       "      <td>Block</td>\n",
       "      <td>1</td>\n",
       "      <td>Angul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Baradiha</td>\n",
       "      <td>236</td>\n",
       "      <td>12/16/2018 3:35</td>\n",
       "      <td>1</td>\n",
       "      <td>Pallahara</td>\n",
       "      <td>1</td>\n",
       "      <td>Rural</td>\n",
       "      <td>1</td>\n",
       "      <td>Block</td>\n",
       "      <td>1</td>\n",
       "      <td>Angul</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          villagename  blockid       madeondate  isactive    blcname  aid  \\\n",
       "villageid                                                                   \n",
       "1             Khadaka      236  9/28/2019 16:21         1  Pallahara    1   \n",
       "2            Tanugula      236  2/14/2019 11:42         1  Pallahara    1   \n",
       "3               Kaira      236  12/16/2018 3:35         1  Pallahara    1   \n",
       "4          Paburi R.F      236  9/20/2019 17:22         1  Pallahara    1   \n",
       "5            Baradiha      236  12/16/2018 3:35         1  Pallahara    1   \n",
       "\n",
       "          areatype  areaid areacategoryname  distid  dname  \n",
       "villageid                                                   \n",
       "1            Rural       1            Block       1  Angul  \n",
       "2            Rural       1            Block       1  Angul  \n",
       "3            Rural       1            Block       1  Angul  \n",
       "4            Rural       1            Block       1  Angul  \n",
       "5            Rural       1            Block       1  Angul  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method IndexOpsMixin.nunique of villageid\n",
       "1             Khadaka\n",
       "2            Tanugula\n",
       "3               Kaira\n",
       "4          Paburi R.F\n",
       "5            Baradiha\n",
       "             ...     \n",
       "81616      Puttarguma\n",
       "81617    Andharijhola\n",
       "81618         Gantili\n",
       "81619       Karansahi\n",
       "81620       Pandasahi\n",
       "Name: villagename, Length: 81620, dtype: object>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.villagename.nunique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=pd.read_csv(r'C:\\Users\\Rakesh\\Desktop\\Sql\\Village_data.Gascon.csv',index_col=0)"
   ]
  },
  {
   "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>dist_code</th>\n",
       "      <th>dist_name</th>\n",
       "      <th>company</th>\n",
       "      <th>dealer_code</th>\n",
       "      <th>dealer_name</th>\n",
       "      <th>ccode</th>\n",
       "      <th>cname</th>\n",
       "      <th>caddress</th>\n",
       "      <th>pincode</th>\n",
       "      <th>mobile</th>\n",
       "      <th>...</th>\n",
       "      <th>issync</th>\n",
       "      <th>updatedate</th>\n",
       "      <th>lpgid</th>\n",
       "      <th>instalationdate</th>\n",
       "      <th>refillcount</th>\n",
       "      <th>isloan</th>\n",
       "      <th>letterreceived</th>\n",
       "      <th>modiknownstatus</th>\n",
       "      <th>callingid</th>\n",
       "      <th>reassignstatus</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2201</td>\n",
       "      <td>ANGUL</td>\n",
       "      <td>BPCL</td>\n",
       "      <td>168522</td>\n",
       "      <td>SUPRIYA BHARATGAS</td>\n",
       "      <td>78553260</td>\n",
       "      <td>MRS KUSUMA  BEHERA</td>\n",
       "      <td>1 1 10 KHANDAHATA ANGUL ODISHA Orissa ANGUL</td>\n",
       "      <td>759132</td>\n",
       "      <td>7683873734</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0000-00-00 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5342733</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2201</td>\n",
       "      <td>ANGUL</td>\n",
       "      <td>BPCL</td>\n",
       "      <td>168522</td>\n",
       "      <td>SUPRIYA BHARATGAS</td>\n",
       "      <td>78593665</td>\n",
       "      <td>MRS SUKANTI  BEHERA</td>\n",
       "      <td>0 0 0 JINDAL ROAD ANGUL ODISHA Orissa KUMANDA</td>\n",
       "      <td>759143</td>\n",
       "      <td>7377146761</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0000-00-00 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3617966</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2201</td>\n",
       "      <td>ANGUL</td>\n",
       "      <td>BPCL</td>\n",
       "      <td>168522</td>\n",
       "      <td>SUPRIYA BHARATGAS</td>\n",
       "      <td>78594947</td>\n",
       "      <td>MRS GITA  MOHARANA</td>\n",
       "      <td>4 2 3 JINDAL ROAD ANGUL ODISHA Orissa ANGUL</td>\n",
       "      <td>759143</td>\n",
       "      <td>9861613888</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0000-00-00 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3617967</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2201</td>\n",
       "      <td>ANGUL</td>\n",
       "      <td>BPCL</td>\n",
       "      <td>168522</td>\n",
       "      <td>SUPRIYA BHARATGAS</td>\n",
       "      <td>78595154</td>\n",
       "      <td>MRS SABITRI  SAHU</td>\n",
       "      <td>2 1 23 JINDAL ROAD ANGUL ODISHA Orissa ANGUL</td>\n",
       "      <td>759143</td>\n",
       "      <td>9777345643</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0000-00-00 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3617968</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2201</td>\n",
       "      <td>ANGUL</td>\n",
       "      <td>BPCL</td>\n",
       "      <td>168522</td>\n",
       "      <td>SUPRIYA BHARATGAS</td>\n",
       "      <td>78596195</td>\n",
       "      <td>MRS SHOBHAGINI  NAYAK</td>\n",
       "      <td>3 3 2 INKARABANDHA ANGUL ODISHA Orissa ANGUL</td>\n",
       "      <td>759132</td>\n",
       "      <td>9668371858</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0000-00-00 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>879872</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    dist_code dist_name company  dealer_code        dealer_name     ccode  \\\n",
       "id                                                                          \n",
       "1        2201     ANGUL    BPCL       168522  SUPRIYA BHARATGAS  78553260   \n",
       "2        2201     ANGUL    BPCL       168522  SUPRIYA BHARATGAS  78593665   \n",
       "3        2201     ANGUL    BPCL       168522  SUPRIYA BHARATGAS  78594947   \n",
       "4        2201     ANGUL    BPCL       168522  SUPRIYA BHARATGAS  78595154   \n",
       "5        2201     ANGUL    BPCL       168522  SUPRIYA BHARATGAS  78596195   \n",
       "\n",
       "                    cname                                       caddress  \\\n",
       "id                                                                         \n",
       "1      MRS KUSUMA  BEHERA    1 1 10 KHANDAHATA ANGUL ODISHA Orissa ANGUL   \n",
       "2     MRS SUKANTI  BEHERA  0 0 0 JINDAL ROAD ANGUL ODISHA Orissa KUMANDA   \n",
       "3      MRS GITA  MOHARANA    4 2 3 JINDAL ROAD ANGUL ODISHA Orissa ANGUL   \n",
       "4       MRS SABITRI  SAHU   2 1 23 JINDAL ROAD ANGUL ODISHA Orissa ANGUL   \n",
       "5   MRS SHOBHAGINI  NAYAK   3 3 2 INKARABANDHA ANGUL ODISHA Orissa ANGUL   \n",
       "\n",
       "    pincode      mobile  ...  issync           updatedate lpgid  \\\n",
       "id                       ...                                      \n",
       "1    759132  7683873734  ...       0  0000-00-00 00:00:00   NaN   \n",
       "2    759143  7377146761  ...       0  0000-00-00 00:00:00   NaN   \n",
       "3    759143  9861613888  ...       0  0000-00-00 00:00:00   NaN   \n",
       "4    759143  9777345643  ...       0  0000-00-00 00:00:00   NaN   \n",
       "5    759132  9668371858  ...       0  0000-00-00 00:00:00   NaN   \n",
       "\n",
       "   instalationdate refillcount isloan letterreceived modiknownstatus  \\\n",
       "id                                                                     \n",
       "1       2016-11-01           3      4              0               0   \n",
       "2       2016-11-01           2      4              0               0   \n",
       "3       2016-11-01           1      4              0               0   \n",
       "4       2016-11-01           1      4              0               0   \n",
       "5       2016-11-01           2      4              2               0   \n",
       "\n",
       "    callingid  reassignstatus  \n",
       "id                             \n",
       "1     5342733               1  \n",
       "2     3617966               1  \n",
       "3     3617967               1  \n",
       "4     3617968               1  \n",
       "5      879872               1  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.vill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-44-181dc7c456f1>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[0morg_rec\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'dname'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m==\u001b[0m\u001b[1;34m'Angul'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'villagename'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[1;33m[\u001b[0m \u001b[0mx\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mx\u001b[0m \u001b[1;32min\u001b[0m \u001b[0morg_rec\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mndf1\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mlist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0morg_rec\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-44-181dc7c456f1>\u001b[0m in \u001b[0;36m<listcomp>\u001b[1;34m(.0)\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[0morg_rec\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'dname'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m==\u001b[0m\u001b[1;34m'Angul'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'villagename'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[1;33m[\u001b[0m \u001b[0mx\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mx\u001b[0m \u001b[1;32min\u001b[0m \u001b[0morg_rec\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mndf1\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mlist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0morg_rec\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\.vscode\\Anaconda3new\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m__nonzero__\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1553\u001b[0m             \u001b[1;34m\"The truth value of a {0} is ambiguous. \"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1554\u001b[0m             \"Use a.empty, a.bool(), a.item(), a.any() or a.all().\".format(\n\u001b[1;32m-> 1555\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__class__\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__name__\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1556\u001b[0m             )\n\u001b[0;32m   1557\u001b[0m         )\n",
      "\u001b[1;31mValueError\u001b[0m: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()."
     ]
    }
   ],
   "source": [
    "org_rec=df[df['dname']=='Angul']['villagename']\n",
    "[ x for x in org_rec if ndf1. in list(org_rec) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>KHANDAHATA ANGUL ODISHA Orissa ANGUL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>JINDAL ROAD ANGUL ODISHA Orissa KUMANDA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>JINDAL ROAD ANGUL ODISHA Orissa ANGUL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>JINDAL ROAD ANGUL ODISHA Orissa ANGUL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>INKARABANDHA ANGUL ODISHA Orissa ANGUL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>996</td>\n",
       "      <td>C1246</td>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>KOSALA ANGUL ODISHA Orissa KOSALA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>997</td>\n",
       "      <td>K425</td>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>PALASAMA DEOGARH ODISHA Orissa PALASAMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>998</td>\n",
       "      <td>K426</td>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>PALASAMA DEOGARH ODISHA Orissa PALASAMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>999</td>\n",
       "      <td>K427</td>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>PALASAMA DEOGARH ODISHA Orissa PALASAMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1000</td>\n",
       "      <td>K428</td>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>PALASAMA DEOGARH ODISHA Orissa PALASAMA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          0  1   2                                        3\n",
       "id                                                         \n",
       "1         1  1  10     KHANDAHATA ANGUL ODISHA Orissa ANGUL\n",
       "2         0  0   0  JINDAL ROAD ANGUL ODISHA Orissa KUMANDA\n",
       "3         4  2   3    JINDAL ROAD ANGUL ODISHA Orissa ANGUL\n",
       "4         2  1  23    JINDAL ROAD ANGUL ODISHA Orissa ANGUL\n",
       "5         3  3   2   INKARABANDHA ANGUL ODISHA Orissa ANGUL\n",
       "...     ... ..  ..                                      ...\n",
       "996   C1246  1   A        KOSALA ANGUL ODISHA Orissa KOSALA\n",
       "997    K425  1   A  PALASAMA DEOGARH ODISHA Orissa PALASAMA\n",
       "998    K426  1   A  PALASAMA DEOGARH ODISHA Orissa PALASAMA\n",
       "999    K427  1   A  PALASAMA DEOGARH ODISHA Orissa PALASAMA\n",
       "1000   K428  1   A  PALASAMA DEOGARH ODISHA Orissa PALASAMA\n",
       "\n",
       "[1000 rows x 4 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ndf=df1['caddress'].str.split(' ',n=3,expand=True)\n",
    "ndf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    132\n",
       "1      9\n",
       "2     81\n",
       "dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ndf1=ndf[3].str.split(' ',n=2,expand=True)\n",
    "ndf1.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'72','PODAKHOL','NISA','KUTASINGA','BATGAON','GAUDPARA','KHANDAHATA','UPARAMUNDA','64','BAGHUABOL','BATAGAN','BRAHMANABIL','BHALUGADIA','KAMPASALA','MACHHAKUTA','BADATRIBIDA','TANGIR','TURANGA','DAMAJODI','PATAKUMUNDA','HULURISINGHA','KANLOI','ANGUL','SUSUBA','SAMAL','ANGARABANDHA','PIPALBAHAL','KELO','HITASARA','BUDHAPAL','KANTIAPASI','BARASAHI','CHANGUDIA','KUSHAKILA','SAPOINALI','NUAPADA','GAMBHARIPAL','KHAMAR','BAHALASAHI','RAIJHARAN','GANDAMALA','MAHAKUD','MUNDURIBEDA','555HANDAHATA','BOL','PANUNALI','CHANDRAPUR','KISINDA','BAGADIA','PATRAPADA','NUAGOAN','GHUNTULIPASI','BALIPATA','KOSALA','JI','BAHALA','NATADA','HATIADANDA','BAGHABAR','SARAPALI','BRAHMANBIL','INKARABANDHA','RAIPAL','KAINCHHAPADA','KARADAGADIA','SENDHA','JARSINGHA','PURIABEDA','PURUNAGARH','57','KELOBERNI','BALIPASI','SARADHAPUR','PODAPADA','SIMILIASAHI','JHARBEDA','BASANTPUR','CHHENDIPADA','SENDHAPAL','LANDIMAL','BANJAGOLA','KARLAGA','JINDALA','BARAMANCHA','TAKUA','HARICHANDANPUR','BANTALA','RENGALI','SAHLEBHATA','PURUNAKOT','MARUDHIP','UPARMUNDA','KUNJAMURA','JARASINGHA','PARACHHAT','AUNLI','KUMANDA','TENTLOI','SAHEBI','JARAPADA','0','78','TALEISAR','SHARADHAPUR','62','ARKIL','SIMILIPADA','NH','NH55','HINJAMURA','BALASINGHA','JINDAL','CHAMPATIMUNDA','KAUNSIPAL','BETHIABERNI','PALSAMA','BHETIABERNI','MAMURIA','GENGUTIA','KUMURISINGHA','SURAB','PALASAMA','SABALABHANGA','RAIJHARANA','DANGAPAL','LUHAMUNDA','PODAGARH','BAGDIA','KHINDA','SANSAMARA','RANTALEI','SIMILIPAL'}\n"
     ]
    }
   ],
   "source": [
    "def find_notinlist(data1,data2):\n",
    "    new_rec=[]\n",
    "    for name in ndf1[0]:\n",
    "        if name not in org_rec:\n",
    "            new_rec.append(name)\n",
    "    return(set(new_rec))\n",
    "\n",
    "        \n",
    "df3=pd.DataFrame(find_notinlist(ndf1,org_rec))\n",
    "print(''.join((str(find_notinlist(ndf1,org_rec)).split())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapp_village= pd.concat((org_rec,df3),\n",
    "                        axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapp_village.fillna('NotAvailable')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict1={'villagename':'Vill_name',0:'Extract From card Details'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapp_village.rename(mapper=dict1,axis=1,inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "scatter_mat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_grp=df.groupby(by=['villagename','dname'],axis=0,as_index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "must supply a tuple to get_group with multiple grouping keys",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-55-ca5e4d730ffa>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf_grp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_group\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mareacategoryname\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue_counts\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mareatype\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue_counts\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\.vscode\\Anaconda3new\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36mget_group\u001b[1;34m(self, name, obj)\u001b[0m\n\u001b[0;32m    676\u001b[0m             \u001b[0mobj\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_selected_obj\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    677\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 678\u001b[1;33m         \u001b[0minds\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    679\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    680\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\.vscode\\Anaconda3new\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36m_get_index\u001b[1;34m(self, name)\u001b[0m\n\u001b[0;32m    489\u001b[0m         \u001b[0mSafe\u001b[0m \u001b[0mget\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtranslate\u001b[0m \u001b[0mkeys\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mdatelike\u001b[0m \u001b[0mto\u001b[0m \u001b[0munderlying\u001b[0m \u001b[0mrepr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    490\u001b[0m         \"\"\"\n\u001b[1;32m--> 491\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_indices\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    492\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    493\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mcache_readonly\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\.vscode\\Anaconda3new\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36m_get_indices\u001b[1;34m(self, names)\u001b[0m\n\u001b[0;32m    463\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname_sample\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtuple\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    464\u001b[0m                 \u001b[0mmsg\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"must supply a tuple to get_group with multiple\"\u001b[0m \u001b[1;34m\" grouping keys\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 465\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    466\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname_sample\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindex_sample\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    467\u001b[0m                 \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: must supply a tuple to get_group with multiple grouping keys"
     ]
    }
   ],
   "source": [
    "df_grp.get_group(df.areacategoryname.value_counts(),df.areatype.value_counts())"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}