pycon_AU_online_2020.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# <h1 align='center'>Extracting Data from Excel using Python</h1>\n",
    "**<center><font color='grey'>Speaker: Samuel Oranyeli</font></center>**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### **Import libraries**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:04.101546Z",
     "iopub.status.busy": "2020-09-03T05:41:04.101283Z",
     "iopub.status.idle": "2020-09-03T05:41:04.841255Z",
     "shell.execute_reply": "2020-09-03T05:41:04.840431Z",
     "shell.execute_reply.started": "2020-09-03T05:41:04.101520Z"
    },
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import janitor\n",
    "from openpyxl import load_workbook\n",
    "from collections import defaultdict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Reading Excel data into Pandas is easy : "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "- Read a single file: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:04.845050Z",
     "iopub.status.busy": "2020-09-03T05:41:04.844864Z",
     "iopub.status.idle": "2020-09-03T05:41:04.877039Z",
     "shell.execute_reply": "2020-09-03T05:41:04.876246Z",
     "shell.execute_reply.started": "2020-09-03T05:41:04.845028Z"
    },
    "hideOutput": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tolu</td>\n",
       "      <td>24</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chukwuka</td>\n",
       "      <td>50</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ogor</td>\n",
       "      <td>15</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Name  Age  Height\n",
       "0      Tolu   24     2.0\n",
       "1  Chukwuka   50     1.8\n",
       "2      Ogor   15     1.5"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(io = \"names.xlsx\", sheet_name = \"naija\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "- Read in multiple sheets from a single file: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:04.878549Z",
     "iopub.status.busy": "2020-09-03T05:41:04.878315Z",
     "iopub.status.idle": "2020-09-03T05:41:04.954623Z",
     "shell.execute_reply": "2020-09-03T05:41:04.953855Z",
     "shell.execute_reply.started": "2020-09-03T05:41:04.878524Z"
    },
    "hideOutput": true,
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'naija':        Name  Age  Height\n",
       " 0      Tolu   24     2.0\n",
       " 1  Chukwuka   50     1.8\n",
       " 2      Ogor   15     1.5,\n",
       " 'vikings':      Name  Age  Height\n",
       " 0  ragnar   32     1.6\n",
       " 1   bjorn   49     2.2\n",
       " 2    loki  400     1.8}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(io = \"names.xlsx\", sheet_name = [\"naija\", \"vikings\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "- Read in all the sheets from a single file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:04.956009Z",
     "iopub.status.busy": "2020-09-03T05:41:04.955776Z",
     "iopub.status.idle": "2020-09-03T05:41:05.054303Z",
     "shell.execute_reply": "2020-09-03T05:41:05.053667Z",
     "shell.execute_reply.started": "2020-09-03T05:41:04.955984Z"
    },
    "hideOutput": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'naija':        Name  Age  Height\n",
       " 0      Tolu   24     2.0\n",
       " 1  Chukwuka   50     1.8\n",
       " 2      Ogor   15     1.5,\n",
       " 'vikings':      Name  Age  Height\n",
       " 0  ragnar   32     1.6\n",
       " 1   bjorn   49     2.2\n",
       " 2    loki  400     1.8,\n",
       " 'olympus':        Name  Age  Height\n",
       " 0      zeus   65     1.9\n",
       " 1  poseidon   26     2.2\n",
       " 2    athena   30     1.4,\n",
       " 'defined_tables':        Name  Age  Height  Unnamed: 3  Name.1  Age.1  Height.1  Unnamed: 7  \\\n",
       " 0      Tolu   24     2.0         NaN  ragnar     32       1.6         NaN   \n",
       " 1  Chukwuka   50     1.8         NaN   bjorn     49       2.2         NaN   \n",
       " 2      Ogor   15     1.5         NaN    loki    400       1.8         NaN   \n",
       " \n",
       "      Name.2  Age.2  Height.2  \n",
       " 0      zeus     65       1.9  \n",
       " 1  poseidon     26       2.2  \n",
       " 2    athena     30       1.4  ,\n",
       " 'position':     Unnamed: 0  Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4  Unnamed: 5  \\\n",
       " 0          NaN         NaN       Name        Age     Height         NaN   \n",
       " 1          NaN         NaN       Tolu         24          2         NaN   \n",
       " 2          NaN         NaN   Chukwuka         50        1.8         NaN   \n",
       " 3          NaN         NaN       Ogor         15        1.5         NaN   \n",
       " 4          NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 5          NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 6          NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 7          NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 8          NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 9          NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 10         NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 11         NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 12         NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 13         NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 14         NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 15         NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " 16         NaN         NaN        NaN        NaN        NaN         NaN   \n",
       " \n",
       "    Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10  \n",
       " 0         NaN        NaN        NaN        NaN         NaN  \n",
       " 1         NaN        NaN        NaN        NaN         NaN  \n",
       " 2         NaN        NaN        NaN        NaN         NaN  \n",
       " 3         NaN        NaN        NaN        NaN         NaN  \n",
       " 4         NaN        NaN        NaN        NaN         NaN  \n",
       " 5         NaN        NaN       Name        Age      Height  \n",
       " 6         NaN        NaN       zeus         65         1.9  \n",
       " 7         NaN        NaN   poseidon         26         2.2  \n",
       " 8         NaN        NaN     athena         30         1.4  \n",
       " 9         NaN        NaN        NaN        NaN         NaN  \n",
       " 10        NaN        NaN        NaN        NaN         NaN  \n",
       " 11        NaN        NaN        NaN        NaN         NaN  \n",
       " 12        NaN        NaN        NaN        NaN         NaN  \n",
       " 13       Name        Age     Height        NaN         NaN  \n",
       " 14     ragnar         32        1.6        NaN         NaN  \n",
       " 15      bjorn         49        2.2        NaN         NaN  \n",
       " 16       loki        400        1.8        NaN         NaN  }"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(io = \"names.xlsx\", sheet_name= None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "For multiple sheets, a dictionary is returned, with the sheetname as key, and the dataframe as the value."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "- If the sheets have the same columns, you can combine them into one dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.055354Z",
     "iopub.status.busy": "2020-09-03T05:41:05.055159Z",
     "iopub.status.idle": "2020-09-03T05:41:05.118778Z",
     "shell.execute_reply": "2020-09-03T05:41:05.118089Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.055332Z"
    },
    "hideOutput": true,
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">naija</th>\n",
       "      <th>0</th>\n",
       "      <td>Tolu</td>\n",
       "      <td>24</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chukwuka</td>\n",
       "      <td>50</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ogor</td>\n",
       "      <td>15</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">vikings</th>\n",
       "      <th>0</th>\n",
       "      <td>ragnar</td>\n",
       "      <td>32</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bjorn</td>\n",
       "      <td>49</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>loki</td>\n",
       "      <td>400</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">olympus</th>\n",
       "      <th>0</th>\n",
       "      <td>zeus</td>\n",
       "      <td>65</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>poseidon</td>\n",
       "      <td>26</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>athena</td>\n",
       "      <td>30</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Name  Age  Height\n",
       "naija   0      Tolu   24     2.0\n",
       "        1  Chukwuka   50     1.8\n",
       "        2      Ogor   15     1.5\n",
       "vikings 0    ragnar   32     1.6\n",
       "        1     bjorn   49     2.2\n",
       "        2      loki  400     1.8\n",
       "olympus 0      zeus   65     1.9\n",
       "        1  poseidon   26     2.2\n",
       "        2    athena   30     1.4"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "excel_dfs = pd.read_excel(io = \"names.xlsx\", sheet_name= [\"naija\", \"vikings\", \"olympus\"])\n",
    "pd.concat(excel_dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The sheet names become the indices of the new dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.119933Z",
     "iopub.status.busy": "2020-09-03T05:41:05.119754Z",
     "iopub.status.idle": "2020-09-03T05:41:05.178776Z",
     "shell.execute_reply": "2020-09-03T05:41:05.178191Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.119911Z"
    }
   },
   "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>Sheet_name</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>naija</td>\n",
       "      <td>Tolu</td>\n",
       "      <td>24</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>naija</td>\n",
       "      <td>Chukwuka</td>\n",
       "      <td>50</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>naija</td>\n",
       "      <td>Ogor</td>\n",
       "      <td>15</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>vikings</td>\n",
       "      <td>ragnar</td>\n",
       "      <td>32</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>vikings</td>\n",
       "      <td>bjorn</td>\n",
       "      <td>49</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>vikings</td>\n",
       "      <td>loki</td>\n",
       "      <td>400</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>olympus</td>\n",
       "      <td>zeus</td>\n",
       "      <td>65</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>olympus</td>\n",
       "      <td>poseidon</td>\n",
       "      <td>26</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>olympus</td>\n",
       "      <td>athena</td>\n",
       "      <td>30</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Sheet_name      Name  Age  Height\n",
       "0      naija      Tolu   24     2.0\n",
       "1      naija  Chukwuka   50     1.8\n",
       "2      naija      Ogor   15     1.5\n",
       "3    vikings    ragnar   32     1.6\n",
       "4    vikings     bjorn   49     2.2\n",
       "5    vikings      loki  400     1.8\n",
       "6    olympus      zeus   65     1.9\n",
       "7    olympus  poseidon   26     2.2\n",
       "8    olympus    athena   30     1.4"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#cleaner output\n",
    "pd.concat(excel_dfs).droplevel(-1).rename_axis(index=\"Sheet_name\").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.179923Z",
     "iopub.status.busy": "2020-09-03T05:41:05.179731Z",
     "iopub.status.idle": "2020-09-03T05:41:05.311723Z",
     "shell.execute_reply": "2020-09-03T05:41:05.311087Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.179902Z"
    }
   },
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tolu</td>\n",
       "      <td>24</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Chukwuka</td>\n",
       "      <td>50</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ogor</td>\n",
       "      <td>15</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Name  Age  Height\n",
       "0      Tolu   24     2.0\n",
       "1  Chukwuka   50     1.8\n",
       "2      Ogor   15     1.5"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#if data does not start at \"A\" or row 1\n",
    "pd.read_excel(\"names.xlsx\", sheet_name = \"position\", usecols = \"C:E\", header = 1, nrows=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.314430Z",
     "iopub.status.busy": "2020-09-03T05:41:05.314184Z",
     "iopub.status.idle": "2020-09-03T05:41:05.400180Z",
     "shell.execute_reply": "2020-09-03T05:41:05.399488Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.314405Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'naija':        Name  Age  Height\n",
       " 0      Tolu   24     2.0\n",
       " 1  Chukwuka   50     1.8\n",
       " 2      Ogor   15     1.5,\n",
       " 'vikings':      Name  Age  Height\n",
       " 0  ragnar   32     1.6\n",
       " 1   bjorn   49     2.2\n",
       " 2    loki  400     1.8,\n",
       " 'olympus':        Name  Age  Height\n",
       " 0      zeus   65     1.9\n",
       " 1  poseidon   26     2.2\n",
       " 2    athena   30     1.4}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read in all excel files in a folder\n",
    "# you can read it in as a dictionary\n",
    "from pathlib import Path\n",
    "folder = Path(\"excel_files\")\n",
    "excel_data = {filename.stem : pd.read_excel(filename) for filename in folder.iterdir()}\n",
    "excel_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.401807Z",
     "iopub.status.busy": "2020-09-03T05:41:05.401620Z",
     "iopub.status.idle": "2020-09-03T05:41:05.476782Z",
     "shell.execute_reply": "2020-09-03T05:41:05.475945Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.401786Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'naija':        Name  Age  Height\n",
       " 0      Tolu   24     2.0\n",
       " 1  Chukwuka   50     1.8\n",
       " 2      Ogor   15     1.5,\n",
       " 'vikings':      Name  Age  Height\n",
       " 0  ragnar   32     1.6\n",
       " 1   bjorn   49     2.2\n",
       " 2    loki  400     1.8,\n",
       " 'olympus':        Name  Age  Height\n",
       " 0      zeus   65     1.9\n",
       " 1  poseidon   26     2.2\n",
       " 2    athena   30     1.4}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read only the excel files in a folder\n",
    "folder = Path(\"mixed_files\").rglob(\"*.[xX]*\")\n",
    "excel_data = {filename.stem : pd.read_excel(filename) \n",
    "                              for filename in folder}\n",
    "excel_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Pivot Table with multiple headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.477813Z",
     "iopub.status.busy": "2020-09-03T05:41:05.477610Z",
     "iopub.status.idle": "2020-09-03T05:41:05.556396Z",
     "shell.execute_reply": "2020-09-03T05:41:05.555544Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.477789Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Age</th>\n",
       "      <th>Child</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Adult</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Survived</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Class</td>\n",
       "      <td>Sex</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>1st</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>118</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>154</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>13</td>\n",
       "      <td>387</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>14</td>\n",
       "      <td>89</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>670</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0 Unnamed: 1       Age Child Unnamed: 4 Adult Unnamed: 6\n",
       "0        NaN        NaN  Survived    No        Yes    No        Yes\n",
       "1      Class        Sex       NaN   NaN        NaN   NaN        NaN\n",
       "2        1st       Male       NaN     0          5   118         57\n",
       "3        NaN     Female       NaN     0          1     4        140\n",
       "4        2nd       Male       NaN     0         11   154         14\n",
       "5        NaN     Female       NaN     0         13    13         80\n",
       "6        3rd       Male       NaN    35         13   387         75\n",
       "7        NaN     Female       NaN    17         14    89         76\n",
       "8       Crew       Male       NaN     0          0   670        192\n",
       "9        NaN     Female       NaN     0          0     3         20"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel('titanic.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.557881Z",
     "iopub.status.busy": "2020-09-03T05:41:05.557530Z",
     "iopub.status.idle": "2020-09-03T05:41:05.621586Z",
     "shell.execute_reply": "2020-09-03T05:41:05.620671Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.557852Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>Age</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Child</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Adult</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Survived</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Class</th>\n",
       "      <th>Sex</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=\"2\" valign=\"top\">1st</th>\n",
       "      <th>Male</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>118</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2nd</th>\n",
       "      <th>Male</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>154</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3rd</th>\n",
       "      <th>Male</th>\n",
       "      <td>NaN</td>\n",
       "      <td>35</td>\n",
       "      <td>13</td>\n",
       "      <td>387</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>14</td>\n",
       "      <td>89</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Crew</th>\n",
       "      <th>Male</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>670</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Female</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Age Child     Adult     \n",
       "             Survived    No Yes    No  Yes\n",
       "Class Sex                                 \n",
       "1st   Male        NaN     0   5   118   57\n",
       "      Female      NaN     0   1     4  140\n",
       "2nd   Male        NaN     0  11   154   14\n",
       "      Female      NaN     0  13    13   80\n",
       "3rd   Male        NaN    35  13   387   75\n",
       "      Female      NaN    17  14    89   76\n",
       "Crew  Male        NaN     0   0   670  192\n",
       "      Female      NaN     0   0     3   20"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel('titanic.xlsx', header = [0,1], index_col=[0,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.622762Z",
     "iopub.status.busy": "2020-09-03T05:41:05.622567Z",
     "iopub.status.idle": "2020-09-03T05:41:05.769058Z",
     "shell.execute_reply": "2020-09-03T05:41:05.766565Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.622739Z"
    },
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>Class</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Adult_or_Child</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Numbers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1st</td>\n",
       "      <td>Male</td>\n",
       "      <td>Adult</td>\n",
       "      <td>No</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1st</td>\n",
       "      <td>Male</td>\n",
       "      <td>Adult</td>\n",
       "      <td>Yes</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1st</td>\n",
       "      <td>Male</td>\n",
       "      <td>Child</td>\n",
       "      <td>No</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1st</td>\n",
       "      <td>Male</td>\n",
       "      <td>Child</td>\n",
       "      <td>Yes</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1st</td>\n",
       "      <td>Female</td>\n",
       "      <td>Adult</td>\n",
       "      <td>No</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1st</td>\n",
       "      <td>Female</td>\n",
       "      <td>Adult</td>\n",
       "      <td>Yes</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1st</td>\n",
       "      <td>Female</td>\n",
       "      <td>Child</td>\n",
       "      <td>No</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1st</td>\n",
       "      <td>Female</td>\n",
       "      <td>Child</td>\n",
       "      <td>Yes</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Male</td>\n",
       "      <td>Adult</td>\n",
       "      <td>No</td>\n",
       "      <td>154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Male</td>\n",
       "      <td>Adult</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Male</td>\n",
       "      <td>Child</td>\n",
       "      <td>No</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Male</td>\n",
       "      <td>Child</td>\n",
       "      <td>Yes</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Female</td>\n",
       "      <td>Adult</td>\n",
       "      <td>No</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Female</td>\n",
       "      <td>Adult</td>\n",
       "      <td>Yes</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Female</td>\n",
       "      <td>Child</td>\n",
       "      <td>No</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2nd</td>\n",
       "      <td>Female</td>\n",
       "      <td>Child</td>\n",
       "      <td>Yes</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Male</td>\n",
       "      <td>Adult</td>\n",
       "      <td>No</td>\n",
       "      <td>387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Male</td>\n",
       "      <td>Adult</td>\n",
       "      <td>Yes</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Male</td>\n",
       "      <td>Child</td>\n",
       "      <td>No</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Male</td>\n",
       "      <td>Child</td>\n",
       "      <td>Yes</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Female</td>\n",
       "      <td>Adult</td>\n",
       "      <td>No</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Female</td>\n",
       "      <td>Adult</td>\n",
       "      <td>Yes</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Female</td>\n",
       "      <td>Child</td>\n",
       "      <td>No</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>3rd</td>\n",
       "      <td>Female</td>\n",
       "      <td>Child</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Male</td>\n",
       "      <td>Adult</td>\n",
       "      <td>No</td>\n",
       "      <td>670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Male</td>\n",
       "      <td>Adult</td>\n",
       "      <td>Yes</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Male</td>\n",
       "      <td>Child</td>\n",
       "      <td>No</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Male</td>\n",
       "      <td>Child</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Female</td>\n",
       "      <td>Adult</td>\n",
       "      <td>No</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Female</td>\n",
       "      <td>Adult</td>\n",
       "      <td>Yes</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Female</td>\n",
       "      <td>Child</td>\n",
       "      <td>No</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Crew</td>\n",
       "      <td>Female</td>\n",
       "      <td>Child</td>\n",
       "      <td>Yes</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Class     Sex Adult_or_Child Survived  Numbers\n",
       "0    1st    Male          Adult       No      118\n",
       "1    1st    Male          Adult      Yes       57\n",
       "2    1st    Male          Child       No        0\n",
       "3    1st    Male          Child      Yes        5\n",
       "4    1st  Female          Adult       No        4\n",
       "5    1st  Female          Adult      Yes      140\n",
       "6    1st  Female          Child       No        0\n",
       "7    1st  Female          Child      Yes        1\n",
       "8    2nd    Male          Adult       No      154\n",
       "9    2nd    Male          Adult      Yes       14\n",
       "10   2nd    Male          Child       No        0\n",
       "11   2nd    Male          Child      Yes       11\n",
       "12   2nd  Female          Adult       No       13\n",
       "13   2nd  Female          Adult      Yes       80\n",
       "14   2nd  Female          Child       No        0\n",
       "15   2nd  Female          Child      Yes       13\n",
       "16   3rd    Male          Adult       No      387\n",
       "17   3rd    Male          Adult      Yes       75\n",
       "18   3rd    Male          Child       No       35\n",
       "19   3rd    Male          Child      Yes       13\n",
       "20   3rd  Female          Adult       No       89\n",
       "21   3rd  Female          Adult      Yes       76\n",
       "22   3rd  Female          Child       No       17\n",
       "23   3rd  Female          Child      Yes       14\n",
       "24  Crew    Male          Adult       No      670\n",
       "25  Crew    Male          Adult      Yes      192\n",
       "26  Crew    Male          Child       No        0\n",
       "27  Crew    Male          Child      Yes        0\n",
       "28  Crew  Female          Adult       No        3\n",
       "29  Crew  Female          Adult      Yes       20\n",
       "30  Crew  Female          Child       No        0\n",
       "31  Crew  Female          Child      Yes        0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.read_excel('titanic.xlsx', header = [0,1], index_col=[0,1])\n",
    " .rename_axis(columns=[\"Adult_or_Child\",\"Survived\"])\n",
    " .dropna(how='all',axis=1)\n",
    " .stack([\"Adult_or_Child\",\"Survived\"])\n",
    " .reset_index(name=\"Numbers\")\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Excel Tables: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.770232Z",
     "iopub.status.busy": "2020-09-03T05:41:05.770031Z",
     "iopub.status.idle": "2020-09-03T05:41:05.797631Z",
     "shell.execute_reply": "2020-09-03T05:41:05.797057Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.770209Z"
    }
   },
   "outputs": [],
   "source": [
    "#read excel file\n",
    "wb  = load_workbook(filename = \"names.xlsx\")\n",
    "\n",
    "#read specific sheet\n",
    "ws = wb['defined_tables']\n",
    "\n",
    "named_tables = {}\n",
    "for table_name, value in ws.tables.items():\n",
    "    dataframe = ws[value]\n",
    "    header, *body = [[cell.value for cell in row] for row in dataframe]\n",
    "    dataframe = pd.DataFrame(body, columns = header)\n",
    "    named_tables[table_name] = dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.798621Z",
     "iopub.status.busy": "2020-09-03T05:41:05.798435Z",
     "iopub.status.idle": "2020-09-03T05:41:05.851741Z",
     "shell.execute_reply": "2020-09-03T05:41:05.851132Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.798600Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'naija':        Name  Age  Height\n",
       " 0      Tolu   24     2.0\n",
       " 1  Chukwuka   50     1.8\n",
       " 2      Ogor   15     1.5,\n",
       " 'olympus':        Name  Age  Height\n",
       " 0      zeus   65     1.9\n",
       " 1  poseidon   26     2.2\n",
       " 2    athena   30     1.4,\n",
       " 'vikings':      Name  Age  Height\n",
       " 0  ragnar   32     1.6\n",
       " 1   bjorn   49     2.2\n",
       " 2    loki  400     1.8}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "named_tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.852689Z",
     "iopub.status.busy": "2020-09-03T05:41:05.852484Z",
     "iopub.status.idle": "2020-09-03T05:41:05.958179Z",
     "shell.execute_reply": "2020-09-03T05:41:05.957146Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.852666Z"
    }
   },
   "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>Table_name</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>naija</td>\n",
       "      <td>Tolu</td>\n",
       "      <td>24</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>naija</td>\n",
       "      <td>Chukwuka</td>\n",
       "      <td>50</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>naija</td>\n",
       "      <td>Ogor</td>\n",
       "      <td>15</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>olympus</td>\n",
       "      <td>zeus</td>\n",
       "      <td>65</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>olympus</td>\n",
       "      <td>poseidon</td>\n",
       "      <td>26</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>olympus</td>\n",
       "      <td>athena</td>\n",
       "      <td>30</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>vikings</td>\n",
       "      <td>ragnar</td>\n",
       "      <td>32</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>vikings</td>\n",
       "      <td>bjorn</td>\n",
       "      <td>49</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>vikings</td>\n",
       "      <td>loki</td>\n",
       "      <td>400</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Table_name      Name  Age  Height\n",
       "0      naija      Tolu   24     2.0\n",
       "1      naija  Chukwuka   50     1.8\n",
       "2      naija      Ogor   15     1.5\n",
       "3    olympus      zeus   65     1.9\n",
       "4    olympus  poseidon   26     2.2\n",
       "5    olympus    athena   30     1.4\n",
       "6    vikings    ragnar   32     1.6\n",
       "7    vikings     bjorn   49     2.2\n",
       "8    vikings      loki  400     1.8"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(named_tables).droplevel(-1).rename_axis(index=\"Table_name\").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:05.959569Z",
     "iopub.status.busy": "2020-09-03T05:41:05.959211Z",
     "iopub.status.idle": "2020-09-03T05:41:06.058528Z",
     "shell.execute_reply": "2020-09-03T05:41:06.057928Z",
     "shell.execute_reply.started": "2020-09-03T05:41:05.959530Z"
    }
   },
   "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>Sheet_name</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>naija</td>\n",
       "      <td>Tolu</td>\n",
       "      <td>24</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>naija</td>\n",
       "      <td>Chukwuka</td>\n",
       "      <td>50</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>naija</td>\n",
       "      <td>Ogor</td>\n",
       "      <td>15</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>olympus</td>\n",
       "      <td>zeus</td>\n",
       "      <td>65</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>olympus</td>\n",
       "      <td>poseidon</td>\n",
       "      <td>26</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>olympus</td>\n",
       "      <td>athena</td>\n",
       "      <td>30</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>vikings</td>\n",
       "      <td>ragnar</td>\n",
       "      <td>32</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>vikings</td>\n",
       "      <td>bjorn</td>\n",
       "      <td>49</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>vikings</td>\n",
       "      <td>loki</td>\n",
       "      <td>400</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Sheet_name      Name  Age  Height\n",
       "0      naija      Tolu   24     2.0\n",
       "1      naija  Chukwuka   50     1.8\n",
       "2      naija      Ogor   15     1.5\n",
       "3    olympus      zeus   65     1.9\n",
       "4    olympus  poseidon   26     2.2\n",
       "5    olympus    athena   30     1.4\n",
       "6    vikings    ragnar   32     1.6\n",
       "7    vikings     bjorn   49     2.2\n",
       "8    vikings      loki  400     1.8"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#complete code :\n",
    "\n",
    "#read excel file\n",
    "wb  = load_workbook(filename = \"names.xlsx\")\n",
    "\n",
    "#read specific sheet\n",
    "ws = wb['defined_tables']\n",
    "\n",
    "named_tables = {}\n",
    "for table_name, value in ws.tables.items():\n",
    "    dataframe = ws[value]\n",
    "    header, *body = [[cell.value for cell in row] for row in dataframe]\n",
    "    dataframe = pd.DataFrame(body, columns = header)\n",
    "    named_tables[table_name] = dataframe\n",
    "    \n",
    "(pd.concat(named_tables)\n",
    " .droplevel(-1)\n",
    " .rename_axis(index=\"Sheet_name\")\n",
    " .reset_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conditional Formatting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.059840Z",
     "iopub.status.busy": "2020-09-03T05:41:06.059468Z",
     "iopub.status.idle": "2020-09-03T05:41:06.124849Z",
     "shell.execute_reply": "2020-09-03T05:41:06.124087Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.059816Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defaultdict(list,\n",
       "            {'C1': ['Name', 'zeus', 'poseidon', 'athena'],\n",
       "             'C2': ['Age', '65,00000000', '26,FFFFFF00', '30,00000000'],\n",
       "             'C3': ['Height', '1.9,00000000', '2.2,00000000', '1.4,FF00A933']})"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read excel file\n",
    "wb  = load_workbook(filename = \"names.xlsx\")\n",
    "\n",
    "#read specific sheet\n",
    "ws = wb[\"olympus\"]\n",
    "\n",
    "data = defaultdict(list)\n",
    "for column in ws.columns:\n",
    "    for cell in column:\n",
    "        if cell.data_type == \"n\":\n",
    "            cell_value = \",\".join((str(cell.value), cell.fill.fgColor.rgb))\n",
    "            data[f\"C{cell.column}\"].append(cell_value)\n",
    "        else:\n",
    "            data[f\"C{cell.column}\"].append(cell.value)\n",
    "            \n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.125850Z",
     "iopub.status.busy": "2020-09-03T05:41:06.125651Z",
     "iopub.status.idle": "2020-09-03T05:41:06.211775Z",
     "shell.execute_reply": "2020-09-03T05:41:06.211170Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.125821Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Variable</th>\n",
       "      <th>Value</th>\n",
       "      <th>Colour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>zeus</td>\n",
       "      <td>Age</td>\n",
       "      <td>65</td>\n",
       "      <td>00000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>zeus</td>\n",
       "      <td>Height</td>\n",
       "      <td>1.9</td>\n",
       "      <td>00000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>poseidon</td>\n",
       "      <td>Age</td>\n",
       "      <td>26</td>\n",
       "      <td>FFFFFF00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>poseidon</td>\n",
       "      <td>Height</td>\n",
       "      <td>2.2</td>\n",
       "      <td>00000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>athena</td>\n",
       "      <td>Age</td>\n",
       "      <td>30</td>\n",
       "      <td>00000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>athena</td>\n",
       "      <td>Height</td>\n",
       "      <td>1.4</td>\n",
       "      <td>FF00A933</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Name Variable Value    Colour\n",
       "0      zeus      Age    65  00000000\n",
       "1      zeus   Height   1.9  00000000\n",
       "2  poseidon      Age    26  FFFFFF00\n",
       "3  poseidon   Height   2.2  00000000\n",
       "4    athena      Age    30  00000000\n",
       "5    athena   Height   1.4  FF00A933"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.DataFrame(data)\n",
    " .row_to_names(row_number = 0,remove_row = True) #pyjanitor\n",
    " .set_index(\"Name\")\n",
    " .stack()\n",
    " .str.split(\",\",expand=True)\n",
    " .set_axis([\"Value\",\"Colour\"], axis=\"columns\")\n",
    " .rename_axis(index=[\"Name\",\"Variable\"])\n",
    " .reset_index()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sheets with comments :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.212955Z",
     "iopub.status.busy": "2020-09-03T05:41:06.212658Z",
     "iopub.status.idle": "2020-09-03T05:41:06.293292Z",
     "shell.execute_reply": "2020-09-03T05:41:06.292643Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.212932Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defaultdict(list,\n",
       "            {1: ['Name', 'Age', 'Height'],\n",
       "             2: ['ragnar', 32, 1.6],\n",
       "             3: ['bjorn', 49, 2.2],\n",
       "             4: ['loki', 400, 1.8]})"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read excel file\n",
    "wb  = load_workbook(filename = \"names.xlsx\")\n",
    "\n",
    "#read specific sheet\n",
    "ws = wb[\"vikings\"]\n",
    "\n",
    "data = defaultdict(list)\n",
    "comments = {}\n",
    "for column in ws.columns:\n",
    "    for cell in column:\n",
    "        if cell.comment:\n",
    "            comments[cell.row] = cell.comment.text\n",
    "        data[cell.row].append(cell.value)\n",
    "            \n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.294306Z",
     "iopub.status.busy": "2020-09-03T05:41:06.294127Z",
     "iopub.status.idle": "2020-09-03T05:41:06.354493Z",
     "shell.execute_reply": "2020-09-03T05:41:06.353811Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.294284Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{2: 'Medieval warlord', 4: 'Not sure about the age'}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.355512Z",
     "iopub.status.busy": "2020-09-03T05:41:06.355328Z",
     "iopub.status.idle": "2020-09-03T05:41:06.420174Z",
     "shell.execute_reply": "2020-09-03T05:41:06.419486Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.355491Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{1: ['Name', 'Age', 'Height', None],\n",
       " 2: ['ragnar', 32, 1.6, 'Medieval warlord'],\n",
       " 3: ['bjorn', 49, 2.2, None],\n",
       " 4: ['loki', 400, 1.8, 'Not sure about the age']}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_data = {}\n",
    "for key, value in data.items():\n",
    "    value.append(comments.get(key, None))\n",
    "    new_data[key] = value\n",
    "    \n",
    "new_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.421310Z",
     "iopub.status.busy": "2020-09-03T05:41:06.421087Z",
     "iopub.status.idle": "2020-09-03T05:41:06.512507Z",
     "shell.execute_reply": "2020-09-03T05:41:06.511732Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.421285Z"
    }
   },
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ragnar</td>\n",
       "      <td>32</td>\n",
       "      <td>1.6</td>\n",
       "      <td>Medieval warlord</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bjorn</td>\n",
       "      <td>49</td>\n",
       "      <td>2.2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>loki</td>\n",
       "      <td>400</td>\n",
       "      <td>1.8</td>\n",
       "      <td>Not sure about the age</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Name  Age Height                comments\n",
       "1  ragnar   32    1.6        Medieval warlord\n",
       "2   bjorn   49    2.2                    None\n",
       "3    loki  400    1.8  Not sure about the age"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(new_data.values()).fillna(\"comments\", limit=1).row_to_names(0,True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.513706Z",
     "iopub.status.busy": "2020-09-03T05:41:06.513526Z",
     "iopub.status.idle": "2020-09-03T05:41:06.633911Z",
     "shell.execute_reply": "2020-09-03T05:41:06.633062Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.513684Z"
    }
   },
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ragnar</td>\n",
       "      <td>32</td>\n",
       "      <td>1.6</td>\n",
       "      <td>Medieval warlord</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bjorn</td>\n",
       "      <td>49</td>\n",
       "      <td>2.2</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>loki</td>\n",
       "      <td>400</td>\n",
       "      <td>1.8</td>\n",
       "      <td>Not sure about the age</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Name  Age Height                comments\n",
       "1  ragnar   32    1.6        Medieval warlord\n",
       "2   bjorn   49    2.2                    None\n",
       "3    loki  400    1.8  Not sure about the age"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#complete code : \n",
    "\n",
    "wb  = load_workbook(filename = \"names.xlsx\")\n",
    "ws = wb[\"vikings\"]\n",
    "\n",
    "data = defaultdict(list)\n",
    "\n",
    "comments = {}\n",
    "for column in ws.columns:\n",
    "    for cell in column:\n",
    "        if cell.comment:\n",
    "            comments[cell.row] = cell.comment.text\n",
    "        data[cell.row].append(cell.value)\n",
    "\n",
    "new_data = {}\n",
    "for key, value in data.items():\n",
    "    value.append(comments.get(key, None))\n",
    "    new_data[key] = value\n",
    "    \n",
    "(pd.DataFrame(new_data.values()).\n",
    " fillna(\"comments\", limit=1)\n",
    " .row_to_names(0,True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Small Multiples : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.634910Z",
     "iopub.status.busy": "2020-09-03T05:41:06.634732Z",
     "iopub.status.idle": "2020-09-03T05:41:06.960756Z",
     "shell.execute_reply": "2020-09-03T05:41:06.959949Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.634888Z"
    }
   },
   "outputs": [],
   "source": [
    "#read excel file\n",
    "wb  = load_workbook(filename = \"enron.xlsx\")\n",
    "\n",
    "#read specific sheet\n",
    "ws = wb[\"Report\"]\n",
    "\n",
    "rows = set()\n",
    "columns = set()\n",
    "data = []\n",
    "for row in ws.rows:\n",
    "    for cell in row:\n",
    "        if cell.value in (\"BID\", \"OFFER\") :\n",
    "            rows.add(cell.row)\n",
    "            columns.add(cell.column)\n",
    "        data.append((cell.value, cell.row, cell.column))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.961605Z",
     "iopub.status.busy": "2020-09-03T05:41:06.961435Z",
     "iopub.status.idle": "2020-09-03T05:41:06.966101Z",
     "shell.execute_reply": "2020-09-03T05:41:06.965337Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.961584Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{16, 27, 38, 49}"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:06.968989Z",
     "iopub.status.busy": "2020-09-03T05:41:06.968806Z",
     "iopub.status.idle": "2020-09-03T05:41:07.053926Z",
     "shell.execute_reply": "2020-09-03T05:41:07.053283Z",
     "shell.execute_reply.started": "2020-09-03T05:41:06.968968Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[range(14, 25), range(25, 36), range(36, 47), range(47, 58)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = [range(n-2, n+9) for n in sorted(rows)]\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:07.057049Z",
     "iopub.status.busy": "2020-09-03T05:41:07.056756Z",
     "iopub.status.idle": "2020-09-03T05:41:07.138046Z",
     "shell.execute_reply": "2020-09-03T05:41:07.136838Z",
     "shell.execute_reply.started": "2020-09-03T05:41:07.057015Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{7, 8, 9, 10, 12, 13, 14, 15, 17, 18, 19, 20}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:07.139686Z",
     "iopub.status.busy": "2020-09-03T05:41:07.139289Z",
     "iopub.status.idle": "2020-09-03T05:41:07.219127Z",
     "shell.execute_reply": "2020-09-03T05:41:07.218560Z",
     "shell.execute_reply.started": "2020-09-03T05:41:07.139644Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[7, 8, 9, 10], [12, 13, 14, 15], [17, 18, 19, 20]]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = sorted(columns)\n",
    "columns = [columns[slice(n,n+4)] for n in range(0, len(columns), 4)]\n",
    "\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:07.220160Z",
     "iopub.status.busy": "2020-09-03T05:41:07.219971Z",
     "iopub.status.idle": "2020-09-03T05:41:07.392868Z",
     "shell.execute_reply": "2020-09-03T05:41:07.392046Z",
     "shell.execute_reply.started": "2020-09-03T05:41:07.220138Z"
    }
   },
   "outputs": [],
   "source": [
    "from itertools import product\n",
    "\n",
    "data_boundary = defaultdict(list)\n",
    "\n",
    "for (value, row, column), row_range, column_list in product(data, rows, columns):\n",
    "    if row in row_range and column in column_list:\n",
    "        data_boundary[(row_range, tuple(column_list))].append(value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:07.393934Z",
     "iopub.status.busy": "2020-09-03T05:41:07.393724Z",
     "iopub.status.idle": "2020-09-03T05:41:07.412151Z",
     "shell.execute_reply": "2020-09-03T05:41:07.411444Z",
     "shell.execute_reply.started": "2020-09-03T05:41:07.393914Z"
    }
   },
   "outputs": [],
   "source": [
    "numpy_data = np.vstack([np.reshape(value,(-1,4)) \n",
    "                        for key, value\n",
    "                        in data_boundary.items()])\n",
    "\n",
    "df = pd.DataFrame(numpy_data).remove_empty().add_prefix(\"A_\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:07.413186Z",
     "iopub.status.busy": "2020-09-03T05:41:07.412995Z",
     "iopub.status.idle": "2020-09-03T05:41:07.504081Z",
     "shell.execute_reply": "2020-09-03T05:41:07.503340Z",
     "shell.execute_reply.started": "2020-09-03T05:41:07.413165Z"
    }
   },
   "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>A_0</th>\n",
       "      <th>A_1</th>\n",
       "      <th>A_2</th>\n",
       "      <th>A_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fixed Price</td>\n",
       "      <td>None</td>\n",
       "      <td>Basis</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BID</td>\n",
       "      <td>OFFER</td>\n",
       "      <td>BID</td>\n",
       "      <td>OFFER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.89</td>\n",
       "      <td>1.91</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.06</td>\n",
       "      <td>2.08</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2.395</td>\n",
       "      <td>2.415</td>\n",
       "      <td>-0.565</td>\n",
       "      <td>-0.545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2.594</td>\n",
       "      <td>2.614</td>\n",
       "      <td>-0.49375</td>\n",
       "      <td>-0.47375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2.58129</td>\n",
       "      <td>2.60129</td>\n",
       "      <td>-0.585</td>\n",
       "      <td>-0.565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3.356</td>\n",
       "      <td>3.376</td>\n",
       "      <td>-0.295</td>\n",
       "      <td>-0.275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2.63408</td>\n",
       "      <td>2.65408</td>\n",
       "      <td>-0.530417</td>\n",
       "      <td>-0.510417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       A_0      A_1       A_2       A_3\n",
       "0  IF NWPL Rocky Mountains     None      None      None\n",
       "1              Fixed Price     None     Basis      None\n",
       "2                      BID    OFFER       BID     OFFER\n",
       "3                     1.89     1.91      None      None\n",
       "4                     2.06     2.08      None      None\n",
       "5                    2.395    2.415    -0.565    -0.545\n",
       "6                    2.594    2.614  -0.49375  -0.47375\n",
       "7                  2.58129  2.60129    -0.585    -0.565\n",
       "8                    3.356    3.376    -0.295    -0.275\n",
       "9                  2.63408  2.65408 -0.530417 -0.510417"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:07.505119Z",
     "iopub.status.busy": "2020-09-03T05:41:07.504924Z",
     "iopub.status.idle": "2020-09-03T05:41:07.583969Z",
     "shell.execute_reply": "2020-09-03T05:41:07.583357Z",
     "shell.execute_reply.started": "2020-09-03T05:41:07.505097Z"
    }
   },
   "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>company_name</th>\n",
       "      <th>Fixed_Price_Bid</th>\n",
       "      <th>Fixed_Price_Offer</th>\n",
       "      <th>Basis_Bid</th>\n",
       "      <th>Basis_Offer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>1.89</td>\n",
       "      <td>1.91</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.06</td>\n",
       "      <td>2.08</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.395</td>\n",
       "      <td>2.415</td>\n",
       "      <td>-0.565</td>\n",
       "      <td>-0.545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.594</td>\n",
       "      <td>2.614</td>\n",
       "      <td>-0.49375</td>\n",
       "      <td>-0.47375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.58129</td>\n",
       "      <td>2.60129</td>\n",
       "      <td>-0.585</td>\n",
       "      <td>-0.565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>PG&amp;E City Gate</td>\n",
       "      <td>2.88</td>\n",
       "      <td>2.9</td>\n",
       "      <td>-0.08</td>\n",
       "      <td>-0.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>PG&amp;E City Gate</td>\n",
       "      <td>3.01525</td>\n",
       "      <td>3.03525</td>\n",
       "      <td>-0.0725</td>\n",
       "      <td>-0.0525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>PG&amp;E City Gate</td>\n",
       "      <td>3.26129</td>\n",
       "      <td>3.28129</td>\n",
       "      <td>0.095</td>\n",
       "      <td>0.115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>PG&amp;E City Gate</td>\n",
       "      <td>3.959</td>\n",
       "      <td>3.979</td>\n",
       "      <td>0.308</td>\n",
       "      <td>0.328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>PG&amp;E City Gate</td>\n",
       "      <td>3.21575</td>\n",
       "      <td>3.23575</td>\n",
       "      <td>0.05125</td>\n",
       "      <td>0.07125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               company_name Fixed_Price_Bid Fixed_Price_Offer Basis_Bid  \\\n",
       "0   IF NWPL Rocky Mountains            1.89              1.91      None   \n",
       "1   IF NWPL Rocky Mountains            2.06              2.08      None   \n",
       "2   IF NWPL Rocky Mountains           2.395             2.415    -0.565   \n",
       "3   IF NWPL Rocky Mountains           2.594             2.614  -0.49375   \n",
       "4   IF NWPL Rocky Mountains         2.58129           2.60129    -0.585   \n",
       "..                      ...             ...               ...       ...   \n",
       "65           PG&E City Gate            2.88               2.9     -0.08   \n",
       "66           PG&E City Gate         3.01525           3.03525   -0.0725   \n",
       "67           PG&E City Gate         3.26129           3.28129     0.095   \n",
       "68           PG&E City Gate           3.959             3.979     0.308   \n",
       "69           PG&E City Gate         3.21575           3.23575   0.05125   \n",
       "\n",
       "   Basis_Offer  \n",
       "0         None  \n",
       "1         None  \n",
       "2       -0.545  \n",
       "3     -0.47375  \n",
       "4       -0.565  \n",
       "..         ...  \n",
       "65       -0.06  \n",
       "66     -0.0525  \n",
       "67       0.115  \n",
       "68       0.328  \n",
       "69     0.07125  \n",
       "\n",
       "[70 rows x 5 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df\n",
    " .assign(company_name = np.where(df.isna().sum(1)==3, \n",
    "                                 df.iloc[:,0], \n",
    "                                 None))\n",
    " # pyjanitor\n",
    " .fill_direction({\"company_name\":\"down\"})\n",
    " .set_axis([\"Fixed_Price_Bid\", \"Fixed_Price_Offer\", \n",
    "            \"Basis_Bid\", \"Basis_Offer\", \"company_name\"], \n",
    "           axis='columns')\n",
    "  .query(\"Fixed_Price_Bid != company_name and Fixed_Price_Bid != ['Fixed Price', 'BID']\")\n",
    " # pyjanitor\n",
    " .reorder_columns([\"company_name\"])\n",
    " .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:07.585292Z",
     "iopub.status.busy": "2020-09-03T05:41:07.585061Z",
     "iopub.status.idle": "2020-09-03T05:41:08.037661Z",
     "shell.execute_reply": "2020-09-03T05:41:08.036772Z",
     "shell.execute_reply.started": "2020-09-03T05:41:07.585262Z"
    }
   },
   "outputs": [],
   "source": [
    "# complete code : \n",
    "from itertools import product\n",
    "\n",
    "wb  = load_workbook(filename = \"enron.xlsx\")\n",
    "ws = wb[\"Report\"]\n",
    "\n",
    "rows = set()\n",
    "columns = set()\n",
    "\n",
    "data = []\n",
    "for row in ws.rows:\n",
    "    for cell in row:\n",
    "        if cell.value in (\"BID\", \"OFFER\") :\n",
    "            rows.add(cell.row)\n",
    "            columns.add(cell.column)\n",
    "        data.append((cell.value, cell.row, cell.column))\n",
    "        \n",
    "rows = [range(n-2, n+9) for n in sorted(rows)]\n",
    "\n",
    "columns = sorted(columns)\n",
    "columns = [columns[slice(n,n+4)] for n in range(0, len(columns), 4)]\n",
    "\n",
    "data_boundary = defaultdict(list)\n",
    "for (value, row, column), row_range, column_list in product(data, rows, columns):\n",
    "    if row in row_range and column in column_list:\n",
    "        data_boundary[(row_range, tuple(column_list))].append(value)\n",
    "        \n",
    "numpy_data = np.vstack([np.reshape(value,(-1,4)) \n",
    "                        for key, value \n",
    "                        in data_boundary.items()])\n",
    "\n",
    "df = (pd.DataFrame(numpy_data)\n",
    "     .remove_empty()\n",
    "     .add_prefix(\"A_\")\n",
    "     .assign(company_name = lambda df: np.where( df.isna().sum(1)==3, \n",
    "                                                 df.iloc[:,0], \n",
    "                                                 None))\n",
    "     .fill_direction({\"company_name\":\"down\"})\n",
    "     .set_axis([\"Fixed_Price_Bid\", \"Fixed_Price_Offer\", \n",
    "                \"Basis_Bid\", \"Basis_Offer\", \"company_name\"], \n",
    "               axis='columns')\n",
    "      .query(\"Fixed_Price_Bid != company_name and Fixed_Price_Bid != ['Fixed Price', 'BID']\")\n",
    "     .reorder_columns([\"company_name\"])\n",
    "     .reset_index(drop=True)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-09-03T05:41:08.038879Z",
     "iopub.status.busy": "2020-09-03T05:41:08.038554Z",
     "iopub.status.idle": "2020-09-03T05:41:08.051702Z",
     "shell.execute_reply": "2020-09-03T05:41:08.051034Z",
     "shell.execute_reply.started": "2020-09-03T05:41:08.038857Z"
    }
   },
   "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>company_name</th>\n",
       "      <th>Fixed_Price_Bid</th>\n",
       "      <th>Fixed_Price_Offer</th>\n",
       "      <th>Basis_Bid</th>\n",
       "      <th>Basis_Offer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>1.89</td>\n",
       "      <td>1.91</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.06</td>\n",
       "      <td>2.08</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.395</td>\n",
       "      <td>2.415</td>\n",
       "      <td>-0.565</td>\n",
       "      <td>-0.545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.594</td>\n",
       "      <td>2.614</td>\n",
       "      <td>-0.49375</td>\n",
       "      <td>-0.47375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.58129</td>\n",
       "      <td>2.60129</td>\n",
       "      <td>-0.585</td>\n",
       "      <td>-0.565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>3.356</td>\n",
       "      <td>3.376</td>\n",
       "      <td>-0.295</td>\n",
       "      <td>-0.275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>IF NWPL Rocky Mountains</td>\n",
       "      <td>2.63408</td>\n",
       "      <td>2.65408</td>\n",
       "      <td>-0.530417</td>\n",
       "      <td>-0.510417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>IF CIG Rocky Mountains</td>\n",
       "      <td>1.94</td>\n",
       "      <td>1.96</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>IF CIG Rocky Mountains</td>\n",
       "      <td>1.96</td>\n",
       "      <td>1.98</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>IF CIG Rocky Mountains</td>\n",
       "      <td>2.345</td>\n",
       "      <td>2.365</td>\n",
       "      <td>-0.615</td>\n",
       "      <td>-0.595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>IF CIG Rocky Mountains</td>\n",
       "      <td>2.54775</td>\n",
       "      <td>2.56775</td>\n",
       "      <td>-0.54</td>\n",
       "      <td>-0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>IF CIG Rocky Mountains</td>\n",
       "      <td>2.47129</td>\n",
       "      <td>2.49129</td>\n",
       "      <td>-0.695</td>\n",
       "      <td>-0.675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>IF CIG Rocky Mountains</td>\n",
       "      <td>3.311</td>\n",
       "      <td>3.331</td>\n",
       "      <td>-0.34</td>\n",
       "      <td>-0.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>IF CIG Rocky Mountains</td>\n",
       "      <td>2.55075</td>\n",
       "      <td>2.57075</td>\n",
       "      <td>-0.61375</td>\n",
       "      <td>-0.59375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>IF EL Paso Permian</td>\n",
       "      <td>2.375</td>\n",
       "      <td>2.395</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>IF EL Paso Permian</td>\n",
       "      <td>2.42</td>\n",
       "      <td>2.44</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>IF EL Paso Permian</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.72</td>\n",
       "      <td>-0.26</td>\n",
       "      <td>-0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>IF EL Paso Permian</td>\n",
       "      <td>2.85463</td>\n",
       "      <td>2.87462</td>\n",
       "      <td>-0.233125</td>\n",
       "      <td>-0.213125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>IF EL Paso Permian</td>\n",
       "      <td>3.00879</td>\n",
       "      <td>3.02879</td>\n",
       "      <td>-0.1575</td>\n",
       "      <td>-0.1375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>IF EL Paso Permian</td>\n",
       "      <td>3.4985</td>\n",
       "      <td>3.5185</td>\n",
       "      <td>-0.1525</td>\n",
       "      <td>-0.1325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>IF EL Paso Permian</td>\n",
       "      <td>2.98221</td>\n",
       "      <td>3.00221</td>\n",
       "      <td>-0.182292</td>\n",
       "      <td>-0.162292</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               company_name Fixed_Price_Bid Fixed_Price_Offer Basis_Bid  \\\n",
       "0   IF NWPL Rocky Mountains            1.89              1.91      None   \n",
       "1   IF NWPL Rocky Mountains            2.06              2.08      None   \n",
       "2   IF NWPL Rocky Mountains           2.395             2.415    -0.565   \n",
       "3   IF NWPL Rocky Mountains           2.594             2.614  -0.49375   \n",
       "4   IF NWPL Rocky Mountains         2.58129           2.60129    -0.585   \n",
       "5   IF NWPL Rocky Mountains           3.356             3.376    -0.295   \n",
       "6   IF NWPL Rocky Mountains         2.63408           2.65408 -0.530417   \n",
       "7    IF CIG Rocky Mountains            1.94              1.96      None   \n",
       "8    IF CIG Rocky Mountains            1.96              1.98      None   \n",
       "9    IF CIG Rocky Mountains           2.345             2.365    -0.615   \n",
       "10   IF CIG Rocky Mountains         2.54775           2.56775     -0.54   \n",
       "11   IF CIG Rocky Mountains         2.47129           2.49129    -0.695   \n",
       "12   IF CIG Rocky Mountains           3.311             3.331     -0.34   \n",
       "13   IF CIG Rocky Mountains         2.55075           2.57075  -0.61375   \n",
       "14       IF EL Paso Permian           2.375             2.395      None   \n",
       "15       IF EL Paso Permian            2.42              2.44      None   \n",
       "16       IF EL Paso Permian             2.7              2.72     -0.26   \n",
       "17       IF EL Paso Permian         2.85463           2.87462 -0.233125   \n",
       "18       IF EL Paso Permian         3.00879           3.02879   -0.1575   \n",
       "19       IF EL Paso Permian          3.4985            3.5185   -0.1525   \n",
       "20       IF EL Paso Permian         2.98221           3.00221 -0.182292   \n",
       "\n",
       "   Basis_Offer  \n",
       "0         None  \n",
       "1         None  \n",
       "2       -0.545  \n",
       "3     -0.47375  \n",
       "4       -0.565  \n",
       "5       -0.275  \n",
       "6    -0.510417  \n",
       "7         None  \n",
       "8         None  \n",
       "9       -0.595  \n",
       "10       -0.52  \n",
       "11      -0.675  \n",
       "12       -0.32  \n",
       "13    -0.59375  \n",
       "14        None  \n",
       "15        None  \n",
       "16       -0.24  \n",
       "17   -0.213125  \n",
       "18     -0.1375  \n",
       "19     -0.1325  \n",
       "20   -0.162292  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(21)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}