I have a data containing bacteria strain id and its resistant or susceptible nature against certain drugs i want to separate them on basis of their resistant to drugs in 4 types which includes:
- MDR
- Pre-XDR
- XDR
- Susceptible
1- MDR means: it is resistant to-> Isoniazid and Rifampicin only
2- Pre-XDR means: its MDR plus Also Resistant to any of these or combination or one or two or all of these: CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN -> i.e: (out of CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN it it is resistant to any one or two or all + it is resistant to ISONIAZID and RIFAMPICIN also)
3- XDR means: MDR + Pre-XDR + AMYCACIN/CAPREOMYCIN/KANAMYCIN -> again here same follows it is resistant to ISONIAZID + RIFAMPICIN + it is resistant to any of these one or two or three or all (CIPROFLOXACIN/MOXIFLOXACIN/OFLOXACIN/LEVOFLOXACIN) + it is resistant to any of these one or two or all (AMYCACIN/CAPREOMYCIN/KANAMYCIN)
4- Susceptible means :It is Susceptible to all drugs.
My data is like this: It has 3 columns:
StrainID - Drug - StrainType
The file is attached as drug.csv
Any help with the formula to make new Four column with the about mentioned four types having the IDS only from first column if match the above mentioned conditions?
