This python tutorial help to read excel file using pandas. The pandas module help to read excel file data using
read_excel() function into a DataFrame object.
I have already shared tutorial How To Read & Update Excel File Using openpyxl.You will learn here how to read an excel file and display data using pandas.
You can also checkout other python excel tutorials:
- Reading Excel Using Python Pandas
- Popular Python excel Library
- How To Read & Update Excel File Using Python
- Inserting & Deleting rows/columns using openpyxl
Whats Python Pandas
Pandas is an open source python library for data analysis. It’s fast, powerful, flexible and easy to use open source library for python. It provides ready to use high-performance data structures and data analysis tools.
There are 3 data structures provided by the Pandas module, which are as follows:
- Series:It is a 1-D size-immutable array like structure having homogeneous data.
- DataFrames: It is a 2-D size-mutable tabular structure with heterogeneously typed columns.
- Panel: It is a 3-D, size-mutable array.
Read Excel Using Pandas
We will use DataFrame for read and represent excel data. The DataFrame has data aligned in rows and columns like the SQL table or a spreadsheet database. You can also add constant data into a DataFrame or import a CSV/Excel/SQL table etc.
You can install pandas using pip as follow command:
pip install pandas
if you are getting undefined pip command, Then please install by following How to Install pip Package tutorial.
Create DataFrame object
We can create DataFrame object from Dictionaries or list of dictionaries. We can use the below constructor for creating a DataFrame object. The syntax is:
pandas.DataFrame(data, index, columns, dtype, copy)
Where the parameters are:
- data – This help to create a DataFrame object from the input data.
- index – This contains the row labels.
- columns – This used to create column labels.
- dtype – This used to specify the data type of each column.
- copy – It help to copying data, if any.
Pandas read_excel() Example
Let’s read excel file using pandas, Taking a sample Students sheets. The top row contains the header(ID, Name,Age and Class) of the table.
The below python code help to read the “Students” sheet data and printing it.
import pandas excel_data_df = pandas.read_excel('stu_data.xlsx', sheet_name='Students') print(excel_data_df)
As you can see above code, I have used
read_excel() method, that takes first parameter is the name of the excel file, the second parameter is the
sheet_name to be read from the excel file.
The output is a two-dimensional table.
Print Excel Sheet Header Using Pandas
We can get the list of column headers using the columns property of the dataframe object.
[‘ID’ ‘Name’ ‘Age’ ‘Class’]
Printing a Column Data
We can get the column data and convert it into a list of values by using
[‘1’ ‘Adam’, ’12’, ‘1’]
How to Read a Particular Column excel sheet Data
We can also read a particular columns data using pandas
usecols. We can specify the column names to be read from the excel file. It’s useful when you are interested in only a few of the columns of the excel sheet.
import pandas excel_data_df = pandas.read_excel('stu_data.xlsx', sheet_name='Students', usecols=['Name', 'Age']) print(excel_data_df)
Reading Excel File without Header Row
You can also read excel sheet which doesn’t have any header row, Or you don’t want to read header row information from excel file.
excel_data_df = pandas.read_excel('stu_data.xlsx', sheet_name='Students', header=None)
'header' contains the integer humber of header row number, if you will pass 2 then it will treat 2 row as header row, and the values will be read from the next row onwards. Any data before the header row will be discarded.
We have read excel sheet using python pandas, it have many method to manipulate excel sheet data. I have discussed some methods, You can explore many method and properties of dataframe object.