Quality Control and Audit
Data quality checks in VisiData use Python expressions for null detection, type validation, and outlier identification. The Describe Sheet (Shift+I) provides a full statistical audit of every column in one view — the equivalent of Excel's built-in data statistics and conditional formatting highlights.
Learning Focus
Start every audit with Shift+I (Describe Sheet) — it instantly surfaces nulls, min/max outliers, and column types. Follow up with targeted z| filters to isolate specific quality issues.
Quality Function Reference
| Excel / Google Sheets | VisiData Equivalent | Description |
|---|---|---|
=ISBLANK(A2) | z| col is None or col == '' | Check for empty/null |
=ISNUMBER(A2) | Cast with #; #ERR if not numeric | Is numeric check |
=ISTEXT(A2) | Cast with ~ | Is text check |
=ISERROR(A2/B2) | Ctrl+E to view error traceback | Error inspection |
=TRIM(A2) | = value.strip() | Remove whitespace |
=CLEAN(A2) | = ''.join(c for c in value if c.isprintable()) | Remove non-printable chars |
Remove Duplicates | Shift+F → check count > 1 → delete extras | Deduplicate |
Data Validation | Type casting + | filter for invalid values | Validate data |
=COUNTBLANK(A:A) | Shift+I → nulls column | Count of blanks |
Conditional Formatting | ! key column (blue); selected rows (yellow) | Visual highlights |
Find & Replace | g* regex replace or ge bulk set | Bulk edit |
Highlight Errors | z| col == None → selected rows visible | Flag errors |
=IF(A2<0, "Negative", "OK") | = 'negative' if value < 0 else 'ok' | Value validation flag |
=ISODD(A2) | = value % 2 != 0 | Odd/even check |
Full Data Profile with Describe Sheet
Start every audit with Shift+I:
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
Shift+I
# Opens Describe Sheet with columns:
# name | type | count | nulls | min | max | mean | stdev
The Describe Sheet reveals:
nulls— count of null/empty cells per columnmin/max— quick outlier detectionmean/stdev— distribution checkcount— non-null row count (vs total rows = missing data)
From the Describe Sheet, drill into any column:
# Navigate to 'amount' row in Describe Sheet
Enter
# Opens: Frequency Sheet for 'amount' column
q
# Return to Describe Sheet
Null Detection
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Find all rows with null amount
z|
# Enter: amount is None
# Count is shown in status bar
# Open filtered sheet to see them
"
# Fix: fill nulls with 0
# Back on source sheet: select null rows first
z|
# Enter: amount is None
# Bulk-set to 0
ge
# Enter: 0
Numeric Type Validation
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Cast amount to float — #ERR cells reveal non-numeric values
# Move to 'amount' column → %
# Filter only error cells
z|
# Enter: amount is None or str(amount) == '#ERR'
# These rows have non-numeric 'amount' values
"
Duplicate Detection
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
# Find duplicate employee IDs
# Move to 'employee_id' column
Shift+F
# In Frequency Sheet: cast 'count' to int (#)
# Filter duplicates
z|
# Enter: count > 1
# Drill into duplicates
Enter
# See all source rows with that employee_id
# Select and delete extras
s # select duplicate row (not the original)
q # return to source
gd # delete selected
Outlier Detection
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Cast amount to float: %
# Find statistical outliers using Describe Sheet mean + stdev
Shift+I
# Note mean and stdev values
q
# Filter rows > mean + 3×stdev (extreme outliers)
z|
# Enter: amount > 500 # adjust threshold based on your data
"
# Inspect outlier orders
Data Validation — Allowed Values
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Validate: status must be one of known values
z|
# Enter: status not in ['pending', 'processing', 'shipped', 'delivered', 'cancelled']
# These rows have invalid status values
"
# Review and fix
Whitespace and Encoding Audit
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Detect leading/trailing whitespace in customer names
z|
# Enter: customer != customer.strip()
"
# Fix by normalizing
gs
g=
# Enter: customer.strip()
Negative Value Check
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Flag negative amounts (should not exist in orders)
z|
# Enter: amount < 0
"
# Review — are these credits, refunds, or data errors?
Visual Highlights
VisiData uses color to flag data quality visually:
| Color | Meaning |
|---|---|
| Yellow rows | Selected rows |
| Blue column header | Key column (!) |
Red #ERR | Expression error or failed type cast |
⌀ / ∅ | Null value |
# Make a validation column that flags issues
=
# Enter: 'INVALID' if (amount is None or amount < 0 or status not in ['pending', 'shipped']) else 'OK'
# Name: audit_flag
# Filter to INVALID rows
z|
# Enter: audit_flag == 'INVALID'
"
Audit Report Workflow
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Step 1: Statistical profile
Shift+I
# Note: columns with nulls, suspicious min/max
q
# Step 2: Cast numeric columns
# Move to 'amount' → %
# Step 3: Find nulls
z|
# Enter: amount is None
# Note count in status bar
gu # deselect
# Step 4: Find negatives
z|
# Enter: amount < 0
"
# View and save as audit report
q
# Step 5: Duplicate check
# Move to 'order_id' → Shift+F
# Filter count > 1 → note duplicates
q
# Step 6: Save clean version
Ctrl+S
# Enter: ~/github/practice-folder/visidata/04-cleaning/orders_audited.csv
Hands-On Practice
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# 1. Open Describe Sheet: Shift+I
# Note: which columns have nulls? What are the min/max of amount?
# Press q → return
# 2. Cast amount to float: move to 'amount' → %
# 3. Find null amounts: z| → amount is None → check count in status bar
# 4. Create audit flag column:
=
# Enter: 'FAIL' if amount is None else 'FAIL' if amount < 0 else 'PASS'
# Name: audit
# 5. Frequency of audit flags: move to 'audit' → Shift+F
# 6. Drill into FAIL rows: navigate to FAIL row → Enter
# 7. Save FAIL rows: " → Ctrl+S → /tmp/audit_failures.csv