data_explorer.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from slugify import slugify\n",
    "import json\n",
    "import math\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "def formatINR(number):\n",
    "    s, *d = str(number).partition(\".\")\n",
    "    r = \",\".join([s[x-2:x] for x in range(-3, -len(s), -2)][::-1] + [s[-3:]])\n",
    "    return \"\".join([r] + d)\n",
    "\n",
    "def formatValue(value, unit, type):\n",
    "    if unit == \"percentage\" and type == \"Derived\":\n",
    "        value = value*100\n",
    "    value = round(value, 2) \n",
    "    \n",
    "    if unit == \"number\":\n",
    "        return str(formatINR(int(value)))\n",
    "    elif unit == \"percentage\":\n",
    "        return (str((formatINR(value)))+\"%\")\n",
    "    elif unit == \"rupees\":\n",
    "        return (\"INR \" + str((formatINR(value))))\n",
    "    elif unit == \"rupees lakhs\":\n",
    "        return (\"INR \" + str((formatINR(value))) + \" Lakhs\")\n",
    "    else:\n",
    "        return value\n",
    "    \n",
    "def percValue(value, unit, type):\n",
    "    if unit == \"percentage\" and type == \"Derived\":\n",
    "        value = value*100\n",
    "    value = round(value, 2) \n",
    "    return value\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read data\n",
    "\n",
    "raw_data = pd.read_excel('/home/abhinav/sink/D4D/data.xlsx', engine='openpyxl')\n",
    "indicators = pd.read_excel('/home/abhinav/sink/D4D/Indicators.xlsx', engine='openpyxl')\n",
    "dist_meta_data = {\"morigaon\":{\"Title\": \"Morigaon\", \"About\": \"Morigaon district is an administrative district in the state of Assam in India. The district headquarters is located at Morigaon. The ancient place of occult Mayong is located in this district as well as Pobitora Wildlife Sanctuary.\", \"link\": \"https://morigaon.gov.in/\"}, \"nagaon\":{\"Title\": \"Nagaon\", \"About\": \"Nagaon is an administrative district in the Indian state of Assam. The undivided district was the most populous in the state as per 2011 census. The eastern, western, and southern fringes of the newly organized district were once ruled by different small feudal kings or their agents. It was a newly organized village system-hence called “Nagaon”, Na meaning new.\", \"link\": \"https://nagaon.assam.gov.in/\"} }\n",
    "dept_meta_data = {'panchayat-and-rural-development': {'Title': \"Panchayat and Rural Development\", 'Desc':'The Panchayat & Rural Development Department largely works to enhance livelihood opportunities, address chronic poverty, provides social security and works for economic inclusion of rural poor families. The Panchayat & Rural Development Department has been implementing a number of programmes in the rural area of the State by empowering rural poor through power of rights based law, aiming to transform the geography of poverty in the State.', 'schemes':['mgnrega']}, \n",
    "             'public-health-engineering': {'Title': \"Public Health Engineering\", 'Desc':'The Public Health Engineering Department has a mission to enhance quality of life of the people by ensuring sustainable safe drinking water and sanitation facilities and services along with promoting hygiene practices according to their choices and affordability.', 'schemes':['jjm']}}\n",
    "scheme_meta_data = {\"mgnrega\": {\"Name\": \"Mahatma Gandhi National Rural Employment Guarantee Act (MGNREGA)\", \"Title\": \"MGNREGA\", \"Desc\": \"The objective of MGNREGS is to enhance livelihood security of the rural masses. The scheme is meant to provide at least 100 days of wage employment per financial year (FY) to every rural household whose adult members are willing to do unskilled manual work. This is one of the world’s largest public sector employment programmes that provides guaranteed income through employment.\", \"dept_slug\":\"panchayat-and-rural-development\", \"logo\": \"/logo/mgnrega.webp\"},\n",
    "                    \"jjm\": {\"Name\": \"Jal Jeevan Mission (JJM)\", \"Title\": \"JJM\", \"Desc\": \"The National Rural Drinking Water Programme / Jal Jeevan Mission is an initiative of the Government of India under the Ministry of Jal Shakti. It aims to provide safe and adequate drinking water to all households in rural India through provision of individual household tap connections by the year 2024.\", \"dept_slug\":\"public-health-engineering\", \"logo\": \"/logo/nhm.png\"}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Merge indicator data into data file \n",
    "data = pd.merge(raw_data,indicators[['indicator_slug','indicator_type', 'indicator_selected', 'indicator_overview', 'indicator_explorer', 'indicator_dept', 'indicator_district', 'indicator_unit']],on='indicator_slug', how='left')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>db_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>district_code</th>\n",
       "      <th>block_name</th>\n",
       "      <th>block_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>...</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",
       "      <th>district_slug</th>\n",
       "      <th>block_slug</th>\n",
       "      <th>scheme_slug</th>\n",
       "      <th>dept_slug</th>\n",
       "      <th>dept_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>27--1</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Khagorijan</td>\n",
       "      <td>1</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 active jobcards</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>khagorijan</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>141</th>\n",
       "      <td>27--2</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Batadraba</td>\n",
       "      <td>2</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 active jobcards</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>batadraba</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>142</th>\n",
       "      <td>27--3</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Juria</td>\n",
       "      <td>3</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 active jobcards</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>juria</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>143</th>\n",
       "      <td>27--4</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Kaliabor</td>\n",
       "      <td>4</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 active jobcards</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>kaliabor</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>144</th>\n",
       "      <td>27--5</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Rupahihut</td>\n",
       "      <td>5</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 active jobcards</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>rupahihut</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 × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    db_code district_name  district_code  block_name  block_code  \\\n",
       "140   27--1        Nagaon             27  Khagorijan           1   \n",
       "141   27--2        Nagaon             27   Batadraba           2   \n",
       "142   27--3        Nagaon             27       Juria           3   \n",
       "143   27--4        Nagaon             27    Kaliabor           4   \n",
       "144   27--5        Nagaon             27   Rupahihut           5   \n",
       "\n",
       "    financial_year scheme_name                 indicator_report  \\\n",
       "140      2023-2024     MGNREGA  Category Wise Household Workers   \n",
       "141      2023-2024     MGNREGA  Category Wise Household Workers   \n",
       "142      2023-2024     MGNREGA  Category Wise Household Workers   \n",
       "143      2023-2024     MGNREGA  Category Wise Household Workers   \n",
       "144      2023-2024     MGNREGA  Category Wise Household Workers   \n",
       "\n",
       "    date_of_update         indicator_name  ... indicator_overview  \\\n",
       "140   7-2023-09-34  No of active jobcards  ...                 no   \n",
       "141   7-2023-09-34  No of active jobcards  ...                 no   \n",
       "142   7-2023-09-34  No of active jobcards  ...                 no   \n",
       "143   7-2023-09-34  No of active jobcards  ...                 no   \n",
       "144   7-2023-09-34  No of active jobcards  ...                 no   \n",
       "\n",
       "     indicator_explorer indicator_dept indicator_district  indicator_unit  \\\n",
       "140                 yes             no                yes          number   \n",
       "141                 yes             no                yes          number   \n",
       "142                 yes             no                yes          number   \n",
       "143                 yes             no                yes          number   \n",
       "144                 yes             no                yes          number   \n",
       "\n",
       "     district_slug  block_slug  scheme_slug                        dept_slug  \\\n",
       "140         nagaon  khagorijan      mgnrega  panchayat-and-rural-development   \n",
       "141         nagaon   batadraba      mgnrega  panchayat-and-rural-development   \n",
       "142         nagaon       juria      mgnrega  panchayat-and-rural-development   \n",
       "143         nagaon    kaliabor      mgnrega  panchayat-and-rural-development   \n",
       "144         nagaon   rupahihut      mgnrega  panchayat-and-rural-development   \n",
       "\n",
       "                           dept_name  \n",
       "140  Panchayat and Rural Development  \n",
       "141  Panchayat and Rural Development  \n",
       "142  Panchayat and Rural Development  \n",
       "143  Panchayat and Rural Development  \n",
       "144  Panchayat and Rural Development  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter data for district page indicators and structure the column values\n",
    "data = data[data.indicator_explorer == 'yes']\n",
    "\n",
    "def get_dept_slug(x):\n",
    "    \n",
    "    for key in dept_meta_data:\n",
    "        if x in dept_meta_data[key]['schemes']:\n",
    "            return key\n",
    "        \n",
    "def get_dept_name(x):\n",
    "    \n",
    "    for key in dept_meta_data:\n",
    "        if x in dept_meta_data[key]['schemes']:\n",
    "            return dept_meta_data[key][\"Title\"]      \n",
    "    \n",
    "\n",
    "data['district_name'] = data['district_name'].map(lambda x: x.title())\n",
    "data['district_slug'] = data['district_name'].map(lambda x: slugify(x))\n",
    "data['block_name'] = data['block_name'].map(lambda x: x.title())\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": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>db_code</th>\n",
       "      <th>district_name</th>\n",
       "      <th>district_code</th>\n",
       "      <th>block_name</th>\n",
       "      <th>block_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>...</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",
       "      <th>district_slug</th>\n",
       "      <th>block_slug</th>\n",
       "      <th>scheme_slug</th>\n",
       "      <th>dept_slug</th>\n",
       "      <th>dept_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>140</td>\n",
       "      <td>27--1</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Khagorijan</td>\n",
       "      <td>1</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>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>khagorijan</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>1</th>\n",
       "      <td>141</td>\n",
       "      <td>27--2</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Batadraba</td>\n",
       "      <td>2</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>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>batadraba</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>2</th>\n",
       "      <td>142</td>\n",
       "      <td>27--3</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Juria</td>\n",
       "      <td>3</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>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>juria</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>3</th>\n",
       "      <td>143</td>\n",
       "      <td>27--4</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Kaliabor</td>\n",
       "      <td>4</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>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>kaliabor</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>4</th>\n",
       "      <td>144</td>\n",
       "      <td>27--5</td>\n",
       "      <td>Nagaon</td>\n",
       "      <td>27</td>\n",
       "      <td>Rupahihut</td>\n",
       "      <td>5</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>...</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>number</td>\n",
       "      <td>nagaon</td>\n",
       "      <td>rupahihut</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 × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index db_code district_name  district_code  block_name  block_code  \\\n",
       "0    140   27--1        Nagaon             27  Khagorijan           1   \n",
       "1    141   27--2        Nagaon             27   Batadraba           2   \n",
       "2    142   27--3        Nagaon             27       Juria           3   \n",
       "3    143   27--4        Nagaon             27    Kaliabor           4   \n",
       "4    144   27--5        Nagaon             27   Rupahihut           5   \n",
       "\n",
       "  financial_year scheme_name                 indicator_report date_of_update  \\\n",
       "0      2023-2024     MGNREGA  Category Wise Household Workers   7-2023-09-34   \n",
       "1      2023-2024     MGNREGA  Category Wise Household Workers   7-2023-09-34   \n",
       "2      2023-2024     MGNREGA  Category Wise Household Workers   7-2023-09-34   \n",
       "3      2023-2024     MGNREGA  Category Wise Household Workers   7-2023-09-34   \n",
       "4      2023-2024     MGNREGA  Category Wise Household Workers   7-2023-09-34   \n",
       "\n",
       "   ... indicator_overview indicator_explorer  indicator_dept  \\\n",
       "0  ...                 no                yes              no   \n",
       "1  ...                 no                yes              no   \n",
       "2  ...                 no                yes              no   \n",
       "3  ...                 no                yes              no   \n",
       "4  ...                 no                yes              no   \n",
       "\n",
       "  indicator_district indicator_unit  district_slug  block_slug  scheme_slug  \\\n",
       "0                yes         number         nagaon  khagorijan      mgnrega   \n",
       "1                yes         number         nagaon   batadraba      mgnrega   \n",
       "2                yes         number         nagaon       juria      mgnrega   \n",
       "3                yes         number         nagaon    kaliabor      mgnrega   \n",
       "4                yes         number         nagaon   rupahihut      mgnrega   \n",
       "\n",
       "                         dept_slug                        dept_name  \n",
       "0  panchayat-and-rural-development  Panchayat and Rural Development  \n",
       "1  panchayat-and-rural-development  Panchayat and Rural Development  \n",
       "2  panchayat-and-rural-development  Panchayat and Rural Development  \n",
       "3  panchayat-and-rural-development  Panchayat and Rural Development  \n",
       "4  panchayat-and-rural-development  Panchayat and Rural Development  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.reset_index(inplace=True)\n",
    "data.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create json file for explorer page - for map and bar charts - for morigaon and nagaon\n",
    "\n",
    "filtered_data =  data[data['district_name'].isin([\"Morigaon\", \"Nagaon\"])]\n",
    "\n",
    "base_json = {}\n",
    "\n",
    "for index, row in filtered_data.iterrows():\n",
    "    \n",
    "    if math.isinf(row.indicator_value) or math.isnan(row.indicator_value):\n",
    "        continue \n",
    "    \n",
    "    \n",
    "    # insert district\n",
    "    if  row.district_slug not in base_json:\n",
    "        base_json[row.district_slug] = {\"distTitle\": row.district_name, \"depts\": {row.dept_slug: {\"deptTitle\": row.dept_name,\n",
    "                    \"schemes\": {row.scheme_slug: {\"schemeTitle\": scheme_meta_data[row.scheme_slug]['Name'],\n",
    "                    \"indicators\": {row.indicator_slug: {\"indicatorTitle\":row.indicator_name, \"years\": {row.financial_year: \n",
    "                        {\"bardata\":{[row.block_name]: [percValue(row.indicator_value, row.indicator_unit, row.indicator_report)]}, \"mapdata\":[{\"name\":int(row.BLOCK_LGD) if not math.isnan(row.BLOCK_LGD) else \"Infinity\", \"label\": row.block_name, \"value\": percValue(row.indicator_value, row.indicator_unit, row.indicator_report), \"disp_val\": formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]}\n",
    "                    }}}}} }}}\n",
    "    elif row.dept_slug not in base_json[row.district_slug][\"depts\"]:\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug] = {\"deptTitle\": row.dept_name,\n",
    "                    \"schemes\": {row.scheme_slug: {\"schemeTitle\": scheme_meta_data[row.scheme_slug]['Name'],\n",
    "                    \"indicators\": {row.indicator_slug: {\"indicatorTitle\":row.indicator_name, \"years\": {row.financial_year: \n",
    "                        {\"bardata\":{[row.block_name]: [percValue(row.indicator_value, row.indicator_unit, row.indicator_report)]}, \"mapdata\":[{\"name\":int(row.BLOCK_LGD) if not math.isnan(row.BLOCK_LGD) else \"Infinity\", \"label\": row.block_name, \"value\": percValue(row.indicator_value, row.indicator_unit, row.indicator_report), \"disp_val\": formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]}\n",
    "                    }}}}} }\n",
    "    elif row.scheme_slug not in base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"]:\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug] = {\"schemeTitle\": scheme_meta_data[row.scheme_slug]['Name'],\n",
    "                    \"indicators\": {row.indicator_slug: {\"indicatorTitle\":row.indicator_name, \"years\": {row.financial_year: \n",
    "                        {\"bardata\":{[row.block_name]: [percValue(row.indicator_value, row.indicator_unit, row.indicator_report)]}, \"mapdata\":[{\"name\":int(row.BLOCK_LGD) if not math.isnan(row.BLOCK_LGD) else \"Infinity\", \"label\": row.block_name, \"value\": percValue(row.indicator_value, row.indicator_unit, row.indicator_report), \"disp_val\": formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]}\n",
    "                    }}}}\n",
    "    elif row.indicator_slug not in  base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"indicators\"]:\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"indicators\"][row.indicator_slug] = {\"indicatorTitle\":row.indicator_name, \"years\": {row.financial_year: \n",
    "                        {\"bardata\":{[row.block_name]: [percValue(row.indicator_value, row.indicator_unit, row.indicator_report)]}, \"mapdata\":[{\"name\":int(row.BLOCK_LGD) if not math.isnan(row.BLOCK_LGD) else \"Infinity\", \"label\": row.block_name, \"value\": percValue(row.indicator_value, row.indicator_unit, row.indicator_report), \"disp_val\": formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]}\n",
    "                    }}\n",
    "    elif row.financial_year not in  base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"indicators\"][row.indicator_slug][\"years\"]:\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"indicators\"][row.indicator_slug][\"years\"][row.financial_year] = {\"bardata\":{[row.block_name]: [percValue(row.indicator_value, row.indicator_unit, row.indicator_report)]}, \"mapdata\":[{\"name\":int(row.BLOCK_LGD) if not math.isnan(row.BLOCK_LGD) else \"Infinity\", \"label\": row.block_name, \"value\": percValue(row.indicator_value, row.indicator_unit, row.indicator_report), \"disp_val\": formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]}\n",
    "    else: \n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"indicators\"][row.indicator_slug][\"years\"][row.financial_year][\"bardata\"][\"xAxis\"].append(row.block_name)\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"indicators\"][row.indicator_slug][\"years\"][row.financial_year][\"bardata\"][\"values\"].append(percValue(row.indicator_value, row.indicator_unit, row.indicator_report))\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"indicators\"][row.indicator_slug][\"years\"][row.financial_year][\"mapdata\"].append({\"name\":int(row.BLOCK_LGD) if not math.isnan(row.BLOCK_LGD) else \"Infinity\", \"label\":row.block_name, \"value\": percValue(row.indicator_value, row.indicator_unit, row.indicator_report), \"disp_val\": formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)})              \n",
    "    # if index > 100:\n",
    "    #     break \n",
    "\n",
    "        \n",
    "with open('scheme_explorer_data.json', 'w') as fp:\n",
    "    # json.dump(base_json, fp)\n",
    "    json.dump(base_json, fp, indent=2)\n",
    "    \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create json file for explorer page - for table charts - for morigaon and nagaon\n",
    "\n",
    "filtered_data =  data[data['district_name'].isin([\"Morigaon\", \"Nagaon\"])]\n",
    "\n",
    "base_json = {}\n",
    "\n",
    "for index, row in filtered_data.iterrows():\n",
    "    \n",
    "    if math.isinf(row.indicator_value) or math.isnan(row.indicator_value):\n",
    "        continue \n",
    "    \n",
    "    \n",
    "    # insert district\n",
    "    if  row.district_slug not in base_json:\n",
    "        base_json[row.district_slug] = {\"distTitle\": row.district_name, \"depts\": {row.dept_slug: {\"deptTitle\": row.dept_name,\n",
    "                    \"schemes\": {row.scheme_slug: {\"schemeTitle\": scheme_meta_data[row.scheme_slug]['Name'],\n",
    "                    \"table_data\": {row.financial_year:[{\"Block\": row.block_name, row.indicator_name: formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]}}}}}}\n",
    "    elif row.dept_slug not in base_json[row.district_slug][\"depts\"]:\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug] = {\"deptTitle\": row.dept_name,\n",
    "                    \"schemes\": {row.scheme_slug: {\"schemeTitle\": scheme_meta_data[row.scheme_slug]['Name'],\n",
    "                    \"table_data\": {row.financial_year:[{\"Block\": row.block_name, row.indicator_name: formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]}}}}\n",
    "    elif row.scheme_slug not in base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"]:\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug] =  {\"schemeTitle\": scheme_meta_data[row.scheme_slug]['Name'],\n",
    "                    \"table_data\": {row.financial_year:[{\"Block\": row.block_name, row.indicator_name: formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]}}\n",
    "    elif row.financial_year not in  base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"table_data\"]:\n",
    "        base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"table_data\"][row.financial_year] = [{\"Block\": row.block_name, row.indicator_name: formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)}]\n",
    "    else:\n",
    "        i = 0 \n",
    "        while i <  len(base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"table_data\"][row.financial_year]):\n",
    "            if base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"table_data\"][row.financial_year][i]['Block'] == row.block_name :\n",
    "                base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"table_data\"][row.financial_year][i][row.indicator_name]= formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)\n",
    "                break\n",
    "            i+=1\n",
    "        else:\n",
    "            base_json[row.district_slug][\"depts\"][row.dept_slug][\"schemes\"][row.scheme_slug][\"table_data\"][row.financial_year].append({\"Block\": row.block_name, row.indicator_name: formatValue(row.indicator_value, row.indicator_unit, row.indicator_report)})  \n",
    "        \n",
    "    # if index > 100:\n",
    "    #     break \n",
    "\n",
    "        \n",
    "with open('scheme_explorer_table_data.json', 'w') as fp:\n",
    "    # json.dump(base_json, fp)\n",
    "    json.dump(base_json, fp, indent=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# bar data structure\n",
    "data: {\n",
    "xAxis: [],\n",
    "values: []\n",
    "}\n",
    "\n",
    "\n",
    "# map data structure\n",
    "\n",
    "[\n",
    "{\n",
    "    name: 'blockName1',\n",
    "    value: 20\n",
    "  },\n",
    "{\n",
    "      name: 'blockName2',\n",
    "    value: 30\n",
    "  }\n",
    "]\n",
    "\n",
    "\n",
    "# table data structure\n",
    "[{\n",
    "    firstName: 'Tanner',\n",
    "    lastName: 'Linsley',\n",
    "    age: 24,\n",
    "    visits: 100,\n",
    "    status: 'In Relationship',\n",
    "    progress: 50,\n",
    "  },\n",
    "  {\n",
    "    firstName: 'Tandy',\n",
    "    lastName: 'Miller',\n",
    "    age: 40,\n",
    "    visits: 40,\n",
    "    status: 'Single',\n",
    "    progress: 80,\n",
    "  },]"
   ]
  }
 ],
 "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.12.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}