{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nfrom sklearn.linear_model import LogisticRegression\nfrom sklearn.model_selection import cross_val_score\nfrom sklearn import preprocessing\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import roc_auc_score","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"path = \"/Users/stephanielin/Desktop/edm_cup_2023/\"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def load_data(filenames):\n    dataframes = {}\n    for name, file in filenames.items():\n        dataframes[name] = pd.read_csv(file)\n    return dataframes\n\ndef print_shape(dataframes):\n    for df_name, df in dataframes.items():\n        print(f\"{df_name} shape: {df.shape}\")\n        \ndef unique_values(dataframes):\n    for df_name, df in dataframes.items():\n        print(f\"\\n{df_name} unique values:\")\n        for col in df.select_dtypes(include='object').columns:\n            print(f\"{col}: {len(df[col].unique())} unique values\")\n        \ndef check_missing_values(dataframes):\n    for df_name, df in dataframes.items():\n        print(f\"\\n{df_name} missing values:\")\n        print(df.isnull().sum())\n        \ndef display_head(dataframes):\n    for df_name, df in dataframes.items():\n        print(f\"\\n{df_name} head:\")\n        display(df.head())","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"filenames = {\n    \"action_logs\": path + \"action_logs.csv\",\n    \"assignment_details\": path + \"assignment_details.csv\",\n    \"assignment_relationships\": path + \"assignment_relationships.csv\",\n    \"evaluation_unit_test_scores\": path + \"evaluation_unit_test_scores.csv\",\n    \"explanation_details\": path + \"explanation_details.csv\",\n    \"hint_details\": path + \"hint_details.csv\",\n    \"problem_details\": path + \"problem_details.csv\",\n    \"sequence_details\": path + \"sequence_details.csv\",\n    \"sequence_relationships\": path + \"sequence_relationships.csv\",\n    \"training_unit_test_scores\": path + \"training_unit_test_scores.csv\",\n}\n\ndataframes = load_data(filenames)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print_shape(dataframes)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"check_missing_values(dataframes)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_values(dataframes)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"########################################## Data Clearning ##########################################","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tuts = dataframes[\"training_unit_test_scores\"]\neuts = dataframes[\"evaluation_unit_test_scores\"]\nar = dataframes[\"assignment_relationships\"]\nal = dataframes[\"action_logs\"]\nad = dataframes[\"assignment_details\"]\nproblemd = dataframes[\"problem_details\"]\ned = dataframes[\"explanation_details\"]\nhd = dataframes[\"hint_details\"]\nsd = dataframes[\"sequence_details\"]\nsr = dataframes[\"sequence_relationships\"]","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# assignment_detail.csv","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# assignment_end_time null value indicates removal for predicted question or student not finish\n# assignment_due_date: If a teacher did not specify a due date, this column will be blank.\nad['assignment_due_date'] = ad['assignment_due_date'].fillna('NA')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# problem_details.csv\n# This file contains one row for every problem referenced in the dataset, \n# except for some problems in the action logs, which have been deleted from the database. \n# These problems likely had errors during their original transcription into ASSISTments that were corrected,\n# but no record of the original problems was kept.","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"problemd['problem_skill_code'] = problemd['problem_skill_code'].fillna('NA')\nproblemd['problem_contains_image'] = problemd['problem_contains_image'].fillna(0)\nproblemd['problem_contains_equation'] = problemd['problem_contains_equation'].fillna(0)\nproblemd['problem_contains_video'] = problemd['problem_contains_video'].fillna(0)\nproblemd['problem_skill_description'] = problemd['problem_skill_description'].fillna('NA')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# sequence_details\n# This file contains at least one row for each sequence present in the dataset and contains all the information unique to each sequence. \n# Some sequences have duplicate rows because they are present in multiple locations within units. Whether that is intentional is unclear.","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# sequence_folder_path_level_4 & sequence_folder_path_level_5: If there is no folder at this path depth, this column is blank.\nsd['sequence_folder_path_level_4'] = sd['sequence_folder_path_level_4'].fillna('NA')\nsd['sequence_folder_path_level_5'] = sd['sequence_folder_path_level_5'].fillna('NA')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"########################################## Explorative Analysis ##########################################","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# data viz\nimport matplotlib.pyplot as plt\nfrom matplotlib import rcParams\nimport seaborn as sns","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# explore problem related variables","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"problem_df = tuts.merge(problemd, how=\"left\", left_on=\"problem_id\", right_on='problem_id')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"problem_df.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# continuous variables\nproblem_df.corr()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# categorical variables only problem types: problem_skill_code (too many unique values), problem_skill_description(too many unique values)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.countplot(x='score', hue='problem_type', data=problem_df)\nplt.title('Countplot of score by problem_type')\nplt.show()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"########################################## Feature Engineer ##########################################","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n# too many unique values for problem_skill_code\n\n# If a skill code has a dash in it, that specifies a more detailed sub-skill beyond what the common core provides.\nproblemd['sub_skill_involved'] = problemd['problem_skill_code'].str.contains('-').astype(int)\n\n# create dummy variables for problem_type\nproblem_type = pd.get_dummies(problemd['problem_type'], drop_first=True)\nproblemd.drop('problem_type', axis = 1, inplace=True)\nproblemd = pd.concat([problemd, problem_type], axis=1)\nproblem_type_columns = problemd.iloc[:, -9:].columns.tolist()\nproblem_type_columns.append('problem_id')\nproblemd.head(10)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# If the student never completed the assignment, this assignment_end_time will be blank.\n# create a notfinish column to indicate whether the assignment is finsihed or not\nad[\"notfinish\"] = ad['assignment_end_time'].isnull().astype(int)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# get the sum of unfinished in unit assignments for each unit test assignment\nad_ar = ar.merge(ad, how='left', left_on='in_unit_assignment_log_id', right_on='assignment_log_id')\nad_ar.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"assignment_total = ad_ar[['unit_test_assignment_log_id', 'in_unit_assignment_log_id']]\nassignment_total = assignment_total.groupby('unit_test_assignment_log_id')['in_unit_assignment_log_id'].nunique().rename('Total_Assignment_Count')\nassignment_total.to_frame()\nassignment_total.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"notfinish = ad_ar[['unit_test_assignment_log_id', 'in_unit_assignment_log_id','notfinish']]\nnotfinish = notfinish.groupby('unit_test_assignment_log_id').sum()\nnotfinish.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"notfinish = notfinish.merge(assignment_total, how = \"left\", left_index=True, right_index=True)\n# add a column to calculate the percent of unfinished in-unit assignments\nnotfinish['notfinish_percent'] = notfinish['notfinish']/notfinish['Total_Assignment_Count']\nnotfinish['notfinish_percent'] = notfinish['notfinish_percent'].round(4)\nnotfinish.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Associate the action logs for each in unit assignment with their unit test assignment\nal_ar = ar.merge(al, how='left', left_on='in_unit_assignment_log_id', right_on='assignment_log_id')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"action_df = al_ar[['unit_test_assignment_log_id', 'action']]\n# Get the total number of times each action was taken within the in unit assignments corresponding to each unit test\n# assignment\naction_df = pd.get_dummies(action_df, columns=['action'])\naction_df = action_df.groupby('unit_test_assignment_log_id').sum()\n# Create a feature for the total action count, then scale it between 0 and 1\naction_count = action_df.sum(axis=1)\n# Convert the individual action counts into a fraction of total actions taken\naction_df = action_df.div(action_count, axis=0)\n# Add the scaled total action count to the dataframe\naction_df['action_count'] = (action_count - action_count.min()) / (action_count.max() - action_count.min())","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Associate the tutor for each in unit assignment with their unit test assignment\ntutor_df = al_ar[['unit_test_assignment_log_id', 'available_core_tutoring']]\n# Get the total number of times each available_core_tutoring was taken within the in unit assignments corresponding to each unit test assignment\ntutor_df = pd.get_dummies(tutor_df, columns=['available_core_tutoring'])\ntutor_df = tutor_df.groupby('unit_test_assignment_log_id').sum()\n# Create a feature for the total tutoring count, then scale it between 0 and 1\ntutor_count = tutor_df.sum(axis=1)\n# Convert the individual tutor counts into a fraction of total tutorings\ntutor_df = tutor_df.div(tutor_count, axis=0)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"########################################## Training Set ##########################################","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tuts = dataframes[\"training_unit_test_scores\"]","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merge action count features with the training unit test scores\ntuts = tuts.merge(action_df, how='left', left_on='assignment_log_id', right_index=True)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merge problem type, problem skill code, problem type, problem_multipart_position features with the training unit test scores\n# selected_cols = ['problem_id', 'problem_skill_code', 'problem_multipart_position' ]\ntuts = tuts.merge(problemd[['problem_id', 'sub_skill_involved', 'problem_multipart_position']], how='left', left_on='problem_id', right_on='problem_id')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# merge problem types \ntuts = tuts.merge(problemd[problem_type_columns], how='left', left_on='problem_id', right_on='problem_id')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merge available_core_tutoring count features with the training unit test scores\ntuts = tuts.merge(tutor_df, how='left', left_on='assignment_log_id', right_index=True)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# merge not_finished percent\ntuts = tuts.merge(notfinish['notfinish_percent'], how='left', left_on='assignment_log_id', right_index=True)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tuts.head(10)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tuts = tuts.fillna(0)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"########################################## Test Set ##########################################","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"euts = dataframes[\"evaluation_unit_test_scores\"]","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merge action count features with the evaluation unit test scores\neuts = euts.merge(action_df, how='left', left_on='assignment_log_id', right_index=True)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merge problem type, problem skill code, sub_skill_involved, problem_multipart_position features with the evaluation unit test scores\neuts = euts.merge(problemd[['problem_id' , 'sub_skill_involved', 'problem_multipart_position']], how='left', left_on='problem_id', right_on='problem_id')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# merge problem type dummies\neuts = euts.merge(problemd[problem_type_columns], how='left', left_on='problem_id', right_on='problem_id')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merge available_core_tutoring count features with evaluation unit test scores\neuts = euts.merge(tutor_df, how='left', left_on='assignment_log_id', right_index=True)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# merge not finished percent\neuts = euts.merge(notfinish['notfinish_percent'], how='left', left_on='assignment_log_id', right_index=True)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# euts = euts.merge(attempts['average_attempts'], how='left', left_on='assignment_log_id', right_index=True)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"euts.head(10)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"euts = euts.fillna(0)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"########################################## Model ##########################################","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Collect the input and target columns for the regression\ninput_cols = [c for c in tuts.columns]\ninput_cols.remove('assignment_log_id')\ninput_cols.remove('problem_id')\ninput_cols.remove('score')\ntarget_col = 'score'\nprint(input_cols)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# divide training and testing\nX = tuts[input_cols] \ny = tuts['score']\nX_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)\n\n\n# Initialize a logistic regression\nlr = LogisticRegression(max_iter=1000)\n# Fit the regression on all the training data\nlr = lr.fit(X_train, y_train)\n\n# y_pred_proba = rf_classifier.predict_proba(X_test)[:, 1]\ny_pred_proba =  lr.predict_proba(X_test)[:, 1]\n\nauc = roc_auc_score(y_test, y_pred_proba)\nprint(\"AUC:\", auc)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# For the final prediction, using all tuts to train to predict euts\n# Initialize a logistic regression\nlr = LogisticRegression(max_iter=1000)\n# Fit the regression on all the training data\nlr = lr.fit(tuts[input_cols], tuts[target_col])\n# Predict the score for each evaluation problem\neuts[target_col] = lr.predict_proba(euts[input_cols])[:,1]\neuts[['id', 'score']].to_csv('example_submission.csv', index=False)","metadata":{},"execution_count":null,"outputs":[]}]}