Select lines where at least x columns above threshold value

03-14-2013

Registered User

57, 0

Join Date: Mar 2012

Last Activity: 4 June 2019, 4:18 AM EDT

Posts: 57

Thanks Given: 23

Thanked 0 Times in 0 Posts

Select lines where at least x columns above threshold value

I have a file with 20 columns. I'd like to retain only the lines for which the values in at least x columns, looking only at columns 6-20, are above a threshold.

For example, I'd like to retain only the lines in the file below that have at least 8 columns (again, looking only at columns 6-20) with the value of at least 0.75. (I would like to be able to easily modify the code so that I could play around with the number of minimum columns (8 in this case) as well as the threshold (0.75)).

File:

Code:

s_20331    822    1    1.000    5.0    0.00000000    0.14395044    0.00000000    0.00000000    0.00000000    0.20102041    0.00000000    0.00000000    0.00000000    0.28091837    0.11224490    0.03571429    0.00000000    0.00000000    0.00000000
s_20416    154    1    1.000    5.0    0.00000000    1.00000000    0.66666667    0.40000000    0.30216165    1.00000000    0.66666667    0.45142857    0.35714286    0.11111111    0.32659933    0.55245256    0.17424242    0.32832080    0.10345717
s_20476    114    1    1.000    5.0    0.00000000    1.00000000    0.42857143    0.85100619    1.00000000    1.00000000    0.42857143    0.86996904    1.00000000    0.25000000    0.13039843    0.00000000    0.19697069    0.25000000    0.10607391
s_20477    162    1    1.000    6.0    0.20987654    0.79423868    0.81481481    0.78395062    0.77777778    1.00000000    1.00000000    1.00000000    1.00000000    0.00000000    0.00000000    0.00000000    0.00000000    0.00000000    0.00000000

Output:

Code:

s_20477    162    1    1.000    6.0    0.20987654    0.79423868     0.81481481    0.78395062    0.77777778    1.00000000    1.00000000     1.00000000    1.00000000    0.00000000    0.00000000    0.00000000     0.00000000    0.00000000    0.00000000

I'm a novice and all I have so far is an awk command to set a threshold in individual columns, and then pipe that to another awk command screening another column. This obviously is inelegant as well as ineffective for allowing some columns to remain below the threshold.

Code:

awk '{if($6>=0.75)print;}' | awk '{if($7>=0.9)print;}' | awk '{if($8>=0.9)print;}'  | awk '{if($9>=0.9)print;}' [...etc]

Moderator's Comments:

code tags also for data files

Last edited by Scrutinizer; 03-14-2013 at 04:01 PM.. Reason: additional code tags

pathunkathunk

View Public Profile for pathunkathunk

Find all posts by pathunkathunk

03-14-2013

Registered User

12,315, 4,560

Join Date: Jul 2012

Last Activity: 22 November 2019, 4:29 PM EST

Location: San Jose, CA, USA

Posts: 12,315

Thanks Given: 952

Thanked 4,560 Times in 3,818 Posts

You could try something like:

Code:

#!/bin/ksh
# SYNOPSIS:
# colcheck [file [first_column [last_column [threshhold [pass_count]]]]]
# DESCRIPTION:
# Print all lines in the file named by "file" (default file is input) in which
# at least "pass_count" (default value 8) values in columns "first_column"
# (default value 6) through "last_column" (default value 20) are greater than or
# equal to "threshold" (default value 0.75).
file=${1:-input}
fc=${2:-6}
lc=${3:-20}
threshold=${4:-0.75}
pass_count=${5:-8}
awk -v f="$fc" -v l="$lc" -v t="$threshold" -v p="$pass_count" '
{       c = p
        for(i = f; i <= l && c; i++) if($i >= t) c--
        if(c == 0) print
}' "$file"

If you are using a Solaris/SunOS system, use /usr/xpg4/bin/awk or nawk instead of awk.

I use the Korn shell, but this should also work with any other shell that accepts Bourne shell syntax (such as bash).

Last edited by Don Cragun; 03-14-2013 at 03:41 PM.. Reason: Fix typo in a comment.

This User Gave Thanks to Don Cragun For This Post:

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

03-14-2013

Read Only

1,278, 486

Join Date: Sep 2012

Last Activity: 27 February 2020, 8:59 PM EST

Location: Houston, Texas, USA

Posts: 1,278

Thanks Given: 0

Thanked 486 Times in 451 Posts

try also:

Code:

awk '{count=0; for (col=6; col<=20; col++) ($col >= .75) ? count++ : 0; if (count>=8) print}' infile

This User Gave Thanks to rdrtx1 For This Post:

rdrtx1

View Public Profile for rdrtx1

Find all posts by rdrtx1

03-14-2013

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

In your sample code, you don't have identical thresholds for the columns, but in your spec, you do. I'll assume the latter, as it's easier for a start.
For playing around, it might be best to have all parameters as variables:

Code:

$ awk '{cnt=0; for (i=FST; i<=LST; i++) cnt+=($i>THR)} cnt>=MIN' FST=6 LST=20 THR=0.75 MIN=8 file
s_20477    162    1    1.000    6.0    0.20987654    0.79423868    0.81481481 etc . . .

or, shamelessly stealing Don Cragun's ideas, this should do as well:

Code:

d$ awk '{cnt=MIN; for (i=FST; i<=LST && cnt; i++) cnt-=($i>THR)} !cnt' FST=6 LST=20 THR=0.75 MIN=8 file
s_20477    162    1    1.000    6.0    0.20987654    0.79423868    0.81481481    0.78395062 etc . . .

If you want exactly MIN columns to exceed the threshold, remove the && cnt in the for (...).

Last edited by RudiC; 03-14-2013 at 06:47 PM..

This User Gave Thanks to RudiC For This Post:

RudiC

View Public Profile for RudiC

Find all posts by RudiC

Shell Programming and Scripting

Select lines where at least x columns above threshold value

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to select rows that have opposite values (A vs B, or B vs A) on first two columns?

Discussion started by: nengcheng

2. Shell Programming and Scripting

How do I select certain columns with matching pattern and rest of the lines?

Discussion started by: kchinnam

3. Shell Programming and Scripting

Select all the even columns from a file

Discussion started by: forU

4. Shell Programming and Scripting

sum of a column and selecting lines with value above threshold

Discussion started by: f_o_555

5. UNIX for Dummies Questions & Answers

help! script to select line with greatest value 2 between columns

Discussion started by: wolf_blue

6. Shell Programming and Scripting

Select columns from a matrix given within a range in BASH

Discussion started by: shoaibjameel123

7. Shell Programming and Scripting

Select lines in which column have value greater than some percent of total file lines

Discussion started by: vaibhavkorde

8. Shell Programming and Scripting

[Solved] Select the columns which have value greater than particular number

Discussion started by: vaibhavkorde

9. Shell Programming and Scripting

Select and display sum depending upon even columns

Discussion started by: adityamitra

10. UNIX for Dummies Questions & Answers

Select and display sum depending upon even columns

Discussion started by: adityamitra