AdvancedPythonPandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step by Step advanced Python Pandas features with data cleaning projects\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1. Data Type and Conversions\n",
    "#### 2. Select Columns by data types\n",
    "#### 2. Useful accessor methods for certain data types\n",
    "#### 3. Combining data frames\n",
    "#### 4. Reshaping data frames\n",
    "#### 5. Demo on Data Cleaning Projects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container {width:100% !important;}</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container {width:100% !important;}</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "invoices = pd.read_csv('https://raw.githubusercontent.com/FBosler/you-datascientist/master/invoices.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Order Id              object\n",
       "Date                  object\n",
       "Meal Id               object\n",
       "Company Id            object\n",
       "Date of Meal          object\n",
       "Participants          object\n",
       "Meal Price           float64\n",
       "Type of Meal          object\n",
       "Heroes Adjustment       bool\n",
       "dtype: object"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices.head()\n",
    "invoices.dtypes\n",
    "invoices['Type of Meal'].value_counts()\n",
    "invoices.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Type and Conversions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 7 data types:-\n",
    "- __object__ : This data type is used for strings (i.e., sequences of characters)\n",
    "- __int64__ : Used for integers (whole numbers, no decimals)\n",
    "- __float64__ : Used for floating-point numbers (i.e., figures with decimals/fractions)\n",
    "- __bool__ : Used for values that can only be True/False\n",
    "- __datetime64__ : Used for date and time values\n",
    "- __timedelta__ : Used to represent the difference between datetimes\n",
    "- __category__ : Used for values that take one out of a limited number of available options (categories don’t have to, but can have explicit ordering)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Converting data types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## There are two standard ways of converting pandas data types:\n",
    "\n",
    "- <column> .astype( <desired type>)\n",
    "- conversion helper functions, like pd.to_numeric or pd.to_datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  <column> .astype( <desired type>)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Order Id                     object\n",
       "Date                 datetime64[ns]\n",
       "Meal Id                      object\n",
       "Company Id                   object\n",
       "Date of Meal                 object\n",
       "Participants                 object\n",
       "Meal Price                    int64\n",
       "Type of Meal               category\n",
       "Heroes Adjustment              bool\n",
       "dtype: object"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'] = invoices['Date'].astype('datetime64')\n",
    "invoices['Type of Meal'] = invoices['Type of Meal'].astype('category')\n",
    "invoices['Meal Price'] = invoices['Meal Price'].astype('int64')\n",
    "invoices.dtypes"
   ]
  },
  {
   "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>Type of Meal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Breakfast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dinner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lunch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Dinner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lunch</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Type of Meal\n",
       "0    Breakfast\n",
       "1       Dinner\n",
       "2        Lunch\n",
       "3       Dinner\n",
       "4        Lunch"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices.select_dtypes(include = 'category').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Conversion helper functions, like pd.to_numeric or pd.to_datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There are three pd.to_<some_type> functions, but for me, only two of them come up frequently:\n",
    "\n",
    "- pd.to_numeric()\n",
    "- pd.to_datetime()\n",
    "- pd.to_timedelta()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling using __pd.to_numeric()__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "invoices.loc[45612,'Meal Price'] = 'I am here'\n",
    "invoices.loc[45620,'Meal Price'] = 'Me too'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<class 'int'>    50015\n",
       "<class 'str'>        2\n",
       "Name: Meal Price, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Meal Price'].apply(lambda x:type(x)).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45612    I am here\n",
       "45620       Me too\n",
       "Name: Meal Price, dtype: object"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Meal Price'][invoices['Meal Price'].apply(lambda x:isinstance(x,str))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>45612</th>\n",
       "      <td>SJA1F92KXWZDH398</td>\n",
       "      <td>2017-02-26</td>\n",
       "      <td>OOW0UEXQY5RMPPZ8</td>\n",
       "      <td>ICNGUMLKEB27T1P3</td>\n",
       "      <td>2017-03-02 20:00:00+01:00</td>\n",
       "      <td>['Betty Stroud']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45620</th>\n",
       "      <td>2DKPL6A229CN4WH3</td>\n",
       "      <td>2014-05-22</td>\n",
       "      <td>JH8G8O5LQEUF7U3W</td>\n",
       "      <td>OJKDXPQQXYUOGGJB</td>\n",
       "      <td>2014-05-20 20:00:00+02:00</td>\n",
       "      <td>['Alex Ellis' 'Angela Aguirre']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "45612  SJA1F92KXWZDH398 2017-02-26  OOW0UEXQY5RMPPZ8  ICNGUMLKEB27T1P3   \n",
       "45620  2DKPL6A229CN4WH3 2014-05-22  JH8G8O5LQEUF7U3W  OJKDXPQQXYUOGGJB   \n",
       "\n",
       "                    Date of Meal                     Participants  Meal Price  \\\n",
       "45612  2017-03-02 20:00:00+01:00                 ['Betty Stroud']         NaN   \n",
       "45620  2014-05-20 20:00:00+02:00  ['Alex Ellis' 'Angela Aguirre']         NaN   \n",
       "\n",
       "      Type of Meal  Heroes Adjustment  \n",
       "45612       Dinner              False  \n",
       "45620       Dinner              False  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Meal Price'] = pd.to_numeric(invoices['Meal Price'],errors = 'coerce')\n",
    "invoices1 = invoices[invoices['Meal Price'].isnull()]\n",
    "invoices1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        469\n",
       "1         22\n",
       "2        314\n",
       "3        438\n",
       "4        690\n",
       "        ... \n",
       "50012      9\n",
       "50013     25\n",
       "50014    160\n",
       "50015    497\n",
       "50016    365\n",
       "Name: Meal Price, Length: 50017, dtype: int32"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Meal Price']=invoices['Meal Price'].fillna(invoices['Meal Price'].median())\n",
    "invoices['Meal Price'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling pd.to_datetime(). Check [Link](https://www.programiz.com/python-programming/datetime/strftime#format-code) to know the list of all posible date format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-08-01 00:00:00\n",
      "2019-08-01 00:00:00\n",
      "2019-01-08 00:00:00\n"
     ]
    }
   ],
   "source": [
    "print(pd.to_datetime('2019-8-1'))\n",
    "print(pd.to_datetime('Aug, 1 2019'))\n",
    "print(pd.to_datetime('20190108'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2019-08-01 00:00:00')"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime('20190108',format = '%Y%d%m')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2019-08-01 00:00:00')"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime('yonn 20190108',format = '%Y%d%m',exact = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Useful accessor methods for certain data types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are three different accessors:-\n",
    "\n",
    "- __dt__\n",
    "- __str__\n",
    "- __cat__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert to date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2016-05-27\n",
       "1        2018-09-27\n",
       "2        2014-08-24\n",
       "3        2014-04-12\n",
       "4        2015-07-28\n",
       "            ...    \n",
       "50012    2017-09-20\n",
       "50013    2018-03-19\n",
       "50014    2017-09-21\n",
       "50015    2018-01-28\n",
       "50016    2017-09-06\n",
       "Name: Date, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert to Year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2016\n",
       "1        2018\n",
       "2        2014\n",
       "3        2014\n",
       "4        2015\n",
       "         ... \n",
       "50012    2017\n",
       "50013    2018\n",
       "50014    2017\n",
       "50015    2018\n",
       "50016    2017\n",
       "Name: Date, Length: 50017, dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert to day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        27\n",
       "1        27\n",
       "2        24\n",
       "3        12\n",
       "4        28\n",
       "         ..\n",
       "50012    20\n",
       "50013    19\n",
       "50014    21\n",
       "50015    28\n",
       "50016     6\n",
       "Name: Date, Length: 50017, dtype: int64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert to Weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           Friday\n",
       "1         Thursday\n",
       "2           Sunday\n",
       "3         Saturday\n",
       "4          Tuesday\n",
       "           ...    \n",
       "50012    Wednesday\n",
       "50013       Monday\n",
       "50014     Thursday\n",
       "50015       Sunday\n",
       "50016    Wednesday\n",
       "Name: Date, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.day_name()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert to Month name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0              May\n",
       "1        September\n",
       "2           August\n",
       "3            April\n",
       "4             July\n",
       "           ...    \n",
       "50012    September\n",
       "50013        March\n",
       "50014    September\n",
       "50015      January\n",
       "50016    September\n",
       "Name: Date, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.month_name()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert to Day of the month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        31\n",
       "1        30\n",
       "2        31\n",
       "3        30\n",
       "4        31\n",
       "         ..\n",
       "50012    30\n",
       "50013    31\n",
       "50014    30\n",
       "50015    31\n",
       "50016    30\n",
       "Name: Date, Length: 50017, dtype: int64"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.days_in_month"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- __nanosecond__,__microsecond__,__second__,__minute__,__hour__ ,__day__, __week__, __month__, __quarter__, __year__ gets the integer of the corresponding frequency."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- __is_leap_year__, __is_month_start__, __is_month_end__, __is_quarter_start__, __is_quarter_end__, __is_year_start__, __is_year_end__ returns True or False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>OXX1WA2USIYH69SU</td>\n",
       "      <td>2018-09-01</td>\n",
       "      <td>FX68L7JKDE58ZKS1</td>\n",
       "      <td>PQ79N68UEQ9FFCPU</td>\n",
       "      <td>2018-09-02 14:00:00+02:00</td>\n",
       "      <td>['Monica Espinoza' 'Ann Vitt']</td>\n",
       "      <td>124.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>X546I8JFNVJFE7FH</td>\n",
       "      <td>2017-10-01</td>\n",
       "      <td>G2UDNGRTBYGIS90Z</td>\n",
       "      <td>MR6NETSKD2PSN54L</td>\n",
       "      <td>2017-09-26 07:00:00+02:00</td>\n",
       "      <td>['Martin Riley' 'Jane Bruce' 'Rosa Parramore']</td>\n",
       "      <td>409.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>2UDNOCM41XE38JXR</td>\n",
       "      <td>2014-10-01</td>\n",
       "      <td>4RRVQGEPPDEDYPIZ</td>\n",
       "      <td>MR6NETSKD2PSN54L</td>\n",
       "      <td>2014-10-01 08:00:00+02:00</td>\n",
       "      <td>['Martin Riley' 'Earl Sorrentino']</td>\n",
       "      <td>311.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>SS6TLV5CW0888BC4</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>RZK62CIZ31ZJ5B0K</td>\n",
       "      <td>PYAQ341Y4240ACP4</td>\n",
       "      <td>2015-11-29 12:00:00+01:00</td>\n",
       "      <td>['Ron Pignone']</td>\n",
       "      <td>253.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>ASDP640M48CTNWCV</td>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>0FBMPOWAQ9SQHOIS</td>\n",
       "      <td>4VVOCC6ELGQAOZ7T</td>\n",
       "      <td>2016-02-02 07:00:00+01:00</td>\n",
       "      <td>['Edward Young' 'Clifford Hardman' 'David Leun...</td>\n",
       "      <td>111.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49903</th>\n",
       "      <td>6OGZBDGSOO1BIHC9</td>\n",
       "      <td>2014-09-01</td>\n",
       "      <td>5DO61RK0BX3Y3Y4C</td>\n",
       "      <td>PLGXGQY1FMR95OBC</td>\n",
       "      <td>2014-08-29 14:00:00+02:00</td>\n",
       "      <td>['Theodore Garrison']</td>\n",
       "      <td>101.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49914</th>\n",
       "      <td>8F0VTBD1UBIT8IWL</td>\n",
       "      <td>2017-12-01</td>\n",
       "      <td>2FSMDV5KMJ0USX4H</td>\n",
       "      <td>8YOY0UK6SVPKGWDV</td>\n",
       "      <td>2017-11-28 21:00:00+01:00</td>\n",
       "      <td>['Justin East' 'Hortense Strauss']</td>\n",
       "      <td>610.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49931</th>\n",
       "      <td>P3D63IBCXDVSBMGE</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>CY5W155NLXY6V9CU</td>\n",
       "      <td>DNAC0XNVYCD3J62R</td>\n",
       "      <td>2017-09-05 09:00:00+02:00</td>\n",
       "      <td>['Robyn Price']</td>\n",
       "      <td>459.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49960</th>\n",
       "      <td>HIHM2Y93IZTCLXZZ</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>18JH0E28KFW0BZ7O</td>\n",
       "      <td>41AQ4K1NC4FVCGKJ</td>\n",
       "      <td>2017-01-02 14:00:00+01:00</td>\n",
       "      <td>['Samuel Blum' 'Kenneth Desilva']</td>\n",
       "      <td>593.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49999</th>\n",
       "      <td>JOPTZ9VYV1RWPTW7</td>\n",
       "      <td>2018-02-01</td>\n",
       "      <td>I5EXQPTV5PK8PC3C</td>\n",
       "      <td>5K17HYJQ6JG6UFU0</td>\n",
       "      <td>2018-01-29 13:00:00+01:00</td>\n",
       "      <td>['Arleen Williams']</td>\n",
       "      <td>563.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1752 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "143    OXX1WA2USIYH69SU 2018-09-01  FX68L7JKDE58ZKS1  PQ79N68UEQ9FFCPU   \n",
       "217    X546I8JFNVJFE7FH 2017-10-01  G2UDNGRTBYGIS90Z  MR6NETSKD2PSN54L   \n",
       "223    2UDNOCM41XE38JXR 2014-10-01  4RRVQGEPPDEDYPIZ  MR6NETSKD2PSN54L   \n",
       "239    SS6TLV5CW0888BC4 2015-12-01  RZK62CIZ31ZJ5B0K  PYAQ341Y4240ACP4   \n",
       "265    ASDP640M48CTNWCV 2016-02-01  0FBMPOWAQ9SQHOIS  4VVOCC6ELGQAOZ7T   \n",
       "...                 ...        ...               ...               ...   \n",
       "49903  6OGZBDGSOO1BIHC9 2014-09-01  5DO61RK0BX3Y3Y4C  PLGXGQY1FMR95OBC   \n",
       "49914  8F0VTBD1UBIT8IWL 2017-12-01  2FSMDV5KMJ0USX4H  8YOY0UK6SVPKGWDV   \n",
       "49931  P3D63IBCXDVSBMGE 2017-09-01  CY5W155NLXY6V9CU  DNAC0XNVYCD3J62R   \n",
       "49960  HIHM2Y93IZTCLXZZ 2017-01-01  18JH0E28KFW0BZ7O  41AQ4K1NC4FVCGKJ   \n",
       "49999  JOPTZ9VYV1RWPTW7 2018-02-01  I5EXQPTV5PK8PC3C  5K17HYJQ6JG6UFU0   \n",
       "\n",
       "                    Date of Meal  \\\n",
       "143    2018-09-02 14:00:00+02:00   \n",
       "217    2017-09-26 07:00:00+02:00   \n",
       "223    2014-10-01 08:00:00+02:00   \n",
       "239    2015-11-29 12:00:00+01:00   \n",
       "265    2016-02-02 07:00:00+01:00   \n",
       "...                          ...   \n",
       "49903  2014-08-29 14:00:00+02:00   \n",
       "49914  2017-11-28 21:00:00+01:00   \n",
       "49931  2017-09-05 09:00:00+02:00   \n",
       "49960  2017-01-02 14:00:00+01:00   \n",
       "49999  2018-01-29 13:00:00+01:00   \n",
       "\n",
       "                                            Participants  Meal Price  \\\n",
       "143                       ['Monica Espinoza' 'Ann Vitt']       124.0   \n",
       "217       ['Martin Riley' 'Jane Bruce' 'Rosa Parramore']       409.0   \n",
       "223                   ['Martin Riley' 'Earl Sorrentino']       311.0   \n",
       "239                                      ['Ron Pignone']       253.0   \n",
       "265    ['Edward Young' 'Clifford Hardman' 'David Leun...       111.0   \n",
       "...                                                  ...         ...   \n",
       "49903                              ['Theodore Garrison']       101.0   \n",
       "49914                 ['Justin East' 'Hortense Strauss']       610.0   \n",
       "49931                                    ['Robyn Price']       459.0   \n",
       "49960                  ['Samuel Blum' 'Kenneth Desilva']       593.0   \n",
       "49999                                ['Arleen Williams']       563.0   \n",
       "\n",
       "      Type of Meal  Heroes Adjustment  \n",
       "143          Lunch              False  \n",
       "217      Breakfast              False  \n",
       "223      Breakfast              False  \n",
       "239          Lunch              False  \n",
       "265      Breakfast              False  \n",
       "...            ...                ...  \n",
       "49903        Lunch              False  \n",
       "49914       Dinner              False  \n",
       "49931    Breakfast              False  \n",
       "49960        Lunch              False  \n",
       "49999        Lunch              False  \n",
       "\n",
       "[1752 rows x 9 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices[invoices['Date'].dt.is_month_start]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([datetime.datetime(2016, 5, 27, 0, 0),\n",
       "       datetime.datetime(2018, 9, 27, 0, 0),\n",
       "       datetime.datetime(2014, 8, 24, 0, 0), ...,\n",
       "       datetime.datetime(2017, 9, 21, 0, 0),\n",
       "       datetime.datetime(2018, 1, 28, 0, 0),\n",
       "       datetime.datetime(2017, 9, 6, 0, 0)], dtype=object)"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.to_pydatetime()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Available periods are W, M, Q, and Y, which converts the dates into periods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2016Q2\n",
       "1        2018Q3\n",
       "2        2014Q3\n",
       "3        2014Q2\n",
       "4        2015Q3\n",
       "          ...  \n",
       "50012    2017Q3\n",
       "50013    2018Q1\n",
       "50014    2017Q3\n",
       "50015    2018Q1\n",
       "50016    2017Q3\n",
       "Name: Date, Length: 50017, dtype: period[Q-DEC]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.to_period('Q')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessor — str"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### lower() / upper()/ title () to manage capitalization of strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        Breakfast\n",
       "1           Dinner\n",
       "2            Lunch\n",
       "3           Dinner\n",
       "4            Lunch\n",
       "           ...    \n",
       "50012    Breakfast\n",
       "50013    Breakfast\n",
       "50014       Dinner\n",
       "50015       Dinner\n",
       "50016        Lunch\n",
       "Name: Type of Meal, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].str.title()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        Breakfast      \n",
       "1        Dinner         \n",
       "2        Lunch          \n",
       "3        Dinner         \n",
       "4        Lunch          \n",
       "              ...       \n",
       "50012    Breakfast      \n",
       "50013    Breakfast      \n",
       "50014    Dinner         \n",
       "50015    Dinner         \n",
       "50016    Lunch          \n",
       "Name: Type of Meal, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].str.ljust(width = 15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0              Breakfast\n",
       "1                 Dinner\n",
       "2                  Lunch\n",
       "3                 Dinner\n",
       "4                  Lunch\n",
       "              ...       \n",
       "50012          Breakfast\n",
       "50013          Breakfast\n",
       "50014             Dinner\n",
       "50015             Dinner\n",
       "50016              Lunch\n",
       "Name: Type of Meal, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].str.rjust(width = 15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           Breakfast   \n",
       "1             Dinner    \n",
       "2             Lunch     \n",
       "3             Dinner    \n",
       "4             Lunch     \n",
       "              ...       \n",
       "50012       Breakfast   \n",
       "50013       Breakfast   \n",
       "50014         Dinner    \n",
       "50015         Dinner    \n",
       "50016         Lunch     \n",
       "Name: Type of Meal, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].str.center(width = 15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        000000Breakfast\n",
       "1        000000000Dinner\n",
       "2        0000000000Lunch\n",
       "3        000000000Dinner\n",
       "4        0000000000Lunch\n",
       "              ...       \n",
       "50012    000000Breakfast\n",
       "50013    000000Breakfast\n",
       "50014    000000000Dinner\n",
       "50015    000000000Dinner\n",
       "50016    0000000000Lunch\n",
       "Name: Type of Meal, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].str.zfill(width = 15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### startswith(<substring>), endswith(<substring>), contains(<substring>) checks for the presence of a substring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>INBUX904GIHI8YBD</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2016-05-31 07:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>469.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FM608JK1N01BPUQN</td>\n",
       "      <td>2014-05-08</td>\n",
       "      <td>E8WJZ1FOSKZD2MJN</td>\n",
       "      <td>36MFTZOYMTAJP1RK</td>\n",
       "      <td>2014-05-07 09:00:00+02:00</td>\n",
       "      <td>['Amanda Knowles' 'Cheryl Feaster' 'Ginger Hoa...</td>\n",
       "      <td>320.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>8RIGCF74GUEQHQEE</td>\n",
       "      <td>2018-07-23</td>\n",
       "      <td>5XK0KTFTD6OAP9ZP</td>\n",
       "      <td>36MFTZOYMTAJP1RK</td>\n",
       "      <td>2018-07-27 08:00:00+02:00</td>\n",
       "      <td>['Amanda Knowles']</td>\n",
       "      <td>210.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>TH60C9D8TPYS7DGG</td>\n",
       "      <td>2016-12-15</td>\n",
       "      <td>KDSMP2VJ22HNEPYF</td>\n",
       "      <td>36MFTZOYMTAJP1RK</td>\n",
       "      <td>2016-12-13 08:00:00+01:00</td>\n",
       "      <td>['Cheryl Feaster' 'Bret Adams' 'Ginger Hoagland']</td>\n",
       "      <td>755.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>KNF3E3QTOQ22J269</td>\n",
       "      <td>2018-06-20</td>\n",
       "      <td>737T2U7604ABDFDF</td>\n",
       "      <td>36MFTZOYMTAJP1RK</td>\n",
       "      <td>2018-06-15 07:00:00+02:00</td>\n",
       "      <td>['Glenn Gould' 'Cheryl Feaster' 'Ginger Hoagla...</td>\n",
       "      <td>475.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50008</th>\n",
       "      <td>UHIE76EGIC2KFP34</td>\n",
       "      <td>2016-08-06</td>\n",
       "      <td>BFLY4LHHUI5OIREM</td>\n",
       "      <td>FC1LJXHNH2883N3Y</td>\n",
       "      <td>2016-08-08 08:00:00+02:00</td>\n",
       "      <td>['Jerry Nelson']</td>\n",
       "      <td>164.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50009</th>\n",
       "      <td>FWH3N8N5ARW53BE6</td>\n",
       "      <td>2016-03-17</td>\n",
       "      <td>WHVI4J3J388I8I4B</td>\n",
       "      <td>FC1LJXHNH2883N3Y</td>\n",
       "      <td>2016-03-12 09:00:00+01:00</td>\n",
       "      <td>['Jon Ponce' 'Alice Allen']</td>\n",
       "      <td>523.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50011</th>\n",
       "      <td>81J2RM0YM63VAT0K</td>\n",
       "      <td>2018-06-03</td>\n",
       "      <td>HNXSJZQ120WGAWZP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-06-03 07:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>598.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50012</th>\n",
       "      <td>4OMS8ZSA0UX8LHWI</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>1TD5MROATV1NHZ4Y</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-21 08:00:00+02:00</td>\n",
       "      <td>['Regina Shirley']</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50013</th>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>25.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16663 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "0      839FKFW2LLX4LMBB 2016-05-27  INBUX904GIHI8YBD  LJKS5NK6788CYMUU   \n",
       "7      FM608JK1N01BPUQN 2014-05-08  E8WJZ1FOSKZD2MJN  36MFTZOYMTAJP1RK   \n",
       "11     8RIGCF74GUEQHQEE 2018-07-23  5XK0KTFTD6OAP9ZP  36MFTZOYMTAJP1RK   \n",
       "12     TH60C9D8TPYS7DGG 2016-12-15  KDSMP2VJ22HNEPYF  36MFTZOYMTAJP1RK   \n",
       "17     KNF3E3QTOQ22J269 2018-06-20  737T2U7604ABDFDF  36MFTZOYMTAJP1RK   \n",
       "...                 ...        ...               ...               ...   \n",
       "50008  UHIE76EGIC2KFP34 2016-08-06  BFLY4LHHUI5OIREM  FC1LJXHNH2883N3Y   \n",
       "50009  FWH3N8N5ARW53BE6 2016-03-17  WHVI4J3J388I8I4B  FC1LJXHNH2883N3Y   \n",
       "50011  81J2RM0YM63VAT0K 2018-06-03  HNXSJZQ120WGAWZP  E4K99D4JR9E40VE1   \n",
       "50012  4OMS8ZSA0UX8LHWI 2017-09-20  1TD5MROATV1NHZ4Y  E4K99D4JR9E40VE1   \n",
       "50013  RR0VKJN8V0KHNKGG 2018-03-19  22EX9VZSJKHP4AIP  E4K99D4JR9E40VE1   \n",
       "\n",
       "                    Date of Meal  \\\n",
       "0      2016-05-31 07:00:00+02:00   \n",
       "7      2014-05-07 09:00:00+02:00   \n",
       "11     2018-07-27 08:00:00+02:00   \n",
       "12     2016-12-13 08:00:00+01:00   \n",
       "17     2018-06-15 07:00:00+02:00   \n",
       "...                          ...   \n",
       "50008  2016-08-08 08:00:00+02:00   \n",
       "50009  2016-03-12 09:00:00+01:00   \n",
       "50011  2018-06-03 07:00:00+02:00   \n",
       "50012  2017-09-21 08:00:00+02:00   \n",
       "50013  2018-03-18 09:00:00+01:00   \n",
       "\n",
       "                                            Participants  Meal Price  \\\n",
       "0                                       ['David Bishop']       469.0   \n",
       "7      ['Amanda Knowles' 'Cheryl Feaster' 'Ginger Hoa...       320.0   \n",
       "11                                    ['Amanda Knowles']       210.0   \n",
       "12     ['Cheryl Feaster' 'Bret Adams' 'Ginger Hoagland']       755.0   \n",
       "17     ['Glenn Gould' 'Cheryl Feaster' 'Ginger Hoagla...       475.0   \n",
       "...                                                  ...         ...   \n",
       "50008                                   ['Jerry Nelson']       164.0   \n",
       "50009                        ['Jon Ponce' 'Alice Allen']       523.0   \n",
       "50011                 ['Chester Mortimer' 'Robin Ramos']       598.0   \n",
       "50012                                 ['Regina Shirley']         9.0   \n",
       "50013                 ['Robin Ramos' 'Chester Mortimer']        25.0   \n",
       "\n",
       "      Type of Meal  Heroes Adjustment  \n",
       "0        Breakfast              False  \n",
       "7        Breakfast              False  \n",
       "11       Breakfast              False  \n",
       "12       Breakfast              False  \n",
       "17       Breakfast              False  \n",
       "...            ...                ...  \n",
       "50008    Breakfast              False  \n",
       "50009    Breakfast              False  \n",
       "50011    Breakfast              False  \n",
       "50012    Breakfast              False  \n",
       "50013    Breakfast              False  \n",
       "\n",
       "[16663 rows x 9 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices[invoices['Type of Meal'].str.endswith('ast')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### swapcase(), repeat(times) for kicks and giggles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        bREAKFAST\n",
       "1           dINNER\n",
       "2            lUNCH\n",
       "3           dINNER\n",
       "4            lUNCH\n",
       "           ...    \n",
       "50012    bREAKFAST\n",
       "50013    bREAKFAST\n",
       "50014       dINNER\n",
       "50015       dINNER\n",
       "50016        lUNCH\n",
       "Name: Type of Meal, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].str.swapcase()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        BreakfastBreakfast\n",
       "1              DinnerDinner\n",
       "2                LunchLunch\n",
       "3              DinnerDinner\n",
       "4                LunchLunch\n",
       "                ...        \n",
       "50012    BreakfastBreakfast\n",
       "50013    BreakfastBreakfast\n",
       "50014          DinnerDinner\n",
       "50015          DinnerDinner\n",
       "50016            LunchLunch\n",
       "Name: Type of Meal, Length: 50017, dtype: object"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].str.repeat(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessor — cat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Breakfast', 'Dinner', 'Lunch'], dtype='object')"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].cat.categories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Codes for quick conversion of the category into its numerical representation\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        0\n",
       "1        1\n",
       "2        2\n",
       "3        1\n",
       "4        2\n",
       "        ..\n",
       "50012    0\n",
       "50013    0\n",
       "50014    1\n",
       "50015    1\n",
       "50016    2\n",
       "Length: 50017, dtype: int8"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].cat.codes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### reorder_categories to change the existing order of the categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        Breakfast\n",
       "1           Dinner\n",
       "2            Lunch\n",
       "3           Dinner\n",
       "4            Lunch\n",
       "           ...    \n",
       "50012    Breakfast\n",
       "50013    Breakfast\n",
       "50014       Dinner\n",
       "50015       Dinner\n",
       "50016        Lunch\n",
       "Name: Type of Meal, Length: 50017, dtype: category\n",
       "Categories (3, object): [Lunch, Breakfast, Dinner]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Type of Meal'].cat.reorder_categories(\n",
    "    ['Lunch','Breakfast','Dinner']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining DataFrames (Vertical Stacking) <img src=\"files/VS.png\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2014    10069\n",
       "2015    10039\n",
       "2016     9857\n",
       "2017     9937\n",
       "2018    10115\n",
       "Name: Date, dtype: int64"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices['Date'].dt.year.value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "y_2014 = invoices[invoices['Date'].dt.year == 2014].copy()\n",
    "y_2015 = invoices[invoices['Date'].dt.year == 2015].copy()\n",
    "y_2016 = invoices[invoices['Date'].dt.year == 2016].copy()\n",
    "y_2017 = invoices[invoices['Date'].dt.year == 2017].copy()\n",
    "y_2018 = invoices[invoices['Date'].dt.year == 2018].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>314.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>438.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AT0R4DFYYAFOC88Q</td>\n",
       "      <td>2014-07-21</td>\n",
       "      <td>W48JPR1UYWJ18NC6</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-07-17 20:00:00+02:00</td>\n",
       "      <td>['David Bishop' 'Susan Guerrero' 'Karen Stanse...</td>\n",
       "      <td>181.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2DDN2LHS7G85GKPQ</td>\n",
       "      <td>2014-04-29</td>\n",
       "      <td>1MKLAKBOE3SP7YUL</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-30 21:00:00+02:00</td>\n",
       "      <td>['Susan Guerrero' 'David Bishop']</td>\n",
       "      <td>14.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FM608JK1N01BPUQN</td>\n",
       "      <td>2014-05-08</td>\n",
       "      <td>E8WJZ1FOSKZD2MJN</td>\n",
       "      <td>36MFTZOYMTAJP1RK</td>\n",
       "      <td>2014-05-07 09:00:00+02:00</td>\n",
       "      <td>['Amanda Knowles' 'Cheryl Feaster' 'Ginger Hoa...</td>\n",
       "      <td>320.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49991</th>\n",
       "      <td>CGPOTR0XW6FDEQQ1</td>\n",
       "      <td>2018-12-24</td>\n",
       "      <td>8K07A0E1NNRYG9OG</td>\n",
       "      <td>5K17HYJQ6JG6UFU0</td>\n",
       "      <td>2018-12-24 07:00:00+01:00</td>\n",
       "      <td>['Michelle Lachiatto']</td>\n",
       "      <td>640.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49999</th>\n",
       "      <td>JOPTZ9VYV1RWPTW7</td>\n",
       "      <td>2018-02-01</td>\n",
       "      <td>I5EXQPTV5PK8PC3C</td>\n",
       "      <td>5K17HYJQ6JG6UFU0</td>\n",
       "      <td>2018-01-29 13:00:00+01:00</td>\n",
       "      <td>['Arleen Williams']</td>\n",
       "      <td>563.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50011</th>\n",
       "      <td>81J2RM0YM63VAT0K</td>\n",
       "      <td>2018-06-03</td>\n",
       "      <td>HNXSJZQ120WGAWZP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-06-03 07:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>598.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50013</th>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>25.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50015</th>\n",
       "      <td>QHEUIYNC0XQX7GDR</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>4U0VH2TGQL30X23X</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-02-01 21:00:00+01:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>497.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50017 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "2      041ORQM5OIHTIU6L 2014-08-24  E4UJLQNCI16UX5CS  LJKS5NK6788CYMUU   \n",
       "3      YT796QI18WNGZ7ZJ 2014-04-12  C9SDFHF7553BE247  LJKS5NK6788CYMUU   \n",
       "5      AT0R4DFYYAFOC88Q 2014-07-21  W48JPR1UYWJ18NC6  LJKS5NK6788CYMUU   \n",
       "6      2DDN2LHS7G85GKPQ 2014-04-29  1MKLAKBOE3SP7YUL  LJKS5NK6788CYMUU   \n",
       "7      FM608JK1N01BPUQN 2014-05-08  E8WJZ1FOSKZD2MJN  36MFTZOYMTAJP1RK   \n",
       "...                 ...        ...               ...               ...   \n",
       "49991  CGPOTR0XW6FDEQQ1 2018-12-24  8K07A0E1NNRYG9OG  5K17HYJQ6JG6UFU0   \n",
       "49999  JOPTZ9VYV1RWPTW7 2018-02-01  I5EXQPTV5PK8PC3C  5K17HYJQ6JG6UFU0   \n",
       "50011  81J2RM0YM63VAT0K 2018-06-03  HNXSJZQ120WGAWZP  E4K99D4JR9E40VE1   \n",
       "50013  RR0VKJN8V0KHNKGG 2018-03-19  22EX9VZSJKHP4AIP  E4K99D4JR9E40VE1   \n",
       "50015  QHEUIYNC0XQX7GDR 2018-01-28  4U0VH2TGQL30X23X  E4K99D4JR9E40VE1   \n",
       "\n",
       "                    Date of Meal  \\\n",
       "2      2014-08-23 14:00:00+02:00   \n",
       "3      2014-04-07 21:00:00+02:00   \n",
       "5      2014-07-17 20:00:00+02:00   \n",
       "6      2014-04-30 21:00:00+02:00   \n",
       "7      2014-05-07 09:00:00+02:00   \n",
       "...                          ...   \n",
       "49991  2018-12-24 07:00:00+01:00   \n",
       "49999  2018-01-29 13:00:00+01:00   \n",
       "50011  2018-06-03 07:00:00+02:00   \n",
       "50013  2018-03-18 09:00:00+01:00   \n",
       "50015  2018-02-01 21:00:00+01:00   \n",
       "\n",
       "                                            Participants  Meal Price  \\\n",
       "2                                     ['Karen Stansell']       314.0   \n",
       "3                                       ['Addie Patino']       438.0   \n",
       "5      ['David Bishop' 'Susan Guerrero' 'Karen Stanse...       181.0   \n",
       "6                      ['Susan Guerrero' 'David Bishop']        14.0   \n",
       "7      ['Amanda Knowles' 'Cheryl Feaster' 'Ginger Hoa...       320.0   \n",
       "...                                                  ...         ...   \n",
       "49991                             ['Michelle Lachiatto']       640.0   \n",
       "49999                                ['Arleen Williams']       563.0   \n",
       "50011                 ['Chester Mortimer' 'Robin Ramos']       598.0   \n",
       "50013                 ['Robin Ramos' 'Chester Mortimer']        25.0   \n",
       "50015                 ['Chester Mortimer' 'Robin Ramos']       497.0   \n",
       "\n",
       "      Type of Meal  Heroes Adjustment  \n",
       "2            Lunch              False  \n",
       "3           Dinner              False  \n",
       "5           Dinner              False  \n",
       "6           Dinner              False  \n",
       "7        Breakfast              False  \n",
       "...            ...                ...  \n",
       "49991    Breakfast              False  \n",
       "49999        Lunch              False  \n",
       "50011    Breakfast              False  \n",
       "50013    Breakfast              False  \n",
       "50015       Dinner              False  \n",
       "\n",
       "[50017 rows x 9 columns]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([y_2014,y_2015,y_2016,y_2017,y_2018])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th>origninal index</th>\n",
       "      <th></th>\n",
       "      <th></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 rowspan=\"5\" valign=\"top\">14</th>\n",
       "      <th>2</th>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>314.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>438.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AT0R4DFYYAFOC88Q</td>\n",
       "      <td>2014-07-21</td>\n",
       "      <td>W48JPR1UYWJ18NC6</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-07-17 20:00:00+02:00</td>\n",
       "      <td>['David Bishop' 'Susan Guerrero' 'Karen Stanse...</td>\n",
       "      <td>181.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2DDN2LHS7G85GKPQ</td>\n",
       "      <td>2014-04-29</td>\n",
       "      <td>1MKLAKBOE3SP7YUL</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-30 21:00:00+02:00</td>\n",
       "      <td>['Susan Guerrero' 'David Bishop']</td>\n",
       "      <td>14.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FM608JK1N01BPUQN</td>\n",
       "      <td>2014-05-08</td>\n",
       "      <td>E8WJZ1FOSKZD2MJN</td>\n",
       "      <td>36MFTZOYMTAJP1RK</td>\n",
       "      <td>2014-05-07 09:00:00+02:00</td>\n",
       "      <td>['Amanda Knowles' 'Cheryl Feaster' 'Ginger Hoa...</td>\n",
       "      <td>320.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">18</th>\n",
       "      <th>49991</th>\n",
       "      <td>CGPOTR0XW6FDEQQ1</td>\n",
       "      <td>2018-12-24</td>\n",
       "      <td>8K07A0E1NNRYG9OG</td>\n",
       "      <td>5K17HYJQ6JG6UFU0</td>\n",
       "      <td>2018-12-24 07:00:00+01:00</td>\n",
       "      <td>['Michelle Lachiatto']</td>\n",
       "      <td>640.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49999</th>\n",
       "      <td>JOPTZ9VYV1RWPTW7</td>\n",
       "      <td>2018-02-01</td>\n",
       "      <td>I5EXQPTV5PK8PC3C</td>\n",
       "      <td>5K17HYJQ6JG6UFU0</td>\n",
       "      <td>2018-01-29 13:00:00+01:00</td>\n",
       "      <td>['Arleen Williams']</td>\n",
       "      <td>563.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50011</th>\n",
       "      <td>81J2RM0YM63VAT0K</td>\n",
       "      <td>2018-06-03</td>\n",
       "      <td>HNXSJZQ120WGAWZP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-06-03 07:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>598.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50013</th>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>25.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50015</th>\n",
       "      <td>QHEUIYNC0XQX7GDR</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>4U0VH2TGQL30X23X</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-02-01 21:00:00+01:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>497.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50017 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Order Id       Date           Meal Id  \\\n",
       "year origninal index                                                  \n",
       "14   2                041ORQM5OIHTIU6L 2014-08-24  E4UJLQNCI16UX5CS   \n",
       "     3                YT796QI18WNGZ7ZJ 2014-04-12  C9SDFHF7553BE247   \n",
       "     5                AT0R4DFYYAFOC88Q 2014-07-21  W48JPR1UYWJ18NC6   \n",
       "     6                2DDN2LHS7G85GKPQ 2014-04-29  1MKLAKBOE3SP7YUL   \n",
       "     7                FM608JK1N01BPUQN 2014-05-08  E8WJZ1FOSKZD2MJN   \n",
       "...                                ...        ...               ...   \n",
       "18   49991            CGPOTR0XW6FDEQQ1 2018-12-24  8K07A0E1NNRYG9OG   \n",
       "     49999            JOPTZ9VYV1RWPTW7 2018-02-01  I5EXQPTV5PK8PC3C   \n",
       "     50011            81J2RM0YM63VAT0K 2018-06-03  HNXSJZQ120WGAWZP   \n",
       "     50013            RR0VKJN8V0KHNKGG 2018-03-19  22EX9VZSJKHP4AIP   \n",
       "     50015            QHEUIYNC0XQX7GDR 2018-01-28  4U0VH2TGQL30X23X   \n",
       "\n",
       "                            Company Id               Date of Meal  \\\n",
       "year origninal index                                                \n",
       "14   2                LJKS5NK6788CYMUU  2014-08-23 14:00:00+02:00   \n",
       "     3                LJKS5NK6788CYMUU  2014-04-07 21:00:00+02:00   \n",
       "     5                LJKS5NK6788CYMUU  2014-07-17 20:00:00+02:00   \n",
       "     6                LJKS5NK6788CYMUU  2014-04-30 21:00:00+02:00   \n",
       "     7                36MFTZOYMTAJP1RK  2014-05-07 09:00:00+02:00   \n",
       "...                                ...                        ...   \n",
       "18   49991            5K17HYJQ6JG6UFU0  2018-12-24 07:00:00+01:00   \n",
       "     49999            5K17HYJQ6JG6UFU0  2018-01-29 13:00:00+01:00   \n",
       "     50011            E4K99D4JR9E40VE1  2018-06-03 07:00:00+02:00   \n",
       "     50013            E4K99D4JR9E40VE1  2018-03-18 09:00:00+01:00   \n",
       "     50015            E4K99D4JR9E40VE1  2018-02-01 21:00:00+01:00   \n",
       "\n",
       "                                                           Participants  \\\n",
       "year origninal index                                                      \n",
       "14   2                                               ['Karen Stansell']   \n",
       "     3                                                 ['Addie Patino']   \n",
       "     5                ['David Bishop' 'Susan Guerrero' 'Karen Stanse...   \n",
       "     6                                ['Susan Guerrero' 'David Bishop']   \n",
       "     7                ['Amanda Knowles' 'Cheryl Feaster' 'Ginger Hoa...   \n",
       "...                                                                 ...   \n",
       "18   49991                                       ['Michelle Lachiatto']   \n",
       "     49999                                          ['Arleen Williams']   \n",
       "     50011                           ['Chester Mortimer' 'Robin Ramos']   \n",
       "     50013                           ['Robin Ramos' 'Chester Mortimer']   \n",
       "     50015                           ['Chester Mortimer' 'Robin Ramos']   \n",
       "\n",
       "                      Meal Price Type of Meal  Heroes Adjustment  \n",
       "year origninal index                                              \n",
       "14   2                     314.0        Lunch              False  \n",
       "     3                     438.0       Dinner              False  \n",
       "     5                     181.0       Dinner              False  \n",
       "     6                      14.0       Dinner              False  \n",
       "     7                     320.0    Breakfast              False  \n",
       "...                          ...          ...                ...  \n",
       "18   49991                 640.0    Breakfast              False  \n",
       "     49999                 563.0        Lunch              False  \n",
       "     50011                 598.0    Breakfast              False  \n",
       "     50013                  25.0    Breakfast              False  \n",
       "     50015                 497.0       Dinner              False  \n",
       "\n",
       "[50017 rows x 9 columns]"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([y_2014,y_2015,y_2016,y_2017,y_2018],keys = [14,15,16,17,18],names = ['year','origninal index'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Horizontal Stacking  <img src=\"files/HS.png\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "      <th>Order Id</th>\n",
       "      <th>...</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "      <th>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>J0MMOOPP709DIDIE</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2018-10-01 20:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>314.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>438.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6YLROQT27B6HRF4E</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50012</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50013</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>25.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50014</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50015</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QHEUIYNC0XQX7GDR</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>4U0VH2TGQL30X23X</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-02-01 21:00:00+01:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>497.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50016</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50017 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "0                   NaN        NaT               NaN               NaN   \n",
       "1                   NaN        NaT               NaN               NaN   \n",
       "2      041ORQM5OIHTIU6L 2014-08-24  E4UJLQNCI16UX5CS  LJKS5NK6788CYMUU   \n",
       "3      YT796QI18WNGZ7ZJ 2014-04-12  C9SDFHF7553BE247  LJKS5NK6788CYMUU   \n",
       "4                   NaN        NaT               NaN               NaN   \n",
       "...                 ...        ...               ...               ...   \n",
       "50012               NaN        NaT               NaN               NaN   \n",
       "50013               NaN        NaT               NaN               NaN   \n",
       "50014               NaN        NaT               NaN               NaN   \n",
       "50015               NaN        NaT               NaN               NaN   \n",
       "50016               NaN        NaT               NaN               NaN   \n",
       "\n",
       "                    Date of Meal        Participants Meal Price Type of Meal  \\\n",
       "0                            NaN                 NaN        NaN          NaN   \n",
       "1                            NaN                 NaN        NaN          NaN   \n",
       "2      2014-08-23 14:00:00+02:00  ['Karen Stansell']      314.0        Lunch   \n",
       "3      2014-04-07 21:00:00+02:00    ['Addie Patino']      438.0       Dinner   \n",
       "4                            NaN                 NaN        NaN          NaN   \n",
       "...                          ...                 ...        ...          ...   \n",
       "50012                        NaN                 NaN        NaN          NaN   \n",
       "50013                        NaN                 NaN        NaN          NaN   \n",
       "50014                        NaN                 NaN        NaN          NaN   \n",
       "50015                        NaN                 NaN        NaN          NaN   \n",
       "50016                        NaN                 NaN        NaN          NaN   \n",
       "\n",
       "      Heroes Adjustment          Order Id  ... Heroes Adjustment  \\\n",
       "0                   NaN               NaN  ...               NaN   \n",
       "1                   NaN               NaN  ...               NaN   \n",
       "2                 False               NaN  ...               NaN   \n",
       "3                 False               NaN  ...               NaN   \n",
       "4                   NaN  6YLROQT27B6HRF4E  ...               NaN   \n",
       "...                 ...               ...  ...               ...   \n",
       "50012               NaN               NaN  ...             False   \n",
       "50013               NaN               NaN  ...               NaN   \n",
       "50014               NaN               NaN  ...             False   \n",
       "50015               NaN               NaN  ...               NaN   \n",
       "50016               NaN               NaN  ...             False   \n",
       "\n",
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "0                   NaN        NaT               NaN               NaN   \n",
       "1      97OX39BGVMHODLJM 2018-09-27  J0MMOOPP709DIDIE  LJKS5NK6788CYMUU   \n",
       "2                   NaN        NaT               NaN               NaN   \n",
       "3                   NaN        NaT               NaN               NaN   \n",
       "4                   NaN        NaT               NaN               NaN   \n",
       "...                 ...        ...               ...               ...   \n",
       "50012               NaN        NaT               NaN               NaN   \n",
       "50013  RR0VKJN8V0KHNKGG 2018-03-19  22EX9VZSJKHP4AIP  E4K99D4JR9E40VE1   \n",
       "50014               NaN        NaT               NaN               NaN   \n",
       "50015  QHEUIYNC0XQX7GDR 2018-01-28  4U0VH2TGQL30X23X  E4K99D4JR9E40VE1   \n",
       "50016               NaN        NaT               NaN               NaN   \n",
       "\n",
       "                    Date of Meal                        Participants  \\\n",
       "0                            NaN                                 NaN   \n",
       "1      2018-10-01 20:00:00+02:00                    ['David Bishop']   \n",
       "2                            NaN                                 NaN   \n",
       "3                            NaN                                 NaN   \n",
       "4                            NaN                                 NaN   \n",
       "...                          ...                                 ...   \n",
       "50012                        NaN                                 NaN   \n",
       "50013  2018-03-18 09:00:00+01:00  ['Robin Ramos' 'Chester Mortimer']   \n",
       "50014                        NaN                                 NaN   \n",
       "50015  2018-02-01 21:00:00+01:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "50016                        NaN                                 NaN   \n",
       "\n",
       "      Meal Price Type of Meal Heroes Adjustment  \n",
       "0            NaN          NaN               NaN  \n",
       "1           22.0       Dinner             False  \n",
       "2            NaN          NaN               NaN  \n",
       "3            NaN          NaN               NaN  \n",
       "4            NaN          NaN               NaN  \n",
       "...          ...          ...               ...  \n",
       "50012        NaN          NaN               NaN  \n",
       "50013       25.0    Breakfast             False  \n",
       "50014        NaN          NaN               NaN  \n",
       "50015      497.0       Dinner             False  \n",
       "50016        NaN          NaN               NaN  \n",
       "\n",
       "[50017 rows x 45 columns]"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([y_2014,y_2015,y_2016,y_2017,y_2018],axis = 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging — Load and Inspect new data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load some additional Data\n",
    "order_data = pd.read_csv('https://raw.githubusercontent.com/FBosler/you-datascientist/master/order_leads.csv', parse_dates=[3])\n",
    "# Note the parse_dates? We need this to have consistent types with invoices, otherwise the subsequent merges would throw errors\n",
    "sales_team = pd.read_csv('https://raw.githubusercontent.com/FBosler/you-datascientist/master/sales_team.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "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>Order Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>Order Value</th>\n",
       "      <th>Converted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80EYLOKP9E762WKG</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>4875</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TLEXR1HZWTUTBHPB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2015-07-30</td>\n",
       "      <td>8425</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>4837</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>343</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5T4LGH4XGBWOD49Z</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-01-14</td>\n",
       "      <td>983</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Order Id        Company Id            Company Name       Date  \\\n",
       "0  80EYLOKP9E762WKG  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2017-02-18   \n",
       "1  TLEXR1HZWTUTBHPB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2015-07-30   \n",
       "2  839FKFW2LLX4LMBB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-05-27   \n",
       "3  97OX39BGVMHODLJM  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2018-09-27   \n",
       "4  5T4LGH4XGBWOD49Z  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-01-14   \n",
       "\n",
       "   Order Value  Converted  \n",
       "0         4875          1  \n",
       "1         8425          0  \n",
       "2         4837          0  \n",
       "3          343          0  \n",
       "4          983          0  "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_team.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>INBUX904GIHI8YBD</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2016-05-31 07:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>469.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>J0MMOOPP709DIDIE</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2018-10-01 20:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>314.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>438.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6YLROQT27B6HRF4E</td>\n",
       "      <td>2015-07-28</td>\n",
       "      <td>48EQXS6IHYNZDDZ5</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2015-07-27 14:00:00+02:00</td>\n",
       "      <td>['Addie Patino' 'Susan Guerrero']</td>\n",
       "      <td>690.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50012</th>\n",
       "      <td>4OMS8ZSA0UX8LHWI</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>1TD5MROATV1NHZ4Y</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-21 08:00:00+02:00</td>\n",
       "      <td>['Regina Shirley']</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50013</th>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>25.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50014</th>\n",
       "      <td>STJ6QJC30WPRM93H</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>LMX18PNGWCIMG1QW</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-22 21:00:00+02:00</td>\n",
       "      <td>['Robin Ramos']</td>\n",
       "      <td>160.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50015</th>\n",
       "      <td>QHEUIYNC0XQX7GDR</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>4U0VH2TGQL30X23X</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-02-01 21:00:00+01:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>497.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50016</th>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>ORWFRT5TUSYGNYG7</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-09 14:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>365.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50017 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "0      839FKFW2LLX4LMBB 2016-05-27  INBUX904GIHI8YBD  LJKS5NK6788CYMUU   \n",
       "1      97OX39BGVMHODLJM 2018-09-27  J0MMOOPP709DIDIE  LJKS5NK6788CYMUU   \n",
       "2      041ORQM5OIHTIU6L 2014-08-24  E4UJLQNCI16UX5CS  LJKS5NK6788CYMUU   \n",
       "3      YT796QI18WNGZ7ZJ 2014-04-12  C9SDFHF7553BE247  LJKS5NK6788CYMUU   \n",
       "4      6YLROQT27B6HRF4E 2015-07-28  48EQXS6IHYNZDDZ5  LJKS5NK6788CYMUU   \n",
       "...                 ...        ...               ...               ...   \n",
       "50012  4OMS8ZSA0UX8LHWI 2017-09-20  1TD5MROATV1NHZ4Y  E4K99D4JR9E40VE1   \n",
       "50013  RR0VKJN8V0KHNKGG 2018-03-19  22EX9VZSJKHP4AIP  E4K99D4JR9E40VE1   \n",
       "50014  STJ6QJC30WPRM93H 2017-09-21  LMX18PNGWCIMG1QW  E4K99D4JR9E40VE1   \n",
       "50015  QHEUIYNC0XQX7GDR 2018-01-28  4U0VH2TGQL30X23X  E4K99D4JR9E40VE1   \n",
       "50016  NKHFWT5I2J9LPAPG 2017-09-06  ORWFRT5TUSYGNYG7  E4K99D4JR9E40VE1   \n",
       "\n",
       "                    Date of Meal                        Participants  \\\n",
       "0      2016-05-31 07:00:00+02:00                    ['David Bishop']   \n",
       "1      2018-10-01 20:00:00+02:00                    ['David Bishop']   \n",
       "2      2014-08-23 14:00:00+02:00                  ['Karen Stansell']   \n",
       "3      2014-04-07 21:00:00+02:00                    ['Addie Patino']   \n",
       "4      2015-07-27 14:00:00+02:00   ['Addie Patino' 'Susan Guerrero']   \n",
       "...                          ...                                 ...   \n",
       "50012  2017-09-21 08:00:00+02:00                  ['Regina Shirley']   \n",
       "50013  2018-03-18 09:00:00+01:00  ['Robin Ramos' 'Chester Mortimer']   \n",
       "50014  2017-09-22 21:00:00+02:00                     ['Robin Ramos']   \n",
       "50015  2018-02-01 21:00:00+01:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "50016  2017-09-09 14:00:00+02:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "\n",
       "       Meal Price Type of Meal  Heroes Adjustment  \n",
       "0           469.0    Breakfast              False  \n",
       "1            22.0       Dinner              False  \n",
       "2           314.0        Lunch              False  \n",
       "3           438.0       Dinner              False  \n",
       "4           690.0        Lunch              False  \n",
       "...           ...          ...                ...  \n",
       "50012         9.0    Breakfast              False  \n",
       "50013        25.0    Breakfast              False  \n",
       "50014       160.0       Dinner              False  \n",
       "50015       497.0       Dinner              False  \n",
       "50016       365.0        Lunch              False  \n",
       "\n",
       "[50017 rows x 9 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "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>Order Id</th>\n",
       "      <th>Company Id_x</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Date_x</th>\n",
       "      <th>Order Value</th>\n",
       "      <th>Converted</th>\n",
       "      <th>Date_y</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id_y</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>4837</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>INBUX904GIHI8YBD</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2016-05-31 07:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>469.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>343</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>J0MMOOPP709DIDIE</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2018-10-01 20:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>4185</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>314.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>5839</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>438.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6YLROQT27B6HRF4E</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2015-07-28</td>\n",
       "      <td>8841</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-07-28</td>\n",
       "      <td>48EQXS6IHYNZDDZ5</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2015-07-27 14:00:00+02:00</td>\n",
       "      <td>['Addie Patino' 'Susan Guerrero']</td>\n",
       "      <td>690.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50012</th>\n",
       "      <td>4OMS8ZSA0UX8LHWI</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>110</td>\n",
       "      <td>0</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>1TD5MROATV1NHZ4Y</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-21 08:00:00+02:00</td>\n",
       "      <td>['Regina Shirley']</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50013</th>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>356</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>25.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50014</th>\n",
       "      <td>STJ6QJC30WPRM93H</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>2228</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>LMX18PNGWCIMG1QW</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-22 21:00:00+02:00</td>\n",
       "      <td>['Robin Ramos']</td>\n",
       "      <td>160.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50015</th>\n",
       "      <td>QHEUIYNC0XQX7GDR</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>8869</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>4U0VH2TGQL30X23X</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-02-01 21:00:00+01:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>497.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50016</th>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>5138</td>\n",
       "      <td>0</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>ORWFRT5TUSYGNYG7</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-09 14:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>365.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50017 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id      Company Id_x            Company Name     Date_x  \\\n",
       "0      839FKFW2LLX4LMBB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-05-27   \n",
       "1      97OX39BGVMHODLJM  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2018-09-27   \n",
       "2      041ORQM5OIHTIU6L  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2014-08-24   \n",
       "3      YT796QI18WNGZ7ZJ  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2014-04-12   \n",
       "4      6YLROQT27B6HRF4E  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2015-07-28   \n",
       "...                 ...               ...                     ...        ...   \n",
       "50012  4OMS8ZSA0UX8LHWI  E4K99D4JR9E40VE1     Fierce Productivity 2017-09-20   \n",
       "50013  RR0VKJN8V0KHNKGG  E4K99D4JR9E40VE1     Fierce Productivity 2018-03-19   \n",
       "50014  STJ6QJC30WPRM93H  E4K99D4JR9E40VE1     Fierce Productivity 2017-09-21   \n",
       "50015  QHEUIYNC0XQX7GDR  E4K99D4JR9E40VE1     Fierce Productivity 2018-01-28   \n",
       "50016  NKHFWT5I2J9LPAPG  E4K99D4JR9E40VE1     Fierce Productivity 2017-09-06   \n",
       "\n",
       "       Order Value  Converted     Date_y           Meal Id      Company Id_y  \\\n",
       "0             4837          0 2016-05-27  INBUX904GIHI8YBD  LJKS5NK6788CYMUU   \n",
       "1              343          0 2018-09-27  J0MMOOPP709DIDIE  LJKS5NK6788CYMUU   \n",
       "2             4185          0 2014-08-24  E4UJLQNCI16UX5CS  LJKS5NK6788CYMUU   \n",
       "3             5839          0 2014-04-12  C9SDFHF7553BE247  LJKS5NK6788CYMUU   \n",
       "4             8841          0 2015-07-28  48EQXS6IHYNZDDZ5  LJKS5NK6788CYMUU   \n",
       "...            ...        ...        ...               ...               ...   \n",
       "50012          110          0 2017-09-20  1TD5MROATV1NHZ4Y  E4K99D4JR9E40VE1   \n",
       "50013          356          0 2018-03-19  22EX9VZSJKHP4AIP  E4K99D4JR9E40VE1   \n",
       "50014         2228          1 2017-09-21  LMX18PNGWCIMG1QW  E4K99D4JR9E40VE1   \n",
       "50015         8869          0 2018-01-28  4U0VH2TGQL30X23X  E4K99D4JR9E40VE1   \n",
       "50016         5138          0 2017-09-06  ORWFRT5TUSYGNYG7  E4K99D4JR9E40VE1   \n",
       "\n",
       "                    Date of Meal                        Participants  \\\n",
       "0      2016-05-31 07:00:00+02:00                    ['David Bishop']   \n",
       "1      2018-10-01 20:00:00+02:00                    ['David Bishop']   \n",
       "2      2014-08-23 14:00:00+02:00                  ['Karen Stansell']   \n",
       "3      2014-04-07 21:00:00+02:00                    ['Addie Patino']   \n",
       "4      2015-07-27 14:00:00+02:00   ['Addie Patino' 'Susan Guerrero']   \n",
       "...                          ...                                 ...   \n",
       "50012  2017-09-21 08:00:00+02:00                  ['Regina Shirley']   \n",
       "50013  2018-03-18 09:00:00+01:00  ['Robin Ramos' 'Chester Mortimer']   \n",
       "50014  2017-09-22 21:00:00+02:00                     ['Robin Ramos']   \n",
       "50015  2018-02-01 21:00:00+01:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "50016  2017-09-09 14:00:00+02:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "\n",
       "       Meal Price Type of Meal  Heroes Adjustment  \n",
       "0           469.0    Breakfast              False  \n",
       "1            22.0       Dinner              False  \n",
       "2           314.0        Lunch              False  \n",
       "3           438.0       Dinner              False  \n",
       "4           690.0        Lunch              False  \n",
       "...           ...          ...                ...  \n",
       "50012         9.0    Breakfast              False  \n",
       "50013        25.0    Breakfast              False  \n",
       "50014       160.0       Dinner              False  \n",
       "50015       497.0       Dinner              False  \n",
       "50016       365.0        Lunch              False  \n",
       "\n",
       "[50017 rows x 14 columns]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(order_data,invoices, on = 'Order Id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>Order Id</th>\n",
       "      <th>Company Id_x</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Date_x</th>\n",
       "      <th>Order Value</th>\n",
       "      <th>Converted</th>\n",
       "      <th>New id</th>\n",
       "      <th>Date_y</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id_y</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80EYLOKP9E762WKG</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>4875</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TLEXR1HZWTUTBHPB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2015-07-30</td>\n",
       "      <td>8425</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>4837</td>\n",
       "      <td>0</td>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>INBUX904GIHI8YBD</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2016-05-31 07:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>469.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>343</td>\n",
       "      <td>0</td>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>J0MMOOPP709DIDIE</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2018-10-01 20:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5T4LGH4XGBWOD49Z</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-01-14</td>\n",
       "      <td>983</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>4MFHIAQKH0HCACJM</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-07-25</td>\n",
       "      <td>4851</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>K1UP23P8SIY1YION</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-08-24</td>\n",
       "      <td>7868</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>5138</td>\n",
       "      <td>0</td>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>ORWFRT5TUSYGNYG7</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-09 14:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>365.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>OF79M3H9C44UM6PO</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-10-29</td>\n",
       "      <td>2036</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>EHN2QR78K2RX0FQ9</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-03-07</td>\n",
       "      <td>7183</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100000 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id      Company Id_x            Company Name     Date_x  \\\n",
       "0      80EYLOKP9E762WKG  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2017-02-18   \n",
       "1      TLEXR1HZWTUTBHPB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2015-07-30   \n",
       "2      839FKFW2LLX4LMBB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-05-27   \n",
       "3      97OX39BGVMHODLJM  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2018-09-27   \n",
       "4      5T4LGH4XGBWOD49Z  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-01-14   \n",
       "...                 ...               ...                     ...        ...   \n",
       "99995  4MFHIAQKH0HCACJM  E4K99D4JR9E40VE1     Fierce Productivity 2015-07-25   \n",
       "99996  K1UP23P8SIY1YION  E4K99D4JR9E40VE1     Fierce Productivity 2015-08-24   \n",
       "99997  NKHFWT5I2J9LPAPG  E4K99D4JR9E40VE1     Fierce Productivity 2017-09-06   \n",
       "99998  OF79M3H9C44UM6PO  E4K99D4JR9E40VE1     Fierce Productivity 2015-10-29   \n",
       "99999  EHN2QR78K2RX0FQ9  E4K99D4JR9E40VE1     Fierce Productivity 2015-03-07   \n",
       "\n",
       "       Order Value  Converted            New id     Date_y           Meal Id  \\\n",
       "0             4875          1               NaN        NaT               NaN   \n",
       "1             8425          0               NaN        NaT               NaN   \n",
       "2             4837          0  839FKFW2LLX4LMBB 2016-05-27  INBUX904GIHI8YBD   \n",
       "3              343          0  97OX39BGVMHODLJM 2018-09-27  J0MMOOPP709DIDIE   \n",
       "4              983          0               NaN        NaT               NaN   \n",
       "...            ...        ...               ...        ...               ...   \n",
       "99995         4851          0               NaN        NaT               NaN   \n",
       "99996         7868          0               NaN        NaT               NaN   \n",
       "99997         5138          0  NKHFWT5I2J9LPAPG 2017-09-06  ORWFRT5TUSYGNYG7   \n",
       "99998         2036          0               NaN        NaT               NaN   \n",
       "99999         7183          0               NaN        NaT               NaN   \n",
       "\n",
       "           Company Id_y               Date of Meal  \\\n",
       "0                   NaN                        NaN   \n",
       "1                   NaN                        NaN   \n",
       "2      LJKS5NK6788CYMUU  2016-05-31 07:00:00+02:00   \n",
       "3      LJKS5NK6788CYMUU  2018-10-01 20:00:00+02:00   \n",
       "4                   NaN                        NaN   \n",
       "...                 ...                        ...   \n",
       "99995               NaN                        NaN   \n",
       "99996               NaN                        NaN   \n",
       "99997  E4K99D4JR9E40VE1  2017-09-09 14:00:00+02:00   \n",
       "99998               NaN                        NaN   \n",
       "99999               NaN                        NaN   \n",
       "\n",
       "                             Participants  Meal Price Type of Meal  \\\n",
       "0                                     NaN         NaN          NaN   \n",
       "1                                     NaN         NaN          NaN   \n",
       "2                        ['David Bishop']       469.0    Breakfast   \n",
       "3                        ['David Bishop']        22.0       Dinner   \n",
       "4                                     NaN         NaN          NaN   \n",
       "...                                   ...         ...          ...   \n",
       "99995                                 NaN         NaN          NaN   \n",
       "99996                                 NaN         NaN          NaN   \n",
       "99997  ['Chester Mortimer' 'Robin Ramos']       365.0        Lunch   \n",
       "99998                                 NaN         NaN          NaN   \n",
       "99999                                 NaN         NaN          NaN   \n",
       "\n",
       "      Heroes Adjustment  \n",
       "0                   NaN  \n",
       "1                   NaN  \n",
       "2                 False  \n",
       "3                 False  \n",
       "4                   NaN  \n",
       "...                 ...  \n",
       "99995               NaN  \n",
       "99996               NaN  \n",
       "99997             False  \n",
       "99998               NaN  \n",
       "99999               NaN  \n",
       "\n",
       "[100000 rows x 15 columns]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(order_data,invoices.rename(columns = {'Order Id' : 'New id'}), left_on = 'Order Id',right_on = 'New id', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lookup dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "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>Order Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>Order Value</th>\n",
       "      <th>Converted</th>\n",
       "      <th>Sales Rep</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80EYLOKP9E762WKG</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>4875</td>\n",
       "      <td>1</td>\n",
       "      <td>Jessie Mcallister</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TLEXR1HZWTUTBHPB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2015-07-30</td>\n",
       "      <td>8425</td>\n",
       "      <td>0</td>\n",
       "      <td>Jessie Mcallister</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>4837</td>\n",
       "      <td>0</td>\n",
       "      <td>Jessie Mcallister</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>343</td>\n",
       "      <td>0</td>\n",
       "      <td>Jessie Mcallister</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5T4LGH4XGBWOD49Z</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-01-14</td>\n",
       "      <td>983</td>\n",
       "      <td>0</td>\n",
       "      <td>Jessie Mcallister</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>4MFHIAQKH0HCACJM</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-07-25</td>\n",
       "      <td>4851</td>\n",
       "      <td>0</td>\n",
       "      <td>Hazel Dickerson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>K1UP23P8SIY1YION</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-08-24</td>\n",
       "      <td>7868</td>\n",
       "      <td>0</td>\n",
       "      <td>Hazel Dickerson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>5138</td>\n",
       "      <td>0</td>\n",
       "      <td>Hazel Dickerson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>OF79M3H9C44UM6PO</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-10-29</td>\n",
       "      <td>2036</td>\n",
       "      <td>0</td>\n",
       "      <td>Hazel Dickerson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>EHN2QR78K2RX0FQ9</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-03-07</td>\n",
       "      <td>7183</td>\n",
       "      <td>0</td>\n",
       "      <td>Hazel Dickerson</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id        Company Id            Company Name       Date  \\\n",
       "0      80EYLOKP9E762WKG  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2017-02-18   \n",
       "1      TLEXR1HZWTUTBHPB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2015-07-30   \n",
       "2      839FKFW2LLX4LMBB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-05-27   \n",
       "3      97OX39BGVMHODLJM  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2018-09-27   \n",
       "4      5T4LGH4XGBWOD49Z  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-01-14   \n",
       "...                 ...               ...                     ...        ...   \n",
       "99995  4MFHIAQKH0HCACJM  E4K99D4JR9E40VE1     Fierce Productivity 2015-07-25   \n",
       "99996  K1UP23P8SIY1YION  E4K99D4JR9E40VE1     Fierce Productivity 2015-08-24   \n",
       "99997  NKHFWT5I2J9LPAPG  E4K99D4JR9E40VE1     Fierce Productivity 2017-09-06   \n",
       "99998  OF79M3H9C44UM6PO  E4K99D4JR9E40VE1     Fierce Productivity 2015-10-29   \n",
       "99999  EHN2QR78K2RX0FQ9  E4K99D4JR9E40VE1     Fierce Productivity 2015-03-07   \n",
       "\n",
       "       Order Value  Converted          Sales Rep  \n",
       "0             4875          1  Jessie Mcallister  \n",
       "1             8425          0  Jessie Mcallister  \n",
       "2             4837          0  Jessie Mcallister  \n",
       "3              343          0  Jessie Mcallister  \n",
       "4              983          0  Jessie Mcallister  \n",
       "...            ...        ...                ...  \n",
       "99995         4851          0    Hazel Dickerson  \n",
       "99996         7868          0    Hazel Dickerson  \n",
       "99997         5138          0    Hazel Dickerson  \n",
       "99998         2036          0    Hazel Dickerson  \n",
       "99999         7183          0    Hazel Dickerson  \n",
       "\n",
       "[100000 rows x 7 columns]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lookup = sales_team.set_index('Company Name')['Sales Rep']\n",
    "order_data['Sales Rep'] = order_data['Company Name'].map(lookup)\n",
    "order_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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>Order Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>Order Value</th>\n",
       "      <th>Converted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80EYLOKP9E762WKG</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2017-02-18</td>\n",
       "      <td>4875</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TLEXR1HZWTUTBHPB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2015-07-30</td>\n",
       "      <td>8425</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>4837</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>343</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5T4LGH4XGBWOD49Z</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>Chimera-Chasing Casbah</td>\n",
       "      <td>2016-01-14</td>\n",
       "      <td>983</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>4MFHIAQKH0HCACJM</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-07-25</td>\n",
       "      <td>4851</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>K1UP23P8SIY1YION</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-08-24</td>\n",
       "      <td>7868</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>5138</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>OF79M3H9C44UM6PO</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-10-29</td>\n",
       "      <td>2036</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>EHN2QR78K2RX0FQ9</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>Fierce Productivity</td>\n",
       "      <td>2015-03-07</td>\n",
       "      <td>7183</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id        Company Id            Company Name       Date  \\\n",
       "0      80EYLOKP9E762WKG  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2017-02-18   \n",
       "1      TLEXR1HZWTUTBHPB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2015-07-30   \n",
       "2      839FKFW2LLX4LMBB  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-05-27   \n",
       "3      97OX39BGVMHODLJM  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2018-09-27   \n",
       "4      5T4LGH4XGBWOD49Z  LJKS5NK6788CYMUU  Chimera-Chasing Casbah 2016-01-14   \n",
       "...                 ...               ...                     ...        ...   \n",
       "99995  4MFHIAQKH0HCACJM  E4K99D4JR9E40VE1     Fierce Productivity 2015-07-25   \n",
       "99996  K1UP23P8SIY1YION  E4K99D4JR9E40VE1     Fierce Productivity 2015-08-24   \n",
       "99997  NKHFWT5I2J9LPAPG  E4K99D4JR9E40VE1     Fierce Productivity 2017-09-06   \n",
       "99998  OF79M3H9C44UM6PO  E4K99D4JR9E40VE1     Fierce Productivity 2015-10-29   \n",
       "99999  EHN2QR78K2RX0FQ9  E4K99D4JR9E40VE1     Fierce Productivity 2015-03-07   \n",
       "\n",
       "       Order Value  Converted  \n",
       "0             4875          1  \n",
       "1             8425          0  \n",
       "2             4837          0  \n",
       "3              343          0  \n",
       "4              983          0  \n",
       "...            ...        ...  \n",
       "99995         4851          0  \n",
       "99996         7868          0  \n",
       "99997         5138          0  \n",
       "99998         2036          0  \n",
       "99999         7183          0  \n",
       "\n",
       "[100000 rows x 6 columns]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reshaping DataFrames (melt, pivot, transpose, stack, unstack)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Order Id</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>6YLROQT27B6HRF4E</td>\n",
       "      <td>AT0R4DFYYAFOC88Q</td>\n",
       "      <td>2DDN2LHS7G85GKPQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>2016-05-27 00:00:00</td>\n",
       "      <td>2018-09-27 00:00:00</td>\n",
       "      <td>2014-08-24 00:00:00</td>\n",
       "      <td>2014-04-12 00:00:00</td>\n",
       "      <td>2015-07-28 00:00:00</td>\n",
       "      <td>2014-07-21 00:00:00</td>\n",
       "      <td>2014-04-29 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meal Id</th>\n",
       "      <td>INBUX904GIHI8YBD</td>\n",
       "      <td>J0MMOOPP709DIDIE</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>48EQXS6IHYNZDDZ5</td>\n",
       "      <td>W48JPR1UYWJ18NC6</td>\n",
       "      <td>1MKLAKBOE3SP7YUL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company Id</th>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date of Meal</th>\n",
       "      <td>2016-05-31 07:00:00+02:00</td>\n",
       "      <td>2018-10-01 20:00:00+02:00</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>2015-07-27 14:00:00+02:00</td>\n",
       "      <td>2014-07-17 20:00:00+02:00</td>\n",
       "      <td>2014-04-30 21:00:00+02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Participants</th>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>['Addie Patino' 'Susan Guerrero']</td>\n",
       "      <td>['David Bishop' 'Susan Guerrero' 'Karen Stanse...</td>\n",
       "      <td>['Susan Guerrero' 'David Bishop']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meal Price</th>\n",
       "      <td>469</td>\n",
       "      <td>22</td>\n",
       "      <td>314</td>\n",
       "      <td>438</td>\n",
       "      <td>690</td>\n",
       "      <td>181</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Type of Meal</th>\n",
       "      <td>Breakfast</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>Dinner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Heroes Adjustment</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           0                          1  \\\n",
       "Order Id                    839FKFW2LLX4LMBB           97OX39BGVMHODLJM   \n",
       "Date                     2016-05-27 00:00:00        2018-09-27 00:00:00   \n",
       "Meal Id                     INBUX904GIHI8YBD           J0MMOOPP709DIDIE   \n",
       "Company Id                  LJKS5NK6788CYMUU           LJKS5NK6788CYMUU   \n",
       "Date of Meal       2016-05-31 07:00:00+02:00  2018-10-01 20:00:00+02:00   \n",
       "Participants                ['David Bishop']           ['David Bishop']   \n",
       "Meal Price                               469                         22   \n",
       "Type of Meal                       Breakfast                     Dinner   \n",
       "Heroes Adjustment                      False                      False   \n",
       "\n",
       "                                           2                          3  \\\n",
       "Order Id                    041ORQM5OIHTIU6L           YT796QI18WNGZ7ZJ   \n",
       "Date                     2014-08-24 00:00:00        2014-04-12 00:00:00   \n",
       "Meal Id                     E4UJLQNCI16UX5CS           C9SDFHF7553BE247   \n",
       "Company Id                  LJKS5NK6788CYMUU           LJKS5NK6788CYMUU   \n",
       "Date of Meal       2014-08-23 14:00:00+02:00  2014-04-07 21:00:00+02:00   \n",
       "Participants              ['Karen Stansell']           ['Addie Patino']   \n",
       "Meal Price                               314                        438   \n",
       "Type of Meal                           Lunch                     Dinner   \n",
       "Heroes Adjustment                      False                      False   \n",
       "\n",
       "                                                   4  \\\n",
       "Order Id                            6YLROQT27B6HRF4E   \n",
       "Date                             2015-07-28 00:00:00   \n",
       "Meal Id                             48EQXS6IHYNZDDZ5   \n",
       "Company Id                          LJKS5NK6788CYMUU   \n",
       "Date of Meal               2015-07-27 14:00:00+02:00   \n",
       "Participants       ['Addie Patino' 'Susan Guerrero']   \n",
       "Meal Price                                       690   \n",
       "Type of Meal                                   Lunch   \n",
       "Heroes Adjustment                              False   \n",
       "\n",
       "                                                                   5  \\\n",
       "Order Id                                            AT0R4DFYYAFOC88Q   \n",
       "Date                                             2014-07-21 00:00:00   \n",
       "Meal Id                                             W48JPR1UYWJ18NC6   \n",
       "Company Id                                          LJKS5NK6788CYMUU   \n",
       "Date of Meal                               2014-07-17 20:00:00+02:00   \n",
       "Participants       ['David Bishop' 'Susan Guerrero' 'Karen Stanse...   \n",
       "Meal Price                                                       181   \n",
       "Type of Meal                                                  Dinner   \n",
       "Heroes Adjustment                                              False   \n",
       "\n",
       "                                                   6  \n",
       "Order Id                            2DDN2LHS7G85GKPQ  \n",
       "Date                             2014-04-29 00:00:00  \n",
       "Meal Id                             1MKLAKBOE3SP7YUL  \n",
       "Company Id                          LJKS5NK6788CYMUU  \n",
       "Date of Meal               2014-04-30 21:00:00+02:00  \n",
       "Participants       ['Susan Guerrero' 'David Bishop']  \n",
       "Meal Price                                        14  \n",
       "Type of Meal                                  Dinner  \n",
       "Heroes Adjustment                              False  "
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "invoices.head(7).T\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Meal Price</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "      <th>Breakfast</th>\n",
       "      <th>Dinner</th>\n",
       "      <th>Lunch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>INBUX904GIHI8YBD</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2016-05-31 07:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>469.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "      <td>469.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>J0MMOOPP709DIDIE</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2018-10-01 20:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>314.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>314.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>438.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>438.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6YLROQT27B6HRF4E</td>\n",
       "      <td>2015-07-28</td>\n",
       "      <td>48EQXS6IHYNZDDZ5</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2015-07-27 14:00:00+02:00</td>\n",
       "      <td>['Addie Patino' 'Susan Guerrero']</td>\n",
       "      <td>690.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>690.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50012</th>\n",
       "      <td>4OMS8ZSA0UX8LHWI</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>1TD5MROATV1NHZ4Y</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-21 08:00:00+02:00</td>\n",
       "      <td>['Regina Shirley']</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50013</th>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>25.0</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>False</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50014</th>\n",
       "      <td>STJ6QJC30WPRM93H</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>LMX18PNGWCIMG1QW</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-22 21:00:00+02:00</td>\n",
       "      <td>['Robin Ramos']</td>\n",
       "      <td>160.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50015</th>\n",
       "      <td>QHEUIYNC0XQX7GDR</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>4U0VH2TGQL30X23X</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-02-01 21:00:00+01:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>497.0</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>497.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50016</th>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>ORWFRT5TUSYGNYG7</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-09 14:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>365.0</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>365.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50017 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "0      839FKFW2LLX4LMBB 2016-05-27  INBUX904GIHI8YBD  LJKS5NK6788CYMUU   \n",
       "1      97OX39BGVMHODLJM 2018-09-27  J0MMOOPP709DIDIE  LJKS5NK6788CYMUU   \n",
       "2      041ORQM5OIHTIU6L 2014-08-24  E4UJLQNCI16UX5CS  LJKS5NK6788CYMUU   \n",
       "3      YT796QI18WNGZ7ZJ 2014-04-12  C9SDFHF7553BE247  LJKS5NK6788CYMUU   \n",
       "4      6YLROQT27B6HRF4E 2015-07-28  48EQXS6IHYNZDDZ5  LJKS5NK6788CYMUU   \n",
       "...                 ...        ...               ...               ...   \n",
       "50012  4OMS8ZSA0UX8LHWI 2017-09-20  1TD5MROATV1NHZ4Y  E4K99D4JR9E40VE1   \n",
       "50013  RR0VKJN8V0KHNKGG 2018-03-19  22EX9VZSJKHP4AIP  E4K99D4JR9E40VE1   \n",
       "50014  STJ6QJC30WPRM93H 2017-09-21  LMX18PNGWCIMG1QW  E4K99D4JR9E40VE1   \n",
       "50015  QHEUIYNC0XQX7GDR 2018-01-28  4U0VH2TGQL30X23X  E4K99D4JR9E40VE1   \n",
       "50016  NKHFWT5I2J9LPAPG 2017-09-06  ORWFRT5TUSYGNYG7  E4K99D4JR9E40VE1   \n",
       "\n",
       "                    Date of Meal                        Participants  \\\n",
       "0      2016-05-31 07:00:00+02:00                    ['David Bishop']   \n",
       "1      2018-10-01 20:00:00+02:00                    ['David Bishop']   \n",
       "2      2014-08-23 14:00:00+02:00                  ['Karen Stansell']   \n",
       "3      2014-04-07 21:00:00+02:00                    ['Addie Patino']   \n",
       "4      2015-07-27 14:00:00+02:00   ['Addie Patino' 'Susan Guerrero']   \n",
       "...                          ...                                 ...   \n",
       "50012  2017-09-21 08:00:00+02:00                  ['Regina Shirley']   \n",
       "50013  2018-03-18 09:00:00+01:00  ['Robin Ramos' 'Chester Mortimer']   \n",
       "50014  2017-09-22 21:00:00+02:00                     ['Robin Ramos']   \n",
       "50015  2018-02-01 21:00:00+01:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "50016  2017-09-09 14:00:00+02:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "\n",
       "       Meal Price Type of Meal  Heroes Adjustment  Breakfast  Dinner  Lunch  \n",
       "0           469.0    Breakfast              False      469.0     0.0    0.0  \n",
       "1            22.0       Dinner              False        0.0    22.0    0.0  \n",
       "2           314.0        Lunch              False        0.0     0.0  314.0  \n",
       "3           438.0       Dinner              False        0.0   438.0    0.0  \n",
       "4           690.0        Lunch              False        0.0     0.0  690.0  \n",
       "...           ...          ...                ...        ...     ...    ...  \n",
       "50012         9.0    Breakfast              False        9.0     0.0    0.0  \n",
       "50013        25.0    Breakfast              False       25.0     0.0    0.0  \n",
       "50014       160.0       Dinner              False        0.0   160.0    0.0  \n",
       "50015       497.0       Dinner              False        0.0   497.0    0.0  \n",
       "50016       365.0        Lunch              False        0.0     0.0  365.0  \n",
       "\n",
       "[50017 rows x 12 columns]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melt_exp = pd.merge(\n",
    "    invoices,\n",
    "    pd.get_dummies(invoices['Type of Meal']).mul(invoices['Meal Price'].values,axis = 0),\n",
    "    left_index = True,\n",
    "    right_index = True\n",
    "                  )\n",
    "melt_exp   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "      <th>Breakfast</th>\n",
       "      <th>Dinner</th>\n",
       "      <th>Lunch</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>INBUX904GIHI8YBD</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2016-05-31 07:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>False</td>\n",
       "      <td>469.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>J0MMOOPP709DIDIE</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2018-10-01 20:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>314.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>438.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6YLROQT27B6HRF4E</td>\n",
       "      <td>2015-07-28</td>\n",
       "      <td>48EQXS6IHYNZDDZ5</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2015-07-27 14:00:00+02:00</td>\n",
       "      <td>['Addie Patino' 'Susan Guerrero']</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>690.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50012</th>\n",
       "      <td>4OMS8ZSA0UX8LHWI</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>1TD5MROATV1NHZ4Y</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-21 08:00:00+02:00</td>\n",
       "      <td>['Regina Shirley']</td>\n",
       "      <td>False</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50013</th>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>False</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50014</th>\n",
       "      <td>STJ6QJC30WPRM93H</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>LMX18PNGWCIMG1QW</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-22 21:00:00+02:00</td>\n",
       "      <td>['Robin Ramos']</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50015</th>\n",
       "      <td>QHEUIYNC0XQX7GDR</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>4U0VH2TGQL30X23X</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-02-01 21:00:00+01:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>497.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50016</th>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>ORWFRT5TUSYGNYG7</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-09 14:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>365.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50017 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Order Id       Date           Meal Id        Company Id  \\\n",
       "0      839FKFW2LLX4LMBB 2016-05-27  INBUX904GIHI8YBD  LJKS5NK6788CYMUU   \n",
       "1      97OX39BGVMHODLJM 2018-09-27  J0MMOOPP709DIDIE  LJKS5NK6788CYMUU   \n",
       "2      041ORQM5OIHTIU6L 2014-08-24  E4UJLQNCI16UX5CS  LJKS5NK6788CYMUU   \n",
       "3      YT796QI18WNGZ7ZJ 2014-04-12  C9SDFHF7553BE247  LJKS5NK6788CYMUU   \n",
       "4      6YLROQT27B6HRF4E 2015-07-28  48EQXS6IHYNZDDZ5  LJKS5NK6788CYMUU   \n",
       "...                 ...        ...               ...               ...   \n",
       "50012  4OMS8ZSA0UX8LHWI 2017-09-20  1TD5MROATV1NHZ4Y  E4K99D4JR9E40VE1   \n",
       "50013  RR0VKJN8V0KHNKGG 2018-03-19  22EX9VZSJKHP4AIP  E4K99D4JR9E40VE1   \n",
       "50014  STJ6QJC30WPRM93H 2017-09-21  LMX18PNGWCIMG1QW  E4K99D4JR9E40VE1   \n",
       "50015  QHEUIYNC0XQX7GDR 2018-01-28  4U0VH2TGQL30X23X  E4K99D4JR9E40VE1   \n",
       "50016  NKHFWT5I2J9LPAPG 2017-09-06  ORWFRT5TUSYGNYG7  E4K99D4JR9E40VE1   \n",
       "\n",
       "                    Date of Meal                        Participants  \\\n",
       "0      2016-05-31 07:00:00+02:00                    ['David Bishop']   \n",
       "1      2018-10-01 20:00:00+02:00                    ['David Bishop']   \n",
       "2      2014-08-23 14:00:00+02:00                  ['Karen Stansell']   \n",
       "3      2014-04-07 21:00:00+02:00                    ['Addie Patino']   \n",
       "4      2015-07-27 14:00:00+02:00   ['Addie Patino' 'Susan Guerrero']   \n",
       "...                          ...                                 ...   \n",
       "50012  2017-09-21 08:00:00+02:00                  ['Regina Shirley']   \n",
       "50013  2018-03-18 09:00:00+01:00  ['Robin Ramos' 'Chester Mortimer']   \n",
       "50014  2017-09-22 21:00:00+02:00                     ['Robin Ramos']   \n",
       "50015  2018-02-01 21:00:00+01:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "50016  2017-09-09 14:00:00+02:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "\n",
       "       Heroes Adjustment  Breakfast  Dinner  Lunch  \n",
       "0                  False      469.0     0.0    0.0  \n",
       "1                  False        0.0    22.0    0.0  \n",
       "2                  False        0.0     0.0  314.0  \n",
       "3                  False        0.0   438.0    0.0  \n",
       "4                  False        0.0     0.0  690.0  \n",
       "...                  ...        ...     ...    ...  \n",
       "50012              False        9.0     0.0    0.0  \n",
       "50013              False       25.0     0.0    0.0  \n",
       "50014              False        0.0   160.0    0.0  \n",
       "50015              False        0.0   497.0    0.0  \n",
       "50016              False        0.0     0.0  365.0  \n",
       "\n",
       "[50017 rows x 10 columns]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del melt_exp['Type of Meal']\n",
    "del melt_exp['Meal Price']\n",
    "melt_exp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "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>Order Id</th>\n",
       "      <th>Date</th>\n",
       "      <th>Meal Id</th>\n",
       "      <th>Company Id</th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Participants</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Expenses</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>839FKFW2LLX4LMBB</td>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>INBUX904GIHI8YBD</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2016-05-31 07:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>False</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>469.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97OX39BGVMHODLJM</td>\n",
       "      <td>2018-09-27</td>\n",
       "      <td>J0MMOOPP709DIDIE</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2018-10-01 20:00:00+02:00</td>\n",
       "      <td>['David Bishop']</td>\n",
       "      <td>False</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>041ORQM5OIHTIU6L</td>\n",
       "      <td>2014-08-24</td>\n",
       "      <td>E4UJLQNCI16UX5CS</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-08-23 14:00:00+02:00</td>\n",
       "      <td>['Karen Stansell']</td>\n",
       "      <td>False</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YT796QI18WNGZ7ZJ</td>\n",
       "      <td>2014-04-12</td>\n",
       "      <td>C9SDFHF7553BE247</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2014-04-07 21:00:00+02:00</td>\n",
       "      <td>['Addie Patino']</td>\n",
       "      <td>False</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6YLROQT27B6HRF4E</td>\n",
       "      <td>2015-07-28</td>\n",
       "      <td>48EQXS6IHYNZDDZ5</td>\n",
       "      <td>LJKS5NK6788CYMUU</td>\n",
       "      <td>2015-07-27 14:00:00+02:00</td>\n",
       "      <td>['Addie Patino' 'Susan Guerrero']</td>\n",
       "      <td>False</td>\n",
       "      <td>Breakfast</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150046</th>\n",
       "      <td>4OMS8ZSA0UX8LHWI</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>1TD5MROATV1NHZ4Y</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-21 08:00:00+02:00</td>\n",
       "      <td>['Regina Shirley']</td>\n",
       "      <td>False</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150047</th>\n",
       "      <td>RR0VKJN8V0KHNKGG</td>\n",
       "      <td>2018-03-19</td>\n",
       "      <td>22EX9VZSJKHP4AIP</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-03-18 09:00:00+01:00</td>\n",
       "      <td>['Robin Ramos' 'Chester Mortimer']</td>\n",
       "      <td>False</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150048</th>\n",
       "      <td>STJ6QJC30WPRM93H</td>\n",
       "      <td>2017-09-21</td>\n",
       "      <td>LMX18PNGWCIMG1QW</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-22 21:00:00+02:00</td>\n",
       "      <td>['Robin Ramos']</td>\n",
       "      <td>False</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150049</th>\n",
       "      <td>QHEUIYNC0XQX7GDR</td>\n",
       "      <td>2018-01-28</td>\n",
       "      <td>4U0VH2TGQL30X23X</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2018-02-01 21:00:00+01:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>False</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150050</th>\n",
       "      <td>NKHFWT5I2J9LPAPG</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>ORWFRT5TUSYGNYG7</td>\n",
       "      <td>E4K99D4JR9E40VE1</td>\n",
       "      <td>2017-09-09 14:00:00+02:00</td>\n",
       "      <td>['Chester Mortimer' 'Robin Ramos']</td>\n",
       "      <td>False</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>365.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>150051 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                Order Id       Date           Meal Id        Company Id  \\\n",
       "0       839FKFW2LLX4LMBB 2016-05-27  INBUX904GIHI8YBD  LJKS5NK6788CYMUU   \n",
       "1       97OX39BGVMHODLJM 2018-09-27  J0MMOOPP709DIDIE  LJKS5NK6788CYMUU   \n",
       "2       041ORQM5OIHTIU6L 2014-08-24  E4UJLQNCI16UX5CS  LJKS5NK6788CYMUU   \n",
       "3       YT796QI18WNGZ7ZJ 2014-04-12  C9SDFHF7553BE247  LJKS5NK6788CYMUU   \n",
       "4       6YLROQT27B6HRF4E 2015-07-28  48EQXS6IHYNZDDZ5  LJKS5NK6788CYMUU   \n",
       "...                  ...        ...               ...               ...   \n",
       "150046  4OMS8ZSA0UX8LHWI 2017-09-20  1TD5MROATV1NHZ4Y  E4K99D4JR9E40VE1   \n",
       "150047  RR0VKJN8V0KHNKGG 2018-03-19  22EX9VZSJKHP4AIP  E4K99D4JR9E40VE1   \n",
       "150048  STJ6QJC30WPRM93H 2017-09-21  LMX18PNGWCIMG1QW  E4K99D4JR9E40VE1   \n",
       "150049  QHEUIYNC0XQX7GDR 2018-01-28  4U0VH2TGQL30X23X  E4K99D4JR9E40VE1   \n",
       "150050  NKHFWT5I2J9LPAPG 2017-09-06  ORWFRT5TUSYGNYG7  E4K99D4JR9E40VE1   \n",
       "\n",
       "                     Date of Meal                        Participants  \\\n",
       "0       2016-05-31 07:00:00+02:00                    ['David Bishop']   \n",
       "1       2018-10-01 20:00:00+02:00                    ['David Bishop']   \n",
       "2       2014-08-23 14:00:00+02:00                  ['Karen Stansell']   \n",
       "3       2014-04-07 21:00:00+02:00                    ['Addie Patino']   \n",
       "4       2015-07-27 14:00:00+02:00   ['Addie Patino' 'Susan Guerrero']   \n",
       "...                           ...                                 ...   \n",
       "150046  2017-09-21 08:00:00+02:00                  ['Regina Shirley']   \n",
       "150047  2018-03-18 09:00:00+01:00  ['Robin Ramos' 'Chester Mortimer']   \n",
       "150048  2017-09-22 21:00:00+02:00                     ['Robin Ramos']   \n",
       "150049  2018-02-01 21:00:00+01:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "150050  2017-09-09 14:00:00+02:00  ['Chester Mortimer' 'Robin Ramos']   \n",
       "\n",
       "        Heroes Adjustment Type of Meal  Expenses  \n",
       "0                   False    Breakfast     469.0  \n",
       "1                   False    Breakfast       0.0  \n",
       "2                   False    Breakfast       0.0  \n",
       "3                   False    Breakfast       0.0  \n",
       "4                   False    Breakfast       0.0  \n",
       "...                   ...          ...       ...  \n",
       "150046              False        Lunch       0.0  \n",
       "150047              False        Lunch       0.0  \n",
       "150048              False        Lunch       0.0  \n",
       "150049              False        Lunch       0.0  \n",
       "150050              False        Lunch     365.0  \n",
       "\n",
       "[150051 rows x 9 columns]"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(\n",
    "    frame = melt_exp,\n",
    "    id_vars = ['Order Id','Date','Meal Id','Company Id','Date of Meal','Participants','Heroes Adjustment'],\n",
    "    value_vars = ['Breakfast','Dinner','Lunch'],\n",
    "    var_name = 'Type of Meal',\n",
    "    value_name = 'Expenses'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "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>Type of Meal</th>\n",
       "      <th>Breakfast</th>\n",
       "      <th>Dinner</th>\n",
       "      <th>Lunch</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company Id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>007RF0BCBVMXTL80</th>\n",
       "      <td>301.666667</td>\n",
       "      <td>453.000000</td>\n",
       "      <td>112.000000</td>\n",
       "      <td>373.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00CVEIVO8C92FFBO</th>\n",
       "      <td>346.500000</td>\n",
       "      <td>425.000000</td>\n",
       "      <td>426.428571</td>\n",
       "      <td>403.285714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00OFNRZV8983KC3L</th>\n",
       "      <td>571.500000</td>\n",
       "      <td>665.333333</td>\n",
       "      <td>357.000000</td>\n",
       "      <td>550.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00R1H0DBLAVXHOTZ</th>\n",
       "      <td>778.000000</td>\n",
       "      <td>332.333333</td>\n",
       "      <td>694.200000</td>\n",
       "      <td>582.888889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00WJ866YXWT58K2S</th>\n",
       "      <td>392.166667</td>\n",
       "      <td>342.666667</td>\n",
       "      <td>383.600000</td>\n",
       "      <td>378.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZ99YI36NDHV7ELT</th>\n",
       "      <td>672.000000</td>\n",
       "      <td>341.000000</td>\n",
       "      <td>401.200000</td>\n",
       "      <td>504.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZD813K8WK65MND6</th>\n",
       "      <td>339.000000</td>\n",
       "      <td>82.000000</td>\n",
       "      <td>276.333333</td>\n",
       "      <td>250.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZY3WSHGJWPKJBCV</th>\n",
       "      <td>448.571429</td>\n",
       "      <td>351.428571</td>\n",
       "      <td>511.333333</td>\n",
       "      <td>419.647059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZYB9JLJKXRH7U6O</th>\n",
       "      <td>729.000000</td>\n",
       "      <td>336.000000</td>\n",
       "      <td>149.000000</td>\n",
       "      <td>480.636364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>404.625338</td>\n",
       "      <td>400.073093</td>\n",
       "      <td>405.087956</td>\n",
       "      <td>403.256233</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4726 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Type of Meal       Breakfast      Dinner       Lunch         All\n",
       "Company Id                                                      \n",
       "007RF0BCBVMXTL80  301.666667  453.000000  112.000000  373.500000\n",
       "00CVEIVO8C92FFBO  346.500000  425.000000  426.428571  403.285714\n",
       "00OFNRZV8983KC3L  571.500000  665.333333  357.000000  550.428571\n",
       "00R1H0DBLAVXHOTZ  778.000000  332.333333  694.200000  582.888889\n",
       "00WJ866YXWT58K2S  392.166667  342.666667  383.600000  378.500000\n",
       "...                      ...         ...         ...         ...\n",
       "ZZ99YI36NDHV7ELT  672.000000  341.000000  401.200000  504.000000\n",
       "ZZD813K8WK65MND6  339.000000   82.000000  276.333333  250.000000\n",
       "ZZY3WSHGJWPKJBCV  448.571429  351.428571  511.333333  419.647059\n",
       "ZZYB9JLJKXRH7U6O  729.000000  336.000000  149.000000  480.636364\n",
       "All               404.625338  400.073093  405.087956  403.256233\n",
       "\n",
       "[4726 rows x 4 columns]"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "pd.pivot_table(\n",
    "    invoices,\n",
    "    index = ['Company Id'],\n",
    "    columns = ['Type of Meal'],\n",
    "    values = 'Meal Price',\n",
    "    aggfunc = np.mean,\n",
    "    margins = True\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Meal Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company Id</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">007RF0BCBVMXTL80</th>\n",
       "      <th>Breakfast</th>\n",
       "      <td>301.666667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dinner</th>\n",
       "      <td>453.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lunch</th>\n",
       "      <td>112.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">00CVEIVO8C92FFBO</th>\n",
       "      <th>Breakfast</th>\n",
       "      <td>346.500000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dinner</th>\n",
       "      <td>425.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ZZY3WSHGJWPKJBCV</th>\n",
       "      <th>Dinner</th>\n",
       "      <td>351.428571</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lunch</th>\n",
       "      <td>511.333333</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">ZZYB9JLJKXRH7U6O</th>\n",
       "      <th>Breakfast</th>\n",
       "      <td>729.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dinner</th>\n",
       "      <td>336.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lunch</th>\n",
       "      <td>149.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14175 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Meal Price      \n",
       "Heroes Adjustment                   False True \n",
       "Company Id       Type of Meal                  \n",
       "007RF0BCBVMXTL80 Breakfast     301.666667   NaN\n",
       "                 Dinner        453.000000   NaN\n",
       "                 Lunch         112.000000   NaN\n",
       "00CVEIVO8C92FFBO Breakfast     346.500000   NaN\n",
       "                 Dinner        425.000000   NaN\n",
       "...                                   ...   ...\n",
       "ZZY3WSHGJWPKJBCV Dinner        351.428571   NaN\n",
       "                 Lunch         511.333333   NaN\n",
       "ZZYB9JLJKXRH7U6O Breakfast     729.000000   NaN\n",
       "                 Dinner        336.000000   NaN\n",
       "                 Lunch         149.000000   NaN\n",
       "\n",
       "[14175 rows x 2 columns]"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = invoices.groupby(['Company Id', 'Type of Meal','Heroes Adjustment']).agg({'Meal Price':np.mean})\n",
    "temp.unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"files/Unstack.png\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">Meal Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th>Breakfast</th>\n",
       "      <th>Dinner</th>\n",
       "      <th>Lunch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company Id</th>\n",
       "      <th>Heroes Adjustment</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">007RF0BCBVMXTL80</th>\n",
       "      <th>False</th>\n",
       "      <td>301.666667</td>\n",
       "      <td>453.000000</td>\n",
       "      <td>112.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">00CVEIVO8C92FFBO</th>\n",
       "      <th>False</th>\n",
       "      <td>346.500000</td>\n",
       "      <td>425.000000</td>\n",
       "      <td>426.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00OFNRZV8983KC3L</th>\n",
       "      <th>False</th>\n",
       "      <td>571.500000</td>\n",
       "      <td>665.333333</td>\n",
       "      <td>357.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZZD813K8WK65MND6</th>\n",
       "      <th>True</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ZZY3WSHGJWPKJBCV</th>\n",
       "      <th>False</th>\n",
       "      <td>448.571429</td>\n",
       "      <td>351.428571</td>\n",
       "      <td>511.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ZZYB9JLJKXRH7U6O</th>\n",
       "      <th>False</th>\n",
       "      <td>729.000000</td>\n",
       "      <td>336.000000</td>\n",
       "      <td>149.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9450 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Meal Price                        \n",
       "Type of Meal                         Breakfast      Dinner       Lunch\n",
       "Company Id       Heroes Adjustment                                    \n",
       "007RF0BCBVMXTL80 False              301.666667  453.000000  112.000000\n",
       "                 True                      NaN         NaN         NaN\n",
       "00CVEIVO8C92FFBO False              346.500000  425.000000  426.428571\n",
       "                 True                      NaN         NaN         NaN\n",
       "00OFNRZV8983KC3L False              571.500000  665.333333  357.000000\n",
       "...                                        ...         ...         ...\n",
       "ZZD813K8WK65MND6 True                      NaN         NaN         NaN\n",
       "ZZY3WSHGJWPKJBCV False              448.571429  351.428571  511.333333\n",
       "                 True                      NaN         NaN         NaN\n",
       "ZZYB9JLJKXRH7U6O False              729.000000  336.000000  149.000000\n",
       "                 True                      NaN         NaN         NaN\n",
       "\n",
       "[9450 rows x 3 columns]"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp.unstack(level = 'Type of Meal')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">Meal Price</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Date of Meal</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company Id</th>\n",
       "      <th>Type of Meal</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 rowspan=\"3\" valign=\"top\">007RF0BCBVMXTL80</th>\n",
       "      <th>Breakfast</th>\n",
       "      <td>545.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>301.666667</td>\n",
       "      <td>2015-09-17 08:00:00+02:00</td>\n",
       "      <td>2014-01-10 08:00:00+01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dinner</th>\n",
       "      <td>729.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>453.000000</td>\n",
       "      <td>2018-09-25 22:00:00+02:00</td>\n",
       "      <td>2014-12-29 21:00:00+01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lunch</th>\n",
       "      <td>112.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>112.000000</td>\n",
       "      <td>2015-11-27 13:00:00+01:00</td>\n",
       "      <td>2015-11-27 13:00:00+01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">00CVEIVO8C92FFBO</th>\n",
       "      <th>Breakfast</th>\n",
       "      <td>606.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>346.500000</td>\n",
       "      <td>2016-11-10 07:00:00+01:00</td>\n",
       "      <td>2015-01-17 08:00:00+01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dinner</th>\n",
       "      <td>631.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>425.000000</td>\n",
       "      <td>2017-11-19 22:00:00+01:00</td>\n",
       "      <td>2015-06-21 22:00:00+02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ZZY3WSHGJWPKJBCV</th>\n",
       "      <th>Dinner</th>\n",
       "      <td>826.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>351.428571</td>\n",
       "      <td>2018-05-18 22:00:00+02:00</td>\n",
       "      <td>2014-11-29 21:00:00+01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lunch</th>\n",
       "      <td>763.0</td>\n",
       "      <td>332.0</td>\n",
       "      <td>511.333333</td>\n",
       "      <td>2017-06-08 13:00:00+02:00</td>\n",
       "      <td>2016-05-13 13:00:00+02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">ZZYB9JLJKXRH7U6O</th>\n",
       "      <th>Breakfast</th>\n",
       "      <td>919.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>729.000000</td>\n",
       "      <td>2018-12-16 07:00:00+01:00</td>\n",
       "      <td>2014-01-19 07:00:00+01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dinner</th>\n",
       "      <td>564.0</td>\n",
       "      <td>244.0</td>\n",
       "      <td>336.000000</td>\n",
       "      <td>2016-02-07 20:00:00+01:00</td>\n",
       "      <td>2014-05-11 21:00:00+02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lunch</th>\n",
       "      <td>207.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>149.000000</td>\n",
       "      <td>2018-08-14 14:00:00+02:00</td>\n",
       "      <td>2018-03-04 13:00:00+01:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14175 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Meal Price                     \\\n",
       "                                     max    min        mean   \n",
       "Company Id       Type of Meal                                 \n",
       "007RF0BCBVMXTL80 Breakfast         545.0   74.0  301.666667   \n",
       "                 Dinner            729.0  156.0  453.000000   \n",
       "                 Lunch             112.0  112.0  112.000000   \n",
       "00CVEIVO8C92FFBO Breakfast         606.0  142.0  346.500000   \n",
       "                 Dinner            631.0  234.0  425.000000   \n",
       "...                                  ...    ...         ...   \n",
       "ZZY3WSHGJWPKJBCV Dinner            826.0   93.0  351.428571   \n",
       "                 Lunch             763.0  332.0  511.333333   \n",
       "ZZYB9JLJKXRH7U6O Breakfast         919.0  341.0  729.000000   \n",
       "                 Dinner            564.0  244.0  336.000000   \n",
       "                 Lunch             207.0   91.0  149.000000   \n",
       "\n",
       "                                            Date of Meal  \\\n",
       "                                                     max   \n",
       "Company Id       Type of Meal                              \n",
       "007RF0BCBVMXTL80 Breakfast     2015-09-17 08:00:00+02:00   \n",
       "                 Dinner        2018-09-25 22:00:00+02:00   \n",
       "                 Lunch         2015-11-27 13:00:00+01:00   \n",
       "00CVEIVO8C92FFBO Breakfast     2016-11-10 07:00:00+01:00   \n",
       "                 Dinner        2017-11-19 22:00:00+01:00   \n",
       "...                                                  ...   \n",
       "ZZY3WSHGJWPKJBCV Dinner        2018-05-18 22:00:00+02:00   \n",
       "                 Lunch         2017-06-08 13:00:00+02:00   \n",
       "ZZYB9JLJKXRH7U6O Breakfast     2018-12-16 07:00:00+01:00   \n",
       "                 Dinner        2016-02-07 20:00:00+01:00   \n",
       "                 Lunch         2018-08-14 14:00:00+02:00   \n",
       "\n",
       "                                                          \n",
       "                                                     min  \n",
       "Company Id       Type of Meal                             \n",
       "007RF0BCBVMXTL80 Breakfast     2014-01-10 08:00:00+01:00  \n",
       "                 Dinner        2014-12-29 21:00:00+01:00  \n",
       "                 Lunch         2015-11-27 13:00:00+01:00  \n",
       "00CVEIVO8C92FFBO Breakfast     2015-01-17 08:00:00+01:00  \n",
       "                 Dinner        2015-06-21 22:00:00+02:00  \n",
       "...                                                  ...  \n",
       "ZZY3WSHGJWPKJBCV Dinner        2014-11-29 21:00:00+01:00  \n",
       "                 Lunch         2016-05-13 13:00:00+02:00  \n",
       "ZZYB9JLJKXRH7U6O Breakfast     2014-01-19 07:00:00+01:00  \n",
       "                 Dinner        2014-05-11 21:00:00+02:00  \n",
       "                 Lunch         2018-03-04 13:00:00+01:00  \n",
       "\n",
       "[14175 rows x 5 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack_test = invoices.groupby(['Company Id','Type of Meal']).agg({\n",
    "    'Meal Price' : [max,min,np.mean],\n",
    "    'Date of Meal':[max,min]\n",
    "    \n",
    "})\n",
    "stack_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "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></th>\n",
       "      <th>Date of Meal</th>\n",
       "      <th>Meal Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company Id</th>\n",
       "      <th>Type of Meal</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">007RF0BCBVMXTL80</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Breakfast</th>\n",
       "      <th>max</th>\n",
       "      <td>2015-09-17 08:00:00+02:00</td>\n",
       "      <td>545.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>301.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2014-01-10 08:00:00+01:00</td>\n",
       "      <td>74.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Dinner</th>\n",
       "      <th>max</th>\n",
       "      <td>2018-09-25 22:00:00+02:00</td>\n",
       "      <td>729.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>453.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">ZZYB9JLJKXRH7U6O</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Dinner</th>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>336.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2014-05-11 21:00:00+02:00</td>\n",
       "      <td>244.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Lunch</th>\n",
       "      <th>max</th>\n",
       "      <td>2018-08-14 14:00:00+02:00</td>\n",
       "      <td>207.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>149.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2018-03-04 13:00:00+01:00</td>\n",
       "      <td>91.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41403 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Date of Meal  Meal Price\n",
       "Company Id       Type of Meal                                            \n",
       "007RF0BCBVMXTL80 Breakfast    max   2015-09-17 08:00:00+02:00  545.000000\n",
       "                              mean                        NaN  301.666667\n",
       "                              min   2014-01-10 08:00:00+01:00   74.000000\n",
       "                 Dinner       max   2018-09-25 22:00:00+02:00  729.000000\n",
       "                              mean                        NaN  453.000000\n",
       "...                                                       ...         ...\n",
       "ZZYB9JLJKXRH7U6O Dinner       mean                        NaN  336.000000\n",
       "                              min   2014-05-11 21:00:00+02:00  244.000000\n",
       "                 Lunch        max   2018-08-14 14:00:00+02:00  207.000000\n",
       "                              mean                        NaN  149.000000\n",
       "                              min   2018-03-04 13:00:00+01:00   91.000000\n",
       "\n",
       "[41403 rows x 2 columns]"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack_test.stack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning Projects"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}