PRO-ACT - Data Cleaning

Merging test names

Some tests had different names due to different trials and typing errors. These were merged and typing errors were assessed using the trial information and the units of measure.

Before	After
'Bilirubin (direct)'	'Bilirubin (Direct)'
'Hepatitis A IGG'	'Hepatitis A Antibody'
'Hep B Surface Antigen'	'Hepatitis B Antigen'
'Hep A Antibody (IgG)'	'Hepatitis A Antibody'
'AC HEPATITE B (HBG)'	'Hepatitis B Antibody'
'Hep B Core Antibody'	'Hepatitis B Antibody'
'Hepatitis A IGM'	'Hepatitis A Antibody'
'HBS'	'Hepatitis B Antibody'
'Hep C Antibody'	'Hepatitis C Antibody'
'Absolute Band Neutrophils'	'Absolute Band Neutrophil Count'
'Hep B Surface Antibody'	'Hepatitis B Antibody'
'Activated Partial Thromboplastin Time'	'Partial Thromboplastin Time'
'HB'	'Hemoglobin'
'Absolute Segmented Neutrophils'	'Absolute Segmented Neutrophil Count'
'Absolute Monocytes Count'	'Absolute Monocyte Count'
'Absolute Total Neutrophils'	'Absolute Neutrophil Count'
'BUN'	'Blood Urea Nitrogen (BUN)'
'Arterial Ph'	'Plasma Ph'
'Immuno Electrophoresis'	'Immunofixation'
'CO2'	'Bicarbonate'
'Creatine'	'Creatinine'
'Red Blood Cells'	'Red Blood Cells (RBC)'
'Atypical Lymphocyte Count'	'Absolute Atypical Lymphocyte count'
'Cholesterol'	'Total Cholesterol'
'GGT'	'Gamma-glutamyltransferase'
'Urine Mucous'	'Urine Mucus'
'Microalbumin-24hr Urine'	'Urine albumin'
'Urea'	'Blood Urea Nitrogen (BUN)'
'Transferrin saturation'	'Transferrin Saturation'
'PROTEIN - 24 HR. URINE'	'Urine Protein'
'sodium'	'Sodium'
'Transferrin'(if unit was %)	'Transferrin Saturation'
'IgG'	'IMMUNOGLOBULIN G'
'T4'	'Total T4'
'T3'	'Total T3'
'albumin/globulin ratio'	'Albumin/globulin ratio'
'Alanine Aminotransferase (ALT)'	'ALT(SGPT)'
'Albumin-BCG'	'Albumin'
'ALT'	'ALT(SGPT)'
'Amorphous Crystals'	'Urine Amorphous Crystals'
'Aspartate Aminotransferase (AST)'	'AST(SGOT)'
'AST'	'AST(SGOT)'
'Bands'	'Absolute Band Neutrophil Count'
'Bands (%)'	'Band Neutrophils'
'BANDS, %'	'Band Neutrophils'
'BANDS, ABSOLUTE'	'Absolute Band Neutrophil Count'
'Baseline eGFR'	'Baseline Estimated Glomerular Filtration Rate'
'Basophils'	'Absolute Basophil Count'
'Basophils (%)'	'Basophils'
'Basophils (Absolute Ct)'	'Absolute Basophil Count'
'BASOPHILS, %'	'Basophils'
'BASOPHILS, ABSOLUTE'	'Absolute Basophil Count'
'B-hCG, Qualitative'	'Beta HCG'
'B-hCG, Quantitative'	'Beta Hcg Quantitative'
'BILIRUBIN, DIRECT'	'Bilirubin (Direct)'
'BILIRUBIN, INDIRECT'	'Bilirubin (Indirect)'
'BILIRUBIN, TOTAL'	'Bilirubin (Total)'
'Blood Urea Nitrogen'	'Blood Urea Nitrogen (BUN)'
'BUN'	'Blood Urea Nitrogen (BUN)'
'Calcium (EDTA)'	'Calcium'
'Calcium Carbonate Crystals'	'Urine Calcium Carbonate Crystals'
'Calcium Oxalate Crystals'	'Urine Calcium Oxalate Crystals'
'Calcium Phosphate Crystals'	'Urine Calcium Phosphate Crystals'
'Cholesterol (High Performance)'	'Total Cholesterol'
'Creatine Phosphokinase (CPK)'	'Creatine Kinase'
'Creatinine (Rate Blanked)'	'Creatinine'
'Cysteine Crystals'	'Urine Cystine Crystals'
'Direct Bilirubin'	'Bilirubin (Direct)'
'eGFR CKD Cystatin C'	'eGFR CKD Cystatin C'
'Eosinophils'	'Absolute Eosinophil Count'
'Eosinophils (%)'	'Eosinophils'
'Eosinophils (Absolute Ct)'	'Absolute Eosinophil Count'
'EOSINOPHILS, %'	'Eosinophils'
'EOSINOPHILS, ABSOLUTE'	'Absolute Eosinophil Count'
'FSH'	'Follicle Stimulating Hormone'
'Gamma Glutamyl Transferase'	'Gamma-glutamyltransferase'
'GGT'	'Gamma-glutamyltransferase'
'HCG'	'Beta HCG'
'HCG, QUANT'	'Beta Hcg Quantitative'
'Indirect Bili'	'Bilirubin (Indirect)'
'Indirect Bilirubin'	'Bilirubin (Indirect)'
'LDH'	'Lactate Dehydrogenase'
'Lymphocytes'	'Absolute Lymphocyte Count'
'Lymphocytes (%)'	'Lymphocytes'
'Lymphocytes (Absolute Ct)'	'Absolute Lymphocyte Count'
'LYMPHOCYTES, %'	'Lymphocytes'
'LYMPHOCYTES, ABSOLUTE'	'Absolute Lymphocyte Count'
'MCH'	'Mean Corpuscular Hemoglobin'
'MCHC'	'Mean Corpuscular Hemoglobin Concentration'
'MCV'	'Mean Corpuscular Volume'
'Mean Corpuscular Volume'	'Mean Corpuscular Volume'
'Monocytes'	'Absolute Monocyte Count'
'Monocytes (%)'	'Monocytes'
'Monocytes (Absolute Ct)'	'Absolute Monocyte Count'
'MONOCYTES, %'	'Monocytes'
'MONOCYTES, ABSOLUTE'	'Absolute Monocyte Count'
'MPV'	'Mean Platelet Volume'
'Neutrophils'	'Absolute Neutrophil Count'
'Neutrophils (%)'	'Neutrophils'
'NEUTROPHILS, %'	'Neutrophils'
'NEUTROPHILS, ABSOLUTE'	'Absolute Neutrophil Count'
'Non Fasting Glucose'	'Glucose'
'Percent eGFR Decline'	'Estimated Glomerular Filtration Rate (Percent Decline)'
'Platelet Count'	'Platelets'
'RBC'	'Red Blood Cells (RBC)'
'RDW'	'Erythrocyte Distribution Width'
'Red Blood Cell Casts'	'RBC Casts'
'Renal Epithelial Cells'	'Urine Renal Epithelial Cells'
'Serum Bicarbonate'	'Bicarbonate'
'Serum Chloride'	'Chloride'
'Serum Cystatin C'	'Cystatin C'
'Serum Glucose'	'Glucose'
'Serum Potassium'	'Potassium'
'SERUM PREGNANCY'	'Pregnancy (serum)'
'Serum Sodium'	'Sodium'
'Serum Uric Acid'	'Uric Acid'
'Squamous Epithelial Cells'	'Urine Squamous Epithelial Cells'
'Thyroid Stimulating Hormone'	'Thyroid Stimulating Hormone'
'Total Bilirubin'	'Bilirubin (Total)'
'Total Neutrophils'	'Neutrophils'
'Total Neutrophils (Absolute Ct)'	'Absolute Neutrophil Count'
'TOTAL PROTEIN'	'Protein'
'Transitional Epithelial Cells'	'Urine Transitional Epithelial Cells'
'Triglycerides (GPO)'	'Triglycerides'
'Triple Phosphate Crystals'	'Triple Phosphate Crystals'
'TSH'	'Thyroid Stimulating Hormone'
'TSH Random'	'Thyroid Stimulating Hormone'
'Ur Ammonium Biurate Crystals'	'Ammonium Biurate Crystals'
'Ur Amorphous Crystals'	'Urine Amorphous Crystals'
'Ur Bacteria'	'Urine Bacteria'
'Ur Bilirubin'	'Urine Bilirubin'
'Ur Bilirubin Crystals'	'Urine Bilirubin Crystals'
'Ur Blood'	'Urine Blood'
'Ur Calcium Carbonate Crystals'	'Urine Calcium Carbonate Crystals'
'Ur Calcium Oxalate Crystals'	'Urine Calcium Oxalate Crystals'
'Ur Calcium Phosphate Crystals'	'Urine Calcium Phosphate Crystals'
'Ur Clarity'	'Urine Clarity'
'Ur Color'	'Urine Color'
'Ur Cystine Crystals'	'Urine Cystine Crystals'
'Ur Glucose'	'Urine Glucose'
'Ur Hyaline Casts/LPF'	'Hyaline Cast'
'Ur Ketones'	'Urine Ketones'
'Ur Leucine Crystals'	'Leucine Crystals'
'Ur Leukocyte Esterase'	'Urine Leukesterase'
'Ur Microscopic'	'URINE MICROSCOPIC'
'Ur Mucous'	'Urine Mucous'
'Ur Nitrite'	'Urine Nitrite'
'Ur pH'	'Urine Ph'
'Ur Protein'	'Urine Protein'
'Ur RBC Clumps'	'Urine RBC clumps'
'Ur RBC/HPF'	'Urine RBCs'
'Ur Renal Tubular Epi Cells'	'Tubular Epithelial Cells'
'Ur Specific Gravity'	'Urine Specific Gravity'
'Ur Sperm'	'Urine Spermatozoa'
'Ur Squamous Epithelial Cells'	'Urine Squamous Epithelial Cells'
'Ur Transitional Epi Cells'	'Urine Transitional Epithelial Cells'
'Ur Triple Phosphate Crystals'	'Urine Triple Phosphate Crystals'
'Ur Tyrosine Crystals'	'Tyrosine Crystals'
'Ur Uric Acid Crystals'	'Urine Uric Acid Crystals'
'Ur Urobilinogen'	'Urine Urobilinogen'
'Ur WBC Clumps'	'Urine WBC clumps'
'Ur WBC/HPF'	'Urine WBCs'
'Urea Nitrogen'	'Blood Urea Nitrogen (BUN)'
'Urinary Bacteria'	'Urine Bacteria'
'Urinary Bilirubin'	'Urine Bilirubin'
'Urinary Blood'	'Urine Blood'
'Urinary Color'	'Urine Color'
'Urinary Glucose'	'Urine Glucose'
'Urinary Ketones'	'Urine Ketones'
'Urinary Mucus'	'Urine Mucus'
'Urinary Nitrite'	'Urine Nitrite'
'Urinary Protein'	'Urine Protein'
'Urinary Red Blood Cells'	'Urine RBCS'
'Urinary White Blood Cells'	'Urine WBCs'
'URINE RBC'	'Urine RBCs'
'URINE WBC'	'Urine WBCs'
'Urobilinogen'	'Urine Urobilinogen'
'Waxy Casts'	'Urine Waxy Casts'
'WBC'	'White Blood Cell (WBC)'
'White Blood Cell Casts'	'Urine White Blood Cell Casts'
'White Blood Cells'	'White Blood Cell (WBC)'

In addition, in certain white blood cells (monocytes, neutrophils, etc.) the displayed unit of measure was volume, rather than percentage. In these cases names were changed to Absolute Monocyte count, Absolute Neutrophil Count, etc. respectively.

Merging units of measurements

The units of measurements were merged to overcome inconsistency with writing styles.

Before	After
'G/L'	'g/L'
'G/dL'	'g/dL'
'G/l'	'g/L'
'G/dl'	'g/dL'
'g/l'	'g/L'
'g/dl'	'g/dL'
'mg/dl'	'mg/dL'
'IU/L'	'U/L'
'MMOL/L'	'mmol/L'
'UMOL/L'	'umol/L'
'#/HPF'	'/HPF'
'#/LPF'	'/LPF'
'10^12/L'	'10E12/L'
'10^3/uL'	'10E9/L'
'10^6/uL'	'10E12/L'
'10^9/L'	'10E9/L'
'K/cu mm'	'10E9/L'
'mcIU/mL'	'mIU/L'
'mcmol/L'	'umol/L'
'mIU/mL'	'IU/L'
'pH'	' '
'uIU/mL'	'mIU/L'
'x 10^12/L'	'10E12/L'
'x 10^6/cu mm'	'10E12/L'
'x 10^9/L'	'10E9/L'
'x10^3/uL'	'10E9/L'
'x10^6/uL'	'10E12/L'

Removal of unreadable records

Records where the values were deemed unreadable were removed. These values were

Value	Action
'COMMENTS'	Removed
'M/D'	Removed
'U'	Removed
'u'	Removed
'A'	Removed
'MID'	Removed
'THE TEST HAS BE'	Removed
'MB'	Removed
NA	Removed
N.A.	Removed
ND	Removed
N.D.	Removed
'MG'	Removed
'N.N'	Removed
'INF5'	Removed
'LT1'	Removed
'MD MAN SUBJECT'	Removed

Overall 1031 records were removed (<0.1%, including all removals listed in this document).

Merging Text Values

Part I: A variety of text items indicated only the presence or lack of detection. They were merged together according to the following table:

Before	After
'(+)'	+
'(-)'	-
'NEG'	-
'NEGATIF'	-
'NEGATIVE'	-
'POSITIVE'	+
'PRESENT'	+
'-VC'	-
'MD/NA'	-
'NEGAIL'	-
'NN'	-
'DETECTED'	+
'NONE'	-
'NONE DET'	-
'Negative'	-
'Present'	+
'POS'	+
'STERIEL'	-
'_'	-
'Positive'	+
'NONE SEEN'	-
'Positive'	+
'NONE FOUND'	-
'NOT INDICATED'	-

Part II: other items received descriptive measurements rather than numerical ones. They were merged together according to the following table:

Before	After
'OCC'	Trace
'1+ OR 1/4 G/DL'	Small
'2+ OR 1/2 G/DL'	Moderate
'4+ OR 2 OR MORE'	Large
'TRACE'	Trace
'MODERATE'	Moderate
'TRACE OR 1/10 G'	Trace
'NORMAL'	Normal
'MODERATE'	Moderate
'INNUMERABLE'	Very Large
'trace'	Trace
'FEW'	Small
'MANY'	Large
'MODERATE'	Moderate
'OCCASIONAL'	Trace
'MOD'	Moderate
'NORM'	Normal
'FEW(1-5)'	Small
'MODERATE(6-20)'	Moderate
'MANY (21 OR GRE'	Large
'SMALL'	Small
'TRACES'	Trace
'SLIGHT'	Trace
'Non Hemolyzed Trace'	Trace
'Non Hemolyzed Modera'	Moderate
'OCC''L'	Trace
'CLEAR'	Normal
'FROM 1 TILL 4/FIELD'	Small
'MODERATE(6-20)'	Moderate

Treating '<' and '>'

There were cases in which instead of a numerical value, the assessment results were given as '<' or '>' and a numerical value. In cases where the range represented in the argument was normal, they were replaced with ‘Normal’. If the range wasn’t normal, they were removed.

Before - Test Name and Value	After - Value
'Band Neutrophils' '<10'	'Normal'
'Basophils' '<.2'	'Normal'
'Basophils' '<0.2'	'Normal'
'Beta HCG' '< 2'	'Normal'
'Beta HCG' '<1' '	'Normal'
'Beta HCG' '<2'	'Normal'
'Beta HCG' '<2.0'	'Normal'
'Beta HCG' '<5'	'Normal'
'Bilirubin (direct)' '<0.1'	'Normal'
'C-Reactive Protein' '<3'	'Normal'
'C-Reactive Protein' '<3.0'	'Normal'
'C-Reactive Protein' '<5.00'	'Normal'
'CK' '<20'	'Normal'
'CK' '<20'	'Normal'
'Eosinophils' '<.7'	'Normal'
'Eosinophils' '<0.7'	'Normal'
'Thyroid Stimulating Hormone' '< 0.14'	'Normal'
'Gamma-glutamyltransferase' '<6'	'Normal'
'Glucose' '<40'	'Normal'
'HbA1c (Glycated Hemoglobin)' '<3.6'	'Normal'
'HbA1c (Glycated Hemoglobin)' '<5.0'	'Normal'
'Thyroid Stimulating Hormone' '< 0.14'	'Normal'
'Urine Ketones' '=<1.5'	'Normal'
'Urine Protein' '<0.05'	'Normal'
'Hepatitis B' '>200'	+
'Lithium' '<0.10'	-
'HbA1c (Glycated Hemoglobin)''<5'	'Normal'
'Urine Protein' '<2'	'Trace'
'Basophils' '<1'	'Normal'

Cases removed

Before - Test Name and Value	Action
'Beta HCG' '<25'	Removed
'International Normalized Ratio (clotting)' '> 16.50'	Removed
'CA 19-9' '<8'	Removed
'Myoglobin' '<50'	Removed
'Partial Thromboplastin Time' '>60.0'	Removed
'Potassium' '>7.3'	Removed
'Prothrombin Time (clotting)' '> 200.0'	Removed
'Prothrombin Time (clotting)' '>100'	Removed
'Troponin I' '<0.5'	Removed
'Urine RBCs' '<2000'	Removed
'Urine Urobilinogen' '=<17'	Removed
'Vitamin B12' '>2000'	Removed
'Bilirubin (total)' 'T.0.5 D.0.1'	Removed
'Urine RBCs' 'TNTC'	Removed
'Urine Culture' '3+ MIXED BACTERIA-PROBABLE CONTAMINATION'	Removed
'HbA1c (Glycated Hemoglobin)' 'MA'	Removed
'Bilirubin (total)' '<0.1'	Removed
'Bilirubin (total)' '<0.5'	Removed
'Bilirubin (total)' '<4'	Removed
'Urine Microalbumin' '<3.0'	Removed
'Urine WBCs' '>50'	Removed
'Beta HCG' '<0.2'	Removed
'Bilirubin (total)' '<3'	Removed

Treating range assessments

In some cases, ranges (i.e. 1-5) appeared instead of numerical values. In these cases the values were averaged.

Test Name	Unit of measurement	Action
'Urine RBCs'	'/HPF'	Average
'Urine WBCs'	'/HPF'	Average
'Urine WBCs'		Average
'Red Blood Cells (RBC)'		Average
'WBC'		Average
'Urine Granular Cast'		Average
'Urine Granular Cast'	'/HPF'	Average
'Hyaline Cast'		Average
'Urine Hyaline Cast'		Average
'Urine blood'		Average
'Beta HCG'	'IU/L'	Average
'Glucose'	'mmol/L'	Average
'Urine RBCs'		Average
'Urine Epithelial Cells'	'/HPF'	Average
'Urine Epithelial Cells'		Average
'Urine RBCs'	'HPF'	Average
'Urine WBCs'	'per field'	Average

Note: An empty field in the Unit of measurement indicates that one was not listed.

Individual changes to text values

In some cases, values were listed as text, indicating a mistake that was fixed on an individual basis:

Test name	Value Before	Unit of measurement	Value After
'Basophils'	'(3%'	'%'	3
'Basophils'	'0%'	'10E9/L'	0
'Basophils'	'1%'	'%'	1
normal'>'Lymphocytes'	normal'>'32%'	normal'>'%'	normal'>32
'Basophils'	'1.1e-005'	'10E9/L'	110000
'Basophils'	'1.4e-005'	'10E9/L'	140000
'Basophils'	'1.8e-005'	'10E9/L'	180000
'Basophils'	'1e-005'	'10E9/L'	100000
'Basophils'	'2.7e-005'	'10E9/L'	270000
'Basophils'	'2e-005'	'10E9/L'	200000
'Basophils'	'3.2e-005'	'10E9/L'	320000
'Basophils'	'3.6e-005'	'10E9/L'	360000
'Basophils'	'3e-005'	'10E9/L'	300000
'Basophils'	'4.3e-005'	'10E9/L'	430000
'Basophils'	'4.4e-005'	'10E9/L'	440000
'Basophils'	'4.8e-005'	'10E9/L'	480000
'Basophils'	'4.9e-005'	'10E9/L'	490000
'Basophils'	'4e-005'	'10E9/L'	400000
'Basophils'	'5.7e-005'	'10E9/L'	570000
'Basophils'	'5e-005'	'10E9/L'	500000
'Basophils'	'6.1e-005'	'10E9/L'	610000
'Basophils'	'6.3e-005'	'10E9/L'	630000
'Basophils'	'6.6e-005'	'10E9/L'	660000
'Basophils'	'6e-005'	'10E9/L'	600000
'Basophils'	'7.1e-005'	'10E9/L'	710000
'Basophils'	'7.3e-005'	'10E9/L'	730000
'Basophils'	'7.4e-005'	'10E9/L'	740000
'Basophils'	'7e-005'	'10E9/L'	700000
'Basophils'	'8e-005'	'10E9/L'	800000
'Basophils'	'9.1e-005'	'10E9/L'	910000
'Basophils'	'9.4e-005'	'10E9/L'	940000
'Basophils'	'9e-005'	'10E9/L'	900000
'CK'	'377 U/L'	'U/L'	377
'Eosinophils'	'0%'	'%'	0
'Eosinophils'	'(10%'	'%'	10
'Eosinophils'	'1e-005'	'10E9/L'	100000
'HbA1c (Glycated Hemoglobin)'	'4.6%'	'%'	4.6
'HbA1c (Glycated Hemoglobin)'	'5.0%'	'%'	5
'Basophillic Stippling'	'1+'		+
'Monocytes'	'4.9e-005'	'10E9/L'	490000
'Monocytes'	'5%'	'%'	5
'Eosinophils'	'3e-005'	'10E9/L'	300000
'Eosinophils'	'4e-005'	'10E9/L'	400000
'Eosinophils'	'5e-005'	'10E9/L'	500000
'Eosinophils'	'6.3e-005'	'10E9/L'	630000
'Eosinophils'	'6.9e-005'	'10E9/L'	690000
'Eosinophils'	'6e-005'	'10E9/L'	600000
'Eosinophils'	'7.1e-005'	'10E9/L'	710000
'Eosinophils'	'7.2e-005'	'10E9/L'	720000
'Eosinophils'	'7e-005'	'10E9/L'	700000
'Eosinophils'	'8.6e-005'	'10E9/L'	860000
'Eosinophils'	'8.7e-005'	'10E9/L'	870000
'Eosinophils'	'8e-005'	'10E9/L'	800000
'Eosinophils'	'9e-005'	'10E9/L'	900000
'Glucose'	'FASTING 5.6'	'mmol/L'	5.6
'Neutrophils'	'44%'	'%'	44
'Neutrophils'	'61%'	'%'	61
'Neutrophils'	'87%'	'%'	87
'Urine Appearance'	'YELLOW CLEAR'		Clear
'HbA1c (Glycated Hemoglobin)'	'<5'	'%'	Normal
'Urine Casts'	'2++'		'2+'
'Urine Protein'	'<2'	'mg/dL'	Trace
'Beta HCG'	'<10'	'U/L'	Normal
'Beta HCG'	'<4'	'U/L'	Normal
'Lymphocytes'	'32%'	'%'	32

Note: An empty field in the Unit of measurement indicates that one was not listed.

Individual changes of value and unit

In some cases the value was listed as text, indicating a mistake that was fixed on an individual basis and the Unit of measurement was also fixed as well.

Test Name	Value Before	Unit of Measurement Before	Value- After	Unit of measurement- After
'Band Neutrophils'	'2%'	'10E9/L'	2	%
'Eosinophils'	'1%'	'10E9/L'	1	%
'Eosinophils'	'2%'	'10E9/L'	2	%
'Monocytes'	'11%'	'10E9/L'	11	%
'Monocytes'	'3%'	'10E9/L'	3	%
'Monocytes'	'6%'	'10E9/L'	6	%
'Neutrophils'	'48%'	'10E9/L'	48	%
'Neutrophils'	'60%'	'10E9/L'	60	%
'Basophils'	'1%'	'10E9/L'	1	%
'Lymphocytes'	'36%'	'10E9/L'	36	%
'Lymphocytes'	'39%'	'10E9/L'	39	%
'Lymphocytes'	'30%'	'10E9/L'	30	%
'Lymphocytes'	'32%'	'10E9/L'	32	%
'Urine RBCs'	'0-5/HPF'		'0-5'	'/HPF'

Note: An empty field in the Unit of measurement indicates that one was not listed.

Overall, these are the following Text values remaining within PRO-ACT:

Qualitative assessment	Descriptive assessment of Urine	Individual cases
'+'	'AMBER'	'M=29.7;T=34'
'-'	'BROWN'	'GROUP B HEMOLYTIC STREPTOCOCCI 10000-100000 10E3'
'1+'	'CLOUDY'
'2+'	'Clear'
'2++'	'GREEN'
'3+'	'HAZY'
'3+++'	'RED'
'4+'	'STRAW'
'Borderline'	'TURBID'
'Hemolyzed Large'	'YELLOW'
'Hemolyzed Moderate'
'Hemolyzed Small'
'Hemolyzed Trace'
'Large'
'Moderate'
'NO'
'Normal'
'Small'
'Trace'
'Very Large'
'YES'

(this also includes assessments where a numeric value was expected and a text value appeared instead)

Removal of cases

Some measurements were removed completely, often because of a problem with the values listed,

Test Name	Unit of Measurement	Action
'BETA-GLOBULIN'	'%'	Removed
'Protein'	'%'	Removed
'Test'	'Units'	Removed
'Urine Epithelial Cells'		Removed
'Gamm'	'U/L'	Removed
'2EME HEURE'	'mm'	Removed
'Alkaline Phosphatase'		Removed
'IMMUNOGLOBULIN A'		Removed
'IMMUNOGLOBULIN M'		Removed
'IgG'		Removed
'Immunoglobulins'	'IU/mL'	Removed
'NOT APPLICABLE'	'mmol/L'	Removed
'Red Blood Cells (RBC)'	'per field'	Removed
'SGOPT'	'U/L'	Removed
'SGOP' 'U/L'	'U/L'	Removed
'Urine Culture'	'10E3/L'	Removed
'VS 1ERE HEURE'	'mm'	Removed
'Bilirubin'	'MG/L'	Removed
'Urine Urobilinogen'	'IU/dL'	Removed
'Urine Urobilinogen'	'umol/L'	Removed
'Urine WBC Cast'		Removed
'WAXY CASTS'		Removed
'Urine WBCs'	'mL'	Removed
'Urine blood'	'%'	Removed
'VIT SED 1H'	'MM'	Removed
'VIT SED 1H'	'MM'	Removed
'VIT SED 2H'	'MM'	Removed
'Urine RBCs'	'IU/mL'	Removed
'Urine RBCs'	'mL'	Removed
'Urine RBCs'	'per cm3'	Removed
'Free T3'		Removed
'Mean Corpuscular Hemoglobin Concentration'	'mmol/L'	Removed
'Free T3'	'pg/mL'	Removed
'Free T4'	'pg/mL'	Removed
'Myoglobin'	'%'	Removed
'Globulin'	'g/L'	Removed
'Hematocrit'	'g/L'	Removed
'Hemoglobin'	'%'	Removed
'PAL'	'U/L'	Removed
'Urine Epithelial Cells'	'per field'	Removed
'Reticulocyte'	'%'	Removed
'Salivary Amylase'		Removed
'Protein'	'mEq/L'	Removed
'Protein'	'mmol/L'	Removed
'Phosphorus'	'MG/L'	Removed
'ALPHA1-GLOBULIN'	'%'	Removed
'ALPHA2-GLOBULIN'	'%'	Removed
'Anion Gap'	'mg/L'	Removed
'Bicarbonate'	'mmHg'	Removed
'Blood Urea Nitrogen (BUN)'	'umol/L'	Removed
'C-Reactive Protein'		Removed
'Calcium'	'MG/L'	Removed
'CA 19-9'	'IU/mL'	Removed
'Folate'	'nmol/L'	Removed
'Hepatitis B'	'mIU/L'	Removed
'Urine WBCs'	'per cm3'	Removed
'Vitamin B12'	'pmol/L'	Removed
'Myoglobin'	'mg/L'	Removed
'Myoglobin'	'ug/L'	Removed
'T3'	'nmol/L'	Removed
'Troponin	'mg/L'	Removed
'Urine Blood' '		Removed
'Urine Culture'	'10E9/L'	Removed
'Urine Ketones'		Removed
'Alkaline Phosphatase'	'D'	Removed
'Glucose'	'mg/L'	Removed

Some records with test names 'Follicle Stimulating Hormone' and 'Thyroid Stimulating Hormone' were removed due to unit and value incompatibility with other records. They will be added to the dataset in the future after further investigation and necessary cleaning.

Unit conversion for specific Test names

There were cases in which the Unit of measurement was changed for all instances of that combination of Test and Unit of measurement. This could be due to typing error (indicated by the spelling or by a mismatch between the Unit of Measurement and the value), an attempt to merge units with different names but similar meaning, or treating cases of missing Unit of measurements (indicated by an empty cell in the table below)

Test Name	Unit of Measurement-Before	Unit of Measurement-After
'Sodium'	'mEq/L'	'mmol/L'
'Potassium'	'mEq/L'	'mmol/L'
'Anion Gap'	'mEq/L'	'mmol/L'
'Bicarbonate'	'mEq/L'	'mmol/L'
'Chloride'	'mEq/L'	'mmol/L'
'Lithium'	'mEq/L'	'mmol/L'
'CK'	'mIU/L'	'U/L'
'ALT(SGPT)'	'IU'	'U/L'
'ALT(SGPT)'	'IU/mL'	'U/L'
'ALT(SGPT)'	'mIU/L'	'U/L'
'ALT(SGPT)'	'mL/L'	'U/L'
'ALT(SGPT)'	'mU/L'	'U/L'
'AST(SGOT)'	'IU'	'U/L'
'AST(SGOT)'	'IU/mL'	'U/L'
'AST(SGOT)'	'mIU/L'	'U/L'
'AST(SGOT)'	'%'	'U/L'
'Albumin'		'g/L'
'Alkaline Phosphatase'	'mIU/L'	'U/L'
'Anion Gap'		'mmol/L'
'Base Excess'		'mmol/L'
'Basophils'	'10E3/mm3'	'10E9/L'
'Basophils'	'g/L'	'10E9/L'
'Beta HCG'	'IU/mL'	'U/L'
'Bilirubin (total)'	'mmol/L'	'umol/L'
'CK'	'IU'	'U/L'
'CK'	'IU/mL'	'U/L'
'Bicarbonate' 1	'mEq/L'	'mmol/L'
'Eosinophils'	'10E3/mm3'	'10E9/L'
'Eosinophils'	'g/L'	'10E9/L'
'Erythrocyte Sediment'	'mm'	'mm/hr'
'Gamma-glutamyltransferase'	'mIU/L'	'U/L'
'Globulin'		'g/L'
'Glucose'	'g/L'	'mg/dL'
'Hematocrit'	'1'	'%'
'Hemoglobin'	'G % ML'	'g/L'
'Hemoglobin'	'G%'	'g/L'
'Hemoglobin'	'G/%'	'g/L'
'Hemoglobin'	'g/100ml'	'g/dL'
'Hemoglobin'	'g/100ul'	'g/dL'
'Hemoglobin'	'grams'	'g/L'
'Hemoglobin'	'mol/L'	'mmol/L'
'International Normalized Ratio (clotting)'	'1'
'Lymphocytes'	'10E3/mm3'	'10E9/L'
'Lymphocytes'	'g/L'	'10E9/L'
'Mean Corpuscular Hemoglobin Concentration'		'%'
'Mean Corpuscular Hemoglobin'		'pg'
'Mean Corpuscular Volume'		'fL'
'Mean Corpuscular Volume'	'per mm3'	'fL'
'Mean Platelet Volume'		'fL'
'Monocytes'	'10E3/mm3'
'Monocytes'	'g/L'	'10E9/L'
'Neutrophils'	'10E3/mm3'	'10E9/L'
'Neutrophils'	'g/L'	'10E9/L'
'Osmolality'		'mOsm/kg H2O'
'Partial Thromboplastin Time'		'seconds'
'Platelets'	'10E3/L'	'10E9/L'
'Platelets'	'10E3/MM3'	'10E9/L'
'Platelets'	'10E3/mL'	'10E9/L'
'Platelets'	'10E3/mm3'	'10E9/L'
'Platelets'	'g/L'	'10E9/L'
'Potassium'	'mmol'	'mmol/L'
'Red Blood Cells (RBC)'	'10E6/MM3'	'10E12/L'
'Red Blood Cells (RBC)'	'10E6/mL'	'10E12/L'
'Red Blood Cells (RBC)'	'10E6/mm3'	'10E12/L'
'Red Blood Cells (RBC)'	'mm3'	'10E12/L'
'Red Blood Cells (RBC)'	'per mm3'	'10E12/L'
'Saturated O2 Fraction'	'mmHg'	'%'
'Thyroid Stimulating Hormone'		'U/L'
'Thyroid Stimulating Hormone'	'IU/mL'	'U/L'
'Thyroid Stimulating Hormone'	'mIU/L'	'U/L'
'Urea'	'umol/L'	'mmol/L'
'Urine Albumin'		'g/dL'
'Urine Bilirubins'	'mg/dL'
'Urine Glucose'		'mg/dL'
'Urine Ketones'	'mmol/L'
'Urine Leukesterase'		'U/L'
'Urine Ph'	'pH'
'WBC'	'10E3/L'	'10E9/L'
'WBC'	'10E3/MM3'	'10E9/L'
'WBC'	'10E3/mL'	'10E9/L'
'WBC'	'10E3/mm3'	'10E9/L'
'WBC'	'g/L'	'10E9/L'
'Ferritin'	'ng/mL'	'ug/L'
'CK'	'IU/L'	'U/L'
'Creatine Kinase MB'	'IU/L'	'U/L'
'Creatine Kinase MB'	'mg/L'	'U/L'
'Creatine Kinase MB'	'ug/L'	'U/L'
'Creatine Kinase MM'	'IU/L'	'U/L'
'Hepatitis A'	'ND'
'Hepatitis B'	'ND'
'Hepatitis C'	'ND'
'Hepatitis C'	'U/L'
'Hepatitis B'	'U/L'
'Hepatitis A'	'U/L'
'Partial Thromboplastin Time'	'second'	'Seconds'
'International Normalized Ratio (clotting)'	'A'
'Urine Protein'		'mg/dL'
'Urine Hemoglobin'		'mmol/L'
'Mean Corpuscular Hemoglobin'	'pg'	'pg/cell'
'RBC Distribution Width'		'%'
'Prothrombin Time (clotting)'	'sec'	'seconds
'C-Reactive Protein'	'MG/L'	'mg/L'
'RBC Distribution Width'	'fL'	'%'
'Beta Hcg Quantitative'	'IU/L'	'mIU/mL'

Note: An empty field in the Unit of measurement indicates that one was not listed.

Unit conversion

Some units were converted so as to be able to merge synonymous assessments together.

Test Name	Unit of measurement - Before	Unit of measurement - After	Conversion Factor
'Magnesium'	'mEq/L'	'mmol/L'	0.5
'Calcium'	'mEq/L'	'mmol/L'	0.5
'Albumin'	'g/dL'	'g/L'	10
'Albumin'	'mg/L'	'g/L'	100
'Basophils'	'mm3'	'10E9/L'	0.001
'Basophils'	'per mm3'	'10E9/L'	0.001
'Bilirubin (Indirect)'	'mg/L'	'mg/dL'	0.1
'Bilirubin (total)'	'g/L'	'mg/L'	100
'Bilirubin (total)'	'mg/L'	'mg/dL'	0.1
'Bilirubin (total)'	'mg/dL'	'umol/L'	17.104
'Bilirubin (Direct)'	'mg/dL'	'umol/L'	17.104
'Bilirubin (Indirect)'	'mg/dL'	'umol/L'	17.104
'Blood Urea Nitrogen (BUN)'	'mg/dL'	'mmol/L'	0.3651
'C-Reactive Protein'	'mg/dL'	'mg/L'	10
'Calcium'	'g/L'	'mg/L'	100
'Calcium'	'mg/L'	'mg/dL'	0.1
'Calcium'	'mg/dL'	'mmol/L'	0.2495
'Creatinine'	'g/L'	'mg/L'	100
'Creatinine'	'mg/L'	'mg/dL'	0.1
'Creatinine'	'mg/dL'	'umol/L'	88.4
'Creatinine'	'umol/L'	'mmol/L'	0.001
'Creatinine'	'umol/L'	'mmol/L'	0.001
'Eosinophils'	'mm3'	'10E9/L'	0.001
'Eosinophils'	'per mm3'	'10E9/L'	0.001
'Glucose'	'g/L'	'mg/L'	100
'Glucose'	'mg/dL'	'mmol/L'	0.0555
'HbA1c (Glycated Hemoglobin)'	'V/V'	'%'	100
'Hematocrit'	'V/V'	'%'	100
'Hematocrit'	'I/I'	'%'	100
'Hemoglobin'	'g/dL'	'g/L'	10
'Lymphocytes'	'mm3'	'10E9/L'	0.001
'Lymphocytes'	'per mm3'	'10E9/L'	0.001
'Magnesium'	'mg/dL'	'mmol/L'	0.4113
'Mean Corpuscular Hemoglobin Concentration'	'g/L'	'g/dL'	10
'Monocytes'	mm3'	'10E9/L'	0.001
'Monocytes'	'per mm3'	'10E9/L'	0.001
'Neutrophils'	'mm3'	'10E9/L'	0.001
'Neutrophils'	'per mm3'	'10E9/L'	0.001
'Phosphorus'	'g/L'	'mg/L'	100
'Phosphorus'	'mg/L'	'mg/dL'	0.1
'Phosphorus'	'mg/dL'	'mmol/L'	0.323
'Platelets'	'mm3'	'10E9/L'	0.001
'Platelets'	'per mm3'	'10E9/L'	0.001
'Protein'	'g/dL'	'g/L'	10
'Saturated O2 Fraction'		'%'	100
'Saturated O2 Fraction'	'V/V'	'%'	100
'Triglycerides'	'mg/dL'	'mmol/L'	0.0113
'Urea'	'g/L'	'mg/dL'	100
'Urea'	'mg/dL'	'mmol/L'	0.3651
'Uric Acid'	'mg/dL'	'mmol/L'	0.05948
'Urine Protein'	'g/L'	'mg/dL'	100
'WBC' '	ml'	'10E9/L'	0.001
'WBC'	'mm3'	'10E9/L'	0.001
'WBC'	'per mm3'	'10E9/L'	0.001
'Iron'	'umol/L'	'ug/dL'	5.5866
'Total Cholesterol'	'mg/L'	'mg/dL'	1
'Red Blood Cells (RBC)	' 'Millions'	'10E9/L'	1000
'Red Blood Cells (RBC)'	'T/L'	'10E9/L'	1000
'Hemoglobin'	'mg/L'	'g/L'	10
'Free T3'	'pmol/L'	'nmol/L'	0.001
'Uric Acid'	'mmol/L'	'umol/L'	1000
'Total Cholesterol'	'g/L'	'mg/dL'	100
'Neutrophils'	'10E6/L'	'10E9/L'	0.001
'Monocytes'	'10E6/L'	'10E9/L'	0.001
'Basophils'	'10E6/L'	'10E9/L'	0.001
'Eosinophils'	'10E6/L'	'10E9/L'	0.001
'WBC'	'10E6/L'	'10E9/L'	0.001
'Fibrinogen'	'g/L'	'mg/dL'	100
'GAMMA-GLOBULIN'	'mg/L'	'mg/dL'	0.1
'Hemoglobin'	'mmol/L'	'g/dL'	1.551
'IMMUNOGLOBULIN M'	'g/L'	'mg/dL'	100
'Lymphocytes'	'10E6/L'	'10E9/L'	0.001
'IMMUNOGLOBULIN A'	'g/L'	'mg/dL'	100
'Total Cholesterol'	'mg/dL'	'mmol/L'	0.02586
'Platelets'	'10E6/L'	'10E9/L'	0.001
'Red Blood Cells (RBC)'	'10E12/L'	'10E9/L'	1000
'Triglycerides'	'g/L'	'mmol/L'	1.129
normal'> 'bilirubin (total)'	normal'>'MG/L'	normal'>umol/L	normal'>1.7104
normal'> 'Basophils'	normal'>'10E12/L'	normal'>10E9/L	normal'>0.001
normal'> 'Blood Urea Nitrogen (BUN)'	normal'>'g/L'	normal'>mmol/L	normal'>3.651
normal'>'Mean Corpuscular Hemoglobin Concentration'	normal'>'g/dL'	normal'>'g/L'	normal'>10
normal'>'Bilirubin (Direct)'	normal'>'MG/L'	normal'>'umol/L'	1.7104
'Globulin'	'g/dL'	'g/L'	10

Note: An empty field in the Unit of measurement indicates that one was not listed.

Alteration of specific values (outlier analysis)

Done if there were two distinct populations separated by a large magnitude, or if a value was undecipherable.

For the test 'Creatinine' and unit of measurement 'mmol/L' , 'if x < 10, multiply by 1000 change unit to umol/L)
For the test 'Uric Acid' and unit of measurement 'umol/L' , 'if x > 1000, divide by 1000
Remove the test 'Urine Ph' if value is <1.5

General processing of the data

Processing of assessments with Text Values

Processing of assessments with numeric values