Pandas_Tips/02_read_excel.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fab6be38",
   "metadata": {},
   "source": [
    "# Pandas Tips: `read_excel()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3da5f505",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67c7e9a8",
   "metadata": {},
   "source": [
    "Check your pandas version to ensure similar behavior. \n",
    "\n",
    "_Version 2.1.1 was released September 2023._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cc3c4702",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2.1.1'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13c1c666",
   "metadata": {},
   "source": [
    "## Basics"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90e14e8d",
   "metadata": {},
   "source": [
    "### Reading from computer file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "af526693",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dataset_gen.py   purchases.csv    read_excel.ipynb store_data.xlsx\r\n"
     ]
    }
   ],
   "source": [
    "!ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9bb87581",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('store_data.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1c2f6d53",
   "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>ID</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>Phone</th>\n",
       "      <th>LTV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4576</td>\n",
       "      <td>Sophia</td>\n",
       "      <td>Walker</td>\n",
       "      <td>602-310-9331</td>\n",
       "      <td>496.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9488</td>\n",
       "      <td>Evelyn</td>\n",
       "      <td>Harris</td>\n",
       "      <td>171-363-6978</td>\n",
       "      <td>38424.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7265</td>\n",
       "      <td>Lucas</td>\n",
       "      <td>Foster</td>\n",
       "      <td>139-658-8905</td>\n",
       "      <td>14413.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8555</td>\n",
       "      <td>Elijah</td>\n",
       "      <td>Jones</td>\n",
       "      <td>538-241-9868</td>\n",
       "      <td>13440.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7023</td>\n",
       "      <td>Mason</td>\n",
       "      <td>Taylor</td>\n",
       "      <td>937-595-5837</td>\n",
       "      <td>1777.37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ID First Name Last Name         Phone       LTV\n",
       "0  4576     Sophia    Walker  602-310-9331    496.87\n",
       "1  9488     Evelyn    Harris  171-363-6978  38424.41\n",
       "2  7265      Lucas    Foster  139-658-8905  14413.64\n",
       "3  8555     Elijah     Jones  538-241-9868  13440.49\n",
       "4  7023      Mason    Taylor  937-595-5837   1777.37"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "643bf42a",
   "metadata": {},
   "source": [
    "### Specify sheet name: `sheet_name`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5c3eb6f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('store_data.xlsx', sheet_name='purchases')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0efc2b97",
   "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>PRODUCT INFORMATION</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Date of Purchase</td>\n",
       "      <td>Customer ID</td>\n",
       "      <td>Product ID</td>\n",
       "      <td>Product Description</td>\n",
       "      <td>Cost</td>\n",
       "      <td>Online</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-10-11 00:00:00</td>\n",
       "      <td>4576</td>\n",
       "      <td>11</td>\n",
       "      <td>Gray Sweater</td>\n",
       "      <td>59.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-13 00:00:00</td>\n",
       "      <td>9488</td>\n",
       "      <td>32</td>\n",
       "      <td>Running Shoes</td>\n",
       "      <td>49.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-08-20 00:00:00</td>\n",
       "      <td>7265</td>\n",
       "      <td>3</td>\n",
       "      <td>Blue Jeans</td>\n",
       "      <td>49.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-04-07 00:00:00</td>\n",
       "      <td>8555</td>\n",
       "      <td>39</td>\n",
       "      <td>Polo Shirt</td>\n",
       "      <td>29.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Unnamed: 0   Unnamed: 1 PRODUCT INFORMATION           Unnamed: 3  \\\n",
       "0     Date of Purchase  Customer ID          Product ID  Product Description   \n",
       "1  2021-10-11 00:00:00         4576                  11         Gray Sweater   \n",
       "2  2020-11-13 00:00:00         9488                  32        Running Shoes   \n",
       "3  2020-08-20 00:00:00         7265                   3           Blue Jeans   \n",
       "4  2021-04-07 00:00:00         8555                  39           Polo Shirt   \n",
       "\n",
       "  Unnamed: 4 Unnamed: 5  \n",
       "0       Cost     Online  \n",
       "1      59.99         No  \n",
       "2      49.99        Yes  \n",
       "3      49.99        Yes  \n",
       "4      29.99         No  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36d62fe6",
   "metadata": {},
   "source": [
    "### Reading from a URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cf51c48d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\n",
    "    'https://github.com/kimfetti/Videos/blob/master/Pandas_Tips/data/store_data.xlsx?raw=True',\n",
    "    sheet_name='purchases'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6d51e6e5",
   "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>PRODUCT INFORMATION</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Date of Purchase</td>\n",
       "      <td>Customer ID</td>\n",
       "      <td>Product ID</td>\n",
       "      <td>Product Description</td>\n",
       "      <td>Cost</td>\n",
       "      <td>Online</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-10-11 00:00:00</td>\n",
       "      <td>4576</td>\n",
       "      <td>11</td>\n",
       "      <td>Gray Sweater</td>\n",
       "      <td>59.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-13 00:00:00</td>\n",
       "      <td>9488</td>\n",
       "      <td>32</td>\n",
       "      <td>Running Shoes</td>\n",
       "      <td>49.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-08-20 00:00:00</td>\n",
       "      <td>7265</td>\n",
       "      <td>3</td>\n",
       "      <td>Blue Jeans</td>\n",
       "      <td>49.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-04-07 00:00:00</td>\n",
       "      <td>8555</td>\n",
       "      <td>39</td>\n",
       "      <td>Polo Shirt</td>\n",
       "      <td>29.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Unnamed: 0   Unnamed: 1 PRODUCT INFORMATION           Unnamed: 3  \\\n",
       "0     Date of Purchase  Customer ID          Product ID  Product Description   \n",
       "1  2021-10-11 00:00:00         4576                  11         Gray Sweater   \n",
       "2  2020-11-13 00:00:00         9488                  32        Running Shoes   \n",
       "3  2020-08-20 00:00:00         7265                   3           Blue Jeans   \n",
       "4  2021-04-07 00:00:00         8555                  39           Polo Shirt   \n",
       "\n",
       "  Unnamed: 4 Unnamed: 5  \n",
       "0       Cost     Online  \n",
       "1      59.99         No  \n",
       "2      49.99        Yes  \n",
       "3      49.99        Yes  \n",
       "4      29.99         No  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e08d9336",
   "metadata": {},
   "source": [
    "## $\\star$ Level Up $\\star$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efdfad65",
   "metadata": {},
   "source": [
    "### Skip unnecessary rows: `skiprows` (header)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "76590217",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\n",
    "    'store_data.xlsx', \n",
    "    sheet_name='purchases', #specify sheet name\n",
    "    skiprows=1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "98b41998",
   "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>Date of Purchase</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Product ID</th>\n",
       "      <th>Product Description</th>\n",
       "      <th>Cost</th>\n",
       "      <th>Online</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-10-11 00:00:00</td>\n",
       "      <td>4576.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>Gray Sweater</td>\n",
       "      <td>59.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-11-13 00:00:00</td>\n",
       "      <td>9488.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>Running Shoes</td>\n",
       "      <td>49.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-08-20 00:00:00</td>\n",
       "      <td>7265.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Blue Jeans</td>\n",
       "      <td>49.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-04-07 00:00:00</td>\n",
       "      <td>8555.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>Polo Shirt</td>\n",
       "      <td>29.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-10 00:00:00</td>\n",
       "      <td>7023.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>Sweatshirt</td>\n",
       "      <td>49.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Date of Purchase  Customer ID  Product ID Product Description   Cost  \\\n",
       "0  2021-10-11 00:00:00       4576.0        11.0        Gray Sweater  59.99   \n",
       "1  2020-11-13 00:00:00       9488.0        32.0       Running Shoes  49.99   \n",
       "2  2020-08-20 00:00:00       7265.0         3.0          Blue Jeans  49.99   \n",
       "3  2021-04-07 00:00:00       8555.0        39.0          Polo Shirt  29.99   \n",
       "4  2022-01-10 00:00:00       7023.0        44.0          Sweatshirt  49.99   \n",
       "\n",
       "  Online  \n",
       "0     No  \n",
       "1    Yes  \n",
       "2    Yes  \n",
       "3     No  \n",
       "4     No  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65b16b61",
   "metadata": {},
   "source": [
    "### `skipfooter` (footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "52ad8fa3",
   "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>Date of Purchase</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Product ID</th>\n",
       "      <th>Product Description</th>\n",
       "      <th>Cost</th>\n",
       "      <th>Online</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1814</th>\n",
       "      <td>2022-11-02 00:00:00</td>\n",
       "      <td>9176.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>Casual Dress</td>\n",
       "      <td>69.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1815</th>\n",
       "      <td>2023-05-12 00:00:00</td>\n",
       "      <td>8588.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>Maxi Skirt</td>\n",
       "      <td>39.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1816</th>\n",
       "      <td>2021-12-28 00:00:00</td>\n",
       "      <td>1892.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>Leather Jacket</td>\n",
       "      <td>119.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1817</th>\n",
       "      <td>2022-12-25 00:00:00</td>\n",
       "      <td>7437.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Cargo Shorts</td>\n",
       "      <td>22.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1818</th>\n",
       "      <td>TOTAL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76105.20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date of Purchase  Customer ID  Product ID Product Description  \\\n",
       "1814  2022-11-02 00:00:00       9176.0        54.0        Casual Dress   \n",
       "1815  2023-05-12 00:00:00       8588.0        30.0          Maxi Skirt   \n",
       "1816  2021-12-28 00:00:00       1892.0        27.0      Leather Jacket   \n",
       "1817  2022-12-25 00:00:00       7437.0        24.0        Cargo Shorts   \n",
       "1818                TOTAL          NaN         NaN                 NaN   \n",
       "\n",
       "          Cost Online  \n",
       "1814     69.99     No  \n",
       "1815     39.99     No  \n",
       "1816    119.99    Yes  \n",
       "1817     22.99     No  \n",
       "1818  76105.20    NaN  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "55afd622",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\n",
    "    'store_data.xlsx', \n",
    "    sheet_name='purchases', #specify sheet name\n",
    "    skiprows=1, #skip 1 header row\n",
    "    skipfooter=1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "558d5839",
   "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>Date of Purchase</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Product ID</th>\n",
       "      <th>Product Description</th>\n",
       "      <th>Cost</th>\n",
       "      <th>Online</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>2020-01-26</td>\n",
       "      <td>1110</td>\n",
       "      <td>14</td>\n",
       "      <td>Plaid Shorts</td>\n",
       "      <td>22.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1814</th>\n",
       "      <td>2022-11-02</td>\n",
       "      <td>9176</td>\n",
       "      <td>54</td>\n",
       "      <td>Casual Dress</td>\n",
       "      <td>69.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1815</th>\n",
       "      <td>2023-05-12</td>\n",
       "      <td>8588</td>\n",
       "      <td>30</td>\n",
       "      <td>Maxi Skirt</td>\n",
       "      <td>39.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1816</th>\n",
       "      <td>2021-12-28</td>\n",
       "      <td>1892</td>\n",
       "      <td>27</td>\n",
       "      <td>Leather Jacket</td>\n",
       "      <td>1,119.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1817</th>\n",
       "      <td>2022-12-25</td>\n",
       "      <td>7437</td>\n",
       "      <td>24</td>\n",
       "      <td>Cargo Shorts</td>\n",
       "      <td>22.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Date of Purchase  Customer ID  Product ID Product Description      Cost  \\\n",
       "1813       2020-01-26         1110          14        Plaid Shorts     22.99   \n",
       "1814       2022-11-02         9176          54        Casual Dress     69.99   \n",
       "1815       2023-05-12         8588          30          Maxi Skirt     39.99   \n",
       "1816       2021-12-28         1892          27      Leather Jacket  1,119.99   \n",
       "1817       2022-12-25         7437          24        Cargo Shorts     22.99   \n",
       "\n",
       "     Online  \n",
       "1813    Yes  \n",
       "1814     No  \n",
       "1815     No  \n",
       "1816    Yes  \n",
       "1817     No  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4e90b6c",
   "metadata": {},
   "source": [
    "### Recognize digit separators: `thousands`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "fd13fed8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1818 entries, 0 to 1817\n",
      "Data columns (total 6 columns):\n",
      " #   Column               Non-Null Count  Dtype         \n",
      "---  ------               --------------  -----         \n",
      " 0   Date of Purchase     1818 non-null   datetime64[ns]\n",
      " 1   Customer ID          1818 non-null   int64         \n",
      " 2   Product ID           1818 non-null   int64         \n",
      " 3   Product Description  1818 non-null   object        \n",
      " 4   Cost                 1818 non-null   object        \n",
      " 5   Online               1818 non-null   object        \n",
      "dtypes: datetime64[ns](1), int64(2), object(3)\n",
      "memory usage: 85.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "61ed424a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\n",
    "    'store_data.xlsx', \n",
    "    sheet_name='purchases', #specify sheet name\n",
    "    skiprows=1, #skip 1 header row\n",
    "    skipfooter=1, #skip 1 footer row\n",
    "    thousands=','\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "f2b69a33",
   "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>Date of Purchase</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Product ID</th>\n",
       "      <th>Product Description</th>\n",
       "      <th>Cost</th>\n",
       "      <th>Online</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>2020-01-26 00:00:00</td>\n",
       "      <td>1110</td>\n",
       "      <td>14</td>\n",
       "      <td>Plaid Shorts</td>\n",
       "      <td>22.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1814</th>\n",
       "      <td>2022-11-02 00:00:00</td>\n",
       "      <td>9176</td>\n",
       "      <td>54</td>\n",
       "      <td>Casual Dress</td>\n",
       "      <td>69.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1815</th>\n",
       "      <td>2023-05-12 00:00:00</td>\n",
       "      <td>8588</td>\n",
       "      <td>30</td>\n",
       "      <td>Maxi Skirt</td>\n",
       "      <td>39.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1816</th>\n",
       "      <td>2021-12-28 00:00:00</td>\n",
       "      <td>1892</td>\n",
       "      <td>27</td>\n",
       "      <td>Leather Jacket</td>\n",
       "      <td>1119.99</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1817</th>\n",
       "      <td>2022-12-25 00:00:00</td>\n",
       "      <td>7437</td>\n",
       "      <td>24</td>\n",
       "      <td>Cargo Shorts</td>\n",
       "      <td>22.99</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date of Purchase  Customer ID  Product ID Product Description  \\\n",
       "1813  2020-01-26 00:00:00         1110          14        Plaid Shorts   \n",
       "1814  2022-11-02 00:00:00         9176          54        Casual Dress   \n",
       "1815  2023-05-12 00:00:00         8588          30          Maxi Skirt   \n",
       "1816  2021-12-28 00:00:00         1892          27      Leather Jacket   \n",
       "1817  2022-12-25 00:00:00         7437          24        Cargo Shorts   \n",
       "\n",
       "         Cost Online  \n",
       "1813    22.99    Yes  \n",
       "1814    69.99     No  \n",
       "1815    39.99     No  \n",
       "1816  1119.99    Yes  \n",
       "1817    22.99     No  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "82e73669",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1818 entries, 0 to 1817\n",
      "Data columns (total 6 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   Date of Purchase     1818 non-null   object \n",
      " 1   Customer ID          1818 non-null   int64  \n",
      " 2   Product ID           1818 non-null   int64  \n",
      " 3   Product Description  1818 non-null   object \n",
      " 4   Cost                 1818 non-null   float64\n",
      " 5   Online               1818 non-null   object \n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 85.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93268e3c",
   "metadata": {},
   "source": [
    "### Convert strings to Booleans: `true_values`, `false_values`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "6f65a554",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Online\n",
       "No     918\n",
       "Yes    900\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Online.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "2d1e467d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\n",
    "    'store_data.xlsx', \n",
    "    sheet_name='purchases', #specify sheet name\n",
    "    skiprows=1, #skip 1 header row\n",
    "    skipfooter=1, #skip 1 footer row\n",
    "    thousands=',', #recognize comma as digit separator\n",
    "    true_values=['Yes'],\n",
    "    false_values=['No']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "f63aab87",
   "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>Date of Purchase</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Product ID</th>\n",
       "      <th>Product Description</th>\n",
       "      <th>Cost</th>\n",
       "      <th>Online</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-10-11</td>\n",
       "      <td>4576</td>\n",
       "      <td>11</td>\n",
       "      <td>Gray Sweater</td>\n",
       "      <td>59.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-11-13</td>\n",
       "      <td>9488</td>\n",
       "      <td>32</td>\n",
       "      <td>Running Shoes</td>\n",
       "      <td>49.99</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-08-20</td>\n",
       "      <td>7265</td>\n",
       "      <td>3</td>\n",
       "      <td>Blue Jeans</td>\n",
       "      <td>49.99</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-04-07</td>\n",
       "      <td>8555</td>\n",
       "      <td>39</td>\n",
       "      <td>Polo Shirt</td>\n",
       "      <td>29.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-10</td>\n",
       "      <td>7023</td>\n",
       "      <td>44</td>\n",
       "      <td>Sweatshirt</td>\n",
       "      <td>49.99</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Date of Purchase  Customer ID  Product ID Product Description   Cost  Online\n",
       "0       2021-10-11         4576          11        Gray Sweater  59.99   False\n",
       "1       2020-11-13         9488          32       Running Shoes  49.99    True\n",
       "2       2020-08-20         7265           3          Blue Jeans  49.99    True\n",
       "3       2021-04-07         8555          39          Polo Shirt  29.99   False\n",
       "4       2022-01-10         7023          44          Sweatshirt  49.99   False"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "02313a18",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Online\n",
       "False    918\n",
       "True     900\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Online.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "23f8b2e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "900"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Online.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f1ef61e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}