Aggregation and Statistics

Aggregation & Statistics

Tier: Intermediate Commands covered: stats, moarstats, frequency, pragmastat, dedup, extdedup, extsort

Note

Per-command flag reference lives in /docs/help/. This page is the workflow layer — when to reach for each command and how they compose.

These commands turn raw rows into numbers. The headline feature is stats — 48 metrics in under a second on 2.7M rows. The runner-up is frequency — multithreaded with an index, Apache DataSketches-backed frequent_items mode for huge cardinalities.

Quick decision table

If you want to…	Use	Notes
Compute mean/median/stddev/etc. with guaranteed type inference	stats	48 metrics; sub-second on millions of rows
Add bivariate, robust, and outlier stats to an existing stats CSV	moarstats	56 additional measures (Unreleased (after 23.0.1): 73)
See the top-N values per column (and how often each occurs)	frequency	Apache DataSketches mode for huge cardinalities
Robust median-of-pairwise stats (Hodges-Lehmann, Shamos)	pragmastat	Date/datetime aware via stats cache
Drop duplicate rows	dedup	Streaming with --sorted, in-memory otherwise
Drop duplicates from a file > RAM	extdedup	On-disk hash table; preserves input order
Sort a file > RAM	extsort	Multithreaded external merge sort

stats

The headline command. Computes up to 48 summary statistics with guaranteed data type inference (Null / String / Float / Integer / Date / DateTime / Boolean). Streaming by default; loads into memory for non-streaming stats like median / quartiles / cardinality. Multithreaded with an index.

A successful run writes a <filename>.stats.csv cache file that many other commands reuse (frequency, schema, pragmastat, pivotp, sqlp scoresql, etc.) — see Stats Cache & Caching.

Example: full profile of NYC 311 with date and boolean inference

qsv stats --everything --infer-dates --infer-boolean \
  NYC_311_SR_2010-2020-sample-1M.csv > nyc311-stats.csv

--everything adds cardinality, modes/antimodes, median, MAD, quartiles, IQR, fences, skewness, percentiles, and zero_padded_numeric (flags String columns that are entirely zero-padded numbers — zip codes, barcodes, padded IDs — so they aren't mistakenly loaded as integer/float downstream). --infer-dates recognizes 19 date formats; --infer-boolean detects 2-valued patterns like t/f, yes/no, 1/0.

The zero_padded_numeric column can also be enabled on its own with --zero-padded-numeric:

qsv stats --zero-padded-numeric data.csv

Example: pre-populate the stats cache to speed up downstream commands

# Run this once; every smart command (frequency, schema, pragmastat, pivotp, ...)
# will pick up the cache automatically.
qsv stats --cardinality --stats-jsonl NYC_311_SR_2010-2020-sample-1M.csv
ls NYC_311_SR_2010-2020-sample-1M.{stats.csv,stats.csv.data.jsonl}

Example: get the stats as JSON on stdout

stats writes CSV by default. --jsonl emits the same per-column stats as JSON Lines (one object per column) on stdout, and --pretty-json emits them as a single pretty-printed JSON array — handy for piping into jq or an LLM without touching the sidecar files.

# NDJSON on stdout — one object per column
qsv stats --everything --jsonl data.csv | jq 'select(.type == "Integer") | .field'

# a single pretty-printed JSON array
qsv stats --pretty-json data.csv -o stats.json

This is the same per-column shape the --stats-jsonl sidecar uses; the difference is where it goes. --jsonl and --pretty-json are mutually exclusive, and neither can be combined with --stats-jsonl.

Example: custom percentiles for a population study

qsv stats --quartiles --percentiles --percentile-list '5,10,25,50,75,90,95' \
  wcp.csv > wcp-percentiles.csv

The deciles and quintiles keywords expand to 10,20,30,...,90 and 20,40,60,80.

Example: weighted stats (e.g., revenue-weighted price)

qsv stats --weight 'Quantity' --quartiles \
  transactions.csv > price-weighted-by-volume.stats.csv

Example: approximate quantiles & cardinality on a 100M-row file

qsv stats --everything \
  --quantile-method approx \
  --cardinality-method approx \
  huge.csv

approx uses Apache DataSketches (t-digest for quantiles, HyperLogLog for cardinality) — constant memory per column, ~1 % rank error. Use this when exact memory blows up.

Important

New in 23.0.1 — your approx numbers move. The datasketches dependency went 0.4.0 → 0.5.0, carrying upstream fixes to T-Digest interpolation/tail calculations and to HLL union estimate stability. This is a visible output change: re-running stats with either approx flag will not reproduce your 22.0.1 numbers. They are markedly more accurate — but the two flags move differently.

The figures below were measured for the 23.0.1 release by building both dependency versions and diffing, on resources/test/311011.csv with the exact (non-approx) values as ground truth — see the CHANGELOG entry for the full measurement. Note that qsv's own approx tests assert tolerance envelopes rather than exact values, so a drift of this kind is invisible to them.

Flag	Which runs change	Effect on accuracy
--quantile-method approx	All of them — --jobs 1, parallel-unindexed and parallel-indexed	Closer on all seven of q1, median, q3, IQR, both inner fences and skewness. Absolute errors fall from 100–1456 down to ≤ 1.0; on a uniform ascending column the median is now exact (19814, was 19316.5) with skewness 0 (was 0.0638)
--cardinality-method approx	Only the parallel indexed path. Byte-identical under --jobs 1 and parallel-unindexed	That path is the only one exercising the HLL union when merging chunks; the Referrals column goes 909 → 923 against an exact 932 — 2.5 % error down to 0.97 %

So if you use --cardinality-method approx on an unindexed file, nothing changes for you.

The exact methods — the defaults — are unaffected.

Two different validators apply here, and only one of them saves you automatically. stats itself compares the qsv_version recorded in the .stats.csv.json signature sidecar, so it will recompute its own .stats.csv rather than serve you 22.0.1 approximations. But downstream consumers read <csv>.stats.csv.data.jsonl, and that file is judged by mtime and parsing options only — no version check — so a 22.0.1 .data.jsonl keeps being served to schema, frequency, tojsonl and viz smart until the source CSV's mtime changes. Regenerate it explicitly: qsv stats --force --stats-jsonl <file>.

Important

New in 20.1.0 — automatic on OOM. When util::mem_file_check reports that the file is likely bigger than available RAM, stats now auto-enables --quantile-method approx and --cardinality-method approx and emits a wwarn! listing what got switched on. Pass --quantile-method exact / --cardinality-method exact to force the precise calculation regardless. The stats cache key includes the chosen mode, so switching between exact and approximate runs won't return stale results. Requires a little-endian target (Intel, AMD, Apple Silicon, ARM — everything except IBM s390x, which gets a clear error).

Correctness sweep in 23.0.1

Warning

New in 23.0.1. A four-batch review of stats.rs (#4438, #4439, #4443, #4450) fixed panics, undefined behavior, a tempfile leak, stats-cache validity, and a cluster of outright wrong results. Several of these produced silently wrong numbers with exit code 0. If you have stats caches — or anything derived from them — built by 22.0.1 or earlier, regenerate them:

# --stats-jsonl is required. Bare --force rewrites only the human-readable
# .stats.csv and its signature sidecar, leaving the stale .stats.csv.data.jsonl
# that every downstream consumer actually reads.
qsv stats --force --stats-jsonl myfile.csv

Regenerating is not optional politeness. 23.0.1 carries load-time mitigations that stop an old cache from hard-failing and strip its fabricated date zeros, but nothing can reconstruct numbers that were wrong for the other reasons below — an excluded epoch date, a prefix-cached parallel run, an inflated record_count denominator. Those only go away on recompute.

Wrong results that are now right:

Fixed	What was wrong	What you saw
The epoch date is no longer excluded from date min/max (#4444)	Timestamp 0 is 1970-01-01T00:00:00Z — a real date, and a common placeholder in real data — but the date min/max accumulator skipped it behind an int_val != 0 guard	A column holding the epoch reported the wrong min, a truncated range, and computed sort_order/sortiness over the wrong sample set. On a 5-row file starting at 1970-01-01, min read 2020-01-15 with range 19205 — while the same run's percentiles cell showed 5: 1970-01-01. Now min = 1970-01-01, range 19357
Parallel stats no longer caches a PREFIX of the file's statistics when a worker dies (#4442)	parallel_stats merged chunk results by draining the channel until the last sender dropped, never checking that every chunk actually arrived. A panicking worker never sends its chunk, so the loop ended early — returning a prefix of the statistics, or an empty vector when chunk 0 was the one that died	The partial answer was written, printed and cached, with exit code 0. Every later run then reused statistics computed from a fraction of the file
A CSV with an empty column name no longer breaks stats output (#4410, #4412)	The CSV→JSON conversion omitted any key whose cell was empty, so a column with an empty name lost its field key entirely	--jsonl and --pretty-json silently emitted records that no downstream consumer could attribute to a column. The cache-side half of this bug is covered in Stats Cache & Caching
Date renderings are no longer coerced to 0.0 (#4441)	On a Date/DateTime column the median cell is a type-dependent RFC3339 rendering, but it was typed as a float for the JSON conversion	Date medians were silently corrupted to 0.0
A negative --cache-threshold cleanup targeted the wrong file (#4443)	A negative --cache-threshold sets the autoindex size, and one ending in 5 also asks for the autoindex and stats cache to be deleted afterwards. The cleanup built its path with with_extension("csv.idx"), which replaces the input's extension, while util::idx_path — which wrote the file — appends .idx	On non-CSV inputs (data.tsv → written to data.tsv.idx, looked for at data.csv.idx) and on extensionless inputs, the index you explicitly asked to delete survived — silently, since the removal only logs a warning on failure. .csv inputs worked purely by coincidence

Faster on unindexed files — new in 23.0.1

The row-count pre-pass is gone on the unindexed path (#4473). run() used to call util::count_rows() before the compute pass, reading the whole file twice, and used the result only as a capacity hint for the per-column accumulators. The hint is now skipped entirely when the requested stats don't consume it (the plain-stats default), and otherwise estimated from the file size and the average on-disk size of the first sampled records.

How much this buys you depends on your build: with polars the old pre-pass was a cheap mem-mapped scan, but without polars it was a genuine second CSV parse — roughly 30 % of a plain qsvlite run. Nothing to change on your side; indexed runs were never affected (an index gives the row count instantly).

Note

Related correctness point: record_count now comes from the compute pass, not a separate pre-pass (#4458) — and this is why the pre-pass had to go rather than just be cached. util::count_rows() counts blank lines while the csv reader feeding compute() skips them, so seeding the record count from the pre-pass inflated every per-record denominator in the output: sparsity, uniqueness_ratio, and the cardinality == record_count test behind the <ALL_UNIQUE> antimode sentinel. The trigger is a file ending in "...\n\n" — ordinary real-world data — and the same file produced different statistics depending only on whether an index happened to exist. Taking the count from the pass itself makes the denominator self-consistent by construction. This is the same root cause as count disagreeing with itself on blank lines — the two fixes shipped together.

See also: /docs/help/stats.md, docs/STATS_DEFINITIONS.md — definitions of every metric, moarstats, Stats Cache & Caching.

moarstats

Adds up to 56 more measures — univariate (Pearson's second skewness, IQR-to-range ratio, Z-scores of min/max/mode, quartile coefficient of dispersion, …), outlier, and --bivariate pair statistics — plus W3C XSD datatype mapping to an existing .stats.csv. Multithreaded with an index.

moarstats looks for <FILESTEM>.stats.csv and runs stats first if it doesn't exist.

Example: extend allegheny property sales stats

# moarstats updates the stats CSV in place — use -o to write a separate file instead
qsv moarstats allegheny_property_sales.csv -o allegheny.moarstats.csv

Example: build the cache first, then add moar stats

qsv stats --everything --stats-jsonl allegheny_property_sales.csv
qsv moarstats allegheny_property_sales.csv

Feeds viz smart: running moarstats before viz smart lets the auto-dashboard use the extended stats to pick better charts — bimodal columns (high bimodality coefficient, needs --advanced) render as histograms instead of box plots, and box panels are annotated with skew direction and outlier share. (Unreleased (after 23.0.1): plain viz smart computes the bimodality coefficient itself, identically to moarstats --advanced, so the histogram switch no longer needs moarstats — see Visualization.)

Note

New in 23.0.1 — four moarstats fixes.

• --pct-thresholds is now forwarded into the stats percentile list (#4471). When --use-percentiles is set, --pct-thresholds '10,90' picks the winsorization/trimming pair — but a requested threshold that wasn't already in the percentile list was silently not computed. It is now merged in automatically.
• Special-format inputs are read through Config rather than raw paths (#4460, #4464, #4465) — so a .gz/.zip/.parquet/.jsonl input resolves the same way it does for stats.
• The resolved input's delimiter is honored, rather than the original path's.
• The primary header stays primary.

Note

Unreleased (after 23.0.1): moarstats grows from 56 to 73 measures, and three --advanced values change.

• From the stats cache, no extra pass: mad_normalized (MAD/0.6745) and iqr_normalized (IQR/1.349) — robust, normal-consistent estimates of sigma; robust_min_zscore/robust_max_zscore (Iglewicz-Hoaglin modified z-scores of the extremes); kelly_skewness and percentile_ratio_90_10 (from P10/P90); zero_share, which flags zero-inflation; and berger_parker_dominance, the mode's share of rows (any field type).
• --advanced: moment_skewness (G1); the L-moment ratios l_cv, l_skewness and l_kurtosis — bounded, outlier-resistant analogues of CV, skewness and kurtosis; lag1_autocorrelation in file order, which exposes trends, drift and clustered blocks; hoover_index; and benford_mad, Nigrini's first-digit conformity test as a data-quality / fabrication signal (only emitted for numeric data with ≥ 100 non-zero values spanning at least two orders of magnitude).
• --bivariate: cramersv (Cramér's V, for any pair of field types) and regression (regression_slope, regression_intercept, r_squared of field2 on field1). Both are in --bivariate-stats all.
• Values change: jarque_bera and bimodality_coefficient were fed the quartile-based (Bowley) skewness instead of moment skewness, and kurtosis used the cache's rounded, sample-variance inputs. All three now match scipy exactly (#4651).
• Upgrades recompute automatically: a .stats.csv whose sidecar was written by another qsv version is treated as stale, so the appended columns are recomputed rather than kept from the old release (#4655).
• Repeated header names each get their own statistics instead of sharing the first one's (#4663).

Example: bivariate pair statistics (Unreleased (after 23.0.1): cramersv and regression are new)

qsv moarstats allegheny_property_sales.csv --bivariate -S pearson,spearman,cramersv,regression
# writes allegheny_property_sales.stats.bivariate.csv:
# field1,field2,pearson_correlation,spearman_correlation,cramers_v,regression_slope,regression_intercept,r_squared,n_pairs

Unreleased (after 23.0.1): three --bivariate behavior changes:

• All-unique numeric pairs are kept (#4654). A column whose every value is distinct — typical for prices, coordinates and durations at full precision — used to be dropped from the sidecar entirely. Now its correlation, covariance and regression are reported, with only the frequency statistics (mutual information, Theil's U, Cramér's V) left empty.
• String columns are no longer correlated (#4656). Pearson/Spearman/Kendall/covariance/regression use only Integer, Float, Date, DateTime and Boolean columns; a String column still takes part in the frequency statistics.
• The sidecar is always rewritten — header-only when no pair qualifies — so a stale one from an earlier run can't pass for this run's result.

See also: /docs/help/moarstats.md, stats, pragmastat — the robust-stats counterpart, Visualization — charting with viz.

frequency

Frequency distribution tables per column. Output as CSV (default), JSON, or TOON (compact LLM-friendly format). Multithreaded with an index. The stats cache gives a big speedup on ID-like (all-unique) columns.

For columns with millions of unique values, use --sketch-method frequent_items (Apache DataSketches Misra-Gries) — bounded memory, top-K with bounded error.

Example: top-10 NYC 311 complaint types

qsv frequency --select 'Complaint Type' --limit 10 NYC_311_SR_2010-2020-sample-1M.csv

field,value,count,percentage,rank
Complaint Type,Noise - Residential,71872,7.19,1
Complaint Type,HEATING,67234,6.72,2
Complaint Type,GENERAL CONSTRUCTION,52111,5.21,3
...
Complaint Type,Other,538295,53.83,0

Example: build a fresh-cached frequency table for downstream consumers

qsv index NYC_311_SR_2010-2020-sample-1M.csv
qsv stats --cardinality --stats-jsonl NYC_311_SR_2010-2020-sample-1M.csv
qsv frequency --frequency-jsonl NYC_311_SR_2010-2020-sample-1M.csv
ls NYC_311_SR_2010-2020-sample-1M.freq.csv.data.jsonl

Example: top-100 cities by frequency, JSON output for an LLM prompt

qsv frequency --select AccentCity --limit 100 --asc wcp.csv > top_cities.csv
qsv frequency --select AccentCity --limit 100 --asc --json wcp.csv > top_cities.json
qsv frequency --select AccentCity --limit 100 --asc --toon wcp.csv > top_cities.toon

Example: heavy-hitter detection on a huge cardinality column

qsv frequency --select 'Session ID' \
  --sketch-method frequent_items \
  --sketch-map-size 16384 \
  --limit 50 events.csv > hot_sessions.csv

Important

New in 20.1.0 — automatic on OOM. frequency now auto-enables --sketch-method frequent_items (Misra-Gries) when the file is likely bigger than RAM and emits a wwarn!. Pass --sketch-method exact to force exact frequencies regardless. Note that frequent_items tracks heavy hitters only — --asc, --ignore-case, --no-trim, --frequency-jsonl, --json, and weighted frequencies are incompatible and require exact. Requires a little-endian target.

Example: filter columns dynamically with a Luau expression on stats

# Skip floats and columns with > 1,000 nulls
qsv frequency --stats-filter "type == 'Float' or nullcount > 1000" data.csv

--stats-filter requires the luau feature and the stats cache.

See also: /docs/help/frequency.md, stats (pre-populate the cache for speed), Stats Cache & Caching.

pragmastat

Robust statistics from the Pragmastat library — designed for heavy-tailed, outlier-prone data where mean and stddev mislead. Computes Hodges-Lehmann center (median of pairwise averages) and Shamos spread (median of pairwise absolute differences), each with confidence bounds. Both estimators tolerate up to 29 % corruption.

By default, pragmastat appends 7 ps_* columns to the existing stats cache.

Note

Unreleased (after 23.0.1): ps_* columns now land on the right row (#4665). They were matched to stats rows by column name, so repeated header names all got the last one's values, and with --no-headers each row got the previous column's. Rows are now matched to columns exactly. A reused cache that can't be matched unambiguously leaves the ps_* cells empty with a warning; delete the cache (--force recomputes only the ps_* columns, never the baseline) to regenerate it.

Example: robust spread for Allegheny property sales (highly skewed)

qsv stats -E --infer-dates --stats-jsonl allegheny_property_sales.csv
qsv pragmastat --select 'Sale Price' allegheny_property_sales.csv

The --standalone flag emits standalone CSV instead of appending to the cache.

Example: two-sample comparison — does Borough A differ from Borough B?

qsv pragmastat --twosample --select 'priceA,priceB' market_split.csv

Example: confirmatory test — is median latency below 100 ms?

qsv pragmastat --compare1 'center:100' --select latency_ms requests.csv

See also: /docs/help/pragmastat.md, Pragmastat library, stats, moarstats.

dedup

Drop duplicate rows. By default it sorts in memory then deduplicates — use --sorted to stream when input is already sorted (constant memory). With -D <file> it writes the duplicates to a separate file for audit.

Example: deduplicate Boston 311 by composite key

qsv dedup --select 'Created Date,Incident Address' boston311-100.csv > unique.csv

Example: sort then stream-dedup a contact list

qsv sort --select Email contacts.csv \
  | qsv dedup --select Email --sorted -D dupes.csv > unique.csv

Example: case-insensitive dedup of email addresses

qsv dedup --select Email --ignore-case contacts.csv > unique.csv

For files larger than RAM use extdedup.

See also: /docs/help/dedup.md, sort, sortcheck, extdedup.

extdedup

Memory-mapped, on-disk hash table dedup for arbitrarily large files. Unlike dedup, it preserves input order. Two modes: CSV (with --select) and line-by-line (any text file).

Example: dedup a 50 GB clickstream by session_id without loading it into memory

qsv extdedup --select session_id huge_clickstream.csv > unique.csv

Example: line-mode dedup on a log file

qsv extdedup --no-headers nginx-access.log > unique-lines.log

See also: /docs/help/extdedup.md, extsort, Recipe: Larger-than-RAM CSV.

extsort

Multithreaded external merge sort for files larger than RAM. Two modes: CSV (with --select, requires an index) and line-mode (any text file).

Example: sort a 16 GB NYC 311 export by Created Date for time-series joins

qsv index nyc311-full.csv
qsv extsort --select 'Created Date' nyc311-full.csv > nyc311-by-date.csv

Example: sort a 100 GB log file alphabetically (line mode)

qsv extsort --no-headers huge-log.txt > sorted-log.txt

After extsort, follow up with dedup --sorted for streaming dedup, or joinp asof for time-series joins.

See also: /docs/help/extsort.md, sort, dedup --sorted, Recipe: Larger-than-RAM CSV.

Whitespace markers

Both stats and frequency accept --vis-whitespace, which replaces whitespace characters in the output (e.g. min/max/mode/antimode and frequency values) with visible markers, so leading, trailing, and embedded whitespace is easy to spot. Spaces are only marked when a value is entirely spaces — a value of three spaces becomes 《_》《_》《_》, while embedded single spaces in mixed text are left as-is.

Character	Unicode	Marker
space (only when the whole value is spaces)	U+0020	《_》
tab	U+0009	《→》
newline	U+000A	《¶》
carriage return	U+000D	《⏎》
vertical tab	U+000B	《⋮》
form feed	U+000C	《␌》
next line	U+0085	《␤》
left-to-right mark	U+200E	《␎》
right-to-left mark	U+200F	《␏》
line separator	U+2028	《␊》
paragraph separator	U+2029	《␍》
non-breaking space	U+00A0	《⍽》
em space	U+2003	《emsp》
figure space	U+2007	《figsp》
zero-width space	U+200B	《zwsp》

The first three follow Rust's whitespace reference; the rest cover other common invisible characters. The canonical mapping is WHITESPACE_MARKERS in src/util.rs.

See also

• Command Reference (index)
• Transform & Reshape — the preceding step in a typical pipeline
• Joins & Set Ops — combine after aggregating
• SQL & Polars — for GROUP BY + window functions
• docs/STATS_DEFINITIONS.md
• Stats Cache & Caching
• Performance Tuning — when to index, when to use approx methods
• Cookbook → Stats → Insights
• Cookbook → Larger-than-RAM CSV