pandas_04_loc_iloc.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c7f14fd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b5f74480",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>NY</td>\n",
       "      <td>6/1/1930 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>NJ</td>\n",
       "      <td>6/30/1930 20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>CO</td>\n",
       "      <td>2/15/1931 14:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abilene</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>KS</td>\n",
       "      <td>6/1/1931 13:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York Worlds Fair</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIGHT</td>\n",
       "      <td>NY</td>\n",
       "      <td>4/18/1933 19:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Valley City</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>ND</td>\n",
       "      <td>9/15/1934 15:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Crater Lake</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CIRCLE</td>\n",
       "      <td>CA</td>\n",
       "      <td>6/15/1935 0:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Alma</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>MI</td>\n",
       "      <td>7/15/1936 0:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Eklutna</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CIGAR</td>\n",
       "      <td>AK</td>\n",
       "      <td>10/15/1936 17:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Hubbard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CYLINDER</td>\n",
       "      <td>OR</td>\n",
       "      <td>6/15/1937 0:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   City Colors Reported Shape Reported State              Time\n",
       "0                Ithaca             NaN       TRIANGLE    NY    6/1/1930 22:00\n",
       "1           Willingboro             NaN          OTHER    NJ   6/30/1930 20:00\n",
       "2               Holyoke             NaN           OVAL    CO   2/15/1931 14:00\n",
       "3               Abilene             NaN           DISK    KS    6/1/1931 13:00\n",
       "4  New York Worlds Fair             NaN          LIGHT    NY   4/18/1933 19:00\n",
       "5           Valley City             NaN           DISK    ND   9/15/1934 15:30\n",
       "6           Crater Lake             NaN         CIRCLE    CA    6/15/1935 0:00\n",
       "7                  Alma             NaN           DISK    MI    7/15/1936 0:00\n",
       "8               Eklutna             NaN          CIGAR    AK  10/15/1936 17:00\n",
       "9               Hubbard             NaN       CYLINDER    OR    6/15/1937 0:00"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pd.read_csv('http://bit.ly/uforeports').to_csv('Data\\\\uforeports.csv')\n",
    "ufo = pd.read_csv('Data\\\\uforeports.csv', index_col=0)\n",
    "ufo.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b90e218d",
   "metadata": {},
   "source": [
    "# **LOC**\n",
    "\n",
    "Selecting Rows with Labels\n",
    "\n",
    "Syntax = DataFrame.loc[what_rows_do_I_Want : What_cols_do_I_Want]\n",
    "\n",
    "ufo.loc[0:2, :] *#means I want rows 0 to 2 with all columns. Unlike index slicing loc is inclusive from both rows and columns*\n",
    "\n",
    "ufo.loc[0:5, [List of Columns]] *#means first 6 rows with list of columns specified*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "015ff539",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>NY</td>\n",
       "      <td>6/1/1930 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>NJ</td>\n",
       "      <td>6/30/1930 20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>CO</td>\n",
       "      <td>2/15/1931 14:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          City Colors Reported Shape Reported State             Time\n",
       "0       Ithaca             NaN       TRIANGLE    NY   6/1/1930 22:00\n",
       "1  Willingboro             NaN          OTHER    NJ  6/30/1930 20:00\n",
       "2      Holyoke             NaN           OVAL    CO  2/15/1931 14:00"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.loc[0:2, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "3138ea24",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>NY</td>\n",
       "      <td>6/1/1930 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>NJ</td>\n",
       "      <td>6/30/1930 20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>CO</td>\n",
       "      <td>2/15/1931 14:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          City Colors Reported Shape Reported State             Time\n",
       "0       Ithaca             NaN       TRIANGLE    NY   6/1/1930 22:00\n",
       "1  Willingboro             NaN          OTHER    NJ  6/30/1930 20:00\n",
       "2      Holyoke             NaN           OVAL    CO  2/15/1931 14:00"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#If you leave out the comma and write [0:2] it means all columns same as above\n",
    "ufo.loc[0:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "3707346d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abilene</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York Worlds Fair</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   City State\n",
       "0                Ithaca    NY\n",
       "1           Willingboro    NJ\n",
       "2               Holyoke    CO\n",
       "3               Abilene    KS\n",
       "4  New York Worlds Fair    NY"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.loc[0:4, ['City', 'State']] #First 5 rows with City and State Columns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "1a596fb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>NJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abilene</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York Worlds Fair</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIGHT</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18236</th>\n",
       "      <td>Grant Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18237</th>\n",
       "      <td>Spirit Lake</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>IA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18238</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18239</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>RED</td>\n",
       "      <td>LIGHT</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18240</th>\n",
       "      <td>Ybor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>FL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18241 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       City Colors Reported Shape Reported State\n",
       "0                    Ithaca             NaN       TRIANGLE    NY\n",
       "1               Willingboro             NaN          OTHER    NJ\n",
       "2                   Holyoke             NaN           OVAL    CO\n",
       "3                   Abilene             NaN           DISK    KS\n",
       "4      New York Worlds Fair             NaN          LIGHT    NY\n",
       "...                     ...             ...            ...   ...\n",
       "18236            Grant Park             NaN       TRIANGLE    IL\n",
       "18237           Spirit Lake             NaN           DISK    IA\n",
       "18238           Eagle River             NaN            NaN    WI\n",
       "18239           Eagle River             RED          LIGHT    WI\n",
       "18240                  Ybor             NaN           OVAL    FL\n",
       "\n",
       "[18241 rows x 4 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.loc[0:, 'City':'State'] #All rows with columns between City and State"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "3d560f66",
   "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>State</th>\n",
       "      <th>City</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1694</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2144</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4686</th>\n",
       "      <td>MD</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7293</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8488</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8768</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10816</th>\n",
       "      <td>OR</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10948</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11045</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12322</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12941</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16803</th>\n",
       "      <td>MD</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17322</th>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      State     City\n",
       "1694     CA  Oakland\n",
       "2144     CA  Oakland\n",
       "4686     MD  Oakland\n",
       "7293     CA  Oakland\n",
       "8488     CA  Oakland\n",
       "8768     CA  Oakland\n",
       "10816    OR  Oakland\n",
       "10948    CA  Oakland\n",
       "11045    CA  Oakland\n",
       "12322    CA  Oakland\n",
       "12941    CA  Oakland\n",
       "16803    MD  Oakland\n",
       "17322    CA  Oakland"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.loc[ufo.City == 'Oakland', ['State', 'City']] # return all rows where City is Oakland with State and City Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ec51823",
   "metadata": {},
   "source": [
    "# ILOC\n",
    "\n",
    "Selecting rows with Integer Position. I stands for integer\n",
    "\n",
    "DataFrame.iloc[:, 0:4] # Return all rows with range of columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "cce5874c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>NJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abilene</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York Worlds Fair</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIGHT</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18236</th>\n",
       "      <td>Grant Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18237</th>\n",
       "      <td>Spirit Lake</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>IA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18238</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18239</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>RED</td>\n",
       "      <td>LIGHT</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18240</th>\n",
       "      <td>Ybor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>FL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18241 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       City Colors Reported Shape Reported State\n",
       "0                    Ithaca             NaN       TRIANGLE    NY\n",
       "1               Willingboro             NaN          OTHER    NJ\n",
       "2                   Holyoke             NaN           OVAL    CO\n",
       "3                   Abilene             NaN           DISK    KS\n",
       "4      New York Worlds Fair             NaN          LIGHT    NY\n",
       "...                     ...             ...            ...   ...\n",
       "18236            Grant Park             NaN       TRIANGLE    IL\n",
       "18237           Spirit Lake             NaN           DISK    IA\n",
       "18238           Eagle River             NaN            NaN    WI\n",
       "18239           Eagle River             RED          LIGHT    WI\n",
       "18240                  Ybor             NaN           OVAL    FL\n",
       "\n",
       "[18241 rows x 4 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.iloc[:, 0:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "eef4b7ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>NY</td>\n",
       "      <td>6/1/1930 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>NJ</td>\n",
       "      <td>6/30/1930 20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>CO</td>\n",
       "      <td>2/15/1931 14:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          City Colors Reported Shape Reported State             Time\n",
       "0       Ithaca             NaN       TRIANGLE    NY   6/1/1930 22:00\n",
       "1  Willingboro             NaN          OTHER    NJ  6/30/1930 20:00\n",
       "2      Holyoke             NaN           OVAL    CO  2/15/1931 14:00"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.iloc[0:3, :] #First 3 rows with all columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "14ff13bd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          City Colors Reported Shape Reported\n",
       "0       Ithaca             NaN       TRIANGLE\n",
       "1  Willingboro             NaN          OTHER\n",
       "2      Holyoke             NaN           OVAL"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.iloc[0:3, 0:3] #First 3 rows with first 3 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "bb793912",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ithaca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6/1/1930 22:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Willingboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6/30/1930 20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Holyoke</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2/15/1931 14:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          City Colors Reported             Time\n",
       "0       Ithaca             NaN   6/1/1930 22:00\n",
       "1  Willingboro             NaN  6/30/1930 20:00\n",
       "2      Holyoke             NaN  2/15/1931 14:00"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.iloc[0:3, [0,1,4]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10ee083e",
   "metadata": {},
   "source": [
    "# Setting and removing Indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "d17a7f6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             date     city        country  avg_temp_c\n",
      "0      2000-01-01  Abidjan  Côte D'Ivoire      27.293\n",
      "1      2000-02-01  Abidjan  Côte D'Ivoire      27.685\n",
      "2      2000-03-01  Abidjan  Côte D'Ivoire      29.061\n",
      "3      2000-04-01  Abidjan  Côte D'Ivoire      28.162\n",
      "4      2000-05-01  Abidjan  Côte D'Ivoire      27.547\n",
      "...           ...      ...            ...         ...\n",
      "16495  2013-05-01     Xian          China      18.979\n",
      "16496  2013-06-01     Xian          China      23.522\n",
      "16497  2013-07-01     Xian          China      25.251\n",
      "16498  2013-08-01     Xian          China      24.528\n",
      "16499  2013-09-01     Xian          China         NaN\n",
      "\n",
      "[16500 rows x 4 columns]\n",
      "               date        country  avg_temp_c\n",
      "city                                          \n",
      "Abidjan  2000-01-01  Côte D'Ivoire      27.293\n",
      "Abidjan  2000-02-01  Côte D'Ivoire      27.685\n",
      "Abidjan  2000-03-01  Côte D'Ivoire      29.061\n",
      "Abidjan  2000-04-01  Côte D'Ivoire      28.162\n",
      "Abidjan  2000-05-01  Côte D'Ivoire      27.547\n",
      "...             ...            ...         ...\n",
      "Xian     2013-05-01          China      18.979\n",
      "Xian     2013-06-01          China      23.522\n",
      "Xian     2013-07-01          China      25.251\n",
      "Xian     2013-08-01          China      24.528\n",
      "Xian     2013-09-01          China         NaN\n",
      "\n",
      "[16500 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "temperatures = pd.read_csv('Data/temperatures.csv', index_col=0)\n",
    "# Look at temperatures\n",
    "print(temperatures)\n",
    "\n",
    "# Index temperatures by city\n",
    "temperatures_ind = temperatures.set_index('city')\n",
    "\n",
    "# Look at temperatures_ind\n",
    "print(temperatures_ind)\n",
    "\n",
    "#print(temperatures_ind.reset_index())\n",
    "\n",
    "# Reset the index, dropping its contents\n",
    "#print(temperatures_ind.reset_index(drop=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02501f36",
   "metadata": {},
   "source": [
    "# Indexing with .loc[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "98f24ec9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             date              city country  avg_temp_c\n",
      "10725  2000-01-01            Moscow  Russia      -7.313\n",
      "10726  2000-02-01            Moscow  Russia      -3.551\n",
      "10727  2000-03-01            Moscow  Russia      -1.661\n",
      "10728  2000-04-01            Moscow  Russia      10.096\n",
      "10729  2000-05-01            Moscow  Russia      10.357\n",
      "...           ...               ...     ...         ...\n",
      "13360  2013-05-01  Saint Petersburg  Russia      12.355\n",
      "13361  2013-06-01  Saint Petersburg  Russia      17.185\n",
      "13362  2013-07-01  Saint Petersburg  Russia      17.234\n",
      "13363  2013-08-01  Saint Petersburg  Russia      17.153\n",
      "13364  2013-09-01  Saint Petersburg  Russia         NaN\n",
      "\n",
      "[330 rows x 4 columns]\n",
      "                        date country  avg_temp_c\n",
      "city                                            \n",
      "Moscow            2000-01-01  Russia      -7.313\n",
      "Moscow            2000-02-01  Russia      -3.551\n",
      "Moscow            2000-03-01  Russia      -1.661\n",
      "Moscow            2000-04-01  Russia      10.096\n",
      "Moscow            2000-05-01  Russia      10.357\n",
      "...                      ...     ...         ...\n",
      "Saint Petersburg  2013-05-01  Russia      12.355\n",
      "Saint Petersburg  2013-06-01  Russia      17.185\n",
      "Saint Petersburg  2013-07-01  Russia      17.234\n",
      "Saint Petersburg  2013-08-01  Russia      17.153\n",
      "Saint Petersburg  2013-09-01  Russia         NaN\n",
      "\n",
      "[330 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# Make a list of cities to subset on\n",
    "cities = [\"Moscow\", \"Saint Petersburg\"]\n",
    "# Subset temperatures using square brackets\n",
    "print(temperatures[temperatures['city'].isin(cities)])\n",
    "print(temperatures_ind.loc[cities])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "4653d136",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                               date  avg_temp_c\n",
      "country  city                                  \n",
      "Brazil   Rio De Janeiro  2000-01-01      25.974\n",
      "         Rio De Janeiro  2000-02-01      26.699\n",
      "         Rio De Janeiro  2000-03-01      26.270\n",
      "         Rio De Janeiro  2000-04-01      25.750\n",
      "         Rio De Janeiro  2000-05-01      24.356\n",
      "...                             ...         ...\n",
      "Pakistan Lahore          2013-05-01      33.457\n",
      "         Lahore          2013-06-01      34.456\n",
      "         Lahore          2013-07-01      33.279\n",
      "         Lahore          2013-08-01      31.511\n",
      "         Lahore          2013-09-01         NaN\n",
      "\n",
      "[330 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Index temperatures by country & city\n",
    "temperatures_ind = temperatures.set_index(['country', 'city'])\n",
    "\n",
    "# List of tuples: Brazil, Rio De Janeiro & Pakistan, Lahore\n",
    "rows_to_keep = [('Brazil', 'Rio De Janeiro'), ('Pakistan', 'Lahore')]\n",
    "\n",
    "# Subset for rows to keep\n",
    "print(temperatures_ind.loc[rows_to_keep])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f47abf43",
   "metadata": {},
   "source": [
    "## Sorting by Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6448cb52",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                          date  avg_temp_c\n",
      "country     city                          \n",
      "Afghanistan Kabul   2000-01-01       3.326\n",
      "            Kabul   2000-02-01       3.454\n",
      "            Kabul   2000-03-01       9.612\n",
      "            Kabul   2000-04-01      17.925\n",
      "            Kabul   2000-05-01      24.658\n",
      "...                        ...         ...\n",
      "Zimbabwe    Harare  2013-05-01      18.298\n",
      "            Harare  2013-06-01      17.020\n",
      "            Harare  2013-07-01      16.299\n",
      "            Harare  2013-08-01      19.232\n",
      "            Harare  2013-09-01         NaN\n",
      "\n",
      "[16500 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Sort temperatures_ind by index values\n",
    "print(temperatures_ind.sort_index())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "54c37945",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                             date  avg_temp_c\n",
      "country       city                           \n",
      "Côte D'Ivoire Abidjan  2000-01-01      27.293\n",
      "              Abidjan  2000-02-01      27.685\n",
      "              Abidjan  2000-03-01      29.061\n",
      "              Abidjan  2000-04-01      28.162\n",
      "              Abidjan  2000-05-01      27.547\n",
      "...                           ...         ...\n",
      "China         Xian     2013-05-01      18.979\n",
      "              Xian     2013-06-01      23.522\n",
      "              Xian     2013-07-01      25.251\n",
      "              Xian     2013-08-01      24.528\n",
      "              Xian     2013-09-01         NaN\n",
      "\n",
      "[16500 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Sort temperatures_ind by index values at the city level\n",
    "print(temperatures_ind.sort_index(level=['city']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "bb3c4ab9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                          date  avg_temp_c\n",
      "country     city                          \n",
      "Afghanistan Kabul   2000-01-01       3.326\n",
      "            Kabul   2000-02-01       3.454\n",
      "            Kabul   2000-03-01       9.612\n",
      "            Kabul   2000-04-01      17.925\n",
      "            Kabul   2000-05-01      24.658\n",
      "...                        ...         ...\n",
      "Zimbabwe    Harare  2013-05-01      18.298\n",
      "            Harare  2013-06-01      17.020\n",
      "            Harare  2013-07-01      16.299\n",
      "            Harare  2013-08-01      19.232\n",
      "            Harare  2013-09-01         NaN\n",
      "\n",
      "[16500 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Sort temperatures_ind by country then descending city\n",
    "print(temperatures_ind.sort_index(level=['country', 'city'], ascending=[True, False]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb5dd4fb",
   "metadata": {},
   "source": [
    "# Slicing DataFrames\n",
    "\n",
    "You can only slice based on index if index is sorted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "910e44b5",
   "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></th>\n",
       "      <th>date</th>\n",
       "      <th>avg_temp_c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Afghanistan</th>\n",
       "      <th>Kabul</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>3.326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kabul</th>\n",
       "      <td>2000-02-01</td>\n",
       "      <td>3.454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kabul</th>\n",
       "      <td>2000-03-01</td>\n",
       "      <td>9.612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kabul</th>\n",
       "      <td>2000-04-01</td>\n",
       "      <td>17.925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kabul</th>\n",
       "      <td>2000-05-01</td>\n",
       "      <td>24.658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Zimbabwe</th>\n",
       "      <th>Harare</th>\n",
       "      <td>2013-05-01</td>\n",
       "      <td>18.298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Harare</th>\n",
       "      <td>2013-06-01</td>\n",
       "      <td>17.020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Harare</th>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>16.299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Harare</th>\n",
       "      <td>2013-08-01</td>\n",
       "      <td>19.232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Harare</th>\n",
       "      <td>2013-09-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16500 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          date  avg_temp_c\n",
       "country     city                          \n",
       "Afghanistan Kabul   2000-01-01       3.326\n",
       "            Kabul   2000-02-01       3.454\n",
       "            Kabul   2000-03-01       9.612\n",
       "            Kabul   2000-04-01      17.925\n",
       "            Kabul   2000-05-01      24.658\n",
       "...                        ...         ...\n",
       "Zimbabwe    Harare  2013-05-01      18.298\n",
       "            Harare  2013-06-01      17.020\n",
       "            Harare  2013-07-01      16.299\n",
       "            Harare  2013-08-01      19.232\n",
       "            Harare  2013-09-01         NaN\n",
       "\n",
       "[16500 rows x 2 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort the index of temperatures_ind\n",
    "temperatures_srt = temperatures_ind.sort_index()\n",
    "temperatures_srt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "2b14d9ee",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                 date  avg_temp_c\n",
      "country  city                                    \n",
      "Pakistan Faisalabad        2000-01-01      12.792\n",
      "         Faisalabad        2000-02-01      14.339\n",
      "         Faisalabad        2000-03-01      20.309\n",
      "         Faisalabad        2000-04-01      29.072\n",
      "         Faisalabad        2000-05-01      34.845\n",
      "...                               ...         ...\n",
      "Russia   Saint Petersburg  2013-05-01      12.355\n",
      "         Saint Petersburg  2013-06-01      17.185\n",
      "         Saint Petersburg  2013-07-01      17.234\n",
      "         Saint Petersburg  2013-08-01      17.153\n",
      "         Saint Petersburg  2013-09-01         NaN\n",
      "\n",
      "[1155 rows x 2 columns]\n",
      "                          date  avg_temp_c\n",
      "country city                              \n",
      "Mexico  Mexico      2000-01-01      12.694\n",
      "        Mexico      2000-02-01      14.677\n",
      "        Mexico      2000-03-01      17.376\n",
      "        Mexico      2000-04-01      18.294\n",
      "        Mexico      2000-05-01      18.562\n",
      "...                        ...         ...\n",
      "Morocco Casablanca  2013-05-01      19.217\n",
      "        Casablanca  2013-06-01      23.649\n",
      "        Casablanca  2013-07-01      27.488\n",
      "        Casablanca  2013-08-01      27.952\n",
      "        Casablanca  2013-09-01         NaN\n",
      "\n",
      "[330 rows x 2 columns]\n",
      "                       date  avg_temp_c\n",
      "country  city                          \n",
      "Pakistan Lahore  2000-01-01      12.792\n",
      "         Lahore  2000-02-01      14.339\n",
      "         Lahore  2000-03-01      20.309\n",
      "         Lahore  2000-04-01      29.072\n",
      "         Lahore  2000-05-01      34.845\n",
      "...                     ...         ...\n",
      "Russia   Moscow  2013-05-01      16.152\n",
      "         Moscow  2013-06-01      18.718\n",
      "         Moscow  2013-07-01      18.136\n",
      "         Moscow  2013-08-01      17.485\n",
      "         Moscow  2013-09-01         NaN\n",
      "\n",
      "[660 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Subset rows from Pakistan to Russia\n",
    "print(temperatures_srt.loc['Pakistan' : 'Russia'])\n",
    "\n",
    "# Try to subset rows from Lahore to Moscow\n",
    "print(temperatures_srt.loc[('Lahore') : ('Moscow')])\n",
    "\n",
    "# Subset rows from Pakistan, Lahore to Russia, Moscow\n",
    "print(temperatures_srt.loc[('Pakistan', 'Lahore') : ('Russia', 'Moscow')])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1de6631",
   "metadata": {},
   "source": [
    "# Slicing in both Direction i.e. Rows and Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "8d5a0863",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                         date  avg_temp_c\n",
      "country city                             \n",
      "India   Hyderabad  2000-01-01      23.779\n",
      "        Hyderabad  2000-02-01      25.826\n",
      "        Hyderabad  2000-03-01      28.821\n",
      "        Hyderabad  2000-04-01      32.698\n",
      "        Hyderabad  2000-05-01      32.438\n",
      "...                       ...         ...\n",
      "Iraq    Baghdad    2013-05-01      28.673\n",
      "        Baghdad    2013-06-01      33.803\n",
      "        Baghdad    2013-07-01      36.392\n",
      "        Baghdad    2013-08-01      35.463\n",
      "        Baghdad    2013-09-01         NaN\n",
      "\n",
      "[2145 rows x 2 columns]\n",
      "                          date  avg_temp_c\n",
      "country     city                          \n",
      "Afghanistan Kabul   2000-01-01       3.326\n",
      "            Kabul   2000-02-01       3.454\n",
      "            Kabul   2000-03-01       9.612\n",
      "            Kabul   2000-04-01      17.925\n",
      "            Kabul   2000-05-01      24.658\n",
      "...                        ...         ...\n",
      "Zimbabwe    Harare  2013-05-01      18.298\n",
      "            Harare  2013-06-01      17.020\n",
      "            Harare  2013-07-01      16.299\n",
      "            Harare  2013-08-01      19.232\n",
      "            Harare  2013-09-01         NaN\n",
      "\n",
      "[16500 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Subset rows from India, Hyderabad to Iraq, Baghdad\n",
    "print(temperatures_srt.loc[('India', 'Hyderabad') : ('Iraq', 'Baghdad')])\n",
    "\n",
    "# Subset columns from date to avg_temp_c\n",
    "print(temperatures_srt.loc[:, 'date':'avg_temp_c'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "00f5dc49",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                         date  avg_temp_c\n",
      "country city                             \n",
      "India   Hyderabad  2000-01-01      23.779\n",
      "        Hyderabad  2000-02-01      25.826\n",
      "        Hyderabad  2000-03-01      28.821\n",
      "        Hyderabad  2000-04-01      32.698\n",
      "        Hyderabad  2000-05-01      32.438\n",
      "...                       ...         ...\n",
      "Iraq    Baghdad    2013-05-01      28.673\n",
      "        Baghdad    2013-06-01      33.803\n",
      "        Baghdad    2013-07-01      36.392\n",
      "        Baghdad    2013-08-01      35.463\n",
      "        Baghdad    2013-09-01         NaN\n",
      "\n",
      "[2145 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Subset in both directions at once\n",
    "print(temperatures_srt.loc[('India', 'Hyderabad') : ('Iraq', 'Baghdad') , 'date' : 'avg_temp_c'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "1f12f288",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                         date  avg_temp_c\n",
      "country city                             \n",
      "India   Hyderabad  2000-01-01      23.779\n",
      "        Hyderabad  2000-02-01      25.826\n",
      "        Hyderabad  2000-03-01      28.821\n",
      "        Hyderabad  2000-04-01      32.698\n",
      "        Hyderabad  2000-05-01      32.438\n",
      "...                       ...         ...\n",
      "Iraq    Baghdad    2013-05-01      28.673\n",
      "        Baghdad    2013-06-01      33.803\n",
      "        Baghdad    2013-07-01      36.392\n",
      "        Baghdad    2013-08-01      35.463\n",
      "        Baghdad    2013-09-01         NaN\n",
      "\n",
      "[2145 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Subset in both directions at once\n",
    "print(temperatures_srt.loc[('India', 'Hyderabad') : ('Iraq', 'Baghdad') , 'date' : 'avg_temp_c'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "615af10c",
   "metadata": {},
   "source": [
    "# Slicing Date Time data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "cc85c748",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             date     city        country  avg_temp_c\n",
      "120    2010-01-01  Abidjan  Côte D'Ivoire      28.270\n",
      "121    2010-02-01  Abidjan  Côte D'Ivoire      29.262\n",
      "122    2010-03-01  Abidjan  Côte D'Ivoire      29.596\n",
      "123    2010-04-01  Abidjan  Côte D'Ivoire      29.068\n",
      "124    2010-05-01  Abidjan  Côte D'Ivoire      28.258\n",
      "...           ...      ...            ...         ...\n",
      "16474  2011-08-01     Xian          China      23.069\n",
      "16475  2011-09-01     Xian          China      16.775\n",
      "16476  2011-10-01     Xian          China      12.587\n",
      "16477  2011-11-01     Xian          China       7.543\n",
      "16478  2011-12-01     Xian          China      -0.490\n",
      "\n",
      "[2400 rows x 4 columns]\n",
      "               city        country  avg_temp_c\n",
      "date                                          \n",
      "2000-01-01  Abidjan  Côte D'Ivoire      27.293\n",
      "2000-02-01  Abidjan  Côte D'Ivoire      27.685\n",
      "2000-03-01  Abidjan  Côte D'Ivoire      29.061\n",
      "2000-04-01  Abidjan  Côte D'Ivoire      28.162\n",
      "2000-05-01  Abidjan  Côte D'Ivoire      27.547\n",
      "...             ...            ...         ...\n",
      "2013-05-01     Xian          China      18.979\n",
      "2013-06-01     Xian          China      23.522\n",
      "2013-07-01     Xian          China      25.251\n",
      "2013-08-01     Xian          China      24.528\n",
      "2013-09-01     Xian          China         NaN\n",
      "\n",
      "[16500 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# Use Boolean conditions to subset temperatures for rows in 2010 and 2011\n",
    "temperatures_bool = temperatures[(temperatures.date >= \"2010-01-01\") & (temperatures.date <= \"2011-12-31\")]\n",
    "print(temperatures_bool)\n",
    "\n",
    "# Set date as the index and sort the index\n",
    "temperatures_ind = temperatures.set_index('date')\n",
    "print(temperatures_ind)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "20ebdfa4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                  city    country  avg_temp_c\n",
      "date                                         \n",
      "2010-01-01  Faisalabad   Pakistan      11.810\n",
      "2010-01-01   Melbourne  Australia      20.016\n",
      "2010-01-01   Chongqing      China       7.921\n",
      "2010-01-01   São Paulo     Brazil      23.738\n",
      "2010-01-01   Guangzhou      China      14.136\n",
      "...                ...        ...         ...\n",
      "2010-12-01     Jakarta  Indonesia      26.602\n",
      "2010-12-01       Gizeh      Egypt      16.530\n",
      "2010-12-01      Nagpur      India      19.120\n",
      "2010-12-01      Sydney  Australia      19.559\n",
      "2010-12-01    Salvador     Brazil      26.265\n",
      "\n",
      "[1200 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# First sort it and then slice it\n",
    "temperatures_ind.sort_index(inplace=True)\n",
    "\n",
    "# Use .loc[] to subset temperatures_ind for rows in 2010 and 2011\n",
    "print(temperatures_ind.loc[\"2010\" : \"2011\", :])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "300f676e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [city, country, avg_temp_c]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "# Use .loc[] to subset temperatures_ind for rows from Aug 2010 to Feb 2011\n",
    "print(temperatures_ind.loc[\"08-2010\" : \"02-2011\", :])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab27dacd",
   "metadata": {},
   "source": [
    "# Slicing using Row/Column Numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "35d2e97d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          date     city        country  avg_temp_c\n",
      "1   2000-02-01  Abidjan  Côte D'Ivoire      27.685\n",
      "2   2000-03-01  Abidjan  Côte D'Ivoire      29.061\n",
      "3   2000-04-01  Abidjan  Côte D'Ivoire      28.162\n",
      "4   2000-05-01  Abidjan  Côte D'Ivoire      27.547\n",
      "5   2000-06-01  Abidjan  Côte D'Ivoire      25.812\n",
      "6   2000-07-01  Abidjan  Côte D'Ivoire      24.870\n",
      "7   2000-08-01  Abidjan  Côte D'Ivoire      24.884\n",
      "8   2000-09-01  Abidjan  Côte D'Ivoire      25.405\n",
      "9   2000-10-01  Abidjan  Côte D'Ivoire      26.074\n",
      "10  2000-11-01  Abidjan  Côte D'Ivoire      27.315\n",
      "11  2000-12-01  Abidjan  Côte D'Ivoire      26.929\n",
      "12  2001-01-01  Abidjan  Côte D'Ivoire      26.920\n",
      "13  2001-02-01  Abidjan  Côte D'Ivoire      28.234\n",
      "14  2001-03-01  Abidjan  Côte D'Ivoire      28.706\n",
      "15  2001-04-01  Abidjan  Côte D'Ivoire      27.702\n",
      "16  2001-05-01  Abidjan  Côte D'Ivoire      27.653\n",
      "17  2001-06-01  Abidjan  Côte D'Ivoire      25.940\n",
      "18  2001-07-01  Abidjan  Côte D'Ivoire      24.841\n",
      "19  2001-08-01  Abidjan  Côte D'Ivoire      24.280\n",
      "20  2001-09-01  Abidjan  Côte D'Ivoire      24.797\n",
      "21  2001-10-01  Abidjan  Côte D'Ivoire      26.278\n",
      "22  2001-11-01  Abidjan  Côte D'Ivoire      27.374\n",
      "         date     city        country  avg_temp_c\n",
      "0  2000-01-01  Abidjan  Côte D'Ivoire      27.293\n",
      "1  2000-02-01  Abidjan  Côte D'Ivoire      27.685\n",
      "2  2000-03-01  Abidjan  Côte D'Ivoire      29.061\n",
      "3  2000-04-01  Abidjan  Côte D'Ivoire      28.162\n",
      "4  2000-05-01  Abidjan  Côte D'Ivoire      27.547\n",
      "             country  avg_temp_c\n",
      "0      Côte D'Ivoire      27.293\n",
      "1      Côte D'Ivoire      27.685\n",
      "2      Côte D'Ivoire      29.061\n",
      "3      Côte D'Ivoire      28.162\n",
      "4      Côte D'Ivoire      27.547\n",
      "...              ...         ...\n",
      "16495          China      18.979\n",
      "16496          China      23.522\n",
      "16497          China      25.251\n",
      "16498          China      24.528\n",
      "16499          China         NaN\n",
      "\n",
      "[16500 rows x 2 columns]\n",
      "         country  avg_temp_c\n",
      "0  Côte D'Ivoire      27.293\n",
      "1  Côte D'Ivoire      27.685\n",
      "2  Côte D'Ivoire      29.061\n",
      "3  Côte D'Ivoire      28.162\n",
      "4  Côte D'Ivoire      27.547\n"
     ]
    }
   ],
   "source": [
    "# Get 23rd row, 2nd column (index 22, 1)\n",
    "print(temperatures.iloc[1 : 23, :])\n",
    "\n",
    "# Use slicing to get the first 5 rows\n",
    "print(temperatures.iloc[:5, :])\n",
    "\n",
    "# Use slicing to get columns 3 to 4\n",
    "print(temperatures.iloc[:,2:4])\n",
    "\n",
    "# Use slicing in both directions at once\n",
    "print(temperatures.iloc[0:5, 2:4])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c474c105",
   "metadata": {},
   "source": [
    "# Subsetting and Slicing using Pivot Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "e27caa90",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 16500 entries, 0 to 16499\n",
      "Data columns (total 5 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   date        16500 non-null  object \n",
      " 1   city        16500 non-null  object \n",
      " 2   country     16500 non-null  object \n",
      " 3   avg_temp_c  16407 non-null  float64\n",
      " 4   year        16500 non-null  int64  \n",
      "dtypes: float64(1), int64(1), object(3)\n",
      "memory usage: 773.4+ KB\n",
      "None\n",
      "         date     city        country  avg_temp_c  year\n",
      "0  2000-01-01  Abidjan  Côte D'Ivoire      27.293  2000\n",
      "1  2000-02-01  Abidjan  Côte D'Ivoire      27.685  2000\n",
      "2  2000-03-01  Abidjan  Côte D'Ivoire      29.061  2000\n",
      "3  2000-04-01  Abidjan  Côte D'Ivoire      28.162  2000\n",
      "4  2000-05-01  Abidjan  Côte D'Ivoire      27.547  2000\n",
      "Temperature = \n",
      "          date     city        country  avg_temp_c  year\n",
      "0  2000-01-01  Abidjan  Côte D'Ivoire      27.293  2000\n",
      "1  2000-02-01  Abidjan  Côte D'Ivoire      27.685  2000\n",
      "2  2000-03-01  Abidjan  Côte D'Ivoire      29.061  2000\n",
      "3  2000-04-01  Abidjan  Côte D'Ivoire      28.162  2000\n",
      "4  2000-05-01  Abidjan  Côte D'Ivoire      27.547  2000 \n",
      "\n",
      "\n",
      "\n",
      "temp_by_country_city_vs_year = \n",
      " year                                 2000       2001       2002       2003  \\\n",
      "country       city                                                           \n",
      "Afghanistan   Kabul             15.822667  15.847917  15.714583  15.132583   \n",
      "Angola        Luanda            24.410333  24.427083  24.790917  24.867167   \n",
      "Australia     Melbourne         14.320083  14.180000  14.075833  13.985583   \n",
      "              Sydney            17.567417  17.854500  17.733833  17.592333   \n",
      "Bangladesh    Dhaka             25.905250  25.931250  26.095000  25.927417   \n",
      "...                                   ...        ...        ...        ...   \n",
      "United States Chicago           11.089667  11.703083  11.532083  10.481583   \n",
      "              Los Angeles       16.643333  16.466250  16.430250  16.944667   \n",
      "              New York           9.969083  10.931000  11.252167   9.836000   \n",
      "Vietnam       Ho Chi Minh City  27.588917  27.831750  28.064750  27.827667   \n",
      "Zimbabwe      Harare            20.283667  20.861000  21.079333  20.889167   \n",
      "\n",
      "year                                 2004       2005       2006       2007  \\\n",
      "country       city                                                           \n",
      "Afghanistan   Kabul             16.128417  14.847500  15.798500  15.518000   \n",
      "Angola        Luanda            24.216167  24.414583  24.138417  24.241583   \n",
      "Australia     Melbourne         13.742083  14.378500  13.991083  14.991833   \n",
      "              Sydney            17.869667  18.028083  17.749500  18.020833   \n",
      "Bangladesh    Dhaka             26.136083  26.193333  26.440417  25.951333   \n",
      "...                                   ...        ...        ...        ...   \n",
      "United States Chicago           10.943417  11.583833  11.870500  11.448333   \n",
      "              Los Angeles       16.552833  16.431417  16.623083  16.699917   \n",
      "              New York          10.389500  10.681417  11.519250  10.627333   \n",
      "Vietnam       Ho Chi Minh City  27.686583  27.884000  28.044000  27.866667   \n",
      "Zimbabwe      Harare            20.307667  21.487417  20.699750  20.746250   \n",
      "\n",
      "year                                 2008       2009       2010       2011  \\\n",
      "country       city                                                           \n",
      "Afghanistan   Kabul             15.479250  15.093333  15.676000  15.812167   \n",
      "Angola        Luanda            24.266333  24.325083  24.440250  24.150750   \n",
      "Australia     Melbourne         14.110583  14.647417  14.231667  14.190917   \n",
      "              Sydney            17.321083  18.175833  17.999000  17.713333   \n",
      "Bangladesh    Dhaka             26.004500  26.535583  26.648167  25.803250   \n",
      "...                                   ...        ...        ...        ...   \n",
      "United States Chicago           10.242417  10.298333  11.815917  11.214250   \n",
      "              Los Angeles       17.014750  16.677000  15.887000  15.874833   \n",
      "              New York          10.641667  10.141833  11.357583  11.272250   \n",
      "Vietnam       Ho Chi Minh City  27.611417  27.853333  28.281750  27.675417   \n",
      "Zimbabwe      Harare            20.680500  20.523833  21.165833  20.781750   \n",
      "\n",
      "year                                 2012       2013  \n",
      "country       city                                    \n",
      "Afghanistan   Kabul             14.510333  16.206125  \n",
      "Angola        Luanda            24.240083  24.553875  \n",
      "Australia     Melbourne         14.268667  14.741500  \n",
      "              Sydney            17.474333  18.089750  \n",
      "Bangladesh    Dhaka             26.283583  26.587000  \n",
      "...                                   ...        ...  \n",
      "United States Chicago           12.821250  11.586889  \n",
      "              Los Angeles       17.089583  18.120667  \n",
      "              New York          11.971500  12.163889  \n",
      "Vietnam       Ho Chi Minh City  28.248750  28.455000  \n",
      "Zimbabwe      Harare            20.523333  19.756500  \n",
      "\n",
      "[100 rows x 14 columns] \n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import datetime as dt\n",
    "from numpy import mean\n",
    "\n",
    "print(temperatures.info(), temperatures.head(), sep=\"\\n\")\n",
    "\n",
    "# Add a year column to temperatures\n",
    "# Use apply to convert string values to date and get year from it.\n",
    "temperatures['year'] = temperatures['date'].apply(lambda x: dt.datetime.strptime(x, '%Y-%m-%d').year)\n",
    "\n",
    "print(\"Temperature = \\n {} \\n\\n\\n\".format(temperatures.head()))\n",
    "\n",
    "# Pivot avg_temp_c by country and city vs year\n",
    "temp_by_country_city_vs_year = temperatures.pivot_table(values='avg_temp_c', index=['country', 'city'], columns='year')\n",
    "\n",
    "# See the result\n",
    "print(\"temp_by_country_city_vs_year = \\n {} \\n\\n\\n\".format(temp_by_country_city_vs_year))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d7f3e21",
   "metadata": {},
   "source": [
    "## A pivot table is just like a DataFrame with sorted indexes. You can use the same subsetting and slicing methods on it as we used for DataFrames.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "9704a13f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "year                       2000       2001       2002       2003       2004  \\\n",
      "country  city                                                                 \n",
      "Egypt    Alexandria   20.744500  21.454583  21.456167  21.221417  21.064167   \n",
      "         Cairo        21.486167  22.330833  22.414083  22.170500  22.081917   \n",
      "         Gizeh        21.486167  22.330833  22.414083  22.170500  22.081917   \n",
      "Ethiopia Addis Abeba  18.241250  18.296417  18.469750  18.320917  18.292750   \n",
      "France   Paris        11.739667  11.371250  11.871333  11.909500  11.338833   \n",
      "Germany  Berlin       10.963667   9.690250  10.264417  10.065750   9.822583   \n",
      "India    Ahmadabad    27.436000  27.198083  27.719083  27.403833  27.628333   \n",
      "         Bangalore    25.337917  25.528167  25.755333  25.924750  25.252083   \n",
      "         Bombay       27.203667  27.243667  27.628667  27.578417  27.318750   \n",
      "         Calcutta     26.491333  26.515167  26.703917  26.561333  26.634333   \n",
      "         Delhi        26.048333  25.862917  26.634333  25.721083  26.239917   \n",
      "         Hyderabad    27.231833  27.555167  27.665167  27.844583  27.228500   \n",
      "         Jaipur       26.430250  26.023000  27.031667  26.027417  26.642167   \n",
      "         Kanpur       25.353917  25.326500  26.116833  25.409083  25.586500   \n",
      "         Lakhnau      25.353917  25.326500  26.116833  25.409083  25.586500   \n",
      "         Madras       28.811667  29.162917  29.246500  29.273083  28.810917   \n",
      "         Nagpur       26.181417  26.321667  26.753000  26.503833  26.406333   \n",
      "         New Delhi    26.048333  25.862917  26.634333  25.721083  26.239917   \n",
      "         Pune         25.110917  25.337833  25.583000  25.748417  25.316167   \n",
      "         Surat        27.029000  26.897250  27.347750  27.230917  27.290583   \n",
      "\n",
      "year                       2005       2006       2007       2008       2009  \\\n",
      "country  city                                                                 \n",
      "Egypt    Alexandria   21.082333  21.148167  21.507750  21.739000  21.670500   \n",
      "         Cairo        22.006500  22.050000  22.361000  22.644500  22.625000   \n",
      "         Gizeh        22.006500  22.050000  22.361000  22.644500  22.625000   \n",
      "Ethiopia Addis Abeba  18.312833  18.427083  18.142583  18.165000  18.765333   \n",
      "France   Paris        11.552917  11.788500  11.750833  11.278250  11.464083   \n",
      "Germany  Berlin        9.919083  10.545333  10.883167  10.657750  10.062500   \n",
      "India    Ahmadabad    26.828083  27.282833  27.511167  27.048500  28.095833   \n",
      "         Bangalore    25.476500  25.418250  25.464333  25.352583  25.725750   \n",
      "         Bombay       27.035750  27.381500  27.634667  27.177750  27.844500   \n",
      "         Calcutta     26.729167  26.986250  26.584583  26.522333  27.153250   \n",
      "         Delhi        25.716083  26.365917  26.145667  25.675000  26.554250   \n",
      "         Hyderabad    27.488833  27.471000  27.545333  27.446667  28.026500   \n",
      "         Jaipur       25.772250  26.429833  26.267000  25.877583  26.919500   \n",
      "         Kanpur       25.621500  25.848833  25.436083  25.109750  25.987083   \n",
      "         Lakhnau      25.621500  25.848833  25.436083  25.109750  25.987083   \n",
      "         Madras       29.099417  29.073250  28.970167  29.024083  29.417417   \n",
      "         Nagpur       26.229167  26.374833  26.393667  26.188000  27.138917   \n",
      "         New Delhi    25.716083  26.365917  26.145667  25.675000  26.554250   \n",
      "         Pune         25.085583  25.310167  25.584833  25.230250  25.868250   \n",
      "         Surat        26.724500  27.149417  27.311750  26.828750  27.819833   \n",
      "\n",
      "year                       2010       2011       2012       2013  \n",
      "country  city                                                     \n",
      "Egypt    Alexandria   22.459583  21.181500  21.552583  21.438500  \n",
      "         Cairo        23.718250  21.986917  22.484250  22.907000  \n",
      "         Gizeh        23.718250  21.986917  22.484250  22.907000  \n",
      "Ethiopia Addis Abeba  18.298250  18.606750  18.448583  19.539000  \n",
      "France   Paris        10.409833  12.325750  11.219917  11.011625  \n",
      "Germany  Berlin        8.606833  10.556417   9.964333  10.121500  \n",
      "India    Ahmadabad    28.017833  27.290417  27.027250  27.608625  \n",
      "         Bangalore    25.705250  25.362083  26.042333  26.610500  \n",
      "         Bombay       27.765417  27.384917  27.192500  26.713000  \n",
      "         Calcutta     27.288833  26.406917  26.935083  27.369250  \n",
      "         Delhi        26.520250  25.629500  25.889417  26.709250  \n",
      "         Hyderabad    27.693417  27.408833  28.018583  28.851250  \n",
      "         Jaipur       26.818417  25.916500  25.884500  26.844125  \n",
      "         Kanpur       26.021750  25.062083  25.445417  26.121250  \n",
      "         Lakhnau      26.021750  25.062083  25.445417  26.121250  \n",
      "         Madras       29.047333  29.063167  29.778417  30.411750  \n",
      "         Nagpur       26.927000  26.004667  26.327917  27.112375  \n",
      "         New Delhi    26.520250  25.629500  25.889417  26.709250  \n",
      "         Pune         25.749167  25.161417  25.296833  25.847625  \n",
      "         Surat        27.681667  27.016583  26.889250  27.437750  \n",
      "year                       2000       2001       2002       2003       2004  \\\n",
      "country  city                                                                 \n",
      "Egypt    Cairo        21.486167  22.330833  22.414083  22.170500  22.081917   \n",
      "         Gizeh        21.486167  22.330833  22.414083  22.170500  22.081917   \n",
      "Ethiopia Addis Abeba  18.241250  18.296417  18.469750  18.320917  18.292750   \n",
      "France   Paris        11.739667  11.371250  11.871333  11.909500  11.338833   \n",
      "Germany  Berlin       10.963667   9.690250  10.264417  10.065750   9.822583   \n",
      "India    Ahmadabad    27.436000  27.198083  27.719083  27.403833  27.628333   \n",
      "         Bangalore    25.337917  25.528167  25.755333  25.924750  25.252083   \n",
      "         Bombay       27.203667  27.243667  27.628667  27.578417  27.318750   \n",
      "         Calcutta     26.491333  26.515167  26.703917  26.561333  26.634333   \n",
      "         Delhi        26.048333  25.862917  26.634333  25.721083  26.239917   \n",
      "\n",
      "year                       2005       2006       2007       2008       2009  \\\n",
      "country  city                                                                 \n",
      "Egypt    Cairo        22.006500  22.050000  22.361000  22.644500  22.625000   \n",
      "         Gizeh        22.006500  22.050000  22.361000  22.644500  22.625000   \n",
      "Ethiopia Addis Abeba  18.312833  18.427083  18.142583  18.165000  18.765333   \n",
      "France   Paris        11.552917  11.788500  11.750833  11.278250  11.464083   \n",
      "Germany  Berlin        9.919083  10.545333  10.883167  10.657750  10.062500   \n",
      "India    Ahmadabad    26.828083  27.282833  27.511167  27.048500  28.095833   \n",
      "         Bangalore    25.476500  25.418250  25.464333  25.352583  25.725750   \n",
      "         Bombay       27.035750  27.381500  27.634667  27.177750  27.844500   \n",
      "         Calcutta     26.729167  26.986250  26.584583  26.522333  27.153250   \n",
      "         Delhi        25.716083  26.365917  26.145667  25.675000  26.554250   \n",
      "\n",
      "year                       2010       2011       2012       2013  \n",
      "country  city                                                     \n",
      "Egypt    Cairo        23.718250  21.986917  22.484250  22.907000  \n",
      "         Gizeh        23.718250  21.986917  22.484250  22.907000  \n",
      "Ethiopia Addis Abeba  18.298250  18.606750  18.448583  19.539000  \n",
      "France   Paris        10.409833  12.325750  11.219917  11.011625  \n",
      "Germany  Berlin        8.606833  10.556417   9.964333  10.121500  \n",
      "India    Ahmadabad    28.017833  27.290417  27.027250  27.608625  \n",
      "         Bangalore    25.705250  25.362083  26.042333  26.610500  \n",
      "         Bombay       27.765417  27.384917  27.192500  26.713000  \n",
      "         Calcutta     27.288833  26.406917  26.935083  27.369250  \n",
      "         Delhi        26.520250  25.629500  25.889417  26.709250  \n",
      "year                       2005       2006       2007       2008       2009  \\\n",
      "country  city                                                                 \n",
      "Egypt    Cairo        22.006500  22.050000  22.361000  22.644500  22.625000   \n",
      "         Gizeh        22.006500  22.050000  22.361000  22.644500  22.625000   \n",
      "Ethiopia Addis Abeba  18.312833  18.427083  18.142583  18.165000  18.765333   \n",
      "France   Paris        11.552917  11.788500  11.750833  11.278250  11.464083   \n",
      "Germany  Berlin        9.919083  10.545333  10.883167  10.657750  10.062500   \n",
      "India    Ahmadabad    26.828083  27.282833  27.511167  27.048500  28.095833   \n",
      "         Bangalore    25.476500  25.418250  25.464333  25.352583  25.725750   \n",
      "         Bombay       27.035750  27.381500  27.634667  27.177750  27.844500   \n",
      "         Calcutta     26.729167  26.986250  26.584583  26.522333  27.153250   \n",
      "         Delhi        25.716083  26.365917  26.145667  25.675000  26.554250   \n",
      "\n",
      "year                       2010  \n",
      "country  city                    \n",
      "Egypt    Cairo        23.718250  \n",
      "         Gizeh        23.718250  \n",
      "Ethiopia Addis Abeba  18.298250  \n",
      "France   Paris        10.409833  \n",
      "Germany  Berlin        8.606833  \n",
      "India    Ahmadabad    28.017833  \n",
      "         Bangalore    25.705250  \n",
      "         Bombay       27.765417  \n",
      "         Calcutta     27.288833  \n",
      "         Delhi        26.520250  \n"
     ]
    }
   ],
   "source": [
    "# Subset for Egypt to India\n",
    "print(temp_by_country_city_vs_year.loc['Egypt' : 'India'])\n",
    "\n",
    "# Subset for Egypt, Cairo to India, Delhi\n",
    "print(temp_by_country_city_vs_year.loc[('Egypt', 'Cairo'):('India','Delhi')])\n",
    "\n",
    "# Subset in both directions at once\n",
    "print(temp_by_country_city_vs_year.loc[('Egypt', 'Cairo'):('India','Delhi'), 2005:2010])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2efe7006",
   "metadata": {},
   "source": [
    "## After a pivot table is ready you can apply different summary stats on it like min, max, mean.\n",
    "\n",
    "## mean(axis='columns'/'index')\n",
    "\n",
    "<p> When axis = columns, the summary stats would be for each column.\n",
    "    \n",
    "When asix = index, the summary stats would be for each row... This is the default behaviour </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "d5d1e149",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "year\n",
      "2000    19.506243\n",
      "2001    19.679352\n",
      "2002    19.855685\n",
      "2003    19.630197\n",
      "2004    19.672204\n",
      "2005    19.607239\n",
      "2006    19.793993\n",
      "2007    19.854270\n",
      "2008    19.608778\n",
      "2009    19.833752\n",
      "2010    19.911734\n",
      "2011    19.549197\n",
      "2012    19.668239\n",
      "2013    20.312285\n",
      "dtype: float64\n",
      "year\n",
      "2013    20.312285\n",
      "dtype: float64\n",
      "country  city  \n",
      "China    Harbin    4.876551\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Get the worldwide mean temp by year\n",
    "mean_temp_by_year = temp_by_country_city_vs_year.mean()\n",
    "\n",
    "print(mean_temp_by_year)\n",
    "\n",
    "# Filter for the year that had the highest mean temp\n",
    "print(mean_temp_by_year[mean_temp_by_year == mean_temp_by_year.max()])\n",
    "\n",
    "# Get the mean temp by city\n",
    "mean_temp_by_city = temp_by_country_city_vs_year.mean(axis='columns')\n",
    "\n",
    "# Filter for the city that had the lowest mean temp\n",
    "print(mean_temp_by_city[mean_temp_by_city == mean_temp_by_city.min()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "319cfb10",
   "metadata": {},
   "outputs": [],
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}