How to filter data from multiple semicolon separated columns

Question

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.

Answer 1

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

Answer 2

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