Python

How to read Excel Files using Python?

How to read Excel Files using Python?

Introduction

There are plenty of file types available for processing when dealing with Python and one such type is excel. It becomes important when handling data in excel files as Python is widely used to process excel files generally used as CSV files or comma-separated values files. So primarily there are three main methods to read excel files which will be discussed below

Required Module

We need to install the module required for XLwings, openpyxl, and  pandas in order to read excel files which can be done using the pip command below ( Execute this command in the command terminal):

pip install xlrd

pip install pandas

pip install openpyxl

Method 1: Reading an excel file using Pandas

A data frame object is created by using the built-in method of pandas.read_excel() that reads the given excel file and forms a data frame out of it

Syntax: pandas.read_excel(‘file name.xlsx’)

Code

# import pandas libray as pd

import pandas as pd

# read  excel file

df = pd.read_excel('data.xlsx')

print(df)

write your code here: Coding Playground

Output

Method 2: Reading an excel file using Openpyxl

We define two variables namely df and df1 to load the dataframe and read the sheet respectively. Each individual cell is accessed by traversing through the rows and columns using a for loop. As a result, we can access each data unit and by using appropriate print statements we would be able to format the data to print the desired output

import openpyxl as opxl

# Define variable to load the dataframe

df = opxl.load_workbook("data.xlsx")

# Define variable to read sheet

df1 = df.active

# Iterate and print values

for row in range(0, df1.max_row):

print()

for col in df1.iter_cols(1, df1.max_column):

print(col[row].value,end ="   ")

write your code here: Coding Playground

Output:

Method 3: Reading an excel file using Xlwings

This method requires the usage of the Xlwings module which must be imported first following which we specify the file and sheet to read. Then the range of the data to be read is specified in terms of the cells and the required data is printed

import xlwings as xlw

# Specifying a sheet

df = xlw.Book("data.xlsx").sheets['Sheet1']

# Selecting data from

# a single cell

res = df.range("A1:D5").value

print("Result:", res)

write your code here: Coding Playground

Output

Applications

Excel files come in very handy while dealing with projects acting as the backend for the storage of data. Especially in the field of data science, data is the main source of analysis and predictions for future situations.

For example, predicting the weather of a place at a given point in time can be done using Pandas for which excel files containing information regarding the different climatic conditions such as temperature, humidity, air quality index, etc are used. Thus, it becomes vital to explore and understand the different methods available to read and extract data from excel files.