Handling Missing or Corrupt Data | Dataford Interview Questions

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

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

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name
site_code	VARCHAR(20)	Clinical site identifier

lab_results

Column	Type	Description
lab_idPK	INT	Unique laboratory result identifier
patient_id	INT	Patient associated with the measurement
test_name	VARCHAR(50)	Clinical test name
measurement_value	DECIMAL(8,2)	Numeric laboratory measurement
unit	VARCHAR(20)	Measurement unit
measured_at	DATE	Date the measurement was taken
source_status	VARCHAR(20)	Source verification status

Your question is Handling Missing or Corrupt Data. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem