Filo

Summarize a text file by column(s)

: qstats, tabutils, awk

---

[ BioWare | Main_Page ]

---

FILe and stream Operations commands are extremely useful to summarize data on the commandline. E.g. summarize columns from BED files or any tab-delimited files you may generate.

• groupBy

is a useful tool that mimics the "groupBy" clause in database systems. Given a file or stream that is sorted by the appropriate "grouping columns", groupBy will compute summary statistics on another column in the file or stream. This will work with output from all BEDTools as well as any other tab-delimited file or stream.

You specify a list of columns that should be "grouped" with the -g parameter (e.g., -g 2,3,4 will group on the second through fourth columns). You then specify column(s) that should be summarized or "operated upon" for each group with the -c parameter (e.g., -c 2 or -c 2,3 or -c 2,2,2,5). Finally, you specify what operations should be applied to the list of columns in -c.

Here is the current list of the available operations.

1. sum - numeric only
2. count - numeric or text
3. min - numeric only
4. max - numeric only
5. mean - numeric only
6. stdev - numeric only
7. median - numeric only
8. mode - numeric or text
9. antimode - numeric or text collapse (i.e., print a comma separated list) - numeric or text
10. freqasc - print a comma separated list of values observed and the number of times they were observed. Reported in ascendingorder of frequency.
11. freqdesc - print a comma separated list of values observed and the number of times they were observed. Reported in descending order of frequency.
12. collapse - print a comma separated list of each value in the grouped column.#concat - concattenate each value in the grouped column into a single string.

$ cat ex1.out
chr1 10 20 A chr1 15 25 B.1 1000
chr1 10 20 A chr1 25 35 B.2 10000

$ groupBy -i ex1.out -g 1,2,3,4 -c 9 -o sum
chr1 10 20 A 11000

• shuffle

will randomize the order of lines in a file. Somewhat less useful except if you need to bring chaos into your data ;-).

• stats

is a small utility for computing descriptive statistic on a given column of a tab-delimited file or stream. By default, it will assume you want to gather stats on the first column in your file/stream and compute all of the following statistics:

1. total number of lines
2. the sum of all the values in the column
3. the arithmetic mean (i.e., the "average") of the values in the column
4. the geometric mean (if possible)
5. the median
6. the mode
7. the anti-mode (i.e., the least frequent value)
8. the minimum
9. the maximum
10. the variance
11. the standard deviation.

# compute on column 1 by default
$ stats test

# compute on column 2
$ stats test -c 2

Source: https://github.com/arq5x/filo

---

[ BioWare | Main_Page ]