Pandas_messy_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading poorly structured Excel files with Pandas\n",
    "\n",
    "This is a walkthrough of [Chris Moffitt's Practical Business Python](https://pbpython.com/pandas-excel-range.html) blog post from Oct 19, 2020.\n",
    "\n",
    "This post came across my feed right as we were looking at Pandas and I think has great examples with real-world datasets. In an ideal world, all data would be nicely formatted and easy to work with...that world does not exist...data are messy and people don't follow best practices in formatting files.\n",
    "\n",
    "Note that this tutorial requires openpyxl >= 3.0.4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2020-10-20 11:41:39--  https://github.com/chris1610/pbpython/raw/master/data/shipping_tables.xlsx\n",
      "Resolving github.com (github.com)... 140.82.112.4\n",
      "Connecting to github.com (github.com)|140.82.112.4|:443... connected.\n",
      "HTTP request sent, awaiting response... 302 Found\n",
      "Location: https://raw.githubusercontent.com/chris1610/pbpython/master/data/shipping_tables.xlsx [following]\n",
      "--2020-10-20 11:41:39--  https://raw.githubusercontent.com/chris1610/pbpython/master/data/shipping_tables.xlsx\n",
      "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.204.133\n",
      "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.204.133|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 16307 (16K) [application/octet-stream]\n",
      "Saving to: ‘shipping_tables.xlsx.2’\n",
      "\n",
      "100%[======================================>] 16,307      --.-K/s   in 0.008s  \n",
      "\n",
      "2020-10-20 11:41:39 (1.86 MB/s) - ‘shipping_tables.xlsx.2’ saved [16307/16307]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Download the example file to the current directory.\n",
    "!wget https://github.com/chris1610/pbpython/raw/master/data/shipping_tables.xlsx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: xlrd in ./.local/lib/python3.8/site-packages (1.2.0)\n"
     ]
    }
   ],
   "source": [
    "# Need a module for Excel that isn't installed on HiPerGato.\n",
    "#  `pip install MODULE --user` is the command that would install modules\n",
    "#  in your user directory.\n",
    "!pip install xlrd --user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>Date</th>\n",
       "      <th>2020-01-01 00:00:00</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>order id</td>\n",
       "      <td>order date</td>\n",
       "      <td>state</td>\n",
       "      <td>priority</td>\n",
       "      <td>item_type</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Notes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>669165933</td>\n",
       "      <td>2019-01-03 00:00:00</td>\n",
       "      <td>MN</td>\n",
       "      <td>2-day</td>\n",
       "      <td>Baby Food</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Check this one out</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>963881480</td>\n",
       "      <td>2019-01-04 00:00:00</td>\n",
       "      <td>WI</td>\n",
       "      <td>next-day</td>\n",
       "      <td>Cereal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>341417157</td>\n",
       "      <td>2019-01-05 00:00:00</td>\n",
       "      <td>TX</td>\n",
       "      <td>2-day</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>514321792</td>\n",
       "      <td>2019-01-06 00:00:00</td>\n",
       "      <td>CA</td>\n",
       "      <td>next-day</td>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0 Unnamed: 1           Unnamed: 2 Unnamed: 3 Unnamed: 4  \\\n",
       "0         NaN   order id           order date      state   priority   \n",
       "1         NaN  669165933  2019-01-03 00:00:00         MN      2-day   \n",
       "2         NaN  963881480  2019-01-04 00:00:00         WI   next-day   \n",
       "3         NaN  341417157  2019-01-05 00:00:00         TX      2-day   \n",
       "4         NaN  514321792  2019-01-06 00:00:00         CA   next-day   \n",
       "\n",
       "        Unnamed: 5  Unnamed: 6          Unnamed: 7  Date  2020-01-01 00:00:00  \n",
       "0        item_type         NaN               Notes   NaN                  NaN  \n",
       "1        Baby Food         NaN  Check this one out   NaN                  NaN  \n",
       "2           Cereal         NaN                 NaN   NaN                  NaN  \n",
       "3  Office Supplies         NaN                 NaN   NaN                  NaN  \n",
       "4  Office Supplies         NaN                 NaN   NaN                  NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Let's see what happens if we simply try to read this into a dataframe\n",
    "df=pd.read_excel('shipping_tables.xlsx')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Simply reading the file in with `pd.read_excel()` gives messy results because the first row of the first sheet has just a date in columns I & J. The real headers are in the second row of the Excel file. Also column A has no data, so we can ignore that. The `pd.read_excel()` function has options to deal with these. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order id</th>\n",
       "      <th>order date</th>\n",
       "      <th>state</th>\n",
       "      <th>priority</th>\n",
       "      <th>item_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>669165933</td>\n",
       "      <td>2019-01-03</td>\n",
       "      <td>MN</td>\n",
       "      <td>2-day</td>\n",
       "      <td>Baby Food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>963881480</td>\n",
       "      <td>2019-01-04</td>\n",
       "      <td>WI</td>\n",
       "      <td>next-day</td>\n",
       "      <td>Cereal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>341417157</td>\n",
       "      <td>2019-01-05</td>\n",
       "      <td>TX</td>\n",
       "      <td>2-day</td>\n",
       "      <td>Office Supplies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>514321792</td>\n",
       "      <td>2019-01-06</td>\n",
       "      <td>CA</td>\n",
       "      <td>next-day</td>\n",
       "      <td>Office Supplies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>115456712</td>\n",
       "      <td>2019-01-07</td>\n",
       "      <td>CA</td>\n",
       "      <td>2-day</td>\n",
       "      <td>Office Supplies</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order id order date state  priority        item_type\n",
       "0  669165933 2019-01-03    MN     2-day        Baby Food\n",
       "1  963881480 2019-01-04    WI  next-day           Cereal\n",
       "2  341417157 2019-01-05    TX     2-day  Office Supplies\n",
       "3  514321792 2019-01-06    CA  next-day  Office Supplies\n",
       "4  115456712 2019-01-07    CA     2-day  Office Supplies"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('shipping_tables.xlsx', header=1, usecols='B:F')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As noted, the `header=1` is a 0-based index to the header row--the second row in this case.\n",
    "\n",
    "The `usecols` flag also takes a lot of different formats for the specification, letters, numbers, column names, etc. The original post also looks at using a lambda function to make all the column names lower case so that multiple files with similar column names can be combined.\n",
    "\n",
    "Pandas can also read from (and write to) lots of different types of data sources. Check the [I/O section of the Pandas docs](https://pandas.pydata.org/pandas-docs/stable/reference/io.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading from Excel Worksheets, Ranges and Tables\n",
    "\n",
    "Also notice above that we got the data from the first worksheet, and nothing with information that the Excel file has two worksheets. \n",
    "\n",
    "The example in the file may seem extreem, but again...people are people, publishers are publishers, and there's certainly data out there with these formats. One file may not be an issue to work with by hand, but what if you had hundereds of these files to work with?\n",
    "\n",
    "While we can get part of the way there using `sheet_name` in `pd.read_excel` that function doesn't know about Table names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ship_type</th>\n",
       "      <th>Notes</th>\n",
       "      <th>ship_cost</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Baby Food</td>\n",
       "      <td>2-day and next-day</td>\n",
       "      <td>5-7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cereal</td>\n",
       "      <td>next-day and 2-day</td>\n",
       "      <td>8-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fruit</td>\n",
       "      <td>next-day and 2-day</td>\n",
       "      <td>5-6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Office Supplies</td>\n",
       "      <td>2-day and next-day</td>\n",
       "      <td>7-9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ship_type               Notes ship_cost Unnamed: 3 Unnamed: 4\n",
       "0        Baby Food  2-day and next-day       5-7        NaN        NaN\n",
       "1           Cereal  next-day and 2-day      8-11        NaN        NaN\n",
       "2            Fruit  next-day and 2-day       5-6        NaN        NaN\n",
       "3  Office Supplies  2-day and next-day       7-9        NaN        NaN\n",
       "4              NaN                 NaN       NaN        NaN        NaN"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rates = pd.read_excel('shipping_tables.xlsx', sheet_name='shipping_rates')\n",
    "\n",
    "df_rates.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To access named tables within the Excel file, there is another module called `openpyxl`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "openpyxl.workbook.workbook.Workbook"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "wb = load_workbook(filename = 'shipping_tables.xlsx') # Notice the different format here where \n",
    "                                                      # filename flag is needed\n",
    "type(wb) # Like Pandas dataframes, openpyxl adds a data type of workbook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['sales', 'shipping_rates']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb.sheetnames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a sheet variable with the shipping_rates sheet\n",
    "\n",
    "sheet = wb['shipping_rates']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at the named tables with that sheet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['ship_cost'])"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet.tables.keys()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C8:E16'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the Excel range for the ship_cost table:\n",
    "# Again, different than the \n",
    "lookup_table = sheet.tables['ship_cost']\n",
    "lookup_table.ref"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item_type</th>\n",
       "      <th>priority</th>\n",
       "      <th>shipping_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Baby Food</td>\n",
       "      <td>2-day</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Baby Food</td>\n",
       "      <td>next-day</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cereal</td>\n",
       "      <td>2-day</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cereal</td>\n",
       "      <td>next-day</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fruit</td>\n",
       "      <td>2-day</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item_type  priority  shipping_cost\n",
       "0  Baby Food     2-day              5\n",
       "1  Baby Food  next-day              7\n",
       "2     Cereal     2-day              8\n",
       "3     Cereal  next-day             11\n",
       "4      Fruit     2-day              5"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Using the above range, convert the data into a data frame\n",
    "\n",
    "# Access the data in the table range\n",
    "data = sheet[lookup_table.ref]\n",
    "rows_list = []\n",
    "\n",
    "# Loop through each row and get the values in the cells\n",
    "for row in data:\n",
    "    # Get a list of all columns in each row\n",
    "    cols = []\n",
    "    for col in row:\n",
    "        cols.append(col.value)\n",
    "    rows_list.append(cols)\n",
    "\n",
    "# Create a pandas dataframe from the rows_list.\n",
    "# The first row is the column names\n",
    "df = pd.DataFrame(data=rows_list[1:], index=None, columns=rows_list[0])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "Chris also points to a paper that I typically assign later in the semester, [Broman and Woo (2018)](https://www.tandfonline.com/doi/full/10.1080/00031305.2017.1375989), that covers best practices in data organization in spreadsheets. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8 (full)",
   "language": "python",
   "name": "python3-3.8-ufrc"
  },
  "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
}