2

I have a text file like the one below although with almost many columns. I want to extract column names that have a specific proportion/number of TRUE values, for example columns with TRUE in 2 out of the 9 rows (with TRUE/FALSE values).

Alternatively, it would be nice to extract columns with at least a certain number (e.g. 2) of TRUE values. In the example above, it would be columns with 2 to a possible 9 rows with TRUE values. Should be generalizable to files with different number of rows.

Thanks!

Example of input file:

Comparison  MT  group1  group1.1    group1.2    group1.3    group1.4    group1.5    group1.6    group1.7    group1.8    group1.9
BP:HA      FALSE FALSE  FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        TRUE
CB:HA      FALSE TRUE   FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        FALSE 
HA:PI      TRUE  TRUE   FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        FALSE 
AL:GR      FALSE FALSE  FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        FALSE 
AL:LA      TRUE  FALSE  FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        FALSE 
AL:PL      FALSE FALSE  FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       TRUE         FALSE 
GR:PP      FALSE FALSE  FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        FALSE 
LA:PP      TRUE  FALSE  FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        TRUE
PL:PP      FALSE FALSE  FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE       FALSE        TRUE

Desired outcome for columns with 2 or more TRUE values:

output file:

MT
group1
group1.9
  • 1
    Do you care about the column headers? They're all the same in your sample, how would you identify the columns from the output? – ilkkachu Jan 24 '18 at 20:14
  • Please clarify if column headers matter and how you were planning to search for "columns with 5 to a possible 9 rows". Would the ideal be to search for a certain minimum percentage value? I think R or Python would be better than both awk and grep for solving this. – Alexander Jan 24 '18 at 20:50
  • 1
    post the expected result – RomanPerekhrest Jan 24 '18 at 21:08
  • I would like to get the column name in the output. Column names are different and they do matter, I've made the edits to my question – Age87 Jun 18 '18 at 1:01
1

Here's one way of picking out the columns with enough instances of TRUE, using bash:

min_true=3; \
max_col=12; \
for col in $(seq 2 $max_col); do \
  sed 's,    ,.,g;s,   ,.,g' "$filename" | \
    tail -n+2 | \
    cut -d. -f$col > /tmp/f; \
  count=$(grep TRUE /tmp/f | wc -l); \
  if [ "$count" -ge "$min_true" ]; then \
    echo "Column $(($col-1)): $count out of $(echo $(wc -l </tmp/f))"; \
    cat /tmp/f; \
  fi; \
done

The output can be grepped for strings like "3 out of 9", to get the column number (counting from 0):

Column 1: 3 out of 9
FALSE
FALSE
TRUE
FALSE
TRUE
FALSE
FALSE
TRUE
FALSE
  • Thanks! is there a way to get the column heading/name for the the example? – Age87 Jun 18 '18 at 1:02
1

Scan all columns and accumulate the count of "TRUE" for each column.
At the end, print all columns that are equal or bigger than a set point:

#!/bin/bash
awk -vprop="${1:-0.3}" '
        NR==1{split($0,fields);next};
        {for(i=2;i<=NF;i++){  if($i=="TRUE" ){t[i]++};
                              if($i=="FALSE"){f[i]++}
                           }
        }
        END{
            for(j in t)
            if( (1/(1+f[j]/t[j])) >= prop){
                printf("%-10s\t%s\t%s\n",fields[j],j,1/(1+f[j]/t[j]) )
            }
        }' infile

On execution (for the data you supplied):

$ ./script  0.001
MT              i=2     t=3     f=6     p=0.333
group1          i=3     t=2     f=7     p=0.222
group1.8        i=11    t=1     f=8     p=0.111
group1.9        i=12    t=3     f=6     p=0.333

Column 2 (MT) has more than 1 TRUE values: 3 in fact.
Column 3 (group1) has 2 TRUE values.
Column 11 (group1.8) has 1 TRUE value. Column 12 (group1.9) has 3 TRUE values.

If you do not supply a proportion, it defaults to 0.3.

$ ./script
MT              i=2     t=3     f=6     p=0.333
group1          i=3     t=2     f=7     p=0.222
group1.9        i=12    t=3     f=6     p=0.333
0
$ awk -v p='10' 'NR==1 { split($0,cols); next }
                 { 
                     for (i=2; i<=NF; ++i)
                         nt[i] += ($i == "TRUE" ? 1 : 0 )
                 }
                 END {
                     for (i=2; i<=NF; ++i) {
                         cp = 100*nt[i]/NR
                         if (cp > p) printf("%-20s %.2f%%\n", cols[i], cp)
                     }
                 }' file
MT                   30.00%
group1               20.00%
group1.9             30.00%

The awk program takes a parameter p which is the percentage of TRUE that is required for a column header to be displayed.

From the first line, it saves all the column headers into the array cols. It then sums up how many times the word TRUE occurs in each column. At the end, it calculates the percentage of rows that were TRUE in each column as cp and compares this to p. If it's greater than the given p value, the column name and the percentage is outputted.

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.