Skip to main content

Combining Datasets

VisiData can join, append, and merge multiple sheets using keyboard commands — without SQL or pandas. This lesson covers the & operator join types, sheet append, and the Melted Sheet for unpivoting.

Learning Focus

Understand that & always works on selected sheets in the Sheets Sheet (Shift+S). The jointype controls the merge behavior — the same as SQL join types.

Join Two Sheets​

The join workflow:

Step-by-Step Join​

# 1. Open both files
vd servers.csv roles.csv

# 2. Open Sheets Sheet
Shift+S

# 3. Select the two sheets to join
# Move to first sheet row, press s
# Move to second sheet row, press s

# 4. Set key columns on each sheet first (back in the source sheets)
# Go to servers.csv, mark 'hostname' as key: !
# Go to roles.csv, mark 'hostname' as key: !

# 5. Back in Sheets Sheet, press &
# Choose jointype: inner, outer, full, diff, append, extend, merge

Join Types​

TypeSQL equivalentBehavior
innerINNER JOINOnly rows with matching keys in all sheets
outerLEFT JOINAll rows from first sheet, nulls for unmatched
fullFULL OUTER JOINAll rows from all sheets
diffEXCEPTRows NOT present in all sheets
appendUNION ALLAll rows from all sheets (stack vertically)
concatUNION ALLLike append but keep first sheet's columns/type
extend—Copy first sheet, add columns from others
merge—First sheet rows, fill empty cells from second

Append Two Sheets​

# Simple vertical append (stack rows):
# Sheets Sheet → select both → & → choose append

Or use the keyboard shortcut from any sheet:

&               # append top two sheets in sheet stack
g&              # append ALL sheets in sheet stack

Transposing​

T               # open transposed sheet (rows ↔ columns)

Useful when columns represent time periods and you want to treat them as rows.

Melted Sheet (Unpivot)​

The Melted Sheet unpivots non-key columns into variable and value rows — the inverse of a pivot table.

# Set key columns first (the columns to keep as-is)
!               # mark 'region' as key
!               # mark 'product' as key

Shift+M         # open Melted Sheet
# All non-key columns become rows with 'variable' and 'value' columns

Global version with regex:

gM  <regex>    # melt non-key columns matching regex into variable/value rows

Memory Sheet​

Alt+Shift+M    # open Memory Sheet
# Shows values stored by z+ (aggregator results)
# Useful as a clipboard for computed statistics

Practical Use Cases​

Enrich Server Inventory with Role Data​

# servers.csv: hostname, ip, status
# roles.csv: hostname, role, team

vd servers.csv roles.csv

# Set 'hostname' as key on both sheets:
# In servers.csv: move to hostname, !
# In roles.csv: move to hostname, !

# Sheets Sheet → select both → & → inner
# Result: hostname, ip, status, role, team

Append Monthly Log Exports​

vd jan.csv feb.csv mar.csv

Shift+S
# Select all three (s on each)
# & → append
# All rows combined into one sheet

Normalize Wide Data with Melt​

# Input: region, q1_sales, q2_sales, q3_sales, q4_sales
vd quarterly.csv

# Mark 'region' as key: !
Shift+M
# Output: region, variable (q1_sales/q2_sales...), value
# Now suitable for frequency tables and time-series analysis

Troubleshooting Matrix​

ProblemCauseFix
Join produces empty resultKey columns not setPress ! on the key column in each source sheet
& joins wrong sheetsWrong sheets selectedUse Shift+S and select explicitly with s
Append duplicates columnsColumn names don't matchRename columns to match first
Melt creates too many rowsToo many non-key columnsBe precise about which columns to keep as keys

Best Practices​

  • Always set key columns before navigating to the Sheets Sheet to join — the key designation must exist on the source sheet.
  • Use append for homogeneous datasets (same schema). Use extend when schemas differ but share a key.
  • After joining, use the Describe Sheet (Shift+I) to verify the join produced the expected row count and no unexpected nulls.

Hands-On Practice​

cat > /tmp/servers.csv << 'EOF'
hostname,ip,status
web01,10.0.0.1,active
db01,10.0.0.2,active
EOF

cat > /tmp/roles.csv << 'EOF'
hostname,role,team
web01,webserver,frontend
db01,database,backend
monitor01,monitoring,ops
EOF

vd /tmp/servers.csv /tmp/roles.csv

# 1. In servers.csv: move to hostname, press !
# 2. In roles.csv: move to hostname, press !
# 3. Press Shift+S → select both sheets
# 4. Press & → choose: outer
# 5. Observe: monitor01 appears with null ip/status (left join behavior)

What's Next​