coding heatmap
"""
ТЕПЛОВАЯ КАРТА: филиалы x месяцы, два блока -- ПОРТФЕЛЬ и NPL %
+ строка ИТОГО ПО БАНКУ. Для печати: A4, PNG 300 dpi + PDF (вектор).
ИСХОДНЫЕ ДАННЫЕ -- Excel в «длинном» формате, по строке на филиал-месяц:
branches | date | portfel | npl
----------------|------------|---------|------
Чиланзарский | 2026-01-01 | 634.0 | 59.4
Чиланзарский | 2026-02-01 | 660.0 | 64.8
...
NPL %, доли и итоги считаются сами -- в файле их держать не нужно.
Колонка npl нужна: из неё считается NPL % и признак скрытого ухудшения.
КАК ЧИТАЕТСЯ ЦВЕТ
ПОРТФЕЛЬ -- доля филиала в портфеле банка за ЭТОТ месяц.
Чем больше доля, тем зеленее. Колонки красятся независимо,
поэтому сдвиг оттенка по строке = филиал набирает или
теряет вес в банке.
NPL % -- сам уровень показателя. Чем выше, тем коричневее.
Запуск:
python heatmap.py # берёт DATA_FILE из настроек
python heatmap.py my_data.xlsx # или путь аргументом
"""
import os
import sys
import numpy as np
import pandas as pd
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
from matplotlib.colors import LinearSegmentedColormap, Normalize
from matplotlib.cm import ScalarMappable
from matplotlib.gridspec import GridSpec
from matplotlib.transforms import blended_transform_factory
# ==========================================================================
# НАСТРОЙКИ
# ==========================================================================
DATA_FILE = "data.xlsx"
SHEET = 0
OUT = "heatmap_out"
COL_BRANCH = None # None = определить автоматически
COL_DATE = None
COL_PORT = None
COL_NPL = None
TOTAL_POS = "top" # "top" | "bottom"
CELL_TEXT = "amount" # "amount" -- суммы портфеля
# "share" -- доля филиала в портфеле банка, %
NPL_CRIT = 8.0 # критический порог NPL % -- ПОСТАВЬТЕ СВОЙ
SORT_BY = "npl_pct" # "npl_pct" | "portfolio" | "name"
AUTO_SCALE = True # подобрать диапазоны шкал под данные
S_LO, S_HI = 0, 15 # доля в портфеле, % (если AUTO_SCALE = False)
Q_LO, Q_HI = 0, 14 # уровень NPL, %
AMOUNT_UNIT = "auto" # "auto" | 1 | 1000 | 1e6 | 1e9
# В какие единицы пересчитать суммы портфеля.
# "auto" подберёт так, чтобы числа влезали в ячейку;
# единица уходит в подпись блока, а не в каждую ячейку.
CELL_FS = 7.6 # размер шрифта в ячейках
CELL_FS_TOT = 8.2 # в строке ИТОГО
SHOW_COLORBARS = True
A4_W, A4_H = 11.69, 8.27
BAD = "#8C510A"
TOT_BG = "#DEDDD5" # фон строки ИТОГО в блоке портфеля
# Зелёная ветвь BrBG: чем больше доля, тем темнее
CM_SHARE = LinearSegmentedColormap.from_list(
"share", ["#FFFFFF", "#C7EAE5", "#80CDC1", "#35978F", "#01665E", "#003C30"])
# Коричневая ветвь BrBG: чем выше NPL %, тем темнее
CM_LEVEL = LinearSegmentedColormap.from_list(
"npl_level", ["#FFFFFF", "#F6E8C3", "#DFC27D", "#BF812D", "#8C510A", "#543005"])
MON_SHORT = {1: "янв", 2: "фев", 3: "мар", 4: "апр", 5: "май", 6: "июн",
7: "июл", 8: "авг", 9: "сен", 10: "окт", 11: "ноя", 12: "дек"}
MON_FULL = {1: "январь", 2: "февраль", 3: "март", 4: "апрель", 5: "май",
6: "июнь", 7: "июль", 8: "август", 9: "сентябрь", 10: "октябрь",
11: "ноябрь", 12: "декабрь"}
plt.rcParams.update({
"font.family": "DejaVu Sans", "font.size": 9, "figure.dpi": 110,
"savefig.dpi": 300, "savefig.bbox": "tight", "pdf.fonttype": 42,
})
# ==========================================================================
# ЧТЕНИЕ И ПРОВЕРКА ДАННЫХ
# ==========================================================================
def _find_col(cols, keys, exclude=()):
for c in cols:
low = str(c).strip().lower()
if any(x in low for x in exclude):
continue
if any(k in low for k in keys):
return c
return None
def resolve_columns(cols):
b = COL_BRANCH or _find_col(cols, ["branch", "фил", "отделен", "мфо"])
d = COL_DATE or _find_col(cols, ["date", "дат", "месяц", "period", "период"])
p = COL_PORT or _find_col(cols, ["portfel", "portfolio", "портф", "кредит", "loan"])
n = COL_NPL or _find_col(cols, ["npl", "просроч", "проблем"],
exclude=["%", "percent", "проц", "доля"])
missing = [name for name, v in
[("филиал", b), ("дата", d), ("портфель", p), ("NPL", n)] if v is None]
if missing:
raise SystemExit(
f"Не удалось определить колонки: {', '.join(missing)}.\n"
f"Колонки в файле: {list(cols)}\n"
f"Впишите их явно в COL_BRANCH / COL_DATE / COL_PORT / COL_NPL.")
return b, d, p, n
def load_excel(path, sheet=SHEET):
if not os.path.exists(path):
raise SystemExit(f"Файл не найден: {path}")
df = pd.read_excel(path, sheet_name=sheet)
if isinstance(df, dict):
df = list(df.values())[0]
df.columns = [str(c).strip() for c in df.columns]
cb, cd, cp, cn = resolve_columns(df.columns)
print(f"Колонки: филиал='{cb}', дата='{cd}', портфель='{cp}', NPL='{cn}'")
df = df[[cb, cd, cp, cn]].copy()
df.columns = ["branch", "date", "port", "npl"]
df["branch"] = df["branch"].astype(str).str.strip()
df["date"] = pd.to_datetime(df["date"], dayfirst=True, errors="coerce")
for c in ("port", "npl"):
df[c] = pd.to_numeric(df[c], errors="coerce")
problems = []
bad_date = int(df["date"].isna().sum())
if bad_date:
problems.append(f"{bad_date} строк с нераспознанной датой — отброшены")
df = df[df["date"].notna()]
nan_val = int(df[["port", "npl"]].isna().any(axis=1).sum())
if nan_val:
problems.append(f"{nan_val} строк с пустым портфелем или NPL — отброшены")
df = df.dropna(subset=["port", "npl"])
dup = int(df.duplicated(["branch", "date"]).sum())
if dup:
problems.append(f"{dup} повторяющихся пар филиал+дата — просуммированы")
df = df.groupby(["branch", "date"], as_index=False)[["port", "npl"]].sum()
port = df.pivot(index="branch", columns="date", values="port").sort_index(axis=1)
npl = df.pivot(index="branch", columns="date", values="npl").sort_index(axis=1)
gaps = int(port.isna().sum().sum())
if gaps:
miss = [f"{b}/{d:%m.%Y}" for b in port.index for d in port.columns
if pd.isna(port.loc[b, d])][:6]
problems.append(f"{gaps} пропусков филиал-месяц (напр. {', '.join(miss)})")
nonpos = int((port <= 0).sum().sum())
if nonpos:
problems.append(f"{nonpos} ячеек с портфелем <= 0 — NPL % не определён")
over = int((npl > port).sum().sum())
if over:
problems.append(f"{over} ячеек, где NPL больше портфеля — значения "
f"ОСТАВЛЕНЫ как есть, итоги искажены; исправьте источник")
neg = int((npl < 0).sum().sum())
if neg:
problems.append(f"{neg} ячеек с отрицательным NPL — ОСТАВЛЕНЫ как есть")
print(f"Загружено: {len(port)} филиалов x {port.shape[1]} месяцев "
f"({port.columns[0]:%m.%Y} – {port.columns[-1]:%m.%Y})")
if problems:
print("ВНИМАНИЕ:")
for p in problems:
print(" · " + p)
else:
print("Проверки пройдены, замечаний нет.")
npl = npl.reindex(index=port.index, columns=port.columns)
npl_pct = npl / port.where(port > 0) * 100
return port, npl, npl_pct
_args = [a for a in sys.argv[1:]
if not a.startswith("-") and a.lower().endswith((".xlsx", ".xls", ".xlsm"))]
path = _args[0] if _args else DATA_FILE
portfolio, npl_abs, npl_pct = load_excel(path)
MONTHS = [MON_SHORT[c.month] for c in portfolio.columns]
if portfolio.columns[0].year != portfolio.columns[-1].year:
MONTHS = [f"{MON_SHORT[c.month]}\n{c:%y}" for c in portfolio.columns]
tot_port = portfolio.sum(axis=0, skipna=True)
tot_npl = npl_abs.sum(axis=0, skipna=True)
# Итоговый NPL % = сумма NPL / сумма портфеля, а НЕ среднее из процентов
# филиалов: среднее дало бы мелкому филиалу тот же вес, что и крупному.
tot_pct = tot_npl / tot_port * 100
# ДОЛЯ филиала в портфеле банка за каждый месяц -- это то, чем красится
# первый блок. Каждая колонка нормируется на свой итог, поэтому январь
# ничем не выделен и красится наравне с остальными месяцами.
share = portfolio.div(tot_port, axis=1) * 100
pct_chg = npl_pct.iloc[:, -1] - npl_pct.iloc[:, 0]
# «Скрытое ухудшение»: NPL % падает только потому, что портфель растёт
# быстрее плохих кредитов. Показатель улучшился, проблема осталась.
hidden = (pct_chg < 0) & (npl_abs.iloc[:, -1] > npl_abs.iloc[:, 0])
if SORT_BY == "npl_pct":
ORDER = npl_pct.iloc[:, -1].sort_values(ascending=False).index
elif SORT_BY == "portfolio":
ORDER = portfolio.iloc[:, -1].sort_values(ascending=False).index
else:
ORDER = portfolio.index.sort_values()
if AUTO_SCALE:
S_LO, S_HI = 0, float(np.nanmax(share.values))
Q_LO, Q_HI = 0, max(float(np.nanpercentile(npl_pct.values, 97)), NPL_CRIT * 1.2)
print(f"Шкалы (авто): доля 0–{S_HI:.1f}%, NPL% 0–{Q_HI:.1f}%")
# ---- единицы измерения сумм ---------------------------------------------
UNIT_NAMES = {1: "", 1000: "тыс.", 1000000: "млн", 1000000000: "млрд"}
if AMOUNT_UNIT == "auto":
_mx = float(np.nanmax(tot_port.values))
UNIT = 1
for _d in (1, 1000, 1000000, 1000000000):
UNIT = _d
if _mx / _d < 100000:
break
else:
UNIT = int(AMOUNT_UNIT)
UNIT_LBL = UNIT_NAMES.get(UNIT, f"/{UNIT:g}")
if UNIT != 1:
print(f"Суммы показаны в единицах: {UNIT_LBL} (делитель {UNIT:g})")
S_DARK = S_LO + 0.62 * (S_HI - S_LO)
Q_DARK = Q_LO + 0.62 * (Q_HI - Q_LO)
os.makedirs(OUT, exist_ok=True)
# ==========================================================================
# ОТРИСОВКА
# ==========================================================================
def fmt_num(v, dec=1):
"""Число с запятой как десятичным разделителем: 14 555,0 / 123,4"""
if v is None or not np.isfinite(v):
return "—"
return f"{v:,.{dec}f}".replace(",", "\u2009").replace(".", ",")
def draw(ax, color_vals, text_vals, cmap, vmin, vmax, dark_at,
ylabels=None, show_x=False, bold=False, flat=None):
vals = np.asarray(color_vals, dtype=float)
if flat is not None:
ax.imshow(np.zeros_like(vals), cmap=LinearSegmentedColormap.from_list(
"f", [flat, flat]), vmin=0, vmax=1, aspect="auto")
else:
cm = cmap.copy()
cm.set_bad("#EDEDE8")
ax.imshow(np.ma.masked_invalid(vals), cmap=cm, vmin=vmin, vmax=vmax,
aspect="auto")
ax.set_xticks(range(len(MONTHS)))
if show_x:
ax.set_xticklabels(MONTHS, fontsize=9)
ax.xaxis.set_ticks_position("top")
else:
ax.set_xticklabels([])
ax.set_yticks(range(vals.shape[0]))
ax.set_yticklabels(ylabels if ylabels is not None else [], fontsize=8.5)
ax.set_xticks(np.arange(-.5, len(MONTHS), 1), minor=True)
ax.set_yticks(np.arange(-.5, vals.shape[0], 1), minor=True)
ax.grid(which="minor", color="white", linewidth=1.6)
ax.tick_params(which="both", length=0)
for s in ax.spines.values():
s.set_visible(False)
for i in range(vals.shape[0]):
for j in range(vals.shape[1]):
light = (flat is None) and np.isfinite(vals[i, j]) and dark_at(vals[i, j])
ax.text(j, i, text_vals[i][j], ha="center", va="center",
fontsize=CELL_FS_TOT if bold else CELL_FS,
fontweight="bold" if bold else "normal",
color="white" if light else "#1a1a1a")
def make_heatmap():
n_br = len(ORDER)
fig_h = A4_H if n_br <= 18 else A4_H * n_br / 18.0
p_amt = portfolio.loc[ORDER].values / UNIT
p_shr = share.loc[ORDER].values
q_lvl = npl_pct.loc[ORDER].values
t_amt = tot_port.values[None, :] / UNIT
t_pct = tot_pct.values[None, :]
def txt_port(src, as_share):
suf = "%" if as_share else ""
return [[fmt_num(src[i, j], 1) + suf for j in range(src.shape[1])]
for i in range(src.shape[0])]
def txt_pct(q):
return [[fmt_num(q[i, j], 1) for j in range(q.shape[1])]
for i in range(q.shape[0])]
top = TOTAL_POS == "top"
fig = plt.figure(figsize=(A4_W, fig_h))
gs = GridSpec(2, 2, figure=fig, height_ratios=[1, n_br] if top else [n_br, 1],
hspace=0.055, wspace=0.085,
left=0.185, right=0.960, top=0.800,
bottom=0.130 if SHOW_COLORBARS else 0.070)
r_tot, r_main = (0, 1) if top else (1, 0)
labels = [("‼ " if bool(hidden.get(b, False)) else " ") + b for b in ORDER]
n_crit = int((npl_pct.loc[ORDER].iloc[:, -1] >= NPL_CRIT).sum())
axes_main = []
for col in range(2):
ax_t = fig.add_subplot(gs[r_tot, col])
ax_m = fig.add_subplot(gs[r_main, col])
axes_main.append(ax_m)
if col == 0:
# ИТОГО в блоке портфеля не красим: доля итога всегда 100 %,
# цвет нёс бы нулевую информацию.
# ИТОГО всегда суммой: доля итога в самом себе -- всегда 100 %.
draw(ax_t, np.zeros_like(t_amt), txt_port(t_amt, False),
CM_SHARE, S_LO, S_HI, lambda v: False,
ylabels=["ИТОГО ПО БАНКУ"], show_x=top, bold=True, flat=TOT_BG)
_as = CELL_TEXT == "share"
draw(ax_m, p_shr, txt_port(p_shr if _as else p_amt, _as),
CM_SHARE, S_LO, S_HI,
lambda v: v > S_DARK, ylabels=labels, show_x=not top)
_u = f", {UNIT_LBL}" if UNIT_LBL else ""
title, sub = "Портфель", (
"доля филиала в портфеле банка, %" if CELL_TEXT == "share"
else f"суммы{_u}; цвет — доля филиала в портфеле банка за месяц")
else:
draw(ax_t, t_pct, txt_pct(t_pct), CM_LEVEL, Q_LO, Q_HI,
lambda v: v > Q_DARK, ylabels=[""], show_x=top, bold=True)
draw(ax_m, q_lvl, txt_pct(q_lvl), CM_LEVEL, Q_LO, Q_HI,
lambda v: v > Q_DARK, ylabels=[""] * n_br, show_x=not top)
title, sub = "NPL, %", "уровень; чем выше, тем коричневее"
if col == 0:
ax_t.get_yticklabels()[0].set_fontweight("bold")
ax_t.get_yticklabels()[0].set_fontsize(9)
for t, b in zip(ax_m.get_yticklabels(), ORDER):
if npl_pct.loc[b].iloc[-1] >= NPL_CRIT:
t.set_color(BAD)
t.set_fontweight("bold")
head = ax_t if top else ax_m
head.set_title(title, fontsize=12, fontweight="bold", pad=38)
head.annotate(sub, xy=(0.5, 1.0), xycoords="axes fraction",
xytext=(0, 17), textcoords="offset points",
ha="center", va="bottom", fontsize=8.5, color="#555555")
if 0 < n_crit < n_br:
ax_m.axhline(n_crit - 0.5, color="#C62828", lw=1.4, ls=(0, (4, 2)))
if col == 1:
tr = blended_transform_factory(ax_m.transAxes, ax_m.transData)
ax_m.annotate(f"порог\n{fmt_num(NPL_CRIT)}%", xy=(1.0, n_crit - 0.5),
xycoords=tr, xytext=(5, 0), textcoords="offset points",
fontsize=7.4, color="#C62828", ha="left", va="center",
annotation_clip=False)
# ---- шкалы цвета -----------------------------------------------------
if SHOW_COLORBARS:
for col, (cm, lo, hi, cap) in enumerate([
(CM_SHARE, S_LO, S_HI, "доля в портфеле банка, %"),
(CM_LEVEL, Q_LO, Q_HI, "NPL, %")]):
pos = axes_main[col].get_position()
cax = fig.add_axes([pos.x0, 0.062, pos.width, 0.014])
cb = fig.colorbar(ScalarMappable(Normalize(lo, hi), cm),
cax=cax, orientation="horizontal")
cb.outline.set_visible(False)
cb.set_ticks([lo, hi])
cb.set_ticklabels([fmt_num(lo), fmt_num(hi)])
cax.tick_params(labelsize=7.5, length=0, pad=2)
cax.set_title(cap, fontsize=8, color="#555555", pad=4)
c0, c1 = portfolio.columns[0], portfolio.columns[-1]
period = (f"{MON_FULL[c0.month]} – {MON_FULL[c1.month]} {c1.year}"
if c0.year == c1.year else
f"{MON_FULL[c0.month]} {c0.year} – {MON_FULL[c1.month]} {c1.year}")
fig.suptitle(f"Портфель и NPL % по филиалам · {period}",
fontsize=15, fontweight="bold", y=0.985)
fig.text(0.5, 0.940,
"Филиалы отсортированы по NPL % на конец периода — худшие сверху. "
"Зелёный — больше доля в портфеле. Коричневый — выше NPL %.",
fontsize=9, color="#333333", ha="center")
fig.text(0.5, 0.905,
"‼ — NPL % снизился, но объём NPL вырос: показатель улучшился только "
"за счёт роста портфеля, плохие кредиты никуда не делись.",
fontsize=8.5, color=BAD, ha="center")
fig.text(0.5, 0.018,
"Каждый месяц окрашен независимо от других: цвет — доля филиала "
"в портфеле банка за этот месяц. "
"Итоговый NPL % = сумма NPL / сумма портфеля, а не среднее из процентов.",
fontsize=7.5, color="#777777", ha="center")
fig.savefig(f"{OUT}/heatmap.png", facecolor="white")
fig.savefig(f"{OUT}/heatmap.pdf", facecolor="white")
plt.close(fig)
print(f"\nГотово: {OUT}/heatmap.png и {OUT}/heatmap.pdf")
if __name__ == "__main__":
make_heatmap()
print(f"Портфель ИТОГО: {fmt_num(tot_port.iloc[0]/UNIT)} → "
f"{fmt_num(tot_port.iloc[-1]/UNIT)} {UNIT_LBL}".rstrip())
print(f"NPL % по банку: {fmt_num(tot_pct.iloc[0], 2)}% → "
f"{fmt_num(tot_pct.iloc[-1], 2)}%")
h = list(hidden[hidden].index)
print(f"Скрытое ухудшение: {', '.join(h) if h else 'нет'}")