data_main.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from slugify import slugify\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_data = pd.read_excel('/home/abhinav/sink/D4D/data_master.xlsx', engine='openpyxl', sheet_name=\"data\")\n",
    "indicators = pd.read_excel('/home/abhinav/sink/D4D/data_master.xlsx', engine='openpyxl', sheet_name=\"indicators\")\n",
    "dist_meta_data = {\"morigaon\":{\"Title\": \"Morigaon\", \"About\": \"Morigaon Dist Description\"}}\n",
    "dept_meta_data = {'panchayat-and-rural-development': {'Title': \"Panchayat and Rural Development\", 'Desc':'Department Description', 'schemes':['mgnrega']}, \n",
    "             'public-health-engineering': {'Title': \"Public Health Engineering\", 'Desc':'Department Description', 'schemes':['jjm']}}\n",
    "scheme_meta_data = {\"mgnrega\": {\"Name\": \"Mahatma Gandhi National Rural Employment Guarantee Act (MGNREGA)\", \"Title\": \"MGNREGA\", \"Desc\": \"Desc of scheme\", \"dept_slug\":\"panchayat-and-rural-development\", \"logo\": \"/logo/mgnrega.webp\"},\n",
    "                    \"jjm\": {\"Name\": \"Jal Jeevan Mission (JJM)\", \"Title\": \"JJM\", \"Desc\": \"Desc of scheme\", \"dept_slug\":\"public-health-engineering\", \"logo\": \"/logo/nhm.png\"}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>scheme_name</th>\n",
       "      <th>indicator_report</th>\n",
       "      <th>indicator_name</th>\n",
       "      <th>indicator_slug</th>\n",
       "      <th>indicator_type</th>\n",
       "      <th>indicator_name_short</th>\n",
       "      <th>indicator_selected</th>\n",
       "      <th>indicator_overview</th>\n",
       "      <th>indicator_explorer</th>\n",
       "      <th>indicator_dept</th>\n",
       "      <th>indicator_district</th>\n",
       "      <th>indicator_unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>No of jobcards applied for</td>\n",
       "      <td>nojaf</td>\n",
       "      <td>district_profile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>number</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>No of jobcards issued</td>\n",
       "      <td>nojid</td>\n",
       "      <td>district_profile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>number</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>No of registered SC workers</td>\n",
       "      <td>nscws</td>\n",
       "      <td>district_profile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>number</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>No of registered ST workers</td>\n",
       "      <td>norsw</td>\n",
       "      <td>district_profile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>number</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>No of registered non-SC, non-ST workers</td>\n",
       "      <td>norns</td>\n",
       "      <td>district_profile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>number</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Derived</td>\n",
       "      <td>Share of persondays generated for women</td>\n",
       "      <td>spgfw</td>\n",
       "      <td>district_performance</td>\n",
       "      <td>Share of persondays generated for women</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>percentage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Derived</td>\n",
       "      <td>Cumulative Expenditure on Unskilled wage as a ...</td>\n",
       "      <td>cussw</td>\n",
       "      <td>district_performance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>percentage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Derived</td>\n",
       "      <td>Cumulative Expenditure on Unskilled wage as a ...</td>\n",
       "      <td>cuswm</td>\n",
       "      <td>district_performance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>percentage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Derived</td>\n",
       "      <td>Cumulative Expenditure on materials as a share...</td>\n",
       "      <td>cmsum</td>\n",
       "      <td>district_performance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>percentage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Derived</td>\n",
       "      <td>Cumulative Expenditure on Semi-skiilled and sk...</td>\n",
       "      <td>csswa</td>\n",
       "      <td>district_performance</td>\n",
       "      <td>NaN</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>percentage</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>186 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    scheme_name                 indicator_report  \\\n",
       "0       MGNREGA  Category Wise Household Workers   \n",
       "1       MGNREGA  Category Wise Household Workers   \n",
       "2       MGNREGA  Category Wise Household Workers   \n",
       "3       MGNREGA  Category Wise Household Workers   \n",
       "4       MGNREGA  Category Wise Household Workers   \n",
       "..          ...                              ...   \n",
       "181     MGNREGA                          Derived   \n",
       "182     MGNREGA                          Derived   \n",
       "183     MGNREGA                          Derived   \n",
       "184     MGNREGA                          Derived   \n",
       "185     MGNREGA                          Derived   \n",
       "\n",
       "                                        indicator_name indicator_slug  \\\n",
       "0                           No of jobcards applied for          nojaf   \n",
       "1                                No of jobcards issued          nojid   \n",
       "2                          No of registered SC workers          nscws   \n",
       "3                          No of registered ST workers          norsw   \n",
       "4              No of registered non-SC, non-ST workers          norns   \n",
       "..                                                 ...            ...   \n",
       "181            Share of persondays generated for women          spgfw   \n",
       "182  Cumulative Expenditure on Unskilled wage as a ...          cussw   \n",
       "183  Cumulative Expenditure on Unskilled wage as a ...          cuswm   \n",
       "184  Cumulative Expenditure on materials as a share...          cmsum   \n",
       "185  Cumulative Expenditure on Semi-skiilled and sk...          csswa   \n",
       "\n",
       "           indicator_type                     indicator_name_short  \\\n",
       "0        district_profile                                      NaN   \n",
       "1        district_profile                                      NaN   \n",
       "2        district_profile                                      NaN   \n",
       "3        district_profile                                      NaN   \n",
       "4        district_profile                                      NaN   \n",
       "..                    ...                                      ...   \n",
       "181  district_performance  Share of persondays generated for women   \n",
       "182  district_performance                                      NaN   \n",
       "183  district_performance                                      NaN   \n",
       "184  district_performance                                      NaN   \n",
       "185  district_performance                                      NaN   \n",
       "\n",
       "    indicator_selected indicator_overview indicator_explorer indicator_dept  \\\n",
       "0                   no                 no                 no             no   \n",
       "1                   no                 no                 no             no   \n",
       "2                   no                 no                 no             no   \n",
       "3                   no                 no                 no             no   \n",
       "4                   no                 no                 no             no   \n",
       "..                 ...                ...                ...            ...   \n",
       "181                yes                yes                yes             no   \n",
       "182                yes                 no                yes             no   \n",
       "183                yes                 no                yes             no   \n",
       "184                yes                 no                yes             no   \n",
       "185                yes                 no                yes             no   \n",
       "\n",
       "    indicator_district indicator_unit  \n",
       "0                   no         number  \n",
       "1                   no         number  \n",
       "2                   no         number  \n",
       "3                   no         number  \n",
       "4                   no         number  \n",
       "..                 ...            ...  \n",
       "181                 no     percentage  \n",
       "182                 no     percentage  \n",
       "183                 no     percentage  \n",
       "184                 no     percentage  \n",
       "185                 no     percentage  \n",
       "\n",
       "[186 rows x 12 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indicators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/abhinav/pyprojenv/lib/python3.6/site-packages/ipykernel_launcher.py:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "/home/abhinav/pyprojenv/lib/python3.6/site-packages/ipykernel_launcher.py:29: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\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>block_name</th>\n",
       "      <th>block_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>district_code</th>\n",
       "      <th>financial_year</th>\n",
       "      <th>scheme_name</th>\n",
       "      <th>indicator_report</th>\n",
       "      <th>date_of_update</th>\n",
       "      <th>indicator_name</th>\n",
       "      <th>indicator_slug</th>\n",
       "      <th>...</th>\n",
       "      <th>indicator_unit</th>\n",
       "      <th>dist_page</th>\n",
       "      <th>dept_page</th>\n",
       "      <th>narrative_page</th>\n",
       "      <th>explorer_page</th>\n",
       "      <th>district_slug</th>\n",
       "      <th>block_slug</th>\n",
       "      <th>scheme_slug</th>\n",
       "      <th>dept_name</th>\n",
       "      <th>dept_slug</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1912</th>\n",
       "      <td>Tamulpur</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Tamulpur</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2023-2024</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>7-2023-09-34</td>\n",
       "      <td>No of SC workers with active jobcards</td>\n",
       "      <td>nscjc</td>\n",
       "      <td>...</td>\n",
       "      <td>number</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>tamulpur</td>\n",
       "      <td>tamulpur</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1913</th>\n",
       "      <td>Nagrijuli</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Tamulpur</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2023-2024</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>7-2023-09-34</td>\n",
       "      <td>No of SC workers with active jobcards</td>\n",
       "      <td>nscjc</td>\n",
       "      <td>...</td>\n",
       "      <td>number</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>tamulpur</td>\n",
       "      <td>nagrijuli</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1914</th>\n",
       "      <td>Goreswar</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Tamulpur</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2023-2024</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>7-2023-09-34</td>\n",
       "      <td>No of SC workers with active jobcards</td>\n",
       "      <td>nscjc</td>\n",
       "      <td>...</td>\n",
       "      <td>number</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>tamulpur</td>\n",
       "      <td>goreswar</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1915</th>\n",
       "      <td>Kaliapani</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Jorhat</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2023-2024</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>7-2023-09-33</td>\n",
       "      <td>No of SC workers with active jobcards</td>\n",
       "      <td>nscjc</td>\n",
       "      <td>...</td>\n",
       "      <td>number</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>jorhat</td>\n",
       "      <td>kaliapani</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1916</th>\n",
       "      <td>Jorhat East</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Jorhat</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2023-2024</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>Category Wise Household Workers</td>\n",
       "      <td>7-2023-09-33</td>\n",
       "      <td>No of SC workers with active jobcards</td>\n",
       "      <td>nscjc</td>\n",
       "      <td>...</td>\n",
       "      <td>number</td>\n",
       "      <td>no</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>jorhat</td>\n",
       "      <td>jorhat-east</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       block_name  block_code district_name  district_code financial_year  \\\n",
       "1912     Tamulpur         1.0      Tamulpur           32.0      2023-2024   \n",
       "1913    Nagrijuli         2.0      Tamulpur           32.0      2023-2024   \n",
       "1914     Goreswar         3.0      Tamulpur           32.0      2023-2024   \n",
       "1915    Kaliapani         1.0        Jorhat           18.0      2023-2024   \n",
       "1916  Jorhat East         2.0        Jorhat           18.0      2023-2024   \n",
       "\n",
       "     scheme_name                 indicator_report date_of_update  \\\n",
       "1912     MGNREGA  Category Wise Household Workers   7-2023-09-34   \n",
       "1913     MGNREGA  Category Wise Household Workers   7-2023-09-34   \n",
       "1914     MGNREGA  Category Wise Household Workers   7-2023-09-34   \n",
       "1915     MGNREGA  Category Wise Household Workers   7-2023-09-33   \n",
       "1916     MGNREGA  Category Wise Household Workers   7-2023-09-33   \n",
       "\n",
       "                             indicator_name indicator_slug  ...  \\\n",
       "1912  No of SC workers with active jobcards          nscjc  ...   \n",
       "1913  No of SC workers with active jobcards          nscjc  ...   \n",
       "1914  No of SC workers with active jobcards          nscjc  ...   \n",
       "1915  No of SC workers with active jobcards          nscjc  ...   \n",
       "1916  No of SC workers with active jobcards          nscjc  ...   \n",
       "\n",
       "      indicator_unit dist_page dept_page narrative_page explorer_page  \\\n",
       "1912          number        no        no            yes           yes   \n",
       "1913          number        no        no            yes           yes   \n",
       "1914          number        no        no            yes           yes   \n",
       "1915          number        no        no            yes           yes   \n",
       "1916          number        no        no            yes           yes   \n",
       "\n",
       "     district_slug   block_slug scheme_slug                        dept_name  \\\n",
       "1912      tamulpur     tamulpur     mgnrega  Panchayat and Rural Development   \n",
       "1913      tamulpur    nagrijuli     mgnrega  Panchayat and Rural Development   \n",
       "1914      tamulpur     goreswar     mgnrega  Panchayat and Rural Development   \n",
       "1915        jorhat    kaliapani     mgnrega  Panchayat and Rural Development   \n",
       "1916        jorhat  jorhat-east     mgnrega  Panchayat and Rural Development   \n",
       "\n",
       "                            dept_slug  \n",
       "1912  panchayat-and-rural-development  \n",
       "1913  panchayat-and-rural-development  \n",
       "1914  panchayat-and-rural-development  \n",
       "1915  panchayat-and-rural-development  \n",
       "1916  panchayat-and-rural-development  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merging the data and indicator files to get required rows\n",
    "data = raw_data[raw_data.indicator_selected == 'yes']\n",
    "\n",
    "def get_dept_slug(x):\n",
    "    \n",
    "    for key in dept_data:\n",
    "        if x in dept_data[key]['schemes']:\n",
    "            return key\n",
    "        \n",
    "def get_dept_name(x):\n",
    "    \n",
    "    for key in dept_data:\n",
    "        if x in dept_data[key]['schemes']:\n",
    "            return dept_data[key][\"Title\"]      \n",
    "    \n",
    "\n",
    "\n",
    "data['dist_page'] = data['indicator_slug'].map(lambda x: \"yes\" if x in ['npdgn', 'nobaw', 'tcexp'] else \"no\")\n",
    "data['dept_page'] = data['indicator_slug'].map(lambda x: \"yes\" if x in ['npdgn', 'nobaw', 'tcexp'] else \"no\")\n",
    "data['narrative_page'] = data['indicator_slug'].map(lambda x: \"yes\" if x in ['npdgn', 'nobaw', 'notww', 'nscjc', 'nopoe', 'nopwa'] else \"no\")\n",
    "data['explorer_page'] = data['indicator_slug'].map(lambda x: \"yes\")\n",
    "\n",
    "data['district_name'] = data['district_name'].map(lambda x: x.title())\n",
    "data['block_name'] = data['block_name'].map(lambda x: x.title())\n",
    "data['district_slug'] = data['district_name'].map(lambda x: slugify(x))\n",
    "data['block_slug'] = data['block_name'].map(lambda x: slugify(x))\n",
    "data['scheme_slug'] = data['scheme_name'].map(lambda x: slugify(x))\n",
    "\n",
    "data['dept_slug'] = data['scheme_slug'].map(lambda x: get_dept_slug(x))\n",
    "data['dept_name'] = data['scheme_slug'].map(lambda x: get_dept_name(x))\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/abhinav/pyprojenv/lib/python3.6/site-packages/ipykernel_launcher.py:5: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  \"\"\"\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>district_name</th>\n",
       "      <th>district_slug</th>\n",
       "      <th>notww</th>\n",
       "      <th>pdcpd</th>\n",
       "      <th>aeaaw</th>\n",
       "      <th>ppcgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bajali</td>\n",
       "      <td>bajali</td>\n",
       "      <td>75195.0</td>\n",
       "      <td>20.55</td>\n",
       "      <td>63.76</td>\n",
       "      <td>86.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Baksa</td>\n",
       "      <td>baksa</td>\n",
       "      <td>121904.0</td>\n",
       "      <td>53.79</td>\n",
       "      <td>58.48</td>\n",
       "      <td>86.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barpeta</td>\n",
       "      <td>barpeta</td>\n",
       "      <td>323601.0</td>\n",
       "      <td>139.00</td>\n",
       "      <td>70.06</td>\n",
       "      <td>83.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Biswanath</td>\n",
       "      <td>biswanath</td>\n",
       "      <td>176750.0</td>\n",
       "      <td>28.13</td>\n",
       "      <td>45.95</td>\n",
       "      <td>88.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bongaigaon</td>\n",
       "      <td>bongaigaon</td>\n",
       "      <td>167207.0</td>\n",
       "      <td>31.69</td>\n",
       "      <td>42.16</td>\n",
       "      <td>73.22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  district_name district_slug     notww   pdcpd  aeaaw  ppcgt\n",
       "0        Bajali        bajali   75195.0   20.55  63.76  86.77\n",
       "1         Baksa         baksa  121904.0   53.79  58.48  86.78\n",
       "2       Barpeta       barpeta  323601.0  139.00  70.06  83.59\n",
       "3     Biswanath     biswanath  176750.0   28.13  45.95  88.99\n",
       "4    Bongaigaon    bongaigaon  167207.0   31.69  42.16  73.22"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate district aggregate values for district page \n",
    "def f(x):\n",
    "    d = {}\n",
    "    d['notww'] = x[x['indicator_slug'] == 'notww']['indicator_value'].sum()\n",
    "    d['pdcpd'] = round(100 * (x[x['indicator_slug'] == 'npdgn']['indicator_value'].sum() / x[x['indicator_slug'] == 'cpmar']['indicator_value'].sum()), 2)\n",
    "    d['aeaaw'] = round(100 * (x[x['indicator_slug'] == 'nobaw']['indicator_value'].sum() / x[x['indicator_slug'] == 'notww']['indicator_value'].sum()), 2)\n",
    "    d['ppcgt'] = round(100 * (x[x['indicator_slug'] == 'nwgs3']['indicator_value'].sum() / x[x['indicator_slug'] == 'tnws3']['indicator_value'].sum()), 2)\n",
    "\n",
    "    return pd.Series(d, index=['notww', 'pdcpd', 'aeaaw', 'ppcgt']) \n",
    "\n",
    "dist_data = data.groupby(['district_name', 'district_slug']).apply(f).reset_index()\n",
    "dist_data.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# District Page Generator\n",
    "\n",
    "base_json = {}\n",
    "\n",
    "for index, row in dist_data.iterrows():\n",
    "    \n",
    "    # insert district\n",
    "    if  row.district_slug not in base_json:\n",
    "        base_json[row.district_slug] = {\n",
    "            \"title\": row.district_name,\n",
    "            \"href\": row.district_slug,\n",
    "            \"collapsible\": {\n",
    "            \"title\": \"District Highlights & Information\",\n",
    "            \"content\": {\n",
    "                \"cards\": [\n",
    "                    {\n",
    "                        \"value\": str(row.notww),\n",
    "                        \"label\": \"Number of workers with active job cards\",\n",
    "                        \"color\": \"highlight\"\n",
    "                    },\n",
    "                    {\n",
    "                        \"value\": str(row.pdcpd) + \" %\", #\"₹\" + \n",
    "                        \"label\": \"Person Days Generated as a share of Cumulative Projection of Person Days\",\n",
    "                        \"color\": \"highlight\"\n",
    "                    },\n",
    "                    {\n",
    "                        \"value\": str(row.aeaaw) + \" %\", #\"₹\" + \n",
    "                        \"label\": \"ABPS Enabled Accounts as a share of Total Active Workers\",\n",
    "                        \"color\": \"highlight\"\n",
    "                    },\n",
    "                    {\n",
    "                        \"value\": str(row.ppcgt) + \" %\", #\"₹\" + \n",
    "                        \"label\": \"Percentage of Projects with completed geotagging\",\n",
    "                        \"color\": \"highlight\"\n",
    "                    }\n",
    "                ],\n",
    "                \"rightTitle\": \"About \" + row.district_name,\n",
    "                \"description\": [dist_meta_data[row.district_slug][\"About\"] if row.district_slug in dist_meta_data else row.district_name]\n",
    "            }\n",
    "            },\n",
    "            \"listTitle\": \"Browse Line Departments\",\n",
    "            \"list\": [\n",
    "            {\n",
    "                \"label\": \"Panchayat & Rural Development\",\n",
    "                \"href\": \"panchayat-and-rural-development\"\n",
    "            },\n",
    "            {\n",
    "                \"label\": \"Public Health Engineering\",\n",
    "                \"href\": \"public-health-engineering\"\n",
    "            }\n",
    "            ]\n",
    "            }\n",
    "            \n",
    "    else:\n",
    "        pass\n",
    "        \n",
    "with open('dist_data.json', 'w') as fp:\n",
    "    json.dump(base_json, fp, indent=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/abhinav/pyprojenv/lib/python3.6/site-packages/ipykernel_launcher.py:6: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  \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>district_name</th>\n",
       "      <th>district_slug</th>\n",
       "      <th>dept_name</th>\n",
       "      <th>dept_slug</th>\n",
       "      <th>scheme_name</th>\n",
       "      <th>scheme_slug</th>\n",
       "      <th>last_updated</th>\n",
       "      <th>notww</th>\n",
       "      <th>pdcpd</th>\n",
       "      <th>aeaaw</th>\n",
       "      <th>notwwjjm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bajali</td>\n",
       "      <td>bajali</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>7-2023-09-31</td>\n",
       "      <td>75195.0</td>\n",
       "      <td>20.55</td>\n",
       "      <td>63.76</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Baksa</td>\n",
       "      <td>baksa</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>7-2023-09-31</td>\n",
       "      <td>121904.0</td>\n",
       "      <td>53.79</td>\n",
       "      <td>58.48</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barpeta</td>\n",
       "      <td>barpeta</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>7-2023-09-31</td>\n",
       "      <td>323601.0</td>\n",
       "      <td>139.00</td>\n",
       "      <td>70.06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Biswanath</td>\n",
       "      <td>biswanath</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>7-2023-09-31</td>\n",
       "      <td>176750.0</td>\n",
       "      <td>28.13</td>\n",
       "      <td>45.95</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bongaigaon</td>\n",
       "      <td>bongaigaon</td>\n",
       "      <td>Panchayat and Rural Development</td>\n",
       "      <td>panchayat-and-rural-development</td>\n",
       "      <td>MGNREGA</td>\n",
       "      <td>mgnrega</td>\n",
       "      <td>7-2023-09-31</td>\n",
       "      <td>167207.0</td>\n",
       "      <td>31.69</td>\n",
       "      <td>42.16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  district_name district_slug                        dept_name  \\\n",
       "0        Bajali        bajali  Panchayat and Rural Development   \n",
       "1         Baksa         baksa  Panchayat and Rural Development   \n",
       "2       Barpeta       barpeta  Panchayat and Rural Development   \n",
       "3     Biswanath     biswanath  Panchayat and Rural Development   \n",
       "4    Bongaigaon    bongaigaon  Panchayat and Rural Development   \n",
       "\n",
       "                         dept_slug scheme_name scheme_slug  last_updated  \\\n",
       "0  panchayat-and-rural-development     MGNREGA     mgnrega  7-2023-09-31   \n",
       "1  panchayat-and-rural-development     MGNREGA     mgnrega  7-2023-09-31   \n",
       "2  panchayat-and-rural-development     MGNREGA     mgnrega  7-2023-09-31   \n",
       "3  panchayat-and-rural-development     MGNREGA     mgnrega  7-2023-09-31   \n",
       "4  panchayat-and-rural-development     MGNREGA     mgnrega  7-2023-09-31   \n",
       "\n",
       "      notww   pdcpd  aeaaw  notwwjjm  \n",
       "0   75195.0   20.55  63.76       NaN  \n",
       "1  121904.0   53.79  58.48       NaN  \n",
       "2  323601.0  139.00  70.06       NaN  \n",
       "3  176750.0   28.13  45.95       NaN  \n",
       "4  167207.0   31.69  42.16       NaN  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate district aggregate values for schemes on department page \n",
    "def f(x):\n",
    "    d = {}\n",
    "    if x.iloc[0]['scheme_slug'] == \"mgnrega\":\n",
    "        d['notww'] = x[x['indicator_slug'] == 'notww']['indicator_value'].sum()\n",
    "        d['pdcpd'] = round(100 * (x[x['indicator_slug'] == 'npdgn']['indicator_value'].sum() / x[x['indicator_slug'] == 'cpmar']['indicator_value'].sum()), 2)\n",
    "        d['aeaaw'] = round(100 * (x[x['indicator_slug'] == 'nobaw']['indicator_value'].sum() / x[x['indicator_slug'] == 'notww']['indicator_value'].sum()), 2)\n",
    "    elif x.iloc[0]['scheme_slug'] == \"jjm\":\n",
    "        d['notwwjjm'] = x[x['indicator_slug'] == 'notww']['indicator_value'].sum()\n",
    "    else:\n",
    "        pass\n",
    "    \n",
    "    d['last_updated'] = x.iloc[0]['date_of_update']\n",
    "        \n",
    "    return pd.Series(d, index=['last_updated','notww', 'pdcpd', 'aeaaw', 'notwwjjm']) \n",
    "\n",
    "dept_data = data.groupby(['district_name', 'district_slug', 'dept_name', 'dept_slug', 'scheme_name', 'scheme_slug']).apply(f).reset_index()\n",
    "dept_data.head()\n",
    "\n",
    "\n",
    "#Test data cration\n",
    "# remove in production \n",
    "dept_data = dept_data.append(dept_data.iloc[0], ignore_index=True)\n",
    "dept_data['dept_slug'].iloc[35]  = \"public-health-engineering\"\n",
    "dept_data['scheme_slug'].iloc[35]  = \"jjm\"\n",
    "dept_data['notwwjjm'].iloc[35]  = 500"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dept Page Generator\n",
    "\n",
    "base_json = {}\n",
    "\n",
    "for index, row in dept_data.iterrows():\n",
    "    \n",
    "    # insert district\n",
    "    if  row.district_slug not in base_json:\n",
    "        base_json[row.district_slug] = {\n",
    "        \"distTitle\": row.district_name,\n",
    "        \"depts\" :\n",
    "            {\n",
    "                row.dept_slug: \n",
    "                   {\n",
    "                        \"deptTitle\": row.dept_name,\n",
    "                        \"collapsible\": {\n",
    "                        \"title\": \"Department Information\",\n",
    "                        \"content\": [\n",
    "                            dept_meta_data[row.dept_slug][\"Desc\"] if row.dept_slug in dept_meta_data else \"Dummy Dept Desc\"\n",
    "                            ]\n",
    "                        },\n",
    "                        \"listTitle\": \"Browse Schemes and Surveys\",\n",
    "                        \"list\": [\n",
    "                        {\n",
    "                            \"label\": scheme_meta_data[row.scheme_slug][\"Name\"],\n",
    "                            \"href\": row.scheme_slug,\n",
    "                            \"image\": scheme_meta_data[row.scheme_slug][\"logo\"],\n",
    "                            \"lastUpdated\": row.last_updated,\n",
    "                            \"cards\": [\n",
    "                            {\n",
    "                                \"value\": (\"INR \" + str(row.notww)) if row.scheme_slug == \"mgnrega\" else row.notwwjjm,\n",
    "                                \"label\": \"Average Daily Wage\" if row.scheme_slug == \"mgnrega\" else \"JJM label\"\n",
    "                            },\n",
    "                            {\n",
    "                                \"value\": (\"INR \" + str(row.pdcpd))  if row.scheme_slug == \"mgnrega\" else row.notwwjjm,\n",
    "                                \"label\": \"Cost / PersonDay\" if row.scheme_slug == \"mgnrega\" else \"JJM label\"\n",
    "                            },\n",
    "                            {\n",
    "                                \"value\": (row.aeaaw)  if row.scheme_slug == \"mgnrega\" else row.notwwjjm,\n",
    "                                \"label\":\"Person Days Generated\" if row.scheme_slug == \"mgnrega\" else \"JJM label\",\n",
    "                                \"type\" : \"progress\"\n",
    "                            }\n",
    "                            ]\n",
    "                        },\n",
    "                        ]\n",
    "                    },\n",
    "            }\n",
    "            \n",
    "        }\n",
    "\n",
    "            \n",
    "    elif  row.dept_slug not in base_json[row.district_slug][\"depts\"]:\n",
    "        \n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug] =  {\n",
    "                        \"deptTitle\": row.dept_name,\n",
    "                        \"collapsible\": {\n",
    "                        \"title\": \"Department Information\",\n",
    "                        \"content\": [\n",
    "                            dept_meta_data[row.dept_slug][\"Desc\"] if row.dept_slug in dept_meta_data else \"Dummy Dept Desc\"\n",
    "                            ]\n",
    "                        },\n",
    "                        \"listTitle\": \"Browse Schemes and Surveys\",\n",
    "                        \"list\": [\n",
    "                        {\n",
    "                            \"label\": scheme_meta_data[row.scheme_slug][\"Name\"],\n",
    "                            \"href\": row.scheme_slug,\n",
    "                            \"image\": scheme_meta_data[row.scheme_slug][\"logo\"],\n",
    "                            \"lastUpdated\": row.last_updated,\n",
    "                            \"cards\": [\n",
    "                            {\n",
    "                                \"value\": (\"INR \" + str(row.notww)) if row.scheme_slug == \"mgnrega\" else row.notwwjjm,\n",
    "                                \"label\": \"Average Daily Wage\" if row.scheme_slug == \"mgnrega\" else \"JJM label\"\n",
    "                            },\n",
    "                            {\n",
    "                                \"value\": (\"INR \" + str(row.pdcpd))  if row.scheme_slug == \"mgnrega\" else row.notwwjjm,\n",
    "                                \"label\": \"Cost / PersonDay\" if row.scheme_slug == \"mgnrega\" else \"JJM label\"\n",
    "                            },\n",
    "                            {\n",
    "                                \"value\": (row.aeaaw)  if row.scheme_slug == \"mgnrega\" else row.notwwjjm,\n",
    "                                \"label\":\"Person Days Generated\" if row.scheme_slug == \"mgnrega\" else \"JJM label\",\n",
    "                                \"type\" : \"progress\"\n",
    "                            }\n",
    "                            ]\n",
    "                        },\n",
    "                        ]\n",
    "                    }\n",
    "    else:\n",
    "        pass\n",
    "\n",
    "        \n",
    "with open('dept_data.json', 'w') as fp:\n",
    "    json.dump(base_json, fp, indent=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "opub_back_env",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}