Covid 19 world python pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5c7773b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3f0663bb",
   "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>sno</th>\n",
       "      <th>observationdate</th>\n",
       "      <th>province_state</th>\n",
       "      <th>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>deaths</th>\n",
       "      <th>recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-01-22 17:00:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-01-22 17:00:00</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sno observationdate province_state  country_region         last_update   \n",
       "0    1      2020-01-22          Anhui  Mainland China 2020-01-22 17:00:00  \\\n",
       "1    2      2020-01-22        Beijing  Mainland China 2020-01-22 17:00:00   \n",
       "\n",
       "   confirmed  deaths  recovered  \n",
       "0        1.0     0.0        0.0  \n",
       "1       14.0     0.0        0.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# here we read csv file \n",
    "df=pd.read_csv('covid_19_data.csv').dropna()\n",
    "# now just remove the space to easy acess the column name\n",
    "df.columns=df.columns.str.strip().str.lower().str.replace('/','_').str.replace(' ','_')\n",
    "# set this to columnb to date time\n",
    "df.observationdate  = pd.to_datetime(df.observationdate)  \n",
    "df.last_update = pd.to_datetime(df.last_update ,format='mixed')\n",
    "df.head(2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f8486b7",
   "metadata": {},
   "source": [
    "# top 10 country with max deaths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bd6cfaa3",
   "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>country_region</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>US</td>\n",
       "      <td>123303762.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>72339029.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>India</td>\n",
       "      <td>44256013.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>42918161.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>UK</td>\n",
       "      <td>27316263.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Italy</td>\n",
       "      <td>24852224.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Russia</td>\n",
       "      <td>18271229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Spain</td>\n",
       "      <td>18124340.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Colombia</td>\n",
       "      <td>13962927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Germany</td>\n",
       "      <td>13150115.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_region        count\n",
       "27             US  123303762.0\n",
       "2          Brazil   72339029.0\n",
       "11          India   44256013.0\n",
       "17         Mexico   42918161.0\n",
       "26             UK   27316263.0\n",
       "13          Italy   24852224.0\n",
       "22         Russia   18271229.0\n",
       "23          Spain   18124340.0\n",
       "6        Colombia   13962927.0\n",
       "9         Germany   13150115.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "df.groupby('country_region').deaths.sum()\\\n",
    ".reset_index(name='count').nlargest(10,'count')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f69a8110",
   "metadata": {},
   "source": [
    "# top 3 city in each country with max death"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "98e98db6",
   "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>sno</th>\n",
       "      <th>observationdate</th>\n",
       "      <th>province_state</th>\n",
       "      <th>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>deaths</th>\n",
       "      <th>recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15550</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>Australian Capital Territory</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-04-15 23:04:26</td>\n",
       "      <td>103.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15874</td>\n",
       "      <td>2020-04-16</td>\n",
       "      <td>Australian Capital Territory</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-04-16 23:38:19</td>\n",
       "      <td>103.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16198</td>\n",
       "      <td>2020-04-17</td>\n",
       "      <td>Australian Capital Territory</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-04-17 23:38:48</td>\n",
       "      <td>103.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4802</td>\n",
       "      <td>2020-03-10</td>\n",
       "      <td>Diamond Princess cruise ship</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-02-29 02:03:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5023</td>\n",
       "      <td>2020-03-11</td>\n",
       "      <td>Diamond Princess cruise ship</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-02-29 02:03:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2193</th>\n",
       "      <td>305660</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>Zaporizhia Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-29 04:20:41</td>\n",
       "      <td>102471.0</td>\n",
       "      <td>2323.0</td>\n",
       "      <td>94730.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2194</th>\n",
       "      <td>304895</td>\n",
       "      <td>2021-05-27</td>\n",
       "      <td>Zaporizhia Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-28 04:20:36</td>\n",
       "      <td>102276.0</td>\n",
       "      <td>2313.0</td>\n",
       "      <td>94167.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2195</th>\n",
       "      <td>306428</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zhytomyr Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>87550.0</td>\n",
       "      <td>1738.0</td>\n",
       "      <td>83790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2196</th>\n",
       "      <td>305663</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>Zhytomyr Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-29 04:20:41</td>\n",
       "      <td>87473.0</td>\n",
       "      <td>1734.0</td>\n",
       "      <td>83575.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2197</th>\n",
       "      <td>304898</td>\n",
       "      <td>2021-05-27</td>\n",
       "      <td>Zhytomyr Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-28 04:20:36</td>\n",
       "      <td>87399.0</td>\n",
       "      <td>1733.0</td>\n",
       "      <td>83258.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2198 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         sno observationdate                province_state country_region   \n",
       "0      15550      2020-04-15  Australian Capital Territory      Australia  \\\n",
       "1      15874      2020-04-16  Australian Capital Territory      Australia   \n",
       "2      16198      2020-04-17  Australian Capital Territory      Australia   \n",
       "3       4802      2020-03-10  Diamond Princess cruise ship      Australia   \n",
       "4       5023      2020-03-11  Diamond Princess cruise ship      Australia   \n",
       "...      ...             ...                           ...            ...   \n",
       "2193  305660      2021-05-28             Zaporizhia Oblast        Ukraine   \n",
       "2194  304895      2021-05-27             Zaporizhia Oblast        Ukraine   \n",
       "2195  306428      2021-05-29               Zhytomyr Oblast        Ukraine   \n",
       "2196  305663      2021-05-28               Zhytomyr Oblast        Ukraine   \n",
       "2197  304898      2021-05-27               Zhytomyr Oblast        Ukraine   \n",
       "\n",
       "             last_update  confirmed  deaths  recovered  \n",
       "0    2020-04-15 23:04:26      103.0     3.0       72.0  \n",
       "1    2020-04-16 23:38:19      103.0     3.0       82.0  \n",
       "2    2020-04-17 23:38:48      103.0     3.0       81.0  \n",
       "3    2020-02-29 02:03:10        0.0     0.0        0.0  \n",
       "4    2020-02-29 02:03:10        0.0     0.0        0.0  \n",
       "...                  ...        ...     ...        ...  \n",
       "2193 2021-05-29 04:20:41   102471.0  2323.0    94730.0  \n",
       "2194 2021-05-28 04:20:36   102276.0  2313.0    94167.0  \n",
       "2195 2021-05-30 04:20:55    87550.0  1738.0    83790.0  \n",
       "2196 2021-05-29 04:20:41    87473.0  1734.0    83575.0  \n",
       "2197 2021-05-28 04:20:36    87399.0  1733.0    83258.0  \n",
       "\n",
       "[2198 rows x 8 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "df.groupby(['country_region','province_state']).apply(lambda x: x.nlargest(3,'deaths')).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b701bda",
   "metadata": {},
   "source": [
    "# use of group by\n",
    "# in which month country have max death"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "5fb3ff4a",
   "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>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>p</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>US</td>\n",
       "      <td>2021-Apr</td>\n",
       "      <td>104413544.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>2021-Apr</td>\n",
       "      <td>59358479.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>2021-Apr</td>\n",
       "      <td>36317391.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>India</td>\n",
       "      <td>2021-Apr</td>\n",
       "      <td>36194211.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>UK</td>\n",
       "      <td>2021-Apr</td>\n",
       "      <td>23476671.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Denmark</td>\n",
       "      <td>2020-May</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Macau</td>\n",
       "      <td>2020-Jan</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Macau</td>\n",
       "      <td>2020-Mar</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Macau</td>\n",
       "      <td>2020-May</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Macau</td>\n",
       "      <td>2020-Feb</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_region last_update            p\n",
       "98             US    2021-Apr  104413544.0\n",
       "9          Brazil    2021-Apr   59358479.0\n",
       "66         Mexico    2021-Apr   36317391.0\n",
       "45          India    2021-Apr   36194211.0\n",
       "91             UK    2021-Apr   23476671.0\n",
       "..            ...         ...          ...\n",
       "27        Denmark    2020-May          0.0\n",
       "54          Macau    2020-Jan          0.0\n",
       "55          Macau    2020-Mar          0.0\n",
       "56          Macau    2020-May          0.0\n",
       "53          Macau    2020-Feb          0.0\n",
       "\n",
       "[102 rows x 3 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['country_region',df.last_update.dt.strftime('%Y-%b')])\\\n",
    ".apply(lambda x: x.deaths.sum()).reset_index(name='p').sort_values('p',ascending=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a288656",
   "metadata": {},
   "source": [
    "# in each country top 2 month with max death"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "29157787",
   "metadata": {
    "scrolled": false
   },
   "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>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>no death</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>238651.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>27300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>3480030.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>739245.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>59358479.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>12980550.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Canada</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>4869164.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Canada</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>745304.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Chile</td>\n",
       "      <td>2021-04</td>\n",
       "      <td>4877403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Chile</td>\n",
       "      <td>2021-05</td>\n",
       "      <td>830261.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_region last_update    no death\n",
       "0      Australia     2021-04    238651.0\n",
       "1      Australia     2021-05     27300.0\n",
       "2        Belgium     2021-04   3480030.0\n",
       "3        Belgium     2021-05    739245.0\n",
       "4         Brazil     2021-04  59358479.0\n",
       "5         Brazil     2021-05  12980550.0\n",
       "6         Canada     2021-04   4869164.0\n",
       "7         Canada     2021-05    745304.0\n",
       "8          Chile     2021-04   4877403.0\n",
       "9          Chile     2021-05    830261.0"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['country_region',df.last_update.dt.to_period('M')])\\\n",
    ".deaths.sum().reset_index(name='no death').groupby('country_region')\\\n",
    ".apply(lambda x: x.nlargest(2,'no death'))[['last_update','no death']]\\\n",
    ".reset_index()[['country_region','last_update','no death']].sort_values('country_region').head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63b35c95",
   "metadata": {},
   "source": [
    "# use of table Join\n",
    "# show date on which death are more than avg death of that month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "45c3764d",
   "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>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>province_state</th>\n",
       "      <th>deaths</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>avg_of_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>355947</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-03-08 05:31:00</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1617.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356003</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-03-08 05:31:00</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1791.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356059</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-03-08 05:31:00</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2032.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356115</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-03-31 23:49:27</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2032.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356171</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-04-01 22:04:58</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2182.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737024</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Volyn Oblast</td>\n",
       "      <td>1148.0</td>\n",
       "      <td>61187.0</td>\n",
       "      <td>525.743521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737026</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Zakarpattia Oblast</td>\n",
       "      <td>1586.0</td>\n",
       "      <td>61611.0</td>\n",
       "      <td>525.743521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737028</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Zaporizhia Oblast</td>\n",
       "      <td>2335.0</td>\n",
       "      <td>102641.0</td>\n",
       "      <td>525.743521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737029</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Zaporizhia Oblast</td>\n",
       "      <td>2335.0</td>\n",
       "      <td>102641.0</td>\n",
       "      <td>1830.298765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737030</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Zhytomyr Oblast</td>\n",
       "      <td>1738.0</td>\n",
       "      <td>87550.0</td>\n",
       "      <td>525.743521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>163317 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       country_region         last_update      province_state  deaths   \n",
       "355947      Australia 2020-03-08 05:31:00     New South Wales     8.0  \\\n",
       "356003      Australia 2020-03-08 05:31:00     New South Wales     8.0   \n",
       "356059      Australia 2020-03-08 05:31:00     New South Wales     8.0   \n",
       "356115      Australia 2020-03-31 23:49:27     New South Wales     8.0   \n",
       "356171      Australia 2020-04-01 22:04:58     New South Wales     9.0   \n",
       "...               ...                 ...                 ...     ...   \n",
       "737024        Ukraine 2021-05-30 04:20:55        Volyn Oblast  1148.0   \n",
       "737026        Ukraine 2021-05-30 04:20:55  Zakarpattia Oblast  1586.0   \n",
       "737028        Ukraine 2021-05-30 04:20:55   Zaporizhia Oblast  2335.0   \n",
       "737029        Ukraine 2021-05-30 04:20:55   Zaporizhia Oblast  2335.0   \n",
       "737030        Ukraine 2021-05-30 04:20:55     Zhytomyr Oblast  1738.0   \n",
       "\n",
       "        confirmed  avg_of_month  \n",
       "355947     1617.0      7.431034  \n",
       "356003     1791.0      7.431034  \n",
       "356059     2032.0      7.431034  \n",
       "356115     2032.0      7.431034  \n",
       "356171     2182.0      7.431034  \n",
       "...           ...           ...  \n",
       "737024    61187.0    525.743521  \n",
       "737026    61611.0    525.743521  \n",
       "737028   102641.0    525.743521  \n",
       "737029   102641.0   1830.298765  \n",
       "737030    87550.0    525.743521  \n",
       "\n",
       "[163317 rows x 6 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df.groupby(['country_region',df.last_update.dt.strftime('%Y-%b')])\\\n",
    ".deaths.mean().reset_index(name='avg_of_month')[['country_region','avg_of_month']]\n",
    "\n",
    "df3=pd.merge(df,df2,on='country_region')\n",
    "df3[(df3.deaths>df3.avg_of_month)&(df3.avg_of_month>3)].sort_values(by=['country_region','last_update'])\\\n",
    "[['country_region','last_update','province_state','deaths','confirmed','avg_of_month']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95c6cde5",
   "metadata": {},
   "source": [
    "# for china  top 2 day with max death"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "fa9aa2f6",
   "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>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>province_state</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-04-17 23:38:48</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>4512.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-04-18 22:40:18</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>4512.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_region         last_update province_state  deaths\n",
       "0  Mainland China 2020-04-17 23:38:48          Hubei  4512.0\n",
       "1  Mainland China 2020-04-18 22:40:18          Hubei  4512.0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df[df.country_region == 'Mainland China'].nlargest(2,'deaths')\\\n",
    "[['country_region','last_update','province_state','deaths']].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8b2ff66",
   "metadata": {},
   "source": [
    "# show data of that  day where confirmd case is more than death and death are above 10 and confirmed case also above 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c1f5d271",
   "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>sno</th>\n",
       "      <th>observationdate</th>\n",
       "      <th>province_state</th>\n",
       "      <th>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>deaths</th>\n",
       "      <th>recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-01-22 17:00:00</td>\n",
       "      <td>444.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>50</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-01-23 17:00:00</td>\n",
       "      <td>444.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>54</td>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-01-23 17:00:00</td>\n",
       "      <td>444.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>89</td>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-01-24 17:00:00</td>\n",
       "      <td>549.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>132</td>\n",
       "      <td>2020-01-25</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-01-25 17:00:00</td>\n",
       "      <td>761.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306423</th>\n",
       "      <td>306424</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zakarpattia Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>61611.0</td>\n",
       "      <td>1586.0</td>\n",
       "      <td>58882.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306424</th>\n",
       "      <td>306425</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zaporizhia Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>102641.0</td>\n",
       "      <td>2335.0</td>\n",
       "      <td>95289.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306425</th>\n",
       "      <td>306426</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zeeland</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>29147.0</td>\n",
       "      <td>245.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306427</th>\n",
       "      <td>306428</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zhytomyr Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>87550.0</td>\n",
       "      <td>1738.0</td>\n",
       "      <td>83790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306428</th>\n",
       "      <td>306429</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zuid-Holland</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>391559.0</td>\n",
       "      <td>4252.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>175670 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           sno observationdate      province_state  country_region   \n",
       "13          14      2020-01-22               Hubei  Mainland China  \\\n",
       "49          50      2020-01-23               Hubei  Mainland China   \n",
       "53          54      2020-01-23               Hubei  Mainland China   \n",
       "88          89      2020-01-24               Hubei  Mainland China   \n",
       "131        132      2020-01-25               Hubei  Mainland China   \n",
       "...        ...             ...                 ...             ...   \n",
       "306423  306424      2021-05-29  Zakarpattia Oblast         Ukraine   \n",
       "306424  306425      2021-05-29   Zaporizhia Oblast         Ukraine   \n",
       "306425  306426      2021-05-29             Zeeland     Netherlands   \n",
       "306427  306428      2021-05-29     Zhytomyr Oblast         Ukraine   \n",
       "306428  306429      2021-05-29        Zuid-Holland     Netherlands   \n",
       "\n",
       "               last_update  confirmed  deaths  recovered  \n",
       "13     2020-01-22 17:00:00      444.0    17.0       28.0  \n",
       "49     2020-01-23 17:00:00      444.0    17.0       28.0  \n",
       "53     2020-01-23 17:00:00      444.0    17.0       28.0  \n",
       "88     2020-01-24 17:00:00      549.0    24.0       31.0  \n",
       "131    2020-01-25 17:00:00      761.0    40.0       32.0  \n",
       "...                    ...        ...     ...        ...  \n",
       "306423 2021-05-30 04:20:55    61611.0  1586.0    58882.0  \n",
       "306424 2021-05-30 04:20:55   102641.0  2335.0    95289.0  \n",
       "306425 2021-05-30 04:20:55    29147.0   245.0        0.0  \n",
       "306427 2021-05-30 04:20:55    87550.0  1738.0    83790.0  \n",
       "306428 2021-05-30 04:20:55   391559.0  4252.0        0.0  \n",
       "\n",
       "[175670 rows x 8 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[(df.deaths<df.confirmed)&(df.deaths>10)&(df.confirmed>10)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1bca651",
   "metadata": {},
   "source": [
    "# use of filter \n",
    "# details of that country , day etc   in which monthly confirmed case are more then 1000000 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "810233d4",
   "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>sno</th>\n",
       "      <th>observationdate</th>\n",
       "      <th>province_state</th>\n",
       "      <th>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>deaths</th>\n",
       "      <th>recovered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>513</th>\n",
       "      <td>514</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Hubei</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-02-01 11:53:00</td>\n",
       "      <td>7153.0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>168.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514</th>\n",
       "      <td>515</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Zhejiang</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-02-01 10:53:00</td>\n",
       "      <td>599.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>515</th>\n",
       "      <td>516</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Guangdong</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-02-01 14:23:00</td>\n",
       "      <td>535.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>516</th>\n",
       "      <td>517</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Henan</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-02-01 01:52:00</td>\n",
       "      <td>422.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>517</th>\n",
       "      <td>518</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Hunan</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2020-02-01 11:03:00</td>\n",
       "      <td>389.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306424</th>\n",
       "      <td>306425</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zaporizhia Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>102641.0</td>\n",
       "      <td>2335.0</td>\n",
       "      <td>95289.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306425</th>\n",
       "      <td>306426</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zeeland</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>29147.0</td>\n",
       "      <td>245.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306426</th>\n",
       "      <td>306427</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zhejiang</td>\n",
       "      <td>Mainland China</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>1364.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1324.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306427</th>\n",
       "      <td>306428</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zhytomyr Oblast</td>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>87550.0</td>\n",
       "      <td>1738.0</td>\n",
       "      <td>83790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306428</th>\n",
       "      <td>306429</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>Zuid-Holland</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>391559.0</td>\n",
       "      <td>4252.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>224500 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           sno observationdate     province_state  country_region   \n",
       "513        514      2020-02-01              Hubei  Mainland China  \\\n",
       "514        515      2020-02-01           Zhejiang  Mainland China   \n",
       "515        516      2020-02-01          Guangdong  Mainland China   \n",
       "516        517      2020-02-01              Henan  Mainland China   \n",
       "517        518      2020-02-01              Hunan  Mainland China   \n",
       "...        ...             ...                ...             ...   \n",
       "306424  306425      2021-05-29  Zaporizhia Oblast         Ukraine   \n",
       "306425  306426      2021-05-29            Zeeland     Netherlands   \n",
       "306426  306427      2021-05-29           Zhejiang  Mainland China   \n",
       "306427  306428      2021-05-29    Zhytomyr Oblast         Ukraine   \n",
       "306428  306429      2021-05-29       Zuid-Holland     Netherlands   \n",
       "\n",
       "               last_update  confirmed  deaths  recovered  \n",
       "513    2020-02-01 11:53:00     7153.0   249.0      168.0  \n",
       "514    2020-02-01 10:53:00      599.0     0.0       21.0  \n",
       "515    2020-02-01 14:23:00      535.0     0.0       14.0  \n",
       "516    2020-02-01 01:52:00      422.0     2.0        3.0  \n",
       "517    2020-02-01 11:03:00      389.0     0.0        8.0  \n",
       "...                    ...        ...     ...        ...  \n",
       "306424 2021-05-30 04:20:55   102641.0  2335.0    95289.0  \n",
       "306425 2021-05-30 04:20:55    29147.0   245.0        0.0  \n",
       "306426 2021-05-30 04:20:55     1364.0     1.0     1324.0  \n",
       "306427 2021-05-30 04:20:55    87550.0  1738.0    83790.0  \n",
       "306428 2021-05-30 04:20:55   391559.0  4252.0        0.0  \n",
       "\n",
       "[224500 rows x 8 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([df.last_update.dt.month,'country_region']).filter(lambda x: x.confirmed.sum()>1000000)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb205160",
   "metadata": {},
   "source": [
    "# use of apply function \n",
    "# for each  month which country have max number of death for that month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "8b325c20",
   "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>country_region</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>last_update</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01</th>\n",
       "      <td>Mainland China</td>\n",
       "      <td>905.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01</th>\n",
       "      <td>Australia</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02</th>\n",
       "      <td>Mainland China</td>\n",
       "      <td>46382.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02</th>\n",
       "      <td>Others</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03</th>\n",
       "      <td>Mainland China</td>\n",
       "      <td>98372.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03</th>\n",
       "      <td>US</td>\n",
       "      <td>18447.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04</th>\n",
       "      <td>US</td>\n",
       "      <td>908408.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04</th>\n",
       "      <td>Mainland China</td>\n",
       "      <td>113537.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05</th>\n",
       "      <td>US</td>\n",
       "      <td>405057.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05</th>\n",
       "      <td>Mainland China</td>\n",
       "      <td>27798.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04</th>\n",
       "      <td>US</td>\n",
       "      <td>104413544.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>59358479.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-05</th>\n",
       "      <td>US</td>\n",
       "      <td>17558305.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-05</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>12980550.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             country_region       deaths\n",
       "last_update                             \n",
       "2020-01      Mainland China        905.0\n",
       "2020-01           Australia          0.0\n",
       "2020-02      Mainland China      46382.0\n",
       "2020-02              Others         41.0\n",
       "2020-03      Mainland China      98372.0\n",
       "2020-03                  US      18447.0\n",
       "2020-04                  US     908408.0\n",
       "2020-04      Mainland China     113537.0\n",
       "2020-05                  US     405057.0\n",
       "2020-05      Mainland China      27798.0\n",
       "2021-04                  US  104413544.0\n",
       "2021-04              Brazil   59358479.0\n",
       "2021-05                  US   17558305.0\n",
       "2021-05              Brazil   12980550.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([df.last_update.dt.to_period('M'),'country_region']).deaths.sum().reset_index()\\\n",
    ".groupby('last_update').apply(lambda x: x.nlargest(2,'deaths'))\\\n",
    ".iloc[:,1:].reset_index(level=1,drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed040ef4",
   "metadata": {},
   "source": [
    "# use of string concat , round , data type change \n",
    "# for each month percentage change of death "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "aea23514",
   "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>last_update</th>\n",
       "      <th>deaths</th>\n",
       "      <th>percentage_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>905.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02</td>\n",
       "      <td>46475.0</td>\n",
       "      <td>98.05%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03</td>\n",
       "      <td>122320.0</td>\n",
       "      <td>62.01%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-04</td>\n",
       "      <td>1075558.0</td>\n",
       "      <td>88.63%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-05</td>\n",
       "      <td>461948.0</td>\n",
       "      <td>-132.83%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-04</td>\n",
       "      <td>374753970.0</td>\n",
       "      <td>99.88%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021-05</td>\n",
       "      <td>71371338.0</td>\n",
       "      <td>-425.08%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  last_update       deaths percentage_change\n",
       "0     2020-01        905.0                 0\n",
       "1     2020-02      46475.0            98.05%\n",
       "2     2020-03     122320.0            62.01%\n",
       "3     2020-04    1075558.0            88.63%\n",
       "4     2020-05     461948.0          -132.83%\n",
       "5     2021-04  374753970.0            99.88%\n",
       "6     2021-05   71371338.0          -425.08%"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(df.last_update.dt.to_period('M'))['deaths']\\\n",
    ".sum().reset_index().assign(percentage_change= lambda x: np.where(x.deaths.shift(1).isnull(),0, \n",
    "(((x.deaths-x.deaths.shift(1))/x.deaths)*100).round(2).astype(str)+'%') )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "6707f954",
   "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>country_region</th>\n",
       "      <th>last_update</th>\n",
       "      <th>province_state</th>\n",
       "      <th>deaths</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>avg_of_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>355947</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-03-08 05:31:00</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1617.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356003</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-03-08 05:31:00</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1791.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356059</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-03-08 05:31:00</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2032.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356115</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-03-31 23:49:27</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2032.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356171</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2020-04-01 22:04:58</td>\n",
       "      <td>New South Wales</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2182.0</td>\n",
       "      <td>7.431034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737024</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Volyn Oblast</td>\n",
       "      <td>1148.0</td>\n",
       "      <td>61187.0</td>\n",
       "      <td>525.743521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737026</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Zakarpattia Oblast</td>\n",
       "      <td>1586.0</td>\n",
       "      <td>61611.0</td>\n",
       "      <td>525.743521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737028</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Zaporizhia Oblast</td>\n",
       "      <td>2335.0</td>\n",
       "      <td>102641.0</td>\n",
       "      <td>525.743521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737029</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Zaporizhia Oblast</td>\n",
       "      <td>2335.0</td>\n",
       "      <td>102641.0</td>\n",
       "      <td>1830.298765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737030</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>2021-05-30 04:20:55</td>\n",
       "      <td>Zhytomyr Oblast</td>\n",
       "      <td>1738.0</td>\n",
       "      <td>87550.0</td>\n",
       "      <td>525.743521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>163317 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       country_region         last_update      province_state  deaths   \n",
       "355947      Australia 2020-03-08 05:31:00     New South Wales     8.0  \\\n",
       "356003      Australia 2020-03-08 05:31:00     New South Wales     8.0   \n",
       "356059      Australia 2020-03-08 05:31:00     New South Wales     8.0   \n",
       "356115      Australia 2020-03-31 23:49:27     New South Wales     8.0   \n",
       "356171      Australia 2020-04-01 22:04:58     New South Wales     9.0   \n",
       "...               ...                 ...                 ...     ...   \n",
       "737024        Ukraine 2021-05-30 04:20:55        Volyn Oblast  1148.0   \n",
       "737026        Ukraine 2021-05-30 04:20:55  Zakarpattia Oblast  1586.0   \n",
       "737028        Ukraine 2021-05-30 04:20:55   Zaporizhia Oblast  2335.0   \n",
       "737029        Ukraine 2021-05-30 04:20:55   Zaporizhia Oblast  2335.0   \n",
       "737030        Ukraine 2021-05-30 04:20:55     Zhytomyr Oblast  1738.0   \n",
       "\n",
       "        confirmed  avg_of_month  \n",
       "355947     1617.0      7.431034  \n",
       "356003     1791.0      7.431034  \n",
       "356059     2032.0      7.431034  \n",
       "356115     2032.0      7.431034  \n",
       "356171     2182.0      7.431034  \n",
       "...           ...           ...  \n",
       "737024    61187.0    525.743521  \n",
       "737026    61611.0    525.743521  \n",
       "737028   102641.0    525.743521  \n",
       "737029   102641.0   1830.298765  \n",
       "737030    87550.0    525.743521  \n",
       "\n",
       "[163317 rows x 6 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}