Tutoring_analysis.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c42fadee",
   "metadata": {},
   "source": [
    "# Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3252ff97-19b6-4833-b119-bdb5b36a545b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-04-15T17:47:10.725895Z",
     "start_time": "2023-04-15T17:47:10.463976Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from scipy import stats"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "28155d89",
   "metadata": {},
   "source": [
    "# Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cb30ed5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_assignment_rel=pd.read_csv('./data/assignment_relationships.csv')\n",
    "df_training=pd.read_csv('./data/unit_test_scores.csv')\n",
    "df_problem=pd.read_csv('./data/problem_details.csv')\n",
    "df_actionlogs=pd.read_csv('./data/action_logs.csv')\n",
    "df_problem=pd.read_csv('./data/problem_details.csv')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "7160e9d3",
   "metadata": {},
   "source": [
    "# Preprocess data (make data ready for analysis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d714d333",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_training=df_training.merge(df_assignment_rel,left_on='assignment_log_id',right_on='unit_test_assignment_log_id',how='left')\n",
    "df_training=df_training[['unit_test_assignment_log_id','problem_id', 'score', 'in_unit_assignment_log_id']]\n",
    "df_actionlogs=df_actionlogs[['assignment_log_id','problem_id','available_core_tutoring','action']]\n",
    "\n",
    "one_hot = pd.get_dummies(df_actionlogs[['action','available_core_tutoring']])\n",
    "\n",
    "# Concatenate the original DataFrame with the one-hot encoded DataFrame\n",
    "df_actionlogs = pd.concat([df_actionlogs, one_hot], axis=1)\n",
    "df_actionlogs=df_actionlogs.rename(columns={'problem_id':'problem_id_action'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a10f20b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get all unique problems where skill_video or live_tutor available\n",
    "problem_skill_video_available = df_actionlogs.loc[df_actionlogs['action_skill_related_video_requested'] == 1, 'problem_id_action']\n",
    "problem_live_tutor_available = df_actionlogs.loc[df_actionlogs['action_live_tutor_requested'] == 1, 'problem_id_action']\n",
    "\n",
    "problem_skill_video_available=problem_skill_video_available.to_list()\n",
    "problem_live_tutor_available=problem_live_tutor_available.to_list()\n",
    "problem_skill_video_available=list(set(problem_skill_video_available))\n",
    "problem_live_tutor_available=list(set(problem_live_tutor_available))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "98023fa3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_training_hint=df_training.merge(df_actionlogs,left_on='in_unit_assignment_log_id',right_on='assignment_log_id',how='left')\n",
    "# Create a new column with initial values as 0\n",
    "df_training_hint['skill_related_video_available'] = 0\n",
    "# Set values to 1 where the where problem is skill_video problem.\n",
    "df_training_hint.loc[(df_training_hint['action'] == 'problem_started') & (df_training_hint['problem_id_action'].isin(problem_skill_video_available)), 'skill_related_video_available'] = 1\n",
    "\n",
    "df_training_hint['live_tutor_available'] = 0\n",
    "# Set values to 1 where the condition is satisfied\n",
    "df_training_hint.loc[(df_training_hint['action'] == 'problem_started') & (df_training_hint['problem_id_action'].isin(problem_skill_video_available)), 'live_tutor_available'] = 1"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b04ccfac",
   "metadata": {},
   "source": [
    "# Tutoring analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ea53b00f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# group data for each 'unit_test_assignment_log_id','problem_id'\n",
    "df_training_hint_group=df_training_hint.groupby(['unit_test_assignment_log_id','problem_id'],as_index=False).agg({'problem_id_action':'nunique','available_core_tutoring_hint':'sum',\n",
    "                                                                                                                  'action_answer_requested':'sum','action_correct_response':'sum','action_hint_requested':'sum',\n",
    "                                                                                                                  'action_explanation_requested':'sum','action_live_tutor_requested':'sum',\n",
    "                                                                                                                  'action_skill_related_video_requested':'sum','available_core_tutoring_answer':'sum',\n",
    "                                                                                                                  'available_core_tutoring_explanation':'sum','available_core_tutoring_hint':'sum',\n",
    "                                                                                                                  'action_correct_response':'sum','action_wrong_response':'sum',\n",
    "                                                                                                                  'skill_related_video_available':'sum','live_tutor_available':'sum',\n",
    "                                                                                                                  'score':'first'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b953576c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate all tutoring type percentage requested and avilable\n",
    "df_training_hint_group['per_hint_available']=df_training_hint_group['available_core_tutoring_hint']/df_training_hint_group['problem_id_action']\n",
    "df_training_hint_group['per_explanation_available']=df_training_hint_group['available_core_tutoring_explanation']/df_training_hint_group['problem_id_action']\n",
    "df_training_hint_group['per_answer_available']=df_training_hint_group['available_core_tutoring_answer']/df_training_hint_group['problem_id_action']\n",
    "df_training_hint_group['per_skill_video_available']=df_training_hint_group['skill_related_video_available']/df_training_hint_group['problem_id_action']\n",
    "df_training_hint_group['per_live_tutor_available']=df_training_hint_group['live_tutor_available']/df_training_hint_group['problem_id_action']\n",
    "df_training_hint_group.loc[df_training_hint_group['per_hint_available']>1,'per_hint_available'] = 1\n",
    "df_training_hint_group.loc[df_training_hint_group['per_explanation_available']>1,'per_explanation_available'] = 1\n",
    "df_training_hint_group.loc[df_training_hint_group['per_answer_available']>1,'per_answer_available'] = 1\n",
    "df_training_hint_group['per_hint_requested']=df_training_hint_group['action_hint_requested']/df_training_hint_group['available_core_tutoring_hint']\n",
    "df_training_hint_group['per_explanation_requested']=df_training_hint_group['action_explanation_requested']/df_training_hint_group['available_core_tutoring_explanation']\n",
    "df_training_hint_group['per_answer_requested']=df_training_hint_group['action_answer_requested']/df_training_hint_group['available_core_tutoring_answer']\n",
    "df_training_hint_group['per_skill_video_requested']=df_training_hint_group['action_skill_related_video_requested']/df_training_hint_group['skill_related_video_available']\n",
    "df_training_hint_group['per_live_tutor_requested']=df_training_hint_group['action_live_tutor_requested']/df_training_hint_group['live_tutor_available']\n",
    "df_training_hint_group.loc[df_training_hint_group['per_hint_requested']>1,'per_hint_requested'] = 1\n",
    "df_training_hint_group.loc[df_training_hint_group['per_explanation_requested']>1,'per_explanation_requested'] = 1\n",
    "df_training_hint_group.loc[df_training_hint_group['per_answer_requested']>1,'per_answer_requested'] = 1\n",
    "df_training_hint_group['total_available_toutoring']=df_training_hint_group['available_core_tutoring_hint']+df_training_hint_group['available_core_tutoring_explanation']+df_training_hint_group['available_core_tutoring_answer']+df_training_hint_group['action_skill_related_video_requested']+df_training_hint_group['action_live_tutor_requested']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c27cc5a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_training_hint_group['per_total_avai_tutoring']=df_training_hint_group['total_available_toutoring']/df_training_hint_group['problem_id_action']\n",
    "df_training_hint_group['total_toutoring_requested']=df_training_hint_group['action_answer_requested']+df_training_hint_group['action_hint_requested']+df_training_hint_group['action_explanation_requested']+df_training_hint_group['action_skill_related_video_requested']+df_training_hint_group['action_live_tutor_requested']\n",
    "df_training_hint_group['per_total_req_tutoring']=df_training_hint_group['total_toutoring_requested']/df_training_hint_group['problem_id_action']\n",
    "\n",
    "df_training_hint_group.loc[df_training_hint_group['per_total_avai_tutoring']>1,'per_total_avai_tutoring'] = 1\n",
    "df_training_hint_group.loc[df_training_hint_group['total_toutoring_requested']>1,'total_toutoring_requested'] = 1\n",
    "df_training_hint_group['per_correct_response']=df_training_hint_group['action_correct_response']/df_training_hint_group['problem_id_action']\n",
    "df_training_hint_group['per_wrong_response']=df_training_hint_group['action_wrong_response']/df_training_hint_group['problem_id_action']\n",
    "df_training_hint_group.loc[df_training_hint_group['per_wrong_response']>1,'per_wrong_response'] = 1\n",
    "df_training_hint_group.loc[df_training_hint_group['per_correct_response']>1,'per_correct_response'] = 1"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "8420642f",
   "metadata": {},
   "source": [
    "# For grades separately"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5328db2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get skill code for all problems.\n",
    "df_training_hint_group=df_training_hint_group.merge(df_problem[['problem_id','problem_skill_code']],on='problem_id',how='left')\n",
    "df_training_hint_group=df_training_hint_group.rename(columns={'problem_id_action':'number_of_problems'})\n",
    "df_training_hint_group['grade_train'] = df_training_hint_group['problem_skill_code'].str.split('.', n=1).str[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "e938f383",
   "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>unit_test_assignment_log_id</th>\n",
       "      <th>problem_id</th>\n",
       "      <th>number_of_problems</th>\n",
       "      <th>available_core_tutoring_hint</th>\n",
       "      <th>action_answer_requested</th>\n",
       "      <th>action_correct_response</th>\n",
       "      <th>action_hint_requested</th>\n",
       "      <th>action_explanation_requested</th>\n",
       "      <th>action_live_tutor_requested</th>\n",
       "      <th>action_skill_related_video_requested</th>\n",
       "      <th>...</th>\n",
       "      <th>per_answer_requested</th>\n",
       "      <th>per_skill_video_requested</th>\n",
       "      <th>per_live_tutor_requested</th>\n",
       "      <th>total_available_toutoring</th>\n",
       "      <th>per_total_avai_tutoring</th>\n",
       "      <th>total_toutoring_requested</th>\n",
       "      <th>per_total_req_tutoring</th>\n",
       "      <th>per_correct_response</th>\n",
       "      <th>per_wrong_response</th>\n",
       "      <th>problem_skill_code</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grade_train</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",
       "      <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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>...</td>\n",
       "      <td>7369</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "      <td>7487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>...</td>\n",
       "      <td>25492</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "      <td>25560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>...</td>\n",
       "      <td>75231</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "      <td>75651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>...</td>\n",
       "      <td>48069</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "      <td>51835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>...</td>\n",
       "      <td>19039</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "      <td>22599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>...</td>\n",
       "      <td>8072</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "      <td>9237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HSA</th>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>...</td>\n",
       "      <td>1053</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "      <td>1060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HSF</th>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>...</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             unit_test_assignment_log_id  problem_id  number_of_problems   \n",
       "grade_train                                                                \n",
       "2                                   7487        7487                7487  \\\n",
       "4                                  25560       25560               25560   \n",
       "5                                  75651       75651               75651   \n",
       "6                                  51835       51835               51835   \n",
       "7                                  22599       22599               22599   \n",
       "8                                   9237        9237                9237   \n",
       "HSA                                 1060        1060                1060   \n",
       "HSF                                  210         210                 210   \n",
       "\n",
       "             available_core_tutoring_hint  action_answer_requested   \n",
       "grade_train                                                          \n",
       "2                                    7487                     7487  \\\n",
       "4                                   25560                    25560   \n",
       "5                                   75651                    75651   \n",
       "6                                   51835                    51835   \n",
       "7                                   22599                    22599   \n",
       "8                                    9237                     9237   \n",
       "HSA                                  1060                     1060   \n",
       "HSF                                   210                      210   \n",
       "\n",
       "             action_correct_response  action_hint_requested   \n",
       "grade_train                                                   \n",
       "2                               7487                   7487  \\\n",
       "4                              25560                  25560   \n",
       "5                              75651                  75651   \n",
       "6                              51835                  51835   \n",
       "7                              22599                  22599   \n",
       "8                               9237                   9237   \n",
       "HSA                             1060                   1060   \n",
       "HSF                              210                    210   \n",
       "\n",
       "             action_explanation_requested  action_live_tutor_requested   \n",
       "grade_train                                                              \n",
       "2                                    7487                         7487  \\\n",
       "4                                   25560                        25560   \n",
       "5                                   75651                        75651   \n",
       "6                                   51835                        51835   \n",
       "7                                   22599                        22599   \n",
       "8                                    9237                         9237   \n",
       "HSA                                  1060                         1060   \n",
       "HSF                                   210                          210   \n",
       "\n",
       "             action_skill_related_video_requested  ...  per_answer_requested   \n",
       "grade_train                                        ...                         \n",
       "2                                            7487  ...                  7369  \\\n",
       "4                                           25560  ...                 25492   \n",
       "5                                           75651  ...                 75231   \n",
       "6                                           51835  ...                 48069   \n",
       "7                                           22599  ...                 19039   \n",
       "8                                            9237  ...                  8072   \n",
       "HSA                                          1060  ...                  1053   \n",
       "HSF                                           210  ...                   210   \n",
       "\n",
       "             per_skill_video_requested  per_live_tutor_requested   \n",
       "grade_train                                                        \n",
       "2                                 7487                      7487  \\\n",
       "4                                25560                     25560   \n",
       "5                                75651                     75651   \n",
       "6                                51835                     51835   \n",
       "7                                22599                     22599   \n",
       "8                                 9237                      9237   \n",
       "HSA                               1060                      1060   \n",
       "HSF                                210                       210   \n",
       "\n",
       "             total_available_toutoring  per_total_avai_tutoring   \n",
       "grade_train                                                       \n",
       "2                                 7487                     7487  \\\n",
       "4                                25560                    25560   \n",
       "5                                75651                    75651   \n",
       "6                                51835                    51835   \n",
       "7                                22599                    22599   \n",
       "8                                 9237                     9237   \n",
       "HSA                               1060                     1060   \n",
       "HSF                                210                      210   \n",
       "\n",
       "             total_toutoring_requested  per_total_req_tutoring   \n",
       "grade_train                                                      \n",
       "2                                 7487                    7487  \\\n",
       "4                                25560                   25560   \n",
       "5                                75651                   75651   \n",
       "6                                51835                   51835   \n",
       "7                                22599                   22599   \n",
       "8                                 9237                    9237   \n",
       "HSA                               1060                    1060   \n",
       "HSF                                210                     210   \n",
       "\n",
       "             per_correct_response  per_wrong_response  problem_skill_code  \n",
       "grade_train                                                                \n",
       "2                            7487                7487                7487  \n",
       "4                           25560               25560               25560  \n",
       "5                           75651               75651               75651  \n",
       "6                           51835               51835               51835  \n",
       "7                           22599               22599               22599  \n",
       "8                            9237                9237                9237  \n",
       "HSA                          1060                1060                1060  \n",
       "HSF                           210                 210                 210  \n",
       "\n",
       "[8 rows x 33 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_training_hint_group[df_training_hint_group['per_skill_video_requested'] < 0.3].groupby('grade_train').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "942ae9bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "upper_limit=0.7\n",
    "lower_limit=0.3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "7a537c29",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from scipy import stats\n",
    "\n",
    "def calculate_metric_dfs(upper_limit, lower_limit):\n",
    "    metrics = ['hint_requested', 'explanation_requested', 'answer_requested', 'total_req_tutoring', 'skill_video_requested', 'live_tutor_requested', 'correct_response', 'wrong_response']\n",
    "\n",
    "    dfs = {}  # Dictionary to store the separate DataFrames\n",
    "\n",
    "    for metric in metrics:\n",
    "        df_metric = pd.DataFrame()  # Create an empty DataFrame for the metric\n",
    "        for limit in [upper_limit, lower_limit]:\n",
    "            if limit == upper_limit:\n",
    "                limit_label = f'>={limit}'\n",
    "                group = df_training_hint_group[df_training_hint_group[f'per_{metric}'] >= limit].groupby('grade_train')['score']\n",
    "                total = df_training_hint_group[df_training_hint_group[f'per_{metric}'] >= limit]['score'].mean()\n",
    "                total_sample_size = df_training_hint_group[df_training_hint_group[f'per_{metric}'] >= limit].shape[0]\n",
    "            else:\n",
    "                limit_label = f'<{limit}'\n",
    "                group = df_training_hint_group[df_training_hint_group[f'per_{metric}'] < limit].groupby('grade_train')['score']\n",
    "                total = df_training_hint_group[df_training_hint_group[f'per_{metric}'] < limit]['score'].mean()\n",
    "                total_sample_size = df_training_hint_group[df_training_hint_group[f'per_{metric}'] < limit].shape[0]\n",
    "            \n",
    "            # Calculate mean and count for each grade\n",
    "            group_mean = group.mean()\n",
    "            group_count = group.count()\n",
    "            \n",
    "            group_mean['Total'] = total\n",
    "            group_count['Total'] = total_sample_size\n",
    "            column_name = f'{metric}({limit_label})'\n",
    "            df_metric[column_name] = group_mean\n",
    "            df_metric[f'{metric}_sample_size({limit_label})'] = group_count\n",
    "\n",
    "        # Perform statistical tests for each grade\n",
    "        t_stats = []\n",
    "        p_values = []\n",
    "        for grade in df_metric.index:\n",
    "            if grade == 'Total':\n",
    "                # Perform statistical test for the total\n",
    "                control_scores = df_training_hint_group[(df_training_hint_group[f'per_{metric}'] < lower_limit)]['score']\n",
    "                treatment_scores = df_training_hint_group[(df_training_hint_group[f'per_{metric}'] >= upper_limit)]['score']\n",
    "                t_stat, p_value = stats.ttest_ind(treatment_scores, control_scores)\n",
    "                t_stats.append(t_stat)\n",
    "                p_values.append(p_value)\n",
    "            else:\n",
    "                control_scores = df_training_hint_group[(df_training_hint_group[f'per_{metric}'] < lower_limit) & (df_training_hint_group['grade_train'] == grade)]['score']\n",
    "                treatment_scores = df_training_hint_group[(df_training_hint_group[f'per_{metric}'] >= upper_limit) & (df_training_hint_group['grade_train'] == grade)]['score']\n",
    "                t_stat, p_value = stats.ttest_ind(treatment_scores, control_scores)\n",
    "                t_stats.append(t_stat)\n",
    "                p_values.append(p_value)\n",
    "                \n",
    "        # Calculate score difference\n",
    "       \n",
    "        df_metric['score_difference'] = df_metric.iloc[:, 0] - df_metric.iloc[:, 2]\n",
    "        df_metric[f'{metric}_t_statistic'] = t_stats\n",
    "        df_metric[f'{metric}_p_value'] = p_values\n",
    "\n",
    "        df_metric.rename_axis('Grade', inplace=True)\n",
    "\n",
    "        dfs[metric] = df_metric  # Add the metric DataFrame to the dictionary\n",
    "\n",
    "    return dfs\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "abec36c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_565460/2265922403.py:48: RuntimeWarning: Precision loss occurred in moment calculation due to catastrophic cancellation. This occurs when the data are nearly identical. Results may be unreliable.\n",
      "  t_stat, p_value = stats.ttest_ind(treatment_scores, control_scores)\n",
      "/home/aswani/.virtualenvs/edm/lib/python3.8/site-packages/scipy/stats/_stats_py.py:1214: RuntimeWarning: divide by zero encountered in divide\n",
      "  var *= np.divide(n, n-ddof)  # to avoid error on division by zero\n",
      "/home/aswani/.virtualenvs/edm/lib/python3.8/site-packages/scipy/stats/_stats_py.py:1214: RuntimeWarning: invalid value encountered in scalar multiply\n",
      "  var *= np.divide(n, n-ddof)  # to avoid error on division by zero\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>hint_requested(&gt;=0.7)</th>\n",
       "      <th>hint_requested_sample_size(&gt;=0.7)</th>\n",
       "      <th>hint_requested(&lt;0.3)</th>\n",
       "      <th>hint_requested_sample_size(&lt;0.3)</th>\n",
       "      <th>score_difference</th>\n",
       "      <th>hint_requested_t_statistic</th>\n",
       "      <th>hint_requested_p_value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grade</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>1</th>\n",
       "      <td>0.652174</td>\n",
       "      <td>23</td>\n",
       "      <td>0.860868</td>\n",
       "      <td>8438</td>\n",
       "      <td>-0.208694</td>\n",
       "      <td>-2.884172</td>\n",
       "      <td>3.934350e-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.408163</td>\n",
       "      <td>49</td>\n",
       "      <td>0.708799</td>\n",
       "      <td>97905</td>\n",
       "      <td>-0.300636</td>\n",
       "      <td>-4.630733</td>\n",
       "      <td>3.648409e-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.206349</td>\n",
       "      <td>189</td>\n",
       "      <td>0.599101</td>\n",
       "      <td>44480</td>\n",
       "      <td>-0.392752</td>\n",
       "      <td>-11.001281</td>\n",
       "      <td>4.094208e-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.419198</td>\n",
       "      <td>823</td>\n",
       "      <td>0.533657</td>\n",
       "      <td>44879</td>\n",
       "      <td>-0.114459</td>\n",
       "      <td>-6.523726</td>\n",
       "      <td>6.929614e-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.398467</td>\n",
       "      <td>261</td>\n",
       "      <td>0.491192</td>\n",
       "      <td>60968</td>\n",
       "      <td>-0.092725</td>\n",
       "      <td>-2.990309</td>\n",
       "      <td>2.788058e-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.342734</td>\n",
       "      <td>3936</td>\n",
       "      <td>0.589595</td>\n",
       "      <td>57721</td>\n",
       "      <td>-0.246861</td>\n",
       "      <td>-30.529849</td>\n",
       "      <td>3.450186e-203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.380713</td>\n",
       "      <td>5973</td>\n",
       "      <td>0.562540</td>\n",
       "      <td>20363</td>\n",
       "      <td>-0.181827</td>\n",
       "      <td>-25.027202</td>\n",
       "      <td>1.223933e-136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.447631</td>\n",
       "      <td>1604</td>\n",
       "      <td>0.540555</td>\n",
       "      <td>15177</td>\n",
       "      <td>-0.092924</td>\n",
       "      <td>-7.103014</td>\n",
       "      <td>1.269629e-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HSA</th>\n",
       "      <td>0.483871</td>\n",
       "      <td>31</td>\n",
       "      <td>0.714614</td>\n",
       "      <td>869</td>\n",
       "      <td>-0.230744</td>\n",
       "      <td>-2.781575</td>\n",
       "      <td>5.522893e-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HSF</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>2</td>\n",
       "      <td>0.630631</td>\n",
       "      <td>222</td>\n",
       "      <td>-0.630631</td>\n",
       "      <td>-1.839605</td>\n",
       "      <td>6.716153e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HSG</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>15</td>\n",
       "      <td>0.785714</td>\n",
       "      <td>182</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>2.012306</td>\n",
       "      <td>4.556349e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HSN</th>\n",
       "      <td>0.333333</td>\n",
       "      <td>15</td>\n",
       "      <td>0.615385</td>\n",
       "      <td>104</td>\n",
       "      <td>-0.282051</td>\n",
       "      <td>-2.089435</td>\n",
       "      <td>3.883578e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HSS</th>\n",
       "      <td>0.250000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.598361</td>\n",
       "      <td>244</td>\n",
       "      <td>-0.348361</td>\n",
       "      <td>-1.406506</td>\n",
       "      <td>1.608360e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>0.384240</td>\n",
       "      <td>13122</td>\n",
       "      <td>0.609120</td>\n",
       "      <td>388096</td>\n",
       "      <td>-0.224880</td>\n",
       "      <td>-51.927802</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       hint_requested(>=0.7)  hint_requested_sample_size(>=0.7)   \n",
       "Grade                                                             \n",
       "1                   0.652174                                 23  \\\n",
       "2                   0.408163                                 49   \n",
       "3                   0.206349                                189   \n",
       "4                   0.419198                                823   \n",
       "5                   0.398467                                261   \n",
       "6                   0.342734                               3936   \n",
       "7                   0.380713                               5973   \n",
       "8                   0.447631                               1604   \n",
       "HSA                 0.483871                                 31   \n",
       "HSF                 0.000000                                  2   \n",
       "HSG                 1.000000                                 15   \n",
       "HSN                 0.333333                                 15   \n",
       "HSS                 0.250000                                  4   \n",
       "Total               0.384240                              13122   \n",
       "\n",
       "       hint_requested(<0.3)  hint_requested_sample_size(<0.3)   \n",
       "Grade                                                           \n",
       "1                  0.860868                              8438  \\\n",
       "2                  0.708799                             97905   \n",
       "3                  0.599101                             44480   \n",
       "4                  0.533657                             44879   \n",
       "5                  0.491192                             60968   \n",
       "6                  0.589595                             57721   \n",
       "7                  0.562540                             20363   \n",
       "8                  0.540555                             15177   \n",
       "HSA                0.714614                               869   \n",
       "HSF                0.630631                               222   \n",
       "HSG                0.785714                               182   \n",
       "HSN                0.615385                               104   \n",
       "HSS                0.598361                               244   \n",
       "Total              0.609120                            388096   \n",
       "\n",
       "       score_difference  hint_requested_t_statistic  hint_requested_p_value  \n",
       "Grade                                                                        \n",
       "1             -0.208694                   -2.884172            3.934350e-03  \n",
       "2             -0.300636                   -4.630733            3.648409e-06  \n",
       "3             -0.392752                  -11.001281            4.094208e-28  \n",
       "4             -0.114459                   -6.523726            6.929614e-11  \n",
       "5             -0.092725                   -2.990309            2.788058e-03  \n",
       "6             -0.246861                  -30.529849           3.450186e-203  \n",
       "7             -0.181827                  -25.027202           1.223933e-136  \n",
       "8             -0.092924                   -7.103014            1.269629e-12  \n",
       "HSA           -0.230744                   -2.781575            5.522893e-03  \n",
       "HSF           -0.630631                   -1.839605            6.716153e-02  \n",
       "HSG            0.214286                    2.012306            4.556349e-02  \n",
       "HSN           -0.282051                   -2.089435            3.883578e-02  \n",
       "HSS           -0.348361                   -1.406506            1.608360e-01  \n",
       "Total         -0.224880                  -51.927802            0.000000e+00  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "calculate_metric_dfs(upper_limit, lower_limit)['hint_requested']\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}