7_Lambda_Functions_Pivot_Tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lambda Functions and Pivot Tables\n",
    "\n",
    "Until now, we have not made any changes or modifications to the data. In this section, we will:\n",
    "* Use lambda functions to create new and alter existing columns\n",
    "* Use pandas pivot tables as an alternative to ```df.groupby()``` to summarise data\n",
    "\n",
    "Let's first read all the files and create a ```master_df```. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>Ord_id</th>\n",
       "      <th>Prod_id</th>\n",
       "      <th>Ship_id</th>\n",
       "      <th>Cust_id</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Order_Quantity</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Product_Base_Margin</th>\n",
       "      <th>...</th>\n",
       "      <th>Region</th>\n",
       "      <th>Customer_Segment</th>\n",
       "      <th>Product_Category</th>\n",
       "      <th>Product_Sub_Category</th>\n",
       "      <th>Order_ID_x</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Order_ID_y</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Order_Priority</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7609</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>136.81</td>\n",
       "      <td>0.01</td>\n",
       "      <td>23</td>\n",
       "      <td>-30.51</td>\n",
       "      <td>3.60</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>WEST</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>36262</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_4</td>\n",
       "      <td>SHP_7610</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>4701.69</td>\n",
       "      <td>0.00</td>\n",
       "      <td>26</td>\n",
       "      <td>1148.90</td>\n",
       "      <td>2.50</td>\n",
       "      <td>0.59</td>\n",
       "      <td>...</td>\n",
       "      <td>WEST</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>TECHNOLOGY</td>\n",
       "      <td>TELEPHONES AND COMMUNICATION</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_6</td>\n",
       "      <td>SHP_7608</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>164.02</td>\n",
       "      <td>0.03</td>\n",
       "      <td>23</td>\n",
       "      <td>-47.64</td>\n",
       "      <td>6.15</td>\n",
       "      <td>0.37</td>\n",
       "      <td>...</td>\n",
       "      <td>WEST</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>PAPER</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ord_2978</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_4112</td>\n",
       "      <td>Cust_1088</td>\n",
       "      <td>305.05</td>\n",
       "      <td>0.04</td>\n",
       "      <td>27</td>\n",
       "      <td>23.12</td>\n",
       "      <td>3.37</td>\n",
       "      <td>0.57</td>\n",
       "      <td>...</td>\n",
       "      <td>ONTARIO</td>\n",
       "      <td>HOME OFFICE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>37863</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>26-02-2011</td>\n",
       "      <td>37863</td>\n",
       "      <td>24-02-2011</td>\n",
       "      <td>HIGH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ord_5484</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7663</td>\n",
       "      <td>Cust_1820</td>\n",
       "      <td>322.82</td>\n",
       "      <td>0.05</td>\n",
       "      <td>35</td>\n",
       "      <td>-17.58</td>\n",
       "      <td>3.98</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>WEST</td>\n",
       "      <td>CONSUMER</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>53026</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>03-03-2012</td>\n",
       "      <td>53026</td>\n",
       "      <td>26-02-2012</td>\n",
       "      <td>LOW</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ord_id  Prod_id   Ship_id    Cust_id    Sales  Discount  Order_Quantity  \\\n",
       "0  Ord_5446  Prod_16  SHP_7609  Cust_1818   136.81      0.01              23   \n",
       "1  Ord_5446   Prod_4  SHP_7610  Cust_1818  4701.69      0.00              26   \n",
       "2  Ord_5446   Prod_6  SHP_7608  Cust_1818   164.02      0.03              23   \n",
       "3  Ord_2978  Prod_16  SHP_4112  Cust_1088   305.05      0.04              27   \n",
       "4  Ord_5484  Prod_16  SHP_7663  Cust_1820   322.82      0.05              35   \n",
       "\n",
       "    Profit  Shipping_Cost  Product_Base_Margin       ...         Region  \\\n",
       "0   -30.51           3.60                 0.56       ...           WEST   \n",
       "1  1148.90           2.50                 0.59       ...           WEST   \n",
       "2   -47.64           6.15                 0.37       ...           WEST   \n",
       "3    23.12           3.37                 0.57       ...        ONTARIO   \n",
       "4   -17.58           3.98                 0.56       ...           WEST   \n",
       "\n",
       "  Customer_Segment Product_Category           Product_Sub_Category Order_ID_x  \\\n",
       "0        CORPORATE  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      36262   \n",
       "1        CORPORATE       TECHNOLOGY   TELEPHONES AND COMMUNICATION      36262   \n",
       "2        CORPORATE  OFFICE SUPPLIES                          PAPER      36262   \n",
       "3      HOME OFFICE  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      37863   \n",
       "4         CONSUMER  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      53026   \n",
       "\n",
       "     Ship_Mode   Ship_Date Order_ID_y  Order_Date  Order_Priority  \n",
       "0  REGULAR AIR  28-07-2010      36262  27-07-2010   NOT SPECIFIED  \n",
       "1  EXPRESS AIR  27-07-2010      36262  27-07-2010   NOT SPECIFIED  \n",
       "2  EXPRESS AIR  28-07-2010      36262  27-07-2010   NOT SPECIFIED  \n",
       "3  REGULAR AIR  26-02-2011      37863  24-02-2011            HIGH  \n",
       "4  REGULAR AIR  03-03-2012      53026  26-02-2012             LOW  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading libraries and files\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "market_df = pd.read_csv(\"global_sales_data/market_fact.csv\")\n",
    "customer_df = pd.read_csv(\"global_sales_data/cust_dimen.csv\")\n",
    "product_df = pd.read_csv(\"global_sales_data/prod_dimen.csv\")\n",
    "shipping_df = pd.read_csv(\"global_sales_data/shipping_dimen.csv\")\n",
    "orders_df = pd.read_csv(\"global_sales_data/orders_dimen.csv\")\n",
    "\n",
    "# Merging the dataframes to create a master_df\n",
    "df_1 = pd.merge(market_df, customer_df, how='inner', on='Cust_id')\n",
    "df_2 = pd.merge(df_1, product_df, how='inner', on='Prod_id')\n",
    "df_3 = pd.merge(df_2, shipping_df, how='inner', on='Ship_id')\n",
    "master_df = pd.merge(df_3, orders_df, how='inner', on='Ord_id')\n",
    "\n",
    "master_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lambda Functions\n",
    "\n",
    "Say you want to create a new column indicating whether a given order was profitable or not (1/0). \n",
    "\n",
    "You need to apply a function which returns 1 if Profit > 0, else 0. This can be easily done using the ```apply()``` method on a column of the dataframe. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Ord_id</th>\n",
       "      <th>Prod_id</th>\n",
       "      <th>Ship_id</th>\n",
       "      <th>Cust_id</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Order_Quantity</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Product_Base_Margin</th>\n",
       "      <th>...</th>\n",
       "      <th>Customer_Segment</th>\n",
       "      <th>Product_Category</th>\n",
       "      <th>Product_Sub_Category</th>\n",
       "      <th>Order_ID_x</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Order_ID_y</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>is_profitable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7609</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>136.81</td>\n",
       "      <td>0.01</td>\n",
       "      <td>23</td>\n",
       "      <td>-30.51</td>\n",
       "      <td>3.60</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>36262</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_4</td>\n",
       "      <td>SHP_7610</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>4701.69</td>\n",
       "      <td>0.00</td>\n",
       "      <td>26</td>\n",
       "      <td>1148.90</td>\n",
       "      <td>2.50</td>\n",
       "      <td>0.59</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>TECHNOLOGY</td>\n",
       "      <td>TELEPHONES AND COMMUNICATION</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_6</td>\n",
       "      <td>SHP_7608</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>164.02</td>\n",
       "      <td>0.03</td>\n",
       "      <td>23</td>\n",
       "      <td>-47.64</td>\n",
       "      <td>6.15</td>\n",
       "      <td>0.37</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>PAPER</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ord_2978</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_4112</td>\n",
       "      <td>Cust_1088</td>\n",
       "      <td>305.05</td>\n",
       "      <td>0.04</td>\n",
       "      <td>27</td>\n",
       "      <td>23.12</td>\n",
       "      <td>3.37</td>\n",
       "      <td>0.57</td>\n",
       "      <td>...</td>\n",
       "      <td>HOME OFFICE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>37863</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>26-02-2011</td>\n",
       "      <td>37863</td>\n",
       "      <td>24-02-2011</td>\n",
       "      <td>HIGH</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ord_5484</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7663</td>\n",
       "      <td>Cust_1820</td>\n",
       "      <td>322.82</td>\n",
       "      <td>0.05</td>\n",
       "      <td>35</td>\n",
       "      <td>-17.58</td>\n",
       "      <td>3.98</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>CONSUMER</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>53026</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>03-03-2012</td>\n",
       "      <td>53026</td>\n",
       "      <td>26-02-2012</td>\n",
       "      <td>LOW</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ord_id  Prod_id   Ship_id    Cust_id    Sales  Discount  Order_Quantity  \\\n",
       "0  Ord_5446  Prod_16  SHP_7609  Cust_1818   136.81      0.01              23   \n",
       "1  Ord_5446   Prod_4  SHP_7610  Cust_1818  4701.69      0.00              26   \n",
       "2  Ord_5446   Prod_6  SHP_7608  Cust_1818   164.02      0.03              23   \n",
       "3  Ord_2978  Prod_16  SHP_4112  Cust_1088   305.05      0.04              27   \n",
       "4  Ord_5484  Prod_16  SHP_7663  Cust_1820   322.82      0.05              35   \n",
       "\n",
       "    Profit  Shipping_Cost  Product_Base_Margin      ...        \\\n",
       "0   -30.51           3.60                 0.56      ...         \n",
       "1  1148.90           2.50                 0.59      ...         \n",
       "2   -47.64           6.15                 0.37      ...         \n",
       "3    23.12           3.37                 0.57      ...         \n",
       "4   -17.58           3.98                 0.56      ...         \n",
       "\n",
       "  Customer_Segment Product_Category           Product_Sub_Category Order_ID_x  \\\n",
       "0        CORPORATE  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      36262   \n",
       "1        CORPORATE       TECHNOLOGY   TELEPHONES AND COMMUNICATION      36262   \n",
       "2        CORPORATE  OFFICE SUPPLIES                          PAPER      36262   \n",
       "3      HOME OFFICE  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      37863   \n",
       "4         CONSUMER  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      53026   \n",
       "\n",
       "     Ship_Mode   Ship_Date  Order_ID_y  Order_Date Order_Priority  \\\n",
       "0  REGULAR AIR  28-07-2010       36262  27-07-2010  NOT SPECIFIED   \n",
       "1  EXPRESS AIR  27-07-2010       36262  27-07-2010  NOT SPECIFIED   \n",
       "2  EXPRESS AIR  28-07-2010       36262  27-07-2010  NOT SPECIFIED   \n",
       "3  REGULAR AIR  26-02-2011       37863  24-02-2011           HIGH   \n",
       "4  REGULAR AIR  03-03-2012       53026  26-02-2012            LOW   \n",
       "\n",
       "   is_profitable  \n",
       "0          False  \n",
       "1           True  \n",
       "2          False  \n",
       "3           True  \n",
       "4          False  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a function to be applied\n",
    "def is_positive(x):\n",
    "    return x > 0\n",
    "\n",
    "# Create a new column\n",
    "master_df['is_profitable'] = master_df['Profit'].apply(is_positive)\n",
    "master_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The same can be done in just one line of code using lambda functions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Ord_id</th>\n",
       "      <th>Prod_id</th>\n",
       "      <th>Ship_id</th>\n",
       "      <th>Cust_id</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Order_Quantity</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Product_Base_Margin</th>\n",
       "      <th>...</th>\n",
       "      <th>Customer_Segment</th>\n",
       "      <th>Product_Category</th>\n",
       "      <th>Product_Sub_Category</th>\n",
       "      <th>Order_ID_x</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Order_ID_y</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>is_profitable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7609</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>136.81</td>\n",
       "      <td>0.01</td>\n",
       "      <td>23</td>\n",
       "      <td>-30.51</td>\n",
       "      <td>3.60</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>36262</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_4</td>\n",
       "      <td>SHP_7610</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>4701.69</td>\n",
       "      <td>0.00</td>\n",
       "      <td>26</td>\n",
       "      <td>1148.90</td>\n",
       "      <td>2.50</td>\n",
       "      <td>0.59</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>TECHNOLOGY</td>\n",
       "      <td>TELEPHONES AND COMMUNICATION</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_6</td>\n",
       "      <td>SHP_7608</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>164.02</td>\n",
       "      <td>0.03</td>\n",
       "      <td>23</td>\n",
       "      <td>-47.64</td>\n",
       "      <td>6.15</td>\n",
       "      <td>0.37</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>PAPER</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ord_2978</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_4112</td>\n",
       "      <td>Cust_1088</td>\n",
       "      <td>305.05</td>\n",
       "      <td>0.04</td>\n",
       "      <td>27</td>\n",
       "      <td>23.12</td>\n",
       "      <td>3.37</td>\n",
       "      <td>0.57</td>\n",
       "      <td>...</td>\n",
       "      <td>HOME OFFICE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>37863</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>26-02-2011</td>\n",
       "      <td>37863</td>\n",
       "      <td>24-02-2011</td>\n",
       "      <td>HIGH</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ord_5484</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7663</td>\n",
       "      <td>Cust_1820</td>\n",
       "      <td>322.82</td>\n",
       "      <td>0.05</td>\n",
       "      <td>35</td>\n",
       "      <td>-17.58</td>\n",
       "      <td>3.98</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>CONSUMER</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>53026</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>03-03-2012</td>\n",
       "      <td>53026</td>\n",
       "      <td>26-02-2012</td>\n",
       "      <td>LOW</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ord_id  Prod_id   Ship_id    Cust_id    Sales  Discount  Order_Quantity  \\\n",
       "0  Ord_5446  Prod_16  SHP_7609  Cust_1818   136.81      0.01              23   \n",
       "1  Ord_5446   Prod_4  SHP_7610  Cust_1818  4701.69      0.00              26   \n",
       "2  Ord_5446   Prod_6  SHP_7608  Cust_1818   164.02      0.03              23   \n",
       "3  Ord_2978  Prod_16  SHP_4112  Cust_1088   305.05      0.04              27   \n",
       "4  Ord_5484  Prod_16  SHP_7663  Cust_1820   322.82      0.05              35   \n",
       "\n",
       "    Profit  Shipping_Cost  Product_Base_Margin      ...        \\\n",
       "0   -30.51           3.60                 0.56      ...         \n",
       "1  1148.90           2.50                 0.59      ...         \n",
       "2   -47.64           6.15                 0.37      ...         \n",
       "3    23.12           3.37                 0.57      ...         \n",
       "4   -17.58           3.98                 0.56      ...         \n",
       "\n",
       "  Customer_Segment Product_Category           Product_Sub_Category Order_ID_x  \\\n",
       "0        CORPORATE  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      36262   \n",
       "1        CORPORATE       TECHNOLOGY   TELEPHONES AND COMMUNICATION      36262   \n",
       "2        CORPORATE  OFFICE SUPPLIES                          PAPER      36262   \n",
       "3      HOME OFFICE  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      37863   \n",
       "4         CONSUMER  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      53026   \n",
       "\n",
       "     Ship_Mode   Ship_Date  Order_ID_y  Order_Date Order_Priority  \\\n",
       "0  REGULAR AIR  28-07-2010       36262  27-07-2010  NOT SPECIFIED   \n",
       "1  EXPRESS AIR  27-07-2010       36262  27-07-2010  NOT SPECIFIED   \n",
       "2  EXPRESS AIR  28-07-2010       36262  27-07-2010  NOT SPECIFIED   \n",
       "3  REGULAR AIR  26-02-2011       37863  24-02-2011           HIGH   \n",
       "4  REGULAR AIR  03-03-2012       53026  26-02-2012            LOW   \n",
       "\n",
       "   is_profitable  \n",
       "0          False  \n",
       "1           True  \n",
       "2          False  \n",
       "3           True  \n",
       "4          False  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new column using a lambda function\n",
    "master_df['is_profitable'] = master_df['Profit'].apply(lambda x: x > 0)\n",
    "master_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you can use the new column to compare the percentage of profitable orders across groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Customer_Segment\n",
       "CONSUMER          0.500910\n",
       "CORPORATE         0.481469\n",
       "HOME OFFICE       0.498524\n",
       "SMALL BUSINESS    0.496346\n",
       "Name: is_profitable, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Comparing percentage of profitable orders across customer segments\n",
    "by_segment = master_df.groupby('Customer_Segment')\n",
    "by_segment.is_profitable.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Product_Category\n",
       "FURNITURE          0.465197\n",
       "OFFICE SUPPLIES    0.466161\n",
       "TECHNOLOGY         0.573366\n",
       "Name: is_profitable, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Comparing percentage of profitable orders across product categories\n",
    "by_category = master_df.groupby('Product_Category')\n",
    "by_category.is_profitable.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In FURNITURE, 46% orders are profitable, compared to 57% in TECHNOLOGY. "
   ]
  },
  {
   "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>Ord_id</th>\n",
       "      <th>Prod_id</th>\n",
       "      <th>Ship_id</th>\n",
       "      <th>Cust_id</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Order_Quantity</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Product_Base_Margin</th>\n",
       "      <th>...</th>\n",
       "      <th>Customer_Segment</th>\n",
       "      <th>Product_Category</th>\n",
       "      <th>Product_Sub_Category</th>\n",
       "      <th>Order_ID_x</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Order_ID_y</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>is_profitable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7609</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>136.81</td>\n",
       "      <td>0.01</td>\n",
       "      <td>23</td>\n",
       "      <td>-30.5</td>\n",
       "      <td>3.60</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>36262</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_4</td>\n",
       "      <td>SHP_7610</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>4701.69</td>\n",
       "      <td>0.00</td>\n",
       "      <td>26</td>\n",
       "      <td>1148.9</td>\n",
       "      <td>2.50</td>\n",
       "      <td>0.59</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>TECHNOLOGY</td>\n",
       "      <td>TELEPHONES AND COMMUNICATION</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_6</td>\n",
       "      <td>SHP_7608</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>164.02</td>\n",
       "      <td>0.03</td>\n",
       "      <td>23</td>\n",
       "      <td>-47.6</td>\n",
       "      <td>6.15</td>\n",
       "      <td>0.37</td>\n",
       "      <td>...</td>\n",
       "      <td>CORPORATE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>PAPER</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ord_2978</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_4112</td>\n",
       "      <td>Cust_1088</td>\n",
       "      <td>305.05</td>\n",
       "      <td>0.04</td>\n",
       "      <td>27</td>\n",
       "      <td>23.1</td>\n",
       "      <td>3.37</td>\n",
       "      <td>0.57</td>\n",
       "      <td>...</td>\n",
       "      <td>HOME OFFICE</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>37863</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>26-02-2011</td>\n",
       "      <td>37863</td>\n",
       "      <td>24-02-2011</td>\n",
       "      <td>HIGH</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ord_5484</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7663</td>\n",
       "      <td>Cust_1820</td>\n",
       "      <td>322.82</td>\n",
       "      <td>0.05</td>\n",
       "      <td>35</td>\n",
       "      <td>-17.6</td>\n",
       "      <td>3.98</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>CONSUMER</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>53026</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>03-03-2012</td>\n",
       "      <td>53026</td>\n",
       "      <td>26-02-2012</td>\n",
       "      <td>LOW</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ord_id  Prod_id   Ship_id    Cust_id    Sales  Discount  Order_Quantity  \\\n",
       "0  Ord_5446  Prod_16  SHP_7609  Cust_1818   136.81      0.01              23   \n",
       "1  Ord_5446   Prod_4  SHP_7610  Cust_1818  4701.69      0.00              26   \n",
       "2  Ord_5446   Prod_6  SHP_7608  Cust_1818   164.02      0.03              23   \n",
       "3  Ord_2978  Prod_16  SHP_4112  Cust_1088   305.05      0.04              27   \n",
       "4  Ord_5484  Prod_16  SHP_7663  Cust_1820   322.82      0.05              35   \n",
       "\n",
       "   Profit  Shipping_Cost  Product_Base_Margin      ...       Customer_Segment  \\\n",
       "0   -30.5           3.60                 0.56      ...              CORPORATE   \n",
       "1  1148.9           2.50                 0.59      ...              CORPORATE   \n",
       "2   -47.6           6.15                 0.37      ...              CORPORATE   \n",
       "3    23.1           3.37                 0.57      ...            HOME OFFICE   \n",
       "4   -17.6           3.98                 0.56      ...               CONSUMER   \n",
       "\n",
       "  Product_Category           Product_Sub_Category Order_ID_x    Ship_Mode  \\\n",
       "0  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      36262  REGULAR AIR   \n",
       "1       TECHNOLOGY   TELEPHONES AND COMMUNICATION      36262  EXPRESS AIR   \n",
       "2  OFFICE SUPPLIES                          PAPER      36262  EXPRESS AIR   \n",
       "3  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      37863  REGULAR AIR   \n",
       "4  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      53026  REGULAR AIR   \n",
       "\n",
       "    Ship_Date  Order_ID_y  Order_Date Order_Priority  is_profitable  \n",
       "0  28-07-2010       36262  27-07-2010  NOT SPECIFIED          False  \n",
       "1  27-07-2010       36262  27-07-2010  NOT SPECIFIED           True  \n",
       "2  28-07-2010       36262  27-07-2010  NOT SPECIFIED          False  \n",
       "3  26-02-2011       37863  24-02-2011           HIGH           True  \n",
       "4  03-03-2012       53026  26-02-2012            LOW          False  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You can also use apply and lambda to alter existing columns\n",
    "# E.g. you want to see Profit as one decimal place\n",
    "# apply the round() function \n",
    "master_df['Profit'] = master_df['Profit'].apply(lambda x: round(x, 1))\n",
    "master_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You sometimes need to create new columns using existing columns, for instance, say you want a column ```Profit / Order_Quantity```. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Ord_id</th>\n",
       "      <th>Prod_id</th>\n",
       "      <th>Ship_id</th>\n",
       "      <th>Cust_id</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Order_Quantity</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <th>Product_Base_Margin</th>\n",
       "      <th>...</th>\n",
       "      <th>Product_Category</th>\n",
       "      <th>Product_Sub_Category</th>\n",
       "      <th>Order_ID_x</th>\n",
       "      <th>Ship_Mode</th>\n",
       "      <th>Ship_Date</th>\n",
       "      <th>Order_ID_y</th>\n",
       "      <th>Order_Date</th>\n",
       "      <th>Order_Priority</th>\n",
       "      <th>is_profitable</th>\n",
       "      <th>profit_per_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7609</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>136.81</td>\n",
       "      <td>0.01</td>\n",
       "      <td>23</td>\n",
       "      <td>-30.5</td>\n",
       "      <td>3.60</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>36262</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>False</td>\n",
       "      <td>-1.326087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_4</td>\n",
       "      <td>SHP_7610</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>4701.69</td>\n",
       "      <td>0.00</td>\n",
       "      <td>26</td>\n",
       "      <td>1148.9</td>\n",
       "      <td>2.50</td>\n",
       "      <td>0.59</td>\n",
       "      <td>...</td>\n",
       "      <td>TECHNOLOGY</td>\n",
       "      <td>TELEPHONES AND COMMUNICATION</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>True</td>\n",
       "      <td>44.188462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ord_5446</td>\n",
       "      <td>Prod_6</td>\n",
       "      <td>SHP_7608</td>\n",
       "      <td>Cust_1818</td>\n",
       "      <td>164.02</td>\n",
       "      <td>0.03</td>\n",
       "      <td>23</td>\n",
       "      <td>-47.6</td>\n",
       "      <td>6.15</td>\n",
       "      <td>0.37</td>\n",
       "      <td>...</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>PAPER</td>\n",
       "      <td>36262</td>\n",
       "      <td>EXPRESS AIR</td>\n",
       "      <td>28-07-2010</td>\n",
       "      <td>36262</td>\n",
       "      <td>27-07-2010</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>False</td>\n",
       "      <td>-2.069565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ord_2978</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_4112</td>\n",
       "      <td>Cust_1088</td>\n",
       "      <td>305.05</td>\n",
       "      <td>0.04</td>\n",
       "      <td>27</td>\n",
       "      <td>23.1</td>\n",
       "      <td>3.37</td>\n",
       "      <td>0.57</td>\n",
       "      <td>...</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>37863</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>26-02-2011</td>\n",
       "      <td>37863</td>\n",
       "      <td>24-02-2011</td>\n",
       "      <td>HIGH</td>\n",
       "      <td>True</td>\n",
       "      <td>0.855556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ord_5484</td>\n",
       "      <td>Prod_16</td>\n",
       "      <td>SHP_7663</td>\n",
       "      <td>Cust_1820</td>\n",
       "      <td>322.82</td>\n",
       "      <td>0.05</td>\n",
       "      <td>35</td>\n",
       "      <td>-17.6</td>\n",
       "      <td>3.98</td>\n",
       "      <td>0.56</td>\n",
       "      <td>...</td>\n",
       "      <td>OFFICE SUPPLIES</td>\n",
       "      <td>SCISSORS, RULERS AND TRIMMERS</td>\n",
       "      <td>53026</td>\n",
       "      <td>REGULAR AIR</td>\n",
       "      <td>03-03-2012</td>\n",
       "      <td>53026</td>\n",
       "      <td>26-02-2012</td>\n",
       "      <td>LOW</td>\n",
       "      <td>False</td>\n",
       "      <td>-0.502857</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Ord_id  Prod_id   Ship_id    Cust_id    Sales  Discount  Order_Quantity  \\\n",
       "0  Ord_5446  Prod_16  SHP_7609  Cust_1818   136.81      0.01              23   \n",
       "1  Ord_5446   Prod_4  SHP_7610  Cust_1818  4701.69      0.00              26   \n",
       "2  Ord_5446   Prod_6  SHP_7608  Cust_1818   164.02      0.03              23   \n",
       "3  Ord_2978  Prod_16  SHP_4112  Cust_1088   305.05      0.04              27   \n",
       "4  Ord_5484  Prod_16  SHP_7663  Cust_1820   322.82      0.05              35   \n",
       "\n",
       "   Profit  Shipping_Cost  Product_Base_Margin       ...        \\\n",
       "0   -30.5           3.60                 0.56       ...         \n",
       "1  1148.9           2.50                 0.59       ...         \n",
       "2   -47.6           6.15                 0.37       ...         \n",
       "3    23.1           3.37                 0.57       ...         \n",
       "4   -17.6           3.98                 0.56       ...         \n",
       "\n",
       "  Product_Category           Product_Sub_Category Order_ID_x    Ship_Mode  \\\n",
       "0  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      36262  REGULAR AIR   \n",
       "1       TECHNOLOGY   TELEPHONES AND COMMUNICATION      36262  EXPRESS AIR   \n",
       "2  OFFICE SUPPLIES                          PAPER      36262  EXPRESS AIR   \n",
       "3  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      37863  REGULAR AIR   \n",
       "4  OFFICE SUPPLIES  SCISSORS, RULERS AND TRIMMERS      53026  REGULAR AIR   \n",
       "\n",
       "    Ship_Date Order_ID_y  Order_Date Order_Priority is_profitable  \\\n",
       "0  28-07-2010      36262  27-07-2010  NOT SPECIFIED         False   \n",
       "1  27-07-2010      36262  27-07-2010  NOT SPECIFIED          True   \n",
       "2  28-07-2010      36262  27-07-2010  NOT SPECIFIED         False   \n",
       "3  26-02-2011      37863  24-02-2011           HIGH          True   \n",
       "4  03-03-2012      53026  26-02-2012            LOW         False   \n",
       "\n",
       "   profit_per_qty  \n",
       "0       -1.326087  \n",
       "1       44.188462  \n",
       "2       -2.069565  \n",
       "3        0.855556  \n",
       "4       -0.502857  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating a column Profit / Order_Quantity\n",
    "master_df['profit_per_qty'] = master_df['Profit'] / master_df['Order_Quantity']\n",
    "master_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot Tables\n",
    "\n",
    "You may want to use pandas pivot tables as an alternative to ```groupby()```. They provide Excel-like functionalities to create aggregate tables. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function pivot_table in module pandas.core.frame:\n",
      "\n",
      "pivot_table(self, values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All')\n",
      "    Create a spreadsheet-style pivot table as a DataFrame. The levels in\n",
      "    the pivot table will be stored in MultiIndex objects (hierarchical\n",
      "    indexes) on the index and columns of the result DataFrame\n",
      "    \n",
      "    Parameters\n",
      "    ----------\n",
      "    values : column to aggregate, optional\n",
      "    index : column, Grouper, array, or list of the previous\n",
      "        If an array is passed, it must be the same length as the data. The\n",
      "        list can contain any of the other types (except list).\n",
      "        Keys to group by on the pivot table index.  If an array is passed,\n",
      "        it is being used as the same manner as column values.\n",
      "    columns : column, Grouper, array, or list of the previous\n",
      "        If an array is passed, it must be the same length as the data. The\n",
      "        list can contain any of the other types (except list).\n",
      "        Keys to group by on the pivot table column.  If an array is passed,\n",
      "        it is being used as the same manner as column values.\n",
      "    aggfunc : function or list of functions, default numpy.mean\n",
      "        If list of functions passed, the resulting pivot table will have\n",
      "        hierarchical columns whose top level are the function names\n",
      "        (inferred from the function objects themselves)\n",
      "    fill_value : scalar, default None\n",
      "        Value to replace missing values with\n",
      "    margins : boolean, default False\n",
      "        Add all row / columns (e.g. for subtotal / grand totals)\n",
      "    dropna : boolean, default True\n",
      "        Do not include columns whose entries are all NaN\n",
      "    margins_name : string, default 'All'\n",
      "        Name of the row / column that will contain the totals\n",
      "        when margins is True.\n",
      "    \n",
      "    Examples\n",
      "    --------\n",
      "    >>> df = pd.DataFrame({\"A\": [\"foo\", \"foo\", \"foo\", \"foo\", \"foo\",\n",
      "    ...                          \"bar\", \"bar\", \"bar\", \"bar\"],\n",
      "    ...                    \"B\": [\"one\", \"one\", \"one\", \"two\", \"two\",\n",
      "    ...                          \"one\", \"one\", \"two\", \"two\"],\n",
      "    ...                    \"C\": [\"small\", \"large\", \"large\", \"small\",\n",
      "    ...                          \"small\", \"large\", \"small\", \"small\",\n",
      "    ...                          \"large\"],\n",
      "    ...                    \"D\": [1, 2, 2, 3, 3, 4, 5, 6, 7]})\n",
      "    >>> df\n",
      "         A    B      C  D\n",
      "    0  foo  one  small  1\n",
      "    1  foo  one  large  2\n",
      "    2  foo  one  large  2\n",
      "    3  foo  two  small  3\n",
      "    4  foo  two  small  3\n",
      "    5  bar  one  large  4\n",
      "    6  bar  one  small  5\n",
      "    7  bar  two  small  6\n",
      "    8  bar  two  large  7\n",
      "    \n",
      "    >>> table = pivot_table(df, values='D', index=['A', 'B'],\n",
      "    ...                     columns=['C'], aggfunc=np.sum)\n",
      "    >>> table\n",
      "    ... # doctest: +NORMALIZE_WHITESPACE\n",
      "    C        large  small\n",
      "    A   B\n",
      "    bar one    4.0    5.0\n",
      "        two    7.0    6.0\n",
      "    foo one    4.0    1.0\n",
      "        two    NaN    6.0\n",
      "    \n",
      "    Returns\n",
      "    -------\n",
      "    table : DataFrame\n",
      "    \n",
      "    See also\n",
      "    --------\n",
      "    DataFrame.pivot : pivot without aggregation that can handle\n",
      "        non-numeric data\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Read documentation\n",
    "help(pd.DataFrame.pivot_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The general syntax is ```pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', ...)```.\n",
    "* ```data``` is a dataframe\n",
    "* ```values``` contains the column to aggregate\n",
    "* ```index``` is the row in the pivot table\n",
    "* ```columns``` contains the columns you want in the pivot table\n",
    "* ```aggfunc``` is the aggregate function\n",
    "\n",
    "Let's see some examples."
   ]
  },
  {
   "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>Sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customer_Segment</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CONSUMER</th>\n",
       "      <td>1857.859965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CORPORATE</th>\n",
       "      <td>1787.680389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HOME OFFICE</th>\n",
       "      <td>1754.312931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SMALL BUSINESS</th>\n",
       "      <td>1698.124841</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Sales\n",
       "Customer_Segment             \n",
       "CONSUMER          1857.859965\n",
       "CORPORATE         1787.680389\n",
       "HOME OFFICE       1754.312931\n",
       "SMALL BUSINESS    1698.124841"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# E.g. Compare average Sales across customer segments\n",
    "master_df.pivot_table(values = 'Sales', index = 'Customer_Segment', aggfunc = 'mean')\n"
   ]
  },
  {
   "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>is_profitable</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ATLANTIC</th>\n",
       "      <td>544.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NORTHWEST TERRITORIES</th>\n",
       "      <td>194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NUNAVUT</th>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ONTARIO</th>\n",
       "      <td>916.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRARIE</th>\n",
       "      <td>852.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QUEBEC</th>\n",
       "      <td>360.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WEST</th>\n",
       "      <td>969.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YUKON</th>\n",
       "      <td>262.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       is_profitable\n",
       "Region                              \n",
       "ATLANTIC                       544.0\n",
       "NORTHWEST TERRITORIES          194.0\n",
       "NUNAVUT                         38.0\n",
       "ONTARIO                        916.0\n",
       "PRARIE                         852.0\n",
       "QUEBEC                         360.0\n",
       "WEST                           969.0\n",
       "YUKON                          262.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# E.g. compare total number of profitable orders across regions\n",
    "# Note that since is_profitable is 1/0, we can directly compute the sum\n",
    "master_df.pivot_table(values = 'is_profitable', index = 'Region', aggfunc = 'sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Customer_Segment</th>\n",
       "      <th>CONSUMER</th>\n",
       "      <th>CORPORATE</th>\n",
       "      <th>HOME OFFICE</th>\n",
       "      <th>SMALL BUSINESS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Product_Category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FURNITURE</th>\n",
       "      <td>42728.5</td>\n",
       "      <td>22008.3</td>\n",
       "      <td>23978.6</td>\n",
       "      <td>28717.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OFFICE SUPPLIES</th>\n",
       "      <td>88532.4</td>\n",
       "      <td>203038.8</td>\n",
       "      <td>121145.6</td>\n",
       "      <td>105306.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TECHNOLOGY</th>\n",
       "      <td>156700.1</td>\n",
       "      <td>374701.1</td>\n",
       "      <td>173230.6</td>\n",
       "      <td>181684.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Customer_Segment  CONSUMER  CORPORATE  HOME OFFICE  SMALL BUSINESS\n",
       "Product_Category                                                  \n",
       "FURNITURE          42728.5    22008.3      23978.6         28717.5\n",
       "OFFICE SUPPLIES    88532.4   203038.8     121145.6        105306.8\n",
       "TECHNOLOGY        156700.1   374701.1     173230.6        181684.1"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Grouping by both rows and columns\n",
    "# Compare the total profit across product categories and customer segments\n",
    "# Since there are two categorical variables, we use both rows (index) and columns\n",
    "master_df.pivot_table(values = 'Profit', \n",
    "                      index = 'Product_Category', \n",
    "                      columns = 'Customer_Segment', \n",
    "                      aggfunc = 'sum')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You don't necessarily need to specify all four arguments, since ```pivot_table()``` has some smart defaults. For instance, if you just provide ```columns```, it will compute the **mean of all the numeric columns** across each column. For e.g.:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Product_Category</th>\n",
       "      <th>FURNITURE</th>\n",
       "      <th>OFFICE SUPPLIES</th>\n",
       "      <th>TECHNOLOGY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Discount</th>\n",
       "      <td>0.049287</td>\n",
       "      <td>0.050230</td>\n",
       "      <td>0.048746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Order_ID_x</th>\n",
       "      <td>30128.711717</td>\n",
       "      <td>30128.122560</td>\n",
       "      <td>29464.891525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Order_ID_y</th>\n",
       "      <td>30128.711717</td>\n",
       "      <td>30128.122560</td>\n",
       "      <td>29464.891525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Order_Quantity</th>\n",
       "      <td>25.709977</td>\n",
       "      <td>25.656833</td>\n",
       "      <td>25.266344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Product_Base_Margin</th>\n",
       "      <td>0.598555</td>\n",
       "      <td>0.461270</td>\n",
       "      <td>0.556305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Profit</th>\n",
       "      <td>68.116531</td>\n",
       "      <td>112.369544</td>\n",
       "      <td>429.208668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales</th>\n",
       "      <td>3003.822820</td>\n",
       "      <td>814.048178</td>\n",
       "      <td>2897.941008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shipping_Cost</th>\n",
       "      <td>30.883811</td>\n",
       "      <td>7.829829</td>\n",
       "      <td>8.954886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_profitable</th>\n",
       "      <td>0.465197</td>\n",
       "      <td>0.466161</td>\n",
       "      <td>0.573366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>profit_per_qty</th>\n",
       "      <td>-3.607020</td>\n",
       "      <td>1.736175</td>\n",
       "      <td>-52.274216</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Product_Category        FURNITURE  OFFICE SUPPLIES    TECHNOLOGY\n",
       "Discount                 0.049287         0.050230      0.048746\n",
       "Order_ID_x           30128.711717     30128.122560  29464.891525\n",
       "Order_ID_y           30128.711717     30128.122560  29464.891525\n",
       "Order_Quantity          25.709977        25.656833     25.266344\n",
       "Product_Base_Margin      0.598555         0.461270      0.556305\n",
       "Profit                  68.116531       112.369544    429.208668\n",
       "Sales                 3003.822820       814.048178   2897.941008\n",
       "Shipping_Cost           30.883811         7.829829      8.954886\n",
       "is_profitable            0.465197         0.466161      0.573366\n",
       "profit_per_qty          -3.607020         1.736175    -52.274216"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Computes the mean of all numeric columns across categories\n",
    "# Notice that the means of Order_IDs are meaningless\n",
    "master_df.pivot_table(columns = 'Product_Category')"
   ]
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}