Classify Clinical Measurements by Data Quality

How do you handle missing or corrupt data in a large clinical dataset using Python?

For this SQL translation, classify each clinical measurement as valid, missing, or corrupt using the recorded value, test type, verification status, and measurement date. Preserve records whose patient identifier does not match the patient registry.

Output

1. One row per clinical measurement, with columns lab_id, patient_id, patient_name, test_name, measured_at, cleaned_value, and data_quality_status.
2. Set cleaned_value to NULL unless the measurement is valid.
3. Include all measurements, order by patient_id ascending with unmatched patients last, then measured_at ascending with NULL dates last, and lab_id ascending.

Output details

• Mark a measurement missing when measurement_value or measured_at is NULL, before applying corruption checks.
• Mark a measurement corrupt unless source_status is exactly verified.
• Accept only Hemoglobin, Glucose, and ALT as test names.
• Use inclusive valid ranges of 8.0 through 20.0 for Hemoglobin, 40.0 through 500.0 for Glucose, and 0.0 through 200.0 for ALT.