Recipe Data Quality moarstats - dathere/qsv GitHub Wiki

Recipe: Data-Quality Signals with moarstats

Tier: Intermediate Commands used: moarstats, frequency, replace, sort, luau Anchor dataset: examples/viz/nyc_capital_projects.csv: 12,587 NYC capital projects (a sample of NYC Open Data's Capital Projects Database) with three budget columns Requires: qsv 24.0.0+ (most measures used here are new in 24.0.0)

Problem

Before you analyze a table, you want to know whether its numbers can be trusted:

Were the amounts typed in, rounded, or made up?
Is "zero" a real value, a placeholder, or several different spellings of one?
Are the extremes real outliers, or is the standard deviation too inflated to tell?
Is the file sorted or clustered, so that head gives a biased sample?
Are two columns the same fact encoded twice?

stats gives you the mean, standard deviation and min/max, but those are the numbers that heavy tails and bad encodings distort. moarstats adds measures built for these questions. 24.0.0 added 17 of them, and this recipe uses them as a data-quality checklist.

Recipe: Stats → Insights covers the full stats → moarstats → pragmastat → describegpt pipeline. This recipe stays on the quality checks.

Data

# bundled with qsv
cp examples/viz/nyc_capital_projects.csv capital.csv

Outside the qsv repo:

curl -L -o capital.csv https://raw.githubusercontent.com/dathere/qsv/master/examples/viz/nyc_capital_projects.csv

The columns are maprojid, magencyacro, magencyname, typecategory, totalplannedcommit, commit_total and spent_total.

Solution

1. Compute everything once

qsv moarstats --advanced capital.csv
# Stats CSV file not found: .../capital.stats.csv
# Computing baseline stats...
# Added 76 additional statistics columns to .../capital.stats.csv

With no capital.stats.csv present, moarstats runs stats first and then appends its columns to that file. --advanced adds the measures that need every value: moment skewness, L-moments, lag-1 autocorrelation and Benford MAD. On this file it takes well under a second.

Each check below reads columns from capital.stats.csv.

2. Zeros: zero share vs. Berger-Parker dominance

qsv select field,nullcount,n_zero,n_negative,zero_share,berger_parker_dominance,mode capital.stats.csv \
  | qsv search -s field total | qsv table

field               nullcount  n_zero  n_negative  zero_share  berger_parker_dominance  mode
totalplannedcommit  0          3222    179         0.256       0.256                    0
commit_total        21         7495    0           0.5965      0.4594                   0
spent_total         21         5689    0           0.4527      0.452                    0

zero_share is the share of non-null numeric values that are exactly zero. 60% of projects have no commitment recorded at all, so any mean or total over commit_total is mostly zeros.
berger_parker_dominance is the share of rows held by the most frequent value. The mode is found by matching the text exactly, so 0 and 0.00 count as different values.

For commit_total the two disagree (0.5965 vs. 0.4594). If the mode is zero, they should match. frequency shows why they don't:

qsv frequency -s commit_total -l 3 capital.csv | qsv table

field         value          count  percentage  rank
commit_total  0              5783   46.02101    1
commit_total  0.00           1552   12.35079    2
commit_total  0.0            160    1.27328     3
commit_total  Other (5,005)  5071   40.35493    0
commit_total  (NULL)         21

Zero is written three ways. It makes no difference to arithmetic, but it breaks every text-based operation, including dedup, joins on the value, frequency and search. Normalize it:

qsv replace -s commit_total,spent_total '^0\.0+$' '0' capital.csv -o capital_fixed.csv
# 1712
qsv moarstats capital_fixed.csv
qsv select field,zero_share,berger_parker_dominance capital_fixed.stats.csv | qsv search -s field total | qsv table

field               zero_share  berger_parker_dominance
totalplannedcommit  0.256       0.256
commit_total        0.5965      0.5955
spent_total         0.4527      0.452

The remaining gap is the 21 nulls. zero_share divides by non-null values and Berger-Parker divides by all rows.

n_negative also shows 179 negative planned commitments. They could be legitimate de-obligations or entry errors, and either way they are worth asking the publisher about.

3. Outliers: robust spread vs. standard deviation

qsv select field,stddev,mad_normalized,iqr_normalized,max_zscore,robust_max_zscore,robust_min_zscore capital.stats.csv \
  | qsv search -s field total | qsv table

field               stddev          mad_normalized  iqr_normalized  max_zscore  robust_max_zscore  robust_min_zscore
totalplannedcommit  117585520.0608  544115.0142     2633101.5401    39.1709     8492.3424          -51.9578
commit_total        31116974.3737   0               142151.4189     61.8178
spent_total         55192274.7786   119467.857      1217649.2671    51.4887     23840.2421         -0.6745

mad_normalized (MAD/0.6745) and iqr_normalized (IQR/1.349) both estimate sigma, and for normal data they agree with stddev. Here stddev is more than 200× mad_normalized: a few huge projects set the spread, not the typical one.
Because those projects inflate stddev, ordinary z-scores understate them. The largest planned commitment has a max_zscore of 39. Its robust_max_zscore (the Iglewicz-Hoaglin modified z-score) is 8,492.
The most negative planned commitment has a min_zscore of only −0.37, so it looks ordinary. Its robust_min_zscore of −51.96 is far beyond the ±3.5 that the help text gives as the usual outlier flag.
When the median is zero the MAD is zero too, so mad_normalized is 0 and the robust z-scores are left empty, as for commit_total. Use iqr_normalized for that column instead.

4. Shape: L-moments when moments break down

qsv select field,moment_skewness,kurtosis,l_cv,l_skewness,l_kurtosis capital.stats.csv \
  | qsv search -s field total | qsv table

field               moment_skewness  kurtosis   l_cv    l_skewness  l_kurtosis
totalplannedcommit  22.6671          660.7657   0.929   0.8813      0.7679
commit_total        41.7436          2072.9999  0.9582  0.9255      0.839
spent_total         28.6198          1114.4807  0.9297  0.8848      0.7719

Moment skewness and kurtosis are unbounded and driven by the few largest values, so numbers like 2,073 are hard to compare or interpret. The L-moment ratios are bounded and outlier-resistant:

l_skewness (τ3) lies in [−1, 1]. Values near 0.9 mean the data is almost as right-skewed as possible.
l_kurtosis (τ4) is about 0.1226 for a normal distribution. All three columns are above 0.76, so their tails are extremely heavy.

5. Benford's law: were these numbers measured or made up?

qsv select field,min,max,benford_mad capital.stats.csv | qsv search -s field total | qsv table

field               min        max            benford_mad
totalplannedcommit  -27904000  4621178000     0.0052
commit_total        0.0        1925842132.58  0.005
spent_total         0.0        2848223215.5   0.0033

benford_mad is the mean absolute deviation between a column's first-digit proportions and Benford's law. The help text gives Nigrini's bands: < 0.006 close conformity, < 0.012 acceptable, < 0.015 marginal, otherwise nonconformity. All three budget columns conform closely, which is what you expect from amounts that come out of real transactions.

For contrast, here is a constructed column of 2,000 invented amounts, drawn uniformly between 100 and 99,999. Picking numbers "at random" spreads the first digits evenly, which is typical of made-up figures:

(echo id; seq 1 2000) > ids.csv
qsv luau map invented -B 'math.randomseed(42)' 'math.random(100, 99999)' ids.csv > invented.csv
qsv moarstats --advanced invented.csv
qsv select field,min,max,benford_mad,lag1_autocorrelation invented.stats.csv | qsv table

field     min  max    benford_mad  lag1_autocorrelation
id        1    2000   0.0597       0.9985
invented  111  99968  0.0608       -0.0191

invented scores 0.0608, about ten times the real columns and well into nonconformity. id fails too (0.0597), but that tells you nothing: Benford's law applies to naturally occurring magnitudes, not to assigned numbers. IDs, ZIP codes, phone numbers, sequence numbers and values with a hard floor or ceiling (such as "payments over £25,000") are expected to fail. Read a high MAD as a reason to look closer, not as proof of fabrication.

benford_mad is only emitted for numeric columns with at least 100 non-zero values spanning at least two orders of magnitude. With math.random(1000, 99999) the range is just under 100×, so the column gets no benford_mad at all. A blank value can mean the column didn't qualify.

6. File order: lag-1 autocorrelation

qsv select field,sortiness,lag1_autocorrelation capital.stats.csv | qsv search -s field total | qsv table

field               sortiness  lag1_autocorrelation
totalplannedcommit  0.1065     0.4319
commit_total        0.4284     0.0597
spent_total         0.2866     0.0473

lag1_autocorrelation correlates each value with the next one in file order. For totalplannedcommit it is 0.43, even though sortiness says the column is barely sorted (0.11). Similar values sit next to each other in blocks without the column being sorted. Shuffle the rows and the effect disappears:

qsv sort --random --seed 42 capital.csv > shuffled.csv
qsv moarstats --advanced shuffled.csv
qsv select field,lag1_autocorrelation shuffled.stats.csv | qsv search -s field total | qsv table

field               lag1_autocorrelation
totalplannedcommit  -0.0004
commit_total        0.0148
spent_total         -0.0003

In practice, the first N rows of this file are not a fair sample. Use qsv sample rather than head or slice when you take a subset to explore or test with. In the constructed example above, the sequential id column scores 0.9985, which is what a sorted column looks like.

7. Categories: dominance and redundant columns

qsv select field,cardinality,berger_parker_dominance,mode capital.stats.csv | qsv search -s field -v total | qsv table

field         cardinality  berger_parker_dominance  mode
maprojid      12587        0.0001
magencyacro   29           0.2194                   DPR
magencyname   29           0.2194                   Department of Parks and Recreation
typecategory  4            0.5738                   Fixed Asset

Berger-Parker works for every type. One agency, Parks, holds 22% of projects, and one project type holds 57% of rows. Any "by agency" chart is dominated by Parks.

Then compare columns pairwise:

qsv moarstats capital.csv --bivariate -S cramersv,regression
qsv table capital.stats.bivariate.csv

field1              field2              cramers_v  regression_slope  regression_intercept  r_squared  n_pairs
magencyacro         magencyname         1                                                             12587
magencyacro         typecategory        0.3748                                                        11528
magencyacro         totalplannedcommit  0.6387                                                        12587
magencyacro         commit_total        0.5797                                                        12566
magencyacro         spent_total                                                                       12566
...
totalplannedcommit  commit_total        0.7275     0.1411            105239.7937           0.2848     12566
totalplannedcommit  spent_total                    0.0906            5061736.5148          0.0373     12566
commit_total        spent_total                    0.3571            5637717.1255          0.0405     12566

Cramér's V = 1 between magencyacro and magencyname means each acronym maps to exactly one name. The two columns hold the same fact twice, so keep one of them.
Cramér's V on an amount column needs care. With thousands of distinct values, each cell of the contingency table is sparse, and V comes out high without meaning much. spent_total has 6,828 distinct values, which is over the default --cardinality-threshold (half the row count), so its V is skipped. To compute V only between true categories, pass -C 100:
```
qsv moarstats capital.csv --bivariate -S cramersv -C 100
qsv search -s cramers_v . capital.stats.bivariate.csv | qsv table
# magencyacro  magencyname   1       12587
# magencyacro  typecategory  0.3748  11528
# magencyname  typecategory  0.3748  11528
```
regression fits field2 on field1. Committed amounts track the plan only loosely (r² = 0.28), and spending hardly tracks either (r² ≈ 0.04). The three budget columns measure different stages and are not nested. If you assumed spent ≤ committed ≤ planned, check it before relying on it.

Variations

Quick triage table for any file

qsv moarstats --advanced data.csv
qsv select field,type,zero_share,berger_parker_dominance,robust_max_zscore,l_skewness,lag1_autocorrelation,benford_mad \
  data.stats.csv | qsv table

Read across the row for each column. A zero share that doesn't match Berger-Parker suggests inconsistent encoding. A large robust_max_zscore suggests outliers, an l_skewness near 1 means a heavy right tail, a high lag1_autocorrelation means order effects, and a benford_mad ≥ 0.015 (on naturally occurring amounts) is a reason to look closer.

Chart it

Run moarstats before viz smart --smarter. The dashboard then uses moment_skewness for skew hints and hoover_index and Gini for its Lorenz panels.

Pre-baked baseline

moarstats reuses an existing capital.stats.csv. Build it yourself with qsv stats --everything --stats-jsonl to control the percentiles and other options, or pass --stats-options.

Performance notes

Everything except --advanced comes from the stats cache, with no extra pass over the data.
--advanced reads every value of every numeric column. It took well under a second here, and it scales with the size of the file.
--bivariate needs an index, which is created automatically if missing. It is multithreaded, and --bivariate-batch bounds its memory on wide files.
After a qsv upgrade, a .stats.csv written by the older version is recomputed automatically, so you never read old formulas' values.

Caveats

These measures are signals, not verdicts. A Benford failure, a high lag-1 value or a dominant category each have innocent explanations, so confirm with the data owner.
The Benford bands are Nigrini's for first digits. They assume many values spanning several orders of magnitude and coming from a natural process.
Robust z-scores are undefined when the MAD is 0, which happens whenever more than half of a column's values are equal.
moment_skewness, kurtosis, Jarque-Bera and the bimodality coefficient changed in 24.0.0 to match scipy. Values from 23.0.1 and earlier are not comparable (details).