💧 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:
- loads the basin-level water-use statistics (
input_data/water_use/wateruse_masterTable.xlsx), - validates the table (balance identities, totals, gaps),
- converts units to million m³ (mln m³) and groups the 21 water-use types into 9 sectors,
- joins the data to the Water management basins boundaries (
input_data/Water management basins.geojson), - explores the most important indicators — spatial pattern, time series, sector structure, water sources, efficiency and losses, trends,
- 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³).
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.
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
| 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
TOTALrow equals the sum of the individual water-use types andAgriculture, totalequals its sub-types — so those two aggregate rows are dropped to avoid double counting.
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_IDof 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.
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.
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.
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.
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.
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.
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.
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.
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).
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 forwater_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.
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.*