Reshaping long to wide with duplicates identifiers

[Meshal AlQhtani]

Hell Statlaist members,

below is a sample of my data

input long id str36 organism str29 antib str1 sensitivity
id organism antib sensitive
373380 "Pseudomonas aeruginosa" "Amikacin" "S"
373380 "Pseudomonas aeruginosa" "Cefepime" "S"
373380 "Pseudomonas aeruginosa" "Ceftazidime" "S"
373380 "Pseudomonas aeruginosa" "Ciprofloxacin" "S"
373380 "Pseudomonas aeruginosa" "Gentamicin" "S"
373380 "Pseudomonas aeruginosa" "Imipenem" "S"
373380 "Pseudomonas aeruginosa" "Levofloxacin" "S"
373380 "Pseudomonas aeruginosa" "Meropenem" "S"
373380 "Pseudomonas aeruginosa" "Pipercillin/Tazobactam" "S"
373380 "Pseudomonas aeruginosa" "Tobramycin" "S"
373380 "Pseudomonas aeruginosa" "Trimethoprim/Sulfamethoxazole" "R"
373380 "klebsiella pneumonia ESBL Positive" "Amikacin" "S"
373380 "klebsiella pneumonia ESBL Positive" "Amoxicillin" "R"
373380 "klebsiella pneumonia ESBL Positive" "Amoxicillin/Clavulanic Acid" "R"
373380 "klebsiella pneumonia ESBL Positive" "Ampicillin" "R"
373380 "klebsiella pneumonia ESBL Positive" "Cefazolin" "R"
373380 "klebsiella pneumonia ESBL Positive" "Cefepime" "R"
373380 "klebsiella pneumonia ESBL Positive" "Cefotaxime" "R"
373380 "klebsiella pneumonia ESBL Positive" "Ceftazidime" "R"
373380 "klebsiella pneumonia ESBL Positive" "Ceftriaxone" "R"
373380 "klebsiella pneumonia ESBL Positive" "Cefuroxime Sodium" "R"
373380 "klebsiella pneumonia ESBL Positive" "Ciprofloxacin" "S"
373380 "klebsiella pneumonia ESBL Positive" "Gentamicin" "S"
373380 "klebsiella pneumonia ESBL Positive" "Imipenem" "S"
373380 "klebsiella pneumonia ESBL Positive" "Meropenem" "S"
373380 "klebsiella pneumonia ESBL Positive" "Nitrofurantoin" "R"
373380 "klebsiella pneumonia ESBL Positive" "Pipercillin/Tazobactam" "R"
373380 "klebsiella pneumonia ESBL Positive" "Tobramycin" "S"
373380 "klebsiella pneumonia ESBL Positive" "Trimethoprim/Sulfamethoxazole" "S"
208306 "Streptococcus agalactiae" "Amoxicillin" "S"
208306 "Streptococcus agalactiae" "Ceftriaxone" "S"
208306 "Streptococcus agalactiae" "Clindamycin" "R"
208306 "Streptococcus agalactiae" "Erythromycin" "R"
208306 "Streptococcus agalactiae" "Levofloxacin" "S"
208306 "Streptococcus agalactiae" "Linezolid" "S"
208306 "Streptococcus agalactiae" "Penicillin G" "S"
208306 "Streptococcus agalactiae" "Vancomycin" "S"
23443 "Escherichia coli" "Amikacin" "S"
23443 "Escherichia coli" "Amoxicillin" "S"
23443 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S"
23443 "Escherichia coli" "Cefazolin" "S"
23443 "Escherichia coli" "Cefixime" "S"
23443 "Escherichia coli" "Cefotaxime" "S"
23443 "Escherichia coli" "Ceftazidime" "S"
23443 "Escherichia coli" "Ceftriaxone" "S"
23443 "Escherichia coli" "Cefuroxime Sodium" "S"
23443 "Escherichia coli" "Ciprofloxacin" "S"
23443 "Escherichia coli" "Gentamicin" "S"
23443 "Escherichia coli" "Nitrofurantoin" "S"
23443 "Escherichia coli" "Pipercillin/Tazobactam" "S"
23443 "Escherichia coli" "Trimethoprim/Sulfamethoxazole" "S"
57262 "Klebsiella pneumoniae" "Amikacin" "S"
57262 "Klebsiella pneumoniae" "Amoxicillin" "R"
57262 "Klebsiella pneumoniae" "Amoxicillin/Clavulanic Acid" "S"
57262 "Klebsiella pneumoniae" "Ampicillin" "R"
57262 "Klebsiella pneumoniae" "Cefazolin" "R"
57262 "Klebsiella pneumoniae" "Cefepime" "S"
57262 "Klebsiella pneumoniae" "Cefotaxime" "S"
57262 "Klebsiella pneumoniae" "Ceftazidime" "S"
57262 "Klebsiella pneumoniae" "Ceftriaxone" "S"
57262 "Klebsiella pneumoniae" "Cefuroxime Sodium" "S"
57262 "Klebsiella pneumoniae" "Ciprofloxacin" "S"
57262 "Klebsiella pneumoniae" "Gentamicin" "S"
57262 "Klebsiella pneumoniae" "Imipenem" "S"
57262 "Klebsiella pneumoniae" "Meropenem" "S"
57262 "Klebsiella pneumoniae" "Nitrofurantoin" "I"
57262 "Klebsiella pneumoniae" "Pipercillin/Tazobactam" "S"
57262 "Klebsiella pneumoniae" "Trimethoprim/Sulfamethoxazole" "S"
50158 "Escherichia coli" "Amikacin" "S"
50158 "Escherichia coli" "Amoxicillin" "S"
50158 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S"
50158 "Escherichia coli" "Cefazolin" "S"
50158 "Escherichia coli" "Cefixime" "S"
50158 "Escherichia coli" "Cefotaxime" "S"
50158 "Escherichia coli" "Ceftazidime" "S"
50158 "Escherichia coli" "Ceftriaxone" "S"
50158 "Escherichia coli" "Cefuroxime Sodium" "S"
50158 "Escherichia coli" "Ciprofloxacin" "S"
50158 "Escherichia coli" "Gentamicin" "S"
50158 "Escherichia coli" "Imipenem" "S"
50158 "Escherichia coli" "Meropenem" "S"
50158 "Escherichia coli" "Nitrofurantoin" "S"
50158 "Escherichia coli" "Pipercillin/Tazobactam" "S"
50158 "Escherichia coli" "Trimethoprim/Sulfamethoxazole" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Amikacin" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Amoxicillin" "R"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Cefazolin" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Cefepime" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Cefotaxime" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Ceftazidime" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Ceftriaxone" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Cefuroxime Sodium" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Ciprofloxacin" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Gentamicin" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Imipenem" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Meropenem" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Nitrofurantoin" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Pipercillin/Tazobactam" "S"
386399 "Klebsiella pneumoniae ssp pneumoniae" "Trimethoprim/Sulfamethoxazole" "S"


What I am trying to do is to have rows with organisms with their antiob and reactions.
I tried to use the reshape wide command but it can not run since there are duplicates. anyone can have an idea how to solve this?

Many thanks,
Meshal

[Leonardo Guizzetti]

What happens next depends on how you should handle rows with duplicate ID values. Are they important? if those rows are complete duplicates, you can safely drop them. then some special handling is appropriate, but you need to tell us what that is.

[Clyde Schechter]

I agree with Leonardo Guizzetti in #2.

But based on my clinical experience with antibiotic sensitivity reports from clinical labs, I think I know what is going on in your data. I think each ID represents a lab report from a specimen. The ID's therefore encompass, potentially, multiple organisms isolated, and will usually include several different antibiotic sensitivity tests on each organism isolated. Crucially, there will never be two observations in your data that have the same values for id, organism, and antib. This is true in your example data. The code below verifies that it is true in your full data before proceeding:

isid id organism antib

sort id organism, stable
by id organism: gen int seq = _n
reshape wide antib sensitivity, i(id organism) j(seq)

I think this is what you are looking for. If not, please post back with a hand-crafted table illustrating what is actually wanted.

[Meshal AlQhtani]

I agree with Leonardo Guizzetti in #2.

But based on my clinical experience with antibiotic sensitivity reports from clinical labs, I think I know what is going on in your data. I think each ID represents a lab report from a specimen. The ID's therefore encompass, potentially, multiple organisms isolated, and will usually include several different antibiotic sensitivity tests on each organism isolated. Crucially, there will never be two observations in your data that have the same values for id, organism, and antib. This is true in your example data. The code below verifies that it is true in your full data before proceeding:

isid id organism antib

sort id organism, stable
by id organism: gen int seq = _n
reshape wide antib sensitivity, i(id organism) j(seq)

I think this is what you are looking for. If not, please post back with a hand-crafted table illustrating what is actually ,wanted.

Thank you Mr. Leonardo Guizzetti and Mr. Clyde Schechter



Regarding the IDs, they are not important.

Yes, Mr. Clyde, that is my scenario with lab data as you specifically described!


However, as you can see below it has not worked yet

reshape wide antib sensitivity, i(id organism) j(seq)

(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22)
values of variable seq not unique within id organism
Your data are currently long. You are performing a reshape wide. You specified i(id organism) and j(seq). There are observations within i(id organism) with the same value of
j(seq). In the long data, variables i() and j() together must uniquely identify the observations.

[Nick Cox]

please post back with a hand-crafted table illustrating what is actually wantee

remains the key question.

[Clyde Schechter]

The code worked properly in your example data. And it is difficult for me to imagine how it failed in the real data. The variable seq was created by:

by id organism: gen int seq = _n

which guarantees that the values of seq will be unique within combinations of id and organism, unless there is some id and organism that has a number of antibiotics tested exceeds the largest value of an int. But the int data type can go up to 32,740 and I doubt there are even that many antibiotics existing in the world, nor, even there are, that any lab report would include so many. Moreover, your -reshape- output even shows the values only range from 1 to 22. So I think that when you ran the first line -isid id organism antib-, Stata gave you an error message, but you blundered on anyway. Am I right? In any case, please post back with example data that reproduces the problem you are having here.

On the assumption that I am right that -isid id organism antib- produced an error message, I have to also ask you how you want this situation to be handled. Suppose a report has the same organism tested with the same antibiotic twice, with contradictory results, what is to be done with that?

Also, on the assumption that I am right that -isid id organism antib- produced an error message, please take to heart the general lesson: error messages are there to protect you. They are your friends, not obstacles to be gotten around. When a command gives you an error message, you should never proceed with the rest of the code, because the presence of an error message tells you that the state of the data and the program are such that subsequent results cannot be relied upon. In the rare circumstance where you can be absolutely certain that the particular error encountered is of no importance to what you are doing, you might proceed cautiously. But even then, it would be better to stop, investigate the source of the error, and fix it, because you wouldn't see any error messages unless something went wrong.

Added: Crossed with #5.

[Meshal AlQhtani]

remains the key question.

For Mr. Nick Cox

This is the ultimate table to have after reshaping

Pathogen	Guidelines	Ampicillin	Azithromycin	Cefepime	Cefoxitin	Ceftriaxone	Cefotaxime	Ceftazidime
Escherichia coli	CLSI	Resistant		Resistant			Resistant	Resistant
Escherichia coli	CLSI	Susceptible		Susceptible		Susceptible		Susceptible
Escherichia coli	CLSI	Resistant		Susceptible			Intermediate	Resistant
Escherichia coli	CLSI	Resistant		Resistant			Resistant	Resistant
Neisseria gonorrhoeae	CLSI					Susceptible
Neisseria gonorrhoeae	CLSI					Susceptible
Neisseria gonorrhoeae	CLSI					Susceptible
Salmonella spp.	CLSI							Susceptible
Salmonella spp.	CLSI					Susceptible		Susceptible
Salmonella spp.	CLSI						Susceptible	Susceptible
Salmonella spp.	CLSI						Susceptible	Susceptible
Acinetobacter baumannii	CLSI
Acinetobacter baumannii	CLSI
Klebsiella pneumoniae	CLSI	Resistant		Intermediate		Resistant		Intermediate
Klebsiella pneumoniae	CLSI			Susceptible			Susceptible	Susceptible

Regarding to Mr. Clyde Schechter

my apology, I re-ran it, and worked!


but based on the table above I am not if it is possible to be eventually.


below is the data after Mr. Clyde Schechter's Codes. Many thanks !!

[Nick Cox]

In the example data there are 6 pathogens and 25 antibiotics and the good news is that sensitivity is constant in the example data within cross-combinations. That's probably not a surprise. Except that your table in #7 contradicts that.

That seems to call for some kind of table. 25 columns is usually a lot to manage.

[Clyde Schechter]

Here is how to get (almost) the table you show in #7:

duplicates drop

//    IDENTIFY ANY REPORTS THAT PROVIDE CONTRADICTORY INFORMATION ABOUT
//    THE SENSITIVITY OF ANY ORGANISM TO ANY ANTIBIOTIC
//    AND "QUARANTINE" THOSE REPORTS INTO A SEPARATE FRAME
by id organism antib (sensitivity), sort: gen byte contradiction = ///
    sensitivity[1] != sensitivity[_N]
frame put _all if contradiction, into(problems)
drop if contradiction
drop contradiction

rename sensitivity s
replace antib = strtoname(antib)
reshape wide s, i(id organism) j(antib) string
rename s* *

This differs slightly from what you show in a few ways:

1. There is no guidelines variable. Guidelines does not appear in your example data set, and I don't know where it comes from or anything about it.
2. I have preserved the original report id, even though you discard it. You might need this in order to cross check your work against the original source data.
3. Some of the antibiotic names contain embedded spaces, and therefore cannot serve as legal Stata variable names. In these instances, an underscore ("_") has been substituted for the space. You might also encounter a problem in your full data set where some antibiotic name exceeds 31 characters in length. If that happens, you will have to add a line of code before the -reshape- to abbreviate that name in some way.
4. I have anticipated the possibility that the same lab report will mention the same organism-antibiotic combination more than once and provide contradictory results. If that happens, this code isolates those observations in a separate frame, called problems, where you can look at them and figure out what to do with them. They do not appear in the table in the original default frame as their information is internally inconsistent. To be clear, this process does not concern itself with the same pathogen having contradictory antibiotic sensitivity if isolated in different specimens (i.e. different id)--that is a normal situation, not a problem. But if the same specimen mentions the same organism and antibiotic twice (or more) with different sensitivities, that is contradictory information that should not be just passed along to a final table.

[Meshal AlQhtani]

I truly appreciated your help Mr. Clyde Schechter !

for Mr. Nick Cox reply, the table in #7 (guideline) is not necessary for my data and the table is a template that I am trying to make my data look like it at the end.


for Mr. Clyde reply:
1. as I said Guideline variable is not necessary to add or consider.
2. I'll do it again for checking as you siad.
3. for the antib variable, yes there are some of them with embedded spaces but I re coded them with abbreviations for the sake of chars exceeding limit as you mentioned.
4. I am not sure for the contradictions since I ran the code you wrote and attached screen shot photo with the error appear.

many thanks again !

[Clyde Schechter]

4. I am not sure for the contradictions since I ran the code you wrote and attached screen shot photo with the error appear.

The message "antib ambiguous abbreviation" means exactly what it says. It means that in the data set you are starting from, unlike the example data you have posted, there is no variable whose actual name is antib, and there are at least two variables whose names begin with the five characters antib. The code in #9 works without error messages, and produces the kind of output you are looking for, starting from the example data you showed in #1. Evidently, your actual data set is materially different in structure, so the code is not working there.

So, you need to rename some variables in your actual data set so that it is just like the example data you showed in #1. If you are unsure how best to do that, then instead post back with a new data example, one that exhibits the same problem you are encountering, and I will adapt the code to work with it.

[Nick Cox]

#8 #10 The contradiction in #7 referred to is that there are some different sensitivities for some (pathogen, antibiotic) pairs I wasn't concerned about CSLI. Sorry that wasn't clear to you.

[Meshal AlQhtani]

Hello Mr. Clyde and Mr. Nick

I know it's been a while to post back here, but I was trying to follow what Mr. Clyde instructions in #11 but I faced the same issue "antib ambiguous abbreviation" even though I restructured the dataset like #1.

below is the last structure of the data that was reshaped using #4, thanks to Mr. Clyde's codes.


input long id str36 organism str27 antib1 str1 sensitivity1 str27 antib2 str1 sensitivity2 str29 antib3 str1 sensitivity3
201 "Escherichia coli" "Ampicillin" "S" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
362 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
362 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
485 "Klebsiella pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
609 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "S" "Ciprofloxacin" "R"
847 "Escherichia coli" "Ampicillin" "S" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
883 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
887 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
958 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "S" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
1275 "Escherichia coli" "Ampicillin" "S" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
1471 "Pseudomonas aeruginosa" "Ceftazidime" "R" "Ciprofloxacin" "S" "Ceftriaxone" "R"
1471 "Pseudomonas aeruginosa" "Ceftazidime" "S" "Ciprofloxacin" "R" "Gentamicin" "S"
1517 "Escherichia coli" "Ampicillin" "S" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
1517 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
1716 "Klebsiella pneumoniae ssp pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
1905 "Staphylococcus aureus" "Ciprofloxacin" "S" "Cefotaxime" "S" "Gentamicin" "S"
2052 "Pseudomonas aeruginosa" "Ceftazidime" "S" "Ciprofloxacin" "R" "Cefepime" "S"
2052 "Pseudomonas aeruginosa" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "R"
2182 "Pseudomonas aeruginosa" "Ceftazidime" "S" "Ciprofloxacin" "S" "Gentamicin" "S"
2203 "Klebsiella pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
2400 "Staphylococcus aureus" "Ciprofloxacin" "R" "Gentamicin" "S" "Levofloxacin" "R"
2400 "Staphylococcus aureus" "Ciprofloxacin" "R" "Gentamicin" "S" "Levofloxacin" "R"
2480 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
2605 "Klebsiella pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
2687 "Klebsiella pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
2687 "Klebsiella pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
2706 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
2935 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
2982 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
3045 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
3373 "Klebsiella pneumoniae ssp pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
3562 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "I" "Ceftazidime" "S"
3633 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "I" "Ceftazidime" "S"
3823 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
3823 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
3823 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
3956 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
3987 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
3987 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
4204 "Klebsiella pneumoniae ssp pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
4204 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Ceftazidime" "S" "Ciprofloxacin" "S"
4291 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "S"
4576 "Staphylococcus aureus" "Ciprofloxacin" "S" "Gentamicin" "S" "Levofloxacin" "S"
4941 "klebsiella pneumonia ESBL Positive" "Ampicillin" "R" "Ceftazidime" "R" "Ciprofloxacin" "S"
4941 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
5006 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
5120 "Klebsiella pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
5228 "Klebsiella pneumoniae" "Ampicillin" "R" "Ceftazidime" "S" "Ciprofloxacin" "R"
5228 "Klebsiella pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
5228 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
5821 "Staphylococcus aureus" "Ciprofloxacin" "S" "Gentamicin" "S" "Levofloxacin" "S"
6038 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
6116 "Escherichia coli" "Ampicillin" "S" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
6154 "Staphylococcus aureus" "Ciprofloxacin" "S" "Gentamicin" "S" "Levofloxacin" "S"
6169 "Klebsiella pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
6412 "Klebsiella pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
6412 "Klebsiella pneumoniae ssp pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
6412 "Acinetobacter baumannii" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "R" "Ciprofloxacin" "R"
6412 "klebsiella pneumonia ESBL Positive" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "R"
6412 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "S"
6648 "Escherichia coli" "Ampicillin" "S" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
6917 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
6949 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "I"
7026 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
7034 "Escherichia coli" "Ampicillin" "S" "Ceftazidime" "S" "Ciprofloxacin" "R"
7202 "Escherichia coli" "Ampicillin" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
7248 "Pseudomonas aeruginosa" "Ceftazidime" "S" "Ciprofloxacin" "S" "Gentamicin" "S"
7408 "Escherichia coli" "Ampicillin" "S" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
7637 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
7887 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Ceftazidime" "S" "Ciprofloxacin" "S"
8064 "Klebsiella pneumoniae ssp pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ciprofloxacin" "S" "Ceftriaxone" "S"
8151 "Staphylococcus aureus" "Ciprofloxacin" "S" "Gentamicin" "S" "Oxacillin" "S"
8295 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
8295 "Pseudomonas aeruginosa" "Ceftazidime" "S" "Ciprofloxacin" "S" "Gentamicin" "S"
8295 "Pseudomonas aeruginosa" "Ceftazidime" "S" "Ciprofloxacin" "S" "Cefepime" "S"
8481 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "I" "Ceftazidime" "S"
8481 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
8737 "Klebsiella pneumoniae ssp pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
8749 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
8972 "Pseudomonas aeruginosa" "Ceftazidime" "S" "Ciprofloxacin" "S" "Cefepime" "S"
9277 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "S" "Ciprofloxacin" "S"
9498 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "S"
9512 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "R"
10152 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
10265 "Klebsiella pneumoniae ssp pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
10622 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
10689 "Acinetobacter baumannii" "Ceftazidime" "S" "Gentamicin" "S" "Imipenem" "S"
10819 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
10819 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"
10819 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
10936 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
11069 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
11200 "Klebsiella pneumoniae" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S"
11209 "Klebsiella pneumoniae" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
11338 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Cefixime" "S"
11387 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "I" "Ceftazidime" "S"
11450 "Escherichia coli" "Ampicillin" "R" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "S"
11979 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "R" "Ceftazidime" "S" "Cefixime" "S"
11979 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "I" "Ceftazidime" "S" "Ciprofloxacin" "R"
12172 "Escherichia coli" "Amoxicillin/Clavulanic Acid" "S" "Ceftazidime" "S" "Ciprofloxacin" "S"

I am trying to use the codes in #9, but as I showed you in #10, it's not working.

I really appreciate any inputs or suggestions.

Many thanks,
Meshal

[Clyde Schechter]

This data set is very messy and I don't really understand how it got that way. But it can be cleaned up enough to rearrange in the way you seek. However, I don't really know what to make of the results. You have this variable, id, but there are sometimes multiple observations with the same id, and sometimes those observations contradict each other. So I don't know what this id variable represents, if anything. I'm keeping it in the data, but I just want to emphasize that it seems quite problematic.

duplicates drop
gen `c(obs_t)' obs_no = _n
reshape long antib sensitivity, i(obs_no)
rename sensitivity s
drop _j
replace antib = strtoname(antib)
drop if antib == "_"
isid obs_no antib
reshape wide s, i(obs_no) j(antib) string
rename s* *
order id organism, after(obs_no)

[Meshal AlQhtani]

My apology Mr. Clyde for the confusion of the arrangement of data on the last post. And Thank you Mr. Clyde , the code worked perfectly!

Again many thanks!