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.
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.
Sample Data Used in This Lesson
mkdir -p ~/github/practice-folder/visidata/03-join
cat > ~/github/practice-folder/visidata/03-join/01-servers.csv << 'EOF'
hostname,ip,status
web01,10.0.0.1,active
db01,10.0.0.2,active
cache01,10.0.0.3,standby
EOF
cat > ~/github/practice-folder/visidata/03-join/02-roles.csv << 'EOF'
hostname,role,team
web01,webserver,frontend
db01,database,backend
monitor01,monitoring,ops
EOF
Inner Join (Matching Rows Only)
Goal: Combine server IPs with role information — only servers present in BOTH files.
Step 1 — Open both files:
vd ~/github/practice-folder/visidata/03-join/01-servers.csv \
~/github/practice-folder/visidata/03-join/02-roles.csv
Step 2 — Set key column in servers.csv:
# Move to 'hostname' column → press !
# hostname turns blue (key column)
Step 3 — Switch to roles.csv and set key column:
Ctrl+^ # swap to roles.csv
# Move to 'hostname' column → press !
Step 4 — Open Sheets Sheet and join:
Shift+S
# Press s on servers.csv row
# Press s on roles.csv row
&
# Choose: inner
Result — only web01 and db01 (present in BOTH files):
hostname ip status role team
web01 10.0.0.1 active webserver frontend
db01 10.0.0.2 active database backend
monitor01 is excluded (not in servers.csv). cache01 is excluded (not in roles.csv).
Outer Join (All Rows from First File)
# Same setup, but choose: outer
&
# outer
Result — all rows from servers.csv, nulls for unmatched:
hostname ip status role team
web01 10.0.0.1 active webserver frontend
db01 10.0.0.2 active database backend
cache01 10.0.0.3 standby ← no match in roles.csv → null
Append (Stack Rows from Multiple Files)
Append is like UNION ALL in SQL — stack all rows vertically.
# Three monthly CSVs with same columns
vd ~/github/practice-folder/visidata/06-append/01-jan.csv \
~/github/practice-folder/visidata/06-append/02-feb.csv \
~/github/practice-folder/visidata/06-append/03-mar.csv
Shift+S
# Select all three sheets (s on each row)
&
# Choose: append
Result:
hostname region month amount
web01 sg jan 250
db01 kl jan 400
web01 sg feb 300
db01 kl feb 450
web01 sg mar 275
...
Melt (Unpivot Wide Data)
Unpivot a wide dataset into long format — turn column headers into row values.
Before (wide format):
region q1_sales q2_sales q3_sales q4_sales
sg 250000 280000 300000 320000
kl 180000 190000 210000 220000
# Mark 'region' as key column
!
# Open Melted Sheet
Shift+M
After (long format):
region variable value
sg q1_sales 250000
sg q2_sales 280000
sg q3_sales 300000
sg q4_sales 320000
kl q1_sales 180000
...
Long format is compatible with frequency tables and pivot operations.
Selective Melt with Regex (gM)
If you only want to melt certain columns:
# Mark 'region' as key
!
gM
# Enter: q\d+_sales
# Only melt columns matching the regex — leave others intact
Result:
region annual_budget variable value
sg 1000000 q1_sales 250000
sg 1000000 q2_sales 280000
(annual_budget was NOT melted — it stays as a regular column)
Join Types Reference
| Type | SQL equivalent | Behavior |
|---|---|---|
inner | INNER JOIN | Only rows with matching keys in ALL sheets |
outer | LEFT JOIN | All rows from first sheet, nulls for unmatched |
full | FULL OUTER JOIN | All rows from all sheets |
diff | EXCEPT | Rows NOT present in all sheets |
append | UNION ALL | Stack all rows from all sheets (columns matched by name) |
concat | UNION ALL | Like append, columns matched by position |
extend | — | Copy first sheet, add columns from others |
merge | — | First sheet rows, fill empty cells from second |
Transpose (T)
T
# Rows become columns, columns become rows
# Useful for wide time-series data
Troubleshooting
| Problem | Cause | Fix |
|---|---|---|
| Join produces empty result | Key columns not set | Press ! on key column in EACH source sheet |
& joins wrong sheets | Wrong sheets selected | Use Shift+S, select explicitly with s |
| Append duplicates columns | Column names don't match | Rename columns to match first |
| Melt creates too many rows | Too many non-key columns | Mark more columns as key before melting |
Hands-On Practice
vd ~/github/practice-folder/visidata/03-join/01-servers.csv \
~/github/practice-folder/visidata/03-join/02-roles.csv
# 1. In servers.csv: move to hostname → press !
# 2. Press Ctrl+^ → swap to roles.csv → move to hostname → press !
# 3. Press Shift+S → select both sheets with s
# 4. Press & → choose outer
# 5. Observe: cache01 has null role/team (outer join behavior)
# 6. Press q → return to Sheets Sheet
# 7. Press & → choose inner → compare result