Hotel utility data pipeline and ESG reporting
Forty hotels type their monthly electricity, water and gas figures into a portal by hand. Before anyone can report a carbon footprint, those numbers have to match the bills. I built a pipeline that checks every value, fixes what it can prove, flags what it can't, and records every change.
- Data
- Simulated: 40 hotels, 8 countries, 24 months, 2,364 submissions + bill register
- Tools
- Python, pandas, NumPy
- Methods
- Rule-based validation, bill reconciliation, robust z-scores
- Outputs
- Clean dataset, issue log, change log, invoice gap report, emissions
The data is simulated because real hotel utility data is confidential. That has an upside: I injected realistic errors on purpose and kept an answer key hidden from the pipeline, so its accuracy is measured, not claimed.
The problem
Sustainability reports roll up hundreds of monthly utility figures. Each one is typed by a hotel engineer, and each mistake flows straight into the reported emissions.
The errors are mundane but costly: MWh entered as kWh (a 1,000× understatement), decimals shifted, months missing or submitted twice, property IDs typed three different ways, and figures that simply don't match the bill.
On top of that, some providers bill 15th to 14th, so a bill never lines up with a calendar month. Naive pro-rating blurs month-to-month changes and raises false alarms.
| Input file | Rows | What it holds |
|---|---|---|
| properties.csv | 40 | country, segment, rooms, floor area, billing cycle |
| occupancy.csv | 960 | occupied room nights per month |
| portal_submissions.csv | 2,364 | values typed by properties (contains the errors) |
| utility_bills.csv | 2,333 | invoice register, the source of truth; ~1.5% never arrived |
How the pipeline works
Each stage writes to two logs: issue_log.csv (what was found, by which rule, what was done) and change_log.csv (old value, new value, reason, source). Any number in the final report can be traced back to its origin.
Standardise
Repair IDs (
htl-001,HTL001→HTL-001), unify period formats, convert MWh and litres to base units.De-duplicate
Drop exact duplicates; for conflicting ones keep the value closest to the bill.
Build reference values
Pro-rate calendar bills. For offset billing cycles, infer each month from both bills that touch it and flag it only when every inference disagrees.
Reconcile and classify
±2% tolerance vs the bill. Ratio ≈ 0.001 means a unit mislabel, ≈ 10× a decimal shift, anything else a transcription mismatch.
Fill gaps
Compare expected vs received records. Fill from the bill, or estimate from median intensity × room nights and mark it estimated.
Check plausibility
Robust z-score (median / MAD) on consumption per occupied room night. Outliers the bill confirms are kept and escalated.
Chase missing invoices
List every expected bill that never arrived.
Calculate emissions
Scope 2 from electricity × grid factor, Scope 1 from natural gas, plus intensity per occupied room night.
What it found
Errors caught by type
Scored against the hidden answer key
The single miss was a 2.0% transcription error, right at the tolerance limit. That's the expected trade-off of a ±2% threshold.
Status of every record after the pipeline
2,400 property-month-utility records
93% passed first time. The rest were corrected or sourced from bills, and only one needed an estimate.
Not every outlier is an error
HTL-017 water consumption, m³ per month
June and July 2025 are 2.6× normal. A blind outlier rule would "fix" them. Because the bill confirms the values, the pipeline keeps them and escalates a likely water leak to operations instead.
Portfolio emissions by month
tCO₂e, from the cleaned dataset
Carbon intensity by country
kgCO₂e per occupied room night
Grid carbon intensity, not hotel efficiency, explains most of the gap: Indonesian hotels emit 2.3× more per room night than those in Singapore.
A few lines from the issue log
| Issue | Property | Period | Utility | Rule | Detail | Action |
|---|
Emission factors are illustrative approximations of national grid averages. A real engagement would use the client-approved factor set for each reporting year.
What this means for a data team
Fix with proof, flag without it
Values are only overwritten when a bill proves the right number. Everything else is flagged for a person to decide.
Audit-ready by default
The change log answers "why is this number different from what the hotel sent?" in one lookup.
Follow-up lists, not just dashboards
The invoice gap report and the list of properties to chase turn data checks into the team's daily work queue.
Rules you can explain
Every check is a readable rule with a tolerance, so clients and auditors can agree on it before the numbers are reported.