Data Wrangling with pyjanitor

pyjanitor is a Python library that extends pandas, providing intuitive functions for data cleaning and preprocessing. Inspired by R’s janitor package, it simplifies common data manipulation tasks with clear, chainable methods. Key features include cleaning column names, handling missing values, filtering, and grouping data, making it ideal for streamlining data preparation workflows. It integrates seamlessly with pandas DataFrames, offering a user-friendly API for both beginners and advanced users.

Prerequisites

Install the required packages:

Code

import importlib.metadata
import subprocess
import sys


def _pyjanitor_works() -> bool:
    """True if pyjanitor's pandas extensions are available (not a distro-only 'janitor' stub)."""
    try:
        import pandas as _pd

        import janitor  # noqa: F401

        return hasattr(_pd.DataFrame(), "clean_names")
    except Exception:
        return False


def _ensure_pyjanitor() -> None:
    if _pyjanitor_works():
        return
    # Install from PyPI as "pyjanitor"; import remains `janitor`. Pin <0.32: 0.32+ targets
    # pandas APIs not present on all Python/pandas builds (e.g. pandas.core.col.Expression).
    subprocess.check_call(
        [
            sys.executable,
            "-m",
            "pip",
            "install",
            "--quiet",
            "pandas",
            "pyjanitor>=0.31,<0.32",
        ]
    )


_ensure_pyjanitor()

import pandas as pd
import janitor

Code

# Verify package availability
try:
    print(f"pandas {importlib.metadata.version('pandas')}")
    print(f"pyjanitor {importlib.metadata.version('pyjanitor')}")
except importlib.metadata.PackageNotFoundError as exc:
    print(f"Could not read package metadata: {exc}")
print(f"DataFrame.clean_names available: {hasattr(pd.DataFrame(), 'clean_names')}")

Data Wrangling

We will create some “messy” data and clean it using pyjanitor functions, including clean_names(), remove_empty(), find_duplicates(), and drop_constant_columns(). Later, we will clean a real dataset to demonstrate a complete workflow.

clean_names()

The clean_names() function standardizes column names by converting them to snake_case and removing special characters.

Code

# Create a DataFrame with messy column names
df = pd.DataFrame({
    "Column One": [1, 2, 3, 4, 5],
    "Column Two!!": [6, 7, 8, 9, 10],
    "Column Three $": [11, 12, 13, 14, 15],
    "%Column four": [11, 12, 13, 14, 15]
})

print("Original DataFrame:")
print(df.head())

# Clean column names
df_cleaned = df.clean_names()
print("\nDataFrame with cleaned names:")
print(df_cleaned.head())

get_dupes()

The get_dupes() function identifies duplicate rows in a DataFrame based on specified columns.

Code

# Create a DataFrame with duplicate rows
df = pd.DataFrame({
    "Column One": [1, 2, 3, 1],
    "Column Two": ["A", "B", "C", "A"]
})

print("Original DataFrame:")
print(df)

# Find duplicates using pyjanitor's get_dupes function
duplicates = df.get_dupes(columns=["Column One", "Column Two"])

print("\nDuplicate rows:")
print(duplicates)

remove_empty()

The remove_empty() function removes rows or columns that are entirely empty or contain only missing values.

Code

# Create a DataFrame with empty rows and columns
df = pd.DataFrame({
    "x": [1, None, 4],
    "y": [None, None, 3],
    "z": [None, None, None]
})

print("Original DataFrame:")
print(df)

# Remove empty rows and columns
df_cleaned = df.remove_empty()
print("\nDataFrame after removing empty rows/columns:")
print(df_cleaned)

Cleaning Messy Data

Now we will clean on very messy data using some functions of janitor packages. We will use Lung Cancer Mortality data.

Data

The raw file has one column containing all fields separated by |.

Code

# Load the dataset
url = "https://github.com/zia207/Python_for_Beginners/raw/refs/heads/main/Data/USA_LBC_Data_raw.csv"
df = pd.read_csv(url)

print("Original DataFrame:")
print(df.head())

Promote Row to Column Names

The dataset may have descriptive text in the first row instead of proper column headers. We can use row_to_names() to set the first row as column names and remove it from the data.

Code

# Promote first row to column names
df_01 = df.row_to_names(row_number=0, remove_row=True, remove_rows_above=True)
print("\nDataFrame after row_to_names:")
print(df_01.head())

Remove Empty Rows and Columns

Next, we remove any empty rows or columns using remove_empty().

Code

# Remove empty rows and columns
df_02 = df_01.remove_empty()
print("\nDataFrame after removing empty rows/columns:")
print(df_02.head())

Clean Column Names

We standardize column names using clean_names().

Code

# Clean column names
df_03 = df_02.clean_names()
print("\nDataFrame with cleaned column names:")
print(df_03.head())

Convert Data Types

The dataset may have incorrect data types (e.g., numeric columns stored as strings). We use convert_to_numeric() and pandas’ astype() to fix this. Assuming columns 4 to 21 are numeric and 22 to 24 are categorical

Code

# Assuming df_03 is the DataFrame after cleaning names
# Convert columns 4 to 21 to numeric using pandas' pd.to_numeric
# Convert columns 22 to 24 to categorical
df_04 = df_03.copy()  # Create a copy to avoid modifying the original
for col in df_03.columns[3:21]:
    df_04[col] = pd.to_numeric(df_03[col], errors="coerce")
df_04 = df_04.astype({col: 'category' for col in df_03.columns[21:24]})

print("\nDataFrame with corrected data types:")
print(df_04.dtypes)

Remove Duplicates

We check for and remove duplicate rows based on a key column (e.g., fips).

Code

# Check for duplicates using pyjanitor's get_dupes
duplicates = df_04.get_dupes(columns="fips")
print("\nDuplicate rows based on 'fips':")
print(duplicates)

# Remove duplicates using pandas' drop_duplicates
df_05 = df_04.drop_duplicates(subset="fips", keep="first")
print("\nDataFrame after removing duplicates:")
print(df_05.head())

Combine All Steps

We can chain all operations using pandas’ method chaining for a clean workflow.

Code

# Cleaning pipeline
df_clean = df.copy()
df_clean = (df_clean
            .row_to_names(row_number=0, remove_row=True, remove_rows_above=True)
            .remove_empty()
            .clean_names()
)

# Convert columns 4 to 21 to numeric
numeric_cols = df_clean.columns[3:21]
for col in numeric_cols:
    df_clean[col] = pd.to_numeric(df_clean[col], errors="coerce")

# Convert columns 22 to 24 to categorical and remove duplicates
df_clean = (df_clean
            .astype({col: 'category' for col in df_clean.columns[21:24]})
            .drop_duplicates(subset="fips", keep="first")
)

print("\nFully cleaned DataFrame:")
print(df_clean.head())
print("\nData types:")
print(df_clean.dtypes)

# Check for duplicates
duplicates = df_clean.get_dupes(columns="fips")
print("\nDuplicate rows based on 'fips':")
print(duplicates)

Some Important Functions

`conditional_join`

Unlike pd.merge, which only handles equi-joins (equality conditions), conditional_join supports joins based on inequality operators (<, <=, >, >=, ==, !=) or combinations of both.

df.conditional_join(right_df, (left_column, right_column, operator), …)

Where:

df: The left DataFrame
right_df: The right DataFrame to join with
(left_column, right_column, operator): A tuple defining the join condition
operator: One of ==, !=, <=, <, >=, >

Range Join

Let’s say we have two datasets:

employees: Contains employee IDs and their salary ranges
jobs: Contains job positions with required salary ranges

We want to find which jobs each employee qualifies for based on their salary falling within the job’s range.

Code

# Create sample data
employees = pd.DataFrame({
    'employee_id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
    'salary': [50000, 75000, 90000, 60000]
})

jobs = pd.DataFrame({
    'job_title': ['Junior Developer', 'Senior Developer', 'Manager', 'Director'],
    'min_salary': [40000, 60000, 80000, 100000],
    'max_salary': [60000, 85000, 120000, 150000]
})

print("Employees:")
print(employees)
print("\nJobs:")
print(jobs)

Code

# Join employees with jobs where employee salary is between min and max salary
result = employees.conditional_join(
    jobs,
    ('salary', 'min_salary', '>='),  # employee.salary >= job.min_salary
    ('salary', 'max_salary', '<=')   # employee.salary <= job.max_salary
)

print("\nEmployees qualified for jobs:")
print(result)

Controlling Output Columns

Often, you don’t need all columns from both DataFrames. Use df_columns and right_columns to select specific ones:

Code

# Only keep employee info and job title
result_slim = employees.conditional_join(
    jobs,
    ('salary', 'min_salary', '>='), 
    ('salary', 'max_salary', '<='),
    df_columns=['employee_id', 'name', 'salary'],  # Columns from employees
    right_columns='job_title'  # Only job_title from jobs
)

print("\nSimplified result - only employee info and job title:")
print(result_slim)

Using Different Join Types

You can specify different types of joins similar to pd.merge:

Code

# Left join: Include all employees, even if they don't qualify for any job
result_left = employees.conditional_join(
    jobs,
    ('salary', 'min_salary', '>='), 
    ('salary', 'max_salary', '<='),
    how='left'
)

print("\nLeft join - all employees included:")
print(result_left)

Getting First or Last Match Only

When multiple matches exist (like Charlie qualifying for two jobs), you might only want one:

Code

# Get only the first matching job for each employee
result_first = employees.conditional_join(
    jobs,
    ('salary', 'min_salary', '>='), 
    ('salary', 'max_salary', '<='),
    keep='first'
)

print("\nFirst match only:")
print(result_first)

# Get only the last matching job for each employee
result_last = employees.conditional_join(
    jobs,
    ('salary', 'min_salary', '>='), 
    ('salary', 'max_salary', '<='),
    keep='last'
)

print("\nLast match only:")
print(result_last)

Adding an Indicator Column

To understand the source of each row (especially useful in outer joins):

Code

# Outer join with indicator
result_outer = employees.conditional_join(
    jobs,
    ('salary', 'min_salary', '>='), 
    ('salary', 'max_salary', '<='),
    how='outer',
    indicator=True
)

print("\nOuter join with indicator column:")
print(result_outer)

Performance Optimization with Numba

For large datasets, you can significantly speed up computations by enabling Numba (if installed):

Code

# Enable Numba optimization only when numba is installed
try:
    import numba  # noqa: F401

    result_optimized = employees.conditional_join(
        jobs,
        ('salary', 'min_salary', '>='),
        ('salary', 'max_salary', '<='),
        use_numba=True,
    )
    print("Numba path OK; row count:", len(result_optimized))
except ModuleNotFoundError:
    print("numba not installed; skipping use_numba=True (pip install numba to run this path).")

Date Range Analysis

Here’s another practical example: finding events that occurred during specific time periods.

Code

# Sample  website visits and marketing campaigns
visits = pd.DataFrame({
    'user_id': [1, 2, 3, 4, 5],
    'visit_time': pd.to_datetime([
        '2023-01-15 10:00:00',
        '2023-01-20 14:30:00',
        '2023-02-05 09:15:00',
        '2023-02-10 16:20:00',
        '2023-02-25 11:45:00'
    ])
})

campaigns = pd.DataFrame({
    'campaign_name': ['Winter Sale', 'New Year Promo', 'February Frenzy'],
    'start_date': pd.to_datetime(['2023-01-10', '2023-01-15', '2023-02-01']),
    'end_date': pd.to_datetime(['2023-01-25', '2023-01-31', '2023-02-28'])
})

# Find which campaign each visit occurred during
visit_campaigns = visits.conditional_join(
    campaigns,
    ('visit_time', 'start_date', '>='),  # visit_time >= campaign_start
    ('visit_time', 'end_date', '<=')     # visit_time <= campaign_end
)

print("\nWebsite visits matched with campaigns:")
print(visit_campaigns)

Summary and Conclusion

In this notebook, we explored data wrangling using the pyjanitor library in Python. We covered key functions such as clean_names(), get_dupes(), and remove_empty(), demonstrating how they can simplify common data cleaning tasks. By applying these functions to a real-world dataset, we illustrated a step-by-step approach to transforming messy data into a tidy format. The integration of pyjanitor with pandas allows for intuitive and efficient data manipulation, making it a valuable tool for data scientists and analysts. Overall, pyjanitor enhances the data cleaning process, enabling users to focus more on analysis and insights.

Resources

pyjanitor Documentation
pandas CROSSTAB Docs
R janitor Vignette
Tidyverse Style Guide → Applies to Python too!