Incremental Values and Regex Replace
Incremental columns generate sequential integers automatically. Regex replace creates derived columns or modifies values in bulk using pattern matching. Together they cover the most common bulk data transformation workflows.
Use i to add a row ID sequence for data that lacks a primary key. Use g* for in-place normalization of values (e.g., lowercase all statuses). Use * when you want a non-destructive replacement in a new column. See also Adding Columns and Splitting and Extracting.
Sample Data Used in This Lesson
cat > ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv << 'EOF'
order_id,customer,status,amount,city
1001,Alice,active,250,singapore
1002,Bob,PENDING,150,kuala lumpur
1003,Carol,Active,400,singapore
1004,Dave,error,0,jakarta
1005,Eve,pending,320,kuala lumpur
EOF
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
Inside VisiData — notice inconsistent casing and city formatting:
order_id customer status amount city
1001 Alice active 250 singapore
1002 Bob PENDING 150 kuala lumpur
1003 Carol Active 400 singapore
1004 Dave error 0 jakarta
1005 Eve pending 320 kuala lumpur
Add a Sequential Row ID Column (i)
The i key creates a new column with sequential integers starting at 1.
# Move cursor to the leftmost column (gH to jump there)
gH
i
# Enter name: row_id
Result — new row_id column on the left:
row_id order_id customer status amount city
1 1001 Alice active 250 singapore
2 1002 Bob PENDING 150 kuala lumpur
3 1003 Carol Active 400 singapore
4 1004 Dave error 0 jakarta
5 1005 Eve pending 320 kuala lumpur
Add ID Column with Custom Step (zi)
Use zi when you want IDs like 100, 200, 300... (useful for batch numbering):
zi
# Enter step: 100
# Enter name: batch_id
Result:
batch_id order_id customer
100 1001 Alice
200 1002 Bob
300 1003 Carol
Non-Destructive Regex Replace (*)
* creates a new column with replacements applied — the original column is untouched.
Scenario: Normalize city names to Title Case — create a cleaned version without modifying original.
# Move to 'city' column
*
# Enter: singapore<Tab>Singapore
Before:
city city_new
singapore Singapore
kuala lumpur kuala lumpur
After pressing * with singapore<Tab>Singapore:
city city_new
singapore Singapore ← replaced
kuala lumpur kuala lumpur ← unchanged (different pattern)
In-Place Regex Replace (g*)
g* modifies the current column for all selected rows — destructive but undoable with U.
Scenario: Normalize status column — standardize all variations to lowercase.
# Select all rows
gs
# Normalize 'PENDING' → 'pending'
g*
# Enter: PENDING<Tab>pending
# Normalize 'Active' → 'active'
g*
# Enter: Active<Tab>active
Before:
status
active
PENDING
Active
error
pending
After both g* replacements:
status
active
pending
active
error
pending
# Undo if needed
U
Replace via Python Expression (g=)
Sometimes g= is cleaner than g* for multi-step normalization:
# Select all rows
gs
# Normalize status to lowercase
g=
# Enter: status.lower().strip()
# Title-case city names
# Move to 'city' column
g=
# Enter: city.title()
Before:
status city
PENDING kuala lumpur
Active singapore
After g= with status.lower().strip() and city.title():
status city
pending Kuala Lumpur
active Singapore
Set Incremental Values on Selected Rows (gi)
If a column already exists but some rows have null/missing IDs:
# Select rows with null order_id
z|
# Enter: order_id is None
# Set incremental values for selected rows only
gi
Complete Reference
| Key | Action |
|---|---|
i | Add new column with sequential integers (1, 2, 3...) |
zi step | Add new column with custom increment step |
gi | Set current column for selected rows to sequential values |
gzi step | Set current column for selected rows at custom step |
* | Create new column with regex replacement (non-destructive) |
g* | Modify current column for selected rows with regex (in-place) |
gz* | Modify all visible columns for selected rows with regex |
Hands-On Practice
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# 1. gH → move to leftmost position
# 2. i → add row_id column
# 3. gs → select all rows
# 4. Move to 'status' column → g* → PENDING<Tab>pending
# 5. Press U to undo
# 6. Move to 'city' column → * → singapore<Tab>Singapore (non-destructive)
# 7. Compare original and new column side by side
# 8. Press Ctrl+S → save as /tmp/orders_normalized.csv