Column Operations Cheatsheet
Complete reference for column manipulation in VisiData.
Column Type Keys
| Key | Type | Display | Sorts correctly for |
|---|---|---|---|
~ | str | alice | Text, labels |
# | int | 42 | Counts, IDs, integers |
% | float | 3.14 | Prices, ratios, metrics |
$ | currency | $3.14 | Financial values |
@ | date | 2025-01-15 | Timestamps, dates |
z# | len | 7 | String length |
Column Width
_ toggle width: full ↔ default
g_ toggle ALL columns: full ↔ default
z_ N set exact width to N characters
gz_ N set all visible columns to N characters
- hide current column (width = 0)
g- hide all columns with only one distinct value
gv unhide all columns
Column Renaming
^ rename current column (input prompt)
g^ rename all unnamed visible columns to current row values
z^ rename current column to current cell value
gz^ rename all visible columns to current column values
Column Reordering
H slide current column left (one position)
L slide current column right (one position)
gH slide current column to far left
gL slide current column to far right
Key Columns
! toggle current column as key column (blue)
z! unset current column as key column
g! (in Columns Sheet) toggle selected columns as key
Key columns are used as:
- X-axis or category in plots (
.) - Row dimension in pivot tables (
Shift+W) - Join keys when merging sheets (
&) - Sort criteria for
g[/g]
Creating Derived Columns
= expr new column from Python expression
g= expr set current column for selected rows to expression result
gz= expr set current column for selected rows to items of sequence
z= expr evaluate expression and set current cell only
Expression variables: column names (without spaces) are directly available.
# Examples:
= first_name + ' ' + last_name
= salary * 12
= email.split('@')[-1]
= 'senior' if age >= 65 else 'adult'
= row['column with spaces'] # for column names with spaces
Regex Operations on Columns
: regex split current column into multiple columns by regex
; regex add columns from named regex capture groups
* s<Tab>r create derived column replacing s with r
g* s<Tab>r modify selected rows in current column (in-place replace)
Named capture group example:
;
# Enter: (?P<ip>\d+\.\d+\.\d+\.\d+) (?P<method>GET|POST|PUT|DELETE)
# Creates columns: ip, method
Aggregators
+ aggregator add aggregator to current column
z+ aggregator compute aggregator result for current/selected rows
| Aggregator | Returns |
|---|---|
count | Count of non-null values |
sum | Total sum |
mean | Arithmetic mean |
median | Median (requires custom — see Module 8) |
min | Minimum value |
max | Maximum value |
stdev | Standard deviation |
distinct | Count of unique values |
list | All values as a Python list |
most_common | Most frequent value |
Aggregators appear in frequency tables (Shift+F) and pivot tables (Shift+W).
Incremental Values
i add column with 1, 2, 3... incremental values
gi set current column for selected rows to incremental values
zi step add column with values at given step (e.g., 10, 20, 30)
gzi step set selected rows at given step
Frozen / Cached Columns
' add a frozen copy of current column (all cells evaluated)
g' open a frozen copy of current sheet
z' gz' add/reset cache for current/all columns
Expanding Nested Columns
For JSON data with [list] or {dict} cells:
( expand current column one level
g( expand ALL such columns one level
z( depth expand to given depth (0 = fully)
) collapse (unexpand) current column
g) collapse all expanded columns
zM row-wise expand current column (list items become rows)
Columns Sheet (Shift+C)
Open the Columns Sheet to view and bulk-edit all column properties:
Shift+C open Columns Sheet
gC open Columns Sheet for all visible columns across all sheets
Inside the Columns Sheet:
e edit property at current cell (name, type, width, aggregator)
g! gz! toggle/unset selected columns as key columns on source sheet
g+ aggregator add aggregator to selected columns
g- hide selected columns
g~ g# g% g$ g@ gz# z% set type of selected columns
Enter open Frequency Table for the referenced column
Column Type in Aggregations
| Type set | What aggregators work |
|---|---|
| None (str) | count, distinct, list, most_common |
# int | All numeric + count, distinct |
% float | All numeric + count, distinct |
@ date | min, max, count, distinct |
Always cast numeric columns before adding sum or mean aggregators.
Quick Reference: Derived Column Patterns
# Full name
= first_name + ' ' + last_name
# Year from date column (typed as @)
= order_date.year
# Domain from email
= email.split('@')[-1]
# Conditional label
= 'high' if amount > 1000 else 'low'
# String length
= len(name)
# Normalize to lowercase
= status.lower().strip()
# Extract last path segment from URL
= path.rstrip('/').split('/')[-1]