Perform a GROUP BY-like command in UNIX

Question

I am having a text file like the following:

1 2 3 4 5 6 7 8 9 ... n    <-- column numbering
1 0 0 1 0 0 0 1 0 ... 0
0 1 0 0 0 0 0 0 0 ... 1
1 0 0 0 0 0 0 1 1 ... 0
0 1 1 1 0 1 0 0 0 ... 0
0 1 0 0 1 0 1 0 1 ... 1

Actually is a very sparse matrix, where binary values. In each row there should be more than one 1s and in each column at least one 1.

What I want to do is find out which columns have let's say less than 2 1s. For example in my matrix above I would like to get rid of columns 3, 5, 6 and 7 because they only have one 1.

Is there an UNIX command to do that? Probably I need a Group by-like (SQL) command and then keep track of which columns are less than a certain number, but I don't see how I could do it.

Answer 1

You can use awk to traverse the matrix and count the number of 1s and 0s using the following script:

count.awk:

NR != 1 {
  for (i=1; i<=NF; ++i)
    count[i] += $i;
}

END {
  ORS = ",";
  for (i=1; i<=length(count); ++i)
    if (count[i] >= min)
       print i
}

If you execute this script using

awk -v min=2 -f count.awk matrix.txt

you will get a line of columns that have two or more 1s, in this case "1,2,4,8,9," (note: you can change the min=X to any minimum threshold you want).

Now, use cut to print out only the columns that we want:

cols=$(awk -v min=2 -f count.awk matrix.txt); cut -d' ' -f${cols:0:-1} matrix.txt

This stores the awk output in a variable (the reason for this is that awk returns a list of columns with an extra , at the end. I "slice" the comma out when I pass the cols to cut).

Set the delimiter for cut to "space" (-d' '), and the output columns to the comma-separated list from awk, with the last comma sliced out (-f${cols:0:-1}).

Output:

1 2 4 8 9 n
1 0 1 1 0 0
0 1 0 0 0 1
1 0 0 1 1 0
0 1 1 0 0 0
0 1 0 0 1 1

If you want to output the columns with fewer than min 1s (ie. columns 3, 5, 6, 7), just reverse the condition of the if statement in the awk script above to read if (count[i] < min).

Output:

3 5 6 7
0 0 0 0
0 0 0 0
0 0 0 0
1 0 1 0
0 1 0 1