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

1import pandas as pd

Load Data From Excel

File is in the same directory as your Jupyter Notebook

1# Read the Excel file
2car_sales_data = pd.read_excel("Car Sales.xlsx")
3
4# Show the first 5 rows
5car_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
1from os.path import expanduser as ospath
2
3user_home_directory = ospath("~")

1# Make sure to use "/" slashes and not "\" slashes
2# There actually needs to be folders named "Path" and "To" and "Excel" and "File"
3# in your home directory (the "~" means "home directory") for this cell to work
4excel_file_path = user_home_directory + "/Path/To/Excel/File/Car Sales.xlsx"
5
6other_path_car_sales_data = pd.read_excel(excel_file_path)
7
8# Show the first 5 rows
9other_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

1# Note the URL Encoding with "%20" for spaces
2url_to_excel_file = "/datasets/Car%20Sales.xlsx"
3
4# Read the Excel file
5url_car_sales_data = pd.read_excel(url_to_excel_file)
6
7# Show the first 5 rows
8url_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