2

I have code running on a Linux machine, that counts the number of TRUES found in a given column/field, printing as output the column number and the number of TRUEs in that column.

In the new input, the rows are assigned (last column in input) as either "large" or "small" (three of each).

I would like to count the number of "small" and "large" for each column with 2 or more TRUEs.

Code for finding columns with 2 or more TRUEs (I know the code below ignores first column of input):

awk -vtc=2 'NR==1{next};
                NR==2{for(i=2;i<=NF;i++){t[i]=0}};
                {for(i=2;i<=NF;i++){if($i=="TRUE"){t[i]++}}}
                END{
                    for(j in t)
                    if(t[j]>=tc){print(j,t[j])}
                }' input.tsv > output.tsv

Input.tsv:

MT MT MT MT MT MT MT MT MT MT
FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE
FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE
FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE
FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE TRUE TRUE

output.tsv:

(first column: column number; second column: number of TRUEs)

3 3
6 3
9 2
10 2

new input.tsv

MT MT MT MT MT MT MT MT MT MT CAT
FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE LARGE        
FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE SMALL         
FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE SMALL        
FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE SMALL        
FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE LARGE     
FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE TRUE TRUE LARGE

Desired output.tsv:

(third column: number of TRUE's assigned as small; fourth column: number of TRUEs assigned as large)

3 3 2 1
6 3 1 2
9 2 1 1
10 2 0 2

Help from any of you linux wizards is highly appreciated!

  • Which of the two csvs do you need to parse? The first or the second? – terdon Sep 26 '19 at 17:06
  • Shouldn't it be 9 2 2 0 instead of 9 2 1 1? – Freddy Sep 26 '19 at 18:43
  • You're right. I've corrected it. Both input files should be the same either than the last CAT column. I – Age87 Sep 26 '19 at 22:09
3

A solution that uses (pseudo) multi-dimensional arrays in awk

awk '
BEGIN {
    b["TRUE"] = 1
    b["FALSE"] = 0
}
FNR > 1 {
    for (i=1; i < NF; ++i)
        a[i, $NF] += b[$i]
}
END {
    s = "SMALL"
    l = "LARGE"
    for (j=1; j<=i; ++j)
        if (a[j, s] || a[j, l])
            print j, a[j, s] + a[j, l],
            a[j, s] + 0,
            a[j, l] + 0
}' input.tsv

Or using true multi-dimensional arrays available in GNU awk

awk '
FNR > 1 {
    for (i=1; i < NF; ++i)
        if ($i == t)
            ++a[i][$NF] 
}
END {
    for (j in a)
        print j, a[j][s] + a[j][l],
        +a[j][s],
        +a[j][l]
}' t=TRUE s=SMALL l=LARGE input.tsv
1

Inelegant sledgehammer but it seems to work

#!/bin/bash

cols=$(echo $(head -n 1 file) | awk '{print gsub(/ /, "")}')
sed -e "1d" -e "s/TRUE/1/g" -e "s/FALSE/0/g" -e "s/ /,/g" file > tmp1
sed "/,S.*/d" tmp1 > tmp2
for s in $(seq 1 $cols); do
    tr=$(cut -d, -f$s tmp1 | paste -s -d+ | bc --)
    if [ $tr -gt 0 ]; then
        trl=$(cut -d, -f$s tmp2 | paste -s -d+ | bc --)
        echo $s $tr $(( $tr-$trl )) $trl
    fi
done | column -t -N Col,True,Small,Large
rm tmp1 tmp2

Output

Col  True  Small  Large
3    3     2      1
6    3     1      2
9    2     1      1
10   2     0      2

EDIT

Slightly less offensive in awk

#!/bin/bash

sed -e "1d" -e "s/TRUE/1/g" -e "s/FALSE/0/g" file | awk '{
    for (i=1; i<NF; i++)
        {sumall[i]+= $i; if ($NF == "LARGE") {sumlarge[i]+= $i}};
    } END {
    for (x in sumall)
        if (sumall[x] > 0)
            { print x, sumall[x], sumall[x]-sumlarge[x], sumlarge[x]}
    }' | column -t -N Col,True,Small,Large
  • Need to check the first one, but there's a bug with the awk version. In some columns with 4 TRUEs it shows all 4 coming from one of the categories (e.g. small) when there are only 3. – Age87 Sep 26 '19 at 23:42
  • For the first solution, the third and fourth columns came back incorrect. – Age87 Sep 26 '19 at 23:54
  • That's what I get for editing after testing. I didn't properly edit the sed to tmp2. Corrected now. – bu5hman Sep 27 '19 at 3:07
  • I couldn't replicate the bug you found in the awk version. Could you post the data file that gives this error? – bu5hman Sep 27 '19 at 3:28
  • the data file has 14 million columns. I've fixed the difference, which was that the "LARGE" and "SMALL" column at the end has first a large, followed by three small, then two more large, rather than three followed by three. Would that help? – Age87 Sep 27 '19 at 3:49
0

This uses pipelines with different utilities being invoked:

$ sed -E '1d;s/FALSE/0/g;/LARGE$/s/TRUE/L/g;s/TRUE/S/g' input.tsv |
  datamash transpose |
  perl -F'\t' -lane '$,="\t"; my %h;
    my $c = grep { /^([LS])$/ && ++$h{$1} } @F;
    print $., $c, $h{S}||0, $h{L}||0 if $c > 1;
  '
3   3   2   1
6   3   1   2
9   2   1   1
10  2   0   2

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.