# Do the workbook's consumption estimates for recent years move independently of territorial ones?
# For each country, the ratio consumption/territorial year by year; a ratio frozen across years
# would mean those years are extrapolated from territorial emissions.
import sys, openpyxl
wb = openpyxl.load_workbook(sys.argv[1], read_only=True, data_only=True)
def sheet(name, header_row):
    rows = list(wb[name].iter_rows(values_only=True))
    names = rows[header_row]
    data = {}
    for r in rows[header_row + 1:]:
        if isinstance(r[0], (int, float)):
            data[int(r[0])] = {names[j]: r[j] for j in range(1, len(r)) if names[j] and isinstance(r[j], (int, float))}
    return data
terr = sheet("Territorial Emissions", 11); cons = sheet("Consumption Emissions", 8)
years = sorted(y for y in cons if y >= 2010)
print("last consumption year:", max(cons))
countries = [c for c in cons[2023] if c in terr.get(2023, {})]
print("countries with 2023 consumption:", len(countries))
import statistics
for y0, y1 in zip(years[:-1], years[1:]):
    changes = []
    for c in countries:
        try:
            r0 = cons[y0][c] / terr[y0][c]; r1 = cons[y1][c] / terr[y1][c]
            changes.append(abs(r1 / r0 - 1))
        except (KeyError, ZeroDivisionError): pass
    print(f"{y0}->{y1}: median |change in consumption/territorial ratio| {statistics.median(changes):.5f}, share unchanged (<1e-9) {sum(ch < 1e-9 for ch in changes)/len(changes):.3f}, n {len(changes)}")
for c in ("USA", "United Kingdom", "Germany", "China", "Bahrain", "Namibia", "Israel", "Sri Lanka"):
    print(c, [round(cons[y][c] / terr[y][c], 4) for y in range(2015, 2024) if c in cons[y] and c in terr[y]])