text: user3686: Im trying to classify my list into three type: XDR - A strain must be resistant to:
  MDR + CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN + AMYCACIN/CAPREOMYCIN/KANAMYCIN-> means ISONIAZID + RIFAMPICIN + any of these or two or three or all (CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN) + any of these or two or three or all (AMYCACIN/CAPREOMYCIN/KANAMYCIN)
  
  PRE-XDR - A strain must be resistant to:
  MDR + CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN -> means ISONIAZID + RIFAMPICIN + any of these or two or three or all (CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN)
  
  MDR - A strain must be resistant to:
  ISONIAZID + RIFAMPICIN 
  and if it is resistant to any one/two/three of these or all of these in 
  addition to ISONIAZID + RIFAMPICIN than again it is MDR:- 
  PYRAZINAMIDE, ETHIONAMIDE, STREPTOMYCIN, ETHAMBUTOL
  
  SUSCEPTIBLE - A strain must be Susceptible to all drugs
  I tried the code as follows for MDR:
  tail -n +2 drug.txt |\
  awk '{a[$1] = a[$1]","$2};END{for(i in a)print i"\t"a[i]}'|\
  grep -P "\t,ISONIAZID,RIFAMPICIN$"
  
  It gave me MDR starins correctly but than i had to first remove all Susceptible strains and applied it to Resistant one only.
  But when i applied the code for Pre-XDR:
  tail -n +2 drug.txt |\
  awk '{a[$1] = a[$1]","$2};END{for(i in a)print i"\t"a[i]}'|\
  grep -P 
  "\t,ISONIAZID,RIFAMPICIN|CIPROFLOXACIN|MOXIFLOXACIN|OFLOXACIN|LEVOFLOXACIN"
  
  It is giving me the result as below:
  SRR671821       ,CAPREOMYCIN,ETHAMBUTOL,ETHIONAMIDE,ISONIAZID,KANAMYCIN,OFLOXACIN,RIFAMPICIN,STREPTOMYCIN
  SRR671863       ,CAPREOMYCIN,ETHAMBUTOL,ISONIAZID,KANAMYCIN,OFLOXACIN,RIFAMPICIN
  SRR671822       ,ETHAMBUTOL,ISONIAZID,OFLOXACIN,RIFAMPICIN,STREPTOMYCIN
  SRR671864       ,CAPREOMYCIN,ETHAMBUTOL,ISONIAZID,KANAMYCIN,OFLOXACIN,RIFAMPICIN
  
  Which is not correct for Pre-XDR.
  So I'm looking for a the drugs if match than look for resistant and susceptible if these criteria match with any of the mention type (MDR,XDR...) it put those under that in a new file. Can anyone help? 
  my file druglist.sxls
  MDR eg from the file attached is:
  ERR038736   ISONIAZID   RESISTANT
  ERR038736   RIFAMPICIN  RESISTANT
  ERR038737   ISONIAZID   RESISTANT
  ERR038737   RIFAMPICIN  RESISTANT
  SRR1270146  AMIKACIN    SUSCEPTIBLE 
  SRR1270146  CAPREOMYCIN SUSCEPTIBLE 
  SRR1270146  CYCLOSERINE SUSCEPTIBLE 
  SRR1270146  ETHAMBUTOL  RESISTANT   
  SRR1270146  ETHIONAMIDE SUSCEPTIBLE 
  SRR1270146  ISONIAZID   RESISTANT   
  SRR1270146  KANAMYCIN   SUSCEPTIBLE 
  SRR1270146  PARA-AMINOSALICYLIC ACID    SUSCEPTIBLE 
  SRR1270146  PYRAZINAMIDE    RESISTANT   
  SRR1270146  RIFAMPICIN  RESISTANT   
  SRR1270146  STREPTOMYCIN    RESISTANT
  
  
  user298: Since this involves combining multiple conditions, it is easier (and certainly more readable if you ever need to come back to it later) to write a script to do this. For example, in perl:
  #!/usr/bin/perl
  
  use strict;
  use warnings;
  
  ## This will hold the information for each strain
  my %strains;
  
  ## These are the functions that will test each strain
  sub isMDR{
    my $_strain = shift;
    my %thisStrain = %{$strains{$_strain}};
    if ($thisStrain{"ISONIAZID"} && $thisStrain{"RIFAMPICIN"}){
      foreach my $drug (keys(%thisStrain)) {
        next if $drug eq "ISONIAZID";
        next if $drug eq "RIFAMPICIN";
        next if $drug eq "PYRAZINAMID";
        next if $drug eq "ETHAMBUTOL";
        next if $drug eq "STREPTOMYCIN";
        ## discard this strain if it is resistant to any other drug
        if ($thisStrain{$drug}) {
          return 0;
        }
      }
  
      return 1;
    }
    return 0;
  }
  
  sub isPreXDR{
    my $_strain = shift;
    my %thisStrain = %{$strains{$_strain}};
    if (
    ($thisStrain{"ISONIAZID"} && $thisStrain{"RIFAMPICIN"}) &&
    (
     $thisStrain{"CIPROFLOXACIN"}  ||
         $thisStrain{"MOXIFLOXACIN"} ||
     $thisStrain{"OFLOXACIN"}  ||
         $thisStrain{"LEVOFLOXACIN"}
        )
       ) {
      return 1;
    }
    return 0;
  }
  
  sub isXDR{
    my $_strain = shift;
    my %thisStrain = %{$strains{$_strain}};
    if (
    isPreXDR($_strain) &&
        (
         $thisStrain{"AMYCACIN"} ||
     $thisStrain{"CAPREOMYCIN"} ||
         $thisStrain{"KANAMYCIN"}
        )
       ){
      return 1;
    }
    return 0;
  }
  
  sub isSusceptible{
    my $_strain = shift;
    foreach my $drug (keys(%{$strains{$_strain}})) {
  if ($strains{$_strain}{$drug}) {
        return 0
      }
    }
    return 1
  }
  
  ## Iterate over each line in the input file.
  ## For each iteration, the special variable $_
  # will hold the contents of the line.
  while (<>) {
    ## Skip first line
    next if $. == 1;
    ## remove Windows-style line endings
    s/\r//;
    ## remove trailing newline
    chomp;
    ## split the line on commas
    my ($strain, $drug, $status) = split(/,/);
    if ($status eq "SUSCEPTIBLE"){
  $status = 0
    }
    else {
  $status = 1;
    } 
    $strains{$strain}{$drug}=$status;
  }
  
  foreach my $strain (keys(%strains)) {
    print "$strain\t";
    if (isMDR($strain)) {
  print "MDR\n";
    }
    elsif (isXDR($strain)) {
  print "XDR\n"
    }
    elsif (isPreXDR($strain)) {
  print "PRE-XDR\n"
    }
    elsif (isSusceptible($strain)) {
      print "SUSCEPTIBLE\n";
    }
    else {
      print "OTHER\n";
    }
  }
  
  If you save that script as foo.pl, you can run it on your file with:
  perl foo.pl drug.csv
  
  Selected output from running this on the drug.csv file you posted is:
  $ foo.pl drug.csv | grep -E 'ERR038288|ERR038736|ERR038751|SRS553990|ERR133879'
  ERR038751   OTHER
  ERR038288   SUSCEPTIBLE
  SRS553990   XDR
  ERR038736   MDR
  ERR133879   PRE-XDR
  
  
  Various details about this question were added in chat: https://chat.stackexchange.com/transcript/message/47821646#47821646
  
original_text: 3686: <p>Im trying to classify my list into three type: XDR - A strain must be resistant to:</p>
  
  <pre><code>MDR + CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN + AMYCACIN/CAPREOMYCIN/KANAMYCIN-&gt; means ISONIAZID + RIFAMPICIN + any of these or two or three or all (CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN) + any of these or two or three or all (AMYCACIN/CAPREOMYCIN/KANAMYCIN)
  </code></pre>
  
  <p>PRE-XDR - A strain must be resistant to:</p>
  
  <pre><code>MDR + CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN -&gt; means ISONIAZID + RIFAMPICIN + any of these or two or three or all (CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN)
  </code></pre>
  
  <p>MDR - A strain must be resistant to:</p>
  
  <pre><code>ISONIAZID + RIFAMPICIN 
  and if it is resistant to any one/two/three of these or all of these in 
  addition to ISONIAZID + RIFAMPICIN than again it is MDR:- 
  PYRAZINAMIDE, ETHIONAMIDE, STREPTOMYCIN, ETHAMBUTOL
  </code></pre>
  
  <p>SUSCEPTIBLE - A strain must be Susceptible to all drugs</p>
  
  <p>I tried the code as follows for MDR:</p>
  
  <pre><code>tail -n +2 drug.txt |\
  awk '{a[<span class="math-container">$1] = a[$</span>1]","<span class="math-container">$2};END{for(i in a)print i"\t"a[i]}'|\
  grep -P "\t,ISONIAZID,RIFAMPICIN$</span>"
  </code></pre>
  
  <p>It gave me MDR starins correctly but than i had to first remove all Susceptible strains and applied it to Resistant one only.</p>
  
  <p>But when i applied the code for Pre-XDR:</p>
  
  <pre><code>tail -n +2 drug.txt |\
  awk '{a[<span class="math-container">$1] = a[$</span>1]","$2};END{for(i in a)print i"\t"a[i]}'|\
  grep -P 
  "\t,ISONIAZID,RIFAMPICIN|CIPROFLOXACIN|MOXIFLOXACIN|OFLOXACIN|LEVOFLOXACIN"
  </code></pre>
  
  <p>It is giving me the result as below:</p>
  
  <pre><code>SRR671821       ,CAPREOMYCIN,ETHAMBUTOL,ETHIONAMIDE,ISONIAZID,KANAMYCIN,OFLOXACIN,RIFAMPICIN,STREPTOMYCIN
  SRR671863       ,CAPREOMYCIN,ETHAMBUTOL,ISONIAZID,KANAMYCIN,OFLOXACIN,RIFAMPICIN
  SRR671822       ,ETHAMBUTOL,ISONIAZID,OFLOXACIN,RIFAMPICIN,STREPTOMYCIN
  SRR671864       ,CAPREOMYCIN,ETHAMBUTOL,ISONIAZID,KANAMYCIN,OFLOXACIN,RIFAMPICIN
  </code></pre>
  
  <p>Which is not correct for Pre-XDR.</p>
  
  <p>So I'm looking for a the drugs if match than look for resistant and susceptible if these criteria match with any of the mention type (MDR,XDR...) it put those under that in a new file. Can anyone help? 
  my file <a href="https://drive.google.com/open?id=1TOYNzcwzoBdgpUCA3d45mg2VTCRa4zyB" rel="nofollow noreferrer">druglist.sxls</a></p>
  
  <p>MDR eg from the file attached is:</p>
  
  <pre><code>ERR038736   ISONIAZID   RESISTANT
  ERR038736   RIFAMPICIN  RESISTANT
  ERR038737   ISONIAZID   RESISTANT
  ERR038737   RIFAMPICIN  RESISTANT
  SRR1270146  AMIKACIN    SUSCEPTIBLE 
  SRR1270146  CAPREOMYCIN SUSCEPTIBLE 
  SRR1270146  CYCLOSERINE SUSCEPTIBLE 
  SRR1270146  ETHAMBUTOL  RESISTANT   
  SRR1270146  ETHIONAMIDE SUSCEPTIBLE 
  SRR1270146  ISONIAZID   RESISTANT   
  SRR1270146  KANAMYCIN   SUSCEPTIBLE 
  SRR1270146  PARA-AMINOSALICYLIC ACID    SUSCEPTIBLE 
  SRR1270146  PYRAZINAMIDE    RESISTANT   
  SRR1270146  RIFAMPICIN  RESISTANT   
  SRR1270146  STREPTOMYCIN    RESISTANT
  </code></pre>
   298: <p>Since this involves combining multiple conditions, it is easier (and certainly more readable if you ever need to come back to it later) to write a script to do this. For example, in perl:</p>
  
  <pre class="lang-perl prettyprint-override"><code>#!/usr/bin/perl
  
  use strict;
  use warnings;
  
  ## This will hold the information for each strain
  my %strains;
  
  ## These are the functions that will test each strain
  sub isMDR{
    my <span class="math-container">$_strain = shift;
    my %thisStrain = %{$strains{$_strain}};
    if ($</span>thisStrain{"ISONIAZID"} &amp;&amp; <span class="math-container">$thisStrain{"RIFAMPICIN"}){
      foreach my $drug (keys(%thisStrain)) {
        next if $drug eq "ISONIAZID";
        next if $drug eq "RIFAMPICIN";
        next if $drug eq "PYRAZINAMID";
        next if $drug eq "ETHAMBUTOL";
        next if $drug eq "STREPTOMYCIN";
        ## discard this strain if it is resistant to any other drug
        if ($thisStrain{$</span>drug}) {
          return 0;
        }
      }
  
      return 1;
    }
    return 0;
  }
  
  sub isPreXDR{
    my <span class="math-container">$_strain = shift;
    my %thisStrain = %{$strains{$_strain}};
    if (
    ($</span>thisStrain{"ISONIAZID"} &amp;&amp; <span class="math-container">$thisStrain{"RIFAMPICIN"}) &amp;&amp;
    (
     $</span>thisStrain{"CIPROFLOXACIN"}  ||
         <span class="math-container">$thisStrain{"MOXIFLOXACIN"} ||
     $</span>thisStrain{"OFLOXACIN"}  ||
         $thisStrain{"LEVOFLOXACIN"}
        )
       ) {
      return 1;
    }
    return 0;
  }
  
  sub isXDR{
    my <span class="math-container">$_strain = shift;
    my %thisStrain = %{$strains{$_strain}};
    if (
    isPreXDR($</span>_strain) &amp;&amp;
        (
         <span class="math-container">$thisStrain{"AMYCACIN"} ||
     $</span>thisStrain{"CAPREOMYCIN"} ||
         $thisStrain{"KANAMYCIN"}
        )
       ){
      return 1;
    }
    return 0;
  }
  
  sub isSusceptible{
    my <span class="math-container">$_strain = shift;
    foreach my $</span>drug (keys(%{<span class="math-container">$strains{$_strain}})) {
  if ($strains{$_strain}{$</span>drug}) {
        return 0
      }
    }
    return 1
  }
  
  ## Iterate over each line in the input file.
  ## For each iteration, the special variable <span class="math-container">$_
  # will hold the contents of the line.
  while (&lt;&gt;) {
    ## Skip first line
    next if $. == 1;
    ## remove Windows-style line endings
    s/\r//;
    ## remove trailing newline
    chomp;
    ## split the line on commas
    my ($strain, $drug, $status) = split(/,/);
    if ($status eq "SUSCEPTIBLE"){
  $status = 0
    }
    else {
  $status = 1;
    } 
    $strains{$strain}{$drug}=$</span>status;
  }
  
  foreach my <span class="math-container">$strain (keys(%strains)) {
    print "$strain\t";
    if (isMDR($strain)) {
  print "MDR\n";
    }
    elsif (isXDR($strain)) {
  print "XDR\n"
    }
    elsif (isPreXDR($strain)) {
  print "PRE-XDR\n"
    }
    elsif (isSusceptible($</span>strain)) {
      print "SUSCEPTIBLE\n";
    }
    else {
      print "OTHER\n";
    }
  }
  </code></pre>
  
  <p>If you save that script as <code>foo.pl</code>, you can run it on your file with:</p>
  
  <pre><code>perl foo.pl drug.csv
  </code></pre>
  
  <p>Selected output from running this on the <code>drug.csv</code> file you posted is:</p>
  
  <pre><code>$ foo.pl drug.csv | grep -E 'ERR038288|ERR038736|ERR038751|SRS553990|ERR133879'
  ERR038751   OTHER
  ERR038288   SUSCEPTIBLE
  SRS553990   XDR
  ERR038736   MDR
  ERR133879   PRE-XDR
  </code></pre>
  
  <hr>
  
  <p>Various details about this question were added in chat: <a href="https://chat.stackexchange.com/transcript/message/47821646#47821646">https://chat.stackexchange.com/transcript/message/47821646#47821646</a></p>
  