{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "---\n",
    "title: \"Load an Excel File With Python and Pandas\"\n",
    "author: \"Andrew Bancroft\"\n",
    "date:   2019-05-22\n",
    "description: \"Several examples of how to load an excel file into a Pandas dataframe with Python\"\n",
    "type: technical_note\n",
    "draft: false\n",
    "comments: true\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name=\"resources\" class=\"jump-target\"></a>\n",
    "<div class=\"resources\">\n",
    "  <div class=\"resources-header\">\n",
    "    Resources\n",
    "  </div>\n",
    "    <div class=\"resources-download-instructions\">\n",
    "    Right-click -> Save as...\n",
    "  </div>\n",
    "  <ul class=\"resources-content\">\n",
    "    <li>\n",
    "        <i class=\"fas fa-file-excel\"></i> <a href=\"https://github.com/andrewcbancroft/datadaylife-blog/raw/master/datasets/Car%20Sales.xlsx\">Car Sales.xlsx</a>\n",
    "    </li>\n",
    "    <li>\n",
    "        <i class=\"fas fa-book\"></i> <a href=\"https://raw.githubusercontent.com/andrewcbancroft/datadaylife-blog/master/content/notes/data-engineering-python/load-excel-file-with-python-pandas.ipynb\">load-excel-file-with-python-pandas.ipynb</a>\n",
    "    </li>\n",
    "  </ul>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prerequisites"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Install the [Pandas](https://pandas.pydata.org/) library for your Python environment\n",
    "* Cells in this notebook expect the <a href=\"https://github.com/andrewcbancroft/datadaylife-blog/raw/master/datasets/Car%20Sales.xlsx\">Car Sales.xlsx</a> file to be in certain locations; specifics are in the cell itself\n",
    "* [Resources](#resources) to help you practice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First Things First"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data From Excel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### File is in the same directory as your Jupyter Notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DealershipName</th>\n",
       "      <th>RedCars</th>\n",
       "      <th>SilverCars</th>\n",
       "      <th>BlackCars</th>\n",
       "      <th>BlueCars</th>\n",
       "      <th>MonthSold</th>\n",
       "      <th>YearSold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>902</td>\n",
       "      <td>650</td>\n",
       "      <td>754</td>\n",
       "      <td>792</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>710</td>\n",
       "      <td>476</td>\n",
       "      <td>518</td>\n",
       "      <td>492</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>248</td>\n",
       "      <td>912</td>\n",
       "      <td>606</td>\n",
       "      <td>350</td>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>782</td>\n",
       "      <td>912</td>\n",
       "      <td>858</td>\n",
       "      <td>446</td>\n",
       "      <td>4</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>278</td>\n",
       "      <td>354</td>\n",
       "      <td>482</td>\n",
       "      <td>752</td>\n",
       "      <td>5</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     DealershipName  RedCars  SilverCars  BlackCars  BlueCars  MonthSold  \\\n",
       "0  Clyde's Clunkers      902         650        754       792          1   \n",
       "1  Clyde's Clunkers      710         476        518       492          2   \n",
       "2  Clyde's Clunkers      248         912        606       350          3   \n",
       "3  Clyde's Clunkers      782         912        858       446          4   \n",
       "4  Clyde's Clunkers      278         354        482       752          5   \n",
       "\n",
       "   YearSold  \n",
       "0      2018  \n",
       "1      2018  \n",
       "2      2018  \n",
       "3      2018  \n",
       "4      2018  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the Excel file\n",
    "car_sales_data = pd.read_excel(\"Car Sales.xlsx\")\n",
    "\n",
    "# Show the first 5 rows\n",
    "car_sales_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### File is in a different directory than your Jupyter Notebook\n",
    "The example will use your \"home directory\" to make this example applicable across operating systems, but you can use any path as long as the file exists there..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Find the home directory using Python "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from os.path import expanduser as ospath\n",
    "\n",
    "user_home_directory = ospath(\"~\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DealershipName</th>\n",
       "      <th>RedCars</th>\n",
       "      <th>SilverCars</th>\n",
       "      <th>BlackCars</th>\n",
       "      <th>BlueCars</th>\n",
       "      <th>MonthSold</th>\n",
       "      <th>YearSold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>902</td>\n",
       "      <td>650</td>\n",
       "      <td>754</td>\n",
       "      <td>792</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>710</td>\n",
       "      <td>476</td>\n",
       "      <td>518</td>\n",
       "      <td>492</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>248</td>\n",
       "      <td>912</td>\n",
       "      <td>606</td>\n",
       "      <td>350</td>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>782</td>\n",
       "      <td>912</td>\n",
       "      <td>858</td>\n",
       "      <td>446</td>\n",
       "      <td>4</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>278</td>\n",
       "      <td>354</td>\n",
       "      <td>482</td>\n",
       "      <td>752</td>\n",
       "      <td>5</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     DealershipName  RedCars  SilverCars  BlackCars  BlueCars  MonthSold  \\\n",
       "0  Clyde's Clunkers      902         650        754       792          1   \n",
       "1  Clyde's Clunkers      710         476        518       492          2   \n",
       "2  Clyde's Clunkers      248         912        606       350          3   \n",
       "3  Clyde's Clunkers      782         912        858       446          4   \n",
       "4  Clyde's Clunkers      278         354        482       752          5   \n",
       "\n",
       "   YearSold  \n",
       "0      2018  \n",
       "1      2018  \n",
       "2      2018  \n",
       "3      2018  \n",
       "4      2018  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make sure to use \"/\" slashes and not \"\\\" slashes\n",
    "# There actually needs to be folders named \"Path\" and \"To\" and \"Excel\" and \"File\"\n",
    "# in your home directory (the \"~\" means \"home directory\") for this cell to work\n",
    "excel_file_path = user_home_directory + \"/Path/To/Excel/File/Car Sales.xlsx\"\n",
    "\n",
    "other_path_car_sales_data = pd.read_excel(excel_file_path)\n",
    "\n",
    "# Show the first 5 rows\n",
    "other_path_car_sales_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From a URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "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>DealershipName</th>\n",
       "      <th>RedCars</th>\n",
       "      <th>SilverCars</th>\n",
       "      <th>BlackCars</th>\n",
       "      <th>BlueCars</th>\n",
       "      <th>MonthSold</th>\n",
       "      <th>YearSold</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>902</td>\n",
       "      <td>650</td>\n",
       "      <td>754</td>\n",
       "      <td>792</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>710</td>\n",
       "      <td>476</td>\n",
       "      <td>518</td>\n",
       "      <td>492</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>248</td>\n",
       "      <td>912</td>\n",
       "      <td>606</td>\n",
       "      <td>350</td>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>782</td>\n",
       "      <td>912</td>\n",
       "      <td>858</td>\n",
       "      <td>446</td>\n",
       "      <td>4</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Clyde's Clunkers</td>\n",
       "      <td>278</td>\n",
       "      <td>354</td>\n",
       "      <td>482</td>\n",
       "      <td>752</td>\n",
       "      <td>5</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     DealershipName  RedCars  SilverCars  BlackCars  BlueCars  MonthSold  \\\n",
       "0  Clyde's Clunkers      902         650        754       792          1   \n",
       "1  Clyde's Clunkers      710         476        518       492          2   \n",
       "2  Clyde's Clunkers      248         912        606       350          3   \n",
       "3  Clyde's Clunkers      782         912        858       446          4   \n",
       "4  Clyde's Clunkers      278         354        482       752          5   \n",
       "\n",
       "   YearSold  \n",
       "0      2018  \n",
       "1      2018  \n",
       "2      2018  \n",
       "3      2018  \n",
       "4      2018  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note the URL Encoding with \"%20\" for spaces\n",
    "url_to_excel_file = \"https://github.com/andrewcbancroft/datadaylife-blog/raw/master/datasets/Car%20Sales.xlsx\"\n",
    "\n",
    "# Read the Excel file\n",
    "url_car_sales_data = pd.read_excel(url_to_excel_file)\n",
    "\n",
    "# Show the first 5 rows\n",
    "url_car_sales_data.head(5)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}