- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to find the number of Blank and Non-Blank cells in the Excel table using Python?
In this article, we will show you how to find the count of a number of blank and non-blank (filled) cells in an excel worksheet using python.
Assume we have taken an excel file with the name demoTutorialsPoint.xlsx containing some random data with some blank cells. We will return the count of a number of blank and non-blank (filled) cells in an excel worksheet.
sampleTutorialsPoint.xlsx
Player Name | Age | Type | Country | Team | Runs | Wickets |
---|---|---|---|---|---|---|
Virat Kohli | Batsman | India | Royal Challengers Bangalore | 20 | ||
34 | Batsman | India | Sun Risers Hyderabad | 333 | 140 | |
Mahendra Singh Dhoni | 39 | Batsman | India | Chennai Super Kings | 0 | |
Rashid Khan | Bowler | Gujarat Titans | 500 | 130 | ||
Hardik Pandya | 29 | All rounder | Gujarat Titans | 2400 | 85 | |
David Warner | 34 | Batsman | Australia | 5500 | 12 | |
Kieron Pollard | 35 | All rounder | West Indies | Mumbai Indians | 0 | 67 |
Rohit Sharma | 33 | Batsman | India | Mumbai Indians | 5456 | 20 |
Kane Williamson | 33 | Batsman | Sun Risers Hyderabad | 3222 | 5 | |
Kagiso Rabada | 29 | Bowler | South Africa | Lucknow Capitals | 335 | 1 |
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).
pip install xlrd
Create a variable to store the count of empty cells present in an excel sheet.
Create another variable to store the count of Non-empty cells present in an excel sheet.
Initialize both the count variables with 0.
Create a variable to store the path of the input excel file.
To create a workbook object, pass the input excel file to the xlrd module's open_workbook() function (opens a workbook).
Using the sheet_by_index() method (opens a sheet with the specific index), open the first worksheet in the given workbook (here 0 represents first sheet).
Traverse in all the rows of the worksheet using the for loop. The nrows attribute is used to get the total number of rows.
Using the nested for loop, traverse all the columns of the worksheet using another nested for loop. The ncols attribute is used to get the total number of columns.
Using the cell_value() function (gives the cell's value in the specified row and column) and the if conditional statement, determine whether the cell is blank or NOT.
Increment the empty cells count by 1 if it is a blank cell.
Else Increment the Non-empty cells count by 1.
Print the count of empty cells found in a given input excel file.
Print the count of Non-empty cells found in a given input excel file.
Example
The following program prints the count of the number of blank and Non-blank cells found in a given input excel file −
import xlrd # storing the count of Empty cells emptyCells=0 # storing the count of Non-empty cells nonEmptycells=0 # input excel file path inputExcelFile ="sampleTutorialsPoint.xlsx" # creating a workbook newWorkbook =xlrd.open_workbook(inputExcelFile) # creating a first worksheet firstWorksheet=newWorkbook.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_column in range (firstWorksheet.ncols) : # Checking whether the cell is a blank cell if (firstWorksheet.cell_value(each_row, each_column)==""): # Incrementing empty cells count by 1, if it is a blank cell emptyCells+=1 else : # Else Incrementing Non-empty cells count by 1 nonEmptycells+=1 # Printing the count of empty cells print("Empty cells count = ", emptyCells) # Printing the count of Non-empty cells print("Non-empty cells count = ", nonEmptycells)
Output
On executing, the above program will generate the following output −
Empty cells count = 10 Non-empty cells count = 67
We used a sample excel file with some dummy data in our program. The excel file has some blank cells in it. We used two variables to count the number of empty and non-empty cells, and we used the for loop to traverse the excel file cell by cell, checking whether it was an empty cell or not, and printed the count of empty and non-empty cells.
Conclusion
We learned how to use the xlrd module to fetch the excel file as a workbook and get the specified sheet as a worksheet. We also learned how to traverse the excel file cell by cell and how to obtain the value of a cell using the cell value() function.