datamash
Column statistics from the command line — sums, means, medians, grouping
GNU datamash computes aggregates over the columns of text tables: sum, mean, minimum, median, number of unique values — over the whole file or per group. Where awk would need a program with arrays, here a single word is enough. The sandbox has version 1.8.
Syntax
datamash [options] operation column [operation column…] < file
datamash [options] -g column operation column… < file
It reads only stdin. Columns are numbered from 1; you can list 1,3 and ranges 2-4. With a header (--header-in, -H) a column can be referred to by name. The default delimiter is a tab, and output uses the same delimiter.
Options
Input and output
| Option | What it does |
|---|---|
-W | the delimiter is any run of spaces and tabs |
-t ',' | the delimiter is the given character |
--output-delimiter=';' | a different delimiter on output |
--header-in | the first input line is a header; skip it |
--header-out | print a header for the result (sum(score), GroupBy(name) …) |
-H | same as --header-in --header-out |
-C | skip comment lines (starting with # or ;) |
-R 2 (--round=2) | round results to 2 decimal places |
--format='%.3f' | printf-style number format |
--narm | skip NA and NaN values |
--no-strict | allow lines with different numbers of fields; --filler=X — what to fill missing ones with (default N/A) |
-z | lines are separated by a NUL byte |
Grouping
| Option | What it does |
|---|---|
-g 1 | group by column 1 (or -g 1,2) — compute aggregates separately for each value |
-s | sort the input before grouping — almost always needed together with -g |
-i | case-insensitive text comparison (grouping and string operations) |
-f | print the whole input line before the result — for per-line operations (round, md5 …) |
-c ';' | separator in collapse and unique lists (comma by default) |
Operations
| Operation | What it computes |
|---|---|
count | number of lines (in the group) |
sum / mean | sum / mean |
min / max / range | minimum / maximum / the difference between them |
absmin / absmax | minimum / maximum by absolute value |
median / q1 / q3 / iqr | median / quartiles / interquartile range |
perc:90 | 90th percentile (perc without a number — the 95th) |
mode / antimode | the most frequent / the rarest value |
sstdev / pstdev | sample / population standard deviation |
svar / pvar | sample / population variance |
geomean / harmmean | geometric / harmonic mean |
first / last | first / last value |
unique / countunique | comma-separated list of distinct values (sorted) / their count |
collapse | all values of the group, comma-separated, in original order |
rand | a random value from the group |
Whole tables: transpose — swap rows and columns, reverse — reverse the column order, check — verify all lines have the same number of fields (check 3 col — exactly 3 columns), crosstab 1,2 — a pivot table over two columns, rmdup 1 — remove lines with a repeated value in the column.
Per line: round, floor, ceil, trunc, bin:10 (round down to a multiple of 10), md5, sha256, base64, debase64, basename, dirname, extname, getnum (extract a number from a string), cut — applied to each line separately.
Examples
printf 'dev\t30\nops\t25\ndev\t35\n' > data.tsv
# sum and mean of the second column
datamash sum 2 mean 2 < data.tsv
# per group of the first column: sum and line count
datamash -s -g 1 sum 2 count 2 < data.tsv
# minimum, maximum, median, quartiles
datamash min 2 max 2 median 2 q1 2 q3 2 < data.tsv
# space-separated values
printf 'x 1\ny 2\nx 3\n' | datamash -W -s -g 1 sum 2
# CSV with a header: skip it and print our own
printf 'name,score\na,1\nb,2\n' | datamash -t, --header-in sum 2
printf 'name,score\na,1\nb,2\n' | datamash -t, -H sum score
# list of values and number of distinct values per group
datamash -s -g 1 collapse 2 countunique 2 < data.tsv
# rounding
datamash -R 2 sstdev 2 < data.tsv
# swap rows and columns
datamash transpose < data.tsv
# check that every line has exactly 2 fields
datamash check 2 col < data.tsv
# pivot table: how many lines for each pair of values
printf 'a x\na y\nb x\na x\n' | datamash -W -s crosstab 1,2
# remove lines with a repeated value in the first column
printf 'a 1\na 2\nb 3\n' | datamash -W rmdup 1
# per line: a number from a string and rounding
printf 'v12.7kg\n' | datamash getnum 1
printf '2.5\n3.7\n' | datamash round 1
Pitfalls
- The default delimiter is a tab. For a line
x 1(space-separated) datamash sees one field and complainsline 1 has only 1 fields. Add-Wor-t ' '. - Grouping only works on adjacent lines, like uniq. Without
-s, on unsorted input the same group comes out several times. Almost always write-s -g …. - A non-numeric value is an error. On a header line or a word in a numeric column datamash stops with
invalid numeric value. Skip the header with--header-in;--narmskips onlyNA/NaN, not arbitrary text. -t ' 'is exactly one space. Two spaces in a row give an empty field. For space-aligned output you need-W.- The line order changes. With
-sthe output is sorted by group key, not in order of first appearance. - Fractional results are printed with many digits (
4.0824829046386); round with-Ror--format. - You can't mix operations arbitrarily. Table operations (
transpose,check,rmdup…) go alone; group and per-line operations can't be combined in one command — datamash answersconflicting operation.