Exercise 4.7
Allocation Reconciliation
Field production is measured two ways that never quite agree. The fiscal meter at the facility gives the trusted monthly total. Per-well rates are allocated estimates that sum to something a few percent off. Reconciliation scales each well so the allocated rates add up to the fiscal truth, without changing any well's share of the total.
The starter gives you allocated (columns: well, month, oil_bopd) and fiscal (columns: month, fiscal_oil_bopd). Write two functions:
allocation_factor(allocated, fiscal): for each month, the scale
factor fiscal_total / allocated_total. Return a Series indexed by month. (groupby("month")["oil_bopd"].sum() gives the allocated totals.)
reconcile(allocated, fiscal): return a copy ofallocatedwith a new
reconciled_oil_bopd column: each well's allocated rate multiplied by its month's factor. After reconciliation, each month's reconciled rates must sum to that month's fiscal total.
Because every well in a month is scaled by the same factor, the wells' relative shares are preserved; you've corrected the total without re-allocating between wells.
Stuck? Reveal hints one at a time — they progress from nudge to near-solution.
visibilityReveal reference solutionexpand_more
Try solving it yourself first — the hints walk you through it. The solution below is one valid approach; yours may differ and still be correct.
import pandas as pd
allocated = pd.DataFrame([
{"well": "OD-001", "month": 1, "oil_bopd": 1000},
{"well": "OD-001", "month": 2, "oil_bopd": 950},
{"well": "OD-001", "month": 3, "oil_bopd": 900},
{"well": "OD-003", "month": 1, "oil_bopd": 800},
{"well": "OD-003", "month": 2, "oil_bopd": 780},
{"well": "OD-003", "month": 3, "oil_bopd": 760},
{"well": "OD-005", "month": 1, "oil_bopd": 1200},
{"well": "OD-005", "month": 2, "oil_bopd": 1150},
{"well": "OD-005", "month": 3, "oil_bopd": 1100},
])
fiscal = pd.DataFrame([
{"month": 1, "fiscal_oil_bopd": 3150.0},
{"month": 2, "fiscal_oil_bopd": 2995.2},
{"month": 3, "fiscal_oil_bopd": 2842.8},
])
def allocation_factor(allocated, fiscal):
allocated_total = allocated.groupby("month")["oil_bopd"].sum()
fiscal_total = fiscal.set_index("month")["fiscal_oil_bopd"]
return fiscal_total / allocated_total
def reconcile(allocated, fiscal):
out = allocated.copy()
factor = allocation_factor(allocated, fiscal)
out["reconciled_oil_bopd"] = out["oil_bopd"] * out["month"].map(factor)
return out
factor = allocation_factor(allocated, fiscal)
print("Allocation factors by month:")
print(factor)
reconciled = reconcile(allocated, fiscal)
print("\nMonthly reconciled totals (should equal fiscal):")
print(reconciled.groupby("month")["reconciled_oil_bopd"].sum())
lockCopying code is a Full Access feature.