Reading Excel Using Python Pandas

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.

Install Dependency

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.

print(excel_data_df.columns.ravel())

The output:

[‘ID’ ‘Name’ ‘Age’ ‘Class’]

Printing a Column Data

We can get the column data and convert it into a list of values by using tolist() method.

print(excel_data_df['Name'].tolist())

Output:

[‘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)

The '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.

Conclusion

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.