Load an Excel File With Python and Pandas

Resources
Right-click -> Save as…

Prerequisites

  • Install the Pandas library for your Python environment
  • Cells in this notebook expect the Car Sales.xlsx file to be in certain locations; specifics are in the cell itself
  • Resources to help you practice

First Things First

import pandas as pd

Load Data From Excel

File is in the same directory as your Jupyter Notebook

# Read the Excel file
car_sales_data = pd.read_excel("Car Sales.xlsx")

# Show the first 5 rows
car_sales_data.head(5)
DealershipName RedCars SilverCars BlackCars BlueCars MonthSold YearSold
0 Clyde's Clunkers 902 650 754 792 1 2018
1 Clyde's Clunkers 710 476 518 492 2 2018
2 Clyde's Clunkers 248 912 606 350 3 2018
3 Clyde's Clunkers 782 912 858 446 4 2018
4 Clyde's Clunkers 278 354 482 752 5 2018

File is in a different directory than your Jupyter Notebook

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…

Find the home directory using Python
from os.path import expanduser as ospath

user_home_directory = ospath("~")
# Make sure to use "/" slashes and not "\" slashes
# There actually needs to be folders named "Path" and "To" and "Excel" and "File"
# in your home directory (the "~" means "home directory") for this cell to work
excel_file_path = user_home_directory + "/Path/To/Excel/File/Car Sales.xlsx"

other_path_car_sales_data = pd.read_excel(excel_file_path)

# Show the first 5 rows
other_path_car_sales_data.head(5)
DealershipName RedCars SilverCars BlackCars BlueCars MonthSold YearSold
0 Clyde's Clunkers 902 650 754 792 1 2018
1 Clyde's Clunkers 710 476 518 492 2 2018
2 Clyde's Clunkers 248 912 606 350 3 2018
3 Clyde's Clunkers 782 912 858 446 4 2018
4 Clyde's Clunkers 278 354 482 752 5 2018

From a URL

# Note the URL Encoding with "%20" for spaces
url_to_excel_file = "https://github.com/andrewcbancroft/datadaylife-blog/raw/master/datasets/Car%20Sales.xlsx"

# Read the Excel file
url_car_sales_data = pd.read_excel(url_to_excel_file)

# Show the first 5 rows
url_car_sales_data.head(5)
DealershipName RedCars SilverCars BlackCars BlueCars MonthSold YearSold
0 Clyde's Clunkers 902 650 754 792 1 2018
1 Clyde's Clunkers 710 476 518 492 2 2018
2 Clyde's Clunkers 248 912 606 350 3 2018
3 Clyde's Clunkers 782 912 858 446 4 2018
4 Clyde's Clunkers 278 354 482 752 5 2018