0

I have a tab delimited file with 3 columns that include semicolon separated data. I want to filter values in each column as such (meet all 3 criteria across the 3 columns): first column (<-0.5), second column (>1), third column (>2). The real data has multiple columns.

Input

-0.6;0.14;-0.56;0.2    10.4;NA;5.1;2    3;1;4;3    A;B;C;D
-0.9;-0.16;-1.1        2.4;0.1;0.9      10;1;3     E;F;G

Desired output

-0.6;-0.56         10.4;5.1       3;4    A;C
-0.9               2.4            10     E

For each row, the number of values in each column should be the same before and after filtering.

Improve this question
3
  • Are there any qualifications on tools/programming languages used? (It is tagged 'shell' and 'scripting', so I would guess using bash and common tools like grep...) Commented Jun 23, 2021 at 1:12
  • What do you want to output when none of the "sub-fields" of a given column survive the filter (e.g. assume you had the criterion ">5" for the third column)? Commented Jun 23, 2021 at 7:53
  • If specific rows don't survive the filter they do not need to be printed in the output Commented Jun 23, 2021 at 13:39

2 Answers 2

Reset to default
2

Here's a crazy bit of perl

perl -lane '
    BEGIN {
        @criteria = (
            sub {shift() < -0.5},
            sub {shift() > 1},
            sub {shift() > 2},
        )
    }
    @filtered = ();
    for $i (0..$#F) {
        push @filtered, join ";", grep {$criteria[$i]->($_)} split /;/, $F[$i];
    }
    print join " ", @filtered;
' file | column -t

-0.6;-0.56  10.4;5.1  3;4
-0.9        2.4       10

This more accurately reflects your requirements

perl -lane '
    sub criteria {
        $_[0] < -0.5 and
        $_[1] > 1    and
        $_[2] > 2
    }

    @data = map {[split /;/]} @F;
    @filtered = map {[]} @F;

    for ($i = 0; $i < @{$data[0]}; $i++) {
        @tuple = map {$data[$_][$i]} (0..$#F);
        if (criteria(@tuple)) {
            push @{$filtered[$_]}, $tuple[$_] for (0..$#F);
        }
    }
    print join " ", map {join ";", @$_} @filtered;
' file
Improve this answer
4
  • I appreciate the response. I have updated the example data to clarify. I only want to keep values where paired data across the 3 columns passes the filter. For example, for the last values in the first row: 0.2 (not less than -0.5) 2 (>1) 3 (>2) ; therefore this set of paired data should be filtered out. Commented Jun 23, 2021 at 14:05
  • The updated script works. Is there a way to print paired labels from another column in the output file? I have updated the example Commented Jun 24, 2021 at 18:14
  • Have you tried it to see if it works as-is? (spoiler alert: it does) Commented Jun 24, 2021 at 18:16
  • Indeed, it works. Commented Jun 25, 2021 at 22:29
1

Via awk:

  • Test the first three fields if the conditions are met for all set members. (split fields on ; and test each pair)

  • If so, remember the position of the set in each field. (in array sel)

  • In the second block, run through all fields and keep only the values matching the positions from before.

  • Print only, if any match was found.

    BEGIN {FS=OFS="\t"}
{
#select IDs of value sets to be keept
  split($1,a,";")
  split($2,b,";")
  split($3,c,";")
  nsel=0 ; delete sel
  for ( i in a ) {
      if (a[i]+0<-0.5 && b[i]+0>1 && c[i]+0>2) {
      sel[++nsel]=i
      }
  }
#if any: run through all fields and reselect
  if (nsel) {
      for (i=1 ; i<=NF ; i++) {
          split($i,a,";")
          $i=a[sel[1]]
          for (j=2 ; j<=nsel ; j++) {
              $i=$i";"a[sel[j]]
          }
      }
  }
}
#print only if any matching set was found
nsel
Improve this answer
4
  • Thanks. I didn't mention clearly that for each row, the values in different columns are paired. Therefore, if the value in the first column doesn't pass the filter, the paired data point in the second and third column should also be filtered out. For each row, the number of values in each column should be the same before and after filtering. See the updated example, for the last values in the first row: 0.2 (not less than -0.5) 2 (>1) 3 (>2) ; therefore this set of paired data should be filtered out Commented Jun 23, 2021 at 14:13
  • See edit. Not with the whole flexibility for the operators, but a bit shorter on the other hand. Of course the functions as defined in the other script could also be applied in the if-clause. Commented Jun 23, 2021 at 14:32
  • I tried running the edited script, and it returns rows with different counts of values for each column. Commented Jun 24, 2021 at 15:44
  • @slives - I see, you meant the selected pairs should also apply to all other fields? In that case the approach is a bit different. See update. Commented Jun 24, 2021 at 20:42

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.