Python Pandas read_excel() – Reading Excel File for Beginners – Pandas Tutorial

By | April 10, 2020

Python pandas is a powerful package to read excel file.  We will introduce how to read excel data in this tutorial for python beginners. You can learn how to do by following our tutorial.

Install python pandas

You can use pip install python pandas first.

pip install pandas

Analysis excel file

As to an excel file, it contains some sheets, which means if we plan to read data in an excel file, we should read sheet by sheet.

python pandas read excel file

As to a sheet in an excel file, it contains two parts: header and the data. We can use python pandas to read these two parts data.

Read data from a sheet in an excel file using pandas

We can use pandas.read_excel() function to read a sheet data.

Here is an example:

import pandas

excel_data = pandas.read_excel('test.xlsx', sheet_name='member')
print(excel_data)

This code means we will read data in the sheet (member) of the excel file (test.xlsx).

Run this code, you will get the result:

   No   Name  Age
0   1    Tom   24
1   2   Kate   22
2   3  Alexa   34

You may encounter ImportError: Missing optional dependency ‘xlrd’, you can read this tutorial to fix it.

Fix Python Pandas Read Excel File – ImportError: Missing optional dependency ‘xlrd’

Get the data size in a sheet

How much data is containd in a sheet? To get this size, you can use code below.

size = excel_data.shape
print(size)

The size is: (3, 3), which means this sheet contains 3 rows * 3 columns data.

Get the header name of a sheet

The header name of a sheet is very important, which can help us to read data by column.

columns = excel_data.columns.values
print(columns)

The header names of this sheet are:

['No' 'Name' 'Age']

Read data by column

We can read a sheet data by column name, here is an example.

print(excel_data['Name'].values.tolist())

The result is:

['Tom', 'Kate', 'Alexa']

Read data by row

We also can read sheet data by row number. You should notice the row number starts 0. Here is an example:

print(excel_data.iloc[1].values.tolist())

This code mean we will get the second row data in sheet.

The result is:

[2, 'Kate', 22]

Leave a Reply

Your email address will not be published. Required fields are marked *