1_Data_operations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data operations in python using Pandas (& NumPy)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Following notebook lists major data operations typically required while doing data analysis and building machine learning models in Python.\n",
    "\n",
    "Python 3.7 has been used for this notebook. Environment package list is present in requirements.txt."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import packages\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#use to change how numbers are displayed in the notebook\n",
    "pd.options.display.float_format = '{:,.2f}'.format"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read csv and excel files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>read_csv()</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('./data/Input_file_CSV.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Majorly used Parameters</b>:\n",
    "- <b>sep</b>: to specify separator (delimiter); default ',' but it can be changed to match the input file. '|' is often used as a separator \n",
    "- <b>header</b>: which row of the input file should be treated as column headers\n",
    "\n",
    "Many other parameters are present that can be used in case the input file needs to be read in a custom way, e.g. skipping some rows the beginning of the file, parsing date columns, reading European data which has ',' as decimal point, etc.\n",
    "\n",
    "Please refer to [pandas documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html) for more details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>read_excel()</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('./data/Input_file_Excel.xlsx',sheet_name='Stores')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Majorly used Parameters</b>:\n",
    "- <b>sheet_name</b>: to specify Excel sheet name\n",
    "- <b>header</b>: which row of the input file should be treated as column headers\n",
    "- <b>engine</b>: which engine to use to read the excel file \n",
    "\n",
    "Many other parameters are present that can be used in case the input file needs to be read in a custom way, e.g. skipping some rows the beginning of the file, parsing date columns, reading columns that have thousands separator for numbers stored as text, etc.\n",
    "\n",
    "Please refer to [pandas documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html) for more details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Data sample</b>: N rows from the start or end of the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Classification</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude  Total Sales  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35   180,001.00   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35   150,002.00   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36   180,003.00   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36   180,004.00   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36    15,000.00   \n",
       "\n",
       "  Classification  \n",
       "0              B  \n",
       "1              B  \n",
       "2              B  \n",
       "3              B  \n",
       "4              C  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#First N rows of the data\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Classification</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area51</td>\n",
       "      <td>O01368</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.51</td>\n",
       "      <td>88.30</td>\n",
       "      <td>15,450.00</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area51</td>\n",
       "      <td>O01369</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.52</td>\n",
       "      <td>88.30</td>\n",
       "      <td>15,900.00</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area51</td>\n",
       "      <td>O01376</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.52</td>\n",
       "      <td>88.30</td>\n",
       "      <td>14,850.00</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area51</td>\n",
       "      <td>O01381</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.51</td>\n",
       "      <td>88.30</td>\n",
       "      <td>14,850.00</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area51</td>\n",
       "      <td>O01382</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.52</td>\n",
       "      <td>88.30</td>\n",
       "      <td>15,900.00</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Region    Area Outlet Code Outlet Type  Latitude  Longitude  Total Sales  \\\n",
       "54  City1  Area51      O01368    RETAILER     22.51      88.30    15,450.00   \n",
       "55  City1  Area51      O01369    RETAILER     22.52      88.30    15,900.00   \n",
       "56  City1  Area51      O01376    RETAILER     22.52      88.30    14,850.00   \n",
       "57  City1  Area51      O01381    RETAILER     22.51      88.30    14,850.00   \n",
       "58  City1  Area51      O01382    RETAILER     22.52      88.30    15,900.00   \n",
       "\n",
       "   Classification  \n",
       "54              C  \n",
       "55              C  \n",
       "56              C  \n",
       "57              C  \n",
       "58              C  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Last N rows of the data\n",
    "df.tail(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Information about the loaded dataset</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 59 entries, 0 to 58\n",
      "Data columns (total 8 columns):\n",
      "Region            59 non-null object\n",
      "Area              59 non-null object\n",
      "Outlet Code       59 non-null object\n",
      "Outlet Type       59 non-null object\n",
      "Latitude          58 non-null float64\n",
      "Longitude         58 non-null float64\n",
      "Total Sales       51 non-null float64\n",
      "Classification    59 non-null object\n",
      "dtypes: float64(3), object(5)\n",
      "memory usage: 3.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>List of columns</b>: This comes in handy for other operations where column names are required. Required set of column names can be taken from this list using list operations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Region',\n",
       " 'Area',\n",
       " 'Outlet Code',\n",
       " 'Outlet Type',\n",
       " 'Latitude',\n",
       " 'Longitude',\n",
       " 'Total Sales',\n",
       " 'Classification']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col_list = list(df.columns)\n",
    "col_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Data summary</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Latitude</th>\n",
       "      <td>58.00</td>\n",
       "      <td>22.55</td>\n",
       "      <td>0.05</td>\n",
       "      <td>22.50</td>\n",
       "      <td>22.52</td>\n",
       "      <td>22.53</td>\n",
       "      <td>22.57</td>\n",
       "      <td>22.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Longitude</th>\n",
       "      <td>58.00</td>\n",
       "      <td>88.34</td>\n",
       "      <td>0.03</td>\n",
       "      <td>88.27</td>\n",
       "      <td>88.31</td>\n",
       "      <td>88.34</td>\n",
       "      <td>88.36</td>\n",
       "      <td>88.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Sales</th>\n",
       "      <td>51.00</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>144,451.94</td>\n",
       "      <td>14,850.00</td>\n",
       "      <td>62,700.00</td>\n",
       "      <td>180,000.00</td>\n",
       "      <td>263,450.00</td>\n",
       "      <td>600,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             count       mean        std       min       25%        50%  \\\n",
       "Latitude     58.00      22.55       0.05     22.50     22.52      22.53   \n",
       "Longitude    58.00      88.34       0.03     88.27     88.31      88.34   \n",
       "Total Sales  51.00 185,657.43 144,451.94 14,850.00 62,700.00 180,000.00   \n",
       "\n",
       "                   75%        max  \n",
       "Latitude         22.57      22.66  \n",
       "Longitude        88.36      88.38  \n",
       "Total Sales 263,450.00 600,000.00  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#numeric cols\n",
    "df.describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>count</th>\n",
       "      <th>unique</th>\n",
       "      <th>top</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <td>59</td>\n",
       "      <td>1</td>\n",
       "      <td>City1</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Area</th>\n",
       "      <td>59</td>\n",
       "      <td>6</td>\n",
       "      <td>Area51</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Outlet Code</th>\n",
       "      <td>59</td>\n",
       "      <td>59</td>\n",
       "      <td>O06525</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Outlet Type</th>\n",
       "      <td>59</td>\n",
       "      <td>1</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Classification</th>\n",
       "      <td>59</td>\n",
       "      <td>3</td>\n",
       "      <td>C</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               count unique       top freq\n",
       "Region            59      1     City1   59\n",
       "Area              59      6    Area51   12\n",
       "Outlet Code       59     59    O06525    1\n",
       "Outlet Type       59      1  RETAILER   59\n",
       "Classification    59      3         C   24"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#string cols\n",
    "df.describe(include='O').T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Data summary with grouped data</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Classification</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">Latitude</th>\n",
       "      <th>count</th>\n",
       "      <td>13.00</td>\n",
       "      <td>21.00</td>\n",
       "      <td>24.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>22.54</td>\n",
       "      <td>22.56</td>\n",
       "      <td>22.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.03</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>22.50</td>\n",
       "      <td>22.50</td>\n",
       "      <td>22.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>22.53</td>\n",
       "      <td>22.52</td>\n",
       "      <td>22.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>22.53</td>\n",
       "      <td>22.56</td>\n",
       "      <td>22.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>22.54</td>\n",
       "      <td>22.57</td>\n",
       "      <td>22.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>22.58</td>\n",
       "      <td>22.66</td>\n",
       "      <td>22.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">Longitude</th>\n",
       "      <th>count</th>\n",
       "      <td>13.00</td>\n",
       "      <td>21.00</td>\n",
       "      <td>24.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>88.34</td>\n",
       "      <td>88.33</td>\n",
       "      <td>88.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.02</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>88.31</td>\n",
       "      <td>88.27</td>\n",
       "      <td>88.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>88.32</td>\n",
       "      <td>88.31</td>\n",
       "      <td>88.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>88.34</td>\n",
       "      <td>88.35</td>\n",
       "      <td>88.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>88.34</td>\n",
       "      <td>88.35</td>\n",
       "      <td>88.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>88.37</td>\n",
       "      <td>88.38</td>\n",
       "      <td>88.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">Total Sales</th>\n",
       "      <th>count</th>\n",
       "      <td>12.00</td>\n",
       "      <td>19.00</td>\n",
       "      <td>20.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>367,839.08</td>\n",
       "      <td>188,953.16</td>\n",
       "      <td>73,217.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>127,022.45</td>\n",
       "      <td>76,727.86</td>\n",
       "      <td>75,576.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>180,000.00</td>\n",
       "      <td>60,600.00</td>\n",
       "      <td>14,850.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>262,405.75</td>\n",
       "      <td>138,500.00</td>\n",
       "      <td>15,900.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>414,061.50</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>30,450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>438,750.00</td>\n",
       "      <td>233,750.00</td>\n",
       "      <td>116,250.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>600,000.00</td>\n",
       "      <td>330,000.00</td>\n",
       "      <td>254,400.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Classification             A          B          C\n",
       "Latitude    count      13.00      21.00      24.00\n",
       "            mean       22.54      22.56      22.56\n",
       "            std         0.03       0.05       0.06\n",
       "            min        22.50      22.50      22.50\n",
       "            25%        22.53      22.52      22.52\n",
       "            50%        22.53      22.56      22.53\n",
       "            75%        22.54      22.57      22.58\n",
       "            max        22.58      22.66      22.66\n",
       "Longitude   count      13.00      21.00      24.00\n",
       "            mean       88.34      88.33      88.34\n",
       "            std         0.02       0.04       0.03\n",
       "            min        88.31      88.27      88.30\n",
       "            25%        88.32      88.31      88.32\n",
       "            50%        88.34      88.35      88.34\n",
       "            75%        88.34      88.35      88.36\n",
       "            max        88.37      88.38      88.38\n",
       "Total Sales count      12.00      19.00      20.00\n",
       "            mean  367,839.08 188,953.16  73,217.50\n",
       "            std   127,022.45  76,727.86  75,576.55\n",
       "            min   180,000.00  60,600.00  14,850.00\n",
       "            25%   262,405.75 138,500.00  15,900.00\n",
       "            50%   414,061.50 180,003.00  30,450.00\n",
       "            75%   438,750.00 233,750.00 116,250.00\n",
       "            max   600,000.00 330,000.00 254,400.00"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Classification').describe().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data checks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Record and column count</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(59, 8)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#(n,m): (roww, columns)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Checking level of dataset</b>: Put the columns to check inside groupby([]). If the output row count matches the row count of original df, then the set of columns tested are the level of the df."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(59,)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['Outlet Code']).size().shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Missing data</b>\n",
    "\n",
    "\n",
    "<b>Count of null values per column</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Region            0\n",
       "Area              0\n",
       "Outlet Code       0\n",
       "Outlet Type       0\n",
       "Latitude          1\n",
       "Longitude         1\n",
       "Total Sales       8\n",
       "Classification    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Fill missing values</b>\n",
    "\n",
    "- Fill value can be a constant or can be mean/median/mode of the column\n",
    "- aggregation and joining can also be used to fill missing values with the mean/median per category using some other column (e.g. Classification)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#fill with mean\n",
    "df['Total Sales New'] = df['Total Sales'].fillna(value=df['Total Sales'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>sales_impute</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Classification</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>367,839.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>188,953.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>73,217.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                sales_impute\n",
       "Classification              \n",
       "A                 367,839.08\n",
       "B                 188,953.16\n",
       "C                  73,217.50"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#fill with mean per group in 'Classification' column: compute mean\n",
    "df_classification_impute = df.groupby(['Classification']).agg({'Total Sales':'mean'})\n",
    "df_classification_impute = df_classification_impute.rename(columns = {'Total Sales':'sales_impute'})\n",
    "df_classification_impute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#fill with mean per group in 'Classification' column: join and fill\n",
    "df = df.merge(df_classification_impute,on='Classification',how='left')\n",
    "\n",
    "df['Total Sales New 2'] = df.apply(lambda r: r['sales_impute'] if pd.isnull(r['Total Sales'])\n",
    "                                   else r['Total Sales'],\n",
    "                                   axis=1\n",
    "                                  ) #more details below in .apply() section\n",
    "\n",
    "df = df.drop(['sales_impute'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Region               0\n",
       "Area                 0\n",
       "Outlet Code          0\n",
       "Outlet Type          0\n",
       "Latitude             1\n",
       "Longitude            1\n",
       "Total Sales          8\n",
       "Classification       0\n",
       "Total Sales New      0\n",
       "Total Sales New 2    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Total Sales</th>\n",
       "      <td>51.00</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>144,451.94</td>\n",
       "      <td>14,850.00</td>\n",
       "      <td>62,700.00</td>\n",
       "      <td>180,000.00</td>\n",
       "      <td>263,450.00</td>\n",
       "      <td>600,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Sales New</th>\n",
       "      <td>59.00</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>134,120.26</td>\n",
       "      <td>14,850.00</td>\n",
       "      <td>80,100.00</td>\n",
       "      <td>182,000.00</td>\n",
       "      <td>233,750.00</td>\n",
       "      <td>600,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total Sales New 2</th>\n",
       "      <td>59.00</td>\n",
       "      <td>181,289.79</td>\n",
       "      <td>139,332.54</td>\n",
       "      <td>14,850.00</td>\n",
       "      <td>72,608.75</td>\n",
       "      <td>180,000.00</td>\n",
       "      <td>244,700.00</td>\n",
       "      <td>600,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   count       mean        std       min       25%        50%  \\\n",
       "Total Sales        51.00 185,657.43 144,451.94 14,850.00 62,700.00 180,000.00   \n",
       "Total Sales New    59.00 185,657.43 134,120.26 14,850.00 80,100.00 182,000.00   \n",
       "Total Sales New 2  59.00 181,289.79 139,332.54 14,850.00 72,608.75 180,000.00   \n",
       "\n",
       "                         75%        max  \n",
       "Total Sales       263,450.00 600,000.00  \n",
       "Total Sales New   233,750.00 600,000.00  \n",
       "Total Sales New 2 244,700.00 600,000.00  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['Total Sales','Total Sales New','Total Sales New 2']].describe().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As can be seen below, same value has been imputed for all records in 'Total Sales New' whereas different values have been imputed basis 'Classification' column in 'Total Sales New 2'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales New</th>\n",
       "      <th>Total Sales New 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18774</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>nan</td>\n",
       "      <td>B</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>188,953.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O19893</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>B</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>188,953.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18728</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>nan</td>\n",
       "      <td>C</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>73,217.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18760</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>nan</td>\n",
       "      <td>C</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>73,217.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18761</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>nan</td>\n",
       "      <td>C</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>73,217.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18781</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>nan</td>\n",
       "      <td>C</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>73,217.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area4</td>\n",
       "      <td>O06525</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.53</td>\n",
       "      <td>88.34</td>\n",
       "      <td>nan</td>\n",
       "      <td>A</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>367,839.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area4</td>\n",
       "      <td>O06364</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.53</td>\n",
       "      <td>88.34</td>\n",
       "      <td>nan</td>\n",
       "      <td>B</td>\n",
       "      <td>185,657.43</td>\n",
       "      <td>188,953.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Region   Area Outlet Code Outlet Type  Latitude  Longitude  Total Sales  \\\n",
       "18  City1  Area3      O18774    RETAILER     22.66      88.38          nan   \n",
       "20  City1  Area3      O19893    RETAILER       nan        nan          nan   \n",
       "21  City1  Area3      O18728    RETAILER     22.66      88.38          nan   \n",
       "22  City1  Area3      O18760    RETAILER     22.66      88.38          nan   \n",
       "23  City1  Area3      O18761    RETAILER     22.66      88.38          nan   \n",
       "24  City1  Area3      O18781    RETAILER     22.66      88.38          nan   \n",
       "28  City1  Area4      O06525    RETAILER     22.53      88.34          nan   \n",
       "29  City1  Area4      O06364    RETAILER     22.53      88.34          nan   \n",
       "\n",
       "   Classification  Total Sales New  Total Sales New 2  \n",
       "18              B       185,657.43         188,953.16  \n",
       "20              B       185,657.43         188,953.16  \n",
       "21              C       185,657.43          73,217.50  \n",
       "22              C       185,657.43          73,217.50  \n",
       "23              C       185,657.43          73,217.50  \n",
       "24              C       185,657.43          73,217.50  \n",
       "28              A       185,657.43         367,839.08  \n",
       "29              B       185,657.43         188,953.16  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Total Sales'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#keep the treated sales column\n",
    "df = df.drop(['Total Sales','Total Sales New'],axis=1)\n",
    "df = df.rename(columns = {'Total Sales New 2':'Total Sales'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Drop missing values</b>\n",
    "\n",
    "(Lat./Long. columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(59, 8)\n",
      "(58, 8)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape)\n",
    "print(df.dropna().shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Region            0\n",
       "Area              0\n",
       "Outlet Code       0\n",
       "Outlet Type       0\n",
       "Latitude          0\n",
       "Longitude         0\n",
       "Classification    0\n",
       "Total Sales       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.dropna()\n",
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selection and Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     O09372\n",
       "1     O09517\n",
       "2     O10190\n",
       "3     O10214\n",
       "4     O10100\n",
       "5     O10313\n",
       "6     O10316\n",
       "7     O01559\n",
       "8     O01565\n",
       "9     O01568\n",
       "10    O01594\n",
       "11    O01595\n",
       "12    O01598\n",
       "13    O02232\n",
       "14    O02236\n",
       "15    O02239\n",
       "16    O02251\n",
       "17    O18739\n",
       "18    O18774\n",
       "19    O18805\n",
       "21    O18728\n",
       "22    O18760\n",
       "23    O18761\n",
       "24    O18781\n",
       "25    O06399\n",
       "26    O06416\n",
       "27    O06507\n",
       "28    O06525\n",
       "29    O06364\n",
       "30    O06384\n",
       "31    O06406\n",
       "32    O06092\n",
       "33    O06093\n",
       "34    O06100\n",
       "35    O06101\n",
       "36    O12732\n",
       "37    O12754\n",
       "38    O12755\n",
       "39    O08115\n",
       "40    O08177\n",
       "41    O08178\n",
       "42    O08222\n",
       "43    O08468\n",
       "44    O08481\n",
       "45    O08499\n",
       "46    O08502\n",
       "47    O03471\n",
       "48    O03472\n",
       "49    O03522\n",
       "50    O00493\n",
       "51    O00494\n",
       "52    O00498\n",
       "53    O00499\n",
       "54    O01368\n",
       "55    O01369\n",
       "56    O01376\n",
       "57    O01381\n",
       "58    O01382\n",
       "Name: Outlet Code, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Outlet Code']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Region</th>\n",
       "      <th>Outlet Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>O09372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>O09517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>O10190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>O10214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>O10100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region Outlet Code\n",
       "0  City1      O09372\n",
       "1  City1      O09517\n",
       "2  City1      O10190\n",
       "3  City1      O10214\n",
       "4  City1      O10100"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#list of columns\n",
    "df[['Region','Outlet Code']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>loc and iloc</b>\n",
    "\n",
    "- .loc[] is primarily label based; takes [rows,columns]\n",
    "- .iloc is primarily integer position based (from 0 to length-1 of the axis); takes [rows,columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Region</th>\n",
       "      <th>Outlet Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>O09372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>O09517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>O10190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>O10214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>O10100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>City1</td>\n",
       "      <td>O10313</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region Outlet Code\n",
       "0  City1      O09372\n",
       "1  City1      O09517\n",
       "2  City1      O10190\n",
       "3  City1      O10214\n",
       "4  City1      O10100\n",
       "5  City1      O10313"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:5,['Region','Outlet Code']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'O09517'"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[1,'Outlet Code']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10313</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>30,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "5  City1  Area1      O10313    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  \n",
       "0   180,001.00  \n",
       "1   150,002.00  \n",
       "2   180,003.00  \n",
       "3   180,004.00  \n",
       "4    15,000.00  \n",
       "5    30,000.00  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[0:6,0:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'O09517'"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[1,2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Add new column</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Total Sales new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>180.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>150.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>180.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>180.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>15.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  Total Sales new  \n",
       "0   180,001.00           180.00  \n",
       "1   150,002.00           150.00  \n",
       "2   180,003.00           180.00  \n",
       "3   180,004.00           180.00  \n",
       "4    15,000.00            15.00  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Total Sales new'] = df['Total Sales']/1000\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Rename columns</b>\n",
    "\n",
    "Pass dictionary of {'Current name':'New name',...}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Total Sales (k)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>180.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>150.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>180.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>180.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>15.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  Total Sales (k)  \n",
       "0   180,001.00           180.00  \n",
       "1   150,002.00           150.00  \n",
       "2   180,003.00           180.00  \n",
       "3   180,004.00           180.00  \n",
       "4    15,000.00            15.00  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename(columns={'Total Sales new':'Total Sales (k)'},inplace = True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Delete columns</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  \n",
       "0   180,001.00  \n",
       "1   150,002.00  \n",
       "2   180,003.00  \n",
       "3   180,004.00  \n",
       "4    15,000.00  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop('Total Sales (k)',axis=1,inplace=True) #pass a list for multiple columns\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conditional Selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01559</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>445,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01565</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>405,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01568</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>432,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area4</td>\n",
       "      <td>O06399</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.53</td>\n",
       "      <td>88.34</td>\n",
       "      <td>A</td>\n",
       "      <td>423,123.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area5</td>\n",
       "      <td>O12732</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.37</td>\n",
       "      <td>A</td>\n",
       "      <td>600,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area5</td>\n",
       "      <td>O12754</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.37</td>\n",
       "      <td>A</td>\n",
       "      <td>436,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area5</td>\n",
       "      <td>O12755</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.37</td>\n",
       "      <td>A</td>\n",
       "      <td>468,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "7   City1  Area2      O01559    RETAILER     22.50      88.31              A   \n",
       "8   City1  Area2      O01565    RETAILER     22.50      88.31              A   \n",
       "9   City1  Area2      O01568    RETAILER     22.50      88.31              A   \n",
       "25  City1  Area4      O06399    RETAILER     22.53      88.34              A   \n",
       "36  City1  Area5      O12732    RETAILER     22.58      88.37              A   \n",
       "37  City1  Area5      O12754    RETAILER     22.57      88.37              A   \n",
       "38  City1  Area5      O12755    RETAILER     22.57      88.37              A   \n",
       "\n",
       "    Total Sales  \n",
       "7    445,500.00  \n",
       "8    405,000.00  \n",
       "9    432,000.00  \n",
       "25   423,123.00  \n",
       "36   600,000.00  \n",
       "37   436,500.00  \n",
       "38   468,000.00  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Total Sales']>400000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area4</td>\n",
       "      <td>O06416</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.53</td>\n",
       "      <td>88.34</td>\n",
       "      <td>A</td>\n",
       "      <td>323,123.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area4</td>\n",
       "      <td>O06525</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.53</td>\n",
       "      <td>88.34</td>\n",
       "      <td>A</td>\n",
       "      <td>367,839.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area5</td>\n",
       "      <td>O08115</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.56</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>324,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area5</td>\n",
       "      <td>O08222</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>330,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "26  City1  Area4      O06416    RETAILER     22.53      88.34              A   \n",
       "28  City1  Area4      O06525    RETAILER     22.53      88.34              A   \n",
       "39  City1  Area5      O08115    RETAILER     22.56      88.35              B   \n",
       "42  City1  Area5      O08222    RETAILER     22.57      88.35              B   \n",
       "\n",
       "    Total Sales  \n",
       "26   323,123.00  \n",
       "28   367,839.08  \n",
       "39   324,000.00  \n",
       "42   330,000.00  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df['Total Sales']>300000) & (df['Total Sales']<=400000)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Index\n",
    "\n",
    "Use inplace=True to make the change to the actual df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  \n",
       "0   180,001.00  \n",
       "1   150,002.00  \n",
       "2   180,003.00  \n",
       "3   180,004.00  \n",
       "4    15,000.00  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>index</th>\n",
       "      <th>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index Region   Area Outlet Code Outlet Type  Latitude  Longitude  \\\n",
       "0      0  City1  Area1      O09372    RETAILER     22.57      88.35   \n",
       "1      1  City1  Area1      O09517    RETAILER     22.57      88.35   \n",
       "2      2  City1  Area1      O10190    RETAILER     22.58      88.36   \n",
       "3      3  City1  Area1      O10214    RETAILER     22.58      88.36   \n",
       "4      4  City1  Area1      O10100    RETAILER     22.58      88.36   \n",
       "\n",
       "  Classification  Total Sales  \n",
       "0              B   180,001.00  \n",
       "1              B   150,002.00  \n",
       "2              B   180,003.00  \n",
       "3              B   180,004.00  \n",
       "4              C    15,000.00  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.reset_index().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Outlet Code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>O09372</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>O09517</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>O10190</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>O10214</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>O10100</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Region   Area Outlet Type  Latitude  Longitude Classification  \\\n",
       "Outlet Code                                                                 \n",
       "O09372       City1  Area1    RETAILER     22.57      88.35              B   \n",
       "O09517       City1  Area1    RETAILER     22.57      88.35              B   \n",
       "O10190       City1  Area1    RETAILER     22.58      88.36              B   \n",
       "O10214       City1  Area1    RETAILER     22.58      88.36              B   \n",
       "O10100       City1  Area1    RETAILER     22.58      88.36              C   \n",
       "\n",
       "             Total Sales  \n",
       "Outlet Code               \n",
       "O09372        180,001.00  \n",
       "O09517        150,002.00  \n",
       "O10190        180,003.00  \n",
       "O10214        180,004.00  \n",
       "O10100         15,000.00  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index('Outlet Code').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  \n",
       "0   180,001.00  \n",
       "1   150,002.00  \n",
       "2   180,003.00  \n",
       "3   180,004.00  \n",
       "4    15,000.00  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#inplace=True is not used above hence original df doesn't change\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## .apply()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Define a function and apply</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "def log_sales(sales):\n",
    "    return np.log(sales)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Log Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>11.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>9.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  Log Sales  \n",
       "0   180,001.00      12.10  \n",
       "1   150,002.00      11.92  \n",
       "2   180,003.00      12.10  \n",
       "3   180,004.00      12.10  \n",
       "4    15,000.00       9.62  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Log Sales'] = df['Total Sales'].apply(log_sales)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Use lambda</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Log Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>11.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>12.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>9.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  Log Sales  \n",
       "0   180,001.00      12.10  \n",
       "1   150,002.00      11.92  \n",
       "2   180,003.00      12.10  \n",
       "3   180,004.00      12.10  \n",
       "4    15,000.00       9.62  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Log Sales'] = df['Total Sales'].apply(lambda x: np.log(x)) #x refers to the values in Series (column) 'Total Sales'\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Functions involving multiple columns</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sales_adj(r): #r refers to a row\n",
    "    sales=r['Total Sales']\n",
    "    classification=r['Classification']\n",
    "    if classification in ['A','WS']:\n",
    "        if sales<=225000:\n",
    "            group = 'Low'\n",
    "        elif sales<=400000:\n",
    "            group = 'Medium'\n",
    "        else:\n",
    "            group = 'High'\n",
    "    elif classification in ['B']:\n",
    "        if sales<=100000:\n",
    "            group = 'Low'\n",
    "        elif sales<=150000:\n",
    "            group = 'Medium'\n",
    "        else:\n",
    "            group = 'High'\n",
    "    elif classification in ['C']:\n",
    "        if sales<=50000:\n",
    "            group = 'Low'\n",
    "        elif sales<=75000:\n",
    "            group = 'Medium'\n",
    "        else:\n",
    "            group = 'High'\n",
    "    #\n",
    "    return group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Log Sales</th>\n",
       "      <th>Sales Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>12.10</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>11.92</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>12.10</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>12.10</td>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>9.62</td>\n",
       "      <td>Low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  Log Sales Sales Group  \n",
       "0   180,001.00      12.10        High  \n",
       "1   150,002.00      11.92        High  \n",
       "2   180,003.00      12.10        High  \n",
       "3   180,004.00      12.10        High  \n",
       "4    15,000.00       9.62         Low  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Sales Group'] = df.apply(sales_adj,axis=1)\n",
    "#df.groupby(['Classification','Sales Group']).agg({'Total Sales':'mean'})\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Same function can be applied using lambda by writing the whole function inside (lambda r: ...) but it easier to define a function if it has complicated/nested operations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Total Sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Area</th>\n",
       "      <th>Classification</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Area1</th>\n",
       "      <th>B</th>\n",
       "      <td>690,010.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>80,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Area2</th>\n",
       "      <th>A</th>\n",
       "      <td>1,282,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>308,100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>327,000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Total Sales\n",
       "Area  Classification             \n",
       "Area1 B                690,010.00\n",
       "      C                 80,000.00\n",
       "Area2 A              1,282,500.00\n",
       "      B                308,100.00\n",
       "      C                327,000.00"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_agg = df.groupby(['Area','Classification']).agg({'Total Sales':'sum'})\n",
    "df_agg.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Aggregation level columns (list inside groupby) become indexes of the output df. Add .reset_index(), if required\n",
    "- Aggregations to be performed can be passed as a dictionary {'column':'function',...} inside .agg()\n",
    "- Only one aggregation per column is output. If multiple aggregations are mapped to a column, then the one occurring later in the dictionary is output\n",
    "- In case multiple aggregations are to be applied on a column, create copies of that column in the input dataset and map each aggregation to a separate copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>Area</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Area1</td>\n",
       "      <td>B</td>\n",
       "      <td>172,502.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Area1</td>\n",
       "      <td>C</td>\n",
       "      <td>26,666.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Area2</td>\n",
       "      <td>A</td>\n",
       "      <td>427,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Area2</td>\n",
       "      <td>B</td>\n",
       "      <td>102,700.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Area2</td>\n",
       "      <td>C</td>\n",
       "      <td>81,750.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Area Classification  Total Sales\n",
       "0  Area1              B   172,502.50\n",
       "1  Area1              C    26,666.67\n",
       "2  Area2              A   427,500.00\n",
       "3  Area2              B   102,700.00\n",
       "4  Area2              C    81,750.00"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#outputs mean() of Total Sales only\n",
    "agg_dict = {'Total Sales':'sum','Total Sales':'mean'}\n",
    "df_agg = df.groupby(['Area','Classification']).agg(agg_dict).reset_index()\n",
    "df_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Area</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Mean Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Area1</td>\n",
       "      <td>B</td>\n",
       "      <td>690,010.00</td>\n",
       "      <td>172,502.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Area1</td>\n",
       "      <td>C</td>\n",
       "      <td>80,000.00</td>\n",
       "      <td>26,666.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Area2</td>\n",
       "      <td>A</td>\n",
       "      <td>1,282,500.00</td>\n",
       "      <td>427,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Area2</td>\n",
       "      <td>B</td>\n",
       "      <td>308,100.00</td>\n",
       "      <td>102,700.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Area2</td>\n",
       "      <td>C</td>\n",
       "      <td>327,000.00</td>\n",
       "      <td>81,750.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Area Classification  Total Sales  Mean Sales\n",
       "0  Area1              B   690,010.00  172,502.50\n",
       "1  Area1              C    80,000.00   26,666.67\n",
       "2  Area2              A 1,282,500.00  427,500.00\n",
       "3  Area2              B   308,100.00  102,700.00\n",
       "4  Area2              C   327,000.00   81,750.00"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Mean Sales'] = df['Total Sales']\n",
    "agg_dict = {'Total Sales':'sum','Mean Sales':'mean'}\n",
    "df_agg = df.groupby(['Area','Classification']).agg(agg_dict).reset_index()\n",
    "df_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(['Log Sales','Sales Group','Mean Sales'],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_more_outlets = pd.read_excel('./data/More_outlets.xlsx',sheet_name='Stores')\n",
    "df_Outlet_act_ind = pd.read_excel('./data/Outlet_active_ind.xlsx',sheet_name='Stores')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Union / Concatenation</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>pd.concat</b>: Below code concatenates two dfs one below the other: it uses column names to match corresponding columns to concatenate. Recommended to match column order & count before this step.\n",
    "\n",
    "Concatenation can also done side-by-side, i.e. adding more columns to a df using another df."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(58, 8) (49, 8)\n",
      "(107, 8)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape,df_more_outlets.shape)\n",
    "df_concat = pd.concat([df[df_more_outlets.columns] #column selection and sequence matching\n",
    "                       , df_more_outlets\n",
    "                      ]\n",
    "                      , ignore_index=True)\n",
    "print(df_concat.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Classification</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area6</td>\n",
       "      <td>O20209</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.53</td>\n",
       "      <td>88.27</td>\n",
       "      <td>32,700.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area6</td>\n",
       "      <td>O20210</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.52</td>\n",
       "      <td>88.27</td>\n",
       "      <td>74,200.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area6</td>\n",
       "      <td>O20211</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.52</td>\n",
       "      <td>88.27</td>\n",
       "      <td>31,800.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area6</td>\n",
       "      <td>O20212</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.52</td>\n",
       "      <td>88.27</td>\n",
       "      <td>27,300.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area6</td>\n",
       "      <td>O20213</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.53</td>\n",
       "      <td>88.27</td>\n",
       "      <td>32,700.00</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Region   Area Outlet Code Outlet Type  Latitude  Longitude  Total Sales  \\\n",
       "102  City1  Area6      O20209    RETAILER     22.53      88.27    32,700.00   \n",
       "103  City1  Area6      O20210    RETAILER     22.52      88.27    74,200.00   \n",
       "104  City1  Area6      O20211    RETAILER     22.52      88.27    31,800.00   \n",
       "105  City1  Area6      O20212    RETAILER     22.52      88.27    27,300.00   \n",
       "106  City1  Area6      O20213    RETAILER     22.53      88.27    32,700.00   \n",
       "\n",
       "    Classification  \n",
       "102              B  \n",
       "103              B  \n",
       "104              B  \n",
       "105              B  \n",
       "106              B  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_concat.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>pd.append</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(58, 8) (49, 8)\n",
      "(107, 8)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape,df_more_outlets.shape)\n",
    "df_concat = df[df_more_outlets.columns].append(df_more_outlets,ignore_index=True)\n",
    "print(df_concat.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Please note:</b> In case you are appending multiple dfs in a loop, its better to save the output per loop in a list and then use pd.concat(loop_df_list,ignore_index=True) post the loop than to use pd.concat at the end of every loop. This method is more optimized."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Merge (or join)</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(58, 8) (20164, 2)\n",
      "(58, 9)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape,df_Outlet_act_ind.shape)\n",
    "df_merged = df.merge(df_Outlet_act_ind,on=['Outlet Code'],how='left')\n",
    "print(df_merged.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Active Ind</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "0  City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1  City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "2  City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "3  City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "4  City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "\n",
       "   Total Sales  Active Ind  \n",
       "0   180,001.00           0  \n",
       "1   150,002.00           1  \n",
       "2   180,003.00           1  \n",
       "3   180,004.00           0  \n",
       "4    15,000.00           0  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Join on multiple columns</b> by adding them to the 'on' list. More complex joins can also be performed- more details [here](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html)\n",
    "\n",
    "<b>df.join(df_right)</b> can also be used for similar operations, but for the right df (df_right), the join key(s) must be its index."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Complex data operations\n",
    "Cross-joins, Window functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Cross-join</b>: Creating a df with m x n records where the input dfs have m and n rows each. Here, each row from df1 is mapped to each row in df2.\n",
    "\n",
    "To do this, add a constant column to both tables and use that as the joining key. Drop the key post join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(58, 2) (58, 2)\n",
      "(3364, 2)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Outlet_Code_1</th>\n",
       "      <th>Outlet_Code_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O09372</td>\n",
       "      <td>O09372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>O09372</td>\n",
       "      <td>O09517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>O09372</td>\n",
       "      <td>O10190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>O09372</td>\n",
       "      <td>O10214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>O09372</td>\n",
       "      <td>O10100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Outlet_Code_1 Outlet_Code_2\n",
       "0        O09372        O09372\n",
       "1        O09372        O09517\n",
       "2        O09372        O10190\n",
       "3        O09372        O10214\n",
       "4        O09372        O10100"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#keeping only one column to showcase the join\n",
    "df1 = df[['Outlet Code']].rename(columns={'Outlet Code':'Outlet_Code_1'})\n",
    "df1['key'] = 1\n",
    "\n",
    "df2 = df[['Outlet Code']].rename(columns={'Outlet Code':'Outlet_Code_2'})\n",
    "df2['key'] = 1\n",
    "\n",
    "#cross join and drop key\n",
    "df_cross = df1.merge(df2,on='key').drop(['key'],axis=1)\n",
    "print(df1.shape,df2.shape)\n",
    "print(df_cross.shape)\n",
    "df_cross.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Window Functions</b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Row Number(): Rank(method=’first’)</b>\n",
    "\n",
    "Add all 'partition by' columns to 'groupby'\n",
    "\n",
    "For different types of ranks, use different 'method':\n",
    "- average: average rank of the group\n",
    "- min: lowest rank in the group\n",
    "- max: highest rank in the group\n",
    "- first: ranks assigned in order they appear in the array\n",
    "- dense: like ‘min’, but rank always increases by 1 between groups."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here Row Num column goes from 1 to N per Area, with 1 assigned to Outlet with highest sales within that Area."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Row Num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10316</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>35,000.00</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10313</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>30,000.00</td>\n",
       "      <td>6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01559</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>445,500.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01568</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>432,000.00</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01565</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>405,000.00</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01594</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>B</td>\n",
       "      <td>127,500.00</td>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01595</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>B</td>\n",
       "      <td>120,000.00</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02232</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.32</td>\n",
       "      <td>C</td>\n",
       "      <td>116,250.00</td>\n",
       "      <td>6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02251</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.32</td>\n",
       "      <td>C</td>\n",
       "      <td>116,250.00</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02236</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.32</td>\n",
       "      <td>C</td>\n",
       "      <td>64,800.00</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01598</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>B</td>\n",
       "      <td>60,600.00</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02239</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.32</td>\n",
       "      <td>C</td>\n",
       "      <td>29,700.00</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18805</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>B</td>\n",
       "      <td>235,000.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18739</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>B</td>\n",
       "      <td>232,500.00</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18774</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>B</td>\n",
       "      <td>188,953.16</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "3   City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "2   City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "0   City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1   City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "6   City1  Area1      O10316    RETAILER     22.58      88.36              C   \n",
       "5   City1  Area1      O10313    RETAILER     22.58      88.36              C   \n",
       "4   City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "7   City1  Area2      O01559    RETAILER     22.50      88.31              A   \n",
       "9   City1  Area2      O01568    RETAILER     22.50      88.31              A   \n",
       "8   City1  Area2      O01565    RETAILER     22.50      88.31              A   \n",
       "10  City1  Area2      O01594    RETAILER     22.50      88.31              B   \n",
       "11  City1  Area2      O01595    RETAILER     22.50      88.31              B   \n",
       "13  City1  Area2      O02232    RETAILER     22.50      88.32              C   \n",
       "16  City1  Area2      O02251    RETAILER     22.50      88.32              C   \n",
       "14  City1  Area2      O02236    RETAILER     22.50      88.32              C   \n",
       "12  City1  Area2      O01598    RETAILER     22.50      88.31              B   \n",
       "15  City1  Area2      O02239    RETAILER     22.50      88.32              C   \n",
       "19  City1  Area3      O18805    RETAILER     22.66      88.38              B   \n",
       "17  City1  Area3      O18739    RETAILER     22.66      88.38              B   \n",
       "18  City1  Area3      O18774    RETAILER     22.66      88.38              B   \n",
       "\n",
       "    Total Sales  Row Num  \n",
       "3    180,004.00     1.00  \n",
       "2    180,003.00     2.00  \n",
       "0    180,001.00     3.00  \n",
       "1    150,002.00     4.00  \n",
       "6     35,000.00     5.00  \n",
       "5     30,000.00     6.00  \n",
       "4     15,000.00     7.00  \n",
       "7    445,500.00     1.00  \n",
       "9    432,000.00     2.00  \n",
       "8    405,000.00     3.00  \n",
       "10   127,500.00     4.00  \n",
       "11   120,000.00     5.00  \n",
       "13   116,250.00     6.00  \n",
       "16   116,250.00     7.00  \n",
       "14    64,800.00     8.00  \n",
       "12    60,600.00     9.00  \n",
       "15    29,700.00    10.00  \n",
       "19   235,000.00     1.00  \n",
       "17   232,500.00     2.00  \n",
       "18   188,953.16     3.00  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#adding row numbers by sales value per Area\n",
    "df['Row Num'] = df.groupby(['Area'])['Total Sales'].rank(method='first',ascending=False) \n",
    "\n",
    "#sort values and check\n",
    "df.sort_values(by=['Area','Total Sales'], ascending=[True,False]).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Row number/Rank with order by based on multiple columns</b>\n",
    "\n",
    "Sort the df beforehand using the order by cols, then use cumcount to get the ranking."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cols = ['Classification', 'Total Sales']\n",
    "df = df.sort_values(cols, ascending=[True,False])\n",
    "df['Rank'] = df.groupby(['Area'],sort=False).cumcount()+1 #+1 as cumcount output starts at 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here Rank column goes from 1 to N per Area, with 1 assigned to Outlet with earliest Classification (alphabetical sort) and  highest sales within that Area."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Row Num</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.57</td>\n",
       "      <td>88.35</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10316</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>35,000.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10313</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>30,000.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.58</td>\n",
       "      <td>88.36</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01559</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>445,500.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01568</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>432,000.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01565</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>A</td>\n",
       "      <td>405,000.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01594</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>B</td>\n",
       "      <td>127,500.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01595</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>B</td>\n",
       "      <td>120,000.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01598</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.31</td>\n",
       "      <td>B</td>\n",
       "      <td>60,600.00</td>\n",
       "      <td>9.00</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02232</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.32</td>\n",
       "      <td>C</td>\n",
       "      <td>116,250.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02251</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.32</td>\n",
       "      <td>C</td>\n",
       "      <td>116,250.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02236</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.32</td>\n",
       "      <td>C</td>\n",
       "      <td>64,800.00</td>\n",
       "      <td>8.00</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02239</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.50</td>\n",
       "      <td>88.32</td>\n",
       "      <td>C</td>\n",
       "      <td>29,700.00</td>\n",
       "      <td>10.00</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18805</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>B</td>\n",
       "      <td>235,000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18739</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>B</td>\n",
       "      <td>232,500.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area3</td>\n",
       "      <td>O18774</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>22.66</td>\n",
       "      <td>88.38</td>\n",
       "      <td>B</td>\n",
       "      <td>188,953.16</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Region   Area Outlet Code Outlet Type  Latitude  Longitude Classification  \\\n",
       "3   City1  Area1      O10214    RETAILER     22.58      88.36              B   \n",
       "2   City1  Area1      O10190    RETAILER     22.58      88.36              B   \n",
       "0   City1  Area1      O09372    RETAILER     22.57      88.35              B   \n",
       "1   City1  Area1      O09517    RETAILER     22.57      88.35              B   \n",
       "6   City1  Area1      O10316    RETAILER     22.58      88.36              C   \n",
       "5   City1  Area1      O10313    RETAILER     22.58      88.36              C   \n",
       "4   City1  Area1      O10100    RETAILER     22.58      88.36              C   \n",
       "7   City1  Area2      O01559    RETAILER     22.50      88.31              A   \n",
       "9   City1  Area2      O01568    RETAILER     22.50      88.31              A   \n",
       "8   City1  Area2      O01565    RETAILER     22.50      88.31              A   \n",
       "10  City1  Area2      O01594    RETAILER     22.50      88.31              B   \n",
       "11  City1  Area2      O01595    RETAILER     22.50      88.31              B   \n",
       "12  City1  Area2      O01598    RETAILER     22.50      88.31              B   \n",
       "13  City1  Area2      O02232    RETAILER     22.50      88.32              C   \n",
       "16  City1  Area2      O02251    RETAILER     22.50      88.32              C   \n",
       "14  City1  Area2      O02236    RETAILER     22.50      88.32              C   \n",
       "15  City1  Area2      O02239    RETAILER     22.50      88.32              C   \n",
       "19  City1  Area3      O18805    RETAILER     22.66      88.38              B   \n",
       "17  City1  Area3      O18739    RETAILER     22.66      88.38              B   \n",
       "18  City1  Area3      O18774    RETAILER     22.66      88.38              B   \n",
       "\n",
       "    Total Sales  Row Num  Rank  \n",
       "3    180,004.00     1.00     1  \n",
       "2    180,003.00     2.00     2  \n",
       "0    180,001.00     3.00     3  \n",
       "1    150,002.00     4.00     4  \n",
       "6     35,000.00     5.00     5  \n",
       "5     30,000.00     6.00     6  \n",
       "4     15,000.00     7.00     7  \n",
       "7    445,500.00     1.00     1  \n",
       "9    432,000.00     2.00     2  \n",
       "8    405,000.00     3.00     3  \n",
       "10   127,500.00     4.00     4  \n",
       "11   120,000.00     5.00     5  \n",
       "12    60,600.00     9.00     6  \n",
       "13   116,250.00     6.00     7  \n",
       "16   116,250.00     7.00     8  \n",
       "14    64,800.00     8.00     9  \n",
       "15    29,700.00    10.00    10  \n",
       "19   235,000.00     1.00     1  \n",
       "17   232,500.00     2.00     2  \n",
       "18   188,953.16     3.00     3  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['Area','Classification', 'Total Sales'],ascending=[True,True,False]).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Cumulative sum</b>\n",
    "\n",
    "Sort data beforehand.\n",
    "\n",
    "Here the Cumulative sales restart for every Area and add up sales in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['Classification', 'Total Sales']\n",
    "df = df.sort_values(cols, ascending=[True,False])\n",
    "\n",
    "df['Cumulative Sales'] = df.groupby(['Area'])['Total Sales'].cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Outlet Type</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Cumulative Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>360,007.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>540,008.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>690,010.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10316</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>C</td>\n",
       "      <td>35,000.00</td>\n",
       "      <td>725,010.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10313</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>C</td>\n",
       "      <td>30,000.00</td>\n",
       "      <td>755,010.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>770,010.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O01559</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>A</td>\n",
       "      <td>445,500.00</td>\n",
       "      <td>445,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O01568</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>A</td>\n",
       "      <td>432,000.00</td>\n",
       "      <td>877,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O01565</td>\n",
       "      <td>RETAILER</td>\n",
       "      <td>A</td>\n",
       "      <td>405,000.00</td>\n",
       "      <td>1,282,500.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Area Outlet Code Outlet Type Classification  Total Sales  Cumulative Sales\n",
       "3  Area1      O10214    RETAILER              B   180,004.00        180,004.00\n",
       "2  Area1      O10190    RETAILER              B   180,003.00        360,007.00\n",
       "0  Area1      O09372    RETAILER              B   180,001.00        540,008.00\n",
       "1  Area1      O09517    RETAILER              B   150,002.00        690,010.00\n",
       "6  Area1      O10316    RETAILER              C    35,000.00        725,010.00\n",
       "5  Area1      O10313    RETAILER              C    30,000.00        755,010.00\n",
       "4  Area1      O10100    RETAILER              C    15,000.00        770,010.00\n",
       "7  Area2      O01559    RETAILER              A   445,500.00        445,500.00\n",
       "9  Area2      O01568    RETAILER              A   432,000.00        877,500.00\n",
       "8  Area2      O01565    RETAILER              A   405,000.00      1,282,500.00"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['Area','Total Sales'],ascending=[True,False])[[\n",
    "    'Area','Outlet Code','Outlet Type','Classification','Total Sales','Cumulative Sales'\n",
    "    ]].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Moving Average: Rolling window calculation</b>\n",
    "\n",
    "Sort data beforehand. This is more applicable to data containing date/time component.\n",
    "\n",
    "- Rolling mean will compute mean of last N rows, whereas\n",
    "- Expanding mean will compute mean of all previous rows\n",
    "- Both include current row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['Area','Classification', 'Total Sales']\n",
    "df = df.sort_values(cols, ascending=[True,True,False])\n",
    "\n",
    "df['Rolling Mean 7 stores']   = df['Total Sales'].rolling(7).mean().round(1)\n",
    "df['Expanding Mean 7 stores'] = df['Total Sales'].expanding().mean().round(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Rolling Mean 7 stores</th>\n",
       "      <th>Expanding Mean 7 stores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>180,003.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>180,002.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>172,502.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10316</td>\n",
       "      <td>C</td>\n",
       "      <td>35,000.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>145,002.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10313</td>\n",
       "      <td>C</td>\n",
       "      <td>30,000.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>125,835.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>110,001.40</td>\n",
       "      <td>110,001.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O01559</td>\n",
       "      <td>A</td>\n",
       "      <td>445,500.00</td>\n",
       "      <td>147,929.40</td>\n",
       "      <td>151,938.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O01568</td>\n",
       "      <td>A</td>\n",
       "      <td>432,000.00</td>\n",
       "      <td>183,929.00</td>\n",
       "      <td>183,056.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O01565</td>\n",
       "      <td>A</td>\n",
       "      <td>405,000.00</td>\n",
       "      <td>216,071.70</td>\n",
       "      <td>205,251.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Area Outlet Code Classification  Total Sales  Rolling Mean 7 stores  \\\n",
       "3  Area1      O10214              B   180,004.00                    nan   \n",
       "2  Area1      O10190              B   180,003.00                    nan   \n",
       "0  Area1      O09372              B   180,001.00                    nan   \n",
       "1  Area1      O09517              B   150,002.00                    nan   \n",
       "6  Area1      O10316              C    35,000.00                    nan   \n",
       "5  Area1      O10313              C    30,000.00                    nan   \n",
       "4  Area1      O10100              C    15,000.00             110,001.40   \n",
       "7  Area2      O01559              A   445,500.00             147,929.40   \n",
       "9  Area2      O01568              A   432,000.00             183,929.00   \n",
       "8  Area2      O01565              A   405,000.00             216,071.70   \n",
       "\n",
       "   Expanding Mean 7 stores  \n",
       "3               180,004.00  \n",
       "2               180,003.50  \n",
       "0               180,002.70  \n",
       "1               172,502.50  \n",
       "6               145,002.00  \n",
       "5               125,835.00  \n",
       "4               110,001.40  \n",
       "7               151,938.80  \n",
       "9               183,056.70  \n",
       "8               205,251.00  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\n",
    "    'Area','Outlet Code','Classification','Total Sales','Rolling Mean 7 stores',\n",
    "    'Expanding Mean 7 stores'\n",
    "    ]].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>First/Last values</b>: achieved using rank method\n",
    "\n",
    "Sort data beforehand. 'ascending = False' inside rank gives Last value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['Area','Total Sales']\n",
    "df = df.sort_values(cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['First Value'] = df['Total Sales'].loc[df.groupby('Area')['Total Sales'].rank(method='first')==1]\n",
    "df['First Value'] = df['First Value'].ffill()\n",
    "\n",
    "df['Last Value'] = df['Total Sales'].loc[df.groupby('Area')['Total Sales'].rank(method='first', ascending=False)==1]\n",
    "df['Last Value'] = df['Last Value'].bfill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>Region</th>\n",
       "      <th>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>First Value</th>\n",
       "      <th>Last Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10313</td>\n",
       "      <td>C</td>\n",
       "      <td>30,000.00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10316</td>\n",
       "      <td>C</td>\n",
       "      <td>35,000.00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02239</td>\n",
       "      <td>C</td>\n",
       "      <td>29,700.00</td>\n",
       "      <td>29,700.00</td>\n",
       "      <td>445,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O01598</td>\n",
       "      <td>B</td>\n",
       "      <td>60,600.00</td>\n",
       "      <td>29,700.00</td>\n",
       "      <td>445,500.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>City1</td>\n",
       "      <td>Area2</td>\n",
       "      <td>O02236</td>\n",
       "      <td>C</td>\n",
       "      <td>64,800.00</td>\n",
       "      <td>29,700.00</td>\n",
       "      <td>445,500.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Region   Area Outlet Code Classification  Total Sales  First Value  \\\n",
       "4   City1  Area1      O10100              C    15,000.00    15,000.00   \n",
       "5   City1  Area1      O10313              C    30,000.00    15,000.00   \n",
       "6   City1  Area1      O10316              C    35,000.00    15,000.00   \n",
       "1   City1  Area1      O09517              B   150,002.00    15,000.00   \n",
       "0   City1  Area1      O09372              B   180,001.00    15,000.00   \n",
       "2   City1  Area1      O10190              B   180,003.00    15,000.00   \n",
       "3   City1  Area1      O10214              B   180,004.00    15,000.00   \n",
       "15  City1  Area2      O02239              C    29,700.00    29,700.00   \n",
       "12  City1  Area2      O01598              B    60,600.00    29,700.00   \n",
       "14  City1  Area2      O02236              C    64,800.00    29,700.00   \n",
       "\n",
       "    Last Value  \n",
       "4   180,004.00  \n",
       "5   180,004.00  \n",
       "6   180,004.00  \n",
       "1   180,004.00  \n",
       "0   180,004.00  \n",
       "2   180,004.00  \n",
       "3   180,004.00  \n",
       "15  445,500.00  \n",
       "12  445,500.00  \n",
       "14  445,500.00  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\n",
    "    'Region','Area','Outlet Code','Classification','Total Sales','First Value','Last Value'\n",
    "    ]].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Lead/Lag</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['Area','Total Sales']\n",
    "df = df.sort_values(cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Prev. Sales'] = df.groupby('Area')['Total Sales'].shift(1)\n",
    "df['Next Sales'] = df.groupby('Area')['Total Sales'].shift(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>Area</th>\n",
       "      <th>Outlet Code</th>\n",
       "      <th>Classification</th>\n",
       "      <th>Total Sales</th>\n",
       "      <th>Prev. Sales</th>\n",
       "      <th>Next Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10100</td>\n",
       "      <td>C</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>30,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10313</td>\n",
       "      <td>C</td>\n",
       "      <td>30,000.00</td>\n",
       "      <td>15,000.00</td>\n",
       "      <td>35,000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10316</td>\n",
       "      <td>C</td>\n",
       "      <td>35,000.00</td>\n",
       "      <td>30,000.00</td>\n",
       "      <td>150,002.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O09517</td>\n",
       "      <td>B</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>35,000.00</td>\n",
       "      <td>180,001.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O09372</td>\n",
       "      <td>B</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>150,002.00</td>\n",
       "      <td>180,003.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10190</td>\n",
       "      <td>B</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>180,001.00</td>\n",
       "      <td>180,004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Area1</td>\n",
       "      <td>O10214</td>\n",
       "      <td>B</td>\n",
       "      <td>180,004.00</td>\n",
       "      <td>180,003.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O02239</td>\n",
       "      <td>C</td>\n",
       "      <td>29,700.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>60,600.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O01598</td>\n",
       "      <td>B</td>\n",
       "      <td>60,600.00</td>\n",
       "      <td>29,700.00</td>\n",
       "      <td>64,800.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Area2</td>\n",
       "      <td>O02236</td>\n",
       "      <td>C</td>\n",
       "      <td>64,800.00</td>\n",
       "      <td>60,600.00</td>\n",
       "      <td>116,250.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Area Outlet Code Classification  Total Sales  Prev. Sales  Next Sales\n",
       "4   Area1      O10100              C    15,000.00          nan   30,000.00\n",
       "5   Area1      O10313              C    30,000.00    15,000.00   35,000.00\n",
       "6   Area1      O10316              C    35,000.00    30,000.00  150,002.00\n",
       "1   Area1      O09517              B   150,002.00    35,000.00  180,001.00\n",
       "0   Area1      O09372              B   180,001.00   150,002.00  180,003.00\n",
       "2   Area1      O10190              B   180,003.00   180,001.00  180,004.00\n",
       "3   Area1      O10214              B   180,004.00   180,003.00         nan\n",
       "15  Area2      O02239              C    29,700.00          nan   60,600.00\n",
       "12  Area2      O01598              B    60,600.00    29,700.00   64,800.00\n",
       "14  Area2      O02236              C    64,800.00    60,600.00  116,250.00"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[[\n",
    "    'Area','Outlet Code','Classification','Total Sales','Prev. Sales','Next Sales'\n",
    "    ]].head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}