e-learning-Karpov.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Проект e-learning: вариант 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Введение\n",
    "\n",
    "Продакт-менеджер Василий попросил вас проанализировать завершенные уроки и ответить на следующие вопросы:\n",
    "\n",
    "1. Сколько студентов успешно сдали только один курс? (Успешная сдача — это зачёт по курсу на экзамене) (7 баллов).\n",
    "\n",
    "2. Выяви самый сложный и самый простой экзамен: найди курсы и экзамены в рамках курса, которые обладают самой низкой и самой высокой завершаемостью*. (10 баллов)\n",
    "\n",
    "3. По каждому предмету определи средний срок сдачи экзаменов (под сдачей понимаем последнее успешное прохождение экзамена студентом). (7 баллов) \n",
    "\n",
    "4. Выяви самые популярные предметы (ТОП-3) по количеству регистраций на них. А также предметы с самым большим оттоком (ТОП-3). (8 баллов)\n",
    "\n",
    "5. Используя pandas, в период с начала 2013 по конец 2014 выяви семестр с самой низкой завершаемостью курсов и самыми долгими средними сроками сдачи курсов.  (15 баллов) \n",
    "\n",
    "6. Часто для качественного анализа аудитории используют подходы, основанные на сегментации. Используя python, построй адаптированные RFM-кластеры студентов, чтобы качественно оценить свою аудиторию. В адаптированной кластеризации можешь выбрать следующие метрики: R - среднее время сдачи одного экзамена, F - завершаемость курсов, M - среднее количество баллов, получаемое за экзамен. Подробно опиши, как ты создавал кластеры. Для каждого RFM-сегмента построй границы метрик recency, frequency и monetary для интерпретации этих кластеров. Описание подхода можно найти тут. (23 балла)\n",
    "\n",
    "Для решения задачи проведи предварительное исследование данных и сформулируй, что должно считаться курсом. Обосновать свой выбор ты можешь с помощью фактов сдачи экзаменов, распределения студентов и уникального идентификатора курса.\n",
    "\n",
    "*завершаемость = кол-во успешных экзаменов / кол-во всех попыток сдать экзамен"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-success\">\n",
    "<b>\n",
    "Устанавливаем необходимые пакеты\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sbs\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-success\">\n",
    "<b>\n",
    "Загрузим данные файлов из <em>Яндекс.Диск</em>. Дополнительно проверим данные на пропущенные значения и правильное определение типа данных.\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from urllib.parse import urlencode\n",
    "import requests\n",
    "\n",
    "def read_yandexd_csv(url_groups, separator):\n",
    "    base_url = 'https://cloud-api.yandex.net/v1/disk/public/resources/download?'\n",
    "    \n",
    "    final_url_groups = base_url + urlencode(dict(public_key=url_groups))  \n",
    "    \n",
    "    response_groups = requests.get(final_url_groups)  \n",
    "    \n",
    "    download_url_groups = response_groups.json()['href']\n",
    "    \n",
    "    df = pd.read_csv(download_url_groups, sep=separator)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "assessments = read_yandexd_csv('https://disk.yandex.ru/d/iSVP1sZ7AdW7TQ', ',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(206, 6)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code_module           0\n",
       "code_presentation     0\n",
       "id_assessment         0\n",
       "assessment_type       0\n",
       "date                 11\n",
       "weight                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code_module           object\n",
       "code_presentation     object\n",
       "id_assessment          int64\n",
       "assessment_type       object\n",
       "date                 float64\n",
       "weight               float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1752</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1753</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1754</td>\n",
       "      <td>TMA</td>\n",
       "      <td>117.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1755</td>\n",
       "      <td>TMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1756</td>\n",
       "      <td>TMA</td>\n",
       "      <td>215.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_module code_presentation  id_assessment assessment_type   date  weight\n",
       "0         AAA             2013J           1752             TMA   19.0    10.0\n",
       "1         AAA             2013J           1753             TMA   54.0    20.0\n",
       "2         AAA             2013J           1754             TMA  117.0    20.0\n",
       "3         AAA             2013J           1755             TMA  166.0    20.0\n",
       "4         AAA             2013J           1756             TMA  215.0    30.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1752</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1753</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1754</td>\n",
       "      <td>TMA</td>\n",
       "      <td>117.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1755</td>\n",
       "      <td>TMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1756</td>\n",
       "      <td>TMA</td>\n",
       "      <td>215.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1757</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1758</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1759</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1760</td>\n",
       "      <td>TMA</td>\n",
       "      <td>117.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1761</td>\n",
       "      <td>TMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1762</td>\n",
       "      <td>TMA</td>\n",
       "      <td>215.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1763</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code_module code_presentation  id_assessment assessment_type   date  weight\n",
       "0          AAA             2013J           1752             TMA   19.0    10.0\n",
       "1          AAA             2013J           1753             TMA   54.0    20.0\n",
       "2          AAA             2013J           1754             TMA  117.0    20.0\n",
       "3          AAA             2013J           1755             TMA  166.0    20.0\n",
       "4          AAA             2013J           1756             TMA  215.0    30.0\n",
       "5          AAA             2013J           1757            Exam    NaN   100.0\n",
       "6          AAA             2014J           1758             TMA   19.0    10.0\n",
       "7          AAA             2014J           1759             TMA   54.0    20.0\n",
       "8          AAA             2014J           1760             TMA  117.0    20.0\n",
       "9          AAA             2014J           1761             TMA  166.0    20.0\n",
       "10         AAA             2014J           1762             TMA  215.0    30.0\n",
       "11         AAA             2014J           1763            Exam    NaN   100.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.query(\"code_module == 'AAA'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-class alert-info\">\n",
    "<b>\n",
    "\n",
    "**assessments.csv** — этот файл содержит информацию об оценках в тесте. Обычно каждый предмет в семестре включает ряд тестов с оценками, за которыми следует заключительный экзаменационный тест (экзамен).\n",
    "-   **code_module** — идентификационный код предмета.\n",
    "-   **code_presentation** — семестр (Идентификационный код).\n",
    "-   **id_assessment** — тест (Идентификационный номер ассессмента).\n",
    "-   **assessment_type** — тип теста. Существуют три типа оценивания: оценка преподавателя (TMA), компьютерная оценка (СМА), экзамен по курсу (Exam).\n",
    "-   **date** — информация об окончательной дате сдачи теста. Рассчитывается как количество дней с момента начала семестра. Дата начала семестра имеет номер 0 (ноль).\n",
    "-   **weight** — вес теста в % в оценке за курс. Обычно экзамены рассматриваются отдельно и имеют вес 100%; сумма всех остальных оценок составляет 100%.\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "courses = read_yandexd_csv('https://disk.yandex.ru/d/jmJxR8yhwCXlaw', ',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22, 3)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "courses.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code_module                   0\n",
       "code_presentation             0\n",
       "module_presentation_length    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "courses.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code_module                   object\n",
       "code_presentation             object\n",
       "module_presentation_length     int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "courses.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>module_presentation_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014J</td>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013B</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_module code_presentation  module_presentation_length\n",
       "0         AAA             2013J                         268\n",
       "1         AAA             2014J                         269\n",
       "2         BBB             2013J                         268\n",
       "3         BBB             2014J                         262\n",
       "4         BBB             2013B                         240"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "courses.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-class alert-info\">\n",
    "<b>\n",
    "\n",
    "**courses.csv** — файл содержит список предметов по семестрам.\n",
    "-   **code_module** — предмет (идентификационный код).\n",
    "-   **code_presentation** — семестр (идентификационный код).\n",
    "-   **module_presentation_length** — продолжительность семестра в днях.\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "studentAssessment = read_yandexd_csv('https://disk.yandex.ru/d/DYd4M5b_8RETsA', ',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(173912, 5)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentAssessment.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id_assessment       0\n",
       "id_student          0\n",
       "date_submitted      0\n",
       "is_banked           0\n",
       "score             173\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentAssessment.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id_assessment       int64\n",
       "id_student          int64\n",
       "date_submitted      int64\n",
       "is_banked           int64\n",
       "score             float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentAssessment.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1752</td>\n",
       "      <td>11391</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1752</td>\n",
       "      <td>28400</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1752</td>\n",
       "      <td>31604</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1752</td>\n",
       "      <td>32885</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1752</td>\n",
       "      <td>38053</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_assessment  id_student  date_submitted  is_banked  score\n",
       "0           1752       11391              18          0   78.0\n",
       "1           1752       28400              22          0   70.0\n",
       "2           1752       31604              17          0   72.0\n",
       "3           1752       32885              26          0   69.0\n",
       "4           1752       38053              19          0   79.0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentAssessment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-class alert-info\">\n",
    "<b>\n",
    "\n",
    "**studentAssessment.csv** — этот файл содержит результаты тестов студентов. Если учащийся не отправляет работу на оценку, результат не записывается в таблицу.\n",
    "-   **id_assessment** — тест (идентификационный номер).\n",
    "-   **id_student** — идентификационный номер студента.\n",
    "-   **date_submitted** — дата сдачи теста студентом, измеряемая как количество дней с начала семестра.\n",
    "-   **is_banked** — факт перезачета теста с прошлого семестра (иногда курсы перезачитывают студентам, вернувшимся из академического отпуска).\n",
    "-   **score** — оценка учащегося в этом тесте. Диапазон составляет от 0 до 100. Оценка ниже 40 неудачная/неуспешная сдача теста.\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "studentRegistration = read_yandexd_csv('https://disk.yandex.ru/d/lqHnH8NOZ458iw', ',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(32593, 5)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentRegistration.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code_module                0\n",
       "code_presentation          0\n",
       "id_student                 0\n",
       "date_registration         45\n",
       "date_unregistration    22521\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentRegistration.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code_module             object\n",
       "code_presentation       object\n",
       "id_student               int64\n",
       "date_registration      float64\n",
       "date_unregistration    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentRegistration.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_registration</th>\n",
       "      <th>date_unregistration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>11391</td>\n",
       "      <td>-159.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>28400</td>\n",
       "      <td>-53.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>30268</td>\n",
       "      <td>-92.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>31604</td>\n",
       "      <td>-52.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>32885</td>\n",
       "      <td>-176.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_module code_presentation  id_student  date_registration  \\\n",
       "0         AAA             2013J       11391             -159.0   \n",
       "1         AAA             2013J       28400              -53.0   \n",
       "2         AAA             2013J       30268              -92.0   \n",
       "3         AAA             2013J       31604              -52.0   \n",
       "4         AAA             2013J       32885             -176.0   \n",
       "\n",
       "   date_unregistration  \n",
       "0                  NaN  \n",
       "1                  NaN  \n",
       "2                 12.0  \n",
       "3                  NaN  \n",
       "4                  NaN  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentRegistration.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-class alert-info\">\n",
    "<b>\n",
    "\n",
    "**studentRegistration.csv** — этот файл содержит информацию о времени, когда студент зарегистрировался для прохождения курса в семестре.\n",
    "-   **code_module** — предмет (идентификационный код).\n",
    "-   **code_presentation** — семестр (идентификационный код)\n",
    "-   **id_student** — идентификационный номер студента.\n",
    "-   **date_registration** — дата регистрации студента. Это количество дней, измеренное от начала семестра (например, отрицательное значение -30 означает, что студент зарегистрировался на прохождение курса за 30 дней до его начала).\n",
    "-   **date_unregistration** — дата отмены регистрации студента с предмета. У студентов, окончивших курс, это поле остается пустым.\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>1752</td>\n",
       "      <td>721259</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>937</th>\n",
       "      <td>1754</td>\n",
       "      <td>260355</td>\n",
       "      <td>127</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2364</th>\n",
       "      <td>1760</td>\n",
       "      <td>2606802</td>\n",
       "      <td>180</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3358</th>\n",
       "      <td>14984</td>\n",
       "      <td>186780</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3914</th>\n",
       "      <td>14984</td>\n",
       "      <td>531205</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148929</th>\n",
       "      <td>34903</td>\n",
       "      <td>582670</td>\n",
       "      <td>241</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159251</th>\n",
       "      <td>37415</td>\n",
       "      <td>610738</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166390</th>\n",
       "      <td>37427</td>\n",
       "      <td>631786</td>\n",
       "      <td>221</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169725</th>\n",
       "      <td>37435</td>\n",
       "      <td>648110</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170103</th>\n",
       "      <td>37435</td>\n",
       "      <td>480914</td>\n",
       "      <td>49</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>173 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id_assessment  id_student  date_submitted  is_banked  score\n",
       "215              1752      721259              22          0    NaN\n",
       "937              1754      260355             127          0    NaN\n",
       "2364             1760     2606802             180          0    NaN\n",
       "3358            14984      186780              77          0    NaN\n",
       "3914            14984      531205              26          0    NaN\n",
       "...               ...         ...             ...        ...    ...\n",
       "148929          34903      582670             241          0    NaN\n",
       "159251          37415      610738              87          0    NaN\n",
       "166390          37427      631786             221          0    NaN\n",
       "169725          37435      648110              62          0    NaN\n",
       "170103          37435      480914              49          0    NaN\n",
       "\n",
       "[173 rows x 5 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#№ строки, где есть значения NaN в столбце score - экзамен еще не сдан\n",
    "studentAssessment[studentAssessment.isna().any(axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<b>\n",
    "Прежде, чем приступить к анализу данных, необходимо определить что такое курс в рамках этих данных.\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>module_presentation_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014J</td>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013B</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014B</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CCC</td>\n",
       "      <td>2014J</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CCC</td>\n",
       "      <td>2014B</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2014J</td>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013B</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2014B</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2014J</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2014B</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2014J</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013B</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2014B</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code_module code_presentation  module_presentation_length\n",
       "0          AAA             2013J                         268\n",
       "1          AAA             2014J                         269\n",
       "2          BBB             2013J                         268\n",
       "3          BBB             2014J                         262\n",
       "4          BBB             2013B                         240\n",
       "5          BBB             2014B                         234\n",
       "6          CCC             2014J                         269\n",
       "7          CCC             2014B                         241\n",
       "8          DDD             2013J                         261\n",
       "9          DDD             2014J                         262\n",
       "10         DDD             2013B                         240\n",
       "11         DDD             2014B                         241\n",
       "12         EEE             2013J                         268\n",
       "13         EEE             2014J                         269\n",
       "14         EEE             2014B                         241\n",
       "15         FFF             2013J                         268\n",
       "16         FFF             2014J                         269\n",
       "17         FFF             2013B                         240\n",
       "18         FFF             2014B                         241\n",
       "19         GGG             2013J                         261\n",
       "20         GGG             2014J                         269\n",
       "21         GGG             2014B                         241"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "courses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "206"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Проверим, что номера тестов уникальны для каждого семестра (каждому семестру и курсу присвоен уникальный номер тестов)\n",
    "assessments['id_assessment'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(206, 6)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1752</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1753</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1754</td>\n",
       "      <td>TMA</td>\n",
       "      <td>117.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1755</td>\n",
       "      <td>TMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1756</td>\n",
       "      <td>TMA</td>\n",
       "      <td>215.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1757</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15003</td>\n",
       "      <td>CMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15004</td>\n",
       "      <td>CMA</td>\n",
       "      <td>96.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15005</td>\n",
       "      <td>CMA</td>\n",
       "      <td>131.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15006</td>\n",
       "      <td>CMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15007</td>\n",
       "      <td>CMA</td>\n",
       "      <td>208.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>14996</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>14997</td>\n",
       "      <td>TMA</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>14998</td>\n",
       "      <td>TMA</td>\n",
       "      <td>96.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>14999</td>\n",
       "      <td>TMA</td>\n",
       "      <td>131.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15000</td>\n",
       "      <td>TMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15001</td>\n",
       "      <td>TMA</td>\n",
       "      <td>208.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15002</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25348</td>\n",
       "      <td>TMA</td>\n",
       "      <td>25.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25349</td>\n",
       "      <td>TMA</td>\n",
       "      <td>53.0</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25350</td>\n",
       "      <td>TMA</td>\n",
       "      <td>88.0</td>\n",
       "      <td>17.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25351</td>\n",
       "      <td>TMA</td>\n",
       "      <td>123.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25352</td>\n",
       "      <td>TMA</td>\n",
       "      <td>165.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25353</td>\n",
       "      <td>TMA</td>\n",
       "      <td>207.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25354</td>\n",
       "      <td>Exam</td>\n",
       "      <td>261.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>30709</td>\n",
       "      <td>TMA</td>\n",
       "      <td>33.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>30710</td>\n",
       "      <td>TMA</td>\n",
       "      <td>68.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>30711</td>\n",
       "      <td>TMA</td>\n",
       "      <td>124.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>30712</td>\n",
       "      <td>TMA</td>\n",
       "      <td>159.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>30713</td>\n",
       "      <td>Exam</td>\n",
       "      <td>235.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34878</td>\n",
       "      <td>CMA</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34879</td>\n",
       "      <td>CMA</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34880</td>\n",
       "      <td>CMA</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34881</td>\n",
       "      <td>CMA</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34882</td>\n",
       "      <td>CMA</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34884</td>\n",
       "      <td>CMA</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34883</td>\n",
       "      <td>CMA</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34873</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34874</td>\n",
       "      <td>TMA</td>\n",
       "      <td>47.0</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34875</td>\n",
       "      <td>TMA</td>\n",
       "      <td>96.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34876</td>\n",
       "      <td>TMA</td>\n",
       "      <td>131.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34877</td>\n",
       "      <td>TMA</td>\n",
       "      <td>173.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34885</td>\n",
       "      <td>Exam</td>\n",
       "      <td>236.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37418</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37419</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37420</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37421</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37422</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37423</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37415</td>\n",
       "      <td>TMA</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37416</td>\n",
       "      <td>TMA</td>\n",
       "      <td>124.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37417</td>\n",
       "      <td>TMA</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37424</td>\n",
       "      <td>Exam</td>\n",
       "      <td>229.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    code_module code_presentation  id_assessment assessment_type   date  \\\n",
       "0           AAA             2013J           1752             TMA   19.0   \n",
       "1           AAA             2013J           1753             TMA   54.0   \n",
       "2           AAA             2013J           1754             TMA  117.0   \n",
       "3           AAA             2013J           1755             TMA  166.0   \n",
       "4           AAA             2013J           1756             TMA  215.0   \n",
       "5           AAA             2013J           1757            Exam    NaN   \n",
       "24          BBB             2013J          15003             CMA   54.0   \n",
       "25          BBB             2013J          15004             CMA   96.0   \n",
       "26          BBB             2013J          15005             CMA  131.0   \n",
       "27          BBB             2013J          15006             CMA  166.0   \n",
       "28          BBB             2013J          15007             CMA  208.0   \n",
       "29          BBB             2013J          14996             TMA   19.0   \n",
       "30          BBB             2013J          14997             TMA   47.0   \n",
       "31          BBB             2013J          14998             TMA   96.0   \n",
       "32          BBB             2013J          14999             TMA  131.0   \n",
       "33          BBB             2013J          15000             TMA  166.0   \n",
       "34          BBB             2013J          15001             TMA  208.0   \n",
       "35          BBB             2013J          15002            Exam    NaN   \n",
       "88          DDD             2013J          25348             TMA   25.0   \n",
       "89          DDD             2013J          25349             TMA   53.0   \n",
       "90          DDD             2013J          25350             TMA   88.0   \n",
       "91          DDD             2013J          25351             TMA  123.0   \n",
       "92          DDD             2013J          25352             TMA  165.0   \n",
       "93          DDD             2013J          25353             TMA  207.0   \n",
       "94          DDD             2013J          25354            Exam  261.0   \n",
       "109         EEE             2013J          30709             TMA   33.0   \n",
       "110         EEE             2013J          30710             TMA   68.0   \n",
       "111         EEE             2013J          30711             TMA  124.0   \n",
       "112         EEE             2013J          30712             TMA  159.0   \n",
       "113         EEE             2013J          30713            Exam  235.0   \n",
       "137         FFF             2013J          34878             CMA  236.0   \n",
       "138         FFF             2013J          34879             CMA  236.0   \n",
       "139         FFF             2013J          34880             CMA  236.0   \n",
       "140         FFF             2013J          34881             CMA  236.0   \n",
       "141         FFF             2013J          34882             CMA  236.0   \n",
       "142         FFF             2013J          34884             CMA  236.0   \n",
       "143         FFF             2013J          34883             CMA  236.0   \n",
       "144         FFF             2013J          34873             TMA   19.0   \n",
       "145         FFF             2013J          34874             TMA   47.0   \n",
       "146         FFF             2013J          34875             TMA   96.0   \n",
       "147         FFF             2013J          34876             TMA  131.0   \n",
       "148         FFF             2013J          34877             TMA  173.0   \n",
       "149         FFF             2013J          34885            Exam  236.0   \n",
       "176         GGG             2013J          37418             CMA  229.0   \n",
       "177         GGG             2013J          37419             CMA  229.0   \n",
       "178         GGG             2013J          37420             CMA  229.0   \n",
       "179         GGG             2013J          37421             CMA  229.0   \n",
       "180         GGG             2013J          37422             CMA  229.0   \n",
       "181         GGG             2013J          37423             CMA  229.0   \n",
       "182         GGG             2013J          37415             TMA   61.0   \n",
       "183         GGG             2013J          37416             TMA  124.0   \n",
       "184         GGG             2013J          37417             TMA  173.0   \n",
       "185         GGG             2013J          37424            Exam  229.0   \n",
       "\n",
       "     weight  \n",
       "0      10.0  \n",
       "1      20.0  \n",
       "2      20.0  \n",
       "3      20.0  \n",
       "4      30.0  \n",
       "5     100.0  \n",
       "24      1.0  \n",
       "25      1.0  \n",
       "26      1.0  \n",
       "27      1.0  \n",
       "28      1.0  \n",
       "29      5.0  \n",
       "30     18.0  \n",
       "31     18.0  \n",
       "32     18.0  \n",
       "33     18.0  \n",
       "34     18.0  \n",
       "35    100.0  \n",
       "88     10.0  \n",
       "89     12.5  \n",
       "90     17.5  \n",
       "91     20.0  \n",
       "92     20.0  \n",
       "93     20.0  \n",
       "94    100.0  \n",
       "109    16.0  \n",
       "110    28.0  \n",
       "111    28.0  \n",
       "112    28.0  \n",
       "113   100.0  \n",
       "137     0.0  \n",
       "138     0.0  \n",
       "139     0.0  \n",
       "140     0.0  \n",
       "141     0.0  \n",
       "142     0.0  \n",
       "143     0.0  \n",
       "144    12.5  \n",
       "145    12.5  \n",
       "146    25.0  \n",
       "147    25.0  \n",
       "148    25.0  \n",
       "149   100.0  \n",
       "176     0.0  \n",
       "177     0.0  \n",
       "178     0.0  \n",
       "179     0.0  \n",
       "180     0.0  \n",
       "181     0.0  \n",
       "182     0.0  \n",
       "183     0.0  \n",
       "184     0.0  \n",
       "185   100.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.query(\"code_presentation == '2013J'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>count_student</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013B</td>\n",
       "      <td>1262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014B</td>\n",
       "      <td>1124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CCC</td>\n",
       "      <td>2014B</td>\n",
       "      <td>1038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CCC</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013B</td>\n",
       "      <td>872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2014B</td>\n",
       "      <td>739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2014B</td>\n",
       "      <td>521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2014J</td>\n",
       "      <td>886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013B</td>\n",
       "      <td>1203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2014B</td>\n",
       "      <td>1039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>625</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code_module code_presentation  count_student\n",
       "0          AAA             2013J            323\n",
       "1          AAA             2014J            299\n",
       "2          BBB             2013B           1262\n",
       "3          BBB             2013J           1590\n",
       "4          BBB             2014B           1124\n",
       "5          BBB             2014J           1556\n",
       "6          CCC             2014B           1038\n",
       "7          CCC             2014J           1449\n",
       "8          DDD             2013B            872\n",
       "9          DDD             2013J           1254\n",
       "10         DDD             2014B            739\n",
       "11         DDD             2014J           1172\n",
       "12         EEE             2013J            809\n",
       "13         EEE             2014B            521\n",
       "14         EEE             2014J            886\n",
       "15         FFF             2013B           1203\n",
       "16         FFF             2013J           1606\n",
       "17         FFF             2014B           1039\n",
       "18         FFF             2014J           1534\n",
       "19         GGG             2013J            887\n",
       "20         GGG             2014B            733\n",
       "21         GGG             2014J            625"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# количество студентов зарегистрированных на курс и не отменивших регистрацию\n",
    "studentRegistration[studentRegistration['date_unregistration'].isna()].\\\n",
    "                    groupby(['code_module', 'code_presentation']).\\\n",
    "                    agg({'id_student':'count'}).\\\n",
    "                    rename(columns={'id_student':'count_student'}).\\\n",
    "                    reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22521"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentRegistration[studentRegistration['date_unregistration'].isna()].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>module_presentation_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014J</td>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013B</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_module code_presentation  module_presentation_length\n",
       "0         AAA             2013J                         268\n",
       "1         AAA             2014J                         269\n",
       "2         BBB             2013J                         268\n",
       "3         BBB             2014J                         262\n",
       "4         BBB             2013B                         240"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "courses.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>module_presentation_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">AAA</th>\n",
       "      <th>2013J</th>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">BBB</th>\n",
       "      <th>2013B</th>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">CCC</th>\n",
       "      <th>2014B</th>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">DDD</th>\n",
       "      <th>2013B</th>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">EEE</th>\n",
       "      <th>2013J</th>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">FFF</th>\n",
       "      <th>2013B</th>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">GGG</th>\n",
       "      <th>2013J</th>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               module_presentation_length\n",
       "code_module code_presentation                            \n",
       "AAA         2013J                                     268\n",
       "            2014J                                     269\n",
       "BBB         2013B                                     240\n",
       "            2013J                                     268\n",
       "            2014B                                     234\n",
       "            2014J                                     262\n",
       "CCC         2014B                                     241\n",
       "            2014J                                     269\n",
       "DDD         2013B                                     240\n",
       "            2013J                                     261\n",
       "            2014B                                     241\n",
       "            2014J                                     262\n",
       "EEE         2013J                                     268\n",
       "            2014B                                     241\n",
       "            2014J                                     269\n",
       "FFF         2013B                                     240\n",
       "            2013J                                     268\n",
       "            2014B                                     241\n",
       "            2014J                                     269\n",
       "GGG         2013J                                     261\n",
       "            2014B                                     241\n",
       "            2014J                                     269"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Проверим, совпадает ли длительность семестра для разных годов\n",
    "courses.groupby(['code_module', 'code_presentation']).\\\n",
    "        agg({'module_presentation_length':'sum'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Вес теста за курс: экзамен - 100%, сумма всех остальных оценок тоже 100%\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAA</th>\n",
       "      <th>2013J</th>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               weight\n",
       "code_module code_presentation        \n",
       "AAA         2013J               100.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.query(\"assessment_type != 'Exam' and code_module == 'AAA' and code_presentation == '2013J'\").groupby(['code_module', 'code_presentation']).agg({'weight':'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BBB</th>\n",
       "      <th>2013J</th>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               weight\n",
       "code_module code_presentation        \n",
       "BBB         2013J               100.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assessments.query(\"assessment_type != 'Exam' and code_module == 'BBB' and code_presentation == '2013J'\").groupby(['code_module', 'code_presentation']).agg({'weight':'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37418</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37419</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37420</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37421</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37422</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37423</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37415</td>\n",
       "      <td>TMA</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37416</td>\n",
       "      <td>TMA</td>\n",
       "      <td>124.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37417</td>\n",
       "      <td>TMA</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37424</td>\n",
       "      <td>Exam</td>\n",
       "      <td>229.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37428</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37429</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37430</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37431</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37432</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37433</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37425</td>\n",
       "      <td>TMA</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37426</td>\n",
       "      <td>TMA</td>\n",
       "      <td>117.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37427</td>\n",
       "      <td>TMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37434</td>\n",
       "      <td>Exam</td>\n",
       "      <td>222.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37438</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37439</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37440</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37441</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37442</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37443</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37435</td>\n",
       "      <td>TMA</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37436</td>\n",
       "      <td>TMA</td>\n",
       "      <td>124.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37437</td>\n",
       "      <td>TMA</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37444</td>\n",
       "      <td>Exam</td>\n",
       "      <td>229.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    code_module code_presentation  id_assessment assessment_type   date  \\\n",
       "176         GGG             2013J          37418             CMA  229.0   \n",
       "177         GGG             2013J          37419             CMA  229.0   \n",
       "178         GGG             2013J          37420             CMA  229.0   \n",
       "179         GGG             2013J          37421             CMA  229.0   \n",
       "180         GGG             2013J          37422             CMA  229.0   \n",
       "181         GGG             2013J          37423             CMA  229.0   \n",
       "182         GGG             2013J          37415             TMA   61.0   \n",
       "183         GGG             2013J          37416             TMA  124.0   \n",
       "184         GGG             2013J          37417             TMA  173.0   \n",
       "185         GGG             2013J          37424            Exam  229.0   \n",
       "186         GGG             2014B          37428             CMA  222.0   \n",
       "187         GGG             2014B          37429             CMA  222.0   \n",
       "188         GGG             2014B          37430             CMA  222.0   \n",
       "189         GGG             2014B          37431             CMA  222.0   \n",
       "190         GGG             2014B          37432             CMA  222.0   \n",
       "191         GGG             2014B          37433             CMA  222.0   \n",
       "192         GGG             2014B          37425             TMA   61.0   \n",
       "193         GGG             2014B          37426             TMA  117.0   \n",
       "194         GGG             2014B          37427             TMA  166.0   \n",
       "195         GGG             2014B          37434            Exam  222.0   \n",
       "196         GGG             2014J          37438             CMA  229.0   \n",
       "197         GGG             2014J          37439             CMA  229.0   \n",
       "198         GGG             2014J          37440             CMA  229.0   \n",
       "199         GGG             2014J          37441             CMA  229.0   \n",
       "200         GGG             2014J          37442             CMA  229.0   \n",
       "201         GGG             2014J          37443             CMA  229.0   \n",
       "202         GGG             2014J          37435             TMA   61.0   \n",
       "203         GGG             2014J          37436             TMA  124.0   \n",
       "204         GGG             2014J          37437             TMA  173.0   \n",
       "205         GGG             2014J          37444            Exam  229.0   \n",
       "\n",
       "     weight  \n",
       "176     0.0  \n",
       "177     0.0  \n",
       "178     0.0  \n",
       "179     0.0  \n",
       "180     0.0  \n",
       "181     0.0  \n",
       "182     0.0  \n",
       "183     0.0  \n",
       "184     0.0  \n",
       "185   100.0  \n",
       "186     0.0  \n",
       "187     0.0  \n",
       "188     0.0  \n",
       "189     0.0  \n",
       "190     0.0  \n",
       "191     0.0  \n",
       "192     0.0  \n",
       "193     0.0  \n",
       "194     0.0  \n",
       "195   100.0  \n",
       "196     0.0  \n",
       "197     0.0  \n",
       "198     0.0  \n",
       "199     0.0  \n",
       "200     0.0  \n",
       "201     0.0  \n",
       "202     0.0  \n",
       "203     0.0  \n",
       "204     0.0  \n",
       "205   100.0  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# По предмету GGG имел вес только экзамен\n",
    "\n",
    "assessments.query('code_module == \"GGG\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id_student\n",
       "           count\n",
       "count           \n",
       "1          25247\n",
       "2           3293\n",
       "3            221\n",
       "4             23\n",
       "5              1"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentRegistration['id_student'].value_counts().reset_index().groupby('count').agg({'count'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Судя по подсчету id_student, значения не уникальные и могут повторяться. Возможно, это студенты, которые были переведены на другой курс"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_registration</th>\n",
       "      <th>date_unregistration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15292</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>562064</td>\n",
       "      <td>-198.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26658</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2014B</td>\n",
       "      <td>562064</td>\n",
       "      <td>-255.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28199</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2014J</td>\n",
       "      <td>562064</td>\n",
       "      <td>-22.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code_module code_presentation  id_student  date_registration  \\\n",
       "15292         DDD             2013J      562064             -198.0   \n",
       "26658         FFF             2014B      562064             -255.0   \n",
       "28199         FFF             2014J      562064              -22.0   \n",
       "\n",
       "       date_unregistration  \n",
       "15292                  NaN  \n",
       "26658                135.0  \n",
       "28199                  NaN  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentRegistration.query('id_student == 562064')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Изучив данные по таблице assessments, увидел, что в каждом семестре есть похожие предметы \n",
    "и все они заканчиваются экзаменом в конце.\n",
    "Я сделал таблицу по количеству студентов в каждом семестре по каждому предмету.\n",
    "По ней я не увидел прямой закономерности количества студентов. Обычно, если курс начался и был набор студентов на него, дальше они отсеиваются, если не сдают экзамен.\n",
    "Здесь же, такой закономерности нет.\n",
    "\n",
    "Но дальше я обратил внимание на столбец weight и информацию по нему: \"вес теста в % в оценке за курс. \n",
    "Обычно экзамены рассматриваются отдельно и имеют вес 100%; сумма всех остальных оценок составляет 100%.\"\n",
    "Т.к. за вес экзамена по умолчанию 100%, мне оставалось проверить, будет ли сумма тестов за предмет в каждом семестре равна 100%. Сделал проверку для 4х предметов в разных семестрах и оказалось, что их вес 100%\n",
    "\n",
    "Из этого я делаю вывод, что 1 предмет в отдельном семестре - это 1 курс.\n",
    "Теперь можно ответить на 1 вопрос"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\"\n",
    "<b>\n",
    "    \n",
    "1. Сколько студентов успешно сдали только один курс? (Успешная сдача — это зачёт по курсу на экзамене).\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1757</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>1763</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013B</td>\n",
       "      <td>14990</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>15002</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014B</td>\n",
       "      <td>15014</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014J</td>\n",
       "      <td>15025</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>CCC</td>\n",
       "      <td>2014B</td>\n",
       "      <td>24290</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>CCC</td>\n",
       "      <td>2014B</td>\n",
       "      <td>40087</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>CCC</td>\n",
       "      <td>2014J</td>\n",
       "      <td>24299</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>CCC</td>\n",
       "      <td>2014J</td>\n",
       "      <td>40088</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013B</td>\n",
       "      <td>25340</td>\n",
       "      <td>Exam</td>\n",
       "      <td>240.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25354</td>\n",
       "      <td>Exam</td>\n",
       "      <td>261.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2014B</td>\n",
       "      <td>25361</td>\n",
       "      <td>Exam</td>\n",
       "      <td>241.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2014J</td>\n",
       "      <td>25368</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>30713</td>\n",
       "      <td>Exam</td>\n",
       "      <td>235.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2014B</td>\n",
       "      <td>30718</td>\n",
       "      <td>Exam</td>\n",
       "      <td>228.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>EEE</td>\n",
       "      <td>2014J</td>\n",
       "      <td>30723</td>\n",
       "      <td>Exam</td>\n",
       "      <td>235.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013B</td>\n",
       "      <td>34872</td>\n",
       "      <td>Exam</td>\n",
       "      <td>222.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>34885</td>\n",
       "      <td>Exam</td>\n",
       "      <td>236.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2014B</td>\n",
       "      <td>34898</td>\n",
       "      <td>Exam</td>\n",
       "      <td>227.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2014J</td>\n",
       "      <td>34911</td>\n",
       "      <td>Exam</td>\n",
       "      <td>241.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>37424</td>\n",
       "      <td>Exam</td>\n",
       "      <td>229.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>37434</td>\n",
       "      <td>Exam</td>\n",
       "      <td>222.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>37444</td>\n",
       "      <td>Exam</td>\n",
       "      <td>229.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    code_module code_presentation  id_assessment assessment_type   date  \\\n",
       "5           AAA             2013J           1757            Exam    NaN   \n",
       "11          AAA             2014J           1763            Exam    NaN   \n",
       "23          BBB             2013B          14990            Exam    NaN   \n",
       "35          BBB             2013J          15002            Exam    NaN   \n",
       "47          BBB             2014B          15014            Exam    NaN   \n",
       "53          BBB             2014J          15025            Exam    NaN   \n",
       "62          CCC             2014B          24290            Exam    NaN   \n",
       "63          CCC             2014B          40087            Exam    NaN   \n",
       "72          CCC             2014J          24299            Exam    NaN   \n",
       "73          CCC             2014J          40088            Exam    NaN   \n",
       "87          DDD             2013B          25340            Exam  240.0   \n",
       "94          DDD             2013J          25354            Exam  261.0   \n",
       "101         DDD             2014B          25361            Exam  241.0   \n",
       "108         DDD             2014J          25368            Exam    NaN   \n",
       "113         EEE             2013J          30713            Exam  235.0   \n",
       "118         EEE             2014B          30718            Exam  228.0   \n",
       "123         EEE             2014J          30723            Exam  235.0   \n",
       "136         FFF             2013B          34872            Exam  222.0   \n",
       "149         FFF             2013J          34885            Exam  236.0   \n",
       "162         FFF             2014B          34898            Exam  227.0   \n",
       "175         FFF             2014J          34911            Exam  241.0   \n",
       "185         GGG             2013J          37424            Exam  229.0   \n",
       "195         GGG             2014B          37434            Exam  222.0   \n",
       "205         GGG             2014J          37444            Exam  229.0   \n",
       "\n",
       "     weight  \n",
       "5     100.0  \n",
       "11    100.0  \n",
       "23    100.0  \n",
       "35    100.0  \n",
       "47    100.0  \n",
       "53    100.0  \n",
       "62    100.0  \n",
       "63    100.0  \n",
       "72    100.0  \n",
       "73    100.0  \n",
       "87    100.0  \n",
       "94    100.0  \n",
       "101   100.0  \n",
       "108   100.0  \n",
       "113   100.0  \n",
       "118   100.0  \n",
       "123   100.0  \n",
       "136   100.0  \n",
       "149   100.0  \n",
       "162   100.0  \n",
       "175   100.0  \n",
       "185   100.0  \n",
       "195   100.0  \n",
       "205   100.0  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Доказательство, что в данных нет оценок за экзамены\n",
    "assessments.query(\"assessment_type == 'Exam'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id_assessment, id_student, date_submitted, is_banked, score]\n",
       "Index: []"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Взял 3 выборочных значения и не нашла совпадений для всех них\n",
    "studentAssessment.query(\"id_assessment == 1757\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id_assessment, id_student, date_submitted, is_banked, score]\n",
       "Index: []"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentAssessment.query(\"id_assessment == 15014\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id_assessment, id_student, date_submitted, is_banked, score]\n",
       "Index: []"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentAssessment.query(\"id_assessment == 34885\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Поэтому я предположил, что зачет перед сдачей экзамена - это наличие сданных тестов по этому предмету на проходной балл(40 и более баллов)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1756</th>\n",
       "      <td>1758</td>\n",
       "      <td>6516</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970</th>\n",
       "      <td>1759</td>\n",
       "      <td>6516</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2376</th>\n",
       "      <td>1760</td>\n",
       "      <td>6516</td>\n",
       "      <td>116</td>\n",
       "      <td>0</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2685</th>\n",
       "      <td>1761</td>\n",
       "      <td>6516</td>\n",
       "      <td>164</td>\n",
       "      <td>0</td>\n",
       "      <td>61.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2871</th>\n",
       "      <td>1762</td>\n",
       "      <td>6516</td>\n",
       "      <td>210</td>\n",
       "      <td>0</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id_assessment  id_student  date_submitted  is_banked  score\n",
       "1756           1758        6516              17          0   60.0\n",
       "1970           1759        6516              51          0   48.0\n",
       "2376           1760        6516             116          0   63.0\n",
       "2685           1761        6516             164          0   61.0\n",
       "2871           1762        6516             210          0   77.0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Какие тесты сдавал отдельно взятый студент с id 6516\n",
    "studentAssessment.query(\"id_student == 6516\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id_assessment, id_student, date_submitted, is_banked, score]\n",
       "Index: []"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "studentAssessment.query(\"score == 'NaN'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ! Учесть тех, кто не сдал какой то экзамен (173 строки!)\n",
    "# замена на ноль\n",
    "studentAssessment['score'] = studentAssessment['score'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id_assessment, id_student, date_submitted, is_banked, score]\n",
       "Index: []"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Проверка, что замена на 0 прошла успешно\n",
    "studentAssessment.query(\"score == 'NaN'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6516</td>\n",
       "      <td>[60.0, 48.0, 63.0, 61.0, 77.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8462</td>\n",
       "      <td>[93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11391</td>\n",
       "      <td>[78.0, 85.0, 80.0, 85.0, 82.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23629</td>\n",
       "      <td>[67.0, 63.0, 100.0, 100.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23698</td>\n",
       "      <td>[94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23364</th>\n",
       "      <td>2698251</td>\n",
       "      <td>[69.0, 76.0, 53.0, 67.0, 62.0, 36.0, 44.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23365</th>\n",
       "      <td>2698257</td>\n",
       "      <td>[58.0, 64.0, 76.0, 67.0, 74.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23366</th>\n",
       "      <td>2698535</td>\n",
       "      <td>[23.0, 20.0, 41.0, 13.0, 61.0, 58.0, 62.0, 36.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23367</th>\n",
       "      <td>2698577</td>\n",
       "      <td>[100.0, 54.0, 56.0, 58.0, 54.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23368</th>\n",
       "      <td>2698588</td>\n",
       "      <td>[100.0, 85.0, 87.0, 95.0, 95.0]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23369 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       id_student                                              score\n",
       "0            6516                     [60.0, 48.0, 63.0, 61.0, 77.0]\n",
       "1            8462         [93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]\n",
       "2           11391                     [78.0, 85.0, 80.0, 85.0, 82.0]\n",
       "3           23629                         [67.0, 63.0, 100.0, 100.0]\n",
       "4           23698  [94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....\n",
       "...           ...                                                ...\n",
       "23364     2698251         [69.0, 76.0, 53.0, 67.0, 62.0, 36.0, 44.0]\n",
       "23365     2698257                     [58.0, 64.0, 76.0, 67.0, 74.0]\n",
       "23366     2698535   [23.0, 20.0, 41.0, 13.0, 61.0, 58.0, 62.0, 36.0]\n",
       "23367     2698577                    [100.0, 54.0, 56.0, 58.0, 54.0]\n",
       "23368     2698588                    [100.0, 85.0, 87.0, 95.0, 95.0]\n",
       "\n",
       "[23369 rows x 2 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Группируем по id студента, потому что он уникален для каждого студента на любом курсе (доказано выше)\n",
    "# И получаем все оценки по тестам, которые студент сдавал по своему предмету (курсу) \n",
    "student_scores = studentAssessment.groupby('id_student').agg({'score': lambda x: x.tolist()}).reset_index()\n",
    "student_scores"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Группируем по id студента, потому что он уникален для каждого студента на людом курсе (доказано выше)\n",
    "И оставляем минимальное значение среди оценок за тесты"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>min_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6516</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8462</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11391</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23629</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23698</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23364</th>\n",
       "      <td>2698251</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23365</th>\n",
       "      <td>2698257</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23366</th>\n",
       "      <td>2698535</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23367</th>\n",
       "      <td>2698577</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23368</th>\n",
       "      <td>2698588</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23369 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       id_student  min_score\n",
       "0            6516       48.0\n",
       "1            8462       83.0\n",
       "2           11391       78.0\n",
       "3           23629       63.0\n",
       "4           23698       56.0\n",
       "...           ...        ...\n",
       "23364     2698251       36.0\n",
       "23365     2698257       58.0\n",
       "23366     2698535       13.0\n",
       "23367     2698577       54.0\n",
       "23368     2698588       85.0\n",
       "\n",
       "[23369 rows x 2 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student_scores_min = studentAssessment.groupby('id_student').agg({'score': 'min'}).rename(columns={'score': 'min_score'}).reset_index()\n",
    "student_scores_min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18402"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Количество студентов, которые успешно сдали 1 курс. (Т.е. сдали тесты по своему предмету > чем на 39 баллов и были допущены к экзамену)\n",
    "student_scores_min.query(\"min_score >= 40\").shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\"\n",
    "<b>\n",
    "    \n",
    "2. Выявить самый сложный и самый простой экзамен: найди курсы и экзамены в рамках курса, которые обладают самой низкой и самой высокой завершаемостью (завершаемость = кол-во успешных экзаменов / кол-во всех попыток сдать экзамен).\n",
    "Под экзаменом здесь я понимаю отдельный тест\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>count_try_pass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1752</td>\n",
       "      <td>359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1753</td>\n",
       "      <td>342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1754</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1755</td>\n",
       "      <td>303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1756</td>\n",
       "      <td>298</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_assessment  count_try_pass\n",
       "0           1752             359\n",
       "1           1753             342\n",
       "2           1754             331\n",
       "3           1755             303\n",
       "4           1756             298"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# количество попыток сдать каждый экзамен\n",
    "count_pass = studentAssessment.groupby(['id_assessment']).agg({'score': 'count'}).reset_index()\n",
    "count_pass = count_pass.rename(columns={'score': 'count_try_pass'})\n",
    "count_pass.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>count_pozitiv_pass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1752</td>\n",
       "      <td>352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1753</td>\n",
       "      <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1754</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1755</td>\n",
       "      <td>294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1756</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_assessment  count_pozitiv_pass\n",
       "0           1752                 352\n",
       "1           1753                 330\n",
       "2           1754                 325\n",
       "3           1755                 294\n",
       "4           1756                 290"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# количество успешных попыток сдать каждый экзамен\n",
    "count_pozitiv_pass = studentAssessment.query(\"score >= 40\").groupby(['id_assessment']).agg({'score': 'count'}).reset_index()\n",
    "count_pozitiv_pass = count_pozitiv_pass.rename(columns={'score': 'count_pozitiv_pass'})\n",
    "count_pozitiv_pass.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>count_try_pass</th>\n",
       "      <th>count_pozitiv_pass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1752</td>\n",
       "      <td>359</td>\n",
       "      <td>352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1753</td>\n",
       "      <td>342</td>\n",
       "      <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1754</td>\n",
       "      <td>331</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1755</td>\n",
       "      <td>303</td>\n",
       "      <td>294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1756</td>\n",
       "      <td>298</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_assessment  count_try_pass  count_pozitiv_pass\n",
       "0           1752             359                 352\n",
       "1           1753             342                 330\n",
       "2           1754             331                 325\n",
       "3           1755             303                 294\n",
       "4           1756             298                 290"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Объединяю 2 таблицы, чтобы собрать вместе количество попыток и количество успешных попыток сдать экзамен\n",
    "count_pass = count_pass.merge(count_pozitiv_pass, how = 'inner', on = 'id_assessment')\n",
    "count_pass.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>count_try_pass</th>\n",
       "      <th>count_pozitiv_pass</th>\n",
       "      <th>complete</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1752</td>\n",
       "      <td>359</td>\n",
       "      <td>352</td>\n",
       "      <td>98.050139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1753</td>\n",
       "      <td>342</td>\n",
       "      <td>330</td>\n",
       "      <td>96.491228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1754</td>\n",
       "      <td>331</td>\n",
       "      <td>325</td>\n",
       "      <td>98.187311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1755</td>\n",
       "      <td>303</td>\n",
       "      <td>294</td>\n",
       "      <td>97.029703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1756</td>\n",
       "      <td>298</td>\n",
       "      <td>290</td>\n",
       "      <td>97.315436</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_assessment  count_try_pass  count_pozitiv_pass   complete\n",
       "0           1752             359                 352  98.050139\n",
       "1           1753             342                 330  96.491228\n",
       "2           1754             331                 325  98.187311\n",
       "3           1755             303                 294  97.029703\n",
       "4           1756             298                 290  97.315436"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Создаю столбец, в котором считаю % успешности сдачи каждого теста\n",
    "count_pass['complete'] = count_pass.count_pozitiv_pass / count_pass.count_try_pass * 100\n",
    "count_pass.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>count_try_pass</th>\n",
       "      <th>count_pozitiv_pass</th>\n",
       "      <th>complete</th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1752</td>\n",
       "      <td>359</td>\n",
       "      <td>352</td>\n",
       "      <td>98.050139</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1753</td>\n",
       "      <td>342</td>\n",
       "      <td>330</td>\n",
       "      <td>96.491228</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1754</td>\n",
       "      <td>331</td>\n",
       "      <td>325</td>\n",
       "      <td>98.187311</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>117.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1755</td>\n",
       "      <td>303</td>\n",
       "      <td>294</td>\n",
       "      <td>97.029703</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1756</td>\n",
       "      <td>298</td>\n",
       "      <td>290</td>\n",
       "      <td>97.315436</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>215.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_assessment  count_try_pass  count_pozitiv_pass   complete code_module  \\\n",
       "0           1752             359                 352  98.050139         AAA   \n",
       "1           1753             342                 330  96.491228         AAA   \n",
       "2           1754             331                 325  98.187311         AAA   \n",
       "3           1755             303                 294  97.029703         AAA   \n",
       "4           1756             298                 290  97.315436         AAA   \n",
       "\n",
       "  code_presentation assessment_type   date  weight  \n",
       "0             2013J             TMA   19.0    10.0  \n",
       "1             2013J             TMA   54.0    20.0  \n",
       "2             2013J             TMA  117.0    20.0  \n",
       "3             2013J             TMA  166.0    20.0  \n",
       "4             2013J             TMA  215.0    30.0  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Добавляю в имеющуюся таблицу данные из таблицы assessments, чтобы у теста был предмет, к которому он относится\n",
    "count_pass_module = count_pass.merge(assessments, on = \"id_assessment\")\n",
    "count_pass_module.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ThinkPad\\AppData\\Local\\Temp\\ipykernel_13240\\499876161.py:3: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n",
      "  result_max = count_pass_module.groupby(['code_module','code_presentation']).apply(lambda x: x[x['complete'] == x['complete'].max()])\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>count_try_pass</th>\n",
       "      <th>count_pozitiv_pass</th>\n",
       "      <th>complete</th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">AAA</th>\n",
       "      <th>2013J</th>\n",
       "      <th>2</th>\n",
       "      <td>1754</td>\n",
       "      <td>331</td>\n",
       "      <td>325</td>\n",
       "      <td>98.187311</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>117.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>5</th>\n",
       "      <td>1758</td>\n",
       "      <td>337</td>\n",
       "      <td>333</td>\n",
       "      <td>98.813056</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">BBB</th>\n",
       "      <th>2013B</th>\n",
       "      <th>16</th>\n",
       "      <td>14991</td>\n",
       "      <td>1189</td>\n",
       "      <td>1186</td>\n",
       "      <td>99.747687</td>\n",
       "      <td>BBB</td>\n",
       "      <td>2013B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <th>27</th>\n",
       "      <td>15003</td>\n",
       "      <td>1490</td>\n",
       "      <td>1488</td>\n",
       "      <td>99.865772</td>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>CMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>38</th>\n",
       "      <td>15015</td>\n",
       "      <td>1054</td>\n",
       "      <td>1051</td>\n",
       "      <td>99.715370</td>\n",
       "      <td>BBB</td>\n",
       "      <td>2014B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>47.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>44</th>\n",
       "      <td>15021</td>\n",
       "      <td>1586</td>\n",
       "      <td>1567</td>\n",
       "      <td>98.802018</td>\n",
       "      <td>BBB</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">CCC</th>\n",
       "      <th>2014B</th>\n",
       "      <th>53</th>\n",
       "      <td>24287</td>\n",
       "      <td>1029</td>\n",
       "      <td>957</td>\n",
       "      <td>93.002915</td>\n",
       "      <td>CCC</td>\n",
       "      <td>2014B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>67.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>57</th>\n",
       "      <td>24291</td>\n",
       "      <td>1428</td>\n",
       "      <td>1352</td>\n",
       "      <td>94.677871</td>\n",
       "      <td>CCC</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>32.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">DDD</th>\n",
       "      <th>2013B</th>\n",
       "      <th>74</th>\n",
       "      <td>25342</td>\n",
       "      <td>906</td>\n",
       "      <td>882</td>\n",
       "      <td>97.350993</td>\n",
       "      <td>DDD</td>\n",
       "      <td>2013B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>51.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <th>81</th>\n",
       "      <td>25349</td>\n",
       "      <td>1314</td>\n",
       "      <td>1259</td>\n",
       "      <td>95.814307</td>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>53.0</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>87</th>\n",
       "      <td>25355</td>\n",
       "      <td>930</td>\n",
       "      <td>876</td>\n",
       "      <td>94.193548</td>\n",
       "      <td>DDD</td>\n",
       "      <td>2014B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>25.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>95</th>\n",
       "      <td>25363</td>\n",
       "      <td>1325</td>\n",
       "      <td>1301</td>\n",
       "      <td>98.188679</td>\n",
       "      <td>DDD</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>41.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">EEE</th>\n",
       "      <th>2013J</th>\n",
       "      <th>102</th>\n",
       "      <td>30710</td>\n",
       "      <td>748</td>\n",
       "      <td>744</td>\n",
       "      <td>99.465241</td>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>68.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>105</th>\n",
       "      <td>30714</td>\n",
       "      <td>533</td>\n",
       "      <td>527</td>\n",
       "      <td>98.874296</td>\n",
       "      <td>EEE</td>\n",
       "      <td>2014B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>33.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>110</th>\n",
       "      <td>30720</td>\n",
       "      <td>826</td>\n",
       "      <td>823</td>\n",
       "      <td>99.636804</td>\n",
       "      <td>EEE</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>68.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">FFF</th>\n",
       "      <th>2013B</th>\n",
       "      <th>118</th>\n",
       "      <td>34865</td>\n",
       "      <td>1091</td>\n",
       "      <td>1089</td>\n",
       "      <td>99.816682</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2013B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <th>131</th>\n",
       "      <td>34879</td>\n",
       "      <td>1352</td>\n",
       "      <td>1352</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>CMA</td>\n",
       "      <td>236.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2014B</th>\n",
       "      <th>143</th>\n",
       "      <td>34892</td>\n",
       "      <td>857</td>\n",
       "      <td>857</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2014B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>227.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>34896</td>\n",
       "      <td>747</td>\n",
       "      <td>747</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2014B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>227.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>157</th>\n",
       "      <td>34907</td>\n",
       "      <td>1234</td>\n",
       "      <td>1234</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2014J</td>\n",
       "      <td>CMA</td>\n",
       "      <td>241.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">GGG</th>\n",
       "      <th>2013J</th>\n",
       "      <th>165</th>\n",
       "      <td>37419</td>\n",
       "      <td>685</td>\n",
       "      <td>684</td>\n",
       "      <td>99.854015</td>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>174</th>\n",
       "      <td>37429</td>\n",
       "      <td>559</td>\n",
       "      <td>559</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>183</th>\n",
       "      <td>37439</td>\n",
       "      <td>504</td>\n",
       "      <td>503</td>\n",
       "      <td>99.801587</td>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   id_assessment  count_try_pass  \\\n",
       "code_module code_presentation                                      \n",
       "AAA         2013J             2             1754             331   \n",
       "            2014J             5             1758             337   \n",
       "BBB         2013B             16           14991            1189   \n",
       "            2013J             27           15003            1490   \n",
       "            2014B             38           15015            1054   \n",
       "            2014J             44           15021            1586   \n",
       "CCC         2014B             53           24287            1029   \n",
       "            2014J             57           24291            1428   \n",
       "DDD         2013B             74           25342             906   \n",
       "            2013J             81           25349            1314   \n",
       "            2014B             87           25355             930   \n",
       "            2014J             95           25363            1325   \n",
       "EEE         2013J             102          30710             748   \n",
       "            2014B             105          30714             533   \n",
       "            2014J             110          30720             826   \n",
       "FFF         2013B             118          34865            1091   \n",
       "            2013J             131          34879            1352   \n",
       "            2014B             143          34892             857   \n",
       "                              147          34896             747   \n",
       "            2014J             157          34907            1234   \n",
       "GGG         2013J             165          37419             685   \n",
       "            2014B             174          37429             559   \n",
       "            2014J             183          37439             504   \n",
       "\n",
       "                                   count_pozitiv_pass    complete code_module  \\\n",
       "code_module code_presentation                                                   \n",
       "AAA         2013J             2                   325   98.187311         AAA   \n",
       "            2014J             5                   333   98.813056         AAA   \n",
       "BBB         2013B             16                 1186   99.747687         BBB   \n",
       "            2013J             27                 1488   99.865772         BBB   \n",
       "            2014B             38                 1051   99.715370         BBB   \n",
       "            2014J             44                 1567   98.802018         BBB   \n",
       "CCC         2014B             53                  957   93.002915         CCC   \n",
       "            2014J             57                 1352   94.677871         CCC   \n",
       "DDD         2013B             74                  882   97.350993         DDD   \n",
       "            2013J             81                 1259   95.814307         DDD   \n",
       "            2014B             87                  876   94.193548         DDD   \n",
       "            2014J             95                 1301   98.188679         DDD   \n",
       "EEE         2013J             102                 744   99.465241         EEE   \n",
       "            2014B             105                 527   98.874296         EEE   \n",
       "            2014J             110                 823   99.636804         EEE   \n",
       "FFF         2013B             118                1089   99.816682         FFF   \n",
       "            2013J             131                1352  100.000000         FFF   \n",
       "            2014B             143                 857  100.000000         FFF   \n",
       "                              147                 747  100.000000         FFF   \n",
       "            2014J             157                1234  100.000000         FFF   \n",
       "GGG         2013J             165                 684   99.854015         GGG   \n",
       "            2014B             174                 559  100.000000         GGG   \n",
       "            2014J             183                 503   99.801587         GGG   \n",
       "\n",
       "                                  code_presentation assessment_type   date  \\\n",
       "code_module code_presentation                                                \n",
       "AAA         2013J             2               2013J             TMA  117.0   \n",
       "            2014J             5               2014J             TMA   19.0   \n",
       "BBB         2013B             16              2013B             CMA   54.0   \n",
       "            2013J             27              2013J             CMA   54.0   \n",
       "            2014B             38              2014B             CMA   47.0   \n",
       "            2014J             44              2014J             TMA   54.0   \n",
       "CCC         2014B             53              2014B             CMA   67.0   \n",
       "            2014J             57              2014J             TMA   32.0   \n",
       "DDD         2013B             74              2013B             CMA   51.0   \n",
       "            2013J             81              2013J             TMA   53.0   \n",
       "            2014B             87              2014B             TMA   25.0   \n",
       "            2014J             95              2014J             TMA   41.0   \n",
       "EEE         2013J             102             2013J             TMA   68.0   \n",
       "            2014B             105             2014B             TMA   33.0   \n",
       "            2014J             110             2014J             TMA   68.0   \n",
       "FFF         2013B             118             2013B             CMA  222.0   \n",
       "            2013J             131             2013J             CMA  236.0   \n",
       "            2014B             143             2014B             CMA  227.0   \n",
       "                              147             2014B             CMA  227.0   \n",
       "            2014J             157             2014J             CMA  241.0   \n",
       "GGG         2013J             165             2013J             CMA  229.0   \n",
       "            2014B             174             2014B             CMA  222.0   \n",
       "            2014J             183             2014J             CMA  229.0   \n",
       "\n",
       "                                   weight  \n",
       "code_module code_presentation              \n",
       "AAA         2013J             2      20.0  \n",
       "            2014J             5      10.0  \n",
       "BBB         2013B             16      1.0  \n",
       "            2013J             27      1.0  \n",
       "            2014B             38      1.0  \n",
       "            2014J             44     10.0  \n",
       "CCC         2014B             53      7.0  \n",
       "            2014J             57      9.0  \n",
       "DDD         2013B             74      3.0  \n",
       "            2013J             81     12.5  \n",
       "            2014B             87     10.0  \n",
       "            2014J             95     10.0  \n",
       "EEE         2013J             102    28.0  \n",
       "            2014B             105    16.0  \n",
       "            2014J             110    28.0  \n",
       "FFF         2013B             118     0.0  \n",
       "            2013J             131     0.0  \n",
       "            2014B             143     0.0  \n",
       "                              147     0.0  \n",
       "            2014J             157     0.0  \n",
       "GGG         2013J             165     0.0  \n",
       "            2014B             174     0.0  \n",
       "            2014J             183     0.0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Поиск самого простого экзамена - который сдают легче всего - в КАЖДОМ курсе\n",
    "# Если таких тестов несколько, они все вносятся в таблицу\n",
    "result_max = count_pass_module.groupby(['code_module','code_presentation']).apply(lambda x: x[x['complete'] == x['complete'].max()])\n",
    "result_max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ThinkPad\\AppData\\Local\\Temp\\ipykernel_13240\\507545333.py:3: DeprecationWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n",
      "  result_min = count_pass_module.groupby(['code_module','code_presentation']).apply(lambda x: x[x['complete'] == x['complete'].min()])\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>count_try_pass</th>\n",
       "      <th>count_pozitiv_pass</th>\n",
       "      <th>complete</th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">AAA</th>\n",
       "      <th>2013J</th>\n",
       "      <th>1</th>\n",
       "      <td>1753</td>\n",
       "      <td>342</td>\n",
       "      <td>330</td>\n",
       "      <td>96.491228</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>6</th>\n",
       "      <td>1759</td>\n",
       "      <td>317</td>\n",
       "      <td>297</td>\n",
       "      <td>93.690852</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">BBB</th>\n",
       "      <th>2013B</th>\n",
       "      <th>13</th>\n",
       "      <td>14987</td>\n",
       "      <td>931</td>\n",
       "      <td>887</td>\n",
       "      <td>95.273899</td>\n",
       "      <td>BBB</td>\n",
       "      <td>2013B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>124.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <th>23</th>\n",
       "      <td>14998</td>\n",
       "      <td>1316</td>\n",
       "      <td>1281</td>\n",
       "      <td>97.340426</td>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>96.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>37</th>\n",
       "      <td>15013</td>\n",
       "      <td>684</td>\n",
       "      <td>646</td>\n",
       "      <td>94.444444</td>\n",
       "      <td>BBB</td>\n",
       "      <td>2014B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>194.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>43</th>\n",
       "      <td>15020</td>\n",
       "      <td>1776</td>\n",
       "      <td>1040</td>\n",
       "      <td>58.558559</td>\n",
       "      <td>BBB</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">CCC</th>\n",
       "      <th>2014B</th>\n",
       "      <th>52</th>\n",
       "      <td>24286</td>\n",
       "      <td>1346</td>\n",
       "      <td>1089</td>\n",
       "      <td>80.906389</td>\n",
       "      <td>CCC</td>\n",
       "      <td>2014B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>65</th>\n",
       "      <td>24299</td>\n",
       "      <td>1168</td>\n",
       "      <td>1019</td>\n",
       "      <td>87.243151</td>\n",
       "      <td>CCC</td>\n",
       "      <td>2014J</td>\n",
       "      <td>Exam</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">DDD</th>\n",
       "      <th>2013B</th>\n",
       "      <th>71</th>\n",
       "      <td>25339</td>\n",
       "      <td>503</td>\n",
       "      <td>392</td>\n",
       "      <td>77.932406</td>\n",
       "      <td>DDD</td>\n",
       "      <td>2013B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>200.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <th>85</th>\n",
       "      <td>25353</td>\n",
       "      <td>845</td>\n",
       "      <td>664</td>\n",
       "      <td>78.579882</td>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>207.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>92</th>\n",
       "      <td>25360</td>\n",
       "      <td>450</td>\n",
       "      <td>334</td>\n",
       "      <td>74.222222</td>\n",
       "      <td>DDD</td>\n",
       "      <td>2014B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>200.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>98</th>\n",
       "      <td>25366</td>\n",
       "      <td>1043</td>\n",
       "      <td>880</td>\n",
       "      <td>84.372004</td>\n",
       "      <td>DDD</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>146.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">EEE</th>\n",
       "      <th>2013J</th>\n",
       "      <th>103</th>\n",
       "      <td>30711</td>\n",
       "      <td>682</td>\n",
       "      <td>657</td>\n",
       "      <td>96.334311</td>\n",
       "      <td>EEE</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>124.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>108</th>\n",
       "      <td>30717</td>\n",
       "      <td>372</td>\n",
       "      <td>351</td>\n",
       "      <td>94.354839</td>\n",
       "      <td>EEE</td>\n",
       "      <td>2014B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>152.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>111</th>\n",
       "      <td>30721</td>\n",
       "      <td>770</td>\n",
       "      <td>750</td>\n",
       "      <td>97.402597</td>\n",
       "      <td>EEE</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>131.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">FFF</th>\n",
       "      <th>2013B</th>\n",
       "      <th>115</th>\n",
       "      <td>34862</td>\n",
       "      <td>1076</td>\n",
       "      <td>1011</td>\n",
       "      <td>93.959108</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2013B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>89.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013J</th>\n",
       "      <th>127</th>\n",
       "      <td>34875</td>\n",
       "      <td>1402</td>\n",
       "      <td>1295</td>\n",
       "      <td>92.368046</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>96.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>141</th>\n",
       "      <td>34890</td>\n",
       "      <td>715</td>\n",
       "      <td>668</td>\n",
       "      <td>93.426573</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2014B</td>\n",
       "      <td>TMA</td>\n",
       "      <td>171.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>153</th>\n",
       "      <td>34903</td>\n",
       "      <td>1137</td>\n",
       "      <td>1065</td>\n",
       "      <td>93.667546</td>\n",
       "      <td>FFF</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>199.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">GGG</th>\n",
       "      <th>2013J</th>\n",
       "      <th>168</th>\n",
       "      <td>37422</td>\n",
       "      <td>576</td>\n",
       "      <td>552</td>\n",
       "      <td>95.833333</td>\n",
       "      <td>GGG</td>\n",
       "      <td>2013J</td>\n",
       "      <td>CMA</td>\n",
       "      <td>229.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014B</th>\n",
       "      <th>177</th>\n",
       "      <td>37432</td>\n",
       "      <td>467</td>\n",
       "      <td>431</td>\n",
       "      <td>92.291221</td>\n",
       "      <td>GGG</td>\n",
       "      <td>2014B</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014J</th>\n",
       "      <th>181</th>\n",
       "      <td>37437</td>\n",
       "      <td>500</td>\n",
       "      <td>472</td>\n",
       "      <td>94.400000</td>\n",
       "      <td>GGG</td>\n",
       "      <td>2014J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   id_assessment  count_try_pass  \\\n",
       "code_module code_presentation                                      \n",
       "AAA         2013J             1             1753             342   \n",
       "            2014J             6             1759             317   \n",
       "BBB         2013B             13           14987             931   \n",
       "            2013J             23           14998            1316   \n",
       "            2014B             37           15013             684   \n",
       "            2014J             43           15020            1776   \n",
       "CCC         2014B             52           24286            1346   \n",
       "            2014J             65           24299            1168   \n",
       "DDD         2013B             71           25339             503   \n",
       "            2013J             85           25353             845   \n",
       "            2014B             92           25360             450   \n",
       "            2014J             98           25366            1043   \n",
       "EEE         2013J             103          30711             682   \n",
       "            2014B             108          30717             372   \n",
       "            2014J             111          30721             770   \n",
       "FFF         2013B             115          34862            1076   \n",
       "            2013J             127          34875            1402   \n",
       "            2014B             141          34890             715   \n",
       "            2014J             153          34903            1137   \n",
       "GGG         2013J             168          37422             576   \n",
       "            2014B             177          37432             467   \n",
       "            2014J             181          37437             500   \n",
       "\n",
       "                                   count_pozitiv_pass   complete code_module  \\\n",
       "code_module code_presentation                                                  \n",
       "AAA         2013J             1                   330  96.491228         AAA   \n",
       "            2014J             6                   297  93.690852         AAA   \n",
       "BBB         2013B             13                  887  95.273899         BBB   \n",
       "            2013J             23                 1281  97.340426         BBB   \n",
       "            2014B             37                  646  94.444444         BBB   \n",
       "            2014J             43                 1040  58.558559         BBB   \n",
       "CCC         2014B             52                 1089  80.906389         CCC   \n",
       "            2014J             65                 1019  87.243151         CCC   \n",
       "DDD         2013B             71                  392  77.932406         DDD   \n",
       "            2013J             85                  664  78.579882         DDD   \n",
       "            2014B             92                  334  74.222222         DDD   \n",
       "            2014J             98                  880  84.372004         DDD   \n",
       "EEE         2013J             103                 657  96.334311         EEE   \n",
       "            2014B             108                 351  94.354839         EEE   \n",
       "            2014J             111                 750  97.402597         EEE   \n",
       "FFF         2013B             115                1011  93.959108         FFF   \n",
       "            2013J             127                1295  92.368046         FFF   \n",
       "            2014B             141                 668  93.426573         FFF   \n",
       "            2014J             153                1065  93.667546         FFF   \n",
       "GGG         2013J             168                 552  95.833333         GGG   \n",
       "            2014B             177                 431  92.291221         GGG   \n",
       "            2014J             181                 472  94.400000         GGG   \n",
       "\n",
       "                                  code_presentation assessment_type   date  \\\n",
       "code_module code_presentation                                                \n",
       "AAA         2013J             1               2013J             TMA   54.0   \n",
       "            2014J             6               2014J             TMA   54.0   \n",
       "BBB         2013B             13              2013B             TMA  124.0   \n",
       "            2013J             23              2013J             TMA   96.0   \n",
       "            2014B             37              2014B             TMA  194.0   \n",
       "            2014J             43              2014J             TMA   19.0   \n",
       "CCC         2014B             52              2014B             CMA   18.0   \n",
       "            2014J             65              2014J            Exam    NaN   \n",
       "DDD         2013B             71              2013B             TMA  200.0   \n",
       "            2013J             85              2013J             TMA  207.0   \n",
       "            2014B             92              2014B             TMA  200.0   \n",
       "            2014J             98              2014J             TMA  146.0   \n",
       "EEE         2013J             103             2013J             TMA  124.0   \n",
       "            2014B             108             2014B             TMA  152.0   \n",
       "            2014J             111             2014J             TMA  131.0   \n",
       "FFF         2013B             115             2013B             TMA   89.0   \n",
       "            2013J             127             2013J             TMA   96.0   \n",
       "            2014B             141             2014B             TMA  171.0   \n",
       "            2014J             153             2014J             TMA  199.0   \n",
       "GGG         2013J             168             2013J             CMA  229.0   \n",
       "            2014B             177             2014B             CMA  222.0   \n",
       "            2014J             181             2014J             TMA  173.0   \n",
       "\n",
       "                                   weight  \n",
       "code_module code_presentation              \n",
       "AAA         2013J             1      20.0  \n",
       "            2014J             6      20.0  \n",
       "BBB         2013B             13     18.0  \n",
       "            2013J             23     18.0  \n",
       "            2014B             37     18.0  \n",
       "            2014J             43      0.0  \n",
       "CCC         2014B             52      2.0  \n",
       "            2014J             65    100.0  \n",
       "DDD         2013B             71     15.0  \n",
       "            2013J             85     20.0  \n",
       "            2014B             92     20.0  \n",
       "            2014J             98     25.0  \n",
       "EEE         2013J             103    28.0  \n",
       "            2014B             108    28.0  \n",
       "            2014J             111    28.0  \n",
       "FFF         2013B             115    25.0  \n",
       "            2013J             127    25.0  \n",
       "            2014B             141    25.0  \n",
       "            2014J             153    25.0  \n",
       "GGG         2013J             168     0.0  \n",
       "            2014B             177     0.0  \n",
       "            2014J             181     0.0  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Поиск самого сложного экзамена - который сдают хуже всего - в КАЖДОМ курсе\n",
    "# Если таких тестов несколько, они все вносятся в таблицу\n",
    "result_min = count_pass_module.groupby(['code_module','code_presentation']).apply(lambda x: x[x['complete'] == x['complete'].min()])\n",
    "result_min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1752</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11391</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1752</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>28400</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1752</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>31604</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1752</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>32885</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>1752</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>38053</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_module code_presentation  id_assessment assessment_type  date  weight  \\\n",
       "0         AAA             2013J           1752             TMA  19.0    10.0   \n",
       "1         AAA             2013J           1752             TMA  19.0    10.0   \n",
       "2         AAA             2013J           1752             TMA  19.0    10.0   \n",
       "3         AAA             2013J           1752             TMA  19.0    10.0   \n",
       "4         AAA             2013J           1752             TMA  19.0    10.0   \n",
       "\n",
       "   id_student  date_submitted  is_banked  score  \n",
       "0       11391              18          0   78.0  \n",
       "1       28400              22          0   70.0  \n",
       "2       31604              17          0   72.0  \n",
       "3       32885              26          0   69.0  \n",
       "4       38053              19          0   79.0  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Объединение таблиц \"studentAssessment\" и \"assessments\" по общему ключу \"id_assessment\", чтобы получить данные по каждому \n",
    "# предмету и тестам в рамках него у каждого студента\n",
    "all_scores = pd.merge(assessments, studentAssessment, on='id_assessment')\n",
    "all_scores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>avg_date_submitted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAA</td>\n",
       "      <td>109.354081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BBB</td>\n",
       "      <td>104.702524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CCC</td>\n",
       "      <td>116.598522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DDD</td>\n",
       "      <td>106.647842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EEE</td>\n",
       "      <td>90.896491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FFF</td>\n",
       "      <td>123.823515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>GGG</td>\n",
       "      <td>152.757671</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_module  avg_date_submitted\n",
       "0         AAA          109.354081\n",
       "1         BBB          104.702524\n",
       "2         CCC          116.598522\n",
       "3         DDD          106.647842\n",
       "4         EEE           90.896491\n",
       "5         FFF          123.823515\n",
       "6         GGG          152.757671"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Среднее количество дней сдачи тестов по каждому предмету\n",
    "mean_date_subm = all_scores.groupby(['code_module']).agg({'date_submitted': 'mean'}).reset_index()\n",
    "mean_date_subm = mean_date_subm.rename(columns={'date_submitted': 'avg_date_submitted'})\n",
    "mean_date_subm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\"\n",
    "<b>\n",
    "    \n",
    "4. Выявить самые популярные предметы (ТОП-3) по количеству регистраций на них. А также предметы с самым большим оттоком (ТОП-3).\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>count_reg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BBB</td>\n",
       "      <td>7909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FFF</td>\n",
       "      <td>7762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DDD</td>\n",
       "      <td>6272</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_module  count_reg\n",
       "1         BBB       7909\n",
       "5         FFF       7762\n",
       "3         DDD       6272"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Количество регистраций по каждому предмету\n",
    "count_reg = studentRegistration.groupby(['code_module']).agg({'id_student': 'count'}).reset_index()\n",
    "count_reg = count_reg.rename(columns={'id_student': 'count_reg'}).sort_values('count_reg', ascending = False)\n",
    "# ТОП-3 популярных предмета по количеству регистраций\n",
    "count_reg.head(3)\n",
    "# Количество регистраций, без учета тех, кто отменил в последствие свою регистрацию."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>count_unreg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BBB</td>\n",
       "      <td>7909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FFF</td>\n",
       "      <td>7762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DDD</td>\n",
       "      <td>6272</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_module  count_unreg\n",
       "1         BBB         7909\n",
       "5         FFF         7762\n",
       "3         DDD         6272"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Количество регистраций на каждый курс, которые были отменены по каждому предмету\n",
    "count_unreg = studentRegistration.query(\"date_unregistration != 'NaN'\").groupby(['code_module']).agg({'id_student': 'count'}).reset_index()\n",
    "count_unreg = count_unreg.rename(columns={'id_student': 'count_unreg'}).sort_values('count_unreg', ascending = False)\n",
    "# ТОП-3 предметов с самым большим оттоком регистраций\n",
    "count_unreg.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\"\n",
    "<b>\n",
    "    \n",
    "5. Используя pandas, в период с начала 2013 по конец 2014 выявить семестр с самой низкой завершаемостью курсов и самыми долгими средними сроками сдачи курсов.\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code_presentation\n",
       "2014J    57\n",
       "2014B    57\n",
       "2013J    53\n",
       "2013B    39\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Проверка, какие семестры есть в таблице assessments\n",
    "assessments.code_presentation.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code_presentation\n",
       "2014J    7\n",
       "2013J    6\n",
       "2014B    6\n",
       "2013B    3\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Проверка, какие семестры есть в таблице courses\n",
    "courses.code_presentation.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>count_try_pass</th>\n",
       "      <th>count_pozitiv_pass</th>\n",
       "      <th>complete</th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1752</td>\n",
       "      <td>359</td>\n",
       "      <td>352</td>\n",
       "      <td>98.050139</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1753</td>\n",
       "      <td>342</td>\n",
       "      <td>330</td>\n",
       "      <td>96.491228</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>54.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1754</td>\n",
       "      <td>331</td>\n",
       "      <td>325</td>\n",
       "      <td>98.187311</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>117.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1755</td>\n",
       "      <td>303</td>\n",
       "      <td>294</td>\n",
       "      <td>97.029703</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>166.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1756</td>\n",
       "      <td>298</td>\n",
       "      <td>290</td>\n",
       "      <td>97.315436</td>\n",
       "      <td>AAA</td>\n",
       "      <td>2013J</td>\n",
       "      <td>TMA</td>\n",
       "      <td>215.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_assessment  count_try_pass  count_pozitiv_pass   complete code_module  \\\n",
       "0           1752             359                 352  98.050139         AAA   \n",
       "1           1753             342                 330  96.491228         AAA   \n",
       "2           1754             331                 325  98.187311         AAA   \n",
       "3           1755             303                 294  97.029703         AAA   \n",
       "4           1756             298                 290  97.315436         AAA   \n",
       "\n",
       "  code_presentation assessment_type   date  weight  \n",
       "0             2013J             TMA   19.0    10.0  \n",
       "1             2013J             TMA   54.0    20.0  \n",
       "2             2013J             TMA  117.0    20.0  \n",
       "3             2013J             TMA  166.0    20.0  \n",
       "4             2013J             TMA  215.0    30.0  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Используем таблицу count_pass_module, в которой у нас уже есть информация о завершаемости каждого теста и \n",
    "# данные по семестрам/предметам\n",
    "count_pass_module.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>mean_complete</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013B</td>\n",
       "      <td>94.616836</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_presentation  mean_complete\n",
       "0             2013B      94.616836"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Для всех курсов в семестре посчитать среднюю завершаемость\n",
    "avg_pass_sem_group = count_pass_module.groupby(['code_presentation']).agg({'complete': 'mean'}).reset_index()\n",
    "avg_pass_sem_group = avg_pass_sem_group.rename(columns={'complete': 'mean_complete'}).sort_values('mean_complete', ascending = True)\n",
    "# Семестр с самой низкой средней завершаемостью курсов\n",
    "avg_pass_sem_group.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Семестр с самыми долгими средними сроками сдачи курсов\n",
    "Используем таблицу all_scores, чтобы взять дату сдачи и сгруппировать уже по семестрам"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>mean_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013J</td>\n",
       "      <td>119.857195</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code_presentation   mean_date\n",
       "1             2013J  119.857195"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Средняя длительность сдачи всех курсов каждого семестра\n",
    "avg_date_sem_group = all_scores.groupby(['code_presentation']).agg({'date_submitted': 'mean'}).reset_index()\n",
    "avg_date_sem_group = avg_date_sem_group.rename(columns={'date_submitted': 'mean_date'}).sort_values('mean_date', ascending = False)\n",
    "# Семестр с самой низкой средней завершаемостью курсов\n",
    "avg_date_sem_group.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\"\n",
    "<b>\n",
    "    \n",
    "6. Построить адаптированные RFM-кластеры студентов, чтобы качественно оценить аудиторию. Для каждого RFM-сегмента построить границы метрик recency, frequency и monetary для интерпретации этих кластеров.\n",
    "\n",
    "</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_module</th>\n",
       "      <th>code_presentation</th>\n",
       "      <th>id_assessment</th>\n",
       "      <th>assessment_type</th>\n",
       "      <th>date</th>\n",
       "      <th>weight</th>\n",
       "      <th>id_student</th>\n",
       "      <th>date_submitted</th>\n",
       "      <th>is_banked</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>33276</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2014B</td>\n",
       "      <td>15008</td>\n",
       "      <td>TMA</td>\n",
       "      <td>12.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>559381</td>\n",
       "      <td>-11</td>\n",
       "      <td>0</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76237</th>\n",
       "      <td>DDD</td>\n",
       "      <td>2013J</td>\n",
       "      <td>25348</td>\n",
       "      <td>TMA</td>\n",
       "      <td>25.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2472145</td>\n",
       "      <td>-10</td>\n",
       "      <td>0</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22282</th>\n",
       "      <td>BBB</td>\n",
       "      <td>2013J</td>\n",
       "      <td>14996</td>\n",
       "      <td>TMA</td>\n",
       "      <td>19.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>610700</td>\n",
       "      <td>-9</td>\n",
       "      <td>0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104658</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013B</td>\n",
       "      <td>34865</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>539759</td>\n",
       "      <td>-8</td>\n",
       "      <td>0</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105967</th>\n",
       "      <td>FFF</td>\n",
       "      <td>2013B</td>\n",
       "      <td>34866</td>\n",
       "      <td>CMA</td>\n",
       "      <td>222.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>557840</td>\n",
       "      <td>-8</td>\n",
       "      <td>0</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       code_module code_presentation  id_assessment assessment_type   date  \\\n",
       "33276          BBB             2014B          15008             TMA   12.0   \n",
       "76237          DDD             2013J          25348             TMA   25.0   \n",
       "22282          BBB             2013J          14996             TMA   19.0   \n",
       "104658         FFF             2013B          34865             CMA  222.0   \n",
       "105967         FFF             2013B          34866             CMA  222.0   \n",
       "\n",
       "        weight  id_student  date_submitted  is_banked  score  \n",
       "33276      5.0      559381             -11          0   63.0  \n",
       "76237     10.0     2472145             -10          0   85.0  \n",
       "22282      5.0      610700              -9          0   75.0  \n",
       "104658     0.0      539759              -8          0   98.0  \n",
       "105967     0.0      557840              -8          0   88.0  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Проверка наличия отрицательных дат\n",
    "all_scores.sort_values('date_submitted').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "502"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Сколько значений  = 0 баллов\n",
    "all_scores.query(\"score == 0\").shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>mean_date_subm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4064</th>\n",
       "      <td>423613</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9515</th>\n",
       "      <td>565851</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18413</th>\n",
       "      <td>653952</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>61352</td>\n",
       "      <td>-3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8635</th>\n",
       "      <td>555297</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id_student  mean_date_subm\n",
       "4064       423613            -4.0\n",
       "9515       565851            -4.0\n",
       "18413      653952            -4.0\n",
       "230         61352            -3.0\n",
       "8635       555297            -2.0"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# R - среднее время сдачи одного экзамена каждым студентом\n",
    "avg_date_idstud = all_scores.groupby(['id_student']).agg({'date_submitted': 'mean'})\n",
    "avg_date_idstud = avg_date_idstud.rename(columns={'date_submitted': 'mean_date_subm'}).reset_index()\n",
    "avg_date_idstud = avg_date_idstud.sort_values('mean_date_subm')\n",
    "avg_date_idstud.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "F - завершаемость курсов студентом - я рассчитывал, как процент успешно пройденных им тестов от общего числа тестов в курсе. Например, если студент успешно сдал 8 из 10 тестов, то его завершаемость будет 80%."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6516</td>\n",
       "      <td>[60.0, 48.0, 63.0, 61.0, 77.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8462</td>\n",
       "      <td>[93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11391</td>\n",
       "      <td>[78.0, 85.0, 80.0, 85.0, 82.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23629</td>\n",
       "      <td>[67.0, 63.0, 100.0, 100.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23698</td>\n",
       "      <td>[94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_student                                              score\n",
       "0        6516                     [60.0, 48.0, 63.0, 61.0, 77.0]\n",
       "1        8462         [93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]\n",
       "2       11391                     [78.0, 85.0, 80.0, 85.0, 82.0]\n",
       "3       23629                         [67.0, 63.0, 100.0, 100.0]\n",
       "4       23698  [94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56...."
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complete_course = student_scores\n",
    "complete_course.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>score</th>\n",
       "      <th>count_scores_above_40</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6516</td>\n",
       "      <td>[60.0, 48.0, 63.0, 61.0, 77.0]</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8462</td>\n",
       "      <td>[93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11391</td>\n",
       "      <td>[78.0, 85.0, 80.0, 85.0, 82.0]</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23629</td>\n",
       "      <td>[67.0, 63.0, 100.0, 100.0]</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23698</td>\n",
       "      <td>[94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_student                                              score  \\\n",
       "0        6516                     [60.0, 48.0, 63.0, 61.0, 77.0]   \n",
       "1        8462         [93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]   \n",
       "2       11391                     [78.0, 85.0, 80.0, 85.0, 82.0]   \n",
       "3       23629                         [67.0, 63.0, 100.0, 100.0]   \n",
       "4       23698  [94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....   \n",
       "\n",
       "   count_scores_above_40  \n",
       "0                      5  \n",
       "1                      7  \n",
       "2                      5  \n",
       "3                      4  \n",
       "4                      9  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Количество оценок больше 39 баллов у каждого студента\n",
    "def count_scores_above_40 (score):\n",
    "    count = sum(score >= 40 for score in score)\n",
    "    return count\n",
    "\n",
    "# Применяем функцию к каждой строке датафрейма\n",
    "complete_course['count_scores_above_40'] = complete_course['score'].apply(count_scores_above_40)\n",
    "\n",
    "complete_course.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>score</th>\n",
       "      <th>count_scores_above_40</th>\n",
       "      <th>count_scores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6516</td>\n",
       "      <td>[60.0, 48.0, 63.0, 61.0, 77.0]</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8462</td>\n",
       "      <td>[93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11391</td>\n",
       "      <td>[78.0, 85.0, 80.0, 85.0, 82.0]</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23629</td>\n",
       "      <td>[67.0, 63.0, 100.0, 100.0]</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23698</td>\n",
       "      <td>[94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_student                                              score  \\\n",
       "0        6516                     [60.0, 48.0, 63.0, 61.0, 77.0]   \n",
       "1        8462         [93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]   \n",
       "2       11391                     [78.0, 85.0, 80.0, 85.0, 82.0]   \n",
       "3       23629                         [67.0, 63.0, 100.0, 100.0]   \n",
       "4       23698  [94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....   \n",
       "\n",
       "   count_scores_above_40  count_scores  \n",
       "0                      5             5  \n",
       "1                      7             7  \n",
       "2                      5             5  \n",
       "3                      4             4  \n",
       "4                      9             9  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Общее количество оценок у каждого студента\n",
    "def count_scores (score):\n",
    "    count = sum(score >= 0 for score in score)\n",
    "    return count\n",
    "\n",
    "# Применяем функцию к каждой строке датафрейма\n",
    "complete_course['count_scores'] = complete_course['score'].apply(count_scores)\n",
    "\n",
    "complete_course.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>score</th>\n",
       "      <th>count_scores_above_40</th>\n",
       "      <th>count_scores</th>\n",
       "      <th>complete_course</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6516</td>\n",
       "      <td>[60.0, 48.0, 63.0, 61.0, 77.0]</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8462</td>\n",
       "      <td>[93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11391</td>\n",
       "      <td>[78.0, 85.0, 80.0, 85.0, 82.0]</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23629</td>\n",
       "      <td>[67.0, 63.0, 100.0, 100.0]</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23698</td>\n",
       "      <td>[94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_student                                              score  \\\n",
       "0        6516                     [60.0, 48.0, 63.0, 61.0, 77.0]   \n",
       "1        8462         [93.0, 83.0, 87.0, 93.0, 83.0, 83.0, 87.0]   \n",
       "2       11391                     [78.0, 85.0, 80.0, 85.0, 82.0]   \n",
       "3       23629                         [67.0, 63.0, 100.0, 100.0]   \n",
       "4       23698  [94.0, 60.0, 69.0, 67.0, 78.0, 93.0, 73.0, 56....   \n",
       "\n",
       "   count_scores_above_40  count_scores  complete_course  \n",
       "0                      5             5            100.0  \n",
       "1                      7             7            100.0  \n",
       "2                      5             5            100.0  \n",
       "3                      4             4            100.0  \n",
       "4                      9             9            100.0  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complete_course['complete_course'] = (complete_course['count_scores_above_40'] / complete_course['count_scores'] * 100).round(2)\n",
    "complete_course.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "complete_course\n",
       "100.00    18402\n",
       "80.00       740\n",
       "0.00        396\n",
       "85.71       374\n",
       "50.00       370\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "complete_course.sort_values('complete_course').value_counts('complete_course').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>mean_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5018</th>\n",
       "      <td>480914</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14571</th>\n",
       "      <td>614345</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8138</th>\n",
       "      <td>549713</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18459</th>\n",
       "      <td>654422</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18590</th>\n",
       "      <td>656122</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id_student  mean_score\n",
       "5018       480914         0.0\n",
       "14571      614345         0.0\n",
       "8138       549713         0.0\n",
       "18459      654422         0.0\n",
       "18590      656122         0.0"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  M - среднее количество баллов, получаемое за экзамен - mean по каждому экзамену - для каждого студента\n",
    "avg_score_idass_group = all_scores.groupby(['id_student']).agg({'score': 'mean'})\n",
    "avg_score_idass_group = avg_score_idass_group.rename(columns={'score': 'mean_score'}).reset_index()\n",
    "avg_score_idass_group = avg_score_idass_group.sort_values('mean_score')\n",
    "avg_score_idass_group.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>mean_date_subm</th>\n",
       "      <th>complete_course</th>\n",
       "      <th>mean_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>423613</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>565851</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>653952</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>61352</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>555297</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_student  mean_date_subm  complete_course  mean_score\n",
       "0      423613            -4.0            100.0       100.0\n",
       "1      565851            -4.0            100.0        80.0\n",
       "2      653952            -4.0            100.0       100.0\n",
       "3       61352            -3.0            100.0       100.0\n",
       "4      555297            -2.0              0.0         0.0"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Столбец айди студента, R, F, M, и их значения\n",
    "stud_rfm = pd.merge(avg_date_idstud, complete_course[['id_student', 'complete_course']], on = 'id_student')\n",
    "stud_rfm = pd.merge(stud_rfm, avg_score_idass_group, on = 'id_student')\n",
    "stud_rfm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. R - Средний срок сдачи экзаменов:\n",
    "\n",
    "    Максимальная длительность семестров была равна 269 дней, поэтому за день окончания семестра я взял это число.\n",
    "   - 5 - студенты, сдавшие экзамены раньше начала семестра - меньше 0 дней\n",
    "   - 4 - студенты, сдавшие экзамены до середины курса - от 0 до 135 дней\n",
    "   - 3 - студенты, сдавшие экзамены в середине курса или чуть позже - от 136 по 204 день\n",
    "   - 2 - студенты, сдавшие экзамены в пределах установленного срока - от 205 дня до 269 дня\n",
    "   - 1 - студенты, сдавшие экзамены после установленного срока - более 269 дней\n",
    "\n",
    "2. F - Завершаемость курса:\n",
    "   - 5 - студенты полностью завершившие курс - 100%\n",
    "   - 4 - студенты, завершившие курс почти полностью - 80-99% (не сдавшие 1 тест)\n",
    "   - 3 - студенты, завершившие курс с задержкой, но все же успешно - 50-79% (половину и более тестов)\n",
    "   - 2 - студенты, сдавшие менее половины тестов - 1 - 49%\n",
    "   - 1 - студенты, не сдавшие ни одного теста - 0%\n",
    "\n",
    "3. M - Средняя оценка за экзамены:\n",
    "   - 5 - студенты с высокими оценками - 100\n",
    "   - 4 - студенты с средними оценками - 76-99\n",
    "   - 3 - студенты с низкими оценками - 40 - 75\n",
    "   - 2 - студенты, получившие оценки ниже неудовлетворительного уровня - 1 - 39\n",
    "   - 1 - студенты, не сдавшие экзамены или получившие нулевые баллы - 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Методы для присвоения рангов от 1 до 5\n",
    "\n",
    "def r_score(x):\n",
    "    if x < 0:\n",
    "        return 5\n",
    "    elif 0 >= x and x <= 135:\n",
    "        return 4\n",
    "    elif 136 >= x and x  <= 204:\n",
    "         return 3\n",
    "    elif 205 >= x and x  <= 269:\n",
    "        return 2\n",
    "    else:\n",
    "        return 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "def f_score(x):\n",
    "    if x == 100:\n",
    "        return 5\n",
    "    elif x < 100 and x > 79:\n",
    "        return 4\n",
    "    elif x < 80 and x > 49:\n",
    "        return 3\n",
    "    elif x < 50 and x > 0:\n",
    "        return 2\n",
    "    else:\n",
    "        return 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "def m_score(x):\n",
    "    if x == 100:\n",
    "        return 5\n",
    "    elif x < 100 and x > 75:\n",
    "        return 4\n",
    "    elif x < 76 and x > 39:\n",
    "        return 3\n",
    "    elif x < 40 and x > 0:\n",
    "        return 2\n",
    "    else:\n",
    "        return 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "stud_rfm['R'] = stud_rfm['mean_date_subm'].apply(lambda x: r_score(x))\n",
    "stud_rfm['F'] = stud_rfm['complete_course'].apply(lambda x: f_score(x))\n",
    "stud_rfm['M'] = stud_rfm['mean_score'].apply(lambda x: m_score(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>mean_date_subm</th>\n",
       "      <th>complete_course</th>\n",
       "      <th>mean_score</th>\n",
       "      <th>R</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>423613</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>565851</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>653952</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>61352</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>555297</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_student  mean_date_subm  complete_course  mean_score  R  F  M\n",
       "0      423613            -4.0            100.0       100.0  5  5  5\n",
       "1      565851            -4.0            100.0        80.0  5  5  4\n",
       "2      653952            -4.0            100.0       100.0  5  5  5\n",
       "3       61352            -3.0            100.0       100.0  5  5  5\n",
       "4      555297            -2.0              0.0         0.0  5  1  1"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stud_rfm.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_student</th>\n",
       "      <th>mean_date_subm</th>\n",
       "      <th>complete_course</th>\n",
       "      <th>mean_score</th>\n",
       "      <th>R</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>RFM Score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>423613</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>565851</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>653952</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>61352</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>555297</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>511</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_student  mean_date_subm  complete_course  mean_score  R  F  M RFM Score\n",
       "0      423613            -4.0            100.0       100.0  5  5  5       555\n",
       "1      565851            -4.0            100.0        80.0  5  5  4       554\n",
       "2      653952            -4.0            100.0       100.0  5  5  5       555\n",
       "3       61352            -3.0            100.0       100.0  5  5  5       555\n",
       "4      555297            -2.0              0.0         0.0  5  1  1       511"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stud_rfm['RFM Score'] = stud_rfm['R'].map(str) + stud_rfm['F'].map(str) + stud_rfm['M'].map(str)\n",
    "stud_rfm.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}