How to Find the Frequency of a Particular Word in a cell in an Excel table in Python

In this article, let us look at how to find the frequency of a specific word(total number of times the word has occurred) in a cell in an excel table using python.

For this purpose, we made use of the xlrd library in python.

Python xlrd Module:

The xlrd module can be used to get data from a spreadsheet. It can be used to read, write, or modify data. In addition, the user may have to navigate through several sheets and get data based on some criteria, or edit some rows and columns, among other things.

To extract data from a spreadsheet, use the xlrd module.

The Python xlrd library, which is available on Pypi, is a library that allows users to read Excel files with the extensions “.xls” or “.xlsx.” Before you move into the code, be sure to install it.

NOTE:

 Support for reading xlsx sheets has been explicitly removed from xlrd module.

Installation:

Type the below command to install the xlrd module:

pip install xlrd

Functions Used:

open_workbook(): It is used to open a workbook.
sheet_by_index(): This function is used to access/open the sheet with a specific index number.
nrows: It is used to give the total number of rows.
ncols: It is used to give the total number of rows.

sampleExcelFile:

Python Program to Find the Frequency of a Particular Word in a cell in an Excel table

Method #1: Using xlrd Module (User Input)

Approach:

Import xlrd module using the import keyword.
Give the word for which the frequency of it is to be found as user input using the input() function and store it in a variable.
Take a variable that stores the count number of times the given word is repeated(frequency)
Give the path of the excel file to be read as static input and store it in a variable.
Pass the above file path as an argument to the open_workbook() function of the xlrd module to create/open a workbook.
Open the first worksheet in the above workbook using the sheet_by_index() function by passing the index value 0 as an argument to it.
You can open any other sheets just by changing the index number(starts from 0)
Iterate till the number of rows in a worksheet using the nrows attribute
The nrows attribute is used to extract the number of rows
Iterate till the number of columns in a worksheet using the ncols attribute in a nested for loop
The ncols attribute is used to extract the number of columns
Check if the value in each cell of the worksheet is equal to the given word using the cell_value() function and if conditional statement.
If it is true, then increment the frequency of the word count by 1
Print the frequency of the given word in an excel sheet.
The Exit of the Program

Below is the implementation:

# Import xlrd module using the import keyword
import xlrd
# Give the word for which the frequeny of it to be found
# as user input using the input() function and store it in a variable.
gvn_word = input("Enter some random word = ")
# Take a variable that stores the count number of times the 
# given word is repeated(frequency)
word_frequency=0
 # Give the path of the excel file to be read as 
 # static input and store it in a variable    
excelFilePath="sampleExcelFile.xlsx"
# Pass the above file path as an argument to the open_workbook() function 
# of the xlrd module to create/open a workbook.              
workbook=xlrd.open_workbook(excelFilePath)
# Open a first worksheet in the above workbook using the sheet_by_index() function
# by passing the index value 0 as argument to it.
# you can open any other sheets just by changing the index number(starts from 0)
worksheet=workbook.sheet_by_index(0)
# Iterate till the number of rows in a worksheet using the nrows attribute
# The nrows attribute is used to extract the number of rows   
for row in range (worksheet.nrows):
    # Iterate till the number of columns in a worksheet using the ncols attribute
    # in a nested for loop
    # The ncols attribute is used to extract the number of columns      
    for column in range (worksheet.ncols):
        # Check if the value in each cell of the worksheet is equal to the given word 
        # using the cell_value() function and if conditional statement
        if(worksheet.cell_value(row,column)==gvn_word):
            # If it is true, then increment the frequency of the word count by 1
            word_frequency=word_frequency+1

# Print the frequency of the given word in an excel sheet 
print("The number of times {", gvn_word,"} word occured in an excel sheet = ", word_frequency)

Output:

Enter some random word = Yes
The number of times { Yes } word occurred in an excel sheet = 17

Method #2: Using xlrd Module (Static Input)