💧 Water Use & Abstraction — Northern Kazakhstan Water Management Basins, 2004–2023¶

Source notebook for the portal's interactive dashboard (water_use.html, water_use_ru.html, water_use_kz.html).

This notebook:

  1. loads the basin-level water-use statistics (input_data/water_use/wateruse_masterTable.xlsx),
  2. validates the table (balance identities, totals, gaps),
  3. converts units to million m³ (mln m³) and groups the 21 water-use types into 9 sectors,
  4. joins the data to the Water management basins boundaries (input_data/Water management basins.geojson),
  5. explores the most important indicators — spatial pattern, time series, sector structure, water sources, efficiency and losses, trends,
  6. exports the data that drives the web dashboard (input_data/water_use/water_use_dashboard.json) plus tidy CSV / Excel tables.

Re-run the whole notebook (Kernel → Restart & Run All) after editing the master table — the dashboard picks up the new JSON automatically.

Units. The master table is in thousand m³; values are divided by 1 000 to obtain mln m³ (1 000 mln m³ = 1 km³).

In [1]:
from pathlib import Path
import json

import numpy as np
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from IPython.display import HTML, Markdown, display

pd.set_option('display.float_format', '{:,.2f}'.format)

ROOT     = Path('..')                                     # portal root (notebook lives in notebooks/)
SRC_XLSX = ROOT / 'input_data/water_use/wateruse_masterTable.xlsx'
WMB_GEO  = ROOT / 'input_data/Water management basins.geojson'
OUT_DIR  = ROOT / 'input_data/water_use'

PALETTE = ['#0e1c36', '#1a7f6e', '#c9a84c', '#3182bd', '#cb4b16', '#6a51a3', '#2ca25f', '#8c6d31', '#969696']

def show(fig, height=None):
    '''Render a Plotly figure so it stays interactive both in Jupyter and in the exported HTML report.'''
    fig.update_layout(template='plotly_white', font_family='Roboto, Arial, sans-serif',
                      margin=dict(l=40, r=20, t=60, b=40), height=height)
    display(HTML(fig.to_html(include_plotlyjs='cdn', full_html=False,
                             config={'displaylogo': False, 'responsive': True})))

1. Load the master table¶

One row per basin × year × type of water use; 18 numeric columns describe abstraction (by source), use (by source), discharge, non-returnable consumption and transport losses.

In [2]:
raw = pd.read_excel(SRC_XLSX, engine='openpyxl')

COLS = {  # short code -> original column
    'W':    'Total water withdrawal',
    'SUR':  'Water intake_Surface (including water received from another country)_Total',
    'SEA':  'Water intake_Surface (including water received from other countries)_Of which sea water',
    'UND':  'Water intake_Underground and mine_Total',
    'MINE': 'Water intake_Underground and mine_Of which mine-mine',
    'COL':  'Water intake_Collector and drainage',
    'WW':   'Water intake_Wastewater',
    'U':    'Total water used',
    'D':    'Total water discharge',
    'DWB':  'Water disposal_Incl. to water bodies',
    'NR':   'Non-returnable water consumption',
    'TL':   'Transport losses',
}
BASIN_COL, TYPE_COL = 'Water management basin', 'Types of water use'

print(f'{len(raw):,} rows × {raw.shape[1]} columns')
print('Basins :', ', '.join(raw[BASIN_COL].unique()))
print('Years  :', raw['Year'].min(), '–', raw['Year'].max(), f"({raw['Year'].nunique()} years)")
print('Types  :', raw[TYPE_COL].nunique())
raw.head()
2,300 rows × 21 columns
Basins : Ertis Basin, Esil Basin, Zhayik-Caspian, Nura-Sarysu, Tobol-Torgai
Years  : 2004 – 2023 (20 years)
Types  : 23
Out[2]:
Water management basin Types of water use Year Total water withdrawal Water intake_Surface (including water received from another country)_Total Water intake_Surface (including water received from other countries)_Of which sea water Water intake_Underground and mine_Total Water intake_Underground and mine_Of which mine-mine Water intake_Collector and drainage Water intake_Wastewater ... Used_surface_Total Used_surface_Of which sea Used_underground_Total Used_underground_Of which mine-mine Used_Collector-drainage Used_Waste Total water discharge Water disposal_Incl. to water bodies Non-returnable water consumption Transport losses
0 Ertis Basin Household and drinking 2004 229,732.38 155,026.66 0.00 74,705.73 0.00 0.00 0.00 ... 125,394.07 0.00 53,642.72 0.00 0.00 0.00 43,602.46 10,856.00 218,876.38 50,695.59
1 Ertis Basin Production facilities 2004 1,695,083.52 1,628,900.21 0.00 65,990.31 1,799.40 193.00 0.00 ... 1,607,784.06 0.00 61,671.49 1,799.40 193.00 0.00 1,525,102.94 1,495,091.64 199,991.88 25,434.97
2 Ertis Basin Agriculture, total 2004 1,037,166.03 980,776.93 0.00 56,389.10 0.00 0.00 0.00 ... 945,741.20 0.00 48,026.10 0.00 0.00 0.00 109.00 0.00 1,037,166.03 43,398.73
3 Ertis Basin Agriculture(agricultural water supply) 2004 29,133.00 6,797.50 0.00 22,335.50 0.00 0.00 0.00 ... 6,797.50 0.00 22,331.50 0.00 0.00 0.00 109.00 0.00 29,133.00 4.00
4 Ertis Basin Agriculture (regular irrigation) 2004 117,912.72 114,194.92 0.00 3,717.80 0.00 0.00 0.00 ... 85,907.40 0.00 2,591.80 0.00 0.00 0.00 0.00 0.00 117,912.72 29,413.52

5 rows × 21 columns

2. Validate the table¶

The checks below must pass before anything is exported. They confirm that

  • there are no missing or negative values, duplicates or basin × type × year gaps,
  • every of which column (sea water, mine water, discharge to water bodies) is within its total,
  • withdrawal = surface + underground + collector-drainage + wastewater intake,
  • the TOTAL row equals the sum of the individual water-use types and Agriculture, total equals its sub-types — so those two aggregate rows are dropped to avoid double counting.
In [3]:
num_cols = [c for c in raw.columns if c not in (BASIN_COL, TYPE_COL, 'Year')]
close = lambda a, b: np.isclose(a, b, rtol=1e-4, atol=1.0)    # 1 thousand m³ tolerance

assert raw[num_cols].notna().all().all(),  'missing values'
assert (raw[num_cols] >= 0).all().all(),   'negative values'
assert not raw.duplicated([BASIN_COL, TYPE_COL, 'Year']).any(), 'duplicate rows'
assert len(raw) == raw[BASIN_COL].nunique() * raw['Year'].nunique() * raw[TYPE_COL].nunique(), 'basin × type × year gaps'
assert raw['Year'].nunique() == raw['Year'].max() - raw['Year'].min() + 1, 'missing years'
for part, whole in (('SEA', 'SUR'), ('MINE', 'UND'), ('DWB', 'D')):     # 'of which' columns never exceed their total
    assert (raw[COLS[part]] <= raw[COLS[whole]] + 1).all(), f'{part} > {whole}'
assert close(raw[COLS['W']], raw[COLS['SUR']] + raw[COLS['UND']] + raw[COLS['COL']] + raw[COLS['WW']]).all()

AGGREGATE_TYPES = ['TOTAL', 'Agriculture, total']
by_type = raw.pivot_table(index=[BASIN_COL, 'Year'], columns=TYPE_COL, values=COLS['W'], aggfunc='sum')
components = [t for t in by_type.columns if t not in AGGREGATE_TYPES]
agri_parts = [t for t in components if t.lower().startswith('agriculture')]
assert close(by_type['TOTAL'], by_type[components].sum(axis=1)).all()
assert close(by_type['Agriculture, total'], by_type[agri_parts].sum(axis=1)).all()
print('✅ all validation checks passed')
✅ all validation checks passed

3. Tidy data, units and sectors¶

  • Aggregate rows are removed and values converted from thousand m³ to mln m³.
  • Basins are linked to the BASIN_ID of the Water management basins layer (5 of its 8 basins are covered by the statistics).
  • The 21 water-use types are grouped into 9 sectors. Labels are kept in English, Russian and Kazakh for the trilingual dashboard.
In [4]:
BASINS = {  # master-table name -> WMB layer id + labels
    'Ertis Basin':    dict(id='WMB_00006', key='ertis',  en='Ertis',          ru='Ертис (Иртыш)', kz='Ертіс'),
    'Esil Basin':     dict(id='WMB_00005', key='yesil',  en='Yesil',          ru='Есиль',         kz='Есіл'),
    'Nura-Sarysu':    dict(id='WMB_00004', key='nura',   en='Nura-Sarysu',    ru='Нура-Сарысу',   kz='Нұра-Сарысу'),
    'Tobol-Torgai':   dict(id='WMB_00001', key='tobyl',  en='Tobyl-Torgai',   ru='Тобол-Торгай',  kz='Тобыл-Торғай'),
    'Zhayik-Caspian': dict(id='WMB_00008', key='zhaiyk', en='Zhaiyk-Caspian', ru='Жайык-Каспий',  kz='Жайық-Каспий'),
}

SECTORS = {  # key -> labels
    'industry':   dict(en='Industry',                    ru='Промышленность',              kz='Өнеркәсіп',                          color='#0e1c36'),
    'household':  dict(en='Household & drinking',        ru='Хозяйственно-питьевые нужды', kz='Шаруашылық-ауыз су',                 color='#3182bd'),
    'irrigation': dict(en='Irrigation',                  ru='Орошение',                    kz='Суару',                              color='#2ca25f'),
    'rural':      dict(en='Rural water supply',          ru='Сельхозводоснабжение',        kz='Ауыл шаруашылығын сумен жабдықтау',  color='#8c6d31'),
    'fisheries':  dict(en='Fisheries & ponds',           ru='Прудово-рыбное хозяйство',    kz='Тоған-балық шаруашылығы',            color='#1a7f6e'),
    'reservoirs': dict(en='Reservoirs & water levels',   ru='Водохранилища и уровни',      kz='Су қоймалары мен деңгейлер',         color='#6a51a3'),
    'releases':   dict(en='Sanitary & other releases',   ru='Санитарные попуски и сбросы', kz='Санитариялық су жіберу және ағызу', color='#c9a84c'),
    'transfers':  dict(en='Transfers & transit',         ru='Передача и транзит',          kz='Беру және транзит',                  color='#cb4b16'),
    'other':      dict(en='Other needs',                 ru='Прочие нужды',                kz='Басқа қажеттіліктер',                color='#969696'),
}

TYPES = {  # master-table name -> sector + labels
    'Production facilities':                  dict(key='production',        sector='industry',   en='Production facilities',              ru='Производственные нужды',                 kz='Өндірістік қажеттіліктер'),
    'Maintaining reservoir pressure':         dict(key='reservoir_pressure',sector='industry',   en='Oil-reservoir pressure maintenance', ru='Поддержание пластового давления',        kz='Қабат қысымын ұстау'),
    'Household and drinking':                 dict(key='household',         sector='household',  en='Household and drinking',             ru='Хозяйственно-питьевые нужды',            kz='Шаруашылық-ауыз су қажеттіліктері'),
    'Agriculture (regular irrigation)':       dict(key='regular_irrigation',sector='irrigation', en='Regular irrigation',                 ru='Регулярное орошение',                    kz='Тұрақты суару'),
    'Agriculture(estuary irrigation)':        dict(key='estuary_irrigation',sector='irrigation', en='Estuary (liman) irrigation',         ru='Лиманное орошение',                      kz='Лиманды суару'),
    'Agriculture (irrigation of pastures)':   dict(key='pasture_watering',  sector='irrigation', en='Pasture watering',                   ru='Обводнение пастбищ',                     kz='Жайылымдарды суландыру'),
    'Agriculture(hayfield bay)':              dict(key='hayfield_flooding', sector='irrigation', en='Hayfield flooding',                  ru='Залив сенокосов',                        kz='Шабындықтарды суға бастыру'),
    'Agriculture(agricultural water supply)': dict(key='rural_supply',      sector='rural',      en='Agricultural water supply',          ru='Сельскохозяйственное водоснабжение',     kz='Ауыл шаруашылығын сумен жабдықтау'),
    'Pond and fishing industry':              dict(key='fisheries',         sector='fisheries',  en='Pond and fishing industry',          ru='Прудово-рыбное хозяйство',               kz='Тоған-балық шаруашылығы'),
    'Maintaining set horizons':               dict(key='set_levels',        sector='reservoirs', en='Maintaining set water levels',       ru='Поддержание заданных горизонтов',        kz='Белгіленген деңгейлерді ұстау'),
    'Filling of bulk reservoirs':             dict(key='reservoir_filling', sector='reservoirs', en='Filling of off-stream reservoirs',   ru='Наполнение наливных водохранилищ',       kz='Құйма су қоймаларын толтыру'),
    'Sanitary permits':                       dict(key='sanitary',          sector='releases',   en='Sanitary releases',                  ru='Санитарные попуски',                     kz='Санитариялық жіберулер'),
    'Resetting the SDR without using':        dict(key='release_unused',    sector='releases',   en='Release without use',                ru='Сброс без использования',                kz='Пайдаланбай ағызу'),
    'Channel flushing':                       dict(key='channel_flushing',  sector='releases',   en='Channel flushing',                   ru='Промывка каналов',                       kz='Каналдарды шаю'),
    'Transferred to another (BWMA)':          dict(key='to_other_basin',    sector='transfers',  en='Transferred to another basin',       ru='Передано в другую БВИ',                  kz='Басқа алапқа берілді'),
    'Transferred to another country':         dict(key='to_other_country',  sector='transfers',  en='Transferred to another country',     ru='Передано в другую страну',               kz='Басқа елге берілді'),
    'Transit water':                          dict(key='transit',           sector='transfers',  en='Transit water',                      ru='Транзитная вода',                        kz='Транзиттік су'),
    'Forced water intake into canals':        dict(key='forced_intake',     sector='transfers',  en='Forced intake into canals',          ru='Принудительный водозабор в каналы',      kz='Каналдарға мәжбүрлі су алу'),
    'Other needs':                            dict(key='other_needs',       sector='other',      en='Other needs',                        ru='Прочие нужды',                           kz='Басқа қажеттіліктер'),
    'Watering of green spaces':               dict(key='green_spaces',      sector='other',      en='Watering of green spaces',           ru='Полив зелёных насаждений',               kz='Жасыл желектерді суару'),
    'Discharge of sewage drainage waters':    dict(key='sewage_drainage',   sector='other',      en='Discharge of sewage & drainage water', ru='Сброс сточно-дренажных вод',          kz='Сарқынды-дренаждық суларды ағызу'),
}

df = (raw[~raw[TYPE_COL].isin(AGGREGATE_TYPES)]
      .rename(columns={v: k for k, v in COLS.items()})
      [[BASIN_COL, TYPE_COL, 'Year', *COLS]])
assert set(df[TYPE_COL]) == set(TYPES), set(df[TYPE_COL]) ^ set(TYPES)
assert set(df[BASIN_COL]) == set(BASINS)

df[list(COLS)] = df[list(COLS)] / 1_000                       # thousand m³ -> mln m³
df['basin']  = df[BASIN_COL].map(lambda b: BASINS[b]['en'])
df['basin_id'] = df[BASIN_COL].map(lambda b: BASINS[b]['id'])
df['type']   = df[TYPE_COL].map(lambda t: TYPES[t]['en'])
df['sector'] = df[TYPE_COL].map(lambda t: SECTORS[TYPES[t]['sector']]['en'])
df = df.rename(columns={'Year': 'year'})

BASIN_ORDER  = [b['en'] for b in BASINS.values()]
SECTOR_ORDER = [s['en'] for s in SECTORS.values()]
SECTOR_COLORS = {s['en']: s['color'] for s in SECTORS.values()}
YEARS = sorted(df['year'].unique())
print(f'{len(df):,} tidy rows · {df.basin.nunique()} basins · {df.type.nunique()} water-use types · {len(SECTORS)} sectors')
2,100 tidy rows · 5 basins · 21 water-use types · 9 sectors

3a. Data-quality flags¶

Basin-years whose total withdrawal or sea-water intake differs by more than 40 % from the median of the surrounding ±2 years. They are kept in all results (they are what the statistics report) but should be checked against the original basin inspection reports before drawing conclusions.

In [5]:
flags = []
for col, name in (('W', 'Total withdrawal'), ('SEA', 'Sea-water intake')):
    table = df.groupby(['basin', 'year'])[col].sum().unstack('basin')[BASIN_ORDER]
    for b in BASIN_ORDER:
        s = table[b]
        for i, y in enumerate(YEARS):
            ref = s.iloc[[j for j in range(max(0, i - 2), min(len(s), i + 3)) if j != i]].median()
            if ref > 1 and abs(s.iloc[i] / ref - 1) > 0.40:     # ref > 1 mln m³ ignores near-zero series
                flags.append({'indicator': name, 'basin': b, 'year': y, 'value, mln m³': s.iloc[i],
                              'median of ±2 yrs': ref, 'deviation, %': 100 * (s.iloc[i] / ref - 1)})
flags = pd.DataFrame(flags)
display(flags.round(1) if len(flags) else Markdown('No outlying basin-years.'))
indicator basin year value, mln m³ median of ±2 yrs deviation, %
0 Total withdrawal Yesil 2015 415.20 211.60 96.20
1 Total withdrawal Yesil 2016 197.10 348.60 -43.50
2 Total withdrawal Zhaiyk-Caspian 2007 2,156.40 1,232.00 75.00
3 Total withdrawal Zhaiyk-Caspian 2008 309.30 2,105.40 -85.30
4 Sea-water intake Zhaiyk-Caspian 2008 8.20 851.20 -99.00

4. Key figures¶

Annual totals for the five basins combined, and the long-term picture per basin.

In [6]:
annual = df.groupby('year')[['W', 'U', 'D', 'NR', 'TL']].sum()
annual['efficiency_%'] = 100 * annual['U'] / annual['W']

first, last = YEARS[0], YEARS[-1]
kpi = pd.DataFrame({
    'Indicator': ['Total withdrawal', 'Water used', 'Discharge', 'Non-returnable consumption', 'Transport losses'],
    f'{first}': annual.loc[first, ['W', 'U', 'D', 'NR', 'TL']].values,
    f'{last}':  annual.loc[last,  ['W', 'U', 'D', 'NR', 'TL']].values,
    'Mean 2004–2023': annual[['W', 'U', 'D', 'NR', 'TL']].mean().values,
})
kpi['Change, %'] = 100 * (kpi[f'{last}'] / kpi[f'{first}'] - 1)
display(Markdown(f'**All five basins, mln m³/year.** Water-use efficiency (used ÷ withdrawn) in {last}: '
                 f"**{annual.loc[last, 'efficiency_%']:.1f} %**"))
display(kpi.set_index('Indicator'))

per_basin = (df.groupby(['basin', 'year'])[['W', 'U', 'D', 'NR', 'TL']].sum()
               .groupby('basin').agg(['mean', 'min', 'max']).reindex(BASIN_ORDER))
display(Markdown('**Per basin, mln m³/year (2004–2023)**'))
display(per_basin.round(1))

All five basins, mln m³/year. Water-use efficiency (used ÷ withdrawn) in 2023: 93.1 %

2004 2023 Mean 2004–2023 Change, %
Indicator
Total withdrawal 6,382.64 8,817.63 7,688.16 38.15
Water used 5,928.56 8,204.97 7,187.92 38.40
Discharge 3,064.18 5,328.62 4,659.91 73.90
Non-returnable consumption 3,708.65 3,777.50 3,474.65 1.86
Transport losses 454.35 318.03 342.23 -30.00

Per basin, mln m³/year (2004–2023)

W U D NR TL
mean min max mean min max mean min max mean min max mean min max
basin
Ertis 3,600.00 3,064.50 4,064.80 3,375.90 2,770.60 3,813.80 2,133.90 1,696.70 2,458.00 1,553.10 835.90 1,837.90 94.20 48.50 181.20
Yesil 290.10 197.10 461.90 258.40 158.90 433.40 113.10 40.70 358.20 260.90 188.40 418.90 30.90 20.40 49.30
Nura-Sarysu 1,517.80 1,279.80 1,811.80 1,452.50 1,181.50 1,723.60 1,243.80 1,008.80 1,490.90 362.70 272.10 413.70 52.80 20.40 110.80
Tobyl-Torgai 139.90 104.00 172.60 114.70 79.40 156.80 71.10 12.40 114.60 117.30 103.30 138.70 11.00 6.80 17.80
Zhaiyk-Caspian 2,140.30 309.30 3,151.10 1,986.40 260.00 2,942.20 1,098.10 51.50 1,472.60 1,180.60 284.50 1,837.60 153.30 49.30 267.90

5. Spatial pattern — water withdrawal by water management basin¶

Choropleth on the Water management basins boundaries; use the slider (or ▶) to step through the years. Southern basins without statistics are shown in grey.

In [7]:
from shapely.geometry import mapping, shape

wmb = json.loads(WMB_GEO.read_text(encoding='utf-8'))
covered = {b['id'] for b in BASINS.values()}
print('WMB layer:', ', '.join(f"{f['properties']['BASIN_ID']} {f['properties']['BASIN_NAME']}" for f in wmb['features']))
# light geometry for the notebook figure (the dashboard uses the full-resolution layer)
wmb_light = {'type': 'FeatureCollection', 'features': [
    {'type': 'Feature', 'properties': f['properties'],
     'geometry': mapping(shape(f['geometry']).simplify(0.02, preserve_topology=True))} for f in wmb['features']]}

by_basin_year = df.groupby(['basin_id', 'basin', 'year'], as_index=False)[['W', 'U', 'D', 'NR', 'TL']].sum()
piv_w = by_basin_year.pivot(index='year', columns='basin_id', values='W')
ids = [b['id'] for b in BASINS.values()]
names = [b['en'] for b in BASINS.values()]
missing = [f['properties'] for f in wmb['features'] if f['properties']['BASIN_ID'] not in covered]

fig = go.Figure([
    go.Choropleth(geojson=wmb_light, featureidkey='properties.BASIN_ID', locations=ids, z=piv_w.loc[YEARS[0], ids],
                  text=names, zmin=0, zmax=float(piv_w.values.max()), colorscale='Blues',
                  colorbar_title='mln m³', hovertemplate='%{text}<br>%{z:,.0f} mln m³<extra></extra>'),
    go.Choropleth(geojson=wmb_light, featureidkey='properties.BASIN_ID', locations=[m['BASIN_ID'] for m in missing],
                  z=[0] * len(missing), colorscale=[[0, '#e6e6e6'], [1, '#e6e6e6']], showscale=False,
                  text=[m['BASIN_NAME'] for m in missing], hovertemplate='%{text}<br>no statistics<extra></extra>'),
])
steps = [dict(label=str(y), method='update',
              args=[{'z': [piv_w.loc[y, ids].tolist()]}, {'title': f'Total water withdrawal by water management basin, {y} (mln m³)'}],
              ) for y in YEARS]
for s in steps:
    s['args'].append([0])                                   # restyle only the data trace
fig.update_layout(title=f'Total water withdrawal by water management basin, {YEARS[0]} (mln m³)',
                  sliders=[dict(active=0, steps=steps, currentvalue=dict(prefix='Year: '), pad=dict(t=30))])
fig.update_geos(fitbounds='locations', visible=False)
show(fig, height=600)
WMB layer: WMB_00001 Tobyl-Torgai, WMB_00002 Aral-Syr Darya, WMB_00003 Shu-Talas, WMB_00004 Nura-Sarysu, WMB_00005 Yesil, WMB_00006 Ertis, WMB_00007 Balkhash-Alakol, WMB_00008 Zhaiyk-Caspian

6. Time series — withdrawal, use and discharge¶

Pick the indicator from the dropdown. The dashed line is the 3-year rolling mean of the five-basin total.

In [8]:
LABEL = {'W': 'Total withdrawal', 'U': 'Water used', 'D': 'Discharge',
         'NR': 'Non-returnable consumption', 'TL': 'Transport losses'}
fig = go.Figure()
buttons = []
for i, (k, name) in enumerate(LABEL.items()):
    piv = by_basin_year.pivot(index='year', columns='basin', values=k)[BASIN_ORDER]
    for j, b in enumerate(BASIN_ORDER):
        fig.add_trace(go.Scatter(x=piv.index, y=piv[b], name=b, mode='lines+markers', visible=(i == 0),
                                 stackgroup=f's{i}', line=dict(color=PALETTE[j], width=1.5)))
    fig.add_trace(go.Scatter(x=piv.index, y=piv.sum(1).rolling(3, min_periods=1).mean(), visible=(i == 0),
                             name='Total · 3-yr mean', line=dict(color='#c9a84c', dash='dash', width=2.5)))
    vis = [False] * (len(LABEL) * (len(BASIN_ORDER) + 1))
    vis[i * (len(BASIN_ORDER) + 1):(i + 1) * (len(BASIN_ORDER) + 1)] = [True] * (len(BASIN_ORDER) + 1)
    buttons.append(dict(label=name, method='update', args=[{'visible': vis}, {'title': f'{name} by basin (mln m³, stacked)'}]))
fig.update_layout(title='Total withdrawal by basin (mln m³, stacked)', hovermode='x unified', yaxis_title='mln m³',
                  updatemenus=[dict(buttons=buttons, x=0, y=1.15, xanchor='left')])
show(fig, height=480)

7. Sector structure¶

Which uses dominate abstraction? Treemap of the mean annual withdrawal (basin → sector → water-use type), followed by the sector time series.

In [9]:
mean_type = (df.groupby(['basin', 'sector', 'type'], as_index=False)['W'].sum()
               .assign(W=lambda d: d['W'] / len(YEARS)).query('W > 0'))
fig = px.treemap(mean_type, path=[px.Constant('5 basins'), 'basin', 'sector', 'type'], values='W',
                 color='sector', color_discrete_map={**SECTOR_COLORS, '(?)': '#f0f3f8'})
fig.update_traces(hovertemplate='%{label}<br>%{value:,.1f} mln m³/yr<br>%{percentRoot:.1%} of total<extra></extra>')
fig.update_layout(title='Mean annual water withdrawal 2004–2023 — basin › sector › type (mln m³/yr)')
show(fig, height=620)

sector_year = df.groupby(['year', 'sector'], as_index=False)['W'].sum()
fig = px.area(sector_year, x='year', y='W', color='sector', category_orders={'sector': SECTOR_ORDER},
              color_discrete_map=SECTOR_COLORS, labels={'W': 'mln m³', 'year': ''})
fig.update_layout(title='Withdrawal by sector, five basins (mln m³)', hovermode='x unified')
show(fig, height=460)

share = (df.groupby(['basin', 'sector'])['W'].sum().unstack().reindex(BASIN_ORDER)[SECTOR_ORDER])
display(Markdown('**Sector share of 2004–2023 withdrawal, % per basin**'))
display((100 * share.div(share.sum(1), axis=0)).round(1))

Sector share of 2004–2023 withdrawal, % per basin

sector Industry Household & drinking Irrigation Rural water supply Fisheries & ponds Reservoirs & water levels Sanitary & other releases Transfers & transit Other needs
basin
Ertis 60.30 4.00 29.50 0.80 0.10 1.20 0.30 3.30 0.50
Yesil 47.30 39.10 4.80 6.80 0.40 0.10 0.10 0.10 1.40
Nura-Sarysu 84.40 6.90 4.60 0.10 0.00 1.20 1.70 0.10 0.90
Tobyl-Torgai 21.80 33.00 9.60 1.10 0.20 0.00 10.10 0.00 24.30
Zhaiyk-Caspian 53.70 9.90 5.60 0.20 3.70 16.40 8.10 0.00 2.30

8. Water sources¶

Surface water (of which sea water), groundwater, collector-drainage and wastewater intake.

In [10]:
src = df.groupby(['basin', 'year'], as_index=False)[['SUR', 'SEA', 'UND', 'COL', 'WW']].sum()
src['Surface (fresh)'] = src['SUR'] - src['SEA']
src = src.rename(columns={'SEA': 'Sea water', 'UND': 'Groundwater & mine', 'COL': 'Collector-drainage', 'WW': 'Wastewater'})
long = src.melt(id_vars=['basin', 'year'], var_name='source', value_name='mln m³',
                value_vars=['Surface (fresh)', 'Sea water', 'Groundwater & mine', 'Collector-drainage', 'Wastewater'])
fig = px.bar(long, x='year', y='mln m³', color='source', facet_col='basin', facet_col_wrap=3,
             category_orders={'basin': BASIN_ORDER},
             color_discrete_sequence=['#3182bd', '#1a7f6e', '#8c6d31', '#969696', '#cb4b16'])
fig.for_each_annotation(lambda a: a.update(text=a.text.split('=')[-1]))
fig.update_yaxes(matches=None, showticklabels=True)
fig.update_layout(title='Water intake by source and basin (mln m³)')
show(fig, height=640)

tot_src = long.groupby('source')['mln m³'].sum()
display(Markdown('**Share of 2004–2023 intake by source, five basins:** ' +
                 ' · '.join(f'{k} {v:.1f} %' for k, v in (100 * tot_src / tot_src.sum()).sort_values(ascending=False).items())))

Share of 2004–2023 intake by source, five basins: Surface (fresh) 79.2 % · Sea water 13.6 % · Groundwater & mine 6.7 % · Wastewater 0.4 % · Collector-drainage 0.0 %

9. Efficiency and losses¶

  • Water-use efficiency = water used ÷ water withdrawn.
  • Loss rate = transport losses ÷ water withdrawn.
In [11]:
eff = by_basin_year.assign(efficiency=lambda d: 100 * d['U'] / d['W'], loss_rate=lambda d: 100 * d['TL'] / d['W'])
fig = px.line(eff, x='year', y='efficiency', color='basin', markers=True, category_orders={'basin': BASIN_ORDER},
              color_discrete_sequence=PALETTE, labels={'efficiency': 'Efficiency, %', 'year': ''})
fig.update_layout(title='Water-use efficiency (used ÷ withdrawn, %)', hovermode='x unified')
show(fig, height=420)

heat = eff.pivot(index='basin', columns='year', values='loss_rate').reindex(BASIN_ORDER)
fig = px.imshow(heat, color_continuous_scale='OrRd', aspect='auto', text_auto='.1f',
                labels=dict(color='Loss rate, %', x='', y=''))
fig.update_layout(title='Transport loss rate (losses ÷ withdrawal, %)')
show(fig, height=360)

eff_summary = eff.groupby('basin').agg(mean_eff=('efficiency', 'mean'), min_eff=('efficiency', 'min'),
                                       max_eff=('efficiency', 'max'), mean_loss=('loss_rate', 'mean')).reindex(BASIN_ORDER)
display(eff_summary.round(1))
mean_eff min_eff max_eff mean_loss
basin
Ertis 93.70 88.50 97.50 2.70
Yesil 87.50 77.40 94.60 12.20
Nura-Sarysu 95.60 91.60 98.30 3.50
Tobyl-Torgai 81.30 62.60 93.10 7.80
Zhaiyk-Caspian 92.10 84.10 95.70 8.00

10. Trends 2004–2023¶

Linear trend (ordinary least squares) of annual withdrawal per basin and per sector, with the change between the first and last three-year means (more robust than first vs last year).

In [12]:
def trend_table(frame, by):
    rows = []
    for name, g in frame.groupby(by):
        s = g.groupby('year')['W'].sum().reindex(YEARS, fill_value=0)
        if s.sum() == 0:
            continue
        slope, _ = np.polyfit(s.index, s.values, 1)
        r = np.corrcoef(s.index, s.values)[0, 1] if s.std() > 0 else np.nan
        early, late = s.iloc[:3].mean(), s.iloc[-3:].mean()
        rows.append({by: name, 'mean, mln m³/yr': s.mean(), 'trend, mln m³/yr²': slope, 'R²': r ** 2,
                     'Δ 2004-06 → 2021-23, %': 100 * (late / early - 1) if early > 0 else np.nan})
    return pd.DataFrame(rows).set_index(by).sort_values('mean, mln m³/yr', ascending=False)

trend_basin, trend_sector = trend_table(df, 'basin'), trend_table(df, 'sector')
display(Markdown('**Withdrawal trend by basin**')); display(trend_basin.round(2))
display(Markdown('**Withdrawal trend by sector (five basins)**')); display(trend_sector.round(2))

fig = px.bar(trend_basin.reset_index(), x='basin', y='trend, mln m³/yr²', color='trend, mln m³/yr²',
             color_continuous_scale='RdBu_r', color_continuous_midpoint=0, labels={'basin': ''})
fig.update_layout(title='Linear trend of annual withdrawal (mln m³ per year)', coloraxis_showscale=False)
show(fig, height=380)

Withdrawal trend by basin

mean, mln m³/yr trend, mln m³/yr² R² Δ 2004-06 → 2021-23, %
basin
Ertis 3,600.04 27.59 0.29 12.78
Zhaiyk-Caspian 2,140.32 84.57 0.58 110.93
Nura-Sarysu 1,517.76 2.48 0.01 14.66
Yesil 290.10 13.71 0.67 97.50
Tobyl-Torgai 139.93 -1.72 0.35 -6.99

Withdrawal trend by sector (five basins)

mean, mln m³/yr trend, mln m³/yr² R² Δ 2004-06 → 2021-23, %
sector
Industry 4,769.40 109.33 0.69 63.56
Irrigation 1,278.86 9.23 0.04 -2.14
Household & drinking 619.27 -22.71 0.29 -51.07
Reservoirs & water levels 412.13 26.12 0.58 311.89
Sanitary & other releases 226.33 25.08 0.55 1,986.57
Transfers & transit 119.68 -2.28 0.04 36.53
Other needs 118.99 -4.32 0.22 -58.42
Fisheries & ponds 85.87 -11.35 0.29 -94.99
Rural water supply 57.63 -2.47 0.78 -51.59

11. Export for the web dashboard¶

  • water_use_dashboard.json — compact data for water_use*.html (per basin × water-use type × indicator, 20-year arrays, mln m³) plus trilingual labels.
  • water_use_tidy.csv — long table (basin, year, sector, type, all indicators, mln m³).
  • water_use_tables.xlsx — tidy data, basin totals, sector totals, efficiency and trend tables.
In [13]:
EXPORT = ['W', 'SUR', 'SEA', 'UND', 'MINE', 'COL', 'WW', 'U', 'D', 'DWB', 'NR', 'TL']
series = {}
for src_name, b in BASINS.items():
    sub = df[df[BASIN_COL] == src_name]
    series[b['id']] = {}
    for t_name, t in TYPES.items():
        g = sub[sub[TYPE_COL] == t_name].set_index('year').reindex(YEARS)
        series[b['id']][t['key']] = {k: [round(float(v), 4) for v in g[k].fillna(0)] for k in EXPORT}

dashboard = {
    'meta': {
        'title': 'Water Use & Abstraction — Northern Kazakhstan water management basins',
        'unit': 'mln m³', 'years': [int(y) for y in YEARS],
        'source': 'Basin water-use statistics (wateruse_masterTable.xlsx), thousand m³ converted to mln m³',
        'indicators': {'W': 'Total withdrawal', 'SUR': 'Surface intake (incl. sea)', 'SEA': 'Sea water intake',
                       'UND': 'Groundwater & mine intake', 'MINE': 'Mine water intake', 'COL': 'Collector-drainage intake',
                       'WW': 'Wastewater intake', 'U': 'Water used', 'D': 'Total discharge',
                       'DWB': 'Discharge to water bodies', 'NR': 'Non-returnable consumption', 'TL': 'Transport losses'},
        'boundaries': 'input_data/Water management basins.geojson',
        'notebook': 'notebooks/water_use_dashboard.ipynb',
    },
    'basins':  [{'id': b['id'], 'key': b['key'], 'source_name': n, 'en': b['en'], 'ru': b['ru'], 'kz': b['kz']}
                for n, b in BASINS.items()],
    'sectors': [{'key': k, **v} for k, v in SECTORS.items()],
    'types':   [{'key': t['key'], 'source_name': n, 'sector': t['sector'], 'en': t['en'], 'ru': t['ru'], 'kz': t['kz']}
                for n, t in TYPES.items()],
    'data': series,
}
(OUT_DIR / 'water_use_dashboard.json').write_text(json.dumps(dashboard, ensure_ascii=False, separators=(',', ':')),
                                                  encoding='utf-8')

tidy = df[['basin_id', 'basin', 'year', 'sector', 'type', TYPE_COL, *EXPORT]].rename(columns={TYPE_COL: 'type_source_name'})
tidy = tidy.rename(columns={k: f'{k} ({v}), mln m3' for k, v in dashboard['meta']['indicators'].items()})
tidy.to_csv(OUT_DIR / 'water_use_tidy.csv', index=False, encoding='utf-8-sig')
with pd.ExcelWriter(OUT_DIR / 'water_use_tables.xlsx') as xl:
    tidy.to_excel(xl, sheet_name='tidy_data', index=False)
    UNITS = {**{k: f'{v}, mln m3' for k, v in LABEL.items()},
             'efficiency': 'Efficiency (used / withdrawn), %', 'loss_rate': 'Loss rate (losses / withdrawn), %'}
    by_basin_year.rename(columns=UNITS).to_excel(xl, sheet_name='basin_by_year', index=False)
    (sector_year.pivot(index='year', columns='sector', values='W')[SECTOR_ORDER]
       .add_suffix(' — withdrawal, mln m3').to_excel(xl, sheet_name='withdrawal_by_sector'))
    eff.rename(columns=UNITS).to_excel(xl, sheet_name='efficiency_losses', index=False)
    trend_basin.to_excel(xl, sheet_name='trend_by_basin'); trend_sector.to_excel(xl, sheet_name='trend_by_sector')

# round-trip check: the JSON reproduces the notebook totals
check = sum(sum(series[b][t]['W']) for b in series for t in series[b])
assert abs(check - df['W'].sum()) < 0.05, (check, df['W'].sum())
for f in ('water_use_dashboard.json', 'water_use_tidy.csv', 'water_use_tables.xlsx'):
    print(f'{f:28s} {(OUT_DIR / f).stat().st_size / 1024:8.0f} KB')
water_use_dashboard.json          138 KB
water_use_tidy.csv                338 KB
water_use_tables.xlsx             172 KB

Rebuild the published report (static HTML copy of this notebook) from the portal root:

jupyter nbconvert --to html --execute notebooks/water_use_dashboard.ipynb

Dashboard: water_use.html · Data: input_data/water_use/ · Boundaries: input_data/Water management basins.*