How to separate a microbial strain of MTB resistant and susceptible drugwise? Im trying to classify my list into three type: XDR - A strain must be resistant to:

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)


PRE-XDR - A strain must be resistant to:

MDR + CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN -&gt; 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
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"} &amp;&amp; $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"} &amp;&amp; $thisStrain{"RIFAMPICIN"}) &amp;&amp;
  (
   $thisStrain{"CIPROFLOXACIN"}  ||
       $thisStrain{"MOXIFLOXACIN"} ||
   $thisStrain{"OFLOXACIN"}  ||
       $thisStrain{"LEVOFLOXACIN"}
      )
     ) {
    return 1;
  }
  return 0;
}

sub isXDR{
  my $_strain = shift;
  my %thisStrain = %{$strains{$_strain}};
  if (
  isPreXDR($_strain) &amp;&amp;
      (
       $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 (&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}=$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