Почему возникла необходимость строить ROC - кривую в Excel? Ведь эта чудесная программка не приспособлена к вычислениям "data science" метрик, то есть не имеет готовых инструментов. В том же Python, написав небольшой блок кода, вы получите то, что хотели, без лишних движений. Так зачем?
Первая модель, с которой я работал, была написана на языке R. Так уж получилось. Функционал я особо не знал, а хотелось поглядеть на те самые метрики, которые определяют успешность моей модели. Плюс файл Excel разрешения сможет открыть любой пользователь и посмотреть чего вы там настроили.
Делать было нечего, пришлось писать что-то своё. Но получилось, что в этом "Писать что-то своё" оказался весь смысл. Когда вы руками посчитаете всё что нужно для построения Roc - кривой, станет понятно как она работает.
Перейдём к построению!
Понадобится 2 столбца:
- Первый - с реальным значения класса (Real result)
- Второй - с предсказанием отнесения наблюдения к положительному классу (1) (Forecast)
Вероятность нам считает модель логистической регрессии. Почитать можно тут (О логистической регрессии).
И ... всё. Да, по этим двум столбцам будем строить кривую.
Создадим столбцы со значениями, необходимыми для построения:
- Порог - пороговое значение отнесения наблюдения к положительному классу (если порог 0.3, то все наблюдения с вероятностью больше чем 0.3 будут отнесены к положительному классу 1)
- FPR, где FPR = FP/(FP + TN)
- TPR, где TPR = TP/(TP + FN)
Я заполнил пороговые значения с шагом 0,05
Будем использовать функцию COUNTIFS или СЧЁТЕСЛИМН чтобы заполнить остальные столбцы.
Считаем TP при пороге 0
В клетку F2 пишем формулу
=COUNTIFS($A$2:$A$101;"1";$B$2:$B$101;">="&E2)
- $A$2:$A$101 - ваш диапазон в столбце Real result
- "1" - Проверка, равно ли значение в ячейке 1
- $B$2:$B$101 - ваш диапазон в столбце Forecast
- "=>"&E2 - Проверка, больше ли значение, чем порог
Эта формула посчитает True Positive (по определению)
Для остальных значений по аналогии запишем формулы
Для FP =COUNTIFS($A$2:$A$101;"0";$B$2:$B$101;">="&E2)
Для TN =COUNTIFS($A$2:$A$101;"0";$B$2:$B$101;"<"&E2)
Для FN =COUNTIFS($A$2:$A$101;"1";$B$2:$B$101;"<="&E2)
Выделяем ячейки и за правый конец тянем вниз, чтобы заполнить остальные значения
По определению FPR = FP/(FP + TN) и TPR = TP/(TP + FN)
В ячейке J2 пишем =G2/(G2+H2)
В ячейке K2 пишем =F2/(F2+I2)
Далее выделяем, как на картинке и тянем вниз за правый нижний уголок
Как мы знаем на осях графика ROC-кривой находятся как раз значения TPR и FPR. Давайте построим этот график!
Выделяем 2 последних столбца и выбираем точечную диаграмму с линиями
Я всё...
С ROC - кривой действительно всё, конечная. Из-за маленькой выборки она выглядит не особо плавно. Добавьте наблюдений и разбиение порога возьмите помельче и будет приемлемо
Напоследок посчитаем AUC. Как считать площадь под графиком в Excel я не знаю, поэтому посчитаем приближенно.
Использовать будем идею, как на картинке снизу
Строим много трапеций под графиком, а потом считаем их площадь и суммируем
Добавим столбец (Area) с площадью трапеций.
Вспоминаем как считать площадь трапеции и пишем формулу в ячейку =ABS((K2+K3)/2*(J3-J2))
Протягиваем за нижний правый угол до конца вниз и получаем площади трапеций
Далее суммируем все площади и получаем AUC
У меня получилось AUC = 0.8.
Теперь всё