Capstone Project — End-to-End Data Investigation
This capstone integrates skills from every module into a single reproducible investigation. You will profile, clean, analyze, visualize, and export a realistic e-commerce dataset — and capture the entire workflow as a replayable CommandLog.
Prerequisites
Before starting, be comfortable with: loading files (M3), row filtering and column casting (M5–6), CommandLog (M7), frequency and pivot tables (M8), and canvas plots (M9).
Dataset Setup
mkdir -p ~/github/practice-folder/visidata/capstone
cat > ~/github/practice-folder/visidata/capstone/orders.csv << 'EOF'
order_id,customer_id,product_id,region,amount,status,created_date
O001,C01,P03,SG,450.00,complete,2025-01-05
O002,C02,P01,KL,120.50,complete,2025-01-06
O003,C03,P02,SG,890.00,PENDING,2025-01-07
O004,C01,P01,SG,320.00,complete,2025-01-08
O005,C04,P03,JK,75.25,failed,2025-01-09
O006,C02,P02,KL,660.00,complete,2025-01-10
O007,C05,P01,SG,210.00,Pending,2025-01-11
O008,C03,P03,SG,980.00,complete,2025-01-12
O009,C04,P02,JK,440.00,complete,2025-01-13
O010,C05,P01,SG,,complete,2025-01-14
EOF
cat > ~/github/practice-folder/visidata/capstone/customers.csv << 'EOF'
customer_id,name,tier,email
C01,Alice Tan,gold,alice@shop.com
C02,Bob Lim,silver,bob@shop.com
C03,Carol Wong,gold,carol@shop.com
C04,Dave Singh,bronze,dave@shop.com
C05,Eve Chong,silver,eve@shop.com
EOF
Phase 1 — Profile
vd ~/github/practice-folder/visidata/capstone/orders.csv
Shift+I # Describe Sheet
Findings:
amount: 1 null (O010 missing amount)status: 4 distinct values —complete,PENDING,Pending,failed(3 should be 2)
# Check status distribution
q # back to source
# Move to 'status' column
Shift+F
status count
complete 6
failed 2
Pending 1 ← normalize needed
PENDING 1 ← normalize needed
Phase 2 — Clean
# Select all rows
gs
# Move to 'status' column — normalize to lowercase
g=
# Enter: status.lower().strip()
# Handle missing amount (O010)
z|
# Enter: amount == '' or amount is None
ge
# Enter: 0
# Cast types
# Move to 'amount' → %
# Move to 'created_date' → @
# Save CommandLog
Shift+D → Ctrl+S
# Enter: ~/github/practice-folder/visidata/capstone/clean_orders.vdj
q
After cleaning:
status values: complete (7), failed (2), pending (2)
amount: all non-null, typed as float
created_date: typed as date
Phase 3 — Enrich with Join
# Open customers sheet
o
# Enter: ~/github/practice-folder/visidata/capstone/customers.csv
# In orders sheet: mark customer_id as key: move to customer_id → !
# In customers sheet: mark customer_id as key → !
# Open Sheets List
Shift+S
# Select both sheets with s
# Press & → inner join on customer_id
Joined sheet now includes: name, tier, email columns from customers.
Phase 4 — Analyze
Revenue by region (pivot):
# Move to 'region' → ! (key = pivot dimension)
# Move to 'amount' → + sum
Shift+W
region sum_amount%
SG 3830.00
KL 780.50
JK 515.25
SG = 78% of total revenue.
Failure rate check:
q # back to joined sheet
# Move to 'status' column
Shift+F
# Observe: 2 failed out of 10 orders (20% failure rate)
Phase 5 — Visualize
# Back to joined sheet
# Ensure created_date is @ (date type)
# Move to 'created_date' → ! (x-axis)
# Move to 'amount' → . (canvas: revenue over time)
Add regional color:
q # back to source
# Move to 'region' → ! (categorical color key)
# Keep created_date as x-axis key
# Move to 'amount' → .
Three colored series: SG consistently above KL and JK.
Phase 6 — Export and Replay
# Export cleaned joined sheet
Ctrl+S
# Enter: ~/github/practice-folder/visidata/capstone/orders-enriched.csv
# Batch replay on a new file
vd --play ~/github/practice-folder/visidata/capstone/clean_orders.vdj \
--batch ~/github/practice-folder/visidata/capstone/orders.csv \
-o /tmp/orders-replay.csv
Skills Matrix
| Skill | Module | Phase Used |
|---|---|---|
| Open files, format detection | 3 | 1 |
Describe Sheet Shift+I | 8 | 1 |
Frequency table Shift+F | 8 | 1, 4 |
Row filter z| | 5 | 2 |
Bulk transform g= | 6 | 2 |
Type casting % @ | 6 | 2 |
| CommandLog save | 7 | 2, 6 |
Join sheets & | 6 | 3 |
Pivot table Shift+W | 8 | 4 |
Canvas plot . | 9 | 5 |
Categorical color key ! | 9 | 5 |
Batch replay --play | 7 | 6 |