exportdf.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import school from SANGRUR folder\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\n",
      "/var/folders/09/905d00cj45n1jrdrn2zd5f9c0000gn/T/ipykernel_94533/846169934.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df = df.append(data)\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>S.No</th>\n",
       "      <th>UDISE</th>\n",
       "      <th>In database?</th>\n",
       "      <th>School Name</th>\n",
       "      <th>District</th>\n",
       "      <th>Same trades</th>\n",
       "      <th>Trade 1</th>\n",
       "      <th>9th Girls</th>\n",
       "      <th>9th Boys</th>\n",
       "      <th>IX Total</th>\n",
       "      <th>10th Girls</th>\n",
       "      <th>10th Boys</th>\n",
       "      <th>X Total</th>\n",
       "      <th>IX-X Total</th>\n",
       "      <th>11th Girls</th>\n",
       "      <th>11th Boys</th>\n",
       "      <th>XI Total</th>\n",
       "      <th>12th Girls</th>\n",
       "      <th>12th Boys</th>\n",
       "      <th>XII Total</th>\n",
       "      <th>XI-XII Total</th>\n",
       "      <th>Trade 2</th>\n",
       "      <th>9th Girls</th>\n",
       "      <th>9th Boys</th>\n",
       "      <th>IX Total</th>\n",
       "      <th>10th Girls</th>\n",
       "      <th>10th Boys</th>\n",
       "      <th>X Total</th>\n",
       "      <th>IX-X Total</th>\n",
       "      <th>11th Girls</th>\n",
       "      <th>11th Boys</th>\n",
       "      <th>XI Total</th>\n",
       "      <th>12th Girls</th>\n",
       "      <th>12th Boys</th>\n",
       "      <th>XII Total</th>\n",
       "      <th>XI-XII Total</th>\n",
       "      <th>Trade 3</th>\n",
       "      <th>9th Girls</th>\n",
       "      <th>9th Boys</th>\n",
       "      <th>IX Total</th>\n",
       "      <th>10th Girls</th>\n",
       "      <th>10th Boys</th>\n",
       "      <th>X Total</th>\n",
       "      <th>IX-X Total</th>\n",
       "      <th>11th Girls</th>\n",
       "      <th>11th Boys</th>\n",
       "      <th>XI Total</th>\n",
       "      <th>12th Girls</th>\n",
       "      <th>12th Boys</th>\n",
       "      <th>XII Total</th>\n",
       "      <th>XI-XII Total</th>\n",
       "      <th>NaN</th>\n",
       "      <th>Aggregate IX-X</th>\n",
       "      <th>Aggregate XI-XII</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3020207202</td>\n",
       "      <td>3020207202</td>\n",
       "      <td>GSSS BHINDI SAIDAN</td>\n",
       "      <td>AMRITSAR</td>\n",
       "      <td>False</td>\n",
       "      <td>IT/ITES</td>\n",
       "      <td>8</td>\n",
       "      <td>17</td>\n",
       "      <td>25</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>45</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "      <td>7</td>\n",
       "      <td>16</td>\n",
       "      <td>23</td>\n",
       "      <td>42</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>23</td>\n",
       "      <td>8</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>42</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3020304802</td>\n",
       "      <td>3020304802</td>\n",
       "      <td>GSSS OTHIAN</td>\n",
       "      <td>AMRITSAR</td>\n",
       "      <td>False</td>\n",
       "      <td>Security</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>Automotive</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>26</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3020406602</td>\n",
       "      <td>3020406602</td>\n",
       "      <td>GSSS MANA WALA KALAN</td>\n",
       "      <td>AMRITSAR</td>\n",
       "      <td>False</td>\n",
       "      <td>Retail</td>\n",
       "      <td>7</td>\n",
       "      <td>16</td>\n",
       "      <td>23</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>19</td>\n",
       "      <td>89</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>24</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>24</td>\n",
       "      <td>48</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>3020501402</td>\n",
       "      <td>3020501402</td>\n",
       "      <td>GSSS WADALA VIRMBHOMA</td>\n",
       "      <td>AMRITSAR</td>\n",
       "      <td>False</td>\n",
       "      <td>Security</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>27</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>21</td>\n",
       "      <td>48</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>IT/ITES</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>24</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>45</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>28</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>93</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>3020802702</td>\n",
       "      <td>3020802702</td>\n",
       "      <td>GSSS NAWAN KOT G</td>\n",
       "      <td>AMRITSAR</td>\n",
       "      <td>False</td>\n",
       "      <td>Beauty and Wellness</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>64</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>40</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>56</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>120</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0 S.No       UDISE In database?            School Name District  Same trades  \\\n",
       "1    1  3020207202   3020207202     GSSS BHINDI SAIDAN  AMRITSAR       False   \n",
       "2    2  3020304802   3020304802            GSSS OTHIAN  AMRITSAR       False   \n",
       "3    3  3020406602   3020406602   GSSS MANA WALA KALAN  AMRITSAR       False   \n",
       "4    4  3020501402   3020501402  GSSS WADALA VIRMBHOMA  AMRITSAR       False   \n",
       "5    5  3020802702   3020802702       GSSS NAWAN KOT G  AMRITSAR       False   \n",
       "\n",
       "0              Trade 1 9th Girls 9th Boys IX Total 10th Girls 10th Boys  \\\n",
       "1              IT/ITES         8       17       25          9        11   \n",
       "2             Security        11        7       18          6         3   \n",
       "3               Retail         7       16       23         15         6   \n",
       "4             Security         6       21       27          6        15   \n",
       "5  Beauty and Wellness        36        0       36         28         0   \n",
       "\n",
       "0 X Total IX-X Total 11th Girls 11th Boys XI Total 12th Girls 12th Boys  \\\n",
       "1      20         45          7        12       19          7        16   \n",
       "2       9         27          8         7       15          0         0   \n",
       "3      21         44          0        70       70          6        13   \n",
       "4      21         48          6        22       28          0         0   \n",
       "5      28         64         19         0       19         21         0   \n",
       "\n",
       "0 XII Total XI-XII Total     Trade 2 9th Girls 9th Boys IX Total 10th Girls  \\\n",
       "1        23           42  Healthcare        17        6       23          8   \n",
       "2         0           15  Automotive         0       10       10          0   \n",
       "3        19           89  Healthcare         8       16       24          9   \n",
       "4         0           28     IT/ITES         8       16       24         14   \n",
       "5        21           40  Healthcare        31        0       31         25   \n",
       "\n",
       "0 10th Boys X Total IX-X Total 11th Girls 11th Boys XI Total 12th Girls  \\\n",
       "1        11      19         42          9         9       18          9   \n",
       "2         9       9         19          0        26       26          0   \n",
       "3        15      24         48         39         0       39          5   \n",
       "4         7      21         45         12        16       28          7   \n",
       "5         0      25         56         34         0       34         20   \n",
       "\n",
       "0 12th Boys XII Total XI-XII Total Trade 3 9th Girls 9th Boys IX Total  \\\n",
       "1         1        10           28     NaN       NaN      NaN        0   \n",
       "2        28        28           54     NaN       NaN      NaN        0   \n",
       "3         6        11           50     NaN       NaN      NaN        0   \n",
       "4         3        10           38     NaN       NaN      NaN        0   \n",
       "5         0        20           54     NaN       NaN      NaN        0   \n",
       "\n",
       "0 10th Girls 10th Boys X Total IX-X Total 11th Girls 11th Boys XI Total  \\\n",
       "1        NaN       NaN       0          0        NaN       NaN        0   \n",
       "2        NaN       NaN       0          0        NaN       NaN        0   \n",
       "3        NaN       NaN       0          0        NaN       NaN        0   \n",
       "4        NaN       NaN       0          0        NaN       NaN        0   \n",
       "5        NaN       NaN       0          0        NaN       NaN        0   \n",
       "\n",
       "0 12th Girls 12th Boys XII Total XI-XII Total  NaN Aggregate IX-X  \\\n",
       "1        NaN       NaN         0            0  NaN             87   \n",
       "2        NaN       NaN         0            0  NaN             46   \n",
       "3        NaN       NaN         0            0  NaN             92   \n",
       "4        NaN       NaN         0            0  NaN             93   \n",
       "5        NaN       NaN         0            0  NaN            120   \n",
       "\n",
       "0 Aggregate XI-XII  \n",
       "1               70  \n",
       "2               69  \n",
       "3              139  \n",
       "4               66  \n",
       "5               94  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read all csv from SANGRUR folder and merge them\n",
    "import os\n",
    "path = './SANGRUR'\n",
    "files = os.listdir(path)\n",
    "files_csv = [f for f in files if f[-3:] == 'csv']\n",
    "df = pd.DataFrame()\n",
    "for f in files_csv:\n",
    "    data = pd.read_csv(os.path.join(path, f))\n",
    "    df = df.append(data)\n",
    "    #remove duplicate\n",
    "df = df.drop_duplicates()\n",
    "df.head()\n",
    "# show all columns\n",
    "pd.set_option('display.max_columns', None)\n",
    "df.head()\n",
    "#merge the blocks and df\n",
    "#read excel\n",
    "df_block = pd.read_excel('./blocks/LUDHIANA.xlsx')\n",
    "df_block.head()\n",
    "#make first row as header\n",
    "new_header = df_block.iloc[0] #grab the first row for the header\n",
    "df_block = df_block[1:] #take the data less the header row\n",
    "df_block.columns = new_header #set the header row as the df header\n",
    "df_block.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>displayFieldName</th>\n",
       "      <th>transform__originPosition</th>\n",
       "      <th>transform__scale</th>\n",
       "      <th>transform__translate</th>\n",
       "      <th>fieldAliases__objectid</th>\n",
       "      <th>fieldAliases__latitude</th>\n",
       "      <th>fieldAliases__longitude</th>\n",
       "      <th>fieldAliases__lgd_state_</th>\n",
       "      <th>fieldAliases__lgd_distri</th>\n",
       "      <th>fieldAliases__pincode</th>\n",
       "      <th>fieldAliases__schcd</th>\n",
       "      <th>fieldAliases__schname</th>\n",
       "      <th>fieldAliases__schmgt</th>\n",
       "      <th>fieldAliases__schcat</th>\n",
       "      <th>fieldAliases__schtype</th>\n",
       "      <th>fieldAliases__rururb</th>\n",
       "      <th>fieldAliases__school_cat</th>\n",
       "      <th>fieldAliases__school_typ</th>\n",
       "      <th>fieldAliases__stcode11</th>\n",
       "      <th>fieldAliases__dtcode11</th>\n",
       "      <th>fieldAliases__sdtcode11</th>\n",
       "      <th>fieldAliases__sdtname</th>\n",
       "      <th>fieldAliases__subdt_lgd</th>\n",
       "      <th>fieldAliases__vilcode11</th>\n",
       "      <th>fieldAliases__stname</th>\n",
       "      <th>fieldAliases__dtname</th>\n",
       "      <th>fieldAliases__vilname</th>\n",
       "      <th>fieldAliases__management</th>\n",
       "      <th>fieldAliases__location</th>\n",
       "      <th>fieldAliases__udise_stco</th>\n",
       "      <th>geometryType</th>\n",
       "      <th>spatialReference__wkid</th>\n",
       "      <th>spatialReference__latestWkid</th>\n",
       "      <th>fields__name</th>\n",
       "      <th>fields__type</th>\n",
       "      <th>fields__alias</th>\n",
       "      <th>fields__length</th>\n",
       "      <th>features__attributes__objectid</th>\n",
       "      <th>features__attributes__latitude</th>\n",
       "      <th>features__attributes__longitude</th>\n",
       "      <th>features__attributes__lgd_state_</th>\n",
       "      <th>features__attributes__lgd_distri</th>\n",
       "      <th>features__attributes__pincode</th>\n",
       "      <th>features__attributes__schcd</th>\n",
       "      <th>features__attributes__schname</th>\n",
       "      <th>features__attributes__schmgt</th>\n",
       "      <th>features__attributes__schcat</th>\n",
       "      <th>features__attributes__schtype</th>\n",
       "      <th>features__attributes__rururb</th>\n",
       "      <th>features__attributes__school_cat</th>\n",
       "      <th>features__attributes__school_typ</th>\n",
       "      <th>features__attributes__stcode11</th>\n",
       "      <th>features__attributes__dtcode11</th>\n",
       "      <th>features__attributes__sdtcode11</th>\n",
       "      <th>features__attributes__sdtname</th>\n",
       "      <th>features__attributes__subdt_lgd</th>\n",
       "      <th>features__attributes__vilcode11</th>\n",
       "      <th>features__attributes__stname</th>\n",
       "      <th>features__attributes__dtname</th>\n",
       "      <th>features__attributes__vilname</th>\n",
       "      <th>features__attributes__management</th>\n",
       "      <th>features__attributes__location</th>\n",
       "      <th>features__attributes__udise_stco</th>\n",
       "      <th>features__geometry__x</th>\n",
       "      <th>features__geometry__y</th>\n",
       "      <th>exceededTransferLimit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>vilname</td>\n",
       "      <td>upperLeft</td>\n",
       "      <td>38.218514</td>\n",
       "      <td>68.501547</td>\n",
       "      <td>objectid</td>\n",
       "      <td>latitude</td>\n",
       "      <td>longitude</td>\n",
       "      <td>lgd_state_</td>\n",
       "      <td>lgd_distri</td>\n",
       "      <td>Pincode</td>\n",
       "      <td>schcd</td>\n",
       "      <td>School</td>\n",
       "      <td>Mngt Code</td>\n",
       "      <td>Cat_Code</td>\n",
       "      <td>Type Code</td>\n",
       "      <td>Rural_Urban</td>\n",
       "      <td>Category</td>\n",
       "      <td>School Type</td>\n",
       "      <td>stcode11</td>\n",
       "      <td>dtcode11</td>\n",
       "      <td>sdtcode11</td>\n",
       "      <td>Sub-district</td>\n",
       "      <td>subdt_lgd</td>\n",
       "      <td>vilcode11</td>\n",
       "      <td>State</td>\n",
       "      <td>District</td>\n",
       "      <td>Village</td>\n",
       "      <td>Management</td>\n",
       "      <td>Location</td>\n",
       "      <td>udise_stco</td>\n",
       "      <td>esriGeometryPoint</td>\n",
       "      <td>102100.0</td>\n",
       "      <td>3857.0</td>\n",
       "      <td>objectid</td>\n",
       "      <td>esriFieldTypeOID</td>\n",
       "      <td>objectid</td>\n",
       "      <td>NaN</td>\n",
       "      <td>940976</td>\n",
       "      <td>31.047346</td>\n",
       "      <td>75.580788</td>\n",
       "      <td>3.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>144039</td>\n",
       "      <td>3040906601</td>\n",
       "      <td>GPS RAMEWAL</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Primary</td>\n",
       "      <td>Co-education</td>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>211</td>\n",
       "      <td>Phillaur</td>\n",
       "      <td>211.0</td>\n",
       "      <td>30056.0</td>\n",
       "      <td>PUNJAB</td>\n",
       "      <td>Jalandhar</td>\n",
       "      <td>Ramewal (96)</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>Rural</td>\n",
       "      <td>3</td>\n",
       "      <td>220143</td>\n",
       "      <td>-95212</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.218514</td>\n",
       "      <td>35.032118</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>latitude</td>\n",
       "      <td>esriFieldTypeDouble</td>\n",
       "      <td>latitude</td>\n",
       "      <td>NaN</td>\n",
       "      <td>939675</td>\n",
       "      <td>30.932993</td>\n",
       "      <td>75.556534</td>\n",
       "      <td>3.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>142025</td>\n",
       "      <td>3091104602</td>\n",
       "      <td>GMS KOT MANA</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Upper Primary only</td>\n",
       "      <td>Co-education</td>\n",
       "      <td>3</td>\n",
       "      <td>41</td>\n",
       "      <td>230</td>\n",
       "      <td>Jagraon</td>\n",
       "      <td>230.0</td>\n",
       "      <td>33843.0</td>\n",
       "      <td>PUNJAB</td>\n",
       "      <td>Ludhiana</td>\n",
       "      <td>Kot Mana (18)</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>Rural</td>\n",
       "      <td>3</td>\n",
       "      <td>220073</td>\n",
       "      <td>-94824</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>longitude</td>\n",
       "      <td>esriFieldTypeDouble</td>\n",
       "      <td>longitude</td>\n",
       "      <td>NaN</td>\n",
       "      <td>939891</td>\n",
       "      <td>30.943320</td>\n",
       "      <td>75.583791</td>\n",
       "      <td>3.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>142025</td>\n",
       "      <td>3091101903</td>\n",
       "      <td>NEW PUNJAB PUBLIC SCHOOL</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Pr. with Up.Pr. Sec. and H.Sec.</td>\n",
       "      <td>Co-education</td>\n",
       "      <td>3</td>\n",
       "      <td>41</td>\n",
       "      <td>230</td>\n",
       "      <td>Jagraon</td>\n",
       "      <td>230.0</td>\n",
       "      <td>33820.0</td>\n",
       "      <td>PUNJAB</td>\n",
       "      <td>Ludhiana</td>\n",
       "      <td>Bhundri (16)</td>\n",
       "      <td>Pvt. Unaided (Recognized)</td>\n",
       "      <td>Rural</td>\n",
       "      <td>3</td>\n",
       "      <td>220152</td>\n",
       "      <td>-94859</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>lgd_state_</td>\n",
       "      <td>esriFieldTypeInteger</td>\n",
       "      <td>lgd_state_</td>\n",
       "      <td>NaN</td>\n",
       "      <td>939036</td>\n",
       "      <td>30.904885</td>\n",
       "      <td>75.420281</td>\n",
       "      <td>3.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>142033</td>\n",
       "      <td>3091108201</td>\n",
       "      <td>GPS SODIWALA</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Primary</td>\n",
       "      <td>Co-education</td>\n",
       "      <td>3</td>\n",
       "      <td>41</td>\n",
       "      <td>230</td>\n",
       "      <td>Jagraon</td>\n",
       "      <td>230.0</td>\n",
       "      <td>33872.0</td>\n",
       "      <td>PUNJAB</td>\n",
       "      <td>Ludhiana</td>\n",
       "      <td>Sodhiwala (42)</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>Rural</td>\n",
       "      <td>3</td>\n",
       "      <td>219676</td>\n",
       "      <td>-94728</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>lgd_distri</td>\n",
       "      <td>esriFieldTypeInteger</td>\n",
       "      <td>lgd_distri</td>\n",
       "      <td>NaN</td>\n",
       "      <td>940592</td>\n",
       "      <td>31.012063</td>\n",
       "      <td>75.420986</td>\n",
       "      <td>3.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>144041</td>\n",
       "      <td>3041101901</td>\n",
       "      <td>GPS GOBINDPUR</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Primary</td>\n",
       "      <td>Co-education</td>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>210</td>\n",
       "      <td>Nakodar</td>\n",
       "      <td>210.0</td>\n",
       "      <td>29988.0</td>\n",
       "      <td>PUNJAB</td>\n",
       "      <td>Jalandhar</td>\n",
       "      <td>Baloki (316)</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>Rural</td>\n",
       "      <td>3</td>\n",
       "      <td>219678</td>\n",
       "      <td>-95092</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  displayFieldName transform__originPosition  transform__scale  \\\n",
       "0          vilname                 upperLeft         38.218514   \n",
       "1              NaN                       NaN         38.218514   \n",
       "2              NaN                       NaN               NaN   \n",
       "3              NaN                       NaN               NaN   \n",
       "4              NaN                       NaN               NaN   \n",
       "\n",
       "   transform__translate fieldAliases__objectid fieldAliases__latitude  \\\n",
       "0             68.501547               objectid               latitude   \n",
       "1             35.032118                    NaN                    NaN   \n",
       "2                   NaN                    NaN                    NaN   \n",
       "3                   NaN                    NaN                    NaN   \n",
       "4                   NaN                    NaN                    NaN   \n",
       "\n",
       "  fieldAliases__longitude fieldAliases__lgd_state_ fieldAliases__lgd_distri  \\\n",
       "0               longitude               lgd_state_               lgd_distri   \n",
       "1                     NaN                      NaN                      NaN   \n",
       "2                     NaN                      NaN                      NaN   \n",
       "3                     NaN                      NaN                      NaN   \n",
       "4                     NaN                      NaN                      NaN   \n",
       "\n",
       "  fieldAliases__pincode fieldAliases__schcd fieldAliases__schname  \\\n",
       "0               Pincode               schcd                School   \n",
       "1                   NaN                 NaN                   NaN   \n",
       "2                   NaN                 NaN                   NaN   \n",
       "3                   NaN                 NaN                   NaN   \n",
       "4                   NaN                 NaN                   NaN   \n",
       "\n",
       "  fieldAliases__schmgt fieldAliases__schcat fieldAliases__schtype  \\\n",
       "0            Mngt Code             Cat_Code             Type Code   \n",
       "1                  NaN                  NaN                   NaN   \n",
       "2                  NaN                  NaN                   NaN   \n",
       "3                  NaN                  NaN                   NaN   \n",
       "4                  NaN                  NaN                   NaN   \n",
       "\n",
       "  fieldAliases__rururb fieldAliases__school_cat fieldAliases__school_typ  \\\n",
       "0          Rural_Urban                 Category              School Type   \n",
       "1                  NaN                      NaN                      NaN   \n",
       "2                  NaN                      NaN                      NaN   \n",
       "3                  NaN                      NaN                      NaN   \n",
       "4                  NaN                      NaN                      NaN   \n",
       "\n",
       "  fieldAliases__stcode11 fieldAliases__dtcode11 fieldAliases__sdtcode11  \\\n",
       "0               stcode11               dtcode11               sdtcode11   \n",
       "1                    NaN                    NaN                     NaN   \n",
       "2                    NaN                    NaN                     NaN   \n",
       "3                    NaN                    NaN                     NaN   \n",
       "4                    NaN                    NaN                     NaN   \n",
       "\n",
       "  fieldAliases__sdtname fieldAliases__subdt_lgd fieldAliases__vilcode11  \\\n",
       "0          Sub-district               subdt_lgd               vilcode11   \n",
       "1                   NaN                     NaN                     NaN   \n",
       "2                   NaN                     NaN                     NaN   \n",
       "3                   NaN                     NaN                     NaN   \n",
       "4                   NaN                     NaN                     NaN   \n",
       "\n",
       "  fieldAliases__stname fieldAliases__dtname fieldAliases__vilname  \\\n",
       "0                State             District               Village   \n",
       "1                  NaN                  NaN                   NaN   \n",
       "2                  NaN                  NaN                   NaN   \n",
       "3                  NaN                  NaN                   NaN   \n",
       "4                  NaN                  NaN                   NaN   \n",
       "\n",
       "  fieldAliases__management fieldAliases__location fieldAliases__udise_stco  \\\n",
       "0               Management               Location               udise_stco   \n",
       "1                      NaN                    NaN                      NaN   \n",
       "2                      NaN                    NaN                      NaN   \n",
       "3                      NaN                    NaN                      NaN   \n",
       "4                      NaN                    NaN                      NaN   \n",
       "\n",
       "        geometryType  spatialReference__wkid  spatialReference__latestWkid  \\\n",
       "0  esriGeometryPoint                102100.0                        3857.0   \n",
       "1                NaN                     NaN                           NaN   \n",
       "2                NaN                     NaN                           NaN   \n",
       "3                NaN                     NaN                           NaN   \n",
       "4                NaN                     NaN                           NaN   \n",
       "\n",
       "  fields__name          fields__type fields__alias  fields__length  \\\n",
       "0     objectid      esriFieldTypeOID      objectid             NaN   \n",
       "1     latitude   esriFieldTypeDouble      latitude             NaN   \n",
       "2    longitude   esriFieldTypeDouble     longitude             NaN   \n",
       "3   lgd_state_  esriFieldTypeInteger    lgd_state_             NaN   \n",
       "4   lgd_distri  esriFieldTypeInteger    lgd_distri             NaN   \n",
       "\n",
       "   features__attributes__objectid  features__attributes__latitude  \\\n",
       "0                          940976                       31.047346   \n",
       "1                          939675                       30.932993   \n",
       "2                          939891                       30.943320   \n",
       "3                          939036                       30.904885   \n",
       "4                          940592                       31.012063   \n",
       "\n",
       "   features__attributes__longitude  features__attributes__lgd_state_  \\\n",
       "0                        75.580788                               3.0   \n",
       "1                        75.556534                               3.0   \n",
       "2                        75.583791                               3.0   \n",
       "3                        75.420281                               3.0   \n",
       "4                        75.420986                               3.0   \n",
       "\n",
       "   features__attributes__lgd_distri  features__attributes__pincode  \\\n",
       "0                              34.0                         144039   \n",
       "1                              36.0                         142025   \n",
       "2                              36.0                         142025   \n",
       "3                              36.0                         142033   \n",
       "4                              34.0                         144041   \n",
       "\n",
       "   features__attributes__schcd features__attributes__schname  \\\n",
       "0                   3040906601                   GPS RAMEWAL   \n",
       "1                   3091104602                  GMS KOT MANA   \n",
       "2                   3091101903      NEW PUNJAB PUBLIC SCHOOL   \n",
       "3                   3091108201                  GPS SODIWALA   \n",
       "4                   3041101901                 GPS GOBINDPUR   \n",
       "\n",
       "   features__attributes__schmgt  features__attributes__schcat  \\\n",
       "0                             1                             1   \n",
       "1                             1                             4   \n",
       "2                             5                             3   \n",
       "3                             1                             1   \n",
       "4                             1                             1   \n",
       "\n",
       "   features__attributes__schtype  features__attributes__rururb  \\\n",
       "0                              3                             1   \n",
       "1                              3                             1   \n",
       "2                              3                             1   \n",
       "3                              3                             1   \n",
       "4                              3                             1   \n",
       "\n",
       "  features__attributes__school_cat features__attributes__school_typ  \\\n",
       "0                          Primary                     Co-education   \n",
       "1               Upper Primary only                     Co-education   \n",
       "2  Pr. with Up.Pr. Sec. and H.Sec.                     Co-education   \n",
       "3                          Primary                     Co-education   \n",
       "4                          Primary                     Co-education   \n",
       "\n",
       "   features__attributes__stcode11  features__attributes__dtcode11  \\\n",
       "0                               3                              37   \n",
       "1                               3                              41   \n",
       "2                               3                              41   \n",
       "3                               3                              41   \n",
       "4                               3                              37   \n",
       "\n",
       "  features__attributes__sdtcode11 features__attributes__sdtname  \\\n",
       "0                             211                      Phillaur   \n",
       "1                             230                       Jagraon   \n",
       "2                             230                       Jagraon   \n",
       "3                             230                       Jagraon   \n",
       "4                             210                       Nakodar   \n",
       "\n",
       "   features__attributes__subdt_lgd  features__attributes__vilcode11  \\\n",
       "0                            211.0                          30056.0   \n",
       "1                            230.0                          33843.0   \n",
       "2                            230.0                          33820.0   \n",
       "3                            230.0                          33872.0   \n",
       "4                            210.0                          29988.0   \n",
       "\n",
       "  features__attributes__stname features__attributes__dtname  \\\n",
       "0                       PUNJAB                    Jalandhar   \n",
       "1                       PUNJAB                     Ludhiana   \n",
       "2                       PUNJAB                     Ludhiana   \n",
       "3                       PUNJAB                     Ludhiana   \n",
       "4                       PUNJAB                    Jalandhar   \n",
       "\n",
       "  features__attributes__vilname features__attributes__management  \\\n",
       "0                  Ramewal (96)          Department of Education   \n",
       "1                 Kot Mana (18)          Department of Education   \n",
       "2                  Bhundri (16)        Pvt. Unaided (Recognized)   \n",
       "3                Sodhiwala (42)          Department of Education   \n",
       "4                  Baloki (316)          Department of Education   \n",
       "\n",
       "  features__attributes__location  features__attributes__udise_stco  \\\n",
       "0                          Rural                                 3   \n",
       "1                          Rural                                 3   \n",
       "2                          Rural                                 3   \n",
       "3                          Rural                                 3   \n",
       "4                          Rural                                 3   \n",
       "\n",
       "   features__geometry__x  features__geometry__y exceededTransferLimit  \n",
       "0                 220143                 -95212                   NaN  \n",
       "1                 220073                 -94824                   NaN  \n",
       "2                 220152                 -94859                   NaN  \n",
       "3                 219676                 -94728                   NaN  \n",
       "4                 219678                 -95092                   NaN  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge df and df_block and remove using UDISE and SCHCD\n",
    "df_master = pd.merge(df, df_block, left_on='features__attributes__schcd', right_on='UDISE', how='inner')\n",
    "#check number of misses for ludhiana"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_master.head()\n",
    "#total number of rows\n",
    "df_master.shape\n",
    "df_master.head()\n",
    "# df master should only have UDISE lat long and school name\n",
    "df_master = df_master[['UDISE', 'features__attributes__latitude', 'features__attributes__longitude', 'School Name']]\n",
    "df_master.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(390, 4)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_master.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_master.head()\n",
    "#export to csv\n",
    "df_master.to_csv('./master.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "dsai",
   "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.10.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}