6

Unix has several utilities for performing relational algebra-like operations on streams (grep, join, cut, additional things with awk). Is there a grouped aggregate utility readily available (or installable on most Linux distros)?

The goal would be to take a file with some keys in one column and values in another, such as:

foo.txt   u1    394082
bar.txt   u2    3948
frob.c    u1    29322

And output a file that has the unique values of one column, along with some aggregate of the values in another column. For example, the sum of the 3rd column by the 2nd:

$ aggregate --sum=3 --group-by=2 <data
u1 423404
u2 3948

Does such a utility exist (Perl, Awk, etc. one-liners don't count), or is it something waiting to be written?

4
  • You answered your own question. awk and/or perl are very useful for what you need. :)
    – schaiba
    Aug 1, 2013 at 15:19
  • Never used it, but from what I read about it here, r sounds also an option.
    – manatwork
    Aug 1, 2013 at 15:26
  • Write an Awk, Perl or R one-liner. Aug 1, 2013 at 23:41
  • Perl and awk one-liners don't count, but they solve the problem. On the other hand, wc and grep -c count, but don't solve the problem. Use what doesn't count!
    – Kaz
    Nov 23, 2013 at 7:11

3 Answers 3

12

I think I found this answer in another StackOverflow question, but I found "q" quite useful for that purpose: https://github.com/harelba/q.

E.g. your example goal would be achievable like that:

$ q "select c2, sum(c3) from data group by c2"
u1 423404
u2 3948

And since it uses sqlite as a backend you can use all sorts of sqlite functions to make calculations.

1
  • 2
    "q" is genius, many thanks for introducing
    – J.N.
    Feb 7, 2014 at 5:51
2

With some constraints GNU Recutils can do this. First, it needs to be a CSV file, not TSV (Recutils doesn't seem to like TSV files), and needs a header. But then I can do:

csv2rec foo.csv |recsel -G user -p 'user,sum(size)' |rec2csv

Not sure if this is better or worse than a Perl or Awk one-liner.

1
  • I'm discovering this tool and it's awesome. You can use grep/sed/awk/git on it. It's really a full database in a plain text with the advantage of being human-readible and unix-friendly, and the disadvantage that it's not SQL (with sqlite3 you would do a front-end for it, while with recutils you wouldn't). I love the idea of using vim and git for personal databases.
    – Unix
    Aug 22, 2021 at 17:06
1

If awk one-liners don't count, maybe you'll like the following shell (bash/ksh) one-liner:

sort -k2 data | ( while read c1 c2 c3; do if [ "$prev" = "$c2" ]; then
sum=$(expr $c3 + $sum); else if [ $prev ]; then echo $prev $sum; fi;
sum=$c3; prev=$c2; fi; done; echo $prev $sum)

Apart from sort and expr (for grouping and summing, respectively), the interesting ingredient used here is the read statement inside the while. And the parentheses, which create a subprocess, necessary for localizing the $prev and $sum variables.

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.