web_scraping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "# ================\n",
    "\n",
    "# for date and time opeations\n",
    "from datetime import datetime\n",
    "# for file and folder operations\n",
    "import os\n",
    "# for regular expression opeations\n",
    "import re\n",
    "# for listing files in a folder\n",
    "import glob\n",
    "# for getting web contents\n",
    "import requests \n",
    "# storing and analysing data\n",
    "import pandas as pd\n",
    "# for scraping web contents\n",
    "from bs4 import BeautifulSoup\n",
    "# regular expression\n",
    "import re\n",
    "# for numerical analysis\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Web Scrapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get data\n",
    "# ========\n",
    "\n",
    "# link at which web data recides\n",
    "link = 'https://www.mohfw.gov.in/'\n",
    "# get web data\n",
    "req = requests.get(link)\n",
    "# parse web data\n",
    "soup = BeautifulSoup(req.content, \"html.parser\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# find the table\n",
    "# ==============\n",
    "# our target table is the last table in the page\n",
    "\n",
    "# get the table head\n",
    "# table head may contain the column names, titles, subtitles\n",
    "thead = soup.find_all('thead')[-1]\n",
    "# print(thead)\n",
    "\n",
    "# get all the rows in table head\n",
    "# it usually have only one row, which has the column names\n",
    "head = thead.find_all('tr')\n",
    "# print(head)\n",
    "\n",
    "# get the table tbody\n",
    "# it contains the contents\n",
    "tbody = soup.find_all('tbody')[-1]\n",
    "# print(tbody)\n",
    "\n",
    "# get all the rows in table body\n",
    "# each row is each state's entry\n",
    "body = tbody.find_all('tr')\n",
    "# print(body)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the table contents\n",
    "# ======================\n",
    "\n",
    "# container for header rows / column title\n",
    "head_rows = []\n",
    "# container for table body / contents\n",
    "body_rows = []\n",
    "\n",
    "# loop through the head and append each row to head\n",
    "for tr in head:\n",
    "    td = tr.find_all(['th', 'td'])\n",
    "    row = [i.text for i in td]\n",
    "    head_rows.append(row)\n",
    "# print(head_rows)\n",
    "\n",
    "# loop through the body and append each row to body\n",
    "for tr in body:\n",
    "    td = tr.find_all(['th', 'td'])\n",
    "    row = [i.text for i in td]\n",
    "    body_rows.append(row)\n",
    "# print(head_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name of State / UT</th>\n",
       "      <th>Active Cases*</th>\n",
       "      <th>Cured/Discharged/Migrated*</th>\n",
       "      <th>Deaths**</th>\n",
       "      <th>Total Confirmed cases*</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman and Nicobar Islands</td>\n",
       "      <td>57</td>\n",
       "      <td>109</td>\n",
       "      <td>0</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>14274</td>\n",
       "      <td>16464</td>\n",
       "      <td>365</td>\n",
       "      <td>31103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>240</td>\n",
       "      <td>145</td>\n",
       "      <td>2</td>\n",
       "      <td>387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Assam</td>\n",
       "      <td>5876</td>\n",
       "      <td>10894</td>\n",
       "      <td>36</td>\n",
       "      <td>16806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bihar</td>\n",
       "      <td>5482</td>\n",
       "      <td>12317</td>\n",
       "      <td>160</td>\n",
       "      <td>17959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chandigarh</td>\n",
       "      <td>157</td>\n",
       "      <td>423</td>\n",
       "      <td>8</td>\n",
       "      <td>588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Chhattisgarh</td>\n",
       "      <td>996</td>\n",
       "      <td>3202</td>\n",
       "      <td>19</td>\n",
       "      <td>4217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Dadra and Nagar Haveli and Daman and Diu</td>\n",
       "      <td>226</td>\n",
       "      <td>268</td>\n",
       "      <td>1</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Delhi</td>\n",
       "      <td>19017</td>\n",
       "      <td>91312</td>\n",
       "      <td>3411</td>\n",
       "      <td>113740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1026</td>\n",
       "      <td>1540</td>\n",
       "      <td>17</td>\n",
       "      <td>2583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Gujarat</td>\n",
       "      <td>10897</td>\n",
       "      <td>29770</td>\n",
       "      <td>2055</td>\n",
       "      <td>42722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Haryana</td>\n",
       "      <td>4984</td>\n",
       "      <td>16602</td>\n",
       "      <td>308</td>\n",
       "      <td>21894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Himachal Pradesh</td>\n",
       "      <td>292</td>\n",
       "      <td>940</td>\n",
       "      <td>11</td>\n",
       "      <td>1243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Jammu and Kashmir</td>\n",
       "      <td>4545</td>\n",
       "      <td>6095</td>\n",
       "      <td>187</td>\n",
       "      <td>10827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Jharkhand</td>\n",
       "      <td>1514</td>\n",
       "      <td>2351</td>\n",
       "      <td>33</td>\n",
       "      <td>3898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Karnataka</td>\n",
       "      <td>24576</td>\n",
       "      <td>16248</td>\n",
       "      <td>757</td>\n",
       "      <td>41581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Kerala</td>\n",
       "      <td>4032</td>\n",
       "      <td>4257</td>\n",
       "      <td>33</td>\n",
       "      <td>8322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ladakh</td>\n",
       "      <td>146</td>\n",
       "      <td>946</td>\n",
       "      <td>1</td>\n",
       "      <td>1093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Madhya Pradesh</td>\n",
       "      <td>4336</td>\n",
       "      <td>13208</td>\n",
       "      <td>663</td>\n",
       "      <td>18207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Maharashtra</td>\n",
       "      <td>105935</td>\n",
       "      <td>144507</td>\n",
       "      <td>10482</td>\n",
       "      <td>260924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Manipur</td>\n",
       "      <td>656</td>\n",
       "      <td>970</td>\n",
       "      <td>0</td>\n",
       "      <td>1626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Meghalaya</td>\n",
       "      <td>250</td>\n",
       "      <td>66</td>\n",
       "      <td>2</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Mizoram</td>\n",
       "      <td>82</td>\n",
       "      <td>151</td>\n",
       "      <td>0</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Nagaland</td>\n",
       "      <td>505</td>\n",
       "      <td>340</td>\n",
       "      <td>0</td>\n",
       "      <td>845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Odisha</td>\n",
       "      <td>4412</td>\n",
       "      <td>9255</td>\n",
       "      <td>70</td>\n",
       "      <td>13737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Puducherry</td>\n",
       "      <td>665</td>\n",
       "      <td>785</td>\n",
       "      <td>18</td>\n",
       "      <td>1468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Punjab</td>\n",
       "      <td>2388</td>\n",
       "      <td>5586</td>\n",
       "      <td>204</td>\n",
       "      <td>8178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Rajasthan</td>\n",
       "      <td>5781</td>\n",
       "      <td>18630</td>\n",
       "      <td>525</td>\n",
       "      <td>24936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Sikkim</td>\n",
       "      <td>106</td>\n",
       "      <td>86</td>\n",
       "      <td>0</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Tamil Nadu</td>\n",
       "      <td>48199</td>\n",
       "      <td>92567</td>\n",
       "      <td>2032</td>\n",
       "      <td>142798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Telangana</td>\n",
       "      <td>12177</td>\n",
       "      <td>23679</td>\n",
       "      <td>365</td>\n",
       "      <td>36221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Tripura</td>\n",
       "      <td>603</td>\n",
       "      <td>1475</td>\n",
       "      <td>2</td>\n",
       "      <td>2080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Uttarakhand</td>\n",
       "      <td>703</td>\n",
       "      <td>2856</td>\n",
       "      <td>49</td>\n",
       "      <td>3608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Uttar Pradesh</td>\n",
       "      <td>12972</td>\n",
       "      <td>24203</td>\n",
       "      <td>955</td>\n",
       "      <td>38130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>11279</td>\n",
       "      <td>19213</td>\n",
       "      <td>956</td>\n",
       "      <td>31448</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Name of State / UT Active Cases*  \\\n",
       "0                Andaman and Nicobar Islands            57   \n",
       "1                             Andhra Pradesh         14274   \n",
       "2                          Arunachal Pradesh           240   \n",
       "3                                      Assam          5876   \n",
       "4                                      Bihar          5482   \n",
       "5                                 Chandigarh           157   \n",
       "6                               Chhattisgarh           996   \n",
       "7   Dadra and Nagar Haveli and Daman and Diu           226   \n",
       "8                                      Delhi         19017   \n",
       "9                                        Goa          1026   \n",
       "10                                   Gujarat         10897   \n",
       "11                                   Haryana          4984   \n",
       "12                          Himachal Pradesh           292   \n",
       "13                         Jammu and Kashmir          4545   \n",
       "14                                 Jharkhand          1514   \n",
       "15                                 Karnataka         24576   \n",
       "16                                    Kerala          4032   \n",
       "17                                    Ladakh           146   \n",
       "18                            Madhya Pradesh          4336   \n",
       "19                               Maharashtra        105935   \n",
       "20                                   Manipur           656   \n",
       "21                                 Meghalaya           250   \n",
       "22                                   Mizoram            82   \n",
       "23                                  Nagaland           505   \n",
       "24                                    Odisha          4412   \n",
       "25                                Puducherry           665   \n",
       "26                                    Punjab          2388   \n",
       "27                                 Rajasthan          5781   \n",
       "28                                    Sikkim           106   \n",
       "29                                Tamil Nadu         48199   \n",
       "30                                 Telangana         12177   \n",
       "31                                   Tripura           603   \n",
       "32                               Uttarakhand           703   \n",
       "33                             Uttar Pradesh         12972   \n",
       "34                               West Bengal         11279   \n",
       "\n",
       "   Cured/Discharged/Migrated* Deaths** Total Confirmed cases*  \n",
       "0                         109        0                    166  \n",
       "1                       16464      365                  31103  \n",
       "2                         145        2                    387  \n",
       "3                       10894       36                  16806  \n",
       "4                       12317      160                  17959  \n",
       "5                         423        8                    588  \n",
       "6                        3202       19                   4217  \n",
       "7                         268        1                    495  \n",
       "8                       91312     3411                 113740  \n",
       "9                        1540       17                   2583  \n",
       "10                      29770     2055                  42722  \n",
       "11                      16602      308                  21894  \n",
       "12                        940       11                   1243  \n",
       "13                       6095      187                  10827  \n",
       "14                       2351       33                   3898  \n",
       "15                      16248      757                  41581  \n",
       "16                       4257       33                   8322  \n",
       "17                        946        1                   1093  \n",
       "18                      13208      663                  18207  \n",
       "19                     144507    10482                 260924  \n",
       "20                        970        0                   1626  \n",
       "21                         66        2                    318  \n",
       "22                        151        0                    233  \n",
       "23                        340        0                    845  \n",
       "24                       9255       70                  13737  \n",
       "25                        785       18                   1468  \n",
       "26                       5586      204                   8178  \n",
       "27                      18630      525                  24936  \n",
       "28                         86        0                    192  \n",
       "29                      92567     2032                 142798  \n",
       "30                      23679      365                  36221  \n",
       "31                       1475        2                   2080  \n",
       "32                       2856       49                   3608  \n",
       "33                      24203      955                  38130  \n",
       "34                      19213      956                  31448  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# save contents in a dataframe\n",
    "# ============================\n",
    "    \n",
    "# skip last 3 rows, it contains unwanted info\n",
    "# head_rows contains column title\n",
    "df_bs = pd.DataFrame(body_rows[:len(body_rows)-6], \n",
    "                     columns=head_rows[0])         \n",
    "\n",
    "# Drop 'S. No.' column\n",
    "df_bs.drop('S. No.', axis=1, inplace=True)\n",
    "\n",
    "# there are 36 states+UT in India\n",
    "df_bs.head(36)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# date-time information\n",
    "# =====================\n",
    "\n",
    "# today's date\n",
    "now  = datetime.now()\n",
    "# format date to month-day-year\n",
    "df_bs['Date'] = now.strftime(\"%m/%d/%Y\") \n",
    "\n",
    "# add 'Date' column to dataframe\n",
    "df_bs['Date'] = pd.to_datetime(df_bs['Date'], format='%m/%d/%Y')\n",
    "\n",
    "# df_bs.head(36)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove extra characters from 'Name of State/UT' column\n",
    "df_bs['Name of State / UT'] = df_bs['Name of State / UT'].str.replace('#', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# latitude and longitude information\n",
    "# ==================================\n",
    "\n",
    "# latitude of the states\n",
    "lat = {'Delhi':28.7041, 'Haryana':29.0588, 'Kerala':10.8505, 'Rajasthan':27.0238,\n",
    "       'Telengana':18.1124, 'Telangana':18.1124, 'Uttar Pradesh':26.8467, 'Ladakh':34.2996, 'Tamil Nadu':11.1271,\n",
    "       'Jammu and Kashmir':33.7782, 'Punjab':31.1471, 'Karnataka':15.3173, 'Maharashtra':19.7515,\n",
    "       'Andhra Pradesh':15.9129, 'Odisha':20.9517, 'Uttarakhand':30.0668, 'West Bengal':22.9868, \n",
    "       'Puducherry': 11.9416, 'Chandigarh': 30.7333, 'Chhattisgarh':21.2787, 'Gujarat': 22.2587, \n",
    "       'Himachal Pradesh': 31.1048, 'Madhya Pradesh': 22.9734, 'Bihar': 25.0961, 'Manipur':24.6637, \n",
    "       'Mizoram':23.1645, 'Goa': 15.2993, 'Andaman and Nicobar Islands': 11.7401, 'Assam' : 26.2006, \n",
    "       'Jharkhand': 23.6102, 'Arunachal Pradesh': 28.2180, 'Tripura': 23.9408, 'Nagaland': 26.1584, \n",
    "       'Meghalaya' : 25.4670, 'Dadar Nagar Haveli' : 20.1809, 'Sikkim':27.5330, \n",
    "       'Dadra and Nagar Haveli and Daman and Diu': 20.1809}\n",
    "\n",
    "# longitude of the states\n",
    "long = {'Delhi':77.1025, 'Haryana':76.0856, 'Kerala':76.2711, 'Rajasthan':74.2179,\n",
    "        'Telengana':79.0193, 'Telangana':79.0193, 'Uttar Pradesh':80.9462, 'Ladakh':78.2932, 'Tamil Nadu':78.6569,\n",
    "        'Jammu and Kashmir':76.5762, 'Punjab':75.3412, 'Karnataka':75.7139, 'Maharashtra':75.7139,\n",
    "        'Andhra Pradesh':79.7400, 'Odisha':85.0985, 'Uttarakhand':79.0193, 'West Bengal':87.8550, \n",
    "        'Puducherry': 79.8083, 'Chandigarh': 76.7794, 'Chhattisgarh':81.8661, 'Gujarat': 71.1924, \n",
    "        'Himachal Pradesh': 77.1734, 'Madhya Pradesh': 78.6569, 'Bihar': 85.3131, 'Manipur':93.9063, \n",
    "        'Mizoram':92.9376, 'Goa': 74.1240, 'Andaman and Nicobar Islands': 92.6586, 'Assam' : 92.9376, \n",
    "        'Jharkhand': 85.2799, 'Arunachal Pradesh': 94.7278, 'Tripura': 91.9882, 'Nagaland': 94.5624,\n",
    "        'Meghalaya' : 91.3662, 'Dadar Nagar Haveli' : 73.0169, 'Sikkim':88.5122,\n",
    "        'Dadra and Nagar Haveli and Daman and Diu': 73.0169}\n",
    "\n",
    "# add latitude column based on 'Name of State / UT' column\n",
    "df_bs['Latitude'] = df_bs['Name of State / UT'].map(lat)\n",
    "\n",
    "# add longitude column based on 'Name of State / UT' column\n",
    "df_bs['Longitude'] = df_bs['Name of State / UT'].map(long)\n",
    "\n",
    "# df_bs.head(36)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Andaman and Nicobar Islands', 'Andhra Pradesh',\n",
       "       'Arunachal Pradesh', 'Assam', 'Bihar', 'Chandigarh',\n",
       "       'Chhattisgarh', 'Dadra and Nagar Haveli and Daman and Diu',\n",
       "       'Delhi', 'Goa', 'Gujarat', 'Haryana', 'Himachal Pradesh',\n",
       "       'Jammu and Kashmir', 'Jharkhand', 'Karnataka', 'Kerala', 'Ladakh',\n",
       "       'Madhya Pradesh', 'Maharashtra', 'Manipur', 'Meghalaya', 'Mizoram',\n",
       "       'Nagaland', 'Odisha', 'Puducherry', 'Punjab', 'Rajasthan',\n",
       "       'Sikkim', 'Tamil Nadu', 'Telangana', 'Tripura', 'Uttarakhand',\n",
       "       'Uttar Pradesh', 'West Bengal'], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# unique state names\n",
    "df_bs['Name of State / UT'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name of State / UT            0\n",
       "Active Cases*                 0\n",
       "Cured/Discharged/Migrated*    0\n",
       "Deaths**                      0\n",
       "Total Confirmed cases*        0\n",
       "Date                          0\n",
       "Latitude                      0\n",
       "Longitude                     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of missing values \n",
    "df_bs.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name of State / UT            35\n",
       "Active Cases*                 35\n",
       "Cured/Discharged/Migrated*    35\n",
       "Deaths**                      26\n",
       "Total Confirmed cases*        35\n",
       "Date                           1\n",
       "Latitude                      35\n",
       "Longitude                     31\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of unique values \n",
    "df_bs.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Name of State / UT</th>\n",
       "      <th>Active Cases*</th>\n",
       "      <th>Cured/Discharged/Migrated*</th>\n",
       "      <th>Deaths**</th>\n",
       "      <th>Total Confirmed cases*</th>\n",
       "      <th>Date</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andaman and Nicobar Islands</td>\n",
       "      <td>57</td>\n",
       "      <td>109</td>\n",
       "      <td>0</td>\n",
       "      <td>166</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>11.7401</td>\n",
       "      <td>92.6586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>14274</td>\n",
       "      <td>16464</td>\n",
       "      <td>365</td>\n",
       "      <td>31103</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>15.9129</td>\n",
       "      <td>79.7400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>240</td>\n",
       "      <td>145</td>\n",
       "      <td>2</td>\n",
       "      <td>387</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>28.2180</td>\n",
       "      <td>94.7278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Assam</td>\n",
       "      <td>5876</td>\n",
       "      <td>10894</td>\n",
       "      <td>36</td>\n",
       "      <td>16806</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>26.2006</td>\n",
       "      <td>92.9376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bihar</td>\n",
       "      <td>5482</td>\n",
       "      <td>12317</td>\n",
       "      <td>160</td>\n",
       "      <td>17959</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>25.0961</td>\n",
       "      <td>85.3131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chandigarh</td>\n",
       "      <td>157</td>\n",
       "      <td>423</td>\n",
       "      <td>8</td>\n",
       "      <td>588</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>30.7333</td>\n",
       "      <td>76.7794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Chhattisgarh</td>\n",
       "      <td>996</td>\n",
       "      <td>3202</td>\n",
       "      <td>19</td>\n",
       "      <td>4217</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>21.2787</td>\n",
       "      <td>81.8661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Dadra and Nagar Haveli and Daman and Diu</td>\n",
       "      <td>226</td>\n",
       "      <td>268</td>\n",
       "      <td>1</td>\n",
       "      <td>495</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>20.1809</td>\n",
       "      <td>73.0169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Delhi</td>\n",
       "      <td>19017</td>\n",
       "      <td>91312</td>\n",
       "      <td>3411</td>\n",
       "      <td>113740</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>28.7041</td>\n",
       "      <td>77.1025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Goa</td>\n",
       "      <td>1026</td>\n",
       "      <td>1540</td>\n",
       "      <td>17</td>\n",
       "      <td>2583</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>15.2993</td>\n",
       "      <td>74.1240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Gujarat</td>\n",
       "      <td>10897</td>\n",
       "      <td>29770</td>\n",
       "      <td>2055</td>\n",
       "      <td>42722</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>22.2587</td>\n",
       "      <td>71.1924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Haryana</td>\n",
       "      <td>4984</td>\n",
       "      <td>16602</td>\n",
       "      <td>308</td>\n",
       "      <td>21894</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>29.0588</td>\n",
       "      <td>76.0856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Himachal Pradesh</td>\n",
       "      <td>292</td>\n",
       "      <td>940</td>\n",
       "      <td>11</td>\n",
       "      <td>1243</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>31.1048</td>\n",
       "      <td>77.1734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Jammu and Kashmir</td>\n",
       "      <td>4545</td>\n",
       "      <td>6095</td>\n",
       "      <td>187</td>\n",
       "      <td>10827</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>33.7782</td>\n",
       "      <td>76.5762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Jharkhand</td>\n",
       "      <td>1514</td>\n",
       "      <td>2351</td>\n",
       "      <td>33</td>\n",
       "      <td>3898</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>23.6102</td>\n",
       "      <td>85.2799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Karnataka</td>\n",
       "      <td>24576</td>\n",
       "      <td>16248</td>\n",
       "      <td>757</td>\n",
       "      <td>41581</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>15.3173</td>\n",
       "      <td>75.7139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Kerala</td>\n",
       "      <td>4032</td>\n",
       "      <td>4257</td>\n",
       "      <td>33</td>\n",
       "      <td>8322</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>10.8505</td>\n",
       "      <td>76.2711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ladakh</td>\n",
       "      <td>146</td>\n",
       "      <td>946</td>\n",
       "      <td>1</td>\n",
       "      <td>1093</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>34.2996</td>\n",
       "      <td>78.2932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Madhya Pradesh</td>\n",
       "      <td>4336</td>\n",
       "      <td>13208</td>\n",
       "      <td>663</td>\n",
       "      <td>18207</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>22.9734</td>\n",
       "      <td>78.6569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Maharashtra</td>\n",
       "      <td>105935</td>\n",
       "      <td>144507</td>\n",
       "      <td>10482</td>\n",
       "      <td>260924</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>19.7515</td>\n",
       "      <td>75.7139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Manipur</td>\n",
       "      <td>656</td>\n",
       "      <td>970</td>\n",
       "      <td>0</td>\n",
       "      <td>1626</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>24.6637</td>\n",
       "      <td>93.9063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Meghalaya</td>\n",
       "      <td>250</td>\n",
       "      <td>66</td>\n",
       "      <td>2</td>\n",
       "      <td>318</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>25.4670</td>\n",
       "      <td>91.3662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Mizoram</td>\n",
       "      <td>82</td>\n",
       "      <td>151</td>\n",
       "      <td>0</td>\n",
       "      <td>233</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>23.1645</td>\n",
       "      <td>92.9376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Nagaland</td>\n",
       "      <td>505</td>\n",
       "      <td>340</td>\n",
       "      <td>0</td>\n",
       "      <td>845</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>26.1584</td>\n",
       "      <td>94.5624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Odisha</td>\n",
       "      <td>4412</td>\n",
       "      <td>9255</td>\n",
       "      <td>70</td>\n",
       "      <td>13737</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>20.9517</td>\n",
       "      <td>85.0985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Puducherry</td>\n",
       "      <td>665</td>\n",
       "      <td>785</td>\n",
       "      <td>18</td>\n",
       "      <td>1468</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>11.9416</td>\n",
       "      <td>79.8083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Punjab</td>\n",
       "      <td>2388</td>\n",
       "      <td>5586</td>\n",
       "      <td>204</td>\n",
       "      <td>8178</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>31.1471</td>\n",
       "      <td>75.3412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Rajasthan</td>\n",
       "      <td>5781</td>\n",
       "      <td>18630</td>\n",
       "      <td>525</td>\n",
       "      <td>24936</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>27.0238</td>\n",
       "      <td>74.2179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Sikkim</td>\n",
       "      <td>106</td>\n",
       "      <td>86</td>\n",
       "      <td>0</td>\n",
       "      <td>192</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>27.5330</td>\n",
       "      <td>88.5122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Tamil Nadu</td>\n",
       "      <td>48199</td>\n",
       "      <td>92567</td>\n",
       "      <td>2032</td>\n",
       "      <td>142798</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>11.1271</td>\n",
       "      <td>78.6569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Telangana</td>\n",
       "      <td>12177</td>\n",
       "      <td>23679</td>\n",
       "      <td>365</td>\n",
       "      <td>36221</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>18.1124</td>\n",
       "      <td>79.0193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Tripura</td>\n",
       "      <td>603</td>\n",
       "      <td>1475</td>\n",
       "      <td>2</td>\n",
       "      <td>2080</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>23.9408</td>\n",
       "      <td>91.9882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Uttarakhand</td>\n",
       "      <td>703</td>\n",
       "      <td>2856</td>\n",
       "      <td>49</td>\n",
       "      <td>3608</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>30.0668</td>\n",
       "      <td>79.0193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Uttar Pradesh</td>\n",
       "      <td>12972</td>\n",
       "      <td>24203</td>\n",
       "      <td>955</td>\n",
       "      <td>38130</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>26.8467</td>\n",
       "      <td>80.9462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>West Bengal</td>\n",
       "      <td>11279</td>\n",
       "      <td>19213</td>\n",
       "      <td>956</td>\n",
       "      <td>31448</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>22.9868</td>\n",
       "      <td>87.8550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Name of State / UT Active Cases*  \\\n",
       "0                Andaman and Nicobar Islands            57   \n",
       "1                             Andhra Pradesh         14274   \n",
       "2                          Arunachal Pradesh           240   \n",
       "3                                      Assam          5876   \n",
       "4                                      Bihar          5482   \n",
       "5                                 Chandigarh           157   \n",
       "6                               Chhattisgarh           996   \n",
       "7   Dadra and Nagar Haveli and Daman and Diu           226   \n",
       "8                                      Delhi         19017   \n",
       "9                                        Goa          1026   \n",
       "10                                   Gujarat         10897   \n",
       "11                                   Haryana          4984   \n",
       "12                          Himachal Pradesh           292   \n",
       "13                         Jammu and Kashmir          4545   \n",
       "14                                 Jharkhand          1514   \n",
       "15                                 Karnataka         24576   \n",
       "16                                    Kerala          4032   \n",
       "17                                    Ladakh           146   \n",
       "18                            Madhya Pradesh          4336   \n",
       "19                               Maharashtra        105935   \n",
       "20                                   Manipur           656   \n",
       "21                                 Meghalaya           250   \n",
       "22                                   Mizoram            82   \n",
       "23                                  Nagaland           505   \n",
       "24                                    Odisha          4412   \n",
       "25                                Puducherry           665   \n",
       "26                                    Punjab          2388   \n",
       "27                                 Rajasthan          5781   \n",
       "28                                    Sikkim           106   \n",
       "29                                Tamil Nadu         48199   \n",
       "30                                 Telangana         12177   \n",
       "31                                   Tripura           603   \n",
       "32                               Uttarakhand           703   \n",
       "33                             Uttar Pradesh         12972   \n",
       "34                               West Bengal         11279   \n",
       "\n",
       "   Cured/Discharged/Migrated* Deaths** Total Confirmed cases*       Date  \\\n",
       "0                         109        0                    166 2020-07-14   \n",
       "1                       16464      365                  31103 2020-07-14   \n",
       "2                         145        2                    387 2020-07-14   \n",
       "3                       10894       36                  16806 2020-07-14   \n",
       "4                       12317      160                  17959 2020-07-14   \n",
       "5                         423        8                    588 2020-07-14   \n",
       "6                        3202       19                   4217 2020-07-14   \n",
       "7                         268        1                    495 2020-07-14   \n",
       "8                       91312     3411                 113740 2020-07-14   \n",
       "9                        1540       17                   2583 2020-07-14   \n",
       "10                      29770     2055                  42722 2020-07-14   \n",
       "11                      16602      308                  21894 2020-07-14   \n",
       "12                        940       11                   1243 2020-07-14   \n",
       "13                       6095      187                  10827 2020-07-14   \n",
       "14                       2351       33                   3898 2020-07-14   \n",
       "15                      16248      757                  41581 2020-07-14   \n",
       "16                       4257       33                   8322 2020-07-14   \n",
       "17                        946        1                   1093 2020-07-14   \n",
       "18                      13208      663                  18207 2020-07-14   \n",
       "19                     144507    10482                 260924 2020-07-14   \n",
       "20                        970        0                   1626 2020-07-14   \n",
       "21                         66        2                    318 2020-07-14   \n",
       "22                        151        0                    233 2020-07-14   \n",
       "23                        340        0                    845 2020-07-14   \n",
       "24                       9255       70                  13737 2020-07-14   \n",
       "25                        785       18                   1468 2020-07-14   \n",
       "26                       5586      204                   8178 2020-07-14   \n",
       "27                      18630      525                  24936 2020-07-14   \n",
       "28                         86        0                    192 2020-07-14   \n",
       "29                      92567     2032                 142798 2020-07-14   \n",
       "30                      23679      365                  36221 2020-07-14   \n",
       "31                       1475        2                   2080 2020-07-14   \n",
       "32                       2856       49                   3608 2020-07-14   \n",
       "33                      24203      955                  38130 2020-07-14   \n",
       "34                      19213      956                  31448 2020-07-14   \n",
       "\n",
       "    Latitude  Longitude  \n",
       "0    11.7401    92.6586  \n",
       "1    15.9129    79.7400  \n",
       "2    28.2180    94.7278  \n",
       "3    26.2006    92.9376  \n",
       "4    25.0961    85.3131  \n",
       "5    30.7333    76.7794  \n",
       "6    21.2787    81.8661  \n",
       "7    20.1809    73.0169  \n",
       "8    28.7041    77.1025  \n",
       "9    15.2993    74.1240  \n",
       "10   22.2587    71.1924  \n",
       "11   29.0588    76.0856  \n",
       "12   31.1048    77.1734  \n",
       "13   33.7782    76.5762  \n",
       "14   23.6102    85.2799  \n",
       "15   15.3173    75.7139  \n",
       "16   10.8505    76.2711  \n",
       "17   34.2996    78.2932  \n",
       "18   22.9734    78.6569  \n",
       "19   19.7515    75.7139  \n",
       "20   24.6637    93.9063  \n",
       "21   25.4670    91.3662  \n",
       "22   23.1645    92.9376  \n",
       "23   26.1584    94.5624  \n",
       "24   20.9517    85.0985  \n",
       "25   11.9416    79.8083  \n",
       "26   31.1471    75.3412  \n",
       "27   27.0238    74.2179  \n",
       "28   27.5330    88.5122  \n",
       "29   11.1271    78.6569  \n",
       "30   18.1124    79.0193  \n",
       "31   23.9408    91.9882  \n",
       "32   30.0668    79.0193  \n",
       "33   26.8467    80.9462  \n",
       "34   22.9868    87.8550  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# saving data\n",
    "# ===========\n",
    "\n",
    "# file names as year-month-day.csv format\n",
    "file_name = now.strftime(\"%Y_%m_%d\")+'.csv'\n",
    "\n",
    "# location for saving the file\n",
    "file_loc = 'C:\\\\Users\\\\imdevskp\\\\Documents\\\\github\\\\covid_india\\\\.day_by_day_data\\\\'\n",
    "\n",
    "# save file as a scv file\n",
    "df_bs.to_csv(file_loc + file_name, index=False)\n",
    "\n",
    "df_bs.head(36)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# column names \n",
    "# df_bs.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combining data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# list of all files available\n",
    "# ! ls C:\\Users\\imdevskp\\Documents\\github\\covid_india\\.day_by_day_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Date</th>\n",
       "      <th>Name of State / UT</th>\n",
       "      <th>Cured/Discharged/Migrated</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Death</th>\n",
       "      <th>Total Confirmed cases</th>\n",
       "      <th>Active Cases*</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1395</th>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>Puducherry</td>\n",
       "      <td>12</td>\n",
       "      <td>11.9416</td>\n",
       "      <td>79.8083</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2499</th>\n",
       "      <td>2020-07-05</td>\n",
       "      <td>Kerala</td>\n",
       "      <td>3048</td>\n",
       "      <td>10.8505</td>\n",
       "      <td>76.2711</td>\n",
       "      <td>25</td>\n",
       "      <td>5204</td>\n",
       "      <td>2131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3532</th>\n",
       "      <td>5/24/2020</td>\n",
       "      <td>Gujarat</td>\n",
       "      <td>6169</td>\n",
       "      <td>22.2587</td>\n",
       "      <td>71.1924</td>\n",
       "      <td>829</td>\n",
       "      <td>13664</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3902</th>\n",
       "      <td>6/11/2020</td>\n",
       "      <td>Gujarat</td>\n",
       "      <td>14735</td>\n",
       "      <td>22.2587</td>\n",
       "      <td>71.1924</td>\n",
       "      <td>1347</td>\n",
       "      <td>21521</td>\n",
       "      <td>5439.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>Odisha</td>\n",
       "      <td>0</td>\n",
       "      <td>20.9517</td>\n",
       "      <td>85.0985</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1761</th>\n",
       "      <td>2020-06-10</td>\n",
       "      <td>Ladakh</td>\n",
       "      <td>52</td>\n",
       "      <td>34.2996</td>\n",
       "      <td>78.2932</td>\n",
       "      <td>1</td>\n",
       "      <td>108</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2299</th>\n",
       "      <td>2020-06-27</td>\n",
       "      <td>Jharkhand</td>\n",
       "      <td>1643</td>\n",
       "      <td>23.6102</td>\n",
       "      <td>85.2799</td>\n",
       "      <td>12</td>\n",
       "      <td>2290</td>\n",
       "      <td>635.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3545</th>\n",
       "      <td>5/24/2020</td>\n",
       "      <td>Jammu and Kashmir</td>\n",
       "      <td>774</td>\n",
       "      <td>33.7782</td>\n",
       "      <td>76.5762</td>\n",
       "      <td>21</td>\n",
       "      <td>1569</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3153</th>\n",
       "      <td>3/28/2020</td>\n",
       "      <td>Chandigarh</td>\n",
       "      <td>0</td>\n",
       "      <td>30.7333</td>\n",
       "      <td>76.7794</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2722</th>\n",
       "      <td>2020-07-12</td>\n",
       "      <td>Karnataka</td>\n",
       "      <td>14716</td>\n",
       "      <td>15.3173</td>\n",
       "      <td>75.7139</td>\n",
       "      <td>613</td>\n",
       "      <td>36216</td>\n",
       "      <td>20887.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date Name of State / UT  Cured/Discharged/Migrated  Latitude  \\\n",
       "1395  2020-05-26         Puducherry                         12   11.9416   \n",
       "2499  2020-07-05             Kerala                       3048   10.8505   \n",
       "3532   5/24/2020            Gujarat                       6169   22.2587   \n",
       "3902   6/11/2020            Gujarat                      14735   22.2587   \n",
       "66    2020-03-31             Odisha                          0   20.9517   \n",
       "1761  2020-06-10             Ladakh                         52   34.2996   \n",
       "2299  2020-06-27          Jharkhand                       1643   23.6102   \n",
       "3545   5/24/2020  Jammu and Kashmir                        774   33.7782   \n",
       "3153   3/28/2020         Chandigarh                          0   30.7333   \n",
       "2722  2020-07-12          Karnataka                      14716   15.3173   \n",
       "\n",
       "      Longitude  Death  Total Confirmed cases  Active Cases*  \n",
       "1395    79.8083      0                     41            NaN  \n",
       "2499    76.2711     25                   5204         2131.0  \n",
       "3532    71.1924    829                  13664            NaN  \n",
       "3902    71.1924   1347                  21521         5439.0  \n",
       "66      85.0985      0                      3            NaN  \n",
       "1761    78.2932      1                    108           55.0  \n",
       "2299    85.2799     12                   2290          635.0  \n",
       "3545    76.5762     21                   1569            NaN  \n",
       "3153    76.7794      0                      8            NaN  \n",
       "2722    75.7139    613                  36216        20887.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# location of the file\n",
    "loc = \"C:\\\\Users\\\\imdevskp\\\\Documents\\\\github\\\\covid_india\\\\.day_by_day_data\\\\\"\n",
    "\n",
    "# list of all files\n",
    "files = glob.glob(loc+'2020*.csv')\n",
    "   \n",
    "# container for each day's data's dataframe\n",
    "dfs = []\n",
    "\n",
    "# loop through the files and append to the dfs list\n",
    "for i in files:\n",
    "    # read data\n",
    "    df_temp = pd.read_csv(i)\n",
    "    \n",
    "    # rename columns\n",
    "    \n",
    "    try:\n",
    "        df_temp = df_temp.drop(['Total Confirmed cases (Indian National)', \n",
    "                                'Total Confirmed cases ( Foreign National )'], axis=1)\n",
    "    except:\n",
    "        pass\n",
    "        \n",
    "    d = {'^Cured.*': 'Cured/Discharged/Migrated', \n",
    "         'Total Confirmed cases.*': 'Total Confirmed cases', \n",
    "         'Death.*': 'Death'}\n",
    "    \n",
    "    df_temp.columns = df_temp.columns.to_series().replace(d, regex=True)\n",
    "\n",
    "\n",
    "#     df_temp = df_temp.rename(columns={'Cured':'Cured/Discharged'})\n",
    "#     df_temp = df_temp.rename(columns={'Cured/Discharged':'Cured/Discharged/Migrated', \n",
    "#                                       'Total Confirmed cases *': 'Total Confirmed cases', \n",
    "#                                       'Total Confirmed cases ': 'Total Confirmed cases', \n",
    "#                                       'Total Confirmed cases* ': 'Total Confirmed cases'})\n",
    "#     df_temp = df_temp.rename(columns=lambda x: re.sub('Total Confirmed cases \\(Including .* foreign Nationals\\) ',\n",
    "#                                                       'Total Confirmed cases',x))\n",
    "#     df_temp = df_temp.rename(columns=lambda x: re.sub(\"Death.*\", \"Death\", x))\n",
    "\n",
    "    \n",
    "    # append to the df_s\n",
    "    dfs.append(df_temp)\n",
    "    \n",
    "# print(dfs)\n",
    "\n",
    "# concat dataframes\n",
    "complete_data = pd.concat(dfs, ignore_index=True).sort_values(['Date'], ascending=True).reset_index(drop=True)\n",
    "\n",
    "# get just numbers\n",
    "complete_data['Death'] = complete_data['Death'].astype('str').str.extract('(\\d+)').astype('int')\n",
    "\n",
    "# few sample rows\n",
    "complete_data.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# complete_data.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fix datatype\n",
    "complete_data['Date'] = pd.to_datetime(complete_data['Date'])\n",
    "\n",
    "# sort rows\n",
    "complete_data = complete_data.sort_values(['Date', 'Name of State / UT']).reset_index(drop=True)\n",
    "\n",
    "# fill missing values with 0\n",
    "cols = ['Cured/Discharged/Migrated', 'Death']\n",
    "complete_data[cols] = complete_data[cols].fillna(0).astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename state/UT names\n",
    "complete_data['Name of State / UT'].replace('Chattisgarh', 'Chhattisgarh', inplace=True)\n",
    "complete_data['Name of State / UT'].replace('Pondicherry', 'Puducherry', inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select only rows with more than 1 case\n",
    "complete_data = complete_data[complete_data['Total Confirmed cases']>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop extra columns\n",
    "complete_data = complete_data.drop(['Active Cases*'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rearrange columns\n",
    "complete_data = complete_data[['Date', 'Name of State / UT', 'Latitude', 'Longitude', \n",
    "                               'Total Confirmed cases', 'Death', 'Cured/Discharged/Migrated']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename state names\n",
    "complete_data['Name of State / UT'] = complete_data['Name of State / UT'].replace('Dadar Nagar Haveli', 'Dadra and Nagar Haveli and Daman and Diu')\n",
    "complete_data['Name of State / UT'] = complete_data['Name of State / UT'].replace('Daman & Diu', 'Dadra and Nagar Haveli and Daman and Diu')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\imdevskp\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  \"\"\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Name of State / UT</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Total Confirmed cases</th>\n",
       "      <th>Death</th>\n",
       "      <th>Cured/Discharged/Migrated</th>\n",
       "      <th>New cases</th>\n",
       "      <th>New deaths</th>\n",
       "      <th>New recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>Kerala</td>\n",
       "      <td>10.8505</td>\n",
       "      <td>76.2711</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>Kerala</td>\n",
       "      <td>10.8505</td>\n",
       "      <td>76.2711</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Kerala</td>\n",
       "      <td>10.8505</td>\n",
       "      <td>76.2711</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-02-02</td>\n",
       "      <td>Kerala</td>\n",
       "      <td>10.8505</td>\n",
       "      <td>76.2711</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-02-03</td>\n",
       "      <td>Kerala</td>\n",
       "      <td>10.8505</td>\n",
       "      <td>76.2711</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Name of State / UT  Latitude  Longitude  Total Confirmed cases  \\\n",
       "0 2020-01-30             Kerala   10.8505    76.2711                      1   \n",
       "1 2020-01-31             Kerala   10.8505    76.2711                      1   \n",
       "2 2020-02-01             Kerala   10.8505    76.2711                      2   \n",
       "3 2020-02-02             Kerala   10.8505    76.2711                      3   \n",
       "4 2020-02-03             Kerala   10.8505    76.2711                      3   \n",
       "\n",
       "   Death  Cured/Discharged/Migrated  New cases  New deaths  New recovered  \n",
       "0      0                          0          0           0              0  \n",
       "1      0                          0          0           0              0  \n",
       "2      0                          0          1           0              0  \n",
       "3      0                          0          1           0              0  \n",
       "4      0                          0          0           0              0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# New cases\n",
    "# =========\n",
    "\n",
    "# temp dataset\n",
    "temp = complete_data.groupby(['Name of State / UT', 'Date', ])['Total Confirmed cases', 'Death', 'Cured/Discharged/Migrated']\n",
    "temp = temp.sum().diff().reset_index()\n",
    "\n",
    "mask = temp['Name of State / UT'] != temp['Name of State / UT'].shift(1)\n",
    "\n",
    "temp.loc[mask, 'Total Confirmed cases'] = np.nan\n",
    "temp.loc[mask, 'Death'] = np.nan\n",
    "temp.loc[mask, 'Cured/Discharged/Migrated'] = np.nan\n",
    "\n",
    "temp = temp[['Date', 'Name of State / UT', 'Total Confirmed cases', 'Death', 'Cured/Discharged/Migrated']]\n",
    "temp.columns = ['Date', 'Name of State / UT', 'New cases', 'New deaths', 'New recovered']\n",
    "\n",
    "# merging new values\n",
    "complete_data = pd.merge(complete_data, temp, on=['Name of State / UT', 'Date'])\n",
    "\n",
    "# filling na with 0\n",
    "complete_data = complete_data.fillna(0)\n",
    "\n",
    "# fixing data types\n",
    "cols = ['New cases', 'New deaths', 'New recovered']\n",
    "complete_data[cols] = complete_data[cols].astype('int')\n",
    "\n",
    "# remove negative values\n",
    "complete_data['New cases'] = complete_data['New cases'].apply(lambda x: 0 if x<0 else x)\n",
    "\n",
    "# final data\n",
    "complete_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Final dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Date</th>\n",
       "      <th>Name of State / UT</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Total Confirmed cases</th>\n",
       "      <th>Death</th>\n",
       "      <th>Cured/Discharged/Migrated</th>\n",
       "      <th>New cases</th>\n",
       "      <th>New deaths</th>\n",
       "      <th>New recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2034</th>\n",
       "      <td>2020-05-18</td>\n",
       "      <td>Karnataka</td>\n",
       "      <td>15.3173</td>\n",
       "      <td>75.7139</td>\n",
       "      <td>1147</td>\n",
       "      <td>37</td>\n",
       "      <td>509</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2532</th>\n",
       "      <td>2020-06-02</td>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>28.2180</td>\n",
       "      <td>94.7278</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1635</th>\n",
       "      <td>2020-05-06</td>\n",
       "      <td>Himachal Pradesh</td>\n",
       "      <td>31.1048</td>\n",
       "      <td>77.1734</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Date Name of State / UT  Latitude  Longitude  \\\n",
       "2034 2020-05-18          Karnataka   15.3173    75.7139   \n",
       "2532 2020-06-02  Arunachal Pradesh   28.2180    94.7278   \n",
       "1635 2020-05-06   Himachal Pradesh   31.1048    77.1734   \n",
       "\n",
       "      Total Confirmed cases  Death  Cured/Discharged/Migrated  New cases  \\\n",
       "2034                   1147     37                        509         55   \n",
       "2532                     22      0                          1         18   \n",
       "1635                     42      2                         38          1   \n",
       "\n",
       "      New deaths  New recovered  \n",
       "2034           1             13  \n",
       "2532           0              0  \n",
       "1635           1              4  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# random rows\n",
    "complete_data.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3895 entries, 0 to 3894\n",
      "Data columns (total 10 columns):\n",
      " #   Column                     Non-Null Count  Dtype         \n",
      "---  ------                     --------------  -----         \n",
      " 0   Date                       3895 non-null   datetime64[ns]\n",
      " 1   Name of State / UT         3895 non-null   object        \n",
      " 2   Latitude                   3895 non-null   float64       \n",
      " 3   Longitude                  3895 non-null   float64       \n",
      " 4   Total Confirmed cases      3895 non-null   int64         \n",
      " 5   Death                      3895 non-null   int32         \n",
      " 6   Cured/Discharged/Migrated  3895 non-null   int32         \n",
      " 7   New cases                  3895 non-null   int64         \n",
      " 8   New deaths                 3895 non-null   int32         \n",
      " 9   New recovered              3895 non-null   int32         \n",
      "dtypes: datetime64[ns](1), float64(2), int32(4), int64(2), object(1)\n",
      "memory usage: 273.9+ KB\n"
     ]
    }
   ],
   "source": [
    "# complete data info\n",
    "complete_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save as .csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save data in a csv file\n",
    "complete_data.to_csv('complete.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# complete_data.groupby('Date').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# complete_data.sort_values('Death', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Date</th>\n",
       "      <th>Name of State / UT</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Total Confirmed cases</th>\n",
       "      <th>Death</th>\n",
       "      <th>Cured/Discharged/Migrated</th>\n",
       "      <th>New cases</th>\n",
       "      <th>New deaths</th>\n",
       "      <th>New recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3860</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Andaman and Nicobar Islands</td>\n",
       "      <td>11.7401</td>\n",
       "      <td>92.6586</td>\n",
       "      <td>166</td>\n",
       "      <td>0</td>\n",
       "      <td>109</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3861</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>15.9129</td>\n",
       "      <td>79.7400</td>\n",
       "      <td>31103</td>\n",
       "      <td>365</td>\n",
       "      <td>16464</td>\n",
       "      <td>1935</td>\n",
       "      <td>37</td>\n",
       "      <td>1052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3862</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>28.2180</td>\n",
       "      <td>94.7278</td>\n",
       "      <td>387</td>\n",
       "      <td>2</td>\n",
       "      <td>145</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3863</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Assam</td>\n",
       "      <td>26.2006</td>\n",
       "      <td>92.9376</td>\n",
       "      <td>16806</td>\n",
       "      <td>36</td>\n",
       "      <td>10894</td>\n",
       "      <td>735</td>\n",
       "      <td>1</td>\n",
       "      <td>468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3864</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Bihar</td>\n",
       "      <td>25.0961</td>\n",
       "      <td>85.3131</td>\n",
       "      <td>17959</td>\n",
       "      <td>160</td>\n",
       "      <td>12317</td>\n",
       "      <td>1317</td>\n",
       "      <td>17</td>\n",
       "      <td>819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3865</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Chandigarh</td>\n",
       "      <td>30.7333</td>\n",
       "      <td>76.7794</td>\n",
       "      <td>588</td>\n",
       "      <td>8</td>\n",
       "      <td>423</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3866</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Chhattisgarh</td>\n",
       "      <td>21.2787</td>\n",
       "      <td>81.8661</td>\n",
       "      <td>4217</td>\n",
       "      <td>19</td>\n",
       "      <td>3202</td>\n",
       "      <td>158</td>\n",
       "      <td>0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3867</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Dadra and Nagar Haveli and Daman and Diu</td>\n",
       "      <td>20.1809</td>\n",
       "      <td>73.0169</td>\n",
       "      <td>495</td>\n",
       "      <td>1</td>\n",
       "      <td>268</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3868</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Delhi</td>\n",
       "      <td>28.7041</td>\n",
       "      <td>77.1025</td>\n",
       "      <td>113740</td>\n",
       "      <td>3411</td>\n",
       "      <td>91312</td>\n",
       "      <td>1246</td>\n",
       "      <td>40</td>\n",
       "      <td>1344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3869</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Goa</td>\n",
       "      <td>15.2993</td>\n",
       "      <td>74.1240</td>\n",
       "      <td>2583</td>\n",
       "      <td>17</td>\n",
       "      <td>1540</td>\n",
       "      <td>130</td>\n",
       "      <td>3</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3870</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Gujarat</td>\n",
       "      <td>22.2587</td>\n",
       "      <td>71.1924</td>\n",
       "      <td>42722</td>\n",
       "      <td>2055</td>\n",
       "      <td>29770</td>\n",
       "      <td>902</td>\n",
       "      <td>10</td>\n",
       "      <td>608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3871</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Haryana</td>\n",
       "      <td>29.0588</td>\n",
       "      <td>76.0856</td>\n",
       "      <td>21894</td>\n",
       "      <td>308</td>\n",
       "      <td>16602</td>\n",
       "      <td>654</td>\n",
       "      <td>7</td>\n",
       "      <td>619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3872</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Himachal Pradesh</td>\n",
       "      <td>31.1048</td>\n",
       "      <td>77.1734</td>\n",
       "      <td>1243</td>\n",
       "      <td>11</td>\n",
       "      <td>940</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3873</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Jammu and Kashmir</td>\n",
       "      <td>33.7782</td>\n",
       "      <td>76.5762</td>\n",
       "      <td>10827</td>\n",
       "      <td>187</td>\n",
       "      <td>6095</td>\n",
       "      <td>314</td>\n",
       "      <td>8</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3874</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Jharkhand</td>\n",
       "      <td>23.6102</td>\n",
       "      <td>85.2799</td>\n",
       "      <td>3898</td>\n",
       "      <td>33</td>\n",
       "      <td>2351</td>\n",
       "      <td>142</td>\n",
       "      <td>3</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3875</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Karnataka</td>\n",
       "      <td>15.3173</td>\n",
       "      <td>75.7139</td>\n",
       "      <td>41581</td>\n",
       "      <td>757</td>\n",
       "      <td>16248</td>\n",
       "      <td>2738</td>\n",
       "      <td>73</td>\n",
       "      <td>839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3876</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Kerala</td>\n",
       "      <td>10.8505</td>\n",
       "      <td>76.2711</td>\n",
       "      <td>8322</td>\n",
       "      <td>33</td>\n",
       "      <td>4257</td>\n",
       "      <td>449</td>\n",
       "      <td>2</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3877</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Ladakh</td>\n",
       "      <td>34.2996</td>\n",
       "      <td>78.2932</td>\n",
       "      <td>1093</td>\n",
       "      <td>1</td>\n",
       "      <td>946</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3878</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Madhya Pradesh</td>\n",
       "      <td>22.9734</td>\n",
       "      <td>78.6569</td>\n",
       "      <td>18207</td>\n",
       "      <td>663</td>\n",
       "      <td>13208</td>\n",
       "      <td>575</td>\n",
       "      <td>10</td>\n",
       "      <td>332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3879</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Maharashtra</td>\n",
       "      <td>19.7515</td>\n",
       "      <td>75.7139</td>\n",
       "      <td>260924</td>\n",
       "      <td>10482</td>\n",
       "      <td>144507</td>\n",
       "      <td>6497</td>\n",
       "      <td>193</td>\n",
       "      <td>4182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3880</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Manipur</td>\n",
       "      <td>24.6637</td>\n",
       "      <td>93.9063</td>\n",
       "      <td>1626</td>\n",
       "      <td>0</td>\n",
       "      <td>970</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3881</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Meghalaya</td>\n",
       "      <td>25.4670</td>\n",
       "      <td>91.3662</td>\n",
       "      <td>318</td>\n",
       "      <td>2</td>\n",
       "      <td>66</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3882</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Mizoram</td>\n",
       "      <td>23.1645</td>\n",
       "      <td>92.9376</td>\n",
       "      <td>233</td>\n",
       "      <td>0</td>\n",
       "      <td>151</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3883</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Nagaland</td>\n",
       "      <td>26.1584</td>\n",
       "      <td>94.5624</td>\n",
       "      <td>845</td>\n",
       "      <td>0</td>\n",
       "      <td>340</td>\n",
       "      <td>71</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3884</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Odisha</td>\n",
       "      <td>20.9517</td>\n",
       "      <td>85.0985</td>\n",
       "      <td>13737</td>\n",
       "      <td>70</td>\n",
       "      <td>9255</td>\n",
       "      <td>616</td>\n",
       "      <td>6</td>\n",
       "      <td>505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3885</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Puducherry</td>\n",
       "      <td>11.9416</td>\n",
       "      <td>79.8083</td>\n",
       "      <td>1468</td>\n",
       "      <td>18</td>\n",
       "      <td>785</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3886</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Punjab</td>\n",
       "      <td>31.1471</td>\n",
       "      <td>75.3412</td>\n",
       "      <td>8178</td>\n",
       "      <td>204</td>\n",
       "      <td>5586</td>\n",
       "      <td>357</td>\n",
       "      <td>5</td>\n",
       "      <td>194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3887</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Rajasthan</td>\n",
       "      <td>27.0238</td>\n",
       "      <td>74.2179</td>\n",
       "      <td>24936</td>\n",
       "      <td>525</td>\n",
       "      <td>18630</td>\n",
       "      <td>544</td>\n",
       "      <td>15</td>\n",
       "      <td>527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3888</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Sikkim</td>\n",
       "      <td>27.5330</td>\n",
       "      <td>88.5122</td>\n",
       "      <td>192</td>\n",
       "      <td>0</td>\n",
       "      <td>86</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3889</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Tamil Nadu</td>\n",
       "      <td>11.1271</td>\n",
       "      <td>78.6569</td>\n",
       "      <td>142798</td>\n",
       "      <td>2032</td>\n",
       "      <td>92567</td>\n",
       "      <td>4328</td>\n",
       "      <td>66</td>\n",
       "      <td>3035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3890</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Telangana</td>\n",
       "      <td>18.1124</td>\n",
       "      <td>79.0193</td>\n",
       "      <td>36221</td>\n",
       "      <td>365</td>\n",
       "      <td>23679</td>\n",
       "      <td>1550</td>\n",
       "      <td>9</td>\n",
       "      <td>1197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3891</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Tripura</td>\n",
       "      <td>23.9408</td>\n",
       "      <td>91.9882</td>\n",
       "      <td>2080</td>\n",
       "      <td>2</td>\n",
       "      <td>1475</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3892</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Uttar Pradesh</td>\n",
       "      <td>26.8467</td>\n",
       "      <td>80.9462</td>\n",
       "      <td>38130</td>\n",
       "      <td>955</td>\n",
       "      <td>24203</td>\n",
       "      <td>1654</td>\n",
       "      <td>21</td>\n",
       "      <td>869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3893</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>Uttarakhand</td>\n",
       "      <td>30.0668</td>\n",
       "      <td>79.0193</td>\n",
       "      <td>3608</td>\n",
       "      <td>49</td>\n",
       "      <td>2856</td>\n",
       "      <td>71</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3894</th>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>West Bengal</td>\n",
       "      <td>22.9868</td>\n",
       "      <td>87.8550</td>\n",
       "      <td>31448</td>\n",
       "      <td>956</td>\n",
       "      <td>19213</td>\n",
       "      <td>1435</td>\n",
       "      <td>24</td>\n",
       "      <td>632</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Date                        Name of State / UT  Latitude  \\\n",
       "3860 2020-07-14               Andaman and Nicobar Islands   11.7401   \n",
       "3861 2020-07-14                            Andhra Pradesh   15.9129   \n",
       "3862 2020-07-14                         Arunachal Pradesh   28.2180   \n",
       "3863 2020-07-14                                     Assam   26.2006   \n",
       "3864 2020-07-14                                     Bihar   25.0961   \n",
       "3865 2020-07-14                                Chandigarh   30.7333   \n",
       "3866 2020-07-14                              Chhattisgarh   21.2787   \n",
       "3867 2020-07-14  Dadra and Nagar Haveli and Daman and Diu   20.1809   \n",
       "3868 2020-07-14                                     Delhi   28.7041   \n",
       "3869 2020-07-14                                       Goa   15.2993   \n",
       "3870 2020-07-14                                   Gujarat   22.2587   \n",
       "3871 2020-07-14                                   Haryana   29.0588   \n",
       "3872 2020-07-14                          Himachal Pradesh   31.1048   \n",
       "3873 2020-07-14                         Jammu and Kashmir   33.7782   \n",
       "3874 2020-07-14                                 Jharkhand   23.6102   \n",
       "3875 2020-07-14                                 Karnataka   15.3173   \n",
       "3876 2020-07-14                                    Kerala   10.8505   \n",
       "3877 2020-07-14                                    Ladakh   34.2996   \n",
       "3878 2020-07-14                            Madhya Pradesh   22.9734   \n",
       "3879 2020-07-14                               Maharashtra   19.7515   \n",
       "3880 2020-07-14                                   Manipur   24.6637   \n",
       "3881 2020-07-14                                 Meghalaya   25.4670   \n",
       "3882 2020-07-14                                   Mizoram   23.1645   \n",
       "3883 2020-07-14                                  Nagaland   26.1584   \n",
       "3884 2020-07-14                                    Odisha   20.9517   \n",
       "3885 2020-07-14                                Puducherry   11.9416   \n",
       "3886 2020-07-14                                    Punjab   31.1471   \n",
       "3887 2020-07-14                                 Rajasthan   27.0238   \n",
       "3888 2020-07-14                                    Sikkim   27.5330   \n",
       "3889 2020-07-14                                Tamil Nadu   11.1271   \n",
       "3890 2020-07-14                                 Telangana   18.1124   \n",
       "3891 2020-07-14                                   Tripura   23.9408   \n",
       "3892 2020-07-14                             Uttar Pradesh   26.8467   \n",
       "3893 2020-07-14                               Uttarakhand   30.0668   \n",
       "3894 2020-07-14                               West Bengal   22.9868   \n",
       "\n",
       "      Longitude  Total Confirmed cases  Death  Cured/Discharged/Migrated  \\\n",
       "3860    92.6586                    166      0                        109   \n",
       "3861    79.7400                  31103    365                      16464   \n",
       "3862    94.7278                    387      2                        145   \n",
       "3863    92.9376                  16806     36                      10894   \n",
       "3864    85.3131                  17959    160                      12317   \n",
       "3865    76.7794                    588      8                        423   \n",
       "3866    81.8661                   4217     19                       3202   \n",
       "3867    73.0169                    495      1                        268   \n",
       "3868    77.1025                 113740   3411                      91312   \n",
       "3869    74.1240                   2583     17                       1540   \n",
       "3870    71.1924                  42722   2055                      29770   \n",
       "3871    76.0856                  21894    308                      16602   \n",
       "3872    77.1734                   1243     11                        940   \n",
       "3873    76.5762                  10827    187                       6095   \n",
       "3874    85.2799                   3898     33                       2351   \n",
       "3875    75.7139                  41581    757                      16248   \n",
       "3876    76.2711                   8322     33                       4257   \n",
       "3877    78.2932                   1093      1                        946   \n",
       "3878    78.6569                  18207    663                      13208   \n",
       "3879    75.7139                 260924  10482                     144507   \n",
       "3880    93.9063                   1626      0                        970   \n",
       "3881    91.3662                    318      2                         66   \n",
       "3882    92.9376                    233      0                        151   \n",
       "3883    94.5624                    845      0                        340   \n",
       "3884    85.0985                  13737     70                       9255   \n",
       "3885    79.8083                   1468     18                        785   \n",
       "3886    75.3412                   8178    204                       5586   \n",
       "3887    74.2179                  24936    525                      18630   \n",
       "3888    88.5122                    192      0                         86   \n",
       "3889    78.6569                 142798   2032                      92567   \n",
       "3890    79.0193                  36221    365                      23679   \n",
       "3891    91.9882                   2080      2                       1475   \n",
       "3892    80.9462                  38130    955                      24203   \n",
       "3893    79.0193                   3608     49                       2856   \n",
       "3894    87.8550                  31448    956                      19213   \n",
       "\n",
       "      New cases  New deaths  New recovered  \n",
       "3860          3           0             16  \n",
       "3861       1935          37           1052  \n",
       "3862         28           0              7  \n",
       "3863        735           1            468  \n",
       "3864       1317          17            819  \n",
       "3865         29           0              6  \n",
       "3866        158           0             49  \n",
       "3867         16           1             23  \n",
       "3868       1246          40           1344  \n",
       "3869        130           3             53  \n",
       "3870        902          10            608  \n",
       "3871        654           7            619  \n",
       "3872         30           0             11  \n",
       "3873        314           8            116  \n",
       "3874        142           3             43  \n",
       "3875       2738          73            839  \n",
       "3876        449           2            162  \n",
       "3877          7           0             18  \n",
       "3878        575          10            332  \n",
       "3879       6497         193           4182  \n",
       "3880         17           0             74  \n",
       "3881         12           0              0  \n",
       "3882          2           0              1  \n",
       "3883         71           0             13  \n",
       "3884        616           6            505  \n",
       "3885         50           0             46  \n",
       "3886        357           5            194  \n",
       "3887        544          15            527  \n",
       "3888         39           0              5  \n",
       "3889       4328          66           3035  \n",
       "3890       1550           9           1197  \n",
       "3891         26           0             54  \n",
       "3892       1654          21            869  \n",
       "3893         71           2             70  \n",
       "3894       1435          24            632  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complete_data[complete_data['Date']==max(complete_data['Date'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}