МТС: пивот-таблица обращений по тематикам и МР для контактного центра

Условие

Дана выгрузка обращений в контактный центр МТС за 07.09.2017:

Сформировать сводную таблицу: по тематикам/подтематикам, в разрезе МР и суммарно по РФ, с колонками Жалоба | Заявка | Консультация | Итого для каждого МР.

Решение

Подход

В Excel это решается через сводную таблицу (Pivot Table) с двумя осями. На SQL — CASE WHEN ... THEN COUNT END или PIVOT (если есть в диалекте).

Реализация (SQL)

-- Допустим, выгрузка лежит в таблице calls
SELECT
    theme,
    subtheme,
    -- Центр
    SUM(CASE WHEN mr = 'МР Центр' AND call_type = 'Жалоба'       THEN cnt ELSE 0 END) AS center_zhaloba,
    SUM(CASE WHEN mr = 'МР Центр' AND call_type = 'Заявка'       THEN cnt ELSE 0 END) AS center_zayavka,
    SUM(CASE WHEN mr = 'МР Центр' AND call_type = 'Консультация' THEN cnt ELSE 0 END) AS center_consult,
    SUM(CASE WHEN mr = 'МР Центр'                                THEN cnt ELSE 0 END) AS center_total,
    -- ... аналогично для остальных МР ...
    -- РФ
    SUM(CASE WHEN call_type = 'Жалоба'       THEN cnt ELSE 0 END) AS rf_zhaloba,
    SUM(CASE WHEN call_type = 'Заявка'       THEN cnt ELSE 0 END) AS rf_zayavka,
    SUM(CASE WHEN call_type = 'Консультация' THEN cnt ELSE 0 END) AS rf_consult,
    SUM(cnt)                                                       AS rf_total
FROM calls
WHERE call_date = '2017-09-07'
GROUP BY ROLLUP (theme, subtheme)
ORDER BY theme, subtheme;

ROLLUP — даёт строки-итоги по theme (без subtheme).

Реализация (Pandas)

Если данные в Excel — Pandas удобнее:

import pandas as pd
 
df = pd.read_excel("calls.xlsx", sheet_name="Выгрузка")
df.columns = ["date","mr","anon","call_type","theme","subtheme","product","cnt","duration"]
 
# Пивот: тема/подтема × МР × тип
pivot = pd.pivot_table(
    df,
    index=["theme","subtheme"],
    columns=["mr","call_type"],
    values="cnt",
    aggfunc="sum",
    fill_value=0,
    margins=True,         # итоги
    margins_name="РФ",
)
 
# Добавляем "Итого" по МР
for mr in df["mr"].unique():
    pivot[(mr, "Итого")] = pivot[mr].sum(axis=1)
 
pivot.to_excel("report.xlsx")

Анализ результата

• В исходных данных колонка Подтема иерархична: одна Тема (например, «Баланс и начисления») содержит несколько подтем (Корректировка, Несогласие, ...). Двойной индекс в pivot это естественно отражает.
• Если на пересечении Тема × МР × Тип нет обращений — fill_value=0. Иначе NaN смутит читателя.
• Топ-5 тематик по РФ, как требует следующая часть задания, — это сортировка pivot[("РФ","Итого")] (или groupby(theme).sum()).

Дополнительные шаги задания

• Условное форматирование: значения от 250 до 300 сек — жёлтым, выше 300 — розовым. В Excel задаётся через Conditional Formatting; в Pandas — через Styler:

  styled = pivot.style.applymap(
      lambda v: "background-color: yellow" if 250 <= v <= 300
                 else "background-color: pink" if v > 300 else ""
  )

• Гистограмма: топ-1 тема, столбцы по МР, выделить красным, где avg_duration > 250. Pandas + matplotlib bar с условными цветами.

Подводные камни

1. Кодировка. Файл в исходнике — Выгрузка с кириллицей. На сервере с другим locale pd.read_excel может не прочитать колонки.
2. Дубли в исходных данных. Иногда одна и та же запись попадает дважды из разных систем учёта. Перед агрегацией — drop_duplicates.
3. Иерархия тем без подтем. Часть строк имеет Подтема = NULL. В пивоте они пойдут отдельной строкой; обычно их сворачивают в Тема, итого.
4. «Время разговора» = sum vs avg. В первом задании просят count — SUM(cnt). Во втором — AVG(duration / cnt) или SUM(duration) / SUM(cnt). Это разные числа.
5. Деление на ноль. Когда SUM(cnt) = 0, AVG(duration) уйдёт в NaN. Используйте NULLIF.
6. Order of MR. Бизнес часто ожидает фиксированный порядок (Центр, СЗ, Юг, ...). Делайте Categorical с заданным categories.

Эталонный ответ

В Pandas — pivot_table(index=[theme, subtheme], columns=[mr, call_type], aggfunc='sum') с margins=True. В SQL — SUM(CASE WHEN ... END) либо PIVOT для каждой пары (МР × Тип). Для топ-5 + среднего времени — отдельный запрос SUM(duration)/SUM(cnt) с условным форматированием через Styler/Excel.