Errors

NORM_CB = 0.05   # ЦБ: доля NPL не выше 5 %
  NORM_KB = 0.04   # внутренний лимит банка: 4 %
  NEAR = 0.10      # «близко» = в пределах 10 % ниже нормы (4 % → с 3,6 %)


  def status(share, norm):
      """'high' (red) at or above the norm, 'near' (yellow) just under it, 'low' (green)."""
      if share >= norm:
          return "high"
      if share >= norm * (1 - NEAR):
          return "near"
      return "low"


  def grouping_okr(df, col_name, second_col_name=None, norm_cb=NORM_CB, norm_kb=NORM_KB):
      """Brutto, NPL amount and NPL share per group, with a status for each norm."""
      columns = [col_name, second_col_name] if second_col_name else [col_name]
      df = df.copy()
      # NPL = category 3 and above: the whole balance of those loans is NPL
      df['npl_amount'] = df['brutto_95_amount'].where(df['npl_category'] > 2, 0)
      grouped = df.groupby(columns, dropna=False)[['brutto_95_amount', 'npl_amount']].sum().reset_index()
      grouped['npl_share'] = (grouped['npl_amount'] / grouped['brutto_95_amount']).where(
          grouped['brutto_95_amount'] != 0, 0)
      grouped['norm_cb'] = grouped['npl_share'].apply(lambda x: status(x, norm_cb))
      grouped['norm_kb'] = grouped['npl_share'].apply(lambda x: status(x, norm_kb))
      if second_col_name:   # passports stay inside their department, worst first
          grouped = grouped.sort_values([col_name, 'npl_share'], ascending=[True, False])
      else:
          grouped = grouped.sort_values('npl_share', ascending=False)
      return grouped


  def make_table(df, title, icon, col_name, second_col_name=None):
      """One table for the template: rows + total, every number ready to print."""
      grouped = grouping_okr(df, col_name, second_col_name)
      rows = []
      for r in grouped.to_dict('records'):
          rows.append({
              'group': str(r[col_name]) if second_col_name else None,
              'name': str(r[second_col_name or col_name]),   # str: a branch code must not print as "1 234"
              'brutto': r['brutto_95_amount'] / 1e6,
              'npl': r['npl_amount'] / 1e6,
              'share': r['npl_share'] * 100,
              'cb': r['norm_cb'],
              'kb': r['norm_kb'],
          })
      brutto = grouped['brutto_95_amount'].sum()
      npl = grouped['npl_amount'].sum()
      share = npl / brutto if brutto else 0
      return {
          'title': title, 'icon': icon, 'rows': rows,
          'total': {'brutto': brutto / 1e6, 'npl': npl / 1e6, 'share': share * 100,
                    'cb': status(share, NORM_CB), 'kb': status(share, NORM_KB)},
          'count': {s: sum(1 for r in rows if r['kb'] == s) for s in ['high', 'near', 'low']},
      }


  def portfel_result(request, dept_type='total'):
      selected_date = request.session.get(f"portfel_date_str_{request.user.id}")
      portfel_filepath_svod = request.session.get(f'portfel_filepath_svod_{request.user.id}')
      if selected_date is None or portfel_filepath_svod is None:
          selected_date = common_functions.find_recent_date()
          request.session[f"portfel_date_str_{request.user.id}"] = str(selected_date)
          portfel_filepath_svod = f"{portfel_svod_folder}/svod_{selected_date}.parquet"
          request.session[f"portfel_filepath_svod_{request.user.id}"] = portfel_filepath_svod

      if not os.path.exists(portfel_filepath_svod):
          messages.warning(request, f"Файла нет: {portfel_filepath_svod}")
          return redirect('portfel-home')
      try:
          df = pd.read_parquet(portfel_filepath_svod)
      except Exception as e:
          messages.warning(request, f"Cant read the df: {e}")
          return redirect('portfel-home')
      if df.empty:
          messages.warning(request, 'Df is empty')
          return redirect('portfel-home')

      df = df.rename({'passport_final': 'passport'}, axis=1)
      df['short_passport'] = df['passport'].replace(common_functions.passport_map)
      df['region'] = df['branch'].map(common_choices.REGION_CHOICES)

      context = {
          'selected_date': selected_date,
          'norm_cb': NORM_CB * 100, 'norm_kb': NORM_KB * 100,
          'near_kb': NORM_KB * (1 - NEAR) * 100,
          'tables': [
              make_table(df, 'По департаментам', 'fa-sitemap', 'department'),
              make_table(df, 'По регионам', 'fa-map-location-dot', 'region'),
              make_table(df, 'По паспортам продуктов', 'fa-id-card', 'department', 'passport'),
              make_table(df, 'По филиалам', 'fa-building-columns', 'branch'),
          ],
      }
      return render(request, 'portfel/okr.html', context)

  templates/portfel/okr.html

  {% extends "base.html" %}
  {% comment %}
    NPL share by department / region / passport / branch, coloured against two norms:
    red = at or above the norm, yellow = within 10 % under it, green = below.
    Every number arrives ready from the view; no JavaScript.
  {% endcomment %}

  {% block extra_css %}
  <style>
    .okr{--ink:#0B0B0C;--rule:#E4E1D8;--paper:#FAF8F2;--muted:#8A867C;--gtext:#8C6A12}
    .okr .box{background:#fff;border:1px solid var(--rule);border-radius:12px;margin-bottom:22px}
    .okr .box-head{background:var(--paper);border-bottom:1px solid var(--rule);border-radius:12px 12px 0 0;
         padding:10px 16px;font-size:12px;letter-spacing:.08em;text-transform:uppercase;color:var(--gtext);
         display:flex;justify-content:space-between;align-items:center;flex-wrap:wrap;gap:8px}
    .okr .box-head i{margin-right:8px}
    .okr table{font-size:13px;margin:0}
    .okr th{font-size:10.5px;letter-spacing:.06em;text-transform:uppercase;color:var(--muted);font-weight:600;white-space:nowrap}
    .okr td.m,.okr th.m{text-align:right;white-space:nowrap;font-variant-numeric:tabular-nums}
    .okr th.c,.okr td.c{text-align:center}
    /* the three colours */
    .okr .st{display:inline-block;min-width:92px;padding:2px
  10px;border-radius:20px;font-size:11.5px;font-weight:600;text-align:center}
    .okr .st-high{background:#FBE3DF;color:#B3341F}
    .okr .st-near{background:#FFF1C2;color:#8C6A12}
    .okr .st-low{background:#E2F3E8;color:#1E7B4A}
    .okr td.share-high{color:#B3341F;font-weight:700}
    .okr td.share-near{color:#B07D0A;font-weight:700}
    .okr td.share-low{color:#1E7B4A;font-weight:600}
    .okr tr.row-high td:first-child{box-shadow:inset 4px 0 #B3341F}
    .okr tr.row-near td:first-child{box-shadow:inset 4px 0 #F2C230}
    .okr tr.row-low td:first-child{box-shadow:inset 4px 0 #3BAA6B}
    .okr tr.group td{background:var(--paper);font-weight:700;font-size:12px;letter-spacing:.04em}
    .okr tr.total td{font-weight:700;border-top:2px solid var(--ink)}
    .okr .count{font-size:12px;letter-spacing:0;text-transform:none}
    .okr .legend{font-size:13px;color:var(--muted)}
    @media print{.sb-topnav,#layoutSidenav_nav{display:none!important}
      #layoutSidenav_content{padding-left:0!important;margin-left:0!important}
      .okr .st,.okr tr td{-webkit-print-color-adjust:exact;print-color-adjust:exact}
      .okr .box{break-inside:avoid}}
  </style>
  {% endblock extra_css %}

  {% block content %}
  <div class="okr container-fluid px-4 py-4">
    <h4 class="mb-1"><i class="fa-solid fa-triangle-exclamation text-warning me-2"></i>Доля NPL на {{ selected_date }}</h4>
    <div class="legend mb-4">
      Норма ЦБ — <strong>{{ norm_cb|floatformat:0 }} %</strong>, лимит банка — <strong>{{ norm_kb|floatformat:0 }} %</strong>:
      <span class="st st-high">выше нормы</span>
      <span class="st st-near">близко ({{ near_kb|floatformat:1 }}–{{ norm_kb|floatformat:0 }} %)</span>
      <span class="st st-low">в норме</span>
    </div>

    {% for t in tables %}
    <div class="box">
      <div class="box-head">
        <span><i class="fa-solid {{ t.icon }}"></i>{{ t.title }}</span>
        <span class="count">лимит банка:
          <span class="st st-high">{{ t.count.high }} выше</span>
          <span class="st st-near">{{ t.count.near }} близко</span>
          <span class="st st-low">{{ t.count.low }} в норме</span></span>
      </div>
      <div class="table-responsive">
        <table class="table table-sm table-hover align-middle">
          <thead><tr>
            <th>{% if t.rows.0.group %}Паспорт{% else %}Название{% endif %}</th>
            <th class="m">Брутто, млн</th><th class="m">NPL, млн</th><th class="m">Доля NPL</th>
            <th class="c">Норма ЦБ {{ norm_cb|floatformat:0 }} %</th>
            <th class="c">Лимит банка {{ norm_kb|floatformat:0 }} %</th>
          </tr></thead>
          <tbody>
          {% for r in t.rows %}
            {% if r.group %}{% ifchanged r.group %}
            <tr class="group"><td colspan="6">{{ r.group }}</td></tr>
            {% endifchanged %}{% endif %}
            <tr class="row-{{ r.kb }}">
              <td>{{ r.name }}</td>
              <td class="m">{{ r.brutto|floatformat:"1g" }}</td>
              <td class="m">{{ r.npl|floatformat:"1g" }}</td>
              <td class="m share-{{ r.kb }}">{{ r.share|floatformat:2 }} %</td>
              <td class="c"><span class="st st-{{ r.cb }}">{% if r.cb == 'high' %}выше{% elif r.cb == 'near' %}близко{% else %}в
  норме{% endif %}</span></td>
              <td class="c"><span class="st st-{{ r.kb }}">{% if r.kb == 'high' %}выше{% elif r.kb == 'near' %}близко{% else %}в
  норме{% endif %}</span></td>
            </tr>
          {% endfor %}
            <tr class="total">
              <td>Итого</td>
              <td class="m">{{ t.total.brutto|floatformat:"1g" }}</td>
              <td class="m">{{ t.total.npl|floatformat:"1g" }}</td>
              <td class="m share-{{ t.total.kb }}">{{ t.total.share|floatformat:2 }} %</td>
              <td class="c"><span class="st st-{{ t.total.cb }}">{% if t.total.cb == 'high' %}выше{% elif t.total.cb == 'near'
  %}близко{% else %}в норме{% endif %}</span></td>
              <td class="c"><span class="st st-{{ t.total.kb }}">{% if t.total.kb == 'high' %}выше{% elif t.total.kb == 'near'
  %}близко{% else %}в норме{% endif %}</span></td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>
    {% endfor %}
  </div>
  {% endblock content %}