file 1:

HOGBRM443983         -2522.00  19800826
HOGBRM445985         -2389.00  19801101
HOUSAM1891409        -1153.00  19811228
HOUSAM2004289        -650.00   19860101
HOUSAM2005991        -843.00   19860109
HOCANM388722         -1546.00  19860116
HOUSAM2007297        -1882.00  19860125
HOUSAM2007389        -1074.00  19860128
HOITAM801038516      -691.00   19860128

Columns 2 and 3 include values and birthdate information( year,month,day) of each id from column1, respectively . I want to check how many ids exist within each birth year and what are the average values (from second column) of ids across different years. For example, in file1 there are 2, 1 and 6 ids across years 1980, 1981 and 1986 respectively so the output should be:


1980 2 -2455.5
1981 1 -1153.00
1986 6 -114.33

in which the first column shows the year of birth, the second column shows a number of ids within each year and the third column is the average values of ids across different years.

Considering that the real data is indeed huge, any suggestion would be appreciated.


The Awk answer:

awk '{y=substr($3,1,4); c[y]++; s[y]+=$2} END {for (y in c) {print y, c[y], (s[y]/c[y])}}' file.txt

With gnu datamash:

cut -c1-35 infile | datamash -W -g 3 count 3 mean 2

Note that you need process your data first (I used cut as it was the obvious choice with your input sample but any tool will do) so as to remove the month and day from the birth date:

HOGBRM443983         -2522.00  1980
HOGBRM445985         -2389.00  1980
HOUSAM1891409        -1153.00  1981
HOUSAM2004289        -650.00   1986

and only then pipe it to datamash.
This also assumes 3rd columns is sorted by year (if it's not sorted use datamash -s -W -g ...)


Consider using a real database.

Using a Postgres sandbox set up in a Vagrant VM, I did this using the following steps:

CREATE TABLE MyData (id text, val float, bday date);
  extract(year FROM bday) AS yr,
  count(id) AS count,
  avg(val) AS average
FROM mydata GROUP BY yr;

Output is:

  yr  | count |      average      
 1981 |     1 |             -1153
 1980 |     2 |           -2455.5
 1986 |     6 | -1114.33333333333
(3 rows)

You could probably handle this with text processing, but you mention that the data is HUGE, and an actual database is designed for this sort of computation. (And the blog post I linked to has all the steps to get a Postgres sandbox set up.)


Miller was created to solve problems like these:

$ cat hogbrm.txt | \
  mlr --nidx --repifs put '$3=sub(string($3),"(....).*", "\1")' \
  then stats1 -a count,mean -f 2 -g 3
1980 2 -2455.500000
1981 1 -1153.000000
1986 6 -1114.333333


  • Use --nidx since there is no header, just positionally indexed columns
  • Use --repifs since multiple spaces separate columns
  • Use sub to drop the last four digits of the date (column 3)
  • Use stats1 to compute the count and mean of column 2 grouped by column 3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.