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.

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...)
    – C. M.
    Jun 23 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)?
    – AdminBee
    Jun 23 at 7:53
  • If specific rows don't survive the filter they do not need to be printed in the output
    – slives
    Jun 23 at 13:39
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
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.
    – slives
    Jun 23 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
    – slives
    Jun 24 at 18:14
  • Have you tried it to see if it works as-is? (spoiler alert: it does) Jun 24 at 18:16
  • Indeed, it works.
    – slives
    Jun 25 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
    
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
    – slives
    Jun 23 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.
    – FelixJN
    Jun 23 at 14:32
  • I tried running the edited script, and it returns rows with different counts of values for each column.
    – slives
    Jun 24 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.
    – FelixJN
    Jun 24 at 20:42

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.