# 03-data-cleaning.md

## Purpose

Phase 3 standardizes the ingested encounter file into a cleaned analytical base table that is easier to inspect, validate, and reuse in later feature engineering. The goal is to fix schema inconsistencies, normalize formats, make missingness explicit, and add documented quality flags without silently changing the study population.

This phase should improve usability and traceability while preserving the original row-level encounter structure from the raw source.

## Inputs

| Artifact | Role |
| -------- | ---- |
| `data/raw/final_with_deceased.csv` | Immutable raw encounter dataset |
| `data/raw/metadata.json` | Source metadata reference |
| `data/output/ingestion_validation_report.json` | Phase 2 validation findings to carry into cleaning |

## Initial Analytical Findings

The Phase 2 validation and initial profiling show the following cleaning priorities:

* The raw schema contains a header inconsistency: `measurement_Date` in the CSV versus `measurement_date` in metadata.
* Several date fields are stored as strings and need consistent parsing and output formatting.
* Empty strings should be standardized to explicit missing values.
* `condition` is a colon-delimited field and 2,263 rows contain repeated condition terms.
* The dataset includes many visits before `2020-01-01`, so cohort scope should be represented with flags rather than hidden filtering.
* `observation_source` is missing in 77,833 rows and includes non-COVID observations in 4,893 additional rows.
* `measurement_date` occurs after `visit_start_date` in 2,790 rows, which is relevant for downstream feature eligibility.
* `age_at_visit_years` is internally consistent with `birth_datetime` and `visit_start_date`, so the source age calculation can be retained.

## Cleaning Objectives

Phase 3 must:

1. Standardize the raw schema into a stable cleaned schema.
2. Preserve all source rows unless an explicit exclusion rule is approved and documented.
3. Represent data quality concerns with flags rather than hidden record removal.
4. Normalize text and missing-value handling so downstream transformations are reproducible.
5. Write a cleaned dataset to `data/cleaned/` and a machine-readable cleaning report to `data/output/`.

## Cleaning Rules

### Schema Standardization

* Rename `measurement_Date` to `measurement_date`.
* Keep the remaining source column names unchanged unless a later approved rule requires renaming.
* Preserve one row per `visit_occurrence_id`.

### Missing Values

* Convert blank strings to `pd.NA` for string/date-like fields.
* Do not impute missing numeric or categorical values in this phase.

### Date Handling

* Parse date fields using ISO 8601 date semantics.
* Persist cleaned date fields in `YYYY-MM-DD` format.
* Add flags for:
  * visits before `2020-01-01`
  * measurements before `2020-01-01`
  * measurements recorded after visit start

### Text Normalization

* Trim surrounding whitespace on string fields.
* Preserve original case for clinically meaningful values already present in the source.
* Normalize `condition` into an additional de-duplicated term field while retaining the original source string.

### Derived Quality Flags

Add explicit indicators for downstream use:

* `is_pre_covid_era_visit`
* `is_pre_covid_era_measurement`
* `measurement_after_visit_start`
* `has_suspected_covid_observation`
* `is_candidate_covid_like_visit`
* `condition_terms_repeated`

### Condition Handling

* Retain the original `condition` field.
* Add `condition_terms_cleaned` as an ordered, de-duplicated pipe-delimited list of condition terms.
* Add `condition_term_count` based on the cleaned term list.

## Explicit Non-Goals

Phase 3 should not yet:

* define final admission or discharge modeling targets
* drop non-COVID visits from the cleaned base table
* impute missing vitals or labs
* collapse outpatient and inpatient encounters into separate modeling datasets
* enforce leakage-safe feature windows beyond documented flags

## Outputs

| Artifact | Role |
| -------- | ---- |
| `data/cleaned/final_with_deceased_cleaned.csv` | Cleaned encounter-level base table |
| `data/output/cleaning_report.json` | Machine-readable summary of cleaning rules and flagged counts |

## Acceptance Criteria

* [ ] Cleaning rules are documented
* [ ] Cleaned dataset is created in `data/cleaned/`
* [ ] Header inconsistency is resolved in the cleaned output
* [ ] Missing values are explicit and reproducible
* [ ] Derived quality flags are added and documented
* [ ] Cleaning tests validate key schema and row-preservation assumptions

## Phase Gate

Phase 4 can begin once the cleaned dataset is created, the cleaning report is written, and the key cleaning rules are reflected in `progress.md`.
