NDIS Data Pipeline

Processing and Quality Control of NDIS Data

Author

Tina Lasisi | Virum Ranka

Published

February 1, 2026

1. Create Intermediate data from raw data

This section processes raw NDIS data to an intermediate stage. Steps:

Load raw data
Clean and aggregate jurisdiction names
Parse timestamps and create derived fields
Save intermediate dataset

Show setup code

from pathlib import Path
import pandas as pd
import numpy as np
import re
import matplotlib.pyplot as plt

# Define paths using main data folder structure
raw_path = Path("../data/ndis/raw/ndis_data_raw.csv")

print(f"Raw data path: {raw_path}")

Raw data path: ../data/ndis/raw/ndis_data_raw.csv

1.1 Load Raw Data

Code

# Load raw data
raw_df = pd.read_csv(raw_path, dtype=str)
raw_df.insert(0, "row_id", np.arange(len(raw_df)))

# Convert numeric columns
numeric_cols = [
    "offender_profiles",
    "arrestee",
    "forensic_profiles",
    "ndis_labs",
    "investigations_aided",
]
for col in numeric_cols:
    raw_df[col] = pd.to_numeric(raw_df[col], errors="coerce")

raw_df["report_month"] = pd.to_numeric(raw_df["report_month"], errors="coerce")
raw_df["report_year"] = pd.to_numeric(raw_df["report_year"], errors="coerce")

print(f"Loaded {len(raw_df):,} rows")

Loaded 32,009 rows

Code

print(f"Columns: {list(raw_df.columns)}")

Columns: ['row_id', 'timestamp', 'report_month', 'report_year', 'jurisdiction', 'offender_profiles', 'arrestee', 'forensic_profiles', 'ndis_labs', 'investigations_aided', 'parser_used', 'source_file', 'capture_date', 'capture_year']

Code

raw_df.head()

	row_id	timestamp	report_month	report_year	jurisdiction	offender_profiles	forensic_profiles	ndis_labs	investigations_aided	parser_used	source_file	capture_date	capture_year
0	0	20010715040342	NaN	NaN	Pennsylvania	7099	883	3	68	pre2007	20010715040342_pa.html	2001-07-15 04:03:42	2001
1	1	20010715040408	NaN	NaN	North Carolina	21246	727	2	40	pre2007	20010715040408_nc.html	2001-07-15 04:04:08	2001
2	2	20010715040546	NaN	NaN	Connecticut	4193	138	1	19	pre2007	20010715040546_ct.html	2001-07-15 04:05:46	2001
3	3	20010715040559	NaN	NaN	West Virginia	0	0	1	0	pre2007	20010715040559_wv.html	2001-07-15 04:05:59	2001
4	4	20010715040600	NaN	NaN	Kansas	1023	200	4	0	pre2007	20010715040600_ks.html	2001-07-15 04:06:00	2001

1.2 Clean Jurisdiction Names

Show jurisdiction cleaning code

# Keep original jurisdiction for reference
raw_df["jurisdiction_raw"] = raw_df["jurisdiction"]

# Jurisdiction cleaning patterns
JURISDICTION_PATTERNS = [
    (r"Alabama$|Alabama Stats", "Alabama"),
    (r"Alaska$|Alaska Stats", "Alaska"),
    (r"Arizona$|Arizona Stats", "Arizona"),
    (r"Arkansas$|Arkansas Stats", "Arkansas"),
    (r"California$|California Stats", "California"),
    (r"Colorado$|Colorado Stats", "Colorado"),
    (r"Connecticut$|Connecticut Stats", "Connecticut"),
    (r"Delaware$|Delaware Stats", "Delaware"),
    (r"Florida$|Florida Stats", "Florida"),
    (r"Georgia$|Georgia Stats", "Georgia"),
    (r"Hawaii$|Hawaii Stats", "Hawaii"),
    (r"Idaho$|Idaho Stats", "Idaho"),
    (r"Illinois$|Illinois Stats", "Illinois"),
    (r"Indiana$|Indiana Stats", "Indiana"),
    (r"Iowa$|Iowa Stats", "Iowa"),
    (r"Kansas$|Kansas Stats", "Kansas"),
    (r"Kentucky$|Kentucky Stats", "Kentucky"),
    (r"Louisiana$|Louisiana Stats", "Louisiana"),
    (r"Maine$|Maine Stats", "Maine"),
    (r"Maryland$|Maryland Stats", "Maryland"),
    (r"Massachusetts$|Massachusetts Stats", "Massachusetts"),
    (r"Michigan$|Michigan Stats", "Michigan"),
    (r"Minnesota$|Minnesota Stats", "Minnesota"),
    (r"Mississippi$|Mississippi Stats", "Mississippi"),
    (r"Missouri$|Missouri Stats", "Missouri"),
    (r"Montana$|Montana Stats", "Montana"),
    (r"Nebraska$|Nebraska Stats", "Nebraska"),
    (r"Nevada$|Nevada Stats", "Nevada"),
    (r"New Hampshire$|New Hampshire Stats", "New Hampshire"),
    (r"New Jersey$|New Jersey Stats", "New Jersey"),
    (r"New Mexico$|New Mexico Stats|Mexico Stats", "New Mexico"),
    (r"New York$|New York Stats", "New York"),
    (r"North Carolina$|North Carolina Stats", "North Carolina"),
    (r"North Dakota$|North Dakota Stats", "North Dakota"),
    (r"Ohio$|Ohio Stats", "Ohio"),
    (r"Oklahoma$|Oklahoma Stats", "Oklahoma"),
    (r"Oregon$|Oregon Stats", "Oregon"),
    (r"Pennsylvania$|Pennsylvania Stats", "Pennsylvania"),
    (r"Rhode Island$|Rhode Island Stats", "Rhode Island"),
    (r"South Carolina$|South Carolina Stats", "South Carolina"),
    (r"South Dakota$|South Dakota Stats", "South Dakota"),
    (r"Tennessee$|Tennessee Stats", "Tennessee"),
    (r"Texas$|Texas Stats", "Texas"),
    (r"Utah$|Utah Stats", "Utah"),
    (r"Vermont$|Vermont Stats", "Vermont"),
    (r"West Virginia$|West Virginia Stats", "West Virginia"),
    (r"Virginia$|Virginia Stats", "Virginia"),
    (r"Washington$|Washington State Stats", "Washington"),
    (r"Wisconsin$|Wisconsin Stats", "Wisconsin"),
    (r"Wyoming$|Wyoming Stats", "Wyoming"),
    (r"DC/FBI|Washington DC Stats|Lab", "DC/FBI Lab"),
    (r"DC/Metro|DC", "DC/Metro PD"),
    (r"^Army$|U\.S\. Army$|U\.S\. Army Stats", "U.S. Army"),
    (r"^PR$|Puerto Rico$|Puerto Rico Stats", "Puerto Rico"),
    (r"Tables by NDIS Participant", "Alabama"),
]


def clean_jurisdiction(value: str) -> str:
    """Clean jurisdiction name using pattern matching."""
    if value is None or (isinstance(value, float) and np.isnan(value)):
        return value
    text = str(value).strip()
    if not text or text.lower() == "nan":
        return np.nan
    for pattern, replacement in JURISDICTION_PATTERNS:
        if re.search(pattern, text, flags=re.IGNORECASE):
            return replacement
    return text


raw_df["jurisdiction"] = raw_df["jurisdiction"].apply(clean_jurisdiction)

1.3 Parse Timestamps and Create Derived Fields

Code

# Parse capture datetime from timestamp
raw_df["capture_datetime"] = pd.to_datetime(
    raw_df["timestamp"], format="%Y%m%d%H%M%S", errors="coerce"
)

# Calculate total profiles
raw_df["total_profiles"] = (
    raw_df["offender_profiles"] + raw_df["arrestee"] + raw_df["forensic_profiles"]
)

# Create asof_date from report year/month
raw_df["asof_date"] = pd.to_datetime(
    {"year": raw_df["report_year"], "month": raw_df["report_month"], "day": 1},
    errors="coerce",
)

print(f"Date range: {raw_df['capture_datetime'].min()} to {raw_df['capture_datetime'].max()}")

Date range: 2001-07-15 04:03:42 to 2025-08-15 00:20:50

Code

raw_df[["timestamp", "capture_datetime", "asof_date", "total_profiles"]].head()

	timestamp	capture_datetime	asof_date	total_profiles
0	20010715040342	2001-07-15 04:03:42	NaT	7982
1	20010715040408	2001-07-15 04:04:08	NaT	21973
2	20010715040546	2001-07-15 04:05:46	NaT	4331
3	20010715040559	2001-07-15 04:05:59	NaT	0
4	20010715040600	2001-07-15 04:06:00	NaT	1223

1.4 Save Intermediate Dataset

Code

# Sort by jurisdiction and capture datetime
raw_df = raw_df.sort_values(["jurisdiction", "capture_datetime"])
print(f"Total rows: {len(raw_df):,}")

Total rows: 32,009

Code

# Select columns for intermediate dataset
intermediate_cols = [
    "capture_datetime",
    "asof_date",
    "jurisdiction",
    "offender_profiles",
    "arrestee",
    "forensic_profiles",
    "total_profiles",
    "ndis_labs",
    "investigations_aided",
    "source_file",
]

ndis_intermediate = raw_df[intermediate_cols].copy()

print(f"Shape: {ndis_intermediate.shape}")

Shape: (32009, 10)

Code

print(f"\nJurisdictions ({ndis_intermediate['jurisdiction'].nunique()}):")


Jurisdictions (54):

Code

print(sorted(ndis_intermediate["jurisdiction"].dropna().unique()))

['Alabama', 'Alaska', 'Arizona', 'Arkansas', 'California', 'Colorado', 'Connecticut', 'DC/FBI Lab', 'DC/Metro PD', 'Delaware', 'Florida', 'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi', 'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New Hampshire', 'New Jersey', 'New Mexico', 'New York', 'North Carolina', 'North Dakota', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania', 'Puerto Rico', 'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'U.S. Army', 'Utah', 'Vermont', 'Virginia', 'Washington', 'West Virginia', 'Wisconsin', 'Wyoming']

Code

ndis_intermediate.head()

	capture_datetime	asof_date	jurisdiction	ndis_labs	investigations_aided	source_file
21	2001-07-15 04:15:59	NaT	Alabama	4	88	20010715041559_al.html
58	2001-08-22 11:55:31	NaT	Alabama	4	88	20010822115531_al.html
84	2001-09-13 00:17:54	NaT	Alabama	4	88	20010913001754_al.html
121	2001-09-13 04:17:42	NaT	Alabama	4	88	20010913041742_al.html
156	2001-09-13 05:46:14	NaT	Alabama	4	88	20010913054614_al.html

1.5 Summary

Code

# Summary statistics
print(" Intermediate Data Summary ")

 Intermediate Data Summary

Code

print(f"Total rows: {len(ndis_intermediate):,}")

Total rows: 32,009

Code

print(f"Unique jurisdictions: {ndis_intermediate['jurisdiction'].nunique()}")

Unique jurisdictions: 54

Code

print(f"Date range: {ndis_intermediate['capture_datetime'].min().year} to {ndis_intermediate['capture_datetime'].max().year}")

Date range: 2001 to 2025