Extra-Curricular Notes: Concepts Used in the Cleaning Notebook

This document explains concepts used in 01-clean-edm-data.ipynb ↗ that go beyond the DS105 course syllabus. These are included for reference and to justify the technical decisions made.

1. Reading Multiple Sheets from an Excel File

What the course taught

DS105 introduced pd.read_excel('file.xlsx') for loading a single sheet from an Excel file.

What we needed

The EDM files store each water company on a separate sheet within the same workbook. The default pd.read_excel call only reads the first sheet, silently ignoring the rest.

The solution: sheet_name=None

Passing sheet_name=None tells pandas to read every sheet in the file. Instead of returning a single DataFrame, it returns a dictionary where each key is a sheet name and each value is a DataFrame:

all_sheets = pd.read_excel('file.xlsx', sheet_name=None, header=1)
# Returns: {'Anglian Water': df1, 'Severn Trent Water': df2, ...}

We then loop through the dictionary and stack all the DataFrames using pd.concat:

frames = []
for sheet_name, df in all_sheets.items():
    frames.append(df)
result = pd.concat(frames, ignore_index=True)

Why ignore_index=True?

Each sheet's DataFrame has its own row index starting from 0. When stacking them, pandas would preserve these overlapping indices by default (so you'd have multiple rows all labelled row 0). ignore_index=True resets the index to a clean 0, 1, 2, ... sequence across the combined DataFrame.

2. The header Parameter in pd.read_excel

What it does

The header parameter tells pandas which row number to use as column names. Row numbers are zero-indexed, so:

• header=0 -> first row of the sheet becomes column names
• header=1 -> second row becomes column names (first row is skipped)
• header=2 -> third row becomes column names

Why we needed it

The EA formats their EDM Excel files with a title row at the top (e.g. "EDM Annual Return 2021") before the actual column headers. So the real column names sit on row 2 (index 1), not row 1 (index 0). Using header=0 would make the title row the column names, and header=1 correctly picks up the real headers.

The 2025 complication

The 2025 file added an extra note row before the column headers, pushing them down to row 3 (index 2). This required header=2 -- but since we manually deleted the note row, header=1 works for all years.

3. The OS National Grid (OSGB36) Coordinate System

What is it?

The Ordnance Survey National Grid is the official coordinate system used on British maps. Rather than using latitude and longitude, it divides Great Britain into a grid and describes positions using easting (how far east) and northing (how far north), measured in metres from a fixed origin point off the southwest coast of England.

A grid reference like SP6419046470 encodes this position compactly:

• The first two letters (SP) identify which 100km x 100km grid square the point is in
• The remaining digits give the easting and northing within that square

Why not just use lat/lon?

OSGB36 was designed for mapping paper maps of Britain before GPS existed. It minimises distortion for the specific shape of Great Britain. The EA and Ordnance Survey still use it for all official UK datasets.

The two-letter prefix system

The letters encode a hierarchical grid:

• The first letter (S, T, N, O, H) identifies a 500km x 500km square
• The second letter (A-Z, skipping I) identifies a 100km x 100km square within that

For example:

• S covers most of southern England and Wales (easting base = 0)
• P within S adds 400,000m east and 200,000m north
• So SP has a base easting of 400,000m and base northing of 200,000m

The numeric digits after the letters are split in half -- the first half is the easting offset, the second half is the northing offset, both padded to 5 digits:

SP6419046470
  └- 64190 -> easting offset -> total easting = 400000 + 64190 = 464190
       └- 46470 -> northing offset -> total northing = 200000 + 46470 = 246470

4. WGS84 -- The Standard GPS Coordinate System

What is it?

WGS84 (World Geodetic System 1984) is the coordinate system used by GPS, Google Maps, and almost all modern mapping and web APIs. It describes positions on Earth using latitude (degrees north/south of the equator) and longitude (degrees east/west of the prime meridian).

Why does it differ from OSGB36?

WGS84 and OSGB36 are based on different reference ellipsoids -- mathematical models of the shape of the Earth. The Earth is not a perfect sphere; it bulges slightly at the equator. OSGB36 uses the Airy 1830 ellipsoid, which was the best estimate of Earth's shape when British mapping began. WGS84 uses a more modern and accurate global ellipsoid.

Because they use different ellipsoids with different origins, the same physical point on Earth has slightly different coordinates in each system -- typically offset by around 100 metres in the UK.

Why we need to convert

The EA Water Quality API expects coordinates in WGS84. If we pass OSGB36 easting/northing directly (even converted to degrees), the API would look for sensors in the wrong location.

5. Why the Linear Approximation Fails

What was tried first

A simple linear formula was initially used to convert easting/northing to lat/lon:

latitude  = (northing - 100000) / 111320 + 49.0
longitude = (easting - 400000) / (111320 * cos(radians(latitude))) + (-2.0)

This looks plausible -- it assumes 1 degree of latitude ~= 111,320 metres -- but it produces results that are up to 200km off.

Why it fails

The formula makes two incorrect assumptions:

1. The OS grid is a linear rescaling of lat/lon. It is not. The OS grid uses a Transverse Mercator projection which introduces non-linear distortion, especially away from the central meridian at 2 degrees W. The further a point is from 2 degrees W, the more the grid stretches.

2. OSGB36 and WGS84 share the same origin. They don't. As explained above, they use different ellipsoids with different reference points. No linear formula can account for this -- you need the Helmert transformation.

The test that revealed it

SP6419046470 is a discharge point in Northamptonshire (around 52.1 degrees N). The linear formula returned 50.3 degrees N -- placing it near Southampton on the south coast. This 200km error would have matched sewage discharge points to water quality sensors in entirely the wrong river catchments.

6. The OS Transverse Mercator Projection

What is a map projection?

The Earth is a three-dimensional sphere but maps are flat. A projection is a mathematical method for converting 3D coordinates (lat/lon) to 2D coordinates (easting/northing) while minimising distortion. Every projection distorts something -- area, shape, distance, or direction -- because you cannot flatten a sphere without stretching or cutting it.

What is Transverse Mercator?

The OS National Grid uses a Transverse Mercator projection, which wraps a cylinder around the Earth touching at the central meridian (2 degrees W for Great Britain). Points are projected outward onto the cylinder, then the cylinder is unrolled flat.

This projection preserves shape (it is conformal) but distorts distance and area away from the central meridian. For Great Britain, the distortion is small enough to be acceptable for mapping.

Converting back (inverse projection)

Going from easting/northing back to lat/lon requires the inverse Transverse Mercator formula. This is an iterative calculation -- there is no direct algebraic solution, so the formula runs in a loop, refining its estimate of latitude until it converges to the correct answer. Ten iterations is sufficient for metre-level accuracy anywhere in Great Britain.

7. The Helmert Transformation

What is it?

A Helmert transformation (also called a 7-parameter similarity transformation) is a method for converting coordinates from one geodetic datum to another. It accounts for differences in:

• The origin of the two coordinate systems (3 translation parameters: tx, ty, tz in metres)
• The orientation of the axes (3 rotation parameters: rx, ry, rz in arcseconds)
• The scale (1 scale factor: s in parts per million)

How it works

The transformation first converts OSGB36 lat/lon to 3D Cartesian coordinates (x, y, z in metres from the Earth's centre), applies the 7 parameters, then converts the shifted Cartesian coordinates back to WGS84 lat/lon:

OSGB36 lat/lon -> 3D Cartesian (x, y, z)
                       ↓
              Apply 7 Helmert parameters
                       ↓
              Shifted (x2, y2, z2)
                       ↓
              WGS84 lat/lon

The published parameters for Great Britain

The Ordnance Survey publishes the official Helmert parameters for converting between OSGB36 and WGS84 in Great Britain. These are the values used in the notebook:

These are not arbitrary -- they were determined by the OS through precise geodetic surveys comparing the two systems at hundreds of points across Great Britain.

8. Data Validation for Grid References

Why validation is needed

Real-world administrative datasets like the EDM returns are entered manually by water companies. This introduces occasional formatting errors -- for example, one row contained the string 5047635589 / in the grid reference column instead of a valid OS reference. Passing this to the parsing functions caused a ValueError that crashed the notebook.

The validation rule

A valid OS National Grid reference always follows this structure:

• Starts with exactly 2 letters (the grid square prefix)
• Followed by an even number of digits (the easting and northing offsets, split in half)
• No spaces, slashes, or other characters

Valid examples: SP6419046470, TQ301805, NY123456 Invalid examples: 5047635589 /, UNKNOWN, NaN

The approach

Rather than letting errors crash the notebook, the is_valid_grid_ref function checks the format and returns None for invalid values. After conversion, any row with None easting is dropped. This is preferable to a try/except block because it makes the validation logic explicit and easy to read.