Group by column

[jonhoo]

If I have a multi-column file, I might want to calculate metrics separately for the "groups" of rows in the file. Imagine I have:

a 1
a 2
b 2
b 3

Ideally, I'd like statistics for a and b rows separately. This could either be done by indicating which column holds the value, and use the remainder (prefix and suffix) of the row as the "group by" key, or by providing a list of columns to use as key, and a key for the value. I'd prefer the former as it is easier to specify, but for large files where only some of the data is relevant, the latter approach is more flexible.

[nferraz]

Thanks for your suggestion.

st was written in a way that it can be chained with other unix tools.

For instance: you can start filtering the "a" rows with grep:

• grep "a" file.txt*
  a 1
  a 2

Next, you can use cut to pick the second column:

• grep "a" file.txt | cut -f2*
  1
  2

(The "cut" command expects the columns are separated by tab. You can use
the -d option to set another delimiter)

Finally, you pipe the output to st:

• grep "a" file.txt | cut -f2 | st*
  N min max sum mean stddev
  2 1 2 3 1.5 0.707107

This will give you the statistics grouped by "a".

The weakest link in the above chain is the cut command, which only
accepts single-character delimiters, so I'm seriously thinking about
creating a replacement for it.

But the best solution, in my opinion, is to follow the Unix Philosophy [
http://www.faqs.org/docs/artu/ch01s06.html]: Write programs that do one
thing and do it well. Write programs to work together. Write programs to
handle text streams, because that is a universal interface.

Let me know what you think!

On Thu, Apr 30, 2015 at 8:17 PM, Jon Gjengset notifications@github.com
wrote:

If I have a multi-column file, I might want to calculate metrics
separately for the "groups" of rows in the file. Imagine I have:

a 1
a 2
b 2
b 3

Ideally, I'd like statistics for a and b rows separately. This could
either be done by indicating which column holds the value, and use the
remainder (prefix and suffix) of the row as the "group by" key, or by
providing a list of columns to use as key, and a key for the value. I'd
prefer the former as it is easier to specify, but for large files where
only some of the data is relevant, the latter approach is more flexible.

—
Reply to this email directly or view it on GitHub
#15.

Nelson Ferraz

[jonhoo]

That's all very well, but not particularly useful if I have a file with lots of "groups", and I want to get the stats for all of them, and then pipe them into some third program (which is my use-case). It'd be better to be able to do:

$ st --value-column 2 file.txt
group N min max sum mean stddev
a 2 1 2 3 1.5 0.707107
b 2 1 1 2 1 0

That would let me do things like plot the means of a dataset using:

$ cat data.txt | st --value-column 2 | tail -n +1 | awk '{print $6}' | gnuplot -p -e 'plot "-" u 1:2'

BTW, you should just use awk instead of cut. awk {print $2} will print the contents of the second whitespace-separated field in the line.

[nferraz]

Thanks for the awk tip - it's a better option indeed.

Now I understand your initial request, a possible workaround (at least
until I implement some kind of group by function) is:

for g in $(awk '{print $1}' x.txt | sort -u ); do grep $g data.txt |

awk '{ print $2 }' | st --mean --no-header; done | gnuplot -p -e 'plot
"-" u 1:2'

The problem with adding a new option is that it may have a cascading effect.

For instance: if we add a --value-column option, should we assume that we
want to group by the first column? What if we want the global mean?

[jonhoo]

Sure, I could do it that way, though that requires two passes over the (potentially very large) file. It's also not safe if a field contains spaces, or if the group name might appear elsewhere in the file. Getting this right is tricky, which is why having an option for it in st would make things much easier.