Pivot Tables and Descriptive Stats
VisiData's pivot table (Shift+W) and Describe Sheet (Shift+I) give you structured aggregation and statistical profiling without writing a single SQL query or Python script.
Learning Focus
Master the setup ritual: set key columns → set aggregators → press Shift+W for pivot. Remember that the Describe Sheet (Shift+I) works instantly on any sheet with no setup required.
Pivot Tables
Setup and Creation
Pivot tables require:
- One or more key columns (row dimension)
- One or more aggregated columns with an aggregator set
# Step 1: Set the row dimension (key column)
# Move to 'region' column
! # mark as key column (blue)
# Step 2: Set aggregator on value column
# Move to 'salary' column
# Cast to float first: press %
+ # add aggregator
# Enter: sum
# Step 3: Open pivot table
# Move cursor back to 'salary' column
Shift+W # open pivot table grouped by key column(s)
The pivot table shows:
region sum_salary
sg 250000
kl 157000
jk 142000
Multi-Key Pivot
Mark multiple key columns to get a cross-tabulated view:
# Mark 'region' as key: !
# Mark 'role' as key: !
# Move to 'salary' column, set aggregator: + mean
Shift+W
Result:
region role mean_salary
sg engineer 81500
sg manager 95000
kl analyst 65000
kl manager 92000
Navigate Pivot Rows
Enter open sheet of source rows for current pivot row
zEnter open source rows for current pivot cell
Describe Sheet (Shift+I)
The Describe Sheet provides summary statistics for every column with zero setup.
Shift+I # open Describe Sheet for current sheet
gI # open Describe Sheet for ALL visible columns across all sheets
The Describe Sheet shows for each column:
| Column | Meaning |
|---|---|
name | Column name |
type | Column type |
nulls | Count of null/empty values |
distinct | Number of unique values |
min | Minimum value |
max | Maximum value |
mean | Mean (numeric only) |
stdev | Standard deviation (numeric only) |
Navigate Describe Sheet
Enter open a Frequency Table for the described column
zs select source rows being described in current cell
zu unselect those rows
! toggle current column as key column on source sheet
One-Line Summary (zF)
# Inside any sheet:
zF # show one-line summary for all rows and selected rows in status bar
This gives a quick count, distinct, min, max, mean for the current column without opening a new sheet.
Practical Use Cases
Revenue by Region and Product Category
vd /var/www/html/exports/sales.csv
# Cast 'revenue' to float: move there, press %
# Add aggregator: + sum
# Mark 'region' as key: move there, !
# Mark 'category' as key: move there, !
Shift+W
# Pivot: region × category → sum_revenue
Instant Data Quality Audit
vd /var/www/html/exports/users.csv
Shift+I
# See which columns have nulls (nulls > 0)
# See which have 1 distinct value (constant — useless column)
# See min/max for numeric columns to catch outliers
Count Requests per IP and Method
vd /var/log/nginx/access.log
# Mark 'ip' as key: !
# Mark 'method' as key: !
# Move to 'status' column, + count
Shift+W
# Pivot: ip × method → count_status
Combining Pivot with Drill-Down
Troubleshooting Matrix
| Problem | Cause | Fix |
|---|---|---|
| Pivot shows only one column | No key column set | Press ! on the row dimension column first |
| Aggregator missing from pivot | Aggregator not set | Move to value column and press + |
stdev shows in Describe Sheet as blank | Column is string type | Cast to # or % first |
| Pivot is slow | Millions of rows with many distinct keys | Use frequency table instead for simple counts |
Best Practices
- Open the Describe Sheet on any new dataset immediately — it gives a full data quality overview in one keystroke.
- For simple counts, use frequency tables (
Shift+F) — they are faster and require no aggregator setup. - Use pivot tables when you need cross-tabulation (row × column combination with aggregation).
Hands-On Practice
vd /tmp/employees.csv
# Pivot: region × role → total salary
# 1. Move to 'salary', press % (float cast)
# 2. Press + → enter: sum
# 3. Move to 'region', press !
# 4. Move to 'role', press !
# 5. Move cursor back to 'salary'
# 6. Press Shift+W → pivot table opens
# 7. Press q → return to source
# 8. Press Shift+I → Describe Sheet
# 9. Inspect nulls, distinct, min, max for each column
# 10. Press q → return