Python: turn hourly demand into a checked daily series

Start with a small, reproducible task

The exercise converts a synthetic hourly demand series into a daily volume table while preserving evidence about data quality. It uses Python’s standard library, so no data-science packages are needed for this first workflow. You will produce daily_audit.csv and a console summary that can be checked against known defects in the source.

The dataset covers 56 days from 1 January 2026 in UTC. Each value is an average rate for one complete hour, expressed in m³/hour. This definition is essential: an instantaneous sensor reading or a cumulative meter total needs a different integration method. The supplied series does not represent measured customer use.

Create a working folder and run the exercise

1. Install a supported Python 3 release if it is not already available through your organisation’s approved environment.
2. Extract the practice pack into a working folder. Keep the supplied files together; the script locates the CSV relative to its own file.
3. Open a terminal in that folder and run python check_demand.py. On systems where the executable is named python3, use python3 instead.
4. Read the console summary and open daily_audit.csv in a table viewer. Keep a copy of the original source unchanged.
5. Compare the output with the expected results below, then read the implementation before modifying it for another dataset.

The script does not connect to a service, change the raw input or upload data. It writes a new CSV in the same folder. For real utility work, use approved data access and storage arrangements, and keep environment and dependency information alongside the analysis.

Understand why the raw row count is misleading

The raw file has 1,344 rows, exactly the apparent count for 56 × 24 hours. However, one hour on 11 January is missing and one hour on 6 January is duplicated. The errors cancel in the row count. Only a timestamp-based completeness check reveals the problem.

The script first checks timestamp alignment and numeric values. It then compares records with an explicitly configured study calendar, so missing intervals at the start or end can also be detected. Identical duplicate rows in this known fixture are logged and retained once. Conflicting duplicate values cause the script to stop.

In real data, duplicates can represent corrections, different sensors or repeated transmissions. Do not use a blanket keep-first policy without understanding the source. The example’s treatment is justified by the deliberately documented fixture, not by a general assumption about telemetry.

Convert rates into volume on the correct basis

For the supplied one-hour interval averages, daily ML = sum of 24 rates / 1,000.

A missing interval is not zero demand. The script leaves daily volume blank whenever the full 24-hour UTC day is unavailable. It records how many hours are present, allowing a reviewer to distinguish an incomplete day from a genuinely low-demand day.

If the data represents instantaneous readings, choose and justify a numerical integration method and the treatment of gaps. If it represents cumulative meter readings, calculate differences while checking resets, rollovers and meter replacements. Applying the interval-average formula to either source without adjustment produces an incorrect water balance.

UTC avoids daylight-saving changes in this fixture. For a Sydney operational-day report, convert timestamps to the required local timezone and define the day boundary. Some local days contain 23 or 25 hours. Do not force them to contain 24 observations or shift records without retaining the timezone meaning.

Read and reuse the complete script

The code separates parsing, uniqueness checks, calendar comparison and aggregation. It rejects non-finite or negative values and stops on conflicting duplicates. For another project, change the configured study interval and document the interpretation of the input rate before running it.

"""Audit interval-average hourly rates. Python standard library only."""
from pathlib import Path
from datetime import datetime,timedelta,timezone
from collections import defaultdict
import csv,math
root=Path(__file__).resolve().parent
values={};duplicates=[]
with (root/'demand_hourly_raw.csv').open() as f:
 for row in csv.DictReader(f):
  t=datetime.fromisoformat(row['timestamp_utc'])
  q=float(row['demand_m3_h'])
  if t.utcoffset() is None or t.minute or t.second or t.microsecond:
   raise ValueError('Timestamp must be an aligned, timezone-aware hour')
  if not math.isfinite(q) or q<0:raise ValueError('Invalid or negative demand needs investigation')
  t=t.astimezone(timezone.utc)
  if t in values:
   if values[t]!=q:raise ValueError(f'Conflicting duplicate: {t}')
   duplicates.append(t)
   continue  # Identical duplicate in this documented fixture only.
  values[t]=q
first=datetime(2026,1,1,tzinfo=timezone.utc)
expected=[first+timedelta(hours=h) for h in range(56*24)]
if any(t<expected[0] or t>expected[-1] for t in values):
 raise ValueError('Data outside the configured study interval')
missing=[t for t in expected if t not in values]
days=defaultdict(list)
for t in expected:days[t.date()].append(t)
output=[]
for day,hours in sorted(days.items()):
 present=[t for t in hours if t in values]
 complete=len(hours)==24 and len(present)==24
 # Each rate represents one complete hour. No interpolation of missing hours.
 volume_ml=sum(values[t] for t in present)/1000 if complete else ''
 output.append([day,len(present),complete,volume_ml])
with (root/'daily_audit.csv').open('w',newline='') as f:
 w=csv.writer(f);w.writerow(['date','hours_present','complete','volume_ml']);w.writerows(output)
print('Unique hours:',len(values))
print('Identical duplicates:',len(duplicates))
print('Missing hours:',len(missing))
print('Complete days:',sum(row[2] for row in output))
print('Missing timestamps:',[t.isoformat() for t in missing])

Check the expected outputs

The daily file has 56 rows even though only 55 daily volumes are complete. A downstream average must state whether it excludes the incomplete day or uses an explicitly justified estimate. Do not quietly average only the available hours and call the result a complete daily volume.

A failed audit is a useful result: it identifies the correction needed before modelling. Preserve the original data, correction reason and revised output. Re-running the same script on the same input should produce the same values.

Extend to a water balance or model-results workflow

For a zone water balance, align inlet flow, outlet transfer, storage change and customer consumption to the same interval and units. Assign sign conventions before merging. A storage-level series must be converted to volume through the relevant geometry before it can be included as a volume change.

For hydraulic results, use a composite key such as scenario, asset ID and timestamp. Check join cardinality so a duplicate asset row does not multiply every result. Keep missing results separate from zero pressure or zero demand. Export a compact exception report that an engineer can trace back to source records.

When the dataset grows, pandas can make tabular operations more convenient, but it does not remove the need to define grain, units, calendars and missing-data policy. Optimise for a clear, auditable transformation first.

Make automation reviewable

• Keep the raw input and record its source, extraction time, units and interval definition.
• Save the script, Python version, parameters and output together.
• Stop on material failures such as conflicting IDs, invalid units or impossible values.
• Report completeness and corrections alongside the engineering result.
• Review a small independent sample and a system-level total before using the output.

Continue to the forecasting tutorial only after the data basis is understood. Use Power BI to communicate checked results while keeping the calculations and source lineage visible.

Sources & further reading

• Python documentation ↗Python Software Foundation · Living documentation
  External source · Checked 24 September 2026
• Best Practice Modelling Guidelines ↗eWater · 2011
  External source · Checked 24 September 2026

Source findings are distinguished from editorial interpretation. Apply current local criteria and project evidence when making engineering decisions.