0

I want to create several separate CSV files from a table.  Here is an example table:

gene   REF_S1_host  REF_S1_FL  S1_host1  S1_host2  S1_FL  REF_S2_host  REF_S2_FL  S2_host1  S2_host2  S2_FL
gene1  1            0          0         0         0      0            0          0         0         0
gene2  1            1          1         1         0      0            0          0         0         0
gene3  0            1          0         0         1      0            0          0         0         0
gene4  1            0          0         0         0      1            0          0         0         0
gene5  0            0          0         0         0      1            0          1         0         0
gene6  1            0          0         0         0      0            0          0         1         1
gene7  0            1          0         0         0      0            0          0         0         1

I would like to create a CSV (or other tab-delimited file) that:

  1. pulls all data that includes "1" under a column header containing "S1", but where all headers containing "S2" have a value of "0" for that same gene. For example:

    gene   REF_S1_host  REF_S1_FL  S1_host1  S1_host2  S1_FL  REF_S2_host  REF_S2_FL  S2_host1  S2_host2  S2_FL
    gene1  1            0          0         0         0      0            0          0         0         0
    gene2  1            1          1         1         0      0            0          0         0         0
    gene3  0            1          0         0         1      0            0          0         0         0
    
  2. pulls only those rows in which there is a "1" value for any REF file (S1 or S2) but only "0"'s for all other fields (i.e., row headers that do not contain the "REF"). For example:

    gene   REF_S1_host  REF_S1_FL  S1_host1  S1_host2  S1_FL  REF_S2_host  REF_S2_FL  S2_host1  S2_host2  S2_FL
    gene1  1            0          0         0         0      0            0          0         0         0
    gene4  1            0          0         0         0      1            0          0         0         0
    
  3. Where a REF_S1* contains a "1" + where all other (i.e., non-REF) S1 samples are "0" + where all REF_S2* are "0" + but where any other S2 samples (non-REF) are "1". For example:

    gene   REF_S1_host  REF_S1_FL  S1_host1  S1_host2  S1_FL  REF_S2_host  REF_S2_FL  S2_host1  S2_host2  S2_FL
    gene6  1            0          0         0         0      0            0          0         1         1
    gene7  0            1          0         0         0      0            0          0         0         1
    
  4. And lastly, where any *FL is "1", and all *host are "0". For example:

    gene   REF_S1_host  REF_S1_FL  S1_host1  S1_host2  S1_FL  REF_S2_host  REF_S2_FL  S2_host1  S2_host2  S2_FL
    gene3  0            1          0         0         1      0            0          0         0         0
    gene7  0            1          0         0         0      0            0          0         0         1
    

But I am not sure how to go about doing this. Any advice is welcome.

1
  • 3
    It's great that you figured out how to use our table formatting feature,  but, if you want CSV output, you should show CSV output. Apr 13, 2021 at 4:09

2 Answers 2

1

I'll assume that

  • Your data are separated by whitespace, as you have (sort-of) shown.
  • Your table always has eleven columns (but, presumably, can have any number of rows).
  • Cell values never contain whitespace.  (In particular, everything other than Row 1 (headers) and Column 1 (gene) is either 0 or 1.)

This is easy with awk.

  1. ... all data that includes "1" under a column header containing "S1", but where all headers containing "S2" have a value of "0" for that same gene.

    In other words,

      (the 2nd column is 1 OR the 3rd column is 1 OR the 4th column is 1 OR the 5th column is 1 OR the 6th column is 1)
        AND
       the 7th column is 0
        AND
       the 8th column is 0
        AND
       the 9th column is 0
        AND
      the 10th column is 0
        AND
      the 11th column is 0
    So,

    awk -v OFS=',' '
            NR==1 { next }
            ($2==1 || $3==1 || $4==1 || $5==1 || $6==1)  &&
                    $7==0 && $8==0 && $9==0 && $10==0 && $11==0 { $1=$1; print }
        '
    
    • OFS is "output field separator".  -v OFS=',' tells awk to write the data with comma-separated fields, even if the input was tab-separated or whitespace-separated.
    • The NR==1 { next } tells awk to skip the first line (the header row).  If you want to print the header row, change this to NR==1 { $1=$1; print; next }.
    • The next two lines encode the AND/OR logic spelled out above.
    • { $1=$1; print } prints the row (if it satisfies the conditions).  The $1=$1 means set the first field equal to itself.  This sounds like it does little to nothing; in fact, it is a trick to force awk to rebuild the line with the new (user-specified) output field separator (which we have specified as comma).  If you ever change your mind and want to output the rows exactly as they appear in the input, delete the -v OFS=',' and the $1=$1;.
  2. ... only those rows in which there is a "1" value for any REF file (S1 or S2) but only "0"'s for all other fields ...

    awk -v OFS=',' '
            NR==1 { next }
            ($2==1 || $3==1 || $7==1 || $8==1)  &&
                    $4==0 && $5==0 && $6==0 && $9==0 && $10==0 && $11==0 { $1=$1; print }
        '
    
  3. Where a REF_S1* contains a "1" + where all other (i.e., non-REF) S1 samples are "0" + where all REF_S2* are "0" but where any other S2 samples (non-REF) are "1".

    awk -v OFS=',' '
            NR==1 { next }
            ($2==1 || $3==1) && $4==0 && $5==0 && $6==0  &&
                    $7==0 && $8==0 && ($9==1 || $10==1 || $11==1) { $1=$1; print }
        '
    
  4. ... where any *FL is "1", and all *host are "0".

    awk -v OFS=',' '
            NR==1 { next }
            ($3==1 || $6==1 || $8==1 || $11==1)  &&
                    $2==0 && $4==0 && $5==0 && $7==0 && $9==0 && $10==0 { $1=$1; print }
        '
    
0

This problem is tackled using perl in three steps.

  • Write a subroutine that, given selection criteria regex, spits out the field numbers that satisfy the criteria.
  • Using the subroutine, generate the field indices for every listed criteria upon reading the First line or the header line.
  • Lastly, armed with these various arrays, we perform the Boolean selection criteria using the functions imported from the List::MoreUtils module.
  • Note: Run the four cases separately, in a one-hot manner. Otherwise the outputs will be interleaved.
perl -MList::MoreUtils=any,all -lane '
  BEGIN {
    sub mkAry {
      my $re = shift;
      grep { $_ }
      map { $h{$_} }
      grep { /$re/ } keys %h
    }
  }
  if ($. == 1) {
    print;
    @h{@F} = (0..$#F);
    @S1 = mkAry qr/S1/;
    @S2 = mkAry qr/S2/;
    @REF = mkAry qr/REF/;
    @notREF = mkAry qr/^(?!.*REF)/;
    @REF_S1 = mkAry qr/REF_S1/;
    @REF_S2 = mkAry qr/REF_S2/;
    @notREF_S1 = mkAry qr/^(?!.*REF)(?=.*S1)/;
    @notREF_S2 = mkAry qr/^(?!.*REF)(?=.*S2)/;
    @FL = mkAry qr/FL/;
    @host = mkAry qr/host/;
    next;
  }

  ##_1_:
  print if
    any { $_ == 1 } @F[@S1] and
    all { $_ == 0 } @F[@S2]
  ;

  ##_2_:
  print if
    any { $_ == 1 } @F[@REF] and
    all { $_ == 0 } @F[@notREF]
  ;

  ##_3_:
  print if
    any { $_ == 1 } @F[@REF_S1] and
    all { $_ == 0 } @F[@notREF_S1] and
    all { $_ == 0 } @F[@REF_S2] and
    any { $_ == 1 } @F[@notREF_S2]
  ;

  ##_4_:
  print if
    any { $_ == 1 } @F[@FL] and
    all { $_ == 0 } @F[@host]
  ;
' file | column -t

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .