SM5005 Chap.9 Cleaning Health-System Spend Data
Cleaning Health-System Spend Data
Raw expenditure is evidence, not yet a model vector
Operational spend files are designed for payment and management, not environmental analysis. Supplier names vary, descriptions can be sparse, credits and taxes may be mixed with purchases, currencies and dates can differ, and a line can combine goods with services.
The first rule is to preserve the raw table unchanged and assign stable record identifiers before cleaning.
Build transformations as separate, testable stages
Create explicit stages for field standardization, duplicate handling, sign conventions, exclusions, currency and price conversion, and category preparation. Each stage should retain the source identifier and a reason for every changed or excluded record.
Reconcile record counts and monetary totals before and after the stage. The difference should equal the documented adjustments.
Separate cleaning from sector mapping
Cleaning determines what the record says and puts values on a consistent basis. Mapping decides which MRIO sector represents it. Combining them in one opaque formula makes errors hard to locate and review.
A cleaned analytical table should preserve original description, supplier, date and amount alongside standardized versions and flags.
Profile the data before estimating
Summarize missing descriptions, negative values, unusually large records, dominant suppliers and category concentration. High-value ambiguous lines deserve manual review because their classifications can control the result.
Sampling small lines checks systematic problems without pretending every record receives equal attention. The deliverable is a reconciled, documented table ready for a transparent concordance.
What this chapter covers
- 01
Immutable raw-data layer
- 02
Stable record identifiers
- 03
Staged standardization and exclusions
- 04
Currency and price alignment
- 05
Count and value reconciliation
- 06
Profiling material and ambiguous records
Worked example · free
Designing an auditable cleaning pipeline
- 2Protect the raw layer and identifiers.
- 2Order the standardization and sign treatments.
- 2Describe exclusion documentation.
- 2State count and value reconciliation checks.
Key terms
- Raw Layer
- An unchanged copy of source records retained for traceability.
- Record Identifier
- A stable key connecting every transformed row to its source record.
- Reconciliation
- A check that changes in counts or value equal documented transformations.
- Sign Convention
- The stated treatment of purchases, credits, refunds and reversals.
- Data Profiling
- Summary checks that reveal missing, extreme, duplicated or concentrated records.
Cleaning Health-System Spend Data FAQ
Should supplier names be standardized before mapping?
Yes, but preserve the original field. Standardized names help group records and add supplier knowledge consistently. They should not erase legal entities or differences that matter for sourcing, so every normalization needs a trace back to the source.
How should negative expenditure be treated?
Determine whether it represents a credit, refund, correction or genuine negative flow and apply a documented sign rule. Removing all negatives can overstate expenditure, while retaining unexplained reversals can distort categories. Reconcile the net treatment.
Which records deserve manual review?
Prioritize high-value lines, categories with weak descriptions, unusual signs, unmatched suppliers and records driving a hotspot. Review effort should follow potential effect on the result, while sampling lower-value records tests whether a systematic mapping problem remains.
Assessment move
Construct a cleaning log with one row per transformation and columns for affected count, value change, rule and reviewer. Begin from an immutable copy with stable record identifiers and keep original supplier, description, date and amount beside every standardized field. Practise reconciling a small messy table without deleting source fields or silently correcting records.
Predict whether each stage should change counts, values, both or neither, then compare the observed movement. Treat duplicates, credits, transfers, remuneration, depreciation and missing descriptions as different cases rather than one exclusion category. Build a pending table for unresolved records so they remain in the control total.
Profile the largest lines, negative amounts, missing descriptions and dominant suppliers, and allocate manual review according to possible effect on the result. Separate currency and price conversion from sector mapping so an unexpected total can be traced to the correct stage.
Before freezing the analytical table, reproduce starting value as eligible, excluded and unresolved amounts, record the version and have another reader trace three cleaned rows back to the source. Add a negative-control record that should survive unchanged and one known duplicate that should be removed. If either control behaves unexpectedly, repair the transformation rule before reviewing carbon factors or category hotspots.
Retain screenshots or exported totals only as supporting evidence; the transformation log remains the reproducible control.
