{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 5GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"!pip install datatable","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Introduction\n\nIn this notebook I want to provide an explaration data analysis. We retrieve the data from the train.csv file and its connection with questions.csv and lectures.csv files. \nIt seems they are some kind of database extractions with a \"star\" schema model. \n\n## Train.csv\n\nFrom the challange description, we get the following infos:\n\n>* row_id: ID code for the row. \n\n*It is an index column for the dataset*\n\n>* timestamp: the time between this user interaction and the first event completion from that user. \n\n*I should check if it is in milliseconds.*\n\n>* user_id: ID code for the user. \n>* content_id: ID code for the user interaction\n>* content_type_id: 0 if the event was a question being posed to the user, 1 if the event was the user watching a lecture.\n\n*The content_type_id can be used as an integer even if it is an id. I can use it as one-hot column for lectures.*\n\n>* task_container_id: Id code for the batch of questions or lectures. For example, a user might see three questions in a row before seeing the explanations for any of them. Those three would all share a task_container_id.\n>* user_answer: the user's answer to the question, if any. Read -1 as null, for lectures.\n>* answered_correctly: if the user responded correctly. Read -1 as null, for lectures.\n>* prior_question_elapsed_time: The average time it took a user to answer each question in the previous question bundle, ignoring any lectures in between. Is null for a user's first question bundle or lecture. Note that the time is the average time a user took to solve each question in the previous bundle.\n\n*I should again check if it is in milliseconds.*\n\n>* prior_question_had_explanation: (bool) Whether or not the user saw an explanation and the correct response(s) after answering the previous question bundle, ignoring any lectures in between. The value is shared across a single question bundle, and is null for a user's first question bundle or lecture. Typically the first several questions a user sees were part of an onboarding diagnostic test where they did not get any feedback.\n\nThe first step is to understand what we have in the Train dataset."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"%%time\n\n# reading the dataset from raw csv file\nimport datatable as dt\nimport plotly.express as px\nimport matplotlib.pyplot as plt\n\n#df = dt.fread(\"../input/riiid-test-answer-prediction/train.csv\").to_pandas()\ndf = pd.read_csv('../input/riiid-test-answer-prediction/train.csv',nrows = 1000000)\nprint(df.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"d = {'row_id':'int64',\n 'timestamp':'int64',\n 'user_id':'object',\n 'content_id':'object',\n 'content_type_id':'int64',\n 'task_container_id':'object',\n 'user_answer':'object',\n 'answered_correctly':'int64',\n 'prior_question_elapsed_time':'float64',\n 'prior_question_had_explanation':'object'}\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.head(100000).astype(d).describe(include='all')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We try to detect students performance with question type content. For this reason, we exclude lectures."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"user_profile = df[df['answered_correctly']!=-1].groupby(\n    'user_id',as_index = False\n    ).agg(age_as_learner = pd.NamedAgg(column = 'timestamp', aggfunc=max),\n          num_contents = pd.NamedAgg(column = 'content_id', aggfunc=pd.Series.nunique),\n          num_events = pd.NamedAgg(column = 'row_id', aggfunc = 'count'),\n          num_true_answers = pd.NamedAgg(column = 'answered_correctly', aggfunc = sum),\n          perc_true_answers = pd.NamedAgg(column = 'answered_correctly', aggfunc = 'mean'),\n          std_true_answers = pd.NamedAgg(column = 'answered_correctly', aggfunc = 'std'),\n          #skew_true_answers = pd.NamedAgg(column = 'answered_correctly', aggfunc = 'skew'),\n          avg_prior_question_elapsed_time = pd.NamedAgg(column = 'prior_question_elapsed_time', aggfunc = 'mean'),\n          std_prior_question_elapsed_time = pd.NamedAgg(column = 'prior_question_elapsed_time', aggfunc = 'std'),\n          )\nuser_profile['user_id'] = user_profile['user_id'].astype('object')\nuser_profile.age_as_learner /=(60*60*24*1000*365)\nuser_profile.avg_prior_question_elapsed_time /= 60*60*24\nuser_profile.std_prior_question_elapsed_time /= 60*60*24\nprint('Number of Users: ', len(user_profile))\nuser_profile.describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I'm not sure but it seems that timestamp is in milliseconds while the elapsed time is in seconds. I rescale it so that we get respectively years and days. \nIn the following we get a scatter matrix to have an overall picture of our dataset and hidden patterns. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"pd.plotting.scatter_matrix(user_profile.select_dtypes(include='number'),alpha = 0.1, figsize = (30,30))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"n = len(df.content_type_id)\na,b = df.content_type_id.value_counts()\nprint('The number of lecture events is : {} ({}%)'.format(b,100*b/n))\nprint('The number of question events is : {} ({}%)'.format(a,100*a/n))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's see who are the \"oldest\" students and how they are characterized"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"user_profile.sort_values(by='age_as_learner',ascending = False).head(10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's see who are the students who have answered more questions"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"user_profile.sort_values(by='num_contents',ascending = False).head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"user_profile.sort_values(by='num_true_answers',ascending = False).head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"user_profile[user_profile.std_true_answers.notnull()].sort_values(by='perc_true_answers',ascending = False).head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"#user_series = df.groupby(['user_id','timestamp']).agg({'answered_correctly':'sum','row_id':'count'})\n#user_series = user_series.groupby(level=0).agg({'answered_correctly':'cumsum','row_id':'cumsum'}).reset_index()\n#user_series['avg_correct'] = user_series.answered_correctly.div(user_series.row_id, axis = 0)\nuser_series","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### To Do:\n\n* Lectures have impact on performances? explanations too?\n* Build timeseries to track students performance\n* Track impact of lectures on students' performance"},{"metadata":{},"cell_type":"markdown","source":"## Questions\n\n>* question_id: foreign key for the train/test content_id column, when the content type is question (0).\n>* bundle_id: code for which questions are served together.\n\n*The  bundle_id key should be added to the train.csv dataset.*\n\n>* correct_answer: the answer to the question. Can be compared with the train user_answer column to check if the user was right.\n>* part: the relevant section of the TOEIC test.\n>* tags: one or more detailed tag codes for the question. The meaning of the tags will not be provided, but these codes are sufficient for clustering the questions together.\n\n*This tags field seems interesting.*"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"questions = pd.read_csv('../input/riiid-test-answer-prediction/questions.csv')#,nrows = 1000000)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"questions.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"questions.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Be aware there's a troublesome missing tag value. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"questions[questions.tags.isnull()]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"dq = {\n    'question_id':'object',\n    'bundle_id':'object',\n    'correct_answer':'object',\n    'part':'object'\n}\n\nquestions.astype(dq).describe()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"(questions.correct_answer.value_counts()/len(questions)).plot.bar()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The questions' answers seems to be fair and balanced so that if a student use choosing answers randomly is not a good strategy. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"(questions.part.value_counts()/len(questions)).plot.bar()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We now study the tags. Remember to fill the na values."},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"questions.fillna('-1', inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"from collections import Counter\n\ntags = Counter(questions.tags.str.split(' ')[0])\nfor tag in questions.tags.str.split(' '):\n    try:\n        tags.update(tag)\n    except:\n        print('Found exception: tags field equal to ', tag)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"tags = pd.DataFrame.from_dict(dict(tags),orient='index').reset_index().sort_values(by=0, ascending = False)\ntags.columns = ['tag','count_tag']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Which are the most common tags?"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"tags.head(20).plot.bar()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Which is the relationship with part field?"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"from sklearn.preprocessing import MultiLabelBinarizer\nmlb = MultiLabelBinarizer()\n\ntags_of_parts = pd.DataFrame(mlb.fit_transform(questions.tags.str.split(' ')), columns = mlb.classes_, index = questions.question_id)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"tags_of_parts.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"test = pd.merge(questions[['question_id','part']],tags_of_parts, on = 'question_id').drop(['question_id'], axis = 1).groupby('part').sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import seaborn as sns\n\nf, ax = plt.subplots(figsize=(34, 5))\n\nax = sns.heatmap(test)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This is a cool finding even if the plot is not the best one probably. It seems there are tags strongly related to specific parts. "},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"test.reset_index().melt(id_vars = ['part'], var_name = 'tag',value_name = 'count').astype('int64')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I've spent a bit of time to change the questions.tags structure in a proper manner. At the end I've found my strategy thanks to [this post on brandonrose.org](http://brandonrose.org/clustering?ref=dzone#K-means-clustering)."},{"metadata":{"trusted":true},"cell_type":"code","source":"import nltk, re\nfrom sklearn.metrics.pairwise import cosine_similarity\nfrom sklearn.cluster import KMeans\n\n\ndef tokenize_and_stem(text):\n    tokens = [word for sent in nltk.sent_tokenize(text) for word in nltk.word_tokenize(sent)]\n    filtered_tokens = []\n    for token in tokens:\n        if re.search('[0-9]', token):\n            filtered_tokens.append(token)\n    return filtered_tokens\n\nfrom sklearn.feature_extraction.text import TfidfVectorizer\n\n#define vectorizer parameters\ntfidf_vectorizer = TfidfVectorizer(max_df=1.0, max_features=200000,\n                                 min_df=0.0,use_idf=True, tokenizer=tokenize_and_stem, ngram_range=(1,3))\n\n%time tfidf_matrix = tfidf_vectorizer.fit_transform(questions.tags) #fit the vectorizer to synopses\n\nprint(tfidf_matrix.shape)\n\ntags = tfidf_vectorizer.get_feature_names()\n\n\ndist = 1 - cosine_similarity(tfidf_matrix)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"sse = {}\nfor k in range(2,30,3):\n    km = KMeans(n_clusters=k)\n    #print('Number of cluster: {}'.format(k))\n    #%time \n    km.fit(tfidf_matrix)\n    sse[k] = km.inertia_\nplt.figure()\nplt.plot(list(sse.keys()), list(sse.values()))\nplt.xlabel(\"Number of cluster\")\nplt.ylabel(\"SSE\")\nplt.show()\nsse.keys()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Using the elbow method, it seems that 11 or 23 are good candidates. However, I prefert to fix the number of cluster to 11 and reduce the number of values make the following analysis much simpler to read. \n\nBe aware that results could slightly change at every run. "},{"metadata":{"trusted":true},"cell_type":"code","source":"NUM_CLUSTERS = 11\n\n\nkm = KMeans(n_clusters=NUM_CLUSTERS)\n\n%time km.fit(tfidf_matrix)\n\nclusters = km.labels_.tolist()\n\nn_words = 5\n\norder_centroids = km.cluster_centers_.argsort()[:, ::-1] \n\nfor i in range(NUM_CLUSTERS):\n    print(\"Cluster %d words:\" % i, end='')\n    \n    for ind in order_centroids[i, :n_words]: \n        print(' %s' % tags[ind], end=' ')\n    print() #add whitespace\n    \nquestions['kmean_cluster'] = clusters","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"questions.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dq['kmean_cluster']='object'\nquestions.astype(dq).info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"questions.kmean_cluster.value_counts()/len(questions)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"(pd.pivot_table(\n    questions, \n    values = 'question_id', \n    index = ['kmean_cluster'], \n    columns = ['part'],aggfunc='count')\n).fillna(0).astype('int64')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"How is bundle related to the other attributes?"},{"metadata":{"trusted":true},"cell_type":"code","source":"questions.groupby('bundle_id').agg({\n    'question_id':'count',\n    'part':pd.Series.nunique,\n    'kmean_cluster':pd.Series.nunique}).describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Every bundle is associated to a single part, while at least the 75% is composed by a single question or associated to a single cluster."},{"metadata":{"trusted":true},"cell_type":"code","source":"(questions.groupby('bundle_id').agg({\n    'question_id':'count',\n    'part':pd.Series.nunique,\n    'kmean_cluster':pd.Series.nunique})[['question_id','kmean_cluster']]).hist()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Lectures\n\n> lectures.csv: metadata for the lectures watched by users as they progress in their education.\n>\n> lecture_id: foreign key for the train/test content_id column, when the content type is lecture (1).\n> part: top level category code for the lecture.\n> tag: one tag codes for the lecture. The meaning of the tags will not be provided, but these codes are sufficient for clustering the lectures together.\n> type_of: brief description of the core purpose of the lecture\n\nIts content is similar to questions.csv. We will provide similar analysis."},{"metadata":{"trusted":true},"cell_type":"code","source":"lectures = pd.read_csv('../input/riiid-test-answer-prediction/lectures.csv')#,nrows = 1000000)\nprint(lectures.info())\nlectures.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(pd.pivot_table(\n    lectures,\n    values = 'lecture_id', \n    index = ['type_of'], \n    columns = ['part'],aggfunc='count'    \n).fillna(0))\nprint()\n(lectures.part.value_counts()).plot.bar()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"(lectures.type_of.value_counts()).plot.bar()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(lectures.groupby('tag').agg({'lecture_id':'count'}).sort_values(by='lecture_id',ascending=False)[:25].plot.bar())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(lectures.groupby('tag').agg({'lecture_id':'count'}).hist())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### To Do:\n\n* Cluster of Tags (LDA approach)\n* Sankey Plot of Tags and Parts\n* Improve charts layout"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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":4,"nbformat_minor":4}