How to find the number of Blank and Non-Blank cells in the Excel table using Python?

Excel files often contain blank cells, and counting these cells is a common requirement for data validation and analysis. Python's xlrd module provides an efficient way to read Excel files and count blank and non-blank cells.

Assume we have an Excel file named sampleTutorialsPoint.xlsx containing cricket player data with some blank cells. We will count both empty and filled cells in the worksheet.

Sample Data

Here's our sample Excel file structure ?

Installing Required Module

First, install the xlrd module for reading Excel files ?

pip install xlrd

Algorithm

The approach involves these key steps ?

• Import the xlrd module for Excel file operations
• Initialize counters for empty and non-empty cells
• Open the Excel workbook and select the first worksheet
• Iterate through each cell using nested loops
• Check if each cell is empty using cell_value()
• Update the appropriate counter based on cell content

Example

Here's the complete program to count blank and non-blank cells ?

import xlrd

# Initialize counters
empty_cells = 0
non_empty_cells = 0

# Excel file path
input_excel_file = "sampleTutorialsPoint.xlsx"

# Open workbook and select first worksheet
workbook = xlrd.open_workbook(input_excel_file)
worksheet = workbook.sheet_by_index(0)

# Iterate through all rows and columns
for row in range(worksheet.nrows):
    for col in range(worksheet.ncols):
        # Check if cell is empty
        if worksheet.cell_value(row, col) == "":
            empty_cells += 1
        else:
            non_empty_cells += 1

# Display results
print("Empty cells count =", empty_cells)
print("Non-empty cells count =", non_empty_cells)
print("Total cells =", empty_cells + non_empty_cells)

Output

The program produces the following output ?

Empty cells count = 10
Non-empty cells count = 67
Total cells = 77

How It Works

• xlrd.open_workbook() Opens the Excel file as a workbook object
• sheet_by_index(0) Selects the first worksheet (index 0)
• nrows and ncols Get total number of rows and columns
• cell_value(row, col) Returns the value at specified row and column
• Empty string comparison checks for blank cells

Alternative Using Pandas

For modern Excel file processing, pandas is often preferred ?

import pandas as pd

# Read Excel file
df = pd.read_excel("sampleTutorialsPoint.xlsx")

# Count empty and non-empty cells
empty_cells = df.isnull().sum().sum()
non_empty_cells = df.count().sum()

print(f"Empty cells: {empty_cells}")
print(f"Non-empty cells: {non_empty_cells}")

Conclusion

Use xlrd for basic Excel reading tasks or pandas for advanced data analysis. Both methods effectively count blank and non-blank cells, with pandas offering more built-in functions for data validation.