Summarization and Pivot Tables
Excel PivotTables find their equivalent in VisiData's Frequency Sheet and Pivot Sheet. The Frequency Sheet groups one column and counts occurrences. The Pivot Sheet cross-tabulates two dimensions with aggregated values — exactly like a PivotTable with row labels, column labels, and a values field.
Learning Focus
The pivot workflow: (1) mark row-group column as key ! → (2) set aggregator on value column + sum → (3) press Shift+W for pivot. For a simpler one-column summary, use Shift+F (frequency table).
PivotTable Equivalents
| Excel / Google Sheets | VisiData Equivalent | Description |
|---|---|---|
PivotTable | ! on row key → + sum/mean/count on value → Shift+W | Pivot table |
| PivotTable Filter | | or z| filter before creating pivot | Filter pivot data |
| PivotTable Slicer | Shift+F → Enter to drill into group | Drill-down filter |
GETPIVOTDATA | Enter on any pivot cell → opens source rows | Drill to detail |
| Grand Total | Status bar shows sum/mean at bottom | Auto-calculated |
| Subtotal | gF (frequency by key columns) | Grouped subtotals |
=GROUPBY(A:A, C:C, SUM) | Set ! on A → + sum on C → Shift+F or Shift+W | Group and aggregate |
| PivotChart | . after setting key column | Visualize pivot |
| Show values as % of total | Frequency table shows percent column automatically | Percentage view |
| Calculated Field | = derived column before creating pivot | Custom calculations |
| Drill Down (double-click) | Enter on any frequency/pivot row | Source data drill-down |
| Refresh PivotTable | Ctrl+R reload | Refresh data |
| Slicer / Timeline | z| date > '2025-01-01' then Shift+W | Date-filtered pivot |
| Power Query / Get & Transform | :, ; regex split + = derived columns | Data transformation |
Frequency Table (One-Column Summary)
The Frequency Sheet replaces Excel's "Count of X" pivot — group one column and count rows:
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Group by customer and count orders
# Move to 'customer' column
Shift+F
# Frequency Sheet shows:
# customer | count | percent
# Sorted by count descending
# Sort by customer name instead
[
# Drill into one customer's orders
# Navigate to a customer row → Enter
Adding Aggregators to Frequency Table
Set an aggregator on a value column before opening the frequency table:
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Cast amount to float
# Move to 'amount' → %
# Set sum aggregator on amount
# Move to 'amount' column
+ sum
# Now open frequency table on 'customer'
# Move to 'customer' column
Shift+F
# Frequency Sheet now shows:
# customer | count | percent | amount (sum per customer)
Pivot Table (Two-Dimension Cross-Tab)
Full pivot: rows = one categorical dimension, columns = aggregated values:
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Step 1: Cast amount to float
# Move to 'amount' → %
# Step 2: Set mean aggregator on amount
# Move to 'amount'
+ mean
# Step 3: Mark 'region' as key column (row dimension)
# Move to 'region'
!
# Step 4: Open Pivot Sheet
Shift+W
# Result: rows = region, columns = mean amount
For a two-axis pivot (row × column cross-tab), mark two key columns before Shift+W:
# Mark region as key 1
# Move to 'region' → !
# Mark status as key 2
# Move to 'status' → !
# Open pivot
Shift+W
# Rows = region, nested by status, with aggregated values
GROUPBY + Subtotals
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Equivalent to Excel GROUPBY(region, amount, SUM)
# Cast amount: %
# Set sum aggregator: + sum
# Mark 'region' as key: !
Shift+F # Frequency by region
# Shows: region, count, percent, amount (sum)
Percentage View
The Frequency Sheet automatically includes a percent column (percentage of total rows):
# Move to 'status' column → Shift+F
# → status | count | percent
# Percent = (count / total_rows) * 100
# To see percentage of total amount:
# Set amount aggregator to sum before Shift+F
+ sum
Shift+F
# Now frequency shows both count and sum per group
# Manually compute % by creating derived column in frequency sheet:
=
# Enter: amount / sum_total * 100 (if you have total in memory)
Drill-Down into Source Data
In any Frequency or Pivot sheet, press Enter to see the original rows:
# In Frequency Sheet
# Navigate to 'APAC' row
Enter
# Opens: all source rows where region == 'APAC'
# Navigate, analyze, then return
q
Calculated Fields (Derived Column Before Pivot)
Create a derived column first, then pivot on it — equivalent to PivotTable Calculated Field:
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Step 1: Create calculated field
=
# Enter: amount * 1.09 # add GST
# Name: amount_with_gst
# Step 2: Set aggregator on calculated field
+ sum
# Step 3: Pivot by region
# Move to 'region' → !
Shift+W
# Pivot shows sum of amount_with_gst per region
Date-Filtered Pivot (Slicer/Timeline equivalent)
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Cast order_date to date: @
# Filter to 2025 only (Slicer equivalent)
z|
# Enter: order_date.year == 2025
# Open filtered sheet
"
# Now pivot within the filtered sheet
# Move to 'region' → !
+ sum # on amount
Shift+W
Grand Total (Status Bar)
VisiData shows aggregated totals in the status bar when z+ is used:
# After setting + sum on amount:
z+
# Status bar: sum = 145,230.50
# This is the Grand Total equivalent
Monthly Trend Summary
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Cast order_date: @
# Create period column
=
# Enter: f"{order_date.year}-{order_date.month:02d}"
# Name: period
# Set sum aggregator on amount
+ sum
# Mark period as key
# Move to 'period' → !
# Open frequency table
# Move to 'period' → Shift+F
# Shows: period | count | percent | amount (monthly totals)
# Sort chronologically
[
Hands-On Practice
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# 1. Cast amount to float: move to 'amount' → %
# 2. Simple frequency by region:
# Move to 'region' → Shift+F → view counts
# Press q → return
# 3. Frequency with sum:
# Move to 'amount' → + sum
# Move to 'region' → Shift+F
# Verify: amount column shows sum per region
# Drill into APAC: navigate to APAC row → Enter → q
# 4. Pivot table:
# Move to 'region' → !
# Shift+W
# View pivot result
# Press q → return to source
# 5. Date-based summary:
# Cast order_date → @
# Create period column: = f"{order_date.year}-{order_date.month:02d}"
# Move to 'period' → Shift+F
# Sort chronologically: [