Research_Question_1/Table-3,4_pollution-in-IG-Plain_and_Delhi-time-series.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "29f4dfcb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('max_colwidth', 100)\n",
    "from pandas import json_normalize\n",
    "import numpy as np\n",
    "import os\n",
    "import csv\n",
    "import multiprocessing as mp\n",
    "import re\n",
    "from datetime import datetime\n",
    "# import helper as h\n",
    "# import AQDataHelper as aqh\n",
    "from pathlib import Path\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.dates import DateFormatter\n",
    "import matplotlib.dates as mdates\n",
    "import matplotlib\n",
    "import geopy\n",
    "\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1c885953",
   "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>media</th>\n",
       "      <th>date</th>\n",
       "      <th>url</th>\n",
       "      <th>heading</th>\n",
       "      <th>content</th>\n",
       "      <th>other.author</th>\n",
       "      <th>other.top_image</th>\n",
       "      <th>other.category</th>\n",
       "      <th>city</th>\n",
       "      <th>year</th>\n",
       "      <th>state</th>\n",
       "      <th>district</th>\n",
       "      <th>matches</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TOI</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>http://timesofindia.indiatimes.com//city/mumbai/Mumbai-celebrates-a-cool-New-Years-Eve-at-13-4C/...</td>\n",
       "      <td>Mumbai celebrates a cool New Year's Eve at 13.4फ्C</td>\n",
       "      <td>MUMBAI: The city witnessed its lowest temperature of the season on New Year ’s Eve when the merc...</td>\n",
       "      <td>TNN /  Jan 1, 2010, 01:47 IST</td>\n",
       "      <td>https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>maharashtra</td>\n",
       "      <td>mumbai suburban</td>\n",
       "      <td>{'smog': 3.0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TOI</td>\n",
       "      <td>2010-01-02</td>\n",
       "      <td>http://timesofindia.indiatimes.com//city/lucknow/Dense-fog-leads-to-tripping-of-power-lines/arti...</td>\n",
       "      <td>Dense fog leads to tripping of power lines</td>\n",
       "      <td>LUCKNOW: The UP Power Corporation Limited is keeping its fingers crossed for Saturday with the s...</td>\n",
       "      <td>TNN /  Jan 2, 2010, 06:40 IST</td>\n",
       "      <td>https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>lucknow</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>uttar pradesh</td>\n",
       "      <td>lucknow</td>\n",
       "      <td>{'air pollutants': 2.0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TOI</td>\n",
       "      <td>2010-01-09</td>\n",
       "      <td>http://timesofindia.indiatimes.com//city/chennai/City-has-high-levels-of-smog-forming-ozone/arti...</td>\n",
       "      <td>City has high levels of smog-forming ozone</td>\n",
       "      <td>CHENNAI: Dangerous levels of ground-level ozone that causes respiratory ailments could be envelo...</td>\n",
       "      <td>Bhama Devi Ravi / TNN /  Jan 9, 2010, 00:37 IST</td>\n",
       "      <td>https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>chennai</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>tamil nadu</td>\n",
       "      <td>chennai</td>\n",
       "      <td>{'ozone': 9.0, 'smog': 4.0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TOI</td>\n",
       "      <td>2010-01-11</td>\n",
       "      <td>http://timesofindia.indiatimes.com//city/jaipur/Woman-four-kids-in-hospital-after-inhaling-smoke...</td>\n",
       "      <td>Woman, four kids in hospital after inhaling smoke</td>\n",
       "      <td>JAIPUR: A woman and her four kids were admitted to a city hospital in a serious condition after ...</td>\n",
       "      <td>TNN /  Jan 11, 2010, 01:56 IST</td>\n",
       "      <td>https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>jaipur</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>rajasthan</td>\n",
       "      <td>jaipur</td>\n",
       "      <td>{'carbon monoxide': 1.0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TOI</td>\n",
       "      <td>2010-01-13</td>\n",
       "      <td>http://timesofindia.indiatimes.com//city/chennai/Pollution-high-on-Bhogi-eve-Study/articleshow/5...</td>\n",
       "      <td>Pollution high on Bhogi eve: Study</td>\n",
       "      <td>CHENNAI: By the time you get to read this, the Bhogi pollutants may have enveloped the city, but...</td>\n",
       "      <td>Bhama Devi Ravi / TNN /  Jan 13, 2010, 04:23 IST</td>\n",
       "      <td>https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>chennai</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>tamil nadu</td>\n",
       "      <td>chennai</td>\n",
       "      <td>{'air quality': 1.0, 'carbon monoxide': 1.0, 'ozone': 1.0, 'so2': 1.0}</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17369</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>2021-05-16</td>\n",
       "      <td>https://www.thehindu.com/news/national/andhra-pradesh/naval-dockyard-teams-repair-two-oxygen-pla...</td>\n",
       "      <td>Naval Dockyard teams repair two oxygen plants in Nellore and Srikalahasti</td>\n",
       "      <td>Oxygen supply gets a big boost with this in the State during the pandemic\\n\\nTeams from the Nava...</td>\n",
       "      <td>SpecialCorrespondent</td>\n",
       "      <td>https://www.thehindu.com/news/national/andhra-pradesh/fvjzld/article34572702.ece/ALTERNATES/LAND...</td>\n",
       "      <td>andhra pradesh</td>\n",
       "      <td>visakhapatnam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>andhra pradesh</td>\n",
       "      <td>visakhapatnam</td>\n",
       "      <td>{'carbon monoxide': 2.0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17370</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>2021-05-24</td>\n",
       "      <td>https://www.thehindu.com/news/national/kerala/greens-call-for-a-relook-into-silverline-implement...</td>\n",
       "      <td>Greens call for a relook into SilverLine implementation</td>\n",
       "      <td>The Kerala Paristhithi Aikya Vedi, a forum of environmentalists, has urged Chief Minister Pinara...</td>\n",
       "      <td>SpecialCorrespondent</td>\n",
       "      <td>https://www.thehindu.com/static/theme/default/base/img/og-image.jpg</td>\n",
       "      <td>kerala</td>\n",
       "      <td>thiruvananthapuram</td>\n",
       "      <td>NaN</td>\n",
       "      <td>kerala</td>\n",
       "      <td>thiruvananthapuram</td>\n",
       "      <td>{'air pollution': 1.0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17371</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>2021-05-25</td>\n",
       "      <td>https://www.thehindu.com/news/national/karnataka/cftris-ozone-based-air-disinfection-system-to-c...</td>\n",
       "      <td>CFTRI’s ozone-based air disinfection system to combat droplet infections</td>\n",
       "      <td>The CSIR-Central Food Technological Research Institute (CFTRI) in Mysuru has come up with an ozo...</td>\n",
       "      <td>LaiqhAKhan</td>\n",
       "      <td>https://www.thehindu.com/sci-tech/science/wsmdd0/article33283019.ece/ALTERNATES/LANDSCAPE_615/CF...</td>\n",
       "      <td>karnataka</td>\n",
       "      <td>mysuru</td>\n",
       "      <td>NaN</td>\n",
       "      <td>karnataka</td>\n",
       "      <td>mysuru</td>\n",
       "      <td>{'ozone': 9.0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17372</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>https://www.thehindu.com/news/national/ngt-stays-green-nod-for-ntpcs-karimnagar-plant/article346...</td>\n",
       "      <td>NGT stays green nod for NTPC’s Karimnagar plant</td>\n",
       "      <td>Environmental Clearance put on hold for seven months, bench calls for fresh appraisal.\\n\\nThe so...</td>\n",
       "      <td>T.K.Rohit</td>\n",
       "      <td>https://www.thehindu.com/news/cities/Hyderabad/article23578273.ece/ALTERNATES/LANDSCAPE_615/17HY...</td>\n",
       "      <td>national</td>\n",
       "      <td>chennai</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tamil nadu</td>\n",
       "      <td>chennai</td>\n",
       "      <td>{'air quality': 1.0}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17373</th>\n",
       "      <td>Hindu</td>\n",
       "      <td>2021-05-30</td>\n",
       "      <td>https://www.thehindu.com/news/national/kerala/create-forests-to-stop-global-warming-says-ministe...</td>\n",
       "      <td>Create forests to stop global warming, says Minister</td>\n",
       "      <td>Minister for Local Self-Governments and Excise M.V. Govindan has called for a conscious effort t...</td>\n",
       "      <td>StaffReporter</td>\n",
       "      <td>https://www.thehindu.com/static/theme/default/base/img/og-image.jpg</td>\n",
       "      <td>kerala</td>\n",
       "      <td>kannur</td>\n",
       "      <td>NaN</td>\n",
       "      <td>kerala</td>\n",
       "      <td>kannur</td>\n",
       "      <td>{'air pollution': 1.0}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17374 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       media        date  \\\n",
       "0        TOI  2010-01-01   \n",
       "1        TOI  2010-01-02   \n",
       "2        TOI  2010-01-09   \n",
       "3        TOI  2010-01-11   \n",
       "4        TOI  2010-01-13   \n",
       "...      ...         ...   \n",
       "17369  Hindu  2021-05-16   \n",
       "17370  Hindu  2021-05-24   \n",
       "17371  Hindu  2021-05-25   \n",
       "17372  Hindu  2021-05-28   \n",
       "17373  Hindu  2021-05-30   \n",
       "\n",
       "                                                                                                       url  \\\n",
       "0      http://timesofindia.indiatimes.com//city/mumbai/Mumbai-celebrates-a-cool-New-Years-Eve-at-13-4C/...   \n",
       "1      http://timesofindia.indiatimes.com//city/lucknow/Dense-fog-leads-to-tripping-of-power-lines/arti...   \n",
       "2      http://timesofindia.indiatimes.com//city/chennai/City-has-high-levels-of-smog-forming-ozone/arti...   \n",
       "3      http://timesofindia.indiatimes.com//city/jaipur/Woman-four-kids-in-hospital-after-inhaling-smoke...   \n",
       "4      http://timesofindia.indiatimes.com//city/chennai/Pollution-high-on-Bhogi-eve-Study/articleshow/5...   \n",
       "...                                                                                                    ...   \n",
       "17369  https://www.thehindu.com/news/national/andhra-pradesh/naval-dockyard-teams-repair-two-oxygen-pla...   \n",
       "17370  https://www.thehindu.com/news/national/kerala/greens-call-for-a-relook-into-silverline-implement...   \n",
       "17371  https://www.thehindu.com/news/national/karnataka/cftris-ozone-based-air-disinfection-system-to-c...   \n",
       "17372  https://www.thehindu.com/news/national/ngt-stays-green-nod-for-ntpcs-karimnagar-plant/article346...   \n",
       "17373  https://www.thehindu.com/news/national/kerala/create-forests-to-stop-global-warming-says-ministe...   \n",
       "\n",
       "                                                                         heading  \\\n",
       "0                             Mumbai celebrates a cool New Year's Eve at 13.4फ्C   \n",
       "1                                     Dense fog leads to tripping of power lines   \n",
       "2                                     City has high levels of smog-forming ozone   \n",
       "3                              Woman, four kids in hospital after inhaling smoke   \n",
       "4                                             Pollution high on Bhogi eve: Study   \n",
       "...                                                                          ...   \n",
       "17369  Naval Dockyard teams repair two oxygen plants in Nellore and Srikalahasti   \n",
       "17370                   Greens call for a relook into SilverLine implementation    \n",
       "17371  CFTRI’s ozone-based air disinfection system to combat droplet infections    \n",
       "17372                            NGT stays green nod for NTPC’s Karimnagar plant   \n",
       "17373                      Create forests to stop global warming, says Minister    \n",
       "\n",
       "                                                                                                   content  \\\n",
       "0      MUMBAI: The city witnessed its lowest temperature of the season on New Year ’s Eve when the merc...   \n",
       "1      LUCKNOW: The UP Power Corporation Limited is keeping its fingers crossed for Saturday with the s...   \n",
       "2      CHENNAI: Dangerous levels of ground-level ozone that causes respiratory ailments could be envelo...   \n",
       "3      JAIPUR: A woman and her four kids were admitted to a city hospital in a serious condition after ...   \n",
       "4      CHENNAI: By the time you get to read this, the Bhogi pollutants may have enveloped the city, but...   \n",
       "...                                                                                                    ...   \n",
       "17369  Oxygen supply gets a big boost with this in the State during the pandemic\\n\\nTeams from the Nava...   \n",
       "17370  The Kerala Paristhithi Aikya Vedi, a forum of environmentalists, has urged Chief Minister Pinara...   \n",
       "17371  The CSIR-Central Food Technological Research Institute (CFTRI) in Mysuru has come up with an ozo...   \n",
       "17372  Environmental Clearance put on hold for seven months, bench calls for fresh appraisal.\\n\\nThe so...   \n",
       "17373  Minister for Local Self-Governments and Excise M.V. Govindan has called for a conscious effort t...   \n",
       "\n",
       "                                           other.author  \\\n",
       "0                         TNN /  Jan 1, 2010, 01:47 IST   \n",
       "1                         TNN /  Jan 2, 2010, 06:40 IST   \n",
       "2       Bhama Devi Ravi / TNN /  Jan 9, 2010, 00:37 IST   \n",
       "3                        TNN /  Jan 11, 2010, 01:56 IST   \n",
       "4      Bhama Devi Ravi / TNN /  Jan 13, 2010, 04:23 IST   \n",
       "...                                                 ...   \n",
       "17369                              SpecialCorrespondent   \n",
       "17370                              SpecialCorrespondent   \n",
       "17371                                        LaiqhAKhan   \n",
       "17372                                         T.K.Rohit   \n",
       "17373                                     StaffReporter   \n",
       "\n",
       "                                                                                           other.top_image  \\\n",
       "0      https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...   \n",
       "1      https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...   \n",
       "2      https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...   \n",
       "3      https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...   \n",
       "4      https://static.toiimg.com/thumb/msid-47529300,width-1070,height-580,imgsize-110164,resizemode-75...   \n",
       "...                                                                                                    ...   \n",
       "17369  https://www.thehindu.com/news/national/andhra-pradesh/fvjzld/article34572702.ece/ALTERNATES/LAND...   \n",
       "17370                                  https://www.thehindu.com/static/theme/default/base/img/og-image.jpg   \n",
       "17371  https://www.thehindu.com/sci-tech/science/wsmdd0/article33283019.ece/ALTERNATES/LANDSCAPE_615/CF...   \n",
       "17372  https://www.thehindu.com/news/cities/Hyderabad/article23578273.ece/ALTERNATES/LANDSCAPE_615/17HY...   \n",
       "17373                                  https://www.thehindu.com/static/theme/default/base/img/og-image.jpg   \n",
       "\n",
       "       other.category                city    year           state  \\\n",
       "0                 NaN              mumbai  2010.0     maharashtra   \n",
       "1                 NaN             lucknow  2010.0   uttar pradesh   \n",
       "2                 NaN             chennai  2010.0      tamil nadu   \n",
       "3                 NaN              jaipur  2010.0       rajasthan   \n",
       "4                 NaN             chennai  2010.0      tamil nadu   \n",
       "...               ...                 ...     ...             ...   \n",
       "17369  andhra pradesh       visakhapatnam     NaN  andhra pradesh   \n",
       "17370          kerala  thiruvananthapuram     NaN          kerala   \n",
       "17371       karnataka              mysuru     NaN       karnataka   \n",
       "17372        national             chennai     NaN      tamil nadu   \n",
       "17373          kerala              kannur     NaN          kerala   \n",
       "\n",
       "                 district  \\\n",
       "0         mumbai suburban   \n",
       "1                 lucknow   \n",
       "2                 chennai   \n",
       "3                  jaipur   \n",
       "4                 chennai   \n",
       "...                   ...   \n",
       "17369       visakhapatnam   \n",
       "17370  thiruvananthapuram   \n",
       "17371              mysuru   \n",
       "17372             chennai   \n",
       "17373              kannur   \n",
       "\n",
       "                                                                      matches  \n",
       "0                                                               {'smog': 3.0}  \n",
       "1                                                     {'air pollutants': 2.0}  \n",
       "2                                                 {'ozone': 9.0, 'smog': 4.0}  \n",
       "3                                                    {'carbon monoxide': 1.0}  \n",
       "4      {'air quality': 1.0, 'carbon monoxide': 1.0, 'ozone': 1.0, 'so2': 1.0}  \n",
       "...                                                                       ...  \n",
       "17369                                                {'carbon monoxide': 2.0}  \n",
       "17370                                                  {'air pollution': 1.0}  \n",
       "17371                                                          {'ozone': 9.0}  \n",
       "17372                                                    {'air quality': 1.0}  \n",
       "17373                                                  {'air pollution': 1.0}  \n",
       "\n",
       "[17374 rows x 13 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_articles = pd.read_csv(\"../dataset/News_articles_dataset/News_articles_dataset.csv.gz\")\n",
    "df_articles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "79beddd7",
   "metadata": {
    "scrolled": true
   },
   "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>Unnamed: 0</th>\n",
       "      <th>From Date</th>\n",
       "      <th>city</th>\n",
       "      <th>WS</th>\n",
       "      <th>WD</th>\n",
       "      <th>Gust</th>\n",
       "      <th>Variance</th>\n",
       "      <th>AT</th>\n",
       "      <th>RH</th>\n",
       "      <th>SR</th>\n",
       "      <th>BP</th>\n",
       "      <th>PPAccum</th>\n",
       "      <th>Power</th>\n",
       "      <th>PM10</th>\n",
       "      <th>PM2_5</th>\n",
       "      <th>NO</th>\n",
       "      <th>NO2</th>\n",
       "      <th>NOx</th>\n",
       "      <th>CO</th>\n",
       "      <th>CO2</th>\n",
       "      <th>Ozone</th>\n",
       "      <th>BlackCarbon</th>\n",
       "      <th>Benzene</th>\n",
       "      <th>Toluene</th>\n",
       "      <th>Eth-Benzene</th>\n",
       "      <th>Xylene</th>\n",
       "      <th>CH4</th>\n",
       "      <th>NMHC</th>\n",
       "      <th>VWS</th>\n",
       "      <th>THC</th>\n",
       "      <th>NH3</th>\n",
       "      <th>SO2</th>\n",
       "      <th>Temp</th>\n",
       "      <th>MP-Xylene</th>\n",
       "      <th>RF</th>\n",
       "      <th>RackTemp</th>\n",
       "      <th>P_Xylene</th>\n",
       "      <th>O Xylene</th>\n",
       "      <th>ETH</th>\n",
       "      <th>SPM</th>\n",
       "      <th>AP</th>\n",
       "      <th>MH</th>\n",
       "      <th>readings_in_day</th>\n",
       "      <th>total_stations</th>\n",
       "      <th>active_stations</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Agra</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>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>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Uttar Pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Ahmedabad</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>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>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Gujarat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Ambala</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>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>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Haryana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Ankleshwar</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>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>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Gujarat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>Bahadurgarh</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>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>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Haryana</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",
       "      <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",
       "      <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",
       "      <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>202192</th>\n",
       "      <td>202192</td>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Udaipur</td>\n",
       "      <td>1.017579</td>\n",
       "      <td>253.649263</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.871263</td>\n",
       "      <td>192.898000</td>\n",
       "      <td>757.183158</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87.814737</td>\n",
       "      <td>34.304255</td>\n",
       "      <td>13.917158</td>\n",
       "      <td>19.854526</td>\n",
       "      <td>33.771684</td>\n",
       "      <td>0.734835</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.070213</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.563936</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.092842</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.120947</td>\n",
       "      <td>12.064725</td>\n",
       "      <td>33.071053</td>\n",
       "      <td>1.188842</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.700211</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>94.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Rajasthan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202193</th>\n",
       "      <td>202193</td>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Ujjain</td>\n",
       "      <td>4.636842</td>\n",
       "      <td>255.042105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.660000</td>\n",
       "      <td>18.252632</td>\n",
       "      <td>161.242105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>147.452632</td>\n",
       "      <td>40.126316</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.891011</td>\n",
       "      <td>5.250562</td>\n",
       "      <td>0.819535</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78.206522</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.620000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.031461</td>\n",
       "      <td>8.647059</td>\n",
       "      <td>31.801075</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</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>95.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Madhya Pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202194</th>\n",
       "      <td>202194</td>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Varanasi</td>\n",
       "      <td>0.801053</td>\n",
       "      <td>180.757895</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.582105</td>\n",
       "      <td>23.600000</td>\n",
       "      <td>80.157895</td>\n",
       "      <td>745.378947</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>331.126761</td>\n",
       "      <td>131.732394</td>\n",
       "      <td>51.202360</td>\n",
       "      <td>104.323370</td>\n",
       "      <td>74.652174</td>\n",
       "      <td>0.860652</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.047692</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>-0.522105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.748652</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>71.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Uttar Pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202195</th>\n",
       "      <td>202195</td>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Vijayawada</td>\n",
       "      <td>0.519041</td>\n",
       "      <td>221.018904</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.019178</td>\n",
       "      <td>55.307671</td>\n",
       "      <td>228.706154</td>\n",
       "      <td>749.476301</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50.119577</td>\n",
       "      <td>20.220000</td>\n",
       "      <td>6.365139</td>\n",
       "      <td>14.738356</td>\n",
       "      <td>21.004658</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.996849</td>\n",
       "      <td>1.870137</td>\n",
       "      <td>0.057397</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.329589</td>\n",
       "      <td>6.234658</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.734110</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.899589</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Andhra Pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202196</th>\n",
       "      <td>202196</td>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Visakhapatnam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.226596</td>\n",
       "      <td>73.127660</td>\n",
       "      <td>163.893617</td>\n",
       "      <td>745.968085</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.734043</td>\n",
       "      <td>13.709302</td>\n",
       "      <td>8.764835</td>\n",
       "      <td>16.689011</td>\n",
       "      <td>15.998901</td>\n",
       "      <td>0.617912</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.763218</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.097872</td>\n",
       "      <td>2.794681</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.318085</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.931111</td>\n",
       "      <td>5.094444</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</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>86.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Andhra Pradesh</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>202197 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Unnamed: 0   From Date           city        WS          WD  Gust  \\\n",
       "0                0  2010-01-01           Agra       NaN         NaN   NaN   \n",
       "1                1  2010-01-01      Ahmedabad       NaN         NaN   NaN   \n",
       "2                2  2010-01-01        Ambala        NaN         NaN   NaN   \n",
       "3                3  2010-01-01     Ankleshwar       NaN         NaN   NaN   \n",
       "4                4  2010-01-01   Bahadurgarh        NaN         NaN   NaN   \n",
       "...            ...         ...            ...       ...         ...   ...   \n",
       "202192      202192  2019-04-30        Udaipur  1.017579  253.649263   NaN   \n",
       "202193      202193  2019-04-30         Ujjain  4.636842  255.042105   NaN   \n",
       "202194      202194  2019-04-30       Varanasi  0.801053  180.757895   NaN   \n",
       "202195      202195  2019-04-30     Vijayawada  0.519041  221.018904   NaN   \n",
       "202196      202196  2019-04-30  Visakhapatnam       NaN         NaN   NaN   \n",
       "\n",
       "        Variance         AT         RH          SR          BP  PPAccum  \\\n",
       "0            NaN        NaN        NaN         NaN         NaN      NaN   \n",
       "1            NaN        NaN        NaN         NaN         NaN      NaN   \n",
       "2            NaN        NaN        NaN         NaN         NaN      NaN   \n",
       "3            NaN        NaN        NaN         NaN         NaN      NaN   \n",
       "4            NaN        NaN        NaN         NaN         NaN      NaN   \n",
       "...          ...        ...        ...         ...         ...      ...   \n",
       "202192       NaN        NaN  17.871263  192.898000  757.183158      NaN   \n",
       "202193       NaN  36.660000  18.252632  161.242105         NaN      NaN   \n",
       "202194       NaN  33.582105  23.600000   80.157895  745.378947      NaN   \n",
       "202195       NaN  37.019178  55.307671  228.706154  749.476301      NaN   \n",
       "202196       NaN  30.226596  73.127660  163.893617  745.968085      NaN   \n",
       "\n",
       "        Power        PM10       PM2_5         NO         NO2        NOx  \\\n",
       "0         NaN         NaN         NaN        NaN         NaN        NaN   \n",
       "1         NaN         NaN         NaN        NaN         NaN        NaN   \n",
       "2         NaN         NaN         NaN        NaN         NaN        NaN   \n",
       "3         NaN         NaN         NaN        NaN         NaN        NaN   \n",
       "4         NaN         NaN         NaN        NaN         NaN        NaN   \n",
       "...       ...         ...         ...        ...         ...        ...   \n",
       "202192    NaN   87.814737   34.304255  13.917158   19.854526  33.771684   \n",
       "202193    NaN  147.452632   40.126316        NaN    8.891011   5.250562   \n",
       "202194    NaN  331.126761  131.732394  51.202360  104.323370  74.652174   \n",
       "202195    NaN   50.119577   20.220000   6.365139   14.738356  21.004658   \n",
       "202196    NaN   52.734043   13.709302   8.764835   16.689011  15.998901   \n",
       "\n",
       "              CO  CO2      Ozone  BlackCarbon   Benzene   Toluene  \\\n",
       "0            NaN  NaN        NaN          NaN       NaN       NaN   \n",
       "1            NaN  NaN        NaN          NaN       NaN       NaN   \n",
       "2            NaN  NaN        NaN          NaN       NaN       NaN   \n",
       "3            NaN  NaN        NaN          NaN       NaN       NaN   \n",
       "4            NaN  NaN        NaN          NaN       NaN       NaN   \n",
       "...          ...  ...        ...          ...       ...       ...   \n",
       "202192  0.734835  NaN  38.070213          NaN  1.563936       NaN   \n",
       "202193  0.819535  NaN  78.206522          NaN  0.000000  0.620000   \n",
       "202194  0.860652  NaN  76.047692          NaN       NaN       NaN   \n",
       "202195       NaN  NaN        NaN          NaN  0.996849  1.870137   \n",
       "202196  0.617912  NaN  10.763218          NaN  1.097872  2.794681   \n",
       "\n",
       "        Eth-Benzene    Xylene  CH4  NMHC       VWS  THC        NH3        SO2  \\\n",
       "0               NaN       NaN  NaN   NaN       NaN  NaN        NaN        NaN   \n",
       "1               NaN       NaN  NaN   NaN       NaN  NaN        NaN        NaN   \n",
       "2               NaN       NaN  NaN   NaN       NaN  NaN        NaN        NaN   \n",
       "3               NaN       NaN  NaN   NaN       NaN  NaN        NaN        NaN   \n",
       "4               NaN       NaN  NaN   NaN       NaN  NaN        NaN        NaN   \n",
       "...             ...       ...  ...   ...       ...  ...        ...        ...   \n",
       "202192     4.092842       NaN  NaN   NaN       NaN  NaN  12.120947  12.064725   \n",
       "202193     0.000000       NaN  NaN   NaN       NaN  NaN  19.031461   8.647059   \n",
       "202194          NaN       NaN  NaN   NaN -0.522105  NaN        NaN  37.748652   \n",
       "202195     0.057397       NaN  NaN   NaN  5.000000  NaN   5.329589   6.234658   \n",
       "202196          NaN  0.318085  NaN   NaN  0.050000  NaN  38.931111   5.094444   \n",
       "\n",
       "             Temp  MP-Xylene   RF  RackTemp  P_Xylene  O Xylene  ETH  SPM  AP  \\\n",
       "0             NaN        NaN  NaN       NaN       NaN       NaN  NaN  NaN NaN   \n",
       "1             NaN        NaN  NaN       NaN       NaN       NaN  NaN  NaN NaN   \n",
       "2             NaN        NaN  NaN       NaN       NaN       NaN  NaN  NaN NaN   \n",
       "3             NaN        NaN  NaN       NaN       NaN       NaN  NaN  NaN NaN   \n",
       "4             NaN        NaN  NaN       NaN       NaN       NaN  NaN  NaN NaN   \n",
       "...           ...        ...  ...       ...       ...       ...  ...  ...  ..   \n",
       "202192  33.071053   1.188842  0.0       NaN       NaN  3.700211  NaN  NaN NaN   \n",
       "202193  31.801075        NaN  0.0       NaN       NaN       NaN  NaN  NaN NaN   \n",
       "202194        NaN        NaN  NaN       NaN       NaN       NaN  NaN  NaN NaN   \n",
       "202195        NaN   0.734110  0.0       NaN       NaN  1.899589  NaN  NaN NaN   \n",
       "202196        NaN        NaN  0.0       NaN       NaN       NaN  NaN  NaN NaN   \n",
       "\n",
       "        MH  readings_in_day  total_stations  active_stations           state  \n",
       "0      NaN              0.0               1                0   Uttar Pradesh  \n",
       "1      NaN              0.0               1                0         Gujarat  \n",
       "2      NaN              0.0               1                0         Haryana  \n",
       "3      NaN              0.0               1                0         Gujarat  \n",
       "4      NaN              0.0               1                0         Haryana  \n",
       "...     ..              ...             ...              ...             ...  \n",
       "202192 NaN             94.0               1                1       Rajasthan  \n",
       "202193 NaN             95.0               1                1  Madhya Pradesh  \n",
       "202194 NaN             71.0               1                1   Uttar Pradesh  \n",
       "202195 NaN             67.0               1                1  Andhra Pradesh  \n",
       "202196 NaN             86.0               1                1  Andhra Pradesh  \n",
       "\n",
       "[202197 rows x 46 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#load PM2.5 data 2010-2019 (cpcb)\n",
    "root_path = \"../PMdataset/\"\n",
    "df_daily_city_stn = pd.read_csv(root_path+\"pm2.5_allcities_groupby_date_city_station_2010_19.csv\",index_col=0)\n",
    "df_daily_city = pd.read_csv(root_path+\"pm2.5_allcities_groupby_date_city_2010_19.csv\",index_col=0)\n",
    "#df_daily_city = df_daily_city.groupby([\"From Date\",\"city\"]).mean().reset_index()\n",
    "df_daily_city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "78f146d0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(101,\n",
       " {'Agra': 'Uttar Pradesh',\n",
       "  'Ahmedabad': 'Gujarat',\n",
       "  'Ambala ': 'Haryana',\n",
       "  'Ankleshwar': 'Gujarat',\n",
       "  'Bahadurgarh ': 'Haryana',\n",
       "  'Ballabgarh': 'Haryana',\n",
       "  'Bengaluru': 'Karnataka',\n",
       "  'Bhiwani': 'Haryana',\n",
       "  'Chennai': 'Tamil Nadu',\n",
       "  'Damoh': 'Madhya Pradesh',\n",
       "  'Delhi': 'Delhi',\n",
       "  'Dharuhera': 'Haryana',\n",
       "  'Faridabad': 'Haryana',\n",
       "  'Fatehabad ': 'Haryana',\n",
       "  'Gandhinagar': 'Gujarat',\n",
       "  'Ghaziabad': 'Uttar Pradesh',\n",
       "  'Gurugram': 'Haryana',\n",
       "  'Guwahati': 'Assam',\n",
       "  'Hapur': 'Uttar Pradesh',\n",
       "  'Hisar': 'Haryana',\n",
       "  'Hyderabad': 'Telangana',\n",
       "  'Jind': 'Haryana',\n",
       "  'Kaithal': 'Haryana',\n",
       "  'Kanpur': 'Uttar Pradesh',\n",
       "  'Karnal': 'Haryana',\n",
       "  'Kurukshetra ': 'Haryana',\n",
       "  'Loni Dehat': 'Uttar Pradesh',\n",
       "  'Lucknow': 'Uttar Pradesh',\n",
       "  'Maihar': 'Madhya Pradesh',\n",
       "  'Mandikhera': 'Haryana',\n",
       "  'Manesar ': 'Haryana',\n",
       "  'Mumbai': 'Maharashtra',\n",
       "  'Nagpur': 'Maharashtra',\n",
       "  'Narnaul': 'Haryana',\n",
       "  'Navi Mumbai': 'Maharashtra',\n",
       "  'Palwal ': 'Haryana',\n",
       "  'Panipat': 'Haryana',\n",
       "  'Pune': 'Maharashtra',\n",
       "  'Ratlam': 'Madhya Pradesh',\n",
       "  'Sirsa ': 'Haryana',\n",
       "  'Solapur': 'Maharashtra',\n",
       "  'Sonipat': 'Haryana',\n",
       "  'Vapi': 'Gujarat',\n",
       "  'Varanasi': 'Uttar Pradesh',\n",
       "  'Vatva': 'Gujarat',\n",
       "  'Yamuna Nagar': 'Haryana',\n",
       "  'Noida': 'Uttar Pradesh',\n",
       "  'Patna': 'Bihar',\n",
       "  'Chandrapur': 'Maharashtra',\n",
       "  'Gaya': 'Bihar',\n",
       "  'Haldia': 'West Bengal',\n",
       "  'Howrah': 'West Bengal',\n",
       "  'Kolkata': 'West Bengal',\n",
       "  'Muzaffarpur': 'Bihar',\n",
       "  'Durgapur': 'West Bengal',\n",
       "  'Jodhpur': 'Rajasthan',\n",
       "  'Nashik': 'Maharashtra',\n",
       "  'Rohtak': 'Haryana',\n",
       "  'Thane': 'Maharashtra',\n",
       "  'Tirupati': 'Andhra Pradesh',\n",
       "  'Visakhapatnam': 'Andhra Pradesh',\n",
       "  ' Alwar': 'Rajasthan',\n",
       "  'Ajmer': 'Rajasthan',\n",
       "  'Amaravati': 'Andhra Pradesh',\n",
       "  'Amritsar': 'Punjab',\n",
       "  'Aurangabad': 'Maharashtra',\n",
       "  'Bhiwadi': 'Rajasthan',\n",
       "  'Brajrajnagar': 'Odisha',\n",
       "  'Dewas': 'Madhya Pradesh',\n",
       "  'Jaipur': 'Rajasthan',\n",
       "  'Jorapokhar': 'Jharkhand',\n",
       "  'Kota': 'Rajasthan',\n",
       "  'Ludhiana': 'Punjab',\n",
       "  'Mandi Gobindgarh': 'Punjab',\n",
       "  'Moradabad': 'Uttar Pradesh',\n",
       "  'Pali': 'Rajasthan',\n",
       "  'Panchkula': 'Haryana',\n",
       "  'Pithampur': 'Madhya Pradesh',\n",
       "  'Rajamahendravaram': 'Andhra Pradesh',\n",
       "  'Singrauli': 'Madhya Pradesh',\n",
       "  'Talcher': 'Odisha',\n",
       "  'Thiruvananthapuram': 'Kerala',\n",
       "  'Udaipur': 'Rajasthan',\n",
       "  'Ujjain': 'Madhya Pradesh',\n",
       "  'Vijayawada': 'Andhra Pradesh',\n",
       "  'Asansol': 'West Bengal',\n",
       "  'Baghpat': 'Uttar Pradesh',\n",
       "  'Bathinda ': 'Punjab',\n",
       "  'Bulandshahr': 'Uttar Pradesh',\n",
       "  'Chikkaballapur': 'Karnataka',\n",
       "  'Greater Noida': 'Uttar Pradesh',\n",
       "  'Hubballi': 'Karnataka',\n",
       "  'Jalandhar': 'Punjab',\n",
       "  'Kalaburagi': 'Karnataka',\n",
       "  'Khanna': 'Punjab',\n",
       "  'Mandideep': 'Madhya Pradesh',\n",
       "  'Muzaffarnagar': 'Uttar Pradesh',\n",
       "  'Patiala': 'Punjab',\n",
       "  'Rupnagar': 'Punjab',\n",
       "  'Satna': 'Madhya Pradesh',\n",
       "  'Siliguri': 'West Bengal'})"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ip = df_daily_city\n",
    "city_state_dict = dict(zip(df_ip[\"city\"].values,df_ip[\"state\"].values))\n",
    "len(city_state_dict), city_state_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "75fade2c",
   "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>From Date</th>\n",
       "      <th>city</th>\n",
       "      <th>PM2_5</th>\n",
       "      <th>readings</th>\n",
       "      <th>total_stations</th>\n",
       "      <th>active_stations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-18</td>\n",
       "      <td>Delhi</td>\n",
       "      <td>133.960000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Agra</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Ajmer</td>\n",
       "      <td>105.640000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Amaravati</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>Amritsar</td>\n",
       "      <td>50.970000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75127</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>Solapur</td>\n",
       "      <td>18.020000</td>\n",
       "      <td>69.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75128</th>\n",
       "      <td>2021-12-03</td>\n",
       "      <td>Solapur</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75129</th>\n",
       "      <td>2021-12-04</td>\n",
       "      <td>Solapur</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75130</th>\n",
       "      <td>2021-12-05</td>\n",
       "      <td>Solapur</td>\n",
       "      <td>14.172069</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75131</th>\n",
       "      <td>2025-06-14</td>\n",
       "      <td>Pune</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>51927 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        From Date       city       PM2_5  readings  total_stations  \\\n",
       "0      2019-01-18      Delhi  133.960000       2.0               1   \n",
       "2      2019-04-30       Agra   90.000000       1.0               1   \n",
       "3      2019-04-30      Ajmer  105.640000       2.0               1   \n",
       "4      2019-04-30  Amaravati   22.000000       1.0               1   \n",
       "6      2019-04-30   Amritsar   50.970000       1.0               1   \n",
       "...           ...        ...         ...       ...             ...   \n",
       "75127  2021-08-01    Solapur   18.020000      69.0               1   \n",
       "75128  2021-12-03    Solapur         NaN       0.0               1   \n",
       "75129  2021-12-04    Solapur         NaN       0.0               1   \n",
       "75130  2021-12-05    Solapur   14.172069      29.0               1   \n",
       "75131  2025-06-14       Pune    6.000000    1951.0               1   \n",
       "\n",
       "       active_stations  \n",
       "0                    1  \n",
       "2                    1  \n",
       "3                    1  \n",
       "4                    1  \n",
       "6                    1  \n",
       "...                ...  \n",
       "75127                1  \n",
       "75128                0  \n",
       "75129                0  \n",
       "75130                1  \n",
       "75131                1  \n",
       "\n",
       "[51927 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#load OpenAQ data 2019-2021\n",
    "root_path = \"../PMdataset/\"\n",
    "df_daily_city_stn_oq = pd.read_csv(root_path+\"pm2.5_allcities_groupby_date_city_station_2019_21_openaq.csv\",index_col=0)\n",
    "df_daily_city_oq = pd.read_csv(root_path+\"pm2.5_allcities_groupby_date_city_2019_21_openaq.csv\",index_col=0)\n",
    "#df_daily_city_oq = df_daily_city_oq.groupby([\"From Date\",\"city\"]).mean().reset_index()\n",
    "df_daily_city_oq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c2380828",
   "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>From Date</th>\n",
       "      <th>city</th>\n",
       "      <th>PM2_5</th>\n",
       "      <th>total_stations</th>\n",
       "      <th>active_stations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>agra</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>ahmedabad</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>ambala</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>ankleshwar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>bahadurgarh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254058</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>solapur</td>\n",
       "      <td>18.020000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254059</th>\n",
       "      <td>2021-12-03</td>\n",
       "      <td>solapur</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254060</th>\n",
       "      <td>2021-12-04</td>\n",
       "      <td>solapur</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254061</th>\n",
       "      <td>2021-12-05</td>\n",
       "      <td>solapur</td>\n",
       "      <td>14.172069</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254062</th>\n",
       "      <td>2025-06-14</td>\n",
       "      <td>pune</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>254063 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        From Date         city      PM2_5  total_stations  active_stations\n",
       "0      2010-01-01         agra        NaN             1.0              0.0\n",
       "1      2010-01-01    ahmedabad        NaN             1.0              0.0\n",
       "2      2010-01-01       ambala        NaN             1.0              0.0\n",
       "3      2010-01-01   ankleshwar        NaN             1.0              0.0\n",
       "4      2010-01-01  bahadurgarh        NaN             1.0              0.0\n",
       "...           ...          ...        ...             ...              ...\n",
       "254058 2021-08-01      solapur  18.020000             1.0              1.0\n",
       "254059 2021-12-03      solapur        NaN             1.0              0.0\n",
       "254060 2021-12-04      solapur        NaN             1.0              0.0\n",
       "254061 2021-12-05      solapur  14.172069             1.0              1.0\n",
       "254062 2025-06-14         pune   6.000000             1.0              1.0\n",
       "\n",
       "[254063 rows x 5 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merge cpcb and openaq \n",
    "df_daily_city_merged = pd.concat([df_daily_city,df_daily_city_oq])[[\"From Date\",\"city\",\n",
    "                                                                    \"PM2_5\",\"total_stations\",\"active_stations\",\"state\"]]\n",
    "\n",
    "df_daily_city_merged.city = df_daily_city_merged.city.apply(lambda x:x.strip().lower())\n",
    "df_daily_city_merged[\"From Date\"] = pd.to_datetime(df_daily_city_merged[\"From Date\"])\n",
    "df_daily_city_merged = df_daily_city_merged.groupby([\"From Date\",\"city\"]).mean().reset_index()\n",
    "df_daily_city_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "2e27a137",
   "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>From Date</th>\n",
       "      <th>city</th>\n",
       "      <th>PM2_5</th>\n",
       "      <th>total_stations</th>\n",
       "      <th>active_stations</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>agra</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>uttar pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>ahmedabad</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>gujarat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>ambala</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>ankleshwar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>gujarat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>bahadurgarh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254058</th>\n",
       "      <td>2021-08-01</td>\n",
       "      <td>solapur</td>\n",
       "      <td>18.020000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>maharashtra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254059</th>\n",
       "      <td>2021-12-03</td>\n",
       "      <td>solapur</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>maharashtra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254060</th>\n",
       "      <td>2021-12-04</td>\n",
       "      <td>solapur</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>maharashtra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254061</th>\n",
       "      <td>2021-12-05</td>\n",
       "      <td>solapur</td>\n",
       "      <td>14.172069</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>maharashtra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254062</th>\n",
       "      <td>2025-06-14</td>\n",
       "      <td>pune</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>maharashtra</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>254063 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        From Date         city      PM2_5  total_stations  active_stations  \\\n",
       "0      2010-01-01         agra        NaN             1.0              0.0   \n",
       "1      2010-01-01    ahmedabad        NaN             1.0              0.0   \n",
       "2      2010-01-01       ambala        NaN             1.0              0.0   \n",
       "3      2010-01-01   ankleshwar        NaN             1.0              0.0   \n",
       "4      2010-01-01  bahadurgarh        NaN             1.0              0.0   \n",
       "...           ...          ...        ...             ...              ...   \n",
       "254058 2021-08-01      solapur  18.020000             1.0              1.0   \n",
       "254059 2021-12-03      solapur        NaN             1.0              0.0   \n",
       "254060 2021-12-04      solapur        NaN             1.0              0.0   \n",
       "254061 2021-12-05      solapur  14.172069             1.0              1.0   \n",
       "254062 2025-06-14         pune   6.000000             1.0              1.0   \n",
       "\n",
       "                state  \n",
       "0       uttar pradesh  \n",
       "1             gujarat  \n",
       "2                 NaN  \n",
       "3             gujarat  \n",
       "4                 NaN  \n",
       "...               ...  \n",
       "254058    maharashtra  \n",
       "254059    maharashtra  \n",
       "254060    maharashtra  \n",
       "254061    maharashtra  \n",
       "254062    maharashtra  \n",
       "\n",
       "[254063 rows x 6 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_daily_city_merged[\"state\"] = df_daily_city_merged[\"city\"].apply(lambda x:city_state_dict[x.capitalize()].lower() if x.capitalize() in city_state_dict else np.nan)\n",
    "df_daily_city_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "29a7eec9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['uttar pradesh', 'gujarat', nan, 'haryana', 'karnataka',\n",
       "       'tamil nadu', 'madhya pradesh', 'delhi', 'assam', 'telangana',\n",
       "       'maharashtra', 'bihar', 'west bengal', 'rajasthan',\n",
       "       'andhra pradesh', 'punjab', 'odisha', 'jharkhand', 'kerala'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_daily_city_merged.state.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d65da83",
   "metadata": {},
   "source": [
    "# create table for PM2.5 & articles for cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "07fab5f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#check city wise data start date & end date\n",
    "def get_start_date(city,threshold = 0.8,n = 365):\n",
    "    print(city)\n",
    "    test = df_daily_city_merged[df_daily_city_merged.city == city]\n",
    "    test[\"From Date\"] = pd.to_datetime(test[\"From Date\"])\n",
    "    test_drop_na = test.dropna(subset=[\"PM2_5\"])\n",
    "\n",
    "    for idx,row in test_drop_na.iterrows():\n",
    "        curr_date = row[\"From Date\"]\n",
    "        n = 365\n",
    "        end_date = curr_date+pd.Timedelta(days=n)\n",
    "        date_range = pd.date_range(curr_date,end_date)\n",
    "        temp = test[test[\"From Date\"].isin(date_range)]\n",
    "        frac = len(temp.dropna(subset=[\"PM2_5\"]))/len(temp)\n",
    "        #print(frac)\n",
    "        threshold = 0.8\n",
    "        if frac >threshold:\n",
    "            print(curr_date)\n",
    "            return curr_date\n",
    "def get_unique_cities_from_df(df,col=\"city\"):\n",
    "    '''\n",
    "    returns dataframe of city->articles from dataframe\n",
    "    as city can be list so unique() will not work\n",
    "    '''\n",
    "    unq = {}\n",
    "    for each in df[col].values:\n",
    "        if type(each) == str:\n",
    "            cities = [each]\n",
    "        elif type(each) == list:\n",
    "            cities = each\n",
    "        else:\n",
    "            continue\n",
    "        for city in cities:\n",
    "            try:\n",
    "                unq[city]+=1\n",
    "            except:\n",
    "                unq[city] = 1\n",
    "    df = pd.DataFrame(unq.items(),columns = [\"city\",\"articles\"]).sort_values(\"articles\",ascending=False)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "6f8d5498",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "18 ['patna', 'faridabad', 'delhi', 'agra', 'varanasi', 'gurugram', 'nagpur', 'mumbai', 'hyderabad', 'noida', 'chennai', 'bengaluru', 'chandigarh', 'amritsar', 'kanpur', 'kolkata', 'lucknow', 'pune']\n",
      "patna\n",
      "2015-10-01 00:00:00\n",
      "faridabad\n",
      "2015-05-20 00:00:00\n",
      "delhi\n",
      "2010-02-17 00:00:00\n",
      "agra\n",
      "2015-05-11 00:00:00\n",
      "varanasi\n",
      "2014-09-13 00:00:00\n",
      "gurugram\n",
      "2016-01-22 00:00:00\n",
      "nagpur\n",
      "2011-05-04 00:00:00\n",
      "mumbai\n",
      "2014-09-05 00:00:00\n",
      "hyderabad\n",
      "2015-03-31 00:00:00\n",
      "noida\n",
      "2017-07-17 00:00:00\n",
      "chennai\n",
      "2015-03-23 00:00:00\n",
      "bengaluru\n",
      "2015-03-20 00:00:00\n",
      "chandigarh\n",
      "chandigarh Invalid comparison between dtype=datetime64[ns] and NoneType\n",
      "amritsar\n",
      "2017-02-27 00:00:00\n",
      "kanpur\n",
      "2015-05-12 00:00:00\n",
      "kolkata\n",
      "2018-04-10 00:00:00\n",
      "lucknow\n",
      "2015-03-18 00:00:00\n",
      "pune\n",
      "2015-06-06 00:00:00\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>city</th>\n",
       "      <th>agra</th>\n",
       "      <th>amritsar</th>\n",
       "      <th>bengaluru</th>\n",
       "      <th>chennai</th>\n",
       "      <th>delhi</th>\n",
       "      <th>faridabad</th>\n",
       "      <th>gurugram</th>\n",
       "      <th>hyderabad</th>\n",
       "      <th>kanpur</th>\n",
       "      <th>kolkata</th>\n",
       "      <th>lucknow</th>\n",
       "      <th>mumbai</th>\n",
       "      <th>nagpur</th>\n",
       "      <th>noida</th>\n",
       "      <th>patna</th>\n",
       "      <th>pune</th>\n",
       "      <th>varanasi</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>From Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-02-18</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>135.687500</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-02-19</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>107.047158</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-02-20</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.914687</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-02-21</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.360000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-02-22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.640370</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",
       "    </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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-18</th>\n",
       "      <td>23.200000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.718750</td>\n",
       "      <td>16.128846</td>\n",
       "      <td>40.66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.647059</td>\n",
       "      <td>4.7</td>\n",
       "      <td>19.250000</td>\n",
       "      <td>8.18</td>\n",
       "      <td>35.372941</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.020</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-19</th>\n",
       "      <td>10.000000</td>\n",
       "      <td>53.615</td>\n",
       "      <td>39.696667</td>\n",
       "      <td>45.333423</td>\n",
       "      <td>30.945804</td>\n",
       "      <td>31.00</td>\n",
       "      <td>39.6</td>\n",
       "      <td>10.564039</td>\n",
       "      <td>25.0</td>\n",
       "      <td>29.350000</td>\n",
       "      <td>21.96</td>\n",
       "      <td>23.340465</td>\n",
       "      <td>33.25</td>\n",
       "      <td>18.355000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.800</td>\n",
       "      <td>20.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-20</th>\n",
       "      <td>24.850000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.202500</td>\n",
       "      <td>31.232143</td>\n",
       "      <td>23.494222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.540984</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.978077</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.835000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-21</th>\n",
       "      <td>19.866667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.820000</td>\n",
       "      <td>30.535714</td>\n",
       "      <td>41.096285</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.5</td>\n",
       "      <td>47.013889</td>\n",
       "      <td>35.5</td>\n",
       "      <td>40.250000</td>\n",
       "      <td>12.90</td>\n",
       "      <td>51.575455</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.658095</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.818</td>\n",
       "      <td>16.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2025-06-14</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>6.000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4101 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "city             agra  amritsar  bengaluru    chennai       delhi  faridabad  \\\n",
       "From Date                                                                      \n",
       "2010-02-18        NaN       NaN        NaN        NaN  135.687500        NaN   \n",
       "2010-02-19        NaN       NaN        NaN        NaN  107.047158        NaN   \n",
       "2010-02-20        NaN       NaN        NaN        NaN   15.914687        NaN   \n",
       "2010-02-21        NaN       NaN        NaN        NaN    9.360000        NaN   \n",
       "2010-02-22        NaN       NaN        NaN        NaN   17.640370        NaN   \n",
       "...               ...       ...        ...        ...         ...        ...   \n",
       "2021-06-18  23.200000       NaN        NaN  19.718750   16.128846      40.66   \n",
       "2021-06-19  10.000000    53.615  39.696667  45.333423   30.945804      31.00   \n",
       "2021-06-20  24.850000       NaN  29.202500  31.232143   23.494222        NaN   \n",
       "2021-06-21  19.866667       NaN  18.820000  30.535714   41.096285        NaN   \n",
       "2025-06-14        NaN       NaN        NaN        NaN         NaN        NaN   \n",
       "\n",
       "city        gurugram  hyderabad  kanpur    kolkata  lucknow     mumbai  \\\n",
       "From Date                                                                \n",
       "2010-02-18       NaN        NaN     NaN        NaN      NaN        NaN   \n",
       "2010-02-19       NaN        NaN     NaN        NaN      NaN        NaN   \n",
       "2010-02-20       NaN        NaN     NaN        NaN      NaN        NaN   \n",
       "2010-02-21       NaN        NaN     NaN        NaN      NaN        NaN   \n",
       "2010-02-22       NaN        NaN     NaN        NaN      NaN        NaN   \n",
       "...              ...        ...     ...        ...      ...        ...   \n",
       "2021-06-18       NaN  17.647059     4.7  19.250000     8.18  35.372941   \n",
       "2021-06-19      39.6  10.564039    25.0  29.350000    21.96  23.340465   \n",
       "2021-06-20      31.0  22.000000     NaN  29.540984      NaN  43.978077   \n",
       "2021-06-21      49.5  47.013889    35.5  40.250000    12.90  51.575455   \n",
       "2025-06-14       NaN        NaN     NaN        NaN      NaN        NaN   \n",
       "\n",
       "city        nagpur      noida  patna    pune  varanasi  \n",
       "From Date                                               \n",
       "2010-02-18     NaN        NaN    NaN     NaN       NaN  \n",
       "2010-02-19     NaN        NaN    NaN     NaN       NaN  \n",
       "2010-02-20     NaN        NaN    NaN     NaN       NaN  \n",
       "2010-02-21     NaN        NaN    NaN     NaN       NaN  \n",
       "2010-02-22     NaN        NaN    NaN     NaN       NaN  \n",
       "...            ...        ...    ...     ...       ...  \n",
       "2021-06-18     NaN        NaN    NaN  10.020       NaN  \n",
       "2021-06-19   33.25  18.355000    NaN  13.800     20.00  \n",
       "2021-06-20     NaN  15.835000    NaN     NaN       NaN  \n",
       "2021-06-21     NaN  31.658095    NaN  63.818     16.78  \n",
       "2025-06-14     NaN        NaN    NaN   6.000       NaN  \n",
       "\n",
       "[4101 rows x 17 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cdf = get_unique_cities_from_df(df_articles)\n",
    "igp_cities = [\"delhi\",\"agra\",\"lucknow\",\"patna\",\"kanpur\",\"varanasi\",\"noida\",\"gurugram\",\"faridabad\",\"kolkata\",\"amritsar\"]\n",
    "most_discussed_cities = list(cdf.nlargest(11,\"articles\")[\"city\"].values) #most 11 discussed cities in news-media\n",
    "needed_cities = list(set(igp_cities + most_discussed_cities)) #most\n",
    "print(len(needed_cities),needed_cities)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "#needed_cities = h.get_unique_cities_from_df(df_articles).nlargest(15,\"articles\")[\"city\"].values\n",
    "\n",
    "start_date = datetime(2010,1,1)\n",
    "end_date = datetime(2021,6,1)\n",
    "\n",
    "#df_cross_cities = df_daily_city_merged[(df_daily_city_merged[\"From Date\"] >= start_date) & (df_daily_city_merged[\"From Date\"] <= end_date)]\n",
    "df_cross_cities = df_daily_city_merged.copy()\n",
    "df_cross_cities = df_cross_cities[df_cross_cities.city.isin(needed_cities)]\n",
    "dfs = []\n",
    "for city in needed_cities:\n",
    "    try:\n",
    "        start_date = get_start_date(city)\n",
    "        df = df_cross_cities[(df_cross_cities[\"city\"] == city) & (df_cross_cities[\"From Date\"]>start_date)]\n",
    "        dfs.append(df)\n",
    "    except Exception as e:\n",
    "        print(city,e)\n",
    "    \n",
    "df_cross_cities = pd.concat(dfs)\n",
    "df_cross_cities = pd.crosstab(index=df_cross_cities[\"From Date\"],\n",
    "                              columns=df_cross_cities.city,values=df_cross_cities[\"PM2_5\"],aggfunc=lambda x:x)\n",
    "df_cross_cities"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e3cf74c",
   "metadata": {},
   "source": [
    "## Comparing with WHO & India standard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ef2336dc",
   "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>city</th>\n",
       "      <th>articles</th>\n",
       "      <th>India limit</th>\n",
       "      <th>WHO limit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>delhi</td>\n",
       "      <td>5285</td>\n",
       "      <td>81\\%</td>\n",
       "      <td>98\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gurugram</td>\n",
       "      <td>849</td>\n",
       "      <td>71\\%</td>\n",
       "      <td>96\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mumbai</td>\n",
       "      <td>647</td>\n",
       "      <td>25\\%</td>\n",
       "      <td>64\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bengaluru</td>\n",
       "      <td>537</td>\n",
       "      <td>11\\%</td>\n",
       "      <td>64\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>kolkata</td>\n",
       "      <td>488</td>\n",
       "      <td>39\\%</td>\n",
       "      <td>64\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>chennai</td>\n",
       "      <td>488</td>\n",
       "      <td>23\\%</td>\n",
       "      <td>81\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>nagpur</td>\n",
       "      <td>471</td>\n",
       "      <td>20\\%</td>\n",
       "      <td>58\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>pune</td>\n",
       "      <td>396</td>\n",
       "      <td>34\\%</td>\n",
       "      <td>76\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>hyderabad</td>\n",
       "      <td>386</td>\n",
       "      <td>29\\%</td>\n",
       "      <td>74\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>noida</td>\n",
       "      <td>368</td>\n",
       "      <td>66\\%</td>\n",
       "      <td>94\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>lucknow</td>\n",
       "      <td>320</td>\n",
       "      <td>66\\%</td>\n",
       "      <td>95\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>patna</td>\n",
       "      <td>317</td>\n",
       "      <td>69\\%</td>\n",
       "      <td>95\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>agra</td>\n",
       "      <td>88</td>\n",
       "      <td>61\\%</td>\n",
       "      <td>94\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>kanpur</td>\n",
       "      <td>56</td>\n",
       "      <td>63\\%</td>\n",
       "      <td>93\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>faridabad</td>\n",
       "      <td>51</td>\n",
       "      <td>68\\%</td>\n",
       "      <td>95\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>varanasi</td>\n",
       "      <td>51</td>\n",
       "      <td>69\\%</td>\n",
       "      <td>92\\%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>amritsar</td>\n",
       "      <td>43</td>\n",
       "      <td>32\\%</td>\n",
       "      <td>85\\%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         city  articles India limit WHO limit\n",
       "0       delhi      5285        81\\%      98\\%\n",
       "1    gurugram       849        71\\%      96\\%\n",
       "2      mumbai       647        25\\%      64\\%\n",
       "3   bengaluru       537        11\\%      64\\%\n",
       "4     kolkata       488        39\\%      64\\%\n",
       "5     chennai       488        23\\%      81\\%\n",
       "6      nagpur       471        20\\%      58\\%\n",
       "7        pune       396        34\\%      76\\%\n",
       "8   hyderabad       386        29\\%      74\\%\n",
       "9       noida       368        66\\%      94\\%\n",
       "10    lucknow       320        66\\%      95\\%\n",
       "11      patna       317        69\\%      95\\%\n",
       "12       agra        88        61\\%      94\\%\n",
       "13     kanpur        56        63\\%      93\\%\n",
       "14  faridabad        51        68\\%      95\\%\n",
       "15   varanasi        51        69\\%      92\\%\n",
       "16   amritsar        43        32\\%      85\\%"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#from helper import geo_cities\n",
    "import pickle\n",
    "geo_cities = pickle.load(open(\"allcities_geo_params\",\"rb\")) #used to get district and state from city used by \n",
    "\n",
    "n = len(df_cross_cities)\n",
    "df_table = pd.DataFrame()\n",
    "df_table[\"India limit\"] = df_cross_cities.apply(lambda x: f\"{int(100*sum(x.dropna()>60)/len(x.dropna()))}\\%\")\n",
    "n = len(df_cross_cities)\n",
    "df_table[\"WHO limit\"] = df_cross_cities.apply(lambda x: f\"{int(100*sum(x.dropna()>25)/len(x.dropna()))}\\%\")\n",
    "df_table= df_table.reset_index()\n",
    "\n",
    "\n",
    "#df_articles = h.get_all_dataframe()\n",
    "df_city_count = get_unique_cities_from_df(df_articles)\n",
    "\n",
    "df_table = df_city_count.merge(df_table,left_on=\"city\",right_on=\"city\").sort_values(\"articles\",ascending=False)\n",
    "#df_table[\"city\"] = df_table[\"city\"].apply(lambda x:x.capitalize())\n",
    "df_table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "177fedb7",
   "metadata": {},
   "source": [
    "## Get start & end date of readings for city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9686da22",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "17 cities\n",
      "delhi\n",
      "2010-02-17 00:00:00\n",
      "gurugram\n",
      "2016-01-22 00:00:00\n",
      "mumbai\n",
      "2014-09-05 00:00:00\n",
      "bengaluru\n",
      "2015-03-20 00:00:00\n",
      "kolkata\n",
      "2018-04-10 00:00:00\n",
      "chennai\n",
      "2015-03-23 00:00:00\n",
      "nagpur\n",
      "2011-05-04 00:00:00\n",
      "pune\n",
      "2015-06-06 00:00:00\n",
      "hyderabad\n",
      "2015-03-31 00:00:00\n",
      "noida\n",
      "2017-07-17 00:00:00\n",
      "lucknow\n",
      "2015-03-18 00:00:00\n",
      "patna\n",
      "2015-10-01 00:00:00\n",
      "agra\n",
      "2015-05-11 00:00:00\n",
      "kanpur\n",
      "2015-05-12 00:00:00\n",
      "faridabad\n",
      "2015-05-20 00:00:00\n",
      "varanasi\n",
      "2014-09-13 00:00:00\n",
      "amritsar\n",
      "2017-02-27 00:00:00\n",
      "Before removing cities which have < 1year data- 17\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>city</th>\n",
       "      <th>state</th>\n",
       "      <th>PM2_5</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>active_days</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>delhi</td>\n",
       "      <td>delhi</td>\n",
       "      <td>142</td>\n",
       "      <td>2010-02-17</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>4078</td>\n",
       "      <td>delhi, delhi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gurugram</td>\n",
       "      <td>haryana</td>\n",
       "      <td>116</td>\n",
       "      <td>2016-01-22</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>1755</td>\n",
       "      <td>gurugram, haryana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mumbai</td>\n",
       "      <td>maharashtra</td>\n",
       "      <td>49</td>\n",
       "      <td>2014-09-05</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2104</td>\n",
       "      <td>mumbai, maharashtra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bengaluru</td>\n",
       "      <td>karnataka</td>\n",
       "      <td>36</td>\n",
       "      <td>2015-03-20</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2122</td>\n",
       "      <td>bengaluru, karnataka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>kolkata</td>\n",
       "      <td>west bengal</td>\n",
       "      <td>64</td>\n",
       "      <td>2018-04-10</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>1080</td>\n",
       "      <td>kolkata, west bengal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>chennai</td>\n",
       "      <td>tamil nadu</td>\n",
       "      <td>47</td>\n",
       "      <td>2015-03-23</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2234</td>\n",
       "      <td>chennai, tamil nadu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>nagpur</td>\n",
       "      <td>maharashtra</td>\n",
       "      <td>41</td>\n",
       "      <td>2011-05-04</td>\n",
       "      <td>2021-06-19</td>\n",
       "      <td>2843</td>\n",
       "      <td>nagpur, maharashtra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>pune</td>\n",
       "      <td>maharashtra</td>\n",
       "      <td>51</td>\n",
       "      <td>2015-06-06</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>1719</td>\n",
       "      <td>pune, maharashtra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>hyderabad</td>\n",
       "      <td>telangana</td>\n",
       "      <td>47</td>\n",
       "      <td>2015-03-31</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2242</td>\n",
       "      <td>hyderabad, telangana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>noida</td>\n",
       "      <td>uttar pradesh</td>\n",
       "      <td>114</td>\n",
       "      <td>2017-07-17</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>1266</td>\n",
       "      <td>noida, uttar pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>lucknow</td>\n",
       "      <td>uttar pradesh</td>\n",
       "      <td>111</td>\n",
       "      <td>2015-03-18</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2141</td>\n",
       "      <td>lucknow, uttar pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>patna</td>\n",
       "      <td>bihar</td>\n",
       "      <td>119</td>\n",
       "      <td>2015-10-01</td>\n",
       "      <td>2021-06-08</td>\n",
       "      <td>1802</td>\n",
       "      <td>patna, bihar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>agra</td>\n",
       "      <td>uttar pradesh</td>\n",
       "      <td>103</td>\n",
       "      <td>2015-05-11</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2000</td>\n",
       "      <td>agra, uttar pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>kanpur</td>\n",
       "      <td>uttar pradesh</td>\n",
       "      <td>113</td>\n",
       "      <td>2015-05-12</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2021</td>\n",
       "      <td>kanpur, uttar pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>faridabad</td>\n",
       "      <td>haryana</td>\n",
       "      <td>120</td>\n",
       "      <td>2015-05-20</td>\n",
       "      <td>2021-06-19</td>\n",
       "      <td>2044</td>\n",
       "      <td>faridabad, haryana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>varanasi</td>\n",
       "      <td>uttar pradesh</td>\n",
       "      <td>114</td>\n",
       "      <td>2014-09-13</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2168</td>\n",
       "      <td>varanasi, uttar pradesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>amritsar</td>\n",
       "      <td>punjab</td>\n",
       "      <td>57</td>\n",
       "      <td>2017-02-27</td>\n",
       "      <td>2021-06-19</td>\n",
       "      <td>1298</td>\n",
       "      <td>amritsar, punjab</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         city          state  PM2_5  start_date    end_date  active_days  \\\n",
       "0       delhi          delhi    142  2010-02-17  2021-06-21         4078   \n",
       "1    gurugram        haryana    116  2016-01-22  2021-06-21         1755   \n",
       "2      mumbai    maharashtra     49  2014-09-05  2021-06-21         2104   \n",
       "3   bengaluru      karnataka     36  2015-03-20  2021-06-21         2122   \n",
       "4     kolkata    west bengal     64  2018-04-10  2021-06-21         1080   \n",
       "5     chennai     tamil nadu     47  2015-03-23  2021-06-21         2234   \n",
       "6      nagpur    maharashtra     41  2011-05-04  2021-06-19         2843   \n",
       "7        pune    maharashtra     51  2015-06-06  2021-06-21         1719   \n",
       "8   hyderabad      telangana     47  2015-03-31  2021-06-21         2242   \n",
       "9       noida  uttar pradesh    114  2017-07-17  2021-06-21         1266   \n",
       "10    lucknow  uttar pradesh    111  2015-03-18  2021-06-21         2141   \n",
       "11      patna          bihar    119  2015-10-01  2021-06-08         1802   \n",
       "12       agra  uttar pradesh    103  2015-05-11  2021-06-21         2000   \n",
       "13     kanpur  uttar pradesh    113  2015-05-12  2021-06-21         2021   \n",
       "14  faridabad        haryana    120  2015-05-20  2021-06-19         2044   \n",
       "15   varanasi  uttar pradesh    114  2014-09-13  2021-06-21         2168   \n",
       "16   amritsar         punjab     57  2017-02-27  2021-06-19         1298   \n",
       "\n",
       "                    address  \n",
       "0              delhi, delhi  \n",
       "1         gurugram, haryana  \n",
       "2       mumbai, maharashtra  \n",
       "3      bengaluru, karnataka  \n",
       "4      kolkata, west bengal  \n",
       "5       chennai, tamil nadu  \n",
       "6       nagpur, maharashtra  \n",
       "7         pune, maharashtra  \n",
       "8      hyderabad, telangana  \n",
       "9      noida, uttar pradesh  \n",
       "10   lucknow, uttar pradesh  \n",
       "11             patna, bihar  \n",
       "12      agra, uttar pradesh  \n",
       "13    kanpur, uttar pradesh  \n",
       "14       faridabad, haryana  \n",
       "15  varanasi, uttar pradesh  \n",
       "16         amritsar, punjab  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "air_pollutant = \"PM2_5\"\n",
    "\n",
    "end_date = datetime(2021,6,21)\n",
    "data = []\n",
    "#cities = df_daily_city_merged.city.unique()\n",
    "#cities = igp_cities\n",
    "\n",
    "#get top discussed cities\n",
    "#cities_art = get_unique_cities_from_df(df_articles).nlargest(15,\"articles\")[\"city\"]\n",
    "\n",
    "#intersection of articles cities and pm2.5 availability cities\n",
    "# cities_art = get_unique_cities_from_df(df_articles)[\"city\"]\n",
    "# cities_pm = df_daily_city_merged.city.unique()\n",
    "\n",
    "# cities = set(cities_art).intersection(cities_pm)\n",
    "\n",
    "cities = df_table[\"city\"].values\n",
    "not_found = []\n",
    "print(len(cities),\"cities\")\n",
    "for fname in cities:\n",
    "    \n",
    "    #df = pd.read_csv(all_cities_path+fname)\n",
    "    fname = fname.lower()\n",
    "    df = df_daily_city_merged[df_daily_city_merged.city == fname]\n",
    "    \n",
    "    \n",
    "    try:\n",
    "        df[\"From Date\"] = pd.to_datetime(df[\"From Date\"])\n",
    "        \n",
    "        start_date = get_start_date(fname)\n",
    "        #start_date = datetime(2015,6,1)\n",
    "        df = df[(df[\"From Date\"]>=start_date) & (df[\"From Date\"]<=end_date)] #subset\n",
    "        #df = df[(df[\"From Date\"]<=start_date) & (df[\"From Date\"]>=end_date)] #strictly have start_date & end_date\n",
    "        \n",
    "        data.append({\"city\":df[\"city\"].unique()[0],\n",
    "                     \"state\":df[\"state\"].unique()[0],\n",
    "\n",
    "                    \"PM2_5\":int(round(df[air_pollutant].mean(),2)),\n",
    "                    \"start_date\":df.dropna(subset=[\"PM2_5\"])[\"From Date\"].min().date(), #start date when first reading taken\n",
    "                    \"end_date\":df.dropna(subset=[\"PM2_5\"])[\"From Date\"].max().date(), #end date when last reading taken\n",
    "                    \"active_days\":len(df[~pd.isna(df[air_pollutant])])})\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        print(fname, \"not found\")\n",
    "        not_found.append(fname)\n",
    "\n",
    "df_avg_pm2_5 = pd.DataFrame(data)\n",
    "df_avg_pm2_5.dropna(subset=[\"PM2_5\"],inplace=True)\n",
    "df_avg_pm2_5[\"address\"] = df_avg_pm2_5.apply(lambda x:f\"{x.city}, {x.state}\",axis=1)\n",
    "print(f\"Before removing cities which have < 1year data- {len(df_avg_pm2_5)}\")\n",
    "\n",
    "df_avg_pm2_5 = df_avg_pm2_5[df_avg_pm2_5[\"active_days\"]>365] #remove the cities which has data less than 1 year\n",
    "df_avg_pm2_5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "283fd2d2",
   "metadata": {},
   "source": [
    "## Create Table showing WHO & India limit along with articles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "307a5c6b",
   "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>city</th>\n",
       "      <th>PM2_5</th>\n",
       "      <th>India limit</th>\n",
       "      <th>WHO limit</th>\n",
       "      <th>articles</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>delhi</td>\n",
       "      <td>142</td>\n",
       "      <td>81\\%</td>\n",
       "      <td>98\\%</td>\n",
       "      <td>5285</td>\n",
       "      <td>2010-02-17</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gurugram</td>\n",
       "      <td>116</td>\n",
       "      <td>71\\%</td>\n",
       "      <td>96\\%</td>\n",
       "      <td>849</td>\n",
       "      <td>2016-01-22</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mumbai</td>\n",
       "      <td>49</td>\n",
       "      <td>25\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>647</td>\n",
       "      <td>2014-09-05</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bengaluru</td>\n",
       "      <td>36</td>\n",
       "      <td>11\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>537</td>\n",
       "      <td>2015-03-20</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>kolkata</td>\n",
       "      <td>64</td>\n",
       "      <td>39\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>488</td>\n",
       "      <td>2018-04-10</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>chennai</td>\n",
       "      <td>47</td>\n",
       "      <td>23\\%</td>\n",
       "      <td>81\\%</td>\n",
       "      <td>488</td>\n",
       "      <td>2015-03-23</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>nagpur</td>\n",
       "      <td>41</td>\n",
       "      <td>20\\%</td>\n",
       "      <td>58\\%</td>\n",
       "      <td>471</td>\n",
       "      <td>2011-05-04</td>\n",
       "      <td>2021-06-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>pune</td>\n",
       "      <td>51</td>\n",
       "      <td>34\\%</td>\n",
       "      <td>76\\%</td>\n",
       "      <td>396</td>\n",
       "      <td>2015-06-06</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>hyderabad</td>\n",
       "      <td>47</td>\n",
       "      <td>29\\%</td>\n",
       "      <td>74\\%</td>\n",
       "      <td>386</td>\n",
       "      <td>2015-03-31</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>noida</td>\n",
       "      <td>114</td>\n",
       "      <td>66\\%</td>\n",
       "      <td>94\\%</td>\n",
       "      <td>368</td>\n",
       "      <td>2017-07-17</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>lucknow</td>\n",
       "      <td>111</td>\n",
       "      <td>66\\%</td>\n",
       "      <td>95\\%</td>\n",
       "      <td>320</td>\n",
       "      <td>2015-03-18</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>patna</td>\n",
       "      <td>119</td>\n",
       "      <td>69\\%</td>\n",
       "      <td>95\\%</td>\n",
       "      <td>317</td>\n",
       "      <td>2015-10-01</td>\n",
       "      <td>2021-06-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>agra</td>\n",
       "      <td>103</td>\n",
       "      <td>61\\%</td>\n",
       "      <td>94\\%</td>\n",
       "      <td>88</td>\n",
       "      <td>2015-05-11</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>kanpur</td>\n",
       "      <td>113</td>\n",
       "      <td>63\\%</td>\n",
       "      <td>93\\%</td>\n",
       "      <td>56</td>\n",
       "      <td>2015-05-12</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>faridabad</td>\n",
       "      <td>120</td>\n",
       "      <td>68\\%</td>\n",
       "      <td>95\\%</td>\n",
       "      <td>51</td>\n",
       "      <td>2015-05-20</td>\n",
       "      <td>2021-06-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>varanasi</td>\n",
       "      <td>114</td>\n",
       "      <td>69\\%</td>\n",
       "      <td>92\\%</td>\n",
       "      <td>51</td>\n",
       "      <td>2014-09-13</td>\n",
       "      <td>2021-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>amritsar</td>\n",
       "      <td>57</td>\n",
       "      <td>32\\%</td>\n",
       "      <td>85\\%</td>\n",
       "      <td>43</td>\n",
       "      <td>2017-02-27</td>\n",
       "      <td>2021-06-19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         city  PM2_5 India limit WHO limit  articles  start_date    end_date\n",
       "0       delhi    142        81\\%      98\\%      5285  2010-02-17  2021-06-21\n",
       "1    gurugram    116        71\\%      96\\%       849  2016-01-22  2021-06-21\n",
       "2      mumbai     49        25\\%      64\\%       647  2014-09-05  2021-06-21\n",
       "3   bengaluru     36        11\\%      64\\%       537  2015-03-20  2021-06-21\n",
       "4     kolkata     64        39\\%      64\\%       488  2018-04-10  2021-06-21\n",
       "5     chennai     47        23\\%      81\\%       488  2015-03-23  2021-06-21\n",
       "6      nagpur     41        20\\%      58\\%       471  2011-05-04  2021-06-19\n",
       "7        pune     51        34\\%      76\\%       396  2015-06-06  2021-06-21\n",
       "8   hyderabad     47        29\\%      74\\%       386  2015-03-31  2021-06-21\n",
       "9       noida    114        66\\%      94\\%       368  2017-07-17  2021-06-21\n",
       "10    lucknow    111        66\\%      95\\%       320  2015-03-18  2021-06-21\n",
       "11      patna    119        69\\%      95\\%       317  2015-10-01  2021-06-08\n",
       "12       agra    103        61\\%      94\\%        88  2015-05-11  2021-06-21\n",
       "13     kanpur    113        63\\%      93\\%        56  2015-05-12  2021-06-21\n",
       "14  faridabad    120        68\\%      95\\%        51  2015-05-20  2021-06-19\n",
       "15   varanasi    114        69\\%      92\\%        51  2014-09-13  2021-06-21\n",
       "16   amritsar     57        32\\%      85\\%        43  2017-02-27  2021-06-19"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_table = df_table.merge(df_avg_pm2_5,left_on=\"city\",right_on=\"city\")[[\"city\",\"PM2_5\",\"India limit\",\"WHO limit\",\"articles\",\"start_date\",\"end_date\"]]\n",
    "df_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "813d8d08",
   "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>city</th>\n",
       "      <th>PM2_5</th>\n",
       "      <th>India limit</th>\n",
       "      <th>WHO limit</th>\n",
       "      <th>articles</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>Time-span</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Delhi</td>\n",
       "      <td>142</td>\n",
       "      <td>81\\%</td>\n",
       "      <td>98\\%</td>\n",
       "      <td>5285</td>\n",
       "      <td>2010-02-17</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>2/10 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gurugram</td>\n",
       "      <td>116</td>\n",
       "      <td>71\\%</td>\n",
       "      <td>96\\%</td>\n",
       "      <td>849</td>\n",
       "      <td>2016-01-22</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>1/16 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mumbai</td>\n",
       "      <td>49</td>\n",
       "      <td>25\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>647</td>\n",
       "      <td>2014-09-05</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>9/14 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>36</td>\n",
       "      <td>11\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>537</td>\n",
       "      <td>2015-03-20</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>3/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kolkata</td>\n",
       "      <td>64</td>\n",
       "      <td>39\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>488</td>\n",
       "      <td>2018-04-10</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>4/18 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chennai</td>\n",
       "      <td>47</td>\n",
       "      <td>23\\%</td>\n",
       "      <td>81\\%</td>\n",
       "      <td>488</td>\n",
       "      <td>2015-03-23</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>3/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Nagpur</td>\n",
       "      <td>41</td>\n",
       "      <td>20\\%</td>\n",
       "      <td>58\\%</td>\n",
       "      <td>471</td>\n",
       "      <td>2011-05-04</td>\n",
       "      <td>2021-06-19</td>\n",
       "      <td>5/11 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Pune</td>\n",
       "      <td>51</td>\n",
       "      <td>34\\%</td>\n",
       "      <td>76\\%</td>\n",
       "      <td>396</td>\n",
       "      <td>2015-06-06</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>6/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Hyderabad</td>\n",
       "      <td>47</td>\n",
       "      <td>29\\%</td>\n",
       "      <td>74\\%</td>\n",
       "      <td>386</td>\n",
       "      <td>2015-03-31</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>3/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Noida</td>\n",
       "      <td>114</td>\n",
       "      <td>66\\%</td>\n",
       "      <td>94\\%</td>\n",
       "      <td>368</td>\n",
       "      <td>2017-07-17</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>7/17 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Lucknow</td>\n",
       "      <td>111</td>\n",
       "      <td>66\\%</td>\n",
       "      <td>95\\%</td>\n",
       "      <td>320</td>\n",
       "      <td>2015-03-18</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>3/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Patna</td>\n",
       "      <td>119</td>\n",
       "      <td>69\\%</td>\n",
       "      <td>95\\%</td>\n",
       "      <td>317</td>\n",
       "      <td>2015-10-01</td>\n",
       "      <td>2021-06-08</td>\n",
       "      <td>10/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Agra</td>\n",
       "      <td>103</td>\n",
       "      <td>61\\%</td>\n",
       "      <td>94\\%</td>\n",
       "      <td>88</td>\n",
       "      <td>2015-05-11</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>5/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Kanpur</td>\n",
       "      <td>113</td>\n",
       "      <td>63\\%</td>\n",
       "      <td>93\\%</td>\n",
       "      <td>56</td>\n",
       "      <td>2015-05-12</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>5/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Faridabad</td>\n",
       "      <td>120</td>\n",
       "      <td>68\\%</td>\n",
       "      <td>95\\%</td>\n",
       "      <td>51</td>\n",
       "      <td>2015-05-20</td>\n",
       "      <td>2021-06-19</td>\n",
       "      <td>5/15 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Varanasi</td>\n",
       "      <td>114</td>\n",
       "      <td>69\\%</td>\n",
       "      <td>92\\%</td>\n",
       "      <td>51</td>\n",
       "      <td>2014-09-13</td>\n",
       "      <td>2021-06-21</td>\n",
       "      <td>9/14 - 6/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Amritsar</td>\n",
       "      <td>57</td>\n",
       "      <td>32\\%</td>\n",
       "      <td>85\\%</td>\n",
       "      <td>43</td>\n",
       "      <td>2017-02-27</td>\n",
       "      <td>2021-06-19</td>\n",
       "      <td>2/17 - 6/21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         city  PM2_5 India limit WHO limit  articles  start_date    end_date  \\\n",
       "0       Delhi    142        81\\%      98\\%      5285  2010-02-17  2021-06-21   \n",
       "1    Gurugram    116        71\\%      96\\%       849  2016-01-22  2021-06-21   \n",
       "2      Mumbai     49        25\\%      64\\%       647  2014-09-05  2021-06-21   \n",
       "3   Bengaluru     36        11\\%      64\\%       537  2015-03-20  2021-06-21   \n",
       "4     Kolkata     64        39\\%      64\\%       488  2018-04-10  2021-06-21   \n",
       "5     Chennai     47        23\\%      81\\%       488  2015-03-23  2021-06-21   \n",
       "6      Nagpur     41        20\\%      58\\%       471  2011-05-04  2021-06-19   \n",
       "7        Pune     51        34\\%      76\\%       396  2015-06-06  2021-06-21   \n",
       "8   Hyderabad     47        29\\%      74\\%       386  2015-03-31  2021-06-21   \n",
       "9       Noida    114        66\\%      94\\%       368  2017-07-17  2021-06-21   \n",
       "10    Lucknow    111        66\\%      95\\%       320  2015-03-18  2021-06-21   \n",
       "11      Patna    119        69\\%      95\\%       317  2015-10-01  2021-06-08   \n",
       "12       Agra    103        61\\%      94\\%        88  2015-05-11  2021-06-21   \n",
       "13     Kanpur    113        63\\%      93\\%        56  2015-05-12  2021-06-21   \n",
       "14  Faridabad    120        68\\%      95\\%        51  2015-05-20  2021-06-19   \n",
       "15   Varanasi    114        69\\%      92\\%        51  2014-09-13  2021-06-21   \n",
       "16   Amritsar     57        32\\%      85\\%        43  2017-02-27  2021-06-19   \n",
       "\n",
       "       Time-span  \n",
       "0    2/10 - 6/21  \n",
       "1    1/16 - 6/21  \n",
       "2    9/14 - 6/21  \n",
       "3    3/15 - 6/21  \n",
       "4    4/18 - 6/21  \n",
       "5    3/15 - 6/21  \n",
       "6    5/11 - 6/21  \n",
       "7    6/15 - 6/21  \n",
       "8    3/15 - 6/21  \n",
       "9    7/17 - 6/21  \n",
       "10   3/15 - 6/21  \n",
       "11  10/15 - 6/21  \n",
       "12   5/15 - 6/21  \n",
       "13   5/15 - 6/21  \n",
       "14   5/15 - 6/21  \n",
       "15   9/14 - 6/21  \n",
       "16   2/17 - 6/21  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "def datetime_to_3charmonth_format(date):\n",
    "    s = date\n",
    "    # d = datetime.strptime(s, '%d %B, %Y')\n",
    "    #mon,y = s.strftime('%B-%Y').split(\"-\")\n",
    "    #mon = mon.capitalize()[:3]\n",
    "    \n",
    "    mon,y = date.month, date.year\n",
    "    y = str(y)[-2:]\n",
    "    if mon <10:\n",
    "        mon = str(mon)\n",
    "    #print(mon,y)\n",
    "    return f'{mon}/{y}'\n",
    "\n",
    "df_table[\"Time-span\"] = df_table.apply(lambda x: datetime_to_3charmonth_format(x.start_date)+\" - \"+ \n",
    "                                       datetime_to_3charmonth_format(x.end_date),axis=1) \n",
    "\n",
    "df_table[\"city\"] = df_table[\"city\"].apply(lambda x:x.capitalize())\n",
    "df_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "179e9c37",
   "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>City</th>\n",
       "      <th>Average PM$_{2.5}$ ($\\mu g/m^3$)</th>\n",
       "      <th>India limit</th>\n",
       "      <th>WHO limit</th>\n",
       "      <th>Time-span(PM$_{2.5}$)</th>\n",
       "      <th>Articles(2010-21)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Delhi</td>\n",
       "      <td>142</td>\n",
       "      <td>81\\%</td>\n",
       "      <td>98\\%</td>\n",
       "      <td>2/10 - 6/21</td>\n",
       "      <td>5285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gurugram</td>\n",
       "      <td>116</td>\n",
       "      <td>71\\%</td>\n",
       "      <td>96\\%</td>\n",
       "      <td>1/16 - 6/21</td>\n",
       "      <td>849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mumbai</td>\n",
       "      <td>49</td>\n",
       "      <td>25\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>9/14 - 6/21</td>\n",
       "      <td>647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>36</td>\n",
       "      <td>11\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>3/15 - 6/21</td>\n",
       "      <td>537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kolkata</td>\n",
       "      <td>64</td>\n",
       "      <td>39\\%</td>\n",
       "      <td>64\\%</td>\n",
       "      <td>4/18 - 6/21</td>\n",
       "      <td>488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chennai</td>\n",
       "      <td>47</td>\n",
       "      <td>23\\%</td>\n",
       "      <td>81\\%</td>\n",
       "      <td>3/15 - 6/21</td>\n",
       "      <td>488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Nagpur</td>\n",
       "      <td>41</td>\n",
       "      <td>20\\%</td>\n",
       "      <td>58\\%</td>\n",
       "      <td>5/11 - 6/21</td>\n",
       "      <td>471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Pune</td>\n",
       "      <td>51</td>\n",
       "      <td>34\\%</td>\n",
       "      <td>76\\%</td>\n",
       "      <td>6/15 - 6/21</td>\n",
       "      <td>396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Hyderabad</td>\n",
       "      <td>47</td>\n",
       "      <td>29\\%</td>\n",
       "      <td>74\\%</td>\n",
       "      <td>3/15 - 6/21</td>\n",
       "      <td>386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Noida</td>\n",
       "      <td>114</td>\n",
       "      <td>66\\%</td>\n",
       "      <td>94\\%</td>\n",
       "      <td>7/17 - 6/21</td>\n",
       "      <td>368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Lucknow</td>\n",
       "      <td>111</td>\n",
       "      <td>66\\%</td>\n",
       "      <td>95\\%</td>\n",
       "      <td>3/15 - 6/21</td>\n",
       "      <td>320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Patna</td>\n",
       "      <td>119</td>\n",
       "      <td>69\\%</td>\n",
       "      <td>94\\%</td>\n",
       "      <td>10/15 - 6/21</td>\n",
       "      <td>317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Agra</td>\n",
       "      <td>103</td>\n",
       "      <td>61\\%</td>\n",
       "      <td>94\\%</td>\n",
       "      <td>5/15 - 6/21</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Kanpur</td>\n",
       "      <td>113</td>\n",
       "      <td>63\\%</td>\n",
       "      <td>93\\%</td>\n",
       "      <td>5/15 - 6/21</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Faridabad</td>\n",
       "      <td>120</td>\n",
       "      <td>68\\%</td>\n",
       "      <td>95\\%</td>\n",
       "      <td>5/15 - 6/21</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Varanasi</td>\n",
       "      <td>114</td>\n",
       "      <td>69\\%</td>\n",
       "      <td>92\\%</td>\n",
       "      <td>9/14 - 6/21</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Amritsar</td>\n",
       "      <td>57</td>\n",
       "      <td>32\\%</td>\n",
       "      <td>85\\%</td>\n",
       "      <td>2/17 - 6/21</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         City  Average PM$_{2.5}$ ($\\mu g/m^3$) India limit WHO limit  \\\n",
       "0       Delhi                               142        81\\%      98\\%   \n",
       "1    Gurugram                               116        71\\%      96\\%   \n",
       "2      Mumbai                                49        25\\%      64\\%   \n",
       "3   Bengaluru                                36        11\\%      64\\%   \n",
       "4     Kolkata                                64        39\\%      64\\%   \n",
       "5     Chennai                                47        23\\%      81\\%   \n",
       "6      Nagpur                                41        20\\%      58\\%   \n",
       "7        Pune                                51        34\\%      76\\%   \n",
       "8   Hyderabad                                47        29\\%      74\\%   \n",
       "9       Noida                               114        66\\%      94\\%   \n",
       "10    Lucknow                               111        66\\%      95\\%   \n",
       "11      Patna                               119        69\\%      94\\%   \n",
       "12       Agra                               103        61\\%      94\\%   \n",
       "13     Kanpur                               113        63\\%      93\\%   \n",
       "14  Faridabad                               120        68\\%      95\\%   \n",
       "15   Varanasi                               114        69\\%      92\\%   \n",
       "16   Amritsar                                57        32\\%      85\\%   \n",
       "\n",
       "   Time-span(PM$_{2.5}$)  Articles(2010-21)  \n",
       "0            2/10 - 6/21               5285  \n",
       "1            1/16 - 6/21                849  \n",
       "2            9/14 - 6/21                647  \n",
       "3            3/15 - 6/21                537  \n",
       "4            4/18 - 6/21                488  \n",
       "5            3/15 - 6/21                488  \n",
       "6            5/11 - 6/21                471  \n",
       "7            6/15 - 6/21                396  \n",
       "8            3/15 - 6/21                386  \n",
       "9            7/17 - 6/21                368  \n",
       "10           3/15 - 6/21                320  \n",
       "11          10/15 - 6/21                317  \n",
       "12           5/15 - 6/21                 88  \n",
       "13           5/15 - 6/21                 56  \n",
       "14           5/15 - 6/21                 51  \n",
       "15           9/14 - 6/21                 51  \n",
       "16           2/17 - 6/21                 43  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = [\"city\",\"PM2_5\",\"India limit\",\"WHO limit\",\"articles\",\"Time-span\"]\n",
    "\n",
    "df_final = df_table[cols]\n",
    "df_final.insert(5, 'Articles', df_final.pop(\"articles\"))\n",
    "df_final.rename(columns={\"city\":\"City\",\n",
    "                        \"PM2_5\":\"Average PM$_{2.5}$ ($\\mu g/m^3$)\",\n",
    "                        \"Articles\":\"Articles(2010-21)\",\n",
    "                        \"Time-span\":\"Time-span(PM$_{2.5}$)\"},inplace=True)\n",
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "465ff2fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_final.to_csv(\"files/table_India_WHO_articles_IG_and_Discussed_cities.csv\",index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:NewsAQ38] *",
   "language": "python",
   "name": "conda-env-NewsAQ38-py"
  },
  "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}