# 02-data.md

## Purpose

Phase 2 defines how the source dataset for the COVID data analysis project will be inventoried, validated, and ingested into the project data flow. The immediate goal is to establish a reproducible raw-data baseline for `final_with_deceased.csv`, document source provenance using `metadata.json`, and identify ingestion-time risks that affect downstream cleaning, cohort definition, and modeling.

This phase does not clean, impute, or model the data. It focuses on confirming what the source file contains, how it should be stored, and what must be validated before downstream phases proceed.

## Source Inventory

### Primary Source Files

| Artifact | Role | Status |
| -------- | ---- | ------ |
| `final_with_deceased.csv` | Primary tabular source dataset for encounter-level analysis | Available locally |
| `metadata.json` | File-level and column-level summary metadata for the CSV | Available locally |

### Source Summary

* Source file name: `final_with_deceased.csv`
* Row count: 156,030
* Column count: 27
* Metadata creation timestamp: `2026-04-22T03:10:53.827423573`
* Metadata modification timestamp: `2026-04-22T03:10:54.275983810`
* Overall missingness reported in metadata: 24.09%
* Variable mix reported in metadata:
  * 10 continuous variables
  * 8 datetime variables
  * 9 categorical variables

## Unit of Analysis

### Current Working Assumption

The dataset is currently treated as an encounter-level file, with `visit_occurrence_id` acting as the encounter identifier and `person_id` acting as the patient identifier.

### Evidence

* `visit_occurrence_id` has 156,030 unique values across 156,030 rows
* `person_id` has 98,985 unique values across 156,030 rows
* `visit_type` includes `Outpatient Visit`, `Inpatient Visit`, and `Emergency Room Visit`

### Open Question

Although the file appears encounter-level, later phases must confirm whether each row represents:

* one encounter,
* one measurement snapshot per encounter, or
* a partially denormalized join of encounter, observation, and immunization data.

## Observed Schema

### Column Inventory

`person_id`, `birth_datetime`, `race_source_value`, `ethnicity_source_value`, `gender_source_value`, `visit_occurrence_id`, `visit_start_date`, `visit_end_date`, `visit_type`, `condition`, `observation_source`, `age_at_visit_years`, `measurement_Date`, `body_height_cm`, `bmi`, `body_temperature_c`, `body_weight_kg`, `systolic`, `diastolic`, `heart_rate_bpm`, `oxygen_saturation_percent`, `respiratory_rate_per_minute`, `flu_last_administered`, `tdap_last_administered`, `mmr_last_administered`, `polio_last_administered`, `deceased`

### Functional Grouping

* Identifiers: `person_id`, `visit_occurrence_id`
* Demographics: `birth_datetime`, `race_source_value`, `ethnicity_source_value`, `gender_source_value`, `age_at_visit_years`
* Encounter timing and setting: `visit_start_date`, `visit_end_date`, `visit_type`
* Clinical presentation: `condition`, `observation_source`
* Vitals and measurements: `measurement_Date`, `body_height_cm`, `bmi`, `body_temperature_c`, `body_weight_kg`, `systolic`, `diastolic`, `heart_rate_bpm`, `oxygen_saturation_percent`, `respiratory_rate_per_minute`
* Immunization history: `flu_last_administered`, `tdap_last_administered`, `mmr_last_administered`, `polio_last_administered`
* Outcome candidate: `deceased`

## Initial Ingestion Findings

### Structural Findings

* The file is readable as CSV and includes a header row.
* The CSV header contains `measurement_Date`, while `metadata.json` names the same field `measurement_date`.
* `visit_occurrence_id` is unique at the row level.
* `visit_start_date` and `visit_end_date` are populated for all rows.
* No records were found where `visit_end_date` is earlier than `visit_start_date`.
* 122,448 visits appear to start and end on the same day.

### Missingness Findings

High missingness is concentrated in measurement and observation fields:

* `body_height_cm`: 98.04% missing
* `bmi`: 98.10% missing
* `oxygen_saturation_percent`: 50.77% missing
* `observation_source`: 49.88% missing
* `body_weight_kg`: 48.90% missing
* `heart_rate_bpm`: 48.90% missing
* `respiratory_rate_per_minute`: 48.90% missing
* `systolic`: 48.89% missing
* `diastolic`: 48.89% missing
* `body_temperature_c`: 44.92% missing
* `measurement_Date`: 43.05% missing

### Distribution Findings

* `visit_type` distribution:
  * `Outpatient Visit`: 134,954
  * `Inpatient Visit`: 21,056
  * `Emergency Room Visit`: 20
* `deceased` distribution:
  * `N`: 139,602
  * `Y`: 16,428
* `observation_source` is dominated by `Suspected COVID-19` among non-missing values, but many rows have no observation value.
* `condition` contains 4,185 unique values and appears to include concatenated multi-condition strings.

### Cohort and Date Findings

* The file contains many rows with `visit_start_date` before `2020-01-01` and before `2020-03-01`, which is inconsistent with a narrowly defined COVID-era encounter cohort.
* `visit_start_date` minimum is `1909-09-17` according to metadata, which is not plausible for a COVID presentation dataset and must be investigated.
* `measurement_date` minimum is `1919-05-11` according to metadata, which is also implausible for this use case.

## Ingestion Objectives

Phase 2 must produce an auditable raw-data baseline and answer the following questions:

1. Is `final_with_deceased.csv` the authoritative source extract for this phase?
2. What provenance can be documented for the file and its accompanying metadata?
3. Does the source schema match the metadata schema exactly?
4. Can the source be copied into `data/raw/` without modification and with clear version tracking?
5. What ingestion-time data issues must be handed off to Phase 3 cleaning or back to Phase 1 problem definition?

## Required Ingestion Outputs

### Raw Data Handling

* Create `data/raw/` if it does not already exist.
* Store an immutable copy of `final_with_deceased.csv` in `data/raw/`.
* Preserve the source file contents exactly during copying.
* Record file size, row count, column count, and a checksum in project documentation or ingestion logs.

### Documentation

* Record source provenance in `progress.md`.
* Document the authoritative column names as observed in the CSV.
* Document mismatches between the CSV and `metadata.json`.
* Record whether the source file should be treated as the full study population or as a pre-filtered subset.

### Validation

At ingestion time, validate:

* header names and column order
* row count and column count
* uniqueness of `visit_occurrence_id`
* presence of required identifiers and visit dates
* basic date ordering for `visit_start_date` and `visit_end_date`
* whether datetime fields are date-only rather than timezone-aware timestamps
* whether the file includes only COVID-like presentations or a broader respiratory/general outpatient population

## Explicit Non-Goals

Phase 2 should not yet:

* rename columns in the raw source
* fill or drop missing values
* derive modeling labels beyond documenting candidate fields
* filter the cohort for modeling use
* standardize concatenated condition strings
* infer discharge-readiness labels

## Risks

* The dataset may be broader than the target COVID-like cohort and may require explicit cohort construction later.
* The `deceased` field may not align with either admission or discharge targets and could introduce leakage or outcome ambiguity.
* The file appears date-only rather than timezone-aware, which conflicts with the project standard and may limit temporal leakage checks.
* The schema mismatch between `measurement_Date` and `measurement_date` could break automated validation if not handled explicitly.
* Extremely early dates suggest synthetic data artifacts, source-system placeholder dates, or metadata parsing issues.
* `condition` may need normalization because repeated colon-delimited values can distort frequency-based summaries.

## Acceptance Criteria

* [ ] Source files are inventoried and documented
* [ ] `final_with_deceased.csv` is copied immutably into `data/raw/`
* [ ] File provenance and schema are recorded in `progress.md`
* [ ] Header names are validated against `metadata.json`, with mismatches documented
* [ ] Encounter identifier assumptions are validated and documented
* [ ] Initial date-range, cohort-scope, and missingness risks are recorded for downstream phases

## Phase Gate

This spec is ready for execution once Phase 1 is approved or explicitly waived for parallel preparation work. Phase 3 should not begin until the raw dataset is ingested, the schema is validated, and the cohort scope concerns identified here are reviewed.
