How to find the frequency of a particular word in a cell of an excel table using Python?

In this article, we will show you how to find the frequency (number of times the word occurs) of a specific word in a cell of a given excel file using python.

Assume we have taken an excel file with the name TutorialsPoint.xlsx containing some random data. We will return the frequency of a given word in a cell of a given excel file.

sampleTutorialsPoint.xlsx

Algorithm (Steps)

Following are the Algorithm/steps to be followed to perform the desired task −

• Use the import keyword to import the xlrd module (To read data from a spreadsheet, use the xlrd module. It has the ability to read, write, and alter data. Furthermore, the user may be required to traverse numerous sheets to obtain data based on certain criteria or to alter specific rows and columns, among other things. Use the xlrd module to extract data from a spreadsheet. The Python xlrd package, which may be found on Pypi, allows users to read Excel files with the extensions ".xls" or ".xlsx").

• Enter the word for which the frequency of it to be found.

• Create a variable to store the count of the number of times the given word is repeated i.e, the frequency of the word.

• Create a variable to store the path of the input excel file.

• To create/open a workbook, pass the input file as an argument to the xlrd module's open_workbook() function (opens a workbook).

• Use the sheet_by_index() function (opens a sheet with the specific index), to open the required worksheet in the above workbook by passing the index value as an argument to it. Here 0 represents the first sheet.

firstWorksheet=new_workbook.sheet_by_index(0)

• Traversing in all the rows of the worksheet using the for loop. The nrows attribute is used to get the total number of rows.

• Use another nested for loop, to traverse all the columns of the worksheet. The ncols attribute is used to get the total number of columns.

• Using the if conditional statement and the cell_value() function, check whether each cell value is equal to the given particular word.

• Increment the frequency count by 1, if the cell value is equal to the given word.

• Print the frequency count of the given word i.e, the number of times the word is repeated.

Example

The following program checks each cell value in an excel file if it is equal to the specified word and prints the frequency count of the word entered −

import xlrd
givenWord =input("Enter some random word = ")

# storing the frequency of the given word in a variable
frequency_count =0

# input excel file path
inputExcelFile ="sampleTutorialsPoint.xlsx"

# Creating a workbook
new_workbook=xlrd.open_workbook(inputExcelFile)

# Opening the first worksheet in the workbook
firstWorksheet=new_workbook.sheet_by_index(0)

# Traversing in all the rows of the worksheet

# (nrows is used to get the number of rows)
for each_row in range (firstWorksheet.nrows):

   # Traversing in all the columns of the worksheet

   # (ncols is used to get the number of columns)
   for each_col in range (firstWorksheet.ncols):

      # Checking whether each cell value is equal to the given word
      if(firstWorksheet.cell_value(each_row, each_col)==givenWord):

         # Incrementing the frequency count by 1
         frequency_count= frequency_count+1

# Printing count of the given word frequency
print("The frequency count of the given word {",givenWord,"} = ", frequency_count)

Output

On executing, the above program will generate the following output −

Enter some random word = India
The frequency count of the given word { India } = 5

In our program, we used a sample excel file with dummy data. We used a variable to store the frequency of the word, and then we used the for loop to traverse the excel file cell by cell, checking whether the value of the cell is equal to the word, and if it is, we incremented the frequency, and finally, we printed the value of the frequency of the word.

Conclusion

We learned how to utilize the xlrd module to create a workbook from an excel file and a worksheet from the selected sheet. We also learned how to go through the excel file cell by cell and compare the cell value to some random string/value. We learned how to compute the frequency of a word in an excel file, which is useful in many situations such as determining the number of times a user appears in a document, filtering the excel file by category, and so on.

Vikram Chiluka

Updated on: 18-Aug-2022

648 Views

Kickstart Your Career

Get certified by completing the course

Get Started