Data Cleaning Workflow
VisiData is a powerful data cleaning tool. This lesson provides a systematic, reproducible workflow for cleaning CSV and tabular data — from inspection to final export.
Learning Focus
Follow this workflow on any messy dataset. The key insight is that VisiData operations are non-destructive — your source file is unchanged until you explicitly save with Ctrl+S.
Sample Dirty Data
cat > ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv << 'EOF'
id,customer,amount,status,region
1,Alice,100.50,ACTIVE,sg
2,Bob,-50,active,
3,Carol,200,Active,kl
4,Alice,100.50,ACTIVE,sg
5,Dave,abc,inactive,jk
EOF
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
Inside VisiData — what we can see is wrong:
id customer amount status region
1 Alice 100.50 ACTIVE sg
2 Bob -50 active ← missing region
3 Carol 200 Active kl
4 Alice 100.50 ACTIVE sg ← duplicate of row 1
5 Dave abc inactive jk ← invalid amount
Step 1 — Inspect with Describe Sheet
Shift+I
Result:
name type nulls distinct min max
id str 0 5
customer str 0 4
amount str 0 5 -50 abc ← wrong type, has 'abc'
status str 0 3 ← 3 variants: ACTIVE/active/Active
region str 1 3 ← 1 null
This immediately shows: amount is typed as string (should be float), status has 3 inconsistent variants, and region has 1 null.
Step 2 — Fix Column Types
# Move to 'id' column
# # cast to integer
# Move to 'amount' column
% # cast to float
# Row 5 (Dave, 'abc') now shows #ERR
Step 3 — Remove Invalid Rows
# Select row 5 (the #ERR row)
z|
# Enter: amount is None (VisiData treats cast failures as None)
# Delete selected invalid rows
gd
# Undo if needed: U
Result — Dave's row removed:
id customer amount status region
1 Alice 100.5 ACTIVE sg
2 Bob -50.0 active
3 Carol 200.0 Active kl
4 Alice 100.5 ACTIVE sg
Step 4 — Remove Negative Amounts
z|
# Enter: amount < 0
gd
Result — Bob's negative row removed:
id customer amount status region
1 Alice 100.5 ACTIVE sg
3 Carol 200.0 Active kl
4 Alice 100.5 ACTIVE sg
Step 5 — Fill Null Region
# Select rows with empty region
z|
# Enter: not region or region == ''
# Set to 'Unknown'
ge
# Enter: Unknown
Step 6 — Normalize Status Values
# Select all rows
gs
# Normalize all case variants to lowercase
g*
# Enter: (?i)active<Tab>active
# Verify: all status values are now 'active'
Shift+F
# on 'status' column
Before:
status count
ACTIVE 2
active 1
Active 1
After:
status count
active 4
Step 7 — Find and Remove Duplicates
# Open frequency table on 'id' column
Shift+F
# Press ] to sort by count descending
# Look for any count > 1 → those are duplicate IDs
Frequency table shows:
id count
1 2 ← duplicate
3 1
4 1
# Press Enter on id=1 row → see the duplicate rows
# Back in source: select one copy of the duplicate
# Press d to delete it
Step 8 — Final Inspection and Export
# Final check
Shift+I
# Verify: nulls=0, types correct, all amounts positive
# Save to new file (never overwrite the source!)
Ctrl+S
# Enter: ~/github/practice-folder/visidata/04-cleaning/clean_orders.csv
Save the Workflow as CommandLog
# After all cleaning steps are done:
Shift+D # view CommandLog for this sheet
Ctrl+S
# Enter: /tmp/clean_orders_workflow.vdj
Replay on a new file next time:
vd --play /tmp/clean_orders_workflow.vdj \
--batch new_raw_orders.csv \
-o new_clean_orders.csv
Complete Workflow Reference
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
1. Shift+I → inspect nulls, types, value ranges
2. # % @ ~ → cast columns to correct types
3. z| expr → gd → remove invalid rows (#ERR, negatives)
4. z| → ge "value" → fill nulls with default
5. gs → g* → normalize text values (case, whitespace)
6. Shift+F on key → find duplicates (count > 1) → delete
7. Shift+I → verify: nulls=0, types correct
8. Ctrl+S → save clean_orders.csv
Troubleshooting
| Problem | Cause | Fix |
|---|---|---|
f (fill) fills wrong direction | Column is sorted differently | Sort by a stable key first |
g* replace doesn't match | Regex case sensitivity | Add (?i) prefix for case-insensitive |
gd deleted wrong rows | Wrong rows selected | Press U immediately to undo |
| Date cast fails | Non-standard date format | Set options.disp_date_fmt in .visidatarc |
Hands-On Practice
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Follow all 8 steps above in order:
# 1. Shift+I → inspect the mess
# 2. % → cast amount to float (Dave row shows #ERR)
# 3. z| amount is None → gd → remove Dave's invalid row
# 4. z| amount < 0 → gd → remove Bob's negative row
# 5. z| not region → ge → Unknown
# 6. gs → g* → (?i)active<Tab>active
# 7. Shift+F on 'id' → find and remove duplicate
# 8. Ctrl+S → save as /tmp/clean.csv