What a naive load gets wrong: traps in Canadian well files

November 2026Illustrative, not for reserves or investment decisions

Most of the work in oil and gas data is cleaning, not analysis. We counted what goes wrong when you open the Alberta, Saskatchewan and BC regulator files with a default spreadsheet or pandas.read_csv, and what the Tidywells loaders do about each one. These are counts from the files we downloaded in November 2026.

1. Codes lose their leading zeros

Many identifier and code columns are made only of digits with leading zeros. Spreadsheets and default CSV readers turn them into numbers and drop the zeros, which breaks matches and makes different codes look equal.

Alberta Well Infrastructure (681,275 rows), values that lose leading zeros:

• WellEventSequence: 496,347 of 681,275 values
• LinkedFacilityIdentifier: 184,728 of 235,289 values
• Field: 666,846 of 681,275 values
• WellStatusFluidCode: 612,102 of 681,275 values

Saskatchewan Well Infrastructure (159,645 rows):

• WellEventSequence: 134,055 of 159,645 values
• LinkedFacilityOperatorBAID: 2,698 of 80,057 values
• WellStatusFluidCode: 158,326 of 159,645 values
• WellStatusModeCode: 100,290 of 159,645 values

BC production file, 2025 onward (188,501 rows):

• Wa_num: 12,649 of 188,501 values
• Compltn_event_seq: 188,480 of 188,501 values
• Area_code: 753 of 188,501 values
• Formtn_code: 1,824 of 188,501 values

Tidywells reads every column as text and pads licence numbers to seven characters.

2. Repeated rows

If you count rows as wells, Alberta is off by 13,735. Tidywells keeps one row per well, the one with the latest status date.

3. Status and licence spelling

Alberta's licence status appears in 16 spellings that reduce to 8 real values after trimming and case-folding. Counting by raw text splits one status into several. A blank well status (121,982 Alberta wells, 18% of rows) is also common, and as the inventory post shows, some of those wells are producing.

4. Same word, different thing

• Units: liquids are in cubic metres, gas in thousands of cubic metres. Mixing them in one column is easy.
• Rates: Alberta and Saskatchewan give monthly volumes with no producing days; BC reports producing days, which changes the rate for wells that were down part of the month.
• Duplicate column names: the BC Well Index has 25 columns, with the names Fluid, Mode, Oper each appearing twice, so a plain read silently renames or overwrites one copy.
• "Oil" is not one thing: in Alberta it includes bitumen wells (about three quarters of the volume).

5. The good news

Linking works when you handle the zeros: 99.99% of Alberta well rows and 100% of Saskatchewan well rows matched a licence record, so the two-file join from well ID to licensee is reliable.

What this means in practice

• Read everything as text first; convert deliberately.
• De-duplicate by the latest status date before counting wells.
• Normalise status and licence text before grouping.
• Record units and day-count conventions next to every number.

Limits

• These are counts of problems we found, not a complete audit; there may be others.
• Row counts reflect the files as downloaded in November 2026.
• The leading-zero lists show the first columns of each file; others are also affected.

Reproduce it

Code: github.com/Jay-Kimani/TidyWells, posts/cache_matrix.py, posts/wells_study.py, posts/dq_study.py and posts/gen_posts2.py. Download the files yourself from Petrinex and the BC Energy Regulator; we do not host the data.

Illustrative only. Not for reserves reporting or investment decisions.

Contains information licensed from Petrinex public data (Alberta, Saskatchewan) and the BC Energy Regulator; Crown copyright of the provinces is acknowledged. Tidywells is not affiliated with any regulator.