Use isnull() with any() and sum() to find missing values in columns, and filter rows containing null values. This targeted approach to Python data cleansing helps you understand the scope of missing data and make informed decisions about which columns with missing values in Pandas need attention during data cleaning.

Finding Columns and Rows with Missing Data

Let’s identify which columns and rows contain missing values:

import pandas as pd

# Sample DataFrame
data = {'Name': ['Amit', 'Priya', 'Ravi', 'Sneha'],
        'Age': [25, None, 30, None],
        'City': ['Delhi', 'Mumbai', None, 'Pune']}
df = pd.DataFrame(data)

# Columns with missing values
print(df.isnull().any(axis=0))

# Count missing values per column
print(df.isnull().sum())

# Rows with missing values
missing_rows = df[df.isnull().any(axis=1)]
print(missing_rows)

Output:

Name    False
Age      True
City     True
dtype: bool

Name    0
Age     2
City    1
dtype: int64

    Name   Age    City
1  Priya   NaN  Mumbai
2   Ravi  30.0     NaN
3  Sneha   NaN    Pune

Understanding any() vs all()

Advanced Column Detection Techniques

# Method 1: Using any()
cols_with_missing = df.columns[df.isnull().any()].tolist()
print(f"Columns with missing values: {cols_with_missing}")

# Method 2: Using sum() and filtering
missing_counts = df.isnull().sum()
cols_with_missing = missing_counts[missing_counts > 0].index.tolist()
print(f"Columns with missing data: {cols_with_missing}")

# Get columns that are completely filled
complete_cols = df.columns[df.notnull().all()].tolist()
print(f"Complete columns: {complete_cols}")

# Alternative method
complete_cols = df.columns[df.isnull().sum() == 0].tolist()
print(f"Columns with no missing data: {complete_cols}")

# Sort columns by number of missing values (descending)
missing_counts = df.isnull().sum().sort_values(ascending=False)
print("Columns ranked by missing data:")
print(missing_counts)

# Get top 5 columns with most missing values
top_missing = df.isnull().sum().nlargest(5)
print("\nTop 5 columns with most missing values:")
print(top_missing)

# Find columns with more than 30% missing values
threshold = 0.3
missing_pct = df.isnull().mean()
high_missing_cols = missing_pct[missing_pct > threshold].index.tolist()

print(f"Columns with >{threshold*100}% missing:")
for col in high_missing_cols:
    pct = missing_pct[col] * 100
    print(f"  {col}: {pct:.2f}%")

Advanced Row Detection Techniques

# Get index positions of rows with missing data
rows_with_missing_idx = df[df.isnull().any(axis=1)].index.tolist()
print(f"Row indices with missing values: {rows_with_missing_idx}")

# Count how many rows have missing data
num_rows_missing = df.isnull().any(axis=1).sum()
print(f"Total rows with missing data: {num_rows_missing}")

# Count missing values per row
missing_per_row = df.isnull().sum(axis=1)
print("Missing values per row:")
print(missing_per_row)

# Get rows with 2 or more missing values
rows_multiple_missing = df[missing_per_row >= 2]
print("\nRows with 2+ missing values:")
print(rows_multiple_missing)

# Find rows where ALL values are missing
completely_empty_rows = df[df.isnull().all(axis=1)]
print("Completely empty rows:")
print(completely_empty_rows)

# Get count
num_empty = df.isnull().all(axis=1).sum()
print(f"\nNumber of completely empty rows: {num_empty}")

# Get rows where specific columns have missing values
rows_missing_age = df[df['Age'].isnull()]
print("Rows with missing Age:")
print(rows_missing_age)

# Rows missing EITHER Age OR City
rows_missing_either = df[df[['Age', 'City']].isnull().any(axis=1)]
print("\nRows missing Age or City:")
print(rows_missing_either)

# Rows missing BOTH Age AND City
rows_missing_both = df[df[['Age', 'City']].isnull().all(axis=1)]
print("\nRows missing both Age and City:")
print(rows_missing_both)

Real-World Detection Scenarios

Complete Missing Data Detection Workflow

import pandas as pd
import numpy as np

def comprehensive_missing_analysis(df):
    """
    Complete analysis of missing values in DataFrame
    """
    print("=" * 60)
    print("COMPREHENSIVE MISSING VALUE DETECTION")
    print("=" * 60)
    
    # Overall statistics
    total_cells = df.shape[0] * df.shape[1]
    total_missing = df.isnull().sum().sum()
    pct_missing = (total_missing / total_cells) * 100
    
    print(f"\n📊 OVERALL STATISTICS")
    print(f"Total cells: {total_cells:,}")
    print(f"Total missing: {total_missing:,}")
    print(f"Percentage missing: {pct_missing:.2f}%")
    
    # Column analysis
    print(f"\n📋 COLUMN ANALYSIS")
    cols_with_missing = df.columns[df.isnull().any()].tolist()
    cols_complete = df.columns[df.notnull().all()].tolist()
    
    print(f"Columns with missing values: {len(cols_with_missing)}")
    print(f"Complete columns: {len(cols_complete)}")
    
    if cols_with_missing:
        print("\nMissing value breakdown by column:")
        missing_summary = df.isnull().sum()
        missing_summary = missing_summary[missing_summary > 0].sort_values(ascending=False)
        
        for col, count in missing_summary.items():
            pct = (count / len(df)) * 100
            print(f"  • {col}: {count} ({pct:.2f}%)")
    
    # Row analysis
    print(f"\n📝 ROW ANALYSIS")
    rows_with_missing = df.isnull().any(axis=1).sum()
    rows_complete = df.notnull().all(axis=1).sum()
    completely_empty = df.isnull().all(axis=1).sum()
    
    print(f"Rows with missing values: {rows_with_missing} ({(rows_with_missing/len(df)*100):.2f}%)")
    print(f"Complete rows: {rows_complete} ({(rows_complete/len(df)*100):.2f}%)")
    print(f"Completely empty rows: {completely_empty}")
    
    # Missing value distribution
    missing_per_row = df.isnull().sum(axis=1)
    print(f"\nMissing value distribution per row:")
    print(missing_per_row.value_counts().sort_index())
    
    # Critical columns check (example)
    critical_cols = ['Customer_ID', 'Order_Date', 'Amount']
    existing_critical = [col for col in critical_cols if col in df.columns]
    
    if existing_critical:
        print(f"\n⚠️  CRITICAL COLUMNS CHECK")
        for col in existing_critical:
            missing = df[col].isnull().sum()
            if missing > 0:
                print(f"  ⚠️  WARNING: {col} has {missing} missing values!")
            else:
                print(f"  ✓ {col} is complete")
    
    return {
        'columns_with_missing': cols_with_missing,
        'rows_with_missing': rows_with_missing,
        'total_missing': total_missing
    }

# Usage
results = comprehensive_missing_analysis(df)
print(f"\nColumns needing attention: {results['columns_with_missing']}")

Quick Reference: Finding Missing Values