How to read an Excel spreadsheet with Python

1 min readDataPandasGetting Started
7-Day Challenge

Land Your First Data Science Job

A proven roadmap to prepare for $75K+ entry-level data roles. Perfect for Data Scientist ready to level up their career.

Build portfolios that hiring managers love
Master the Python and SQL essentials to be industry-ready
Practice with real interview questions from tech companies
Access to the $100k/y Data Scientist Cheatsheet

Join thousands of developers who transformed their careers through our challenge. Unsubscribe anytime.

I bet that your daily work require work with spreadsheet.

You might want to read an excel spreadsheet using Python.

The best library when dealing with spreadsheet is by far the pandas library

In case you don't have the pandas library

Open your terminal and type in :

pip3 install pandas

It will use pip3 package manager to install the pandas library

The example

import pandas as pd
df = pd.read_excel("my_file.xlsx") # We end up with a dataframe

Here we only specify the path to the file, but you can add some other parameters listed on the documentation here.

The most useful I've found are the following:

  1. sheet_name : The name of the spreadsheet of interest (e.g. Sheet1)
  2. headers : Usually the row where are the columns names (e.g. 0)
  3. index_col : The index of the columns we want as index. (e.g. 0)
  4. skiprows : Whether we should skip any rows (e.g. 1)

Where should you put your file ?

What usually matter when dealing with path is

  1. Where do you run your script
  2. Where is your excel file

Usually if you keep them in the same directory like so

current_dir/
├── my_file.xslx
└── my_python_script.py

The following code should work

import pandas as pd
df = pd.read_excel("./my_file.xslx") # Path ./ means current directory

7-Day Challenge

Land Your First Data Science Job

A proven roadmap to prepare for $75K+ entry-level data roles. Perfect for Data Scientist ready to level up their career.

Build portfolios that hiring managers love
Master the Python and SQL essentials to be industry-ready
Practice with real interview questions from tech companies
Access to the $100k/y Data Scientist Cheatsheet

Join thousands of developers who transformed their careers through our challenge. Unsubscribe anytime.

Free Newsletter

Master Data Science in Days, Not Months 🚀

Skip the theoretical rabbit holes. Get practical data science skills delivered in bite-sized lessons – Approach used by real data scientist. Not bookworms. 📚

Weekly simple and practical lessons
Access to ready to use code examples
Skip the math, focus on results
Learn while drinking your coffee

By subscribing, you agree to receive our newsletter. You can unsubscribe at any time.