Read Excel data using Pandas DataFrame

Kailash Chandra Behera | Thursday, July 02, 2020

Introduction

Pandas library has provided different methods for reading data from different files, here we will discuss the method which is used to read data excel data using pandas DatqaFrame.

Here we will see the code examples which guides how to read data from excel file in python.

Read Excel data using Pandas DataFrame

read excel pandas

Read Excel data using Pandas DataFrame

Getting Started

A DataFrame is a very efficient two-dimensional data structure, it is a flat in structure and is arranged in row and columns. Pandas DataFrame can be used for loading, filtering, sorting, grouping, and joining dataset, more it also supports for dealing with missing data.

Before continuing make sure that you have already installed xlrd package. The xlrd is a library for reading data and formatting information from Excel files, whether they are .xls or .xlsx files. The easiest way to install xlrd is PyPI or PIP. The below command describes how to use install xlrd.

 pip install xlrd  

To install xlrd the, PyPI package must be installed in your machine, if you are new in python and not installed PyPI visit my blog(Install Python pip in Windows).

Read Excel data using Pandas DataFrame

Demonstration

Here we will load the excel data in DataFrame and present it in a different way on the screen using Python.

The Pandas DataFrome provides the various methods ( there are 5 methods provided by Pandas DataFrame library) to load data from different sources. read_excel is one of the methods to read excel data.

Minimum two parameters is required to invoke this read excel function, the first parameter is path of excel file and the second parameter is name of sheet. The path of excel file must include the extension.

 data = pandas.read_excel (r'path of excel sheet', sheet_name='Sheet Name')  

Note: - mention r as prefix of excel path while invoking read_excel method to address special character, such as '\'.

The above method can be invoked using the format pandas.methodname. Instead of using Pandas (name of Pandas library) full name the library can be imported using alias (xl as alias.) like below code and same alias can be used to invoke Pandas methods as well.

 Import pandas as xl   
 data = xl.read_excel (r'path of excel sheet', sheet_name='Sheet Name')   

Steps to Read Excel data using Pandas DataFrame

  1. Import Pandas Library.
  2. Invoke read_excel method.

Example

In this example the Pandas library reads data from an excel having name readexcel.xlxs and dumps data into DataFrame. The excel file has 20 rows including header and 10 columns. The table structure is like below image.

how to read data from excel file in python

Read Excel data using Pandas DataFrame

Note:- The first row in the excel sheet is expected to be header. If not, the header parameter need to be set to None.

 # Demonstration for Reading and Loading data from excel   
  # Importing Pandas library   
  import pandas as xl   
  #Loading data from excel   
  data = xl.read_excel (r'D:\pandas.xlsx', sheet_name='Sheet1')   
  #Displaying data in the screen   
  print (data)   

Read Excel data using Pandas DataFrame

Related Articles

  1. Python Connect to SQL Database
  2. How to install pyodbc window
  3. PIP Install on Windows
  4. Installing Python
  5. Overview of Python

Summary

In Read Excel data using Pandas DataFrame, we discussed the method which is used to read data from excel and demonstrated how to read data from excel sheet. I hope you have enjoyed it a lot.

Thanks