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.
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]