← Full reference

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

OptionWhat it does
-Wthe delimiter is any run of spaces and tabs
-t ','the delimiter is the given character
--output-delimiter=';'a different delimiter on output
--header-inthe first input line is a header; skip it
--header-outprint a header for the result (sum(score), GroupBy(name) …)
-Hsame as --header-in --header-out
-Cskip comment lines (starting with # or ;)
-R 2 (--round=2)round results to 2 decimal places
--format='%.3f'printf-style number format
--narmskip NA and NaN values
--no-strictallow lines with different numbers of fields; --filler=X — what to fill missing ones with (default N/A)
-zlines are separated by a NUL byte

Grouping

OptionWhat it does
-g 1group by column 1 (or -g 1,2) — compute aggregates separately for each value
-ssort the input before grouping — almost always needed together with -g
-icase-insensitive text comparison (grouping and string operations)
-fprint the whole input line before the result — for per-line operations (round, md5 …)
-c ';'separator in collapse and unique lists (comma by default)

Operations

OperationWhat it computes
countnumber of lines (in the group)
sum / meansum / mean
min / max / rangeminimum / maximum / the difference between them
absmin / absmaxminimum / maximum by absolute value
median / q1 / q3 / iqrmedian / quartiles / interquartile range
perc:9090th percentile (perc without a number — the 95th)
mode / antimodethe most frequent / the rarest value
sstdev / pstdevsample / population standard deviation
svar / pvarsample / population variance
geomean / harmmeangeometric / harmonic mean
first / lastfirst / last value
unique / countuniquecomma-separated list of distinct values (sorted) / their count
collapseall values of the group, comma-separated, in original order
randa 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 complains line 1 has only 1 fields. Add -W or -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; --narm skips only NA/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 -s the output is sorted by group key, not in order of first appearance.
  • Fractional results are printed with many digits (4.0824829046386); round with -R or --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 answers conflicting operation.

See also

awk · sort · uniq · cut