tutorials/Data Manipulation with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e9180cd9",
   "metadata": {},
   "source": [
    "### Documentation of Pandas is [here](https://pandas.pydata.org/docs/user_guide/10min.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c231ef39",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing libraries \n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63ea37c6",
   "metadata": {},
   "source": [
    "## Series is a datatype(data structure) used in Pandas for data manipulation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86516cab",
   "metadata": {},
   "source": [
    "### passing object as argument "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "dd196fa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_series = pd.Series(list('abcdef'))  # passing list as argument"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "12987a45",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    a\n",
       "1    b\n",
       "2    c\n",
       "3    d\n",
       "4    e\n",
       "5    f\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_series  # index assigned automatically"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a8fe546",
   "metadata": {},
   "source": [
    "### passing List as argument "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7992cf69",
   "metadata": {},
   "outputs": [],
   "source": [
    "np_names = np.array(['nitin','somi','yash','anji','rhea','ujjawal'])   # ndarray for names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6e88e6f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "np_names_series = pd.Series(np_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bdea48d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      nitin\n",
       "1       somi\n",
       "2       yash\n",
       "3       anji\n",
       "4       rhea\n",
       "5    ujjawal\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np_names_series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28189150",
   "metadata": {},
   "source": [
    "### passing dictionary as argument "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2d4ae3e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_country_gdp = pd.Series([2255.225482,629.9553062,11601.63022,25306.82494,27266.40335,19466.99052,588.3691778,2890.345675,24733.62696,1445.760002,4803.398244,2618.876037,590.4521124,665.7982328,7122.938458,2639.54156,3362.4656,15378.16704,30860.12808,2579.115607,6525.541272,229.6769525,2242.689259,27570.4852,23016.84778,1334.646773,402.6953275,6047.200797,394.1156638,385.5793827,1414.072488,5745.981529,837.7464011,1206.991065,27715.52837,18937.24998,39578.07441,478.2194906,16684.21278,279.2204061,5345.213415,6288.25324,1908.304416,274.8728621,14646.42094,40034.85063,672.1547506,3359.517402,36152.66676,3054.727742,33529.83052,3825.093781,15428.32098,33630.24604,39170.41371,2699.123242,21058.43643,28272.40661,37691.02733,9581.05659,5671.912202,757.4009286,347.7456605],index=['Algeria','Angola','Argentina','Australia','Austria','Bahamas','Bangladesh','Belarus','Belgium','Bhutan','Brazil','Bulgaria','Cambodia','Cameroon','Chile','China','Colombia','Cyprus','Denmark','El Salvador','Estonia','Ethiopia','Fiji','Finland','France','Georgia','Ghana','Grenada','Guinea','Haiti','Honduras','Hungary','India','Indonesia','Ireland','Italy','Japan','Kenya', 'South Korea','Liberia','Malaysia','Mexico', 'Morocco','Nepal','New Zealand','Norway','Pakistan', 'Peru','Qatar','Russia','Singapore','South Africa','Spain','Sweden','Switzerland','Thailand', 'United Arab Emirates','United Kingdom','United States','Uruguay','Venezuela','Vietnam','Zimbabwe'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5807a2f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Algeria           2255.225482\n",
       "Angola             629.955306\n",
       "Argentina        11601.630220\n",
       "Australia        25306.824940\n",
       "Austria          27266.403350\n",
       "                     ...     \n",
       "United States    37691.027330\n",
       "Uruguay           9581.056590\n",
       "Venezuela         5671.912202\n",
       "Vietnam            757.400929\n",
       "Zimbabwe           347.745660\n",
       "Length: 63, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_country_gdp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19b595e6",
   "metadata": {},
   "source": [
    "### passing scalar as argument "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8e0c8412",
   "metadata": {},
   "outputs": [],
   "source": [
    "scalar_series = pd.Series(5.,index=['a','b','c','d','e'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0e6db214",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    5.0\n",
       "b    5.0\n",
       "c    5.0\n",
       "d    5.0\n",
       "e    5.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scalar_series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd4da1f0",
   "metadata": {},
   "source": [
    "### Accessing elements in Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3db4a0fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2255.225482"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_country_gdp[0]    # using index no"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "72d72c72",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Algeria       2255.225482\n",
       "Angola         629.955306\n",
       "Argentina    11601.630220\n",
       "Australia    25306.824940\n",
       "Austria      27266.403350\n",
       "dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_country_gdp[0:5]   # using range of index numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "91f7e373",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Austria       27266.403350\n",
       "Bahamas       19466.990520\n",
       "Bangladesh      588.369178\n",
       "Belarus        2890.345675\n",
       "Belgium       24733.626960\n",
       "dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_country_gdp[4:9]   # using range of index nos from any index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "60be7035",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "837.7464011"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_country_gdp.loc['India']   # using name of country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6263316c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2255.225482"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_country_gdp.iloc[0]    # using position"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a44ae5b1",
   "metadata": {},
   "source": [
    "### vectorized operations in Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "bc7d0804",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_vector_series = pd.Series([1,2,3,4,5],index=['a','b','c','d','e'])\n",
    "second_vector_series = pd.Series([10,20,30,40,50],index=['a','b','c','d','e'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5d5970cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    2\n",
       "c    3\n",
       "d    4\n",
       "e    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_vector_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "da36046f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    10\n",
       "b    20\n",
       "c    30\n",
       "d    40\n",
       "e    50\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "second_vector_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "c5fcfc8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    11\n",
       "b    22\n",
       "c    33\n",
       "d    44\n",
       "e    55\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_vector_series + second_vector_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7fbdd86c",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_vector_series = pd.Series([1,2,3,4,5],index=['e','c','b','d','a'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "eada0efc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    15\n",
       "b    23\n",
       "c    32\n",
       "d    44\n",
       "e    51\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_vector_series + second_vector_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "5b867a1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_vector_series = pd.Series([1,2,3,4,5],index=['e','c','b','f','g'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "1ab8716c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a     NaN\n",
       "b    23.0\n",
       "c    32.0\n",
       "d     NaN\n",
       "e    51.0\n",
       "f     NaN\n",
       "g     NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_vector_series + second_vector_series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5649009d",
   "metadata": {},
   "source": [
    "## Dataframes - another data structure"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6705abcb",
   "metadata": {},
   "source": [
    "### Create dataframe from lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "30662343",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "9879e891",
   "metadata": {},
   "outputs": [],
   "source": [
    "# last 5 olympic date,place,year and no of countries participated\n",
    "\n",
    "olympic_data_list = {'Hostcity':['London','Beijing','Athens','Sydney','Atlanta'], 'Year':[2021,2008,2004,2000,1996],\n",
    "                    'No of participating countries':[205,204,201,200,197]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "fe5b540b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_olympic_data = pd.DataFrame(olympic_data_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "24b02d83",
   "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>Hostcity</th>\n",
       "      <th>Year</th>\n",
       "      <th>No of participating countries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>London</td>\n",
       "      <td>2021</td>\n",
       "      <td>205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Beijing</td>\n",
       "      <td>2008</td>\n",
       "      <td>204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Athens</td>\n",
       "      <td>2004</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>2000</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Atlanta</td>\n",
       "      <td>1996</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Hostcity  Year  No of participating countries\n",
       "0   London  2021                            205\n",
       "1  Beijing  2008                            204\n",
       "2   Athens  2004                            201\n",
       "3   Sydney  2000                            200\n",
       "4  Atlanta  1996                            197"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_olympic_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06aad762",
   "metadata": {},
   "source": [
    "### Create dataframe from dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "48f54353",
   "metadata": {},
   "outputs": [],
   "source": [
    "olympic_data_dict = {'London':{2021:205},'Beijing':{2008:204}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "59345be5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_olympic_data_dict = pd.DataFrame(olympic_data_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "b85c5165",
   "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>London</th>\n",
       "      <th>Beijing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>205.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>NaN</td>\n",
       "      <td>204.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      London  Beijing\n",
       "2021   205.0      NaN\n",
       "2008     NaN    204.0"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_olympic_data_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1c8a5a3",
   "metadata": {},
   "source": [
    "### View dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d181b4e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     London\n",
       "1    Beijing\n",
       "2     Athens\n",
       "3     Sydney\n",
       "4    Atlanta\n",
       "Name: Hostcity, dtype: object"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_olympic_data.Hostcity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "e68a6c90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.describe of   Hostcity  Year  No of participating countries\n",
       "0   London  2021                            205\n",
       "1  Beijing  2008                            204\n",
       "2   Athens  2004                            201\n",
       "3   Sydney  2000                            200\n",
       "4  Atlanta  1996                            197>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_olympic_data.describe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3aa1e7ac",
   "metadata": {},
   "source": [
    "### Create dataframe from dict of series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "53135b8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "olympic_series_participation = pd.Series([205,204,201,200,197],index=[2012,2008,2004,2000,1996])\n",
    "olympic_series_country = pd.Series(['London','Beijing','Athens','Sydney','Atlanta'],\n",
    "                                  index=[2012,2008,2004,2000,1996])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "6de8690b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_olympic_series = pd.DataFrame({'No of participating Countries':olympic_series_participation,'Host Countries':olympic_series_country})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "2fc47108",
   "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>No of participating Countries</th>\n",
       "      <th>Host Countries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>205</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>204</td>\n",
       "      <td>Beijing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>201</td>\n",
       "      <td>Athens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>200</td>\n",
       "      <td>Sydney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>197</td>\n",
       "      <td>Atlanta</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      No of participating Countries Host Countries\n",
       "2012                            205         London\n",
       "2008                            204        Beijing\n",
       "2004                            201         Athens\n",
       "2000                            200         Sydney\n",
       "1996                            197        Atlanta"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_olympic_series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee400176",
   "metadata": {},
   "source": [
    "### Create dataframe from ndarray"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "edd6b668",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "6360a552",
   "metadata": {},
   "outputs": [],
   "source": [
    "np_array = np.array([2012,2008,2004,2000,1996])\n",
    "dict_ndarray = {'Year': np_array}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "afede6fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ndarray = pd.DataFrame(dict_ndarray)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "ffbc7897",
   "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>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year\n",
       "0  2012\n",
       "1  2008\n",
       "2  2004\n",
       "3  2000\n",
       "4  1996"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ndarray"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98dd2bd8",
   "metadata": {},
   "source": [
    "### Create dataframe from dataframe object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "21e584ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_from_df = pd.DataFrame(df_olympic_series)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "4bf24495",
   "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>No of participating Countries</th>\n",
       "      <th>Host Countries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>205</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>204</td>\n",
       "      <td>Beijing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>201</td>\n",
       "      <td>Athens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>200</td>\n",
       "      <td>Sydney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>197</td>\n",
       "      <td>Atlanta</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      No of participating Countries Host Countries\n",
       "2012                            205         London\n",
       "2008                            204        Beijing\n",
       "2004                            201         Athens\n",
       "2000                            200         Sydney\n",
       "1996                            197        Atlanta"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_from_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "315d23d5",
   "metadata": {},
   "source": [
    "## View and Select data in Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "3ccad209",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "192bf9af",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create dataframe from dict of series for summer olympics : 1996 - 2012\n",
    "olympic_series_participation = pd.Series([205,204,201,200,197],index=[2012,2008,2004,2000,1996])\n",
    "olympic_series_country = pd.Series(['London','Beijing','Athens','Sydney','Atlanta'],\n",
    "                                  index=[2012,2008,2004,2000,1996])\n",
    "\n",
    "df_olympic_series = pd.DataFrame({'No of participating Countries':olympic_series_participation,'Host Cities':olympic_series_country})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "8d4b20c4",
   "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>No of participating Countries</th>\n",
       "      <th>Host Cities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>205</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>204</td>\n",
       "      <td>Beijing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>201</td>\n",
       "      <td>Athens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>200</td>\n",
       "      <td>Sydney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>197</td>\n",
       "      <td>Atlanta</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      No of participating Countries Host Cities\n",
       "2012                            205      London\n",
       "2008                            204     Beijing\n",
       "2004                            201      Athens\n",
       "2000                            200      Sydney\n",
       "1996                            197     Atlanta"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display content of the dataset\n",
    "df_olympic_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "e15eca36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.describe of       No of participating Countries Host Cities\n",
       "2012                            205      London\n",
       "2008                            204     Beijing\n",
       "2004                            201      Athens\n",
       "2000                            200      Sydney\n",
       "1996                            197     Atlanta>"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view dataframe describe\n",
    "df_olympic_series.describe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "316dddab",
   "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>No of participating Countries</th>\n",
       "      <th>Host Cities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>205</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>204</td>\n",
       "      <td>Beijing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      No of participating Countries Host Cities\n",
       "2012                            205      London\n",
       "2008                            204     Beijing"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view top 2 records\n",
    "df_olympic_series.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "84991d88",
   "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>No of participating Countries</th>\n",
       "      <th>Host Cities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>201</td>\n",
       "      <td>Athens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>200</td>\n",
       "      <td>Sydney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>197</td>\n",
       "      <td>Atlanta</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      No of participating Countries Host Cities\n",
       "2004                            201      Athens\n",
       "2000                            200      Sydney\n",
       "1996                            197     Atlanta"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view last 3 records\n",
    "df_olympic_series.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "aa32307c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([2012, 2008, 2004, 2000, 1996], dtype='int64')"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view indexes of datasets\n",
    "df_olympic_series.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "27591896",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['No of participating Countries', 'Host Cities'], dtype='object')"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view columns of dataset\n",
    "df_olympic_series.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "521c22bc",
   "metadata": {},
   "source": [
    "### Select data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "6134b8cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012     London\n",
       "2008    Beijing\n",
       "2004     Athens\n",
       "2000     Sydney\n",
       "1996    Atlanta\n",
       "Name: Host Cities, dtype: object"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select data for host city\n",
    "df_olympic_series['Host Cities']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "ae155825",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2012    205\n",
       "2008    204\n",
       "2004    201\n",
       "2000    200\n",
       "1996    197\n",
       "Name: No of participating Countries, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Another data selection no. of participating countries\n",
    "df_olympic_series['No of participating Countries']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "99f1d307",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "No of participating Countries       205\n",
       "Host Cities                      London\n",
       "Name: 2012, dtype: object"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select label-location based access by label\n",
    "df_olympic_series.loc[2012]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "a2e87345",
   "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>No of participating Countries</th>\n",
       "      <th>Host Cities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>205</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>204</td>\n",
       "      <td>Beijing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      No of participating Countries Host Cities\n",
       "2012                            205      London\n",
       "2008                            204     Beijing"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# integer-location based indexing by position\n",
    "df_olympic_series.iloc[0:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "15f8b65f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Sydney'"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# integer-location based data selection by index value\n",
    "df_olympic_series.iat[3, 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "646271f8",
   "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>No of participating Countries</th>\n",
       "      <th>Host Cities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>200</td>\n",
       "      <td>Sydney</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      No of participating Countries Host Cities\n",
       "2000                            200      Sydney"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select data element by condition where number of participated countries are more than 200 \n",
    "# HINT - Use boolean expression\n",
    "df_olympic_series[df_olympic_series['No of participating Countries'] == 200]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6ca3179",
   "metadata": {},
   "source": [
    "## Handling missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "9af8f078",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_series = pd.Series([1,2,3,4,5],index=['a','b','c','d','e'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "557eb312",
   "metadata": {},
   "outputs": [],
   "source": [
    "second_series = pd.Series([10,20,30,40,50],index=['c','e','f','g','h'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "d38b49cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "sum_of_series = first_series + second_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "3c96ec12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a     NaN\n",
       "b     NaN\n",
       "c    13.0\n",
       "d     NaN\n",
       "e    25.0\n",
       "f     NaN\n",
       "g     NaN\n",
       "h     NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum_of_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "4522e46a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop NaN (Not a Number) value from dataset\n",
    "dropna_s = sum_of_series.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "6b44bf35",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "c    13.0\n",
       "e    25.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dropna_s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "1e348315",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill NaN (Not a Number) values from dataset with zeroes\n",
    "fillna_s = sum_of_series.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "ca90b754",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a     0.0\n",
       "b     0.0\n",
       "c    13.0\n",
       "d     0.0\n",
       "e    25.0\n",
       "f     0.0\n",
       "g     0.0\n",
       "h     0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fillna_s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "71c5983f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fill NaN with zeroes before performing addition operation for missing indices\n",
    "fill_NaN_with_zeroes_before_sum = first_series.add(second_series, fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "510b4da4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a     1.0\n",
       "b     2.0\n",
       "c    13.0\n",
       "d     4.0\n",
       "e    25.0\n",
       "f    30.0\n",
       "g    40.0\n",
       "h    50.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fill_NaN_with_zeroes_before_sum"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "229e0997",
   "metadata": {},
   "source": [
    "## Data Operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "8ae0ae0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "894c4cd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# declare movie rating dataframe: ratings from 1 to 5 (star * rating)\n",
    "df_movie_rating = pd.DataFrame({'movie 1':[5,4,3,3,2,1],'movie 2':[4,5,2,3,4,2]},index=['Tom','Jeffer','Peter','Ram','Ted','Paul'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "1052ab85",
   "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>movie 1</th>\n",
       "      <th>movie 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Tom</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jeffer</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Peter</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ram</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ted</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Paul</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        movie 1  movie 2\n",
       "Tom           5        4\n",
       "Jeffer        4        5\n",
       "Peter         3        2\n",
       "Ram           3        3\n",
       "Ted           2        4\n",
       "Paul          1        2"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movie_rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "240627f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def movie_grade(rating):\n",
    "    if rating==5:\n",
    "        return 'A'\n",
    "    if rating==4:\n",
    "        return 'B'\n",
    "    if rating==3:\n",
    "        return 'C'\n",
    "    if rating==2:\n",
    "        return 'D'\n",
    "    if rating==1:\n",
    "        return 'E'\n",
    "    else:\n",
    "        return 'F'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "bc77ba50",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A\n"
     ]
    }
   ],
   "source": [
    "print(movie_grade(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "7b2fda9a",
   "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>movie 1</th>\n",
       "      <th>movie 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Tom</th>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jeffer</th>\n",
       "      <td>B</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Peter</th>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ram</th>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ted</th>\n",
       "      <td>D</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Paul</th>\n",
       "      <td>E</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       movie 1 movie 2\n",
       "Tom          A       B\n",
       "Jeffer       B       A\n",
       "Peter        C       D\n",
       "Ram          C       C\n",
       "Ted          D       B\n",
       "Paul         E       D"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movie_rating.applymap(movie_grade)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c3ae176",
   "metadata": {},
   "source": [
    "## Data operations with statistical functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "eb9933c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "7c5051c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test_scores = pd.DataFrame({'Test1':[95,84,73,88,82,61], 'Test2':[74,85,82,73,77,79]}, index=['Jack','Lewis','Patrick','Rich','Kelly','Paula'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "30fb7bea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Test1    95\n",
       "Test2    85\n",
       "dtype: int64"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test_scores.max()   # max function to find maximum score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "5dfc3644",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Test1    80.500000\n",
       "Test2    78.333333\n",
       "dtype: float64"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test_scores.mean()   # mean function to find mean of both of them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "a3b88490",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Test1    11.979149\n",
       "Test2     4.633213\n",
       "dtype: float64"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test_scores.std()    # std function to find standard deviation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8315eaaa",
   "metadata": {},
   "source": [
    "### groupby function to operate data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "a48a9ce7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_president_names = pd.DataFrame({'first':['George','Bill','Ronald','Jimmy','George'],'last':['Bush','Clinton','Regan','Carter','Washington']})\n",
    "\n",
    "# create datafrae with president names "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "c9b6bb50",
   "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>first</th>\n",
       "      <th>last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>George</td>\n",
       "      <td>Bush</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bill</td>\n",
       "      <td>Clinton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ronald</td>\n",
       "      <td>Regan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jimmy</td>\n",
       "      <td>Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>George</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    first        last\n",
       "0  George        Bush\n",
       "1    Bill     Clinton\n",
       "2  Ronald       Regan\n",
       "3   Jimmy      Carter\n",
       "4  George  Washington"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_president_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "4decf259",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_only = df_president_names.groupby('first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "050e9f64",
   "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>first</th>\n",
       "      <th>last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>George</td>\n",
       "      <td>Bush</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>George</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    first        last\n",
       "0  George        Bush\n",
       "4  George  Washington"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_only.get_group('George')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "ebcf48e4",
   "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>first</th>\n",
       "      <th>last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bill</td>\n",
       "      <td>Clinton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>George</td>\n",
       "      <td>Bush</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>George</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jimmy</td>\n",
       "      <td>Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ronald</td>\n",
       "      <td>Regan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    first        last\n",
       "1    Bill     Clinton\n",
       "0  George        Bush\n",
       "4  George  Washington\n",
       "3   Jimmy      Carter\n",
       "2  Ronald       Regan"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_president_names.sort_values('first')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cbac45f",
   "metadata": {},
   "source": [
    "### How to stadardise our dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "c7d0a8ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "def standardize_tests(test):   # create funtion to standardize value\n",
    "    return (test-test.mean())/test.std()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "1282ca78",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Jack       1.210437\n",
       "Lewis      0.292174\n",
       "Patrick   -0.626088\n",
       "Rich       0.626088\n",
       "Kelly      0.125218\n",
       "Paula     -1.627829\n",
       "Name: Test1, dtype: float64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "standardize_tests(df_test_scores['Test1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "6da66161",
   "metadata": {},
   "outputs": [],
   "source": [
    "def standardize_test_scores(dataframe):    # apply function to entire dataset\n",
    "    return dataframe.apply(standardize_tests)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "b6ea774f",
   "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>Test1</th>\n",
       "      <th>Test2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Jack</th>\n",
       "      <td>1.210437</td>\n",
       "      <td>-0.935276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lewis</th>\n",
       "      <td>0.292174</td>\n",
       "      <td>1.438886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Patrick</th>\n",
       "      <td>-0.626088</td>\n",
       "      <td>0.791387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rich</th>\n",
       "      <td>0.626088</td>\n",
       "      <td>-1.151109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kelly</th>\n",
       "      <td>0.125218</td>\n",
       "      <td>-0.287777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Paula</th>\n",
       "      <td>-1.627829</td>\n",
       "      <td>0.143889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Test1     Test2\n",
       "Jack     1.210437 -0.935276\n",
       "Lewis    0.292174  1.438886\n",
       "Patrick -0.626088  0.791387\n",
       "Rich     0.626088 -1.151109\n",
       "Kelly    0.125218 -0.287777\n",
       "Paula   -1.627829  0.143889"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "standardize_test_scores(df_test_scores)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9afecfe5",
   "metadata": {},
   "source": [
    "## Pandas Data Operation - Merge, Duplicate & Concatenation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "a5e9f665",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import required libraries\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "d0ff51d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# define student data with math data\n",
    "df_student_math = pd.DataFrame({'student':['Tom','Jack','Dan','Ram','Jeff','David'],'ID':[10,56,31,85,9,22]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "b6ed5c46",
   "metadata": {},
   "outputs": [],
   "source": [
    "# define student data from science data\n",
    "df_student_science = pd.DataFrame({'student':['Tom','Ram','David'],'ID':[10,12,22]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "d1650d15",
   "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>student</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>David</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  ID\n",
       "0     Tom  10\n",
       "1   David  22"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge both data to form single dataframe with math & science data\n",
    "pd.merge(df_student_math,df_student_science)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "4bdb4c4f",
   "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>student</th>\n",
       "      <th>ID_x</th>\n",
       "      <th>ID_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ram</td>\n",
       "      <td>85</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>David</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  ID_x  ID_y\n",
       "0     Tom    10    10\n",
       "1     Ram    85    12\n",
       "2   David    22    22"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge with key on student\n",
    "pd.merge(df_student_math,df_student_science, on='student')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "dae6db0d",
   "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>student_x</th>\n",
       "      <th>ID</th>\n",
       "      <th>student_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>56</td>\n",
       "      <td>X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dan</td>\n",
       "      <td>31</td>\n",
       "      <td>X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ram</td>\n",
       "      <td>85</td>\n",
       "      <td>X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jeff</td>\n",
       "      <td>9</td>\n",
       "      <td>X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>David</td>\n",
       "      <td>22</td>\n",
       "      <td>David</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_x  ID student_y\n",
       "0       Tom  10       Tom\n",
       "1      Jack  56         X\n",
       "2       Dan  31         X\n",
       "3       Ram  85         X\n",
       "4      Jeff   9         X\n",
       "5     David  22     David"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge left join on key ID & also fill NaN values with x\n",
    "pd.merge(df_student_math,df_student_science, on='ID',how='left').fillna('X')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "8860f4e9",
   "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>student</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dan</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ram</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jeff</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>David</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ram</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>David</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  ID\n",
       "0     Tom  10\n",
       "1    Jack  56\n",
       "2     Dan  31\n",
       "3     Ram  85\n",
       "4    Jeff   9\n",
       "5   David  22\n",
       "6     Tom  10\n",
       "7     Ram  12\n",
       "8   David  22"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concat data of both subjects\n",
    "pd.concat([df_student_math, df_student_science],ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "66eef174",
   "metadata": {},
   "outputs": [],
   "source": [
    "# define new data frame with student survey data\n",
    "df_student_survey_data = pd.DataFrame({'student':['Tom','Jack','Tom','Ram','Jeff','Jack'],'ID':[10,56,10,85,9,56]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "57c7f6a5",
   "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>student</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ram</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jeff</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jack</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  ID\n",
       "0     Tom  10\n",
       "1    Jack  56\n",
       "2     Tom  10\n",
       "3     Ram  85\n",
       "4    Jeff   9\n",
       "5    Jack  56"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view the dataframe\n",
    "df_student_survey_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "73f8582b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2     True\n",
       "3    False\n",
       "4    False\n",
       "5     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check for duplicate data\n",
    "df_student_survey_data.duplicated()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "ca92ea19",
   "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>student</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ram</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jeff</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  ID\n",
       "0     Tom  10\n",
       "1    Jack  56\n",
       "3     Ram  85\n",
       "4    Jeff   9"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop duplicate values with student as key\n",
    "df_student_survey_data.drop_duplicates('student')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "7237696a",
   "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>student</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jack</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ram</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jeff</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  ID\n",
       "0     Tom  10\n",
       "1    Jack  56\n",
       "3     Ram  85\n",
       "4    Jeff   9"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop duplicate values with ID as key\n",
    "df_student_survey_data.drop_duplicates('ID')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "785242fa",
   "metadata": {},
   "source": [
    "## Pandas SQL operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "4ffa9e91",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "b7d95060",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create SQL table\n",
    "create_table = \"\"\"CREATE TABLE student_score(Id INTEGER, Name VARCHAR(20),Math REAL,Science REAL);\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "b3ced0dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute SQL statement\n",
    "execute_SQL = sqlite3.connect(':memory:')\n",
    "execute_SQL.execute(create_table)\n",
    "execute_SQL.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "6bbd2663",
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare SQL query\n",
    "SQL_query = execute_SQL.execute('select * from student_score')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "0754fb45",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fetch result from sqlite database\n",
    "resultset = SQL_query.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "8ab8b33a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view result (empty data)\n",
    "resultset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fd77653",
   "metadata": {},
   "source": [
    "### Prepare records in SQL using Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "aebe463f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare records to be inserted into SQL table through SQL statement\n",
    "insertSQL = [(10,'Jack',85,92),(29,'Tom',73,89),(65,'Ram',65.5,77),(5,'Steve',55,91)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "05634602",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x157fc911ab0>"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insert records through SQL statement into SQL table\n",
    "insert_statement = \"Insert into student_score values (?,?,?,?)\"\n",
    "execute_SQL.executemany(insert_statement, insertSQL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "250f035d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# PRepare SQL query\n",
    "SQL_query = execute_SQL.execute(\"select * from student_score\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "2b48f552",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetch resultant for the query\n",
    "resultset = SQL_query.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "f9af0f66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(10, 'Jack', 85.0, 92.0),\n",
       " (29, 'Tom', 73.0, 89.0),\n",
       " (65, 'Ram', 65.5, 77.0),\n",
       " (5, 'Steve', 55.0, 91.0)]"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View the resultant\n",
    "resultset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "bc9aace7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Put records into a dataframe\n",
    "df_student_records = pd.DataFrame(resultset, columns=list(zip(*SQL_query.description))[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "9acb1fe9",
   "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>Id</th>\n",
       "      <th>Name</th>\n",
       "      <th>Math</th>\n",
       "      <th>Science</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>Jack</td>\n",
       "      <td>85.0</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29</td>\n",
       "      <td>Tom</td>\n",
       "      <td>73.0</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>65</td>\n",
       "      <td>Ram</td>\n",
       "      <td>65.5</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve</td>\n",
       "      <td>55.0</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id   Name  Math  Science\n",
       "0  10   Jack  85.0     92.0\n",
       "1  29    Tom  73.0     89.0\n",
       "2  65    Ram  65.5     77.0\n",
       "3   5  Steve  55.0     91.0"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view data in dataframe\n",
    "df_student_records"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}