Reorganizing rows and columns in excel

[tabihussain]

Hi there,
I really hope someone has a solution for this problem. I want to analyze data from a clinical lab in SPSS. The data I have has several thousand samples, so I want an easy way of transferring it in SPSS for analysis. Here is a screenshot of the data. Now the problem is against one patient, efficacy to a panel of drugs is tested and between patients, the panel of drugs that is tested can also vary. The data is pasted below. The left most column is the sample ID number as you can see the same ID number prevails for about 13 cases, so this is one patient's result. My problem is I want to export this data in spss in a way that all the drugs (Amikacin, Ampicillin etc etc) become variables (columns), each with values 1,0 etc for one patient, so that all data for one ID number/patient is limited to one row only. Is there any way to reorganize this data? If not a shortcut, at least less tedious than manually copying all rows for one ID? I would want the conversion to somewhat like the second table.
I would really appreciate any ideas.
Best Regards
Tabi

14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Amikacin	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Amoxicillin + Clavulanic acid	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Ampicillin	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Ceftriaxone	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Ciprofloxacin	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Cotrimoxazole	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Fosfomycin	S
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Gentamicin	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Imipenem	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Meropenem	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Nitrofurantoin	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Piperacillin+Tazobactam	R
14423230	05:50.7	***	M	80	Y	Urine For C/S	Klebsiella pneumoniae	Sulzone	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Doxycycline	S
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Gentamicin	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Meropenem	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Amikacin	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Polymyxin B	S
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Cotrimoxazole	S
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Tigecycline	I
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Amoxicillin + Clavulanic acid	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Ceftriaxone	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Imipenem	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Ampicillin	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Ciprofloxacin	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Sulzone	R
14423279	40:50.3	***	M	55	Y	NBL for C/S	Klebsiella pneumoniae	Piperacillin+Tazobactam	R
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Cotrimoxazole	R
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Ceftriaxone	R
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Piperacillin+Tazobactam	S
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Amikacin	I
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Amoxicillin + Clavulanic acid	R
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Ciprofloxacin	R
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Tigecycline	S
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Sulzone	R
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Doxycycline	R
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Gentamicin	R
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Imipenem	S
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Meropenem	S
14423316	21:01.9	***	M	60	Y	Fluid For C/S	Klebsiella pneumoniae	Ampicillin	R
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Tigecycline	S
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Doxycycline	R
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Ciprofloxacin	R
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Gentamicin	R
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Amikacin	S
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Piperacillin+Tazobactam	S
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Meropenem	S
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Amoxicillin + Clavulanic acid	R
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Ceftriaxone	R
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Sulzone	S
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Imipenem	S
14423680	36:02.2	*	M	12	D	Pus for C/S	Klebsiella oxytoca	Cotrimoxazole	S

<tbody>
</tbody>

Intended Output

ID number

Age

Gender

Isolate

Amikacin

Amoxicillin + Clavulanic acid

Ampicillin

Ceftriaxone

Cefipime

Ceftazidine

Sulzone

Tigecycline

Ciprofloxacin

Cotrimoxazole

Doxycycline

Fosfomycin

Gentamicin

Imipenem

Meropenem

Nitrofurantoin

Piperacillin+Tazobactam

Polymyxin B

14423230

80

M

Klebsiella

R

R

R

R

R

R

R

R

R

R

R

S

R

R

R

R

R

R

<colgroup><col width="64" span="22" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[tabihussain]

Hi
Sorry to bother you again but I am having some trouble with the code. I realized that the same ID number duplicates for different tests. This happens in cases where one person is infected with more than one bacteria. These entries should be made separately, perhaps by adding a,b,c at the end of the ID number. Can this be done? The total isolates that appear in the complete list have more variations than those appearing in the 8th column here.

Here is sample data, you can see first few IDs have multiple results which I want to sort w.r.t column 8.

13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Doxycycline	R
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Ciprofloxacin	R
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Gentamicin	R
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Ampicillin	R
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Tigecycline	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Sulzone	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Amikacin	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Amoxicillin_Clavulanic acid	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Cotrimoxazole	R
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Meropenem	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Imipenem	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Ceftriaxone	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Piperacillin_Tazobactam	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Piperacillin_Tazobactam	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Amikacin	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Gentamicin	R
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Sulzone	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Ceftazidime	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Aztreonam	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Cefepime	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Imipenem	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Meropenem	S
13040745	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Ciprofloxacin	R
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Imipenem	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Sulzone	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Ampicillin	R
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Gentamicin	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Meropenem	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Tigecycline	I
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Ciprofloxacin	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Amikacin	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Amoxicillin_Clavulanic acid	R
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Cotrimoxazole	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Doxycycline	R
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Klebsiella pneumoniae	Piperacillin_Tazobactam	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Amikacin	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Ciprofloxacin	R
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Gentamicin	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Imipenem	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Cefepime	R
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Meropenem	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Aztreonam	R
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Ceftazidime	R
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Sulzone	S
13043130	32:57.1	Fazal Subhan	M	55	Y	Pus for C/S	Pseudomonas aeruginosa	Piperacillin_Tazobactam	S
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Ciprofloxacin	R
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Amikacin	S
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Ceftriaxone	R
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Cotrimoxazole	R
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Tigecycline	S
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Imipenem	S
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Amoxicillin_Clavulanic acid	R
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Piperacillin_Tazobactam	S
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Sulzone	S
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Gentamicin	R
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Ampicillin	R
13009399	44:16.0	***	F	72	Y	Pus for C/S	Escherichia coli	Meropenem	S

<tbody>
</tbody>

So what I need is that whenever this column's result is different for the same ID number, the output should change the ID number to a new value, say 1304075a and 1304075b instead of 1304075.

13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Doxycycline	R
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Ciprofloxacin	R
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Gentamicin	R
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Ampicillin	R
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Tigecycline	S
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Sulzone	S
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Amikacin	S
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Amoxicillin_Clavulanic acid	S
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Cotrimoxazole	R
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Meropenem	S
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Imipenem	S
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Ceftriaxone	S
13040745a	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Escherichia coli	Piperacillin_Tazobactam	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Piperacillin_Tazobactam	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Amikacin	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Gentamicin	R
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Sulzone	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Ceftazidime	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Aztreonam	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Cefepime	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Imipenem	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Meropenem	S
13040745b	26:27.4	Hameeda Begum	F	65	Y	Pus for C/S	Pseudomonas aeruginosa	Ciprofloxacin	R
									S

<tbody>
</tbody>

I would like this code to run on the raw data and give output with only different ID numbers, then I will run the code for extracting drug information later. I know this is an undue favor to ask but I have a project deadline. I tried to do it manually but there are hundreds of duplicates so that is hard.
Thankyou soo much
Regards
Tabi