# stats

> Compute up to 48 [summary statistics](../STATS_DEFINITIONS.md) & make GUARANTEED data type inferences (Null, String, Float, Integer, Date, DateTime, Boolean) for each column in a CSV ([Example](https://github.com/dathere/qsv/blob/master/scripts/NYC_311_SR_2010-2020-sample-1M.stats.csv)). Uses multithreading to go faster if an index is present. With an index, can compile "streaming" stats on a 1M row sample of NYC's 311 data in [less than 0.25 seconds vs 2.24 seconds without one](https://qsv.dathere.com/benchmarks).

**[Table of Contents](TableOfContents.md)** | **Source: [src/cmd/stats.rs](https://github.com/dathere/qsv/blob/master/src/cmd/stats.rs)** | [📇](TableOfContents.md#legend "uses an index when available.")[🤯](TableOfContents.md#legend "loads entire CSV into memory, though `dedup`, `stats` & `transpose` have \"streaming\" modes as well.")[🏎️](TableOfContents.md#legend "multithreaded and/or faster when an index (📇) is available.")[👆](TableOfContents.md#legend "has powerful column selector support. See `select` for syntax.")[🪄](TableOfContents.md#legend "\"automagical\" commands that uses stats and/or frequency tables to work \"smarter\" & \"faster\".")

<a name="nav"></a>
[Description](#description) | [Examples](#examples) | [Usage](#usage) | [Stats Options](#stats-options) | [Boolean Inferencing Options](#boolean-inferencing-options) | [Numeric & Date/Datetime Stats That Require In-Memory Sorting Options](#numeric-&-date/datetime-stats-that-require-in-memory-sorting-options) | [Date Inferencing Options](#date-inferencing-options) | [Common Options](#common-options)

<a name="description"></a>

## Description [↩](#nav)

Compute summary statistics & infers data types for each column in a CSV.

> [!IMPORTANT]
> `stats` is heavily optimized for speed. It ASSUMES the CSV is well-formed & UTF-8 encoded.
> This allows it to employ numerous performance optimizations (skip repetitive UTF-8 validation, skip
> bounds checks, cache results, etc.) that may result in undefined behavior if the CSV is not well-formed.
> All these optimizations are GUARANTEED to work with well-formed CSVs.
> If you encounter problems generating stats, use `qsv validate` FIRST to confirm the CSV is valid.

> [!NOTE]
> For MAXIMUM PERFORMANCE, create an index for the CSV first with 'qsv index' to enable multithreading,
> or set --cache-threshold option or set the QSV_AUTOINDEX_SIZE environment variable to automatically
> create an index when the file size is greater than the specified size (in bytes).

Summary stats include sum, min/max/range, sort order/sortiness, min/max/sum/avg/stddev/variance/cv length,
mean, standard error of the mean (SEM), geometric mean, harmonic mean, stddev, variance, coefficient of
variation (CV), nullcount, n_negative, n_zero, n_positive, max_precision, sparsity,
Median Absolute Deviation (MAD), quartiles, lower/upper inner/outer fences, skewness, median,
cardinality/uniqueness ratio, mode/s & "antimode/s" & percentiles.

Note that some stats require loading the entire file into memory, so they must be enabled explicitly.

By default, the following "streaming" statistics are reported for *every* column:  
sum, min/max/range values, sort order/"sortiness", min/max/sum/avg/stddev/variance/cv length, mean, sem,
geometric_mean, harmonic_mean,stddev, variance, cv, nullcount, n_negative, n_zero, n_positive,
max_precision & sparsity.

The default set of statistics corresponds to ones that can be computed efficiently on a stream of data
(i.e., constant memory) and works with arbitrarily large CSVs.

The following additional "non-streaming, advanced" statistics require loading the entire file into memory:  
cardinality/uniqueness ratio, modes/antimodes, median, MAD, quartiles and its related measures
(q1, q2, q3, IQR, lower/upper fences & skewness) and percentiles.

When computing "non-streaming" statistics, a memory-aware chunking algorithm is used to dynamically
calculate chunk size based on available memory & record sampling. This SHOULD help process arbitrarily
large "real-world" files by creating smaller chunks that fit in available memory.
However, there is still a chance that the command will run out of memory if the cardinality of
several columns is very high.

Chunk size is dynamically calculated based on the number of logical CPUs detected.
You can override this behavior by setting the QSV_STATS_CHUNK_MEMORY_MB environment variable
(set to 0 for dynamic sizing, or a positive number for a fixed memory limit per chunk,
or -1 for CPU-based chunking (1 chunk = records/number of CPUs)).

"Antimode" is the least frequently occurring non-zero value and is the opposite of mode.
It returns "*ALL" if all the values are unique, and only returns a preview of the first
10 antimodes, truncating after 100 characters (configurable with QSV_ANTIMODES_LEN).

If you need all the antimode values of a column, run the `frequency` command with --limit set
to zero. The resulting frequency table will have all the "antimode" values.

Summary statistics for dates are also computed when --infer-dates is enabled, with DateTime
results in rfc3339 format and Date results in "yyyy-mm-dd" format in the UTC timezone.
Date range, stddev, variance, MAD & IQR are returned in days, not timestamp milliseconds.

Each column's data type is also inferred (NULL, Integer, String, Float, Date, DateTime and
Boolean with --infer-boolean option).
For String data types, it also determines if the column is all ASCII characters.
Unlike the sniff command, stats' data type inferences are GUARANTEED, as the entire file
is scanned, and not just sampled.

Note that the Date and DateTime data types are only inferred with the --infer-dates option
as its an expensive operation to match a date candidate against 19 possible date formats,
with each format, having several variants.

The date formats recognized and its sub-variants along with examples can be found at
<https://github.com/dathere/qsv-dateparser?tab=readme-ov-file#accepted-date-formats>.

Computing statistics on a large file can be made MUCH faster if you create an index for it
first with 'qsv index' to enable multithreading. With an index, the file is split into chunks
and each chunk is processed in parallel.

As stats is a central command in qsv, and can be expensive to compute, `stats` caches results
in <FILESTEM>.stats.csv & if the --stats-jsonl option is used, <FILESTEM>.stats.csv.data.jsonl
(e.g., qsv stats nyc311.csv will create nyc311.stats.csv; adding --stats-jsonl also creates
nyc311.stats.csv.data.jsonl).
The arguments used to generate the cached stats are saved in <FILESTEM>.stats.csv.json.

If stats have already been computed for the input file with similar arguments and the file
hasn't changed, the stats will be loaded from the cache instead of recomputing it.

These cached stats are also used by other qsv commands (currently `describegpt`, `frequency`,
`joinp`, `pivotp`, `schema`, `sqlp` & `tojsonl`) to work smarter & faster.
If the cached stats are not current (i.e., the input file is newer than the cached stats),
the cached stats will be ignored and recomputed.


<a name="examples"></a>

## Examples [↩](#nav)

> Compute "streaming" statistics for "nyc311.csv"

```console
qsv stats nyc311.csv
```

> Compute all statistics for "nyc311.csv"

```console
qsv stats --everything nyc311.csv
```

> Compute all statistics for "nyc311.tsv" (Tab-separated)

```console
qsv stats -E nyc311.tsv
```

> Compute all stats for "nyc311.tsv", inferring dates using sniff to auto-detect date columns

```console
qsv stats -E --infer-dates nyc311.tsv
```

> Compute all stats for "nyc311.tab", inferring dates only for columns
> with "_date" & "_dte" in the column names

```console
qsv stats -E --infer-dates --dates-whitelist _date,_dte nyc311.tab
```

> Compute all stats, infer dates and boolean data types for "nyc311.ssv" file

```console
qsv stats -E --infer-dates --infer-boolean nyc311.ssv
```

> In addition to basic "streaming" stats, also compute cardinality for "nyc311.csv"

```console
qsv stats --cardinality nyc311.csv
```

> Prefer DMY format when inferring dates for the "nyc311.csv"

```console
qsv stats -E --infer-dates --prefer-dmy nyc311.csv
```

> Infer data types only for the "nyc311.csv" file:

```console
qsv stats --typesonly nyc311.csv
```

> Infer data types only, including boolean and date types for "nyc311.csv"

```console
qsv stats --typesonly --infer-boolean --infer-dates nyc311.csv
```

> Automatically create an index for the "nyc311.csv" file to enable multithreading
> if it's larger than 5MB and there is no existing index file:

```console
qsv stats -E --cache-threshold -5000000 nyc311.csv
```

> Auto-create a TEMPORARY index for the "nyc311.csv" file to enable multithreading
> if it's larger than 5MB and delete the index and the stats cache file after the stats run:

```console
qsv stats -E --cache-threshold -5000005 nyc311.csv
```

For more examples, see [tests](https://github.com/dathere/qsv/tree/master/resources/test).

See also <https://github.com/dathere/qsv/wiki/Aggregation-and-Statistics#stats>
If the polars feature is enabled, support additional tabular file formats and
compression formats:  
```console
qsv stats data.parquet // Parquet
```

```console
qsv stats data.avro // Avro
```

```console
qsv stats data.jsonl // JSON Lines
```

```console
qsv stats data.json (will only work with a JSON Array)
```

```console
qsv stats data.csv.gz // Gzipped CSV
```

```console
qsv stats data.tab.zlib // Zlib-compressed Tab-separated
```

```console
qsv stats data.ssv.zst // Zstd-compressed Semicolon-separated
```

For more info, see <https://github.com/dathere/qsv/blob/master/docs/STATS_DEFINITIONS.md>

<a name="usage"></a>

## Usage [↩](#nav)

```console
qsv stats [options] [<input>]
qsv stats --help
```

<a name="stats-options"></a>

## Stats Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑s,`<br>`‑‑select`&nbsp; | string | Select a subset of columns to compute stats for. See 'qsv select --help' for the format details. This is provided here because piping 'qsv select' into 'qsv stats' will prevent the use of indexing. |  |
| &nbsp;`‑E,`<br>`‑‑everything`&nbsp; | flag | Compute all statistics available. |  |
| &nbsp;`‑‑typesonly`&nbsp; | flag | Infer data types only and do not compute statistics. Note that if you want to infer dates and boolean types, you'll still need to use the --infer-dates & --infer-boolean options. |  |

<a name="boolean-inferencing-options"></a>

## Boolean Inferencing Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑‑infer‑boolean`&nbsp; | flag | Infer boolean data type. This automatically enables the --cardinality option. When a column's cardinality is 2, and the 2 values' are in the true/false patterns specified by --boolean-patterns, the data type is inferred as boolean. |  |
| &nbsp;`‑‑boolean‑patterns`&nbsp; | string | Comma-separated list of boolean pattern pairs in the format "true_pattern:false_pattern". Each pattern can be a string of any length. The patterns are case-insensitive. If a pattern ends with a "*", it is treated as a prefix. For example, "t*:f*,y*:n*" will match "true", "truthy", "Truth" as boolean true values so long as the corresponding false pattern (e.g. False, f, etc.) is also matched & cardinality is 2. Ignored if --infer-boolean is false. | `1:0,t*:f*,y*:n*` |
| &nbsp;`‑‑mode`&nbsp; | flag | Compute the mode/s & antimode/s. Multimodal-aware. If there are multiple modes/antimodes, they are separated by the QSV_STATS_SEPARATOR environment variable. If not set, the default separator is "\|". Uses memory proportional to the cardinality of each column. |  |
| &nbsp;`‑‑cardinality`&nbsp; | flag | Compute the cardinality and the uniqueness ratio. This is automatically enabled if --infer-boolean is enabled. <https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)> Uses memory proportional to the number of unique values in each column. |  |
| &nbsp;`‑‑zero‑padded‑numeric`&nbsp; | flag | Add a "zero_padded_numeric" column that is "true" when a column's leading/padding zeros would be lost if it were cast to a number. Qualifies when the inferred type is String AND every non-null value is numeric (an all-digit integer or a zero-padded decimal code), with at least one carrying a leading zero — e.g. US zip codes, barcodes, zero-padded IDs, and zero-padded decimal codes like 007.1, 05.10 (as in ICD-9, Dewey Decimal & Harmonized System classification codes). qsv keeps all of these as text to preserve their leading zeros, so they are not mistakenly loaded/cast as integer/float. Note that ordinary fractions (0.5) and pure trailing-zero codes (7.10) are not flagged. The cell is empty when the column is not zero-padded numeric. Automatically enabled with --everything. |  |

<a name="numeric-&-date/datetime-stats-that-require-in-memory-sorting-options"></a>

## Numeric & Date/Datetime Stats That Require In-Memory Sorting Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑‑median`&nbsp; | flag | Compute the median. Loads & sorts all the selected columns' data in memory. <https://en.wikipedia.org/wiki/Median> |  |
| &nbsp;`‑‑mad`&nbsp; | flag | Compute the median absolute deviation (MAD). <https://en.wikipedia.org/wiki/Median_absolute_deviation> |  |
| &nbsp;`‑‑quartiles`&nbsp; | flag | Compute the quartiles (using method 3), the IQR, the lower/upper, inner/outer fences and skewness. <https://en.wikipedia.org/wiki/Quartile#Method_3> |  |
| &nbsp;`‑‑percentiles`&nbsp; | flag | Compute custom percentiles using the nearest rank method. <https://en.wikipedia.org/wiki/Percentile#The_nearest-rank_method> |  |
| &nbsp;`‑‑percentile‑list`&nbsp; | string | Comma-separated list of percentiles to compute. For example, "5,10,40,60,90,95" will compute percentiles 5th, 10th, 40th, 60th, 90th, and 95th. Multiple percentiles are separated by the QSV_STATS_SEPARATOR environment variable. If not set, the default separator is "\|". It is ignored if --percentiles is not set. Special values "deciles" and "quintiles" are automatically expanded to "10,20,30,40,50,60,70,80,90" and "20,40,60,80" respectively. | `5,10,40,60,90,95` |
| &nbsp;`‑‑quantile‑method`&nbsp; | string | Algorithm used to compute the median, quartiles and custom percentiles. Choices: exact  - load all values into memory and sort (current behavior). O(N) memory per numeric column, exact deterministic results. approx - use t-digest (Apache DataSketches port, based on Dunning's MergingDigest). O(K) memory per numeric column (K~200 centroids), O(1) quantile reads. Approximate (~1% rank error, more accurate at the tails). | `exact` |
| &nbsp;`‑‑cardinality‑method`&nbsp; | string | Algorithm used to compute the --cardinality column. Choices: exact  - track every unique value in a frequency map (current behavior). O(cardinality) memory per column. Subject to --mode-cardinality-cap, which emits the ">=<n>" sentinel on overflow. approx - use HyperLogLog (Apache DataSketches port, lg_k=12). O(1) memory per column (~5KB), ~1.5% relative standard error. | `exact` |
| &nbsp;`‑‑mode‑cardinality‑cap`&nbsp; | integer | Bound mode-tracking memory on high-cardinality columns. When > 0, if a column's mode tracker grows past <n> UNIQUE values (true cardinality, for both unweighted and weighted runs), qsv drops it and emits sentinel values instead of exact modes and cardinality. The cap is a direct memory bound on the tracker, which stores one entry per unique value. | `0` |
| &nbsp;`‑‑round`&nbsp; | integer | Round statistics to <decimal_places>. Rounding is done following Midpoint Nearest Even (aka "Bankers Rounding") rule. <https://docs.rs/rust_decimal/latest/rust_decimal/enum.RoundingStrategy.html> If set to the sentinel value 9999, no rounding is done. For dates - range, stddev & IQR are rounded to 1e-5 day precision (sub-second), with trailing zeros trimmed in the displayed output. | `4` |
| &nbsp;`‑‑nulls`&nbsp; | flag | Include NULLs in the population size for computing mean and standard deviation. |  |
| &nbsp;`‑‑weight`&nbsp; | string | Compute weighted statistics using the specified column as weights. The weight column must be numeric. When specified, all statistics (mean, stddev, variance, median, quartiles, mode, etc.) will be computed using weighted algorithms. The weight column is automatically excluded from statistics computation. Missing or non-numeric weights default to 1.0. Zero and negative weights are ignored and do not contribute to the statistics. The output filename will be <FILESTEM>.stats.weighted.csv to distinguish from unweighted statistics. |  |

<a name="date-inferencing-options"></a>

## Date Inferencing Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑‑infer‑dates`&nbsp; | flag | Infer date/datetime data types. This is an expensive option and should only be used when you know there are date/datetime fields. Also, if timezone is not specified in the data, it'll be set to UTC. |  |
| &nbsp;`‑‑dates‑whitelist`&nbsp; | string | The comma-separated, case-insensitive patterns to look for when shortlisting fields for date inferencing. i.e. if the field's name has any of these patterns, it is shortlisted for date inferencing. | `sniff` |
| &nbsp;`‑‑prefer‑dmy`&nbsp; | flag | Parse dates in dmy format. Otherwise, use mdy format. Ignored if --infer-dates is false. |  |
| &nbsp;`‑‑force`&nbsp; | flag | Force recomputing stats even if valid precomputed stats cache exists. |  |
| &nbsp;`‑j,`<br>`‑‑jobs`&nbsp; | integer | The number of jobs to run in parallel. This works only when the given CSV has an index. Note that a file handle is opened for each job. When not set, the number of jobs is set to the number of CPUs detected. |  |
| &nbsp;`‑‑stats‑jsonl`&nbsp; | flag | Also write the stats in JSONL format. If set, the stats will be written to <FILESTEM>.stats.csv.data.jsonl. Note that this option used internally by other qsv "smart" commands (see <https://github.com/dathere/qsv/blob/master/docs/PERFORMANCE.md#stats-cache>) to load cached stats to make them work smarter & faster. You can preemptively create the stats-jsonl file by using this option BEFORE running "smart" commands and they will automatically use it. |  |
| &nbsp;`‑‑jsonl`&nbsp; | flag | Emit the stats to stdout as JSON Lines (NDJSON) - one JSON object per column - instead of CSV. This is the same per-column shape the --stats-jsonl sidecar file uses, but streamed to stdout. Respects --output (the NDJSON is written to the file instead). Cannot be combined with --stats-jsonl or --pretty-json. |  |
| &nbsp;`‑‑pretty‑json`&nbsp; | flag | Like --jsonl, but emit a single pretty-printed JSON array of the per-column objects instead of newline-delimited objects. Cannot be combined with --stats-jsonl or --jsonl. |  |
| &nbsp;`‑c,`<br>`‑‑cache‑threshold`&nbsp; | integer | Controls the creation of stats cache files.<ul><li>when greater than 1, the threshold in milliseconds before caching stats results. If a stats run takes longer than this threshold, the stats results will be cached.</li><li>0 to suppress caching.</li><li>1 to force caching.</li><li>a negative number to automatically create an index when the input file size is greater than abs(arg) in bytes. If the negative number ends with 5, it will delete the index file and the stats cache file after the stats run. Otherwise, the index file and the cache files are kept.</li></ul> | `5000` |
| &nbsp;`‑‑vis‑whitespace`&nbsp; | flag | Visualize whitespace characters in the output. See <https://github.com/dathere/qsv/wiki/Aggregation-and-Statistics#whitespace-markers> for the list of whitespace markers. |  |

<a name="common-options"></a>

## Common Options [↩](#nav)

| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Option&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Type | Description | Default |
|--------|------|-------------|--------|
| &nbsp;`‑h,`<br>`‑‑help`&nbsp; | flag | Display this message |  |
| &nbsp;`‑o,`<br>`‑‑output`&nbsp; | string | Write output to <file> instead of stdout. |  |
| &nbsp;`‑n,`<br>`‑‑no‑headers`&nbsp; | flag | When set, the first row will NOT be interpreted as column names. i.e., They will be included in statistics. |  |
| &nbsp;`‑d,`<br>`‑‑delimiter`&nbsp; | string | The field delimiter for READING CSV data. Must be a single character. (default: ,) |  |
| &nbsp;`‑‑memcheck`&nbsp; | flag | Use CONSERVATIVE heuristics for the in-memory load check (file size vs. available + free_swap × platform factor − headroom), instead of the default NORMAL check (file size vs. total memory − headroom). The CONSERVATIVE check is stricter and trips OOM far more readily. Ignored when computing default, streaming statistics. (See also: QSV_MEMORY_CHECK env var, equivalent to passing --memcheck.) Independently of this flag, the in-memory load check runs whenever stats takes the non-parallel path with non-streaming columns. On OOM (in either NORMAL or CONSERVATIVE mode), qsv auto-creates an index when no index exists (skipped for stdin) AND switches to approx quantile + approx cardinality methods (DataSketches t-digest and HyperLogLog) where compatible. The sketch fallback can also fire when an index is already present and the OOM still trips (e.g., when jobs is pinned to 1 on a pre-indexed file). A wwarn is emitted listing the auto-enabled estimators. |  |

---
**Source:** [`src/cmd/stats.rs`](https://github.com/dathere/qsv/blob/master/src/cmd/stats.rs)
| **[Table of Contents](TableOfContents.md)** | **[README](../../README.md)**
