Understanding API numbers

The API number is a well identifier standardised by the American Petroleum Institute and adopted by essentially every US state oil and gas regulator. In Texas it is the join key that makes the RRC's separate datasets into one dataset. It is also the source of more silent join failures than anything else in this data, because the number you have and the number in the file are frequently different lengths, differently punctuated, or genuinely different numbers for the same hole.

The anatomy

The full identifier is built in segments, each of which was added to the standard at a different point in history:

42  -  329  -  31234  -  01  -  00
^      ^       ^         ^      ^
|      |       |         |      +-- event / completion sequence  (API-14)
|      |       |         +--------- directional sidetrack / hole  (API-12)
|      |       +------------------- unique well number in county  (API-10)
|      +--------------------------- county code
+---------------------------------- state code: 42 = Texas

• State code, two digits. Texas is 42. It never varies, which is exactly why the RRC usually does not store it.
• County code, three digits. In Texas this segment matches the Census FIPS county code for that county under state FIPS 48 — Anderson is 001, Midland is 329, Reeves is 389. That correspondence is a Texas convenience, not a general rule; API county codes are assigned per state and do not track FIPS everywhere. EZRRC ships the crosswalk as a code set so you never have to assume.
• Unique number, five digits. Assigned sequentially within the county as wells are permitted. It carries no meaning beyond "the nth well identified in this county."
• Sidetrack / hole change, two digits. Distinguishes separate holes drilled from the same surface location — a sidetrack around stuck pipe, a re-drill, a lateral from a shared vertical section.
• Event sequence, two digits. Distinguishes completions or recompletions within a hole.

Ten digits identify a wellbore. Twelve identify a specific hole. Fourteen identify a specific completion event in that hole.

What the RRC actually stores

Here is the part that catches people out. The RRC's own files overwhelmingly store eight digits — county plus unique — and leave the 42 implied.

In the Full Wellbore Database the root record splits it into two fields: api_county (three digits) and api_unique (five). Neither is zero-padded in a way you can rely on if you read them as integers, which is why the upstream loader reconstructs the display form explicitly:

county = str(root.get('api_county', '')).zfill(3)
unique = str(root.get('api_unique', '')).zfill(5)
api_number = f"42-{county}-{unique}"

In the drilling permit file the API number appears as a single eight-character field, and the loader formats it the same way:

def apiNumberFormat(v):
    if not v or len(v) != 8:
        return None
    return '-'.join(['42', v[:3], v[-5:]])

Note the guard. A permit row whose API field is not exactly eight characters gets no API number at all — and that is correct behaviour, not a bug, because a permit can exist before an API number is assigned to it.

The two-digit sidetrack and event segments are handled differently again. The wellbore file keeps a per-completion api_suffix on each completion record, and the root record tracks the next available suffix and the next available hole change number. So Texas does model the API-12 and API-14 concepts; it just does not concatenate them into a fourteen-character string for you.

Practical rule: normalise to the digits, not the formatting. Strip everything that is not a digit, drop a leading 42 if the result is ten digits, and compare on the eight-digit core. Keep the suffix separately if you need completion-level grain.

import re

def api8(value: str) -> str | None:
    """Normalise any Texas API rendering to its 8-digit county+unique core."""
    digits = re.sub(r"\D", "", value or "")
    if len(digits) >= 10 and digits.startswith("42"):
        digits = digits[2:]
    if len(digits) < 8:
        return None
    return digits[:8]

Why the same well appears under different numbers

Three separate mechanisms produce this, and the wellbore file records all three explicitly.

Corrections. API numbers get assigned by people and sometimes get assigned wrongly — a well attributed to the wrong county, or two numbers issued for one hole. The wellbore root record carries error_api_assign_code and refer_correct_api_nbr: a flag saying this number is in error, and a pointer to the number you should use instead. If you join on API and ignore these, you will carry retired numbers forward forever.

Replaced placeholders. The root record also carries dummy_api_number and a date the dummy was replaced. When a well needed an identifier before a real one could be assigned, a placeholder was issued and later superseded. Historical records may still reference the placeholder.

Renumbering. previous_api_nbr records that this wellbore was previously known by another number. County boundary corrections and administrative reassignments both cause this.

Any of these can leave you with two rows that look like two wells and are one. Before you count wells, resolve the chain: if a root record has a correct-API pointer, follow it; if it has a previous number, treat both as aliases of the same physical bore.

Sidetracks are the opposite trap. A well with three sidetracks is genuinely three holes and should not be collapsed to one. Two rows with the same eight-digit core and different suffixes are different holes; two rows with different cores and a correction pointer between them are the same hole. Do not treat those cases the same way.

Joining on API across datasets

The datasets do not agree on representation, so the first thing any cross-dataset join needs is normalisation.

EZRRC publishes a normalised api_number in the 42-XXX-YYYYY form on every dataset where an API exists, alongside the original columns, so a cross-dataset join is a straight equality:

SELECT p.permit_no,
       p.issue_date,
       w.total_depth,
       w.wgs84_latitude,
       w.wgs84_longitude
  FROM texas.permits   p
  JOIN texas.wellbore  w ON w.api_number = p.api_number
 WHERE p.district = '08'
   AND p.issue_date >= DATE '2026-01-01';

Three things to expect from that join:

1. Permits with no match. A permit that was never drilled has no wellbore. Use a left join if you are measuring permitting activity rather than drilling.
2. Wellbores with no permit in the file. The permit files do not go back as far as the wellbore file. A well spudded in 1948 has a wellbore record and no permit row.
3. Multiple permits per wellbore. Amendments, re-entries and recompletions all generate permits against the same hole. The wellbore file's drilling-permit child record type exists precisely because the relationship is one-to-many.

Sanity checks worth running

Before you trust an API-keyed result, run these:

• Every Texas API core is eight digits. Anything else is a parsing error, usually a lost leading zero from reading the county code as an integer.
• The county segment should be a valid Texas county code. Values outside the county list mean you sliced the wrong columns out of a fixed-width record.
• Count distinct API cores and compare to your row count. A large gap means either duplicate rows or completion-grain data you thought was well-grain.
• Check for correction pointers before reporting a well count.

Next: districts and counties — the other geographic keys, and why the RRC's district numbering is not a county grouping you can derive.