# First-pass OCDS mapping analysis

The [source manifest](../data/philgeps_data_v2/manifest.json) records the upstream commit and SHA-256 checksums for 12 copied references. The snapshot contains the original field and codelist workbooks, both filled Open Data workbooks, the Open Data schema registry and verification notes, and the ETL mapping configs and template review. Source directory names are preserved.

The [OCDS Mapping Explorer manifest](../data/ocds_mapping_explorer/manifest.json) records a second upstream commit for the PhilGEPS 1.5 and mPhilGEPS field-mapping projects and the PhilGEPS 1.5 field workbook. The upstream mPhilGEPS workbook matched the existing copy by SHA-256. The Explorer displays both projects independently of the ERD comparison and the Open Data ETL.

## Reproduce

From the sprint directory, run the [analysis script](../scripts/analyze_ocds_mapping.py) with Python and `openpyxl` installed.

```bash
rtk proxy python3 scripts/analyze_ocds_mapping.py
```

The script writes [ocds_mapping_analysis.json](../data/ocds_mapping_analysis.json). It compares each named source field in the workbook's Data Elements sheet to the ERD's candidate table name and field name, trimming surrounding whitespace. Each exact match records the corresponding ERD box and field IDs. A second pass folds case and treats runs of spaces and underscores alike to generate review candidates.

The six stage sheets supply OCDS paths. The script looks up each populated mapping label in the inventory, ignoring case and repeated whitespace. It records the sheet and row even when the label cannot be resolved. Cached paths on the inventory sheet are retained for inspection. The recorded paths come from the stage sheets.

The report also extracts the six PhilGEPS 1.5 procurement method values from the separate codelist workbook and records five explicit mappings from the Closed Codelists sheet. `Shopping` is unmapped in that workbook. The two upstream project JSON files and their field workbooks provide field mappings. A standalone mPhilGEPS source-code crosswalk was not present in the reference repository.

## Initial results

| Check | Result |
| --- | ---: |
| Named workbook inventory entries | 260 |
| Exact ERD table and field matches | 46 |
| Exact table name with field absent | 6 |
| No exact table name | 208 |
| Additional inventory entries with normalized candidates | 22 |
| Populated stage mapping rows | 126 |
| Stage rows linked to exact ERD fields | 20 |
| Stage rows with normalized ERD candidates | 15 |
| Stage rows with unresolved inventory labels | 15 |

- The General and Planning sheets account for all 20 exact stage links. Examples include `departments.name` → `buyer/name` and `app_line_items.total_estimated_budget` → `planning/budget/amount/amount`.
- In the Award sheet, `Awards (ID)` → `awards/id` and `Awards (Contract Amount)` → `awards/value/amount` surface `awards.id` and `awards.contract_amount` as normalized candidates.
- The workbook's `Tenders (Tender Title)` mapping to `tender/title` remains unresolved against the ERD because the extracted catalog has no `tenders` table. Keep that mapping open until a table alias or another source confirms it.
- An exact name match establishes that the field exists in the diagram. Confirm meaning, identifier rules, and publication decisions separately. For example, the workbook links `departments.id` to `relatedProcesses/id` and marks that inventory field `Publish? No`.
- The [Open Data schema registry](../data/philgeps_data_v2/DATA_NORMALIZATION/config/schema_registry.json) describes four CSV/XLSX layouts and identifies four headerless files. Its [field mapping config](../data/philgeps_data_v2/OCDS_ETL/config/field_mapping.json) covers 66 cleaned Open Data fields. Use those files as a separate crosswalk through OCDS concepts when comparing public exports with the relational ERD.

The reference files matched their recorded checksums, and two runs produced byte-identical analysis JSON.
