Column Types and Casting
Every column in VisiData has a type that determines how values are displayed, sorted, and aggregated. A column loaded from CSV starts as str (string). Cast it to the correct type to unlock numeric sorting, date arithmetic, and aggregation.
Type casting is the single most impactful habit in VisiData. Always cast numeric columns (% or #) and date columns (@) before sorting, aggregating, or writing Python expressions. Forgetting this is the source of most beginner errors.
Sample Data Used in This Lesson
cat > ~/github/practice-folder/visidata/01-loading/01-employees.csv << 'EOF'
name,department,salary,hire_date,years_exp,email
Alice,Engineering,85000,2020-03-15,5,alice@company.com
Bob,Marketing,70000,2019-11-01,7,bob@company.com
Charlie,Engineering,90000,2021-06-20,3,charlie@company.com
Dina,HR,60000,2018-04-10,9,dina@company.com
Eve,Marketing,62000,2022-01-05,2,eve@company.com
EOF
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
Opening this file — all columns start as str (no type indicator in header):
name~ department~ salary~ hire_date~ years_exp~ email~
Alice Engineering 85000 2020-03-15 5 alice@company.com
Bob Marketing 70000 2019-11-01 7 bob@company.com
Charlie Engineering 90000 2021-06-20 3 charlie@company.com
Dina HR 60000 2018-04-10 9 dina@company.com
Eve Marketing 62000 2022-01-05 2 eve@company.com
Type Reference
| Key | Type | Header indicator | Display Example | Use For |
|---|---|---|---|---|
~ | str | ~ | alice | Names, labels, free text |
# | int | # | 42 | Counts, IDs, whole numbers |
% | float | % | 3.14 | Prices, metrics, decimals |
$ | currency | $ | $3.14 | Financial values with symbol |
@ | date | @ | 2025-01-15 | Timestamps, hire dates |
z# | len | z# | 7 | String length as integer |
z% | floatsi | z% | 72.5K | Large counts, bytes (SI prefix display) |
Example 1 — Cast salary from String to Float
Problem: Sorting salary while it's a string gives wrong order.
Before casting — string sort (wrong):
name salary~
Bob 70000 ← "7" comes after "9" alphabetically? No — "6" < "7" < "8" < "9"
Eve 62000
Dina 60000
Alice 85000
Charlie 90000
Press [ to sort ascending → result: 60000, 62000, 70000, 85000, 90000 (correct by luck here, but with values like 9, 10, 100 → string sort gives 10, 100, 9)
Fix — cast to float:
# Move to 'salary' column
% # cast to float
After casting — header shows % indicator:
name salary%
Alice 85000.0
Bob 70000.0
Charlie 90000.0
Dina 60000.0
Eve 62000.0
Now [ and ] sort numerically. + mean works. + sum works.
Example 2 — Cast hire_date from String to Date
Problem: Chronological sort of dates stored as strings fails for cross-year dates.
# Move to 'hire_date' column
@ # cast to date (auto-detects ISO 8601 format)
After casting — header shows @ indicator:
name hire_date@
Alice 2020-03-15
Bob 2019-11-01
Charlie 2021-06-20
Dina 2018-04-10
Eve 2022-01-05
Press [ → chronological order: 2018-04-10, 2019-11-01, 2020-03-15, 2021-06-20, 2022-01-05
Date arithmetic in derived columns:
# After casting hire_date to @, you can compute:
=
# Enter: (date.today() - hire_date).days
# Name: days_employed
Example 3 — Cast years_exp to Integer
# Move to 'years_exp' column
# # cast to int
Enables:
- Numeric sort:
2, 3, 5, 7, 9 - Aggregation:
+ mean→5.2,+ max→9 - Python expressions:
= salary / years_exp→ salary per year of experience
Example 4 — floatsi for Large Numbers (z%)
Use z% when values span orders of magnitude (bytes, network traffic, log counts):
# A column with: 1200, 45600, 3200000, 870000000
# Move to the column
z% # display as: 1.2K, 45.6K, 3.2M, 870.0M
Makes large metric columns readable at a glance.
What Changes When You Cast
| Operation | salary as str ~ | salary as float % |
|---|---|---|
Sort ascending [ | Lexicographic (wrong for mixed digits) | Numeric (correct) |
+ mean aggregator | #ERR | 76,400.0 |
+ sum aggregator | #ERR | 367,000.0 |
Python expression = salary * 1.1 | #ERR | 93,500.0 |
| Display in Describe Sheet | No min/max/mean | Full statistical profile |
Batch-Cast Multiple Columns (Columns Sheet)
For many columns at once, use the Columns Sheet:
Shift+C # open Columns Sheet
# Select salary, years_exp with s
# Press g% → cast all selected to float
# Press g# → cast all selected to int
# Press g@ → cast all selected to date
Persist Types in .visidatarc
If you open the same file repeatedly, avoid re-casting every time:
# ~/.visidatarc
# This is not directly supported for specific files,
# but you can create a CommandLog (.vdj) that replays casts:
# vd --play cast_types.vdj input.csv
Better: save a CommandLog with your cast operations and replay it:
# After casting, save CommandLog
Shift+D → Ctrl+S → cast_types.vdj
# Next time:
vd --play cast_types.vdj ~/github/practice-folder/visidata/01-loading/01-employees.csv
Troubleshooting
| Problem | Cause | Fix |
|---|---|---|
#ERR when casting to # or % | Non-numeric values in column (e.g., N/A, —) | Clean data first; or filter out #ERR rows with z| value is None |
| Date cast shows wrong values | Format not ISO 8601 | Set options.disp_date_fmt = '%d/%m/%Y' in .visidatarc |
z% shows 0.0 | Values are 0 or None | Check raw data with ~ first |
| Aggregation returns 0 or wrong result | Column still typed as str | Cast to % or # first |
| Sort still wrong after cast | Derived column not cast | Cast the derived column too |
Hands-On Practice
vd ~/github/practice-folder/visidata/01-loading/01-employees.csv
# 1. Press ] on 'salary' column (string sort) → observe lexicographic sort
# 2. Press % → cast salary to float → press ] again → numeric sort
# 3. Move to 'hire_date' → press @ → cast to date → press [ → chronological sort
# 4. Move to 'years_exp' → press # → cast to int
# 5. Press = → Enter: salary / years_exp → Name: salary_per_year
# 6. Move to 'salary_per_year' → press % → cast new column to float
# 7. Press ] → sort by salary_per_year descending → see most efficient earners
# 8. Press Shift+I → see stats (min, max, mean) now populated for numeric columns
What's Next
- Column Layout — width, hide/show, reorder, slide columns
- Column Naming — renaming, key column mechanics