coursepage.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "<style type=\"text/css\">\n",
    ".input_prompt, .input_area, .output_prompt {\n",
    "display:none !important;\n",
    "}\n",
    ".reveal h1, .reveal h2 {\n",
    "    font-family:times\n",
    "}\n",
    "</style>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "from __future__ import division\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from collections import OrderedDict\n",
    "\n",
    "import plotly\n",
    "import plotly.offline as py\n",
    "import plotly.graph_objs as go\n",
    "import plotly.tools as tls\n",
    "from plotly import __version__\n",
    "from plotly.offline.offline import _plot_html\n",
    "\n",
    "import itertools\n",
    "\n",
    "from IPython.core.display import display, HTML\n",
    "from os import environ\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "py.init_notebook_mode() # graphs charts inline\n",
    "course_id = environ['current_course']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "display(HTML('<title></title><h1><a href=\"\" target=_blank\">Home</a> / '+course_id+'</h1>'))\n",
    "\n",
    "# update courses with graded_problems other than multiple choices\n",
    "not_mc_list = [\n",
    "\n",
    "]\n",
    "# update professional education courses\n",
    "pe_list = [\n",
    "\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a course-specific dashboard. The graphs show information related to course structure and effectiveness of the course design and structure, based on student success in the course.\n",
    "\n",
    "#### Table of Contents\n",
    "[1. Course Structure](#Course-structure-and-activity)\n",
    "\n",
    "[2. Student Engagement by Content Type](#Video-activity)\n",
    "\n",
    "[3. Most and Least Successful Problems](#Ten-graded-problems-with-lowest-success-rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def query_moduleActivity(course_id=course_id):\n",
    "    \"\"\"\n",
    "    Give the course_id, query # students with any activity, # students attempted any problem \n",
    "    and # students watched any video for each chapter of the course\n",
    "    \"\"\"\n",
    "    # query # students who attempted any problem for each chapter of the course, exclude those with fewer than 20 attempts\n",
    "    query = \"\"\"\n",
    "    Select course_id, sub.index As index, module_id, chapter_name, exact_count_distinct(user_id) As tried_problem\n",
    "    From\n",
    "    (SELECT p.course_id As course_id, p.user_id As user_id, c2.index As index, \n",
    "    c2.module_id As module_id, c2.name As chapter_name\n",
    "    FROM [{0}.problem_analysis] p\n",
    "    Left Join [{0}.course_axis] c1\n",
    "    on p.problem_url_name = c1.url_name\n",
    "    Left Join [{0}.course_axis] c2\n",
    "    On c1.chapter_mid = c2.module_id) sub\n",
    "    Group By course_id, index, module_id, chapter_name\n",
    "    Order By index\"\"\".format(course_id)\n",
    "    tried_problem = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    tried_problem = tried_problem[tried_problem.tried_problem > 20]\n",
    "\n",
    "    # query # students who watched any video for each chapter of the course, exclude those with fewer than 20 views\n",
    "    query = \"\"\"\n",
    "    Select course_id, index, module_id, chapter_name, exact_count_distinct(username) As watched_video\n",
    "    From\n",
    "    (SELECT c1.course_id As course_id, v.username As username, c2.index As index, \n",
    "    c2.module_id As module_id, c2.name As chapter_name\n",
    "    FROM [{0}.video_stats_day] v\n",
    "    Left Join [{0}.course_axis] c1\n",
    "    on v.video_id = c1.url_name\n",
    "    Left Join [{0}.course_axis] c2\n",
    "    On c1.chapter_mid = c2.module_id) sub\n",
    "    Group By course_id, index, module_id, chapter_name\n",
    "    Order By index\"\"\".format(course_id)\n",
    "    try:\n",
    "        watched_video = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "        watched_video = watched_video[watched_video.watched_video > 20]\n",
    "    except:\n",
    "        pass\n",
    "        \n",
    "\n",
    "    # query # students with any activity for each chapter of the course, excluding those with fewer than 20 active students\n",
    "    query = \"\"\"\n",
    "    Select sub.course_id As course_id, sub.module_id As module_id, \n",
    "    c.name As chapter_name, c.index As index, sub.nactive As nactive\n",
    "    From [{0}.course_axis] c\n",
    "    Join \n",
    "    (Select course_id As course_id, Regexp_replace(module_id,'i4x://', '') As module_id, \n",
    "    exact_count_distinct(student_id) As nactive\n",
    "    From [{0}.studentmodule]\n",
    "    Where module_type = 'chapter' \n",
    "    Group By course_id, module_id) sub\n",
    "    On sub.module_id = c.module_id\n",
    "    Order By index\"\"\".format(course_id)\n",
    "    nactive = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    nactive = nactive[nactive.nactive > 20]\n",
    "    \n",
    "    # merge watched_video, tried_problem, nactive \n",
    "    try:\n",
    "        module_activity = watched_video.merge(tried_problem, how='outer').merge(nactive, how='outer').fillna(0)\n",
    "    except:\n",
    "        module_activity = tried_problem.merge(nactive, how='outer').fillna(0)\n",
    "    return module_activity[module_activity.chapter_name != 0].sort_values('index').set_index('chapter_name')\n",
    "    \n",
    "module_activity = query_moduleActivity()\n",
    "# create a list of course indices to make sure courses are ordered in the visualization\n",
    "indices = module_activity.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def query_cs(course_id = course_id):    \n",
    "    \"\"\"\n",
    "    Given course_id (e.g. 'UBCx__Climate1x__1T2016'), \n",
    "    return a list of all the course items (graded_problem, self_test, video, assignment, chapter) \n",
    "    from course_axis table ordered by index.\n",
    "    IMPORTANT: Need to update course_axis in SPD1x first (delete items that belong to SPD2x and SPD3x)\n",
    "    \"\"\"\n",
    "    query = \"\"\"\n",
    "    SELECT\n",
    "    Case \n",
    "         When c1.category='problem' And c1.graded='true' Then 'graded_problem'\n",
    "         When c1.category='problem' And c1.graded!='true' Then 'self_test' \n",
    "         Else c1.category\n",
    "    End As category, c1.index As index, c1.name As name,\n",
    "    c1.url_name As url_name, c2.name As chapter\n",
    "    FROM [[{0}.course_axis] c1\n",
    "    Left Join [{0}.course_axis] c2\n",
    "    On c1.chapter_mid = c2.module_id\n",
    "    Where c1.category in ('video', 'problem', 'openassessment', 'chapter')\n",
    "    Order By c1.index\"\"\".format(course_id)\n",
    "\n",
    "    structure = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "        \n",
    "    query = \"\"\"\n",
    "    Select problem_url_name, exact_count_distinct(item.answer_id) As num\n",
    "    From [{0}.problem_analysis]\n",
    "    Group By problem_url_name\"\"\".format(course_id)\n",
    "    nQuestions = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    structure = structure.merge(nQuestions, left_on='url_name', right_on='problem_url_name', how='left')\\\n",
    "    .drop('problem_url_name', axis=1)\n",
    "    structure.num = structure.num.fillna(1)\n",
    "    return structure\n",
    "\n",
    "course_structure = query_cs()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "# count # of videos, graded_problems, self_test and assignments for each chapter and order the courses\n",
    "cs_chapter = course_structure.groupby(['chapter', 'category']).num.sum().unstack('category')\\\n",
    ".reindex(indices).dropna(how='all')\n",
    "\n",
    "cols = []\n",
    "# some courses do not have all component types\n",
    "for col in ['video', 'graded_problem', 'self_test', 'openassessment']:\n",
    "    if col in cs_chapter.columns.values:\n",
    "        cols.append(col)\n",
    "cs_chapter = cs_chapter[cols]\n",
    "cs_chapter['chapter'] = np.nan\n",
    "cs_chapter.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def rolling_count(df):\n",
    "    df['block'] = (df['category'] != df['category'].shift(1)).astype(int).cumsum()\n",
    "    df['count'] = df.groupby('block').num.cumsum()\n",
    "    return df\n",
    "# count # of times an item (graded_problem, self_test, video) appears consecutively\n",
    "df = course_structure.fillna(method='bfill')\n",
    "df = df.groupby('chapter').apply(rolling_count)\n",
    "idx = df.groupby(['chapter', 'block'])['count'].transform(max) == df['count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Course structure and activity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Course structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "scrolled": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "module_activity = module_activity.reindex(cs_chapter.index)\n",
    "fig = tls.make_subplots(rows=1, cols=1, print_grid=False, subplot_titles=('', ''))\n",
    "\n",
    "colors = {'video': 'rgb(106,61,154)', 'graded_problem': 'rgb(31,120,180)', \n",
    "          'self_test': 'rgb(51,160,44)', 'openassessment': 'rgb(255,127,0)', 'chapter': 'rgb(0, 0, 0)'}\n",
    "\n",
    "# traces for module activity\n",
    "for i in range(0, cs_chapter.shape[1]):\n",
    "    column_label = cs_chapter.columns[i]\n",
    "    if cs_chapter.columns[i]=='self_test':\n",
    "        column_label = 'self test'\n",
    "    if cs_chapter.columns[i]=='openassessment':\n",
    "        column_label = 'open assessment'        \n",
    "    if cs_chapter.columns[i]=='graded_problem':\n",
    "        column_label = 'graded problem'                \n",
    "    fig.append_trace(go.Bar(y=cs_chapter.index, x=cs_chapter.iloc[:, i], orientation='h',\n",
    "                       marker=dict(color=colors[cs_chapter.columns[i]]), name=column_label), 1, 1)\n",
    "\n",
    "fig['layout']['yaxis1'].update(tickfont=dict(size=8), showgrid=False, autorange='reversed', title=\"Modules\")\n",
    "fig['layout']['xaxis1'].update(showgrid=False, title='Number of each content type')\n",
    "fig['layout']['legend'].update(x=1, y=0, traceorder='normal')\n",
    "fig['layout'].update(height=50+30*len(cs_chapter), width=850, margin=go.Margin(l=185, t=25, b=40), barmode='stack')\n",
    "print('How many different types of content do I have in my course?' + '\\n')\n",
    "print('This graph shows, for each module (e.g. Final Exam) the number of component types (e.g. video)' + '\\n'\n",
    "      + 'in that module), across all modules in the course.')\n",
    "config = {\"displayModeBar\": True, \"showLink\": False, \"displaylogo\": False, \"modeBarButtonsToRemove\": ['sendDataToCloud', 'zoomIn2d', 'zoomOut2d', 'autoScale2d', 'resetScale2d', 'hoverClosestCartesian', 'hoverCompareCartesian']}\n",
    "plot_htmla, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "plot_htmla, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "py.iplot(fig)\n",
    "display(HTML('<a href=\"#\" style=\"float: right;\">Read more...</a>'))\n",
    "display(HTML('<a href=\"#Table-of-Contents\" style=\"float: left;\">Back to top</a><a href=\"\" target=_blank style=\"float: right;\">Compare with other courses</a>'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def course_item3(course_id=course_id):\n",
    "    \"\"\"\n",
    "    Given course_id, query students' event for video, graded_problem, \n",
    "    self_test, openassessment and chapter from the studentmodule table\n",
    "    => the numbers are slightly different from thosed queried from person_item and video_stats_day\n",
    "    \"\"\"\n",
    "    query = \"\"\"\n",
    "    SELECT sub.module_id As module_id, c.index As index, name, \n",
    "    c.category As category, nactive\n",
    "    FROM [yourprojectname:{0}.course_axis] c\n",
    "    Join \n",
    "    (Select Regexp_replace(module_id,'i4x://', '') As module_id, exact_count_distinct(student_id) As nactive\n",
    "    From [yourprojectname:{0}.studentmodule]\n",
    "    Where module_type='chapter'\n",
    "    Group By module_id) sub\n",
    "    On sub.module_id = c.module_id\n",
    "    Order By index\"\"\".format(course_id)\n",
    "    df = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    return df\n",
    "\n",
    "courseItem3 = course_item3()\n",
    "# exclude course items accessed by fewer than 20 students \n",
    "courseItem3 = courseItem3[courseItem3.nactive > 20].reset_index(drop=True)\n",
    "\n",
    "def course_item2(course_id=course_id):\n",
    "    \"\"\"\n",
    "    Given course_id, query students' event for video, graded_problem, \n",
    "    self_test, openassessment and chapter from the studentmodule table\n",
    "    => the numbers are slightly different from thosed queried from person_item and video_stats_day\n",
    "    \"\"\"\n",
    "    query = \"\"\"\n",
    "    SELECT sub.module_id As module_id, c.index As indexx, name, \n",
    "    c.category As category, nactive\n",
    "    FROM [yourprojectname:{0}.course_axis] c\n",
    "    Join \n",
    "    (Select Regexp_replace(module_id,'i4x://', '') As module_id, exact_count_distinct(student_id) As nactive\n",
    "    From [yourprojectname:{0}.studentmodule]\n",
    "    Where module_type='openassessment'\n",
    "    Group By module_id) sub\n",
    "    On sub.module_id = c.module_id\n",
    "    Order By indexx\"\"\".format(course_id)\n",
    "    df = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    return df\n",
    "\n",
    "courseItem2 = course_item2()\n",
    "# exclude course items accessed by fewer than 20 students \n",
    "courseItem2 = courseItem2[courseItem2.nactive > 20].reset_index(drop=True)\n",
    "\n",
    "def course_item(course_id=course_id):\n",
    "    \"\"\"\n",
    "    Given course_id, query students' event for video, graded_problem, \n",
    "    self_test, openassessment and chapter from the studentmodule table\n",
    "    => the numbers are slightly different from thosed queried from person_item and video_stats_day\n",
    "    \"\"\"\n",
    "    query = \"\"\"\n",
    "    SELECT sub.module_id As module_id, c.index As index, name, \n",
    "    Case \n",
    "     When c.category='problem' And c.graded='true' Then 'graded_problem'\n",
    "     When c.category='problem' And c.graded!='true' Then 'self_test' \n",
    "     Else c.category\n",
    "    End As category, nactive\n",
    "    FROM [yourprojectname:{0}.course_axis] c\n",
    "    Join \n",
    "    (Select Regexp_replace(module_id,'i4x://', '') As module_id, exact_count_distinct(student_id) As nactive\n",
    "    From [yourprojectname:{0}.studentmodule]\n",
    "    Where (module_type='video' And state Not Like '%00:00:00%') Or \n",
    "    (module_type='problem' And state Like '%student_answers%') Or module_type In ('openassessment', 'chapter')\n",
    "    Group By module_id) sub\n",
    "    On sub.module_id = c.module_id\n",
    "    Order By index\"\"\".format(course_id)\n",
    "    df = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    return df\n",
    "\n",
    "courseItem = course_item()\n",
    "# exclude course items accessed by fewer than 20 students \n",
    "courseItem = courseItem[courseItem.nactive > 20].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Overall learner engagement vs. Course structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "colors = {'video': 'rgb(106,61,154)', 'graded_problem': 'rgb(31,120,180)', \n",
    "          'self_test': 'rgb(51,160,44)', 'openassessment': 'rgb(255,127,0)', 'chapter': 'rgb(0, 0, 0)'}\n",
    "# make it center in the middle\n",
    "trace1 = go.Bar(x=courseItem.index+1, y=courseItem.nactive, hoverinfo='text',\n",
    "               text=['{0}:<br>number of students: {1}'.format(name.encode('utf-8'), value) \n",
    "                      for name, value in zip(courseItem.name, courseItem.nactive)],\n",
    "               marker=dict(color=courseItem.category.apply(lambda x: colors[x]).values))\n",
    "trace2 = go.Bar(x=courseItem.index+1, y=-courseItem.nactive, hoverinfo='none',\n",
    "               marker=dict(color=courseItem.category.apply(lambda x: colors[x]).values))\n",
    "\n",
    "data = [trace1, trace2]\n",
    "layout = go.Layout(barmode='relative', title='', \n",
    "                   xaxis=dict(showticklabels=False, title='Course structure'), \n",
    "                   yaxis=dict(showticklabels=False, showgrid=False, title='Number of learners engaging', zeroline=False), \n",
    "                   height=350, width=850, margin=go.Margin(t=25, b=25), showlegend=False)\n",
    "fig = go.Figure(data=data, layout=layout)\n",
    "\n",
    "print('What is the overall structure of my course? How many learners are engaging with different parts of my course?' + '\\n')\n",
    "print('This \"Eiffel Tower\" graph shows, for each component type (e.g. video) in each course module (e.g. Part I' + '\\n'\n",
    "      + 'Conclusion), the number of students who interacted with that component. For more information, please see below:')\n",
    "config = {\"displayModeBar\": True, \"showLink\": False, \"displaylogo\": False, \"modeBarButtonsToRemove\": ['sendDataToCloud', 'zoomIn2d', 'zoomOut2d', 'autoScale2d', 'resetScale2d', 'hoverClosestCartesian', 'hoverCompareCartesian']}\n",
    "plot_html, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "display(HTML(plot_html))\n",
    "display(HTML('<a href=\"#\" style=\"float: right;\">Read more...</a>'))\n",
    "display(HTML('<a href=\"#Table-of-Contents\" style=\"float: left;\">Back to top</a><a href=\"\" target=_blank style=\"float: right;\">Compare with other courses</a>'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def query_nstudents(cs = course_structure, course_id = course_id):\n",
    "    \"\"\"\n",
    "    Query and calculate number of students viewed the video, attempted the problem\n",
    "    \"\"\"\n",
    "    query = \"\"\"\n",
    "    Select vv.mm as mmm, l.video_id As video_id, nstudents, position As length\n",
    "    From (Select video_id, position, Row_number() Over (Partition By video_id Order By position Desc) As rn\n",
    "    From [{0}.video_stats_day]) l\n",
    "    Join \n",
    "    (Select video_id, exact_count_distinct(username) As nstudents\n",
    "    From [{0}.video_stats_day]\n",
    "    Where position != 0\n",
    "    Group By video_id) n\n",
    "    On l.video_id = n.video_id\n",
    "    \n",
    "    Join \n",
    "    (Select max(position) as mm, video_id from [{0}.video_stats_day]\n",
    "    group by video_id) vv\n",
    "    \n",
    "    On l.video_id = vv.video_id    \n",
    "    \n",
    "    Where rn=5\"\"\".format(course_id)\n",
    "    try:\n",
    "        videos = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "        videos = cs[cs.category=='video'].merge(videos, left_on='url_name', right_on='video_id')\n",
    "        videos = videos[(videos.length<1800) & (videos.nstudents>10)].reset_index(drop=True)\n",
    "    except:\n",
    "        videos = None\n",
    "\n",
    "    query = \"\"\"\n",
    "    SELECT c.chapter_name As chapter, c.section_name As section_name,\n",
    "    p.item_short_id As name, c.item_id As problem_id,\n",
    "    count(*) As nstudents, Sum(item_grade) As ncorrect\n",
    "    FROM [{0}.person_item] p\n",
    "    Join [{0}.course_item] c\n",
    "    On p.item_short_id = c.item_short_id\n",
    "    Group By chapter, section_name, name, c.item_nid, problem_id\n",
    "    Order By c.item_nid\"\"\".format(course_id)\n",
    "\n",
    "    graded_problems = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    graded_problems = graded_problems[graded_problems.ncorrect != 0].reset_index(drop=True)\n",
    "    \n",
    "    query = \"\"\"\n",
    "    SELECT ca.name As name, ca.index As index, ca.url_name As problem_id,\n",
    "    count(*) As nstudents, Sum(grade) As ncorrect\n",
    "    FROM [{0}.course_axis] ca\n",
    "    Join [{0}.problem_analysis] pa\n",
    "    On pa.problem_url_name = ca.url_name        \n",
    "    Where ca.graded!='true' AND ca.category='problem'\n",
    "    Group By name, index, problem_id\n",
    "    Order By index\"\"\".format(course_id)\n",
    "\n",
    "    self_test = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    self_test = self_test[self_test.ncorrect != 0].reset_index(drop=True)    \n",
    "    '''query = \"\"\"\n",
    "    SELECT v.video_id As vi, v.length As vl,\n",
    "    FROM [{0}.video_axis] va\"\"\".format(course_id)\n",
    "\n",
    "    graded_problems = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    graded_problems = graded_problems[graded_problems.ncorrect != 0].reset_index(drop=True)'''\n",
    "    \n",
    "    return videos, graded_problems, self_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "videos, problems, self_test = query_nstudents(cs = course_structure)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Video activity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "choices = ['rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)', \n",
    "           'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)', \n",
    "           'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)'\n",
    "          , 'rgba(202,178,214, 0.8)', 'rgba(202,178,214, 0.8)']\n",
    "# create a dictionary to map colors to chapters\n",
    "colors_chapter = dict(zip(cs_chapter.index, choices[:len(cs_chapter.index)]))\n",
    "\n",
    "choices2 = ['rgba(106,61,154, 0.8)', 'rgba(106,61,154, 0.8)', 'rgba(106,61,154, 0.8)', 'rgba(106,61,154 0.8)', \n",
    "           'rgba(106,61,154, 0.8)', 'rgba(106,61,154, 0.8)', 'rgba(106,61,154, 0.8)', 'rgba(106,61,154, 0.8)', \n",
    "           'rgba(106,61,154, 0.8)', 'rgba(106,61,154, 0.8)']\n",
    "# create a dictionary to map colors to chapters, for dataset of full videos lengths\n",
    "colors_chapter2 = dict(zip(cs_chapter.index, choices2[:len(cs_chapter.index)]))\n",
    "\n",
    "choices3 = ['rgba(178,223,138, 0.8)', 'rgba(178,223,138, 0.8)', 'rgba(178,223,138, 0.8)', 'rgba(178,223,138, 0.8)', \n",
    "           'rgba(178,223,138, 0.8)', 'rgba(178,223,138, 0.8)', 'rgba(178,223,138, 0.8)', 'rgba(178,223,138, 0.8)', \n",
    "           'rgba(178,223,138, 0.8)', 'rgba(178,223,138, 0.8)']\n",
    "# create a dictionary to map colors to chapters\n",
    "colors_chapter3 = dict(zip(cs_chapter.index, choices3[:len(cs_chapter.index)]))\n",
    "\n",
    "choices4 = ['rgba(51,160,44, 0.8)', 'rgba(51,160,44, 0.8)', 'rgba(51,160,44, 0.8)', 'rgba(51,160,44, 0.8)', \n",
    "           'rgba(51,160,44, 0.8)', 'rgba(51,160,44, 0.8)', 'rgba(51,160,44, 0.8)', 'rgba(51,160,44, 0.8)', \n",
    "           'rgba(51,160,44, 0.8)', 'rgba(51,160,44, 0.8)']\n",
    "# create a dictionary to map colors to chapters, for dataset of full videos lengths\n",
    "colors_chapter4 = dict(zip(cs_chapter.index, choices4[:len(cs_chapter.index)]))\n",
    "\n",
    "choices5 = ['rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)'\n",
    "           , 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)'\n",
    "           , 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)'\n",
    "           , 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)'\n",
    "           , 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)', 'rgba(166,206,227, 0.8)']\n",
    "# create a dictionary to map colors to chapters, for dataset of full videos lengths\n",
    "colors_chapter5 = dict(zip(cs_chapter.index, choices5[:len(cs_chapter.index)]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Video engagement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# x-axis needs to start from 1, videos.length and videos.length are normalized so that maximum=100\n",
    "# hoverinfo => question_name: actual value\n",
    "if videos is not None:\n",
    "    trace1 = go.Bar(x = videos.index+1, y = videos.length/60,\n",
    "                    text=['{0}: {1}%'.format(name.encode('utf-8'), value) \n",
    "                          for name, value in zip(videos.name, videos.length/videos.mmm*100)], hoverinfo='text',\n",
    "                    marker=dict(color='rgba(202,178,214, 0.8)'), name='video_length')\n",
    "    '''trace2 = go.Bar(x = videos.index+1, y = -videos.nstudents/(videos.nstudents.max()/100), \n",
    "                   text=['{0}: {1}'.format(name.encode('utf-8'), value) \n",
    "                         for name, value in zip(videos.name, videos.nstudents)], hoverinfo='text',\n",
    "                    marker=dict(color=videos.chapter.map(colors_chapter)), name='nstudents_watched')'''\n",
    "    trace2 = go.Bar(x = videos.index+1, y = (videos.mmm-videos.length)/60,\n",
    "                    text=['{0}: {1}%'.format(name.encode('utf-8'), value) \n",
    "                          for name, value in zip(videos.name, itertools.repeat('100'))], hoverinfo='text',\n",
    "                    marker=dict(color='rgba(106,61,154, 0.8)'), name='video_length', orientation = 'v',)\n",
    "    \n",
    "    data = [trace1, trace2]\n",
    "    layout = go.Layout(barmode='stack', xaxis=dict(showticklabels=False), title='',\n",
    "                       yaxis=dict(showticklabels=True, showgrid=False, title='video length (minutes)'), \n",
    "                       height=400, width=850, margin=go.Margin(t=50, b=50), showlegend=False)\n",
    "    fig = go.Figure(data=data, layout=layout)\n",
    "    print('\\n\\n' + 'What percentage of each video did learners watch?' + '\\n')\n",
    "    print('This graph shows the mean (across all students) percentage of a given video watched, across all videos. For more information, please see below:')\n",
    "    config = {\"displayModeBar\": True, \"showLink\": False, \"displaylogo\": False, \"modeBarButtonsToRemove\": ['sendDataToCloud', 'zoomIn2d', 'zoomOut2d', 'autoScale2d', 'resetScale2d', 'hoverClosestCartesian', 'hoverCompareCartesian']}\n",
    "    plot_html, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "    display(HTML(plot_html))\n",
    "    display(HTML('<a href=\"#Table-of-Contents\" style=\"float: left;\">Back to top</a><a href=\"#\" style=\"float: right;\">Read more...</a>'))\n",
    "else:\n",
    "    display(HTML('There are no videos in this course.'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Graded problem activity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Graded problem engagement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# x-axis needs to start from 1, problems.nstudents is normalized so that maximum=100 (the same as pct_correct)\n",
    "# hoverinfo: question_name: actual value\n",
    "pct_correct = (problems.ncorrect/problems.nstudents*100).round(2)\n",
    "trace1 = go.Bar(x=problems.index+1, y = pct_correct, \n",
    "                text=['{0}<br>{1}: {2}%'.format(section.encode('utf-8'), name.encode('utf-8'), value) \n",
    "                      for section, name, value in zip(problems.section_name, problems.name, pct_correct)],\n",
    "                hoverinfo='text', marker=dict(color='rgba(166,206,227, 0.8)'), name='pct_correct')\n",
    "trace2 = go.Bar(x=problems.index+1, y = 100-pct_correct, \n",
    "                text=['{0}<br>{1}: {2}%'.format(section.encode('utf-8'), name.encode('utf-8'), value) \n",
    "                      for section, name, value in zip(problems.section_name, problems.name, pct_correct)],\n",
    "                hoverinfo='text', marker=dict(color='rgba(166,206,227, 0.8)'), name='pct_correct')\n",
    "\n",
    "data = [trace1]\n",
    "layout = go.Layout(barmode='stack', xaxis=dict(showticklabels=False), title='',\n",
    "                   yaxis=dict(showticklabels=True, showgrid=False, title='success rate'), \n",
    "                   height=440, width=850, margin=go.Margin(t=25, b=25), showlegend=False)\n",
    "fig = go.Figure(data=data, layout=layout)\n",
    "print('\\n\\n' + 'What percentage of learners answered each problem correctly on the first attempt?' + '\\n')\n",
    "print('This graph shows the percentage of learners who answered a given problem correctly on their first attempt, across all problems. For more information, please see below:')\n",
    "config = {\"displayModeBar\": True, \"showLink\": False, \"displaylogo\": False, \"modeBarButtonsToRemove\": ['sendDataToCloud', 'zoomIn2d', 'zoomOut2d', 'autoScale2d', 'resetScale2d', 'hoverClosestCartesian', 'hoverCompareCartesian']}\n",
    "plot_html, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "display(HTML(plot_html))\n",
    "display(HTML('<a href=\"#Table-of-Contents\" style=\"float: left;\">Back to top</a><a href=\"#\" style=\"float: right;\">Read more...</a>'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Self-test problem engagement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# x-axis needs to start from 1, problems.nstudents is normalized so that maximum=100 (the same as pct_correct)\n",
    "# hoverinfo: question_name: actual value\n",
    "pct_correct2 = (self_test.ncorrect/self_test.nstudents*100).round(2)\n",
    "trace1 = go.Bar(x=self_test.index+1, y = pct_correct2, \n",
    "                text=['<br>{0}: {1}%'.format(name.encode('utf-8'), value) \n",
    "                      for name, value in zip(self_test.name, pct_correct2)],\n",
    "                hoverinfo='text', marker=dict(color=\"rgb(178,223,138, 0.8)\"), name='pct_correct')\n",
    "\n",
    "data = [trace1]\n",
    "layout = go.Layout(barmode='stack', xaxis=dict(showticklabels=False), title='',\n",
    "                   yaxis=dict(showticklabels=True, showgrid=False, title='success rate'), \n",
    "                   height=440, width=850, margin=go.Margin(t=25, b=25), showlegend=False)\n",
    "fig = go.Figure(data=data, layout=layout)\n",
    "print('\\n\\n' + 'What percentage of learners answered each problem correctly on the first attempt?' + '\\n')\n",
    "print('This graph shows the percentage of learners who answered a given problem correctly on their first attempt, across all problems. For more information, please see below:')\n",
    "config = {\"displayModeBar\": True, \"showLink\": False, \"displaylogo\": False, \"modeBarButtonsToRemove\": ['sendDataToCloud', 'zoomIn2d', 'zoomOut2d', 'autoScale2d', 'resetScale2d', 'hoverClosestCartesian', 'hoverCompareCartesian']}\n",
    "plot_html, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "display(HTML(plot_html))\n",
    "display(HTML('<a href=\"#Table-of-Contents\" style=\"float: left;\">Back to top</a><a href=\"#\" style=\"float: right;\">Read more...</a>'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def query_least(course_id=course_id):\n",
    "    \"\"\"\n",
    "    Given the course_id, return the distribution of answers for the 10 least successful graded_problems\n",
    "    \"\"\"\n",
    "    least = \"', '\".join(problems.loc[pct_correct.argsort()[:5], 'problem_id'].values)\n",
    "    # query 10 least successful problems and corresponding responses by each student\n",
    "    query = \"\"\"\n",
    "    Select item.answer_id As problem_id, user_id, item.response As response, item.correctness\n",
    "    From [{0}.problem_analysis]\n",
    "    Where item.answer_id in ('{1}')\n",
    "    \"\"\".format(course_id, least)\n",
    "\n",
    "    answers = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    answers = answers.merge(problems[['name', 'problem_id']])\n",
    "    # the correct answers: most common responses\n",
    "    correct =  answers[answers.item_correctness=='correct'].groupby('name').response.first().to_dict()\n",
    "    # total # of responses\n",
    "    count = answers.groupby('name').response.count()\n",
    "    \n",
    "    answers.response = answers.response.apply(lambda x: x.replace('[', '').replace(']', '').split(', '))\n",
    "    rows = []\n",
    "    # explode items in a list to multiple rows <= those with more than 1 answer\n",
    "    _ = answers.apply(lambda row: [rows.append([row['name'], row['user_id'], choice]) \n",
    "                             for choice in row.response], axis=1)\n",
    "    answers_new = pd.DataFrame(rows, columns=['name', 'user_id', 'response'])#.set_index(['name', 'opponent'])\n",
    "    # aggregate and calculate % distribution of answers for each problem\n",
    "    if not count.empty:\n",
    "        answers_pct = answers_new.groupby('name').response.value_counts().unstack('name').divide(count, axis=1)\n",
    "    else:\n",
    "        answer_pct = None\n",
    "    return correct, answers_pct#, answers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Ten graded problems with lowest success rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "if course_id in not_mc_list:\n",
    "    pass\n",
    "else:\n",
    "    correct, answers_pct = query_least()\n",
    "    if answers_pct is not None:    \n",
    "        def correct_color(col):\n",
    "            # green for correct answers, blue otherwise\n",
    "            return ['rgb(31,120,180)' if x in correct[answers_pct.columns[col]] else 'rgb(166,206,227)' for x in answers_pct.index]\n",
    "\n",
    "        fig = tls.make_subplots(rows=1, cols=5, print_grid=False, vertical_spacing=0.25)\n",
    "        # the first five problems\n",
    "        for i in range(5):\n",
    "            quesion = answers_pct.iloc[:, i].dropna()*100\n",
    "            fig.append_trace(go.Bar(x=quesion.index, y=quesion, name=answers_pct.columns[i], \n",
    "                                    marker=dict(color=correct_color(i)), showlegend=False), 1, i+1)    \n",
    "\n",
    "        for i in range(1, answers_pct.shape[1]+1):\n",
    "            fig['layout']['xaxis%s' % i].update(tickangle=45, tickfont=dict(size=8),\n",
    "                                                title=answers_pct.columns[i-1], titlefont=dict(size=10))\n",
    "            fig['layout']['yaxis%s' % i].update(showgrid=False, tickfont=dict(size=8), title='% of answers', range=[0,100])\n",
    "\n",
    "            fig['layout'].update(height=500, width=850, \n",
    "                                 title = '')  \n",
    "        print('\\n\\n' + 'What problems did learners struggle with most?' + '\\n')\n",
    "        print('Each graph represents a single problem, and shows the percentage of initial responses for each possible answer.' + '\\n' + 'The five problems' +\n",
    "          ' with the lowest success rate are shown. For more information, please see below:')    \n",
    "        config = {\"displayModeBar\": True, \"showLink\": False, \"displaylogo\": False, \"modeBarButtonsToRemove\": ['sendDataToCloud', 'zoomIn2d', 'zoomOut2d', 'autoScale2d', 'resetScale2d', 'hoverClosestCartesian', 'hoverCompareCartesian']}\n",
    "        plot_html, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "        display(HTML(plot_html))\n",
    "        display(HTML('<a href=\"#Table-of-Contents\" style=\"float: left;\">Back to top</a><a href=\"#\" style=\"float: right;\">Read more...</a>'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Overall engagement:\n",
    "** Learner type **\n",
    "- Registered: learners registered in the course\n",
    "- Sampled: learners who accessed the course at least once\n",
    "- Involved: learners with sum_dt > 15 min\n",
    "- Passed: learners whose grade is at least 50%\n",
    "- Verified: those purchased the verified certificate\n",
    "\n",
    "** sum_dt **: Total elapsed time spent by learner on this course, based on time difference between consecutive events, with a 5 min max cutoff, based on event data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def query_pc(course_id = course_id):     \n",
    "    \"\"\"\n",
    "    Given course_id(e.g. 'UBCx__Marketing1x__3T2015'), query and calculate ndays_act, sum_dt, nforum_posts, \n",
    "    nvideos_watched, nproblems_attempted, pct_video_watched, pct_problem_attempted for involved students,\n",
    "    also return total_videos and total_problems.\n",
    "    \"\"\"\n",
    "    query = \"\"\"\n",
    "    Select pc.user_id As user_id, pc.course_id As course_id, pc.mode As mode, pc.grade As grade, \n",
    "    pc.ndays_act As ndays_act, pc.sum_dt As sum_dt, pc.nforum_posts As nforum_posts,\n",
    "    v.videos_watched As nvideos_watched, p.problems_attempted As nproblems_attempted\n",
    "    From [{0}.person_course] pc\n",
    "    Left Join\n",
    "    (SELECT username, exact_count_distinct(video_id) As videos_watched \n",
    "    FROM [{0}.video_stats_day]\n",
    "    Group By username) v\n",
    "    on pc.username = v.username\n",
    "    Left Join \n",
    "    (Select user_id, exact_count_distinct(item.answer_id) As problems_attempted\n",
    "    From [{0}.problem_analysis]\n",
    "    Group By user_id) p\n",
    "    On pc.user_id = p.user_id\n",
    "    Where pc.sum_dt > 0\"\"\".format(course_id)\n",
    "    total_videos = 0\n",
    "    try:\n",
    "        df = pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False).fillna(0)\n",
    "        total_videos = min(df.nvideos_watched.max(), cs_chapter.video.sum())\n",
    "        df['pct_video_watched'] = df.nvideos_watched / total_videos        \n",
    "    except:      \n",
    "        vidya_query = \"\"\"\n",
    "        Select pc.user_id As user_id, pc.course_id As course_id, pc.mode As mode, pc.grade As grade, \n",
    "        pc.ndays_act As ndays_act, pc.sum_dt As sum_dt, pc.nforum_posts As nforum_posts,\n",
    "        p.problems_attempted As nproblems_attempted\n",
    "        From [{0}.person_course] pc\n",
    "        Left Join \n",
    "        (Select user_id, exact_count_distinct(item.answer_id) As problems_attempted\n",
    "        From [{0}.problem_analysis]\n",
    "        Group By user_id) p\n",
    "        On pc.user_id = p.user_id\n",
    "        Where pc.sum_dt > 0\"\"\".format(course_id)\n",
    "        df = pd.io.gbq.read_gbq(vidya_query, project_id='yourprojectname', verbose=False).fillna(0)\n",
    "        total_videos = 0\n",
    "        df['pct_video_watched'] = 0\n",
    "    # course_axis includes items not accessible to the students, \n",
    "    # => total_videos/total_problems are maximum number of videos/problems students accessed \n",
    "    # if smaller than the number from course_axis then use the latter one\n",
    "\n",
    "    \n",
    "    total_problems = min(df.nproblems_attempted.max(), \n",
    "                         cs_chapter.graded_problem.sum() + cs_chapter.self_test.sum() if 'self_test' in cs_chapter.columns and 'graded_problem' in cs_chapter.columns\n",
    "                         else cs_chapter.self_test.sum() if 'self_test' in cs_chapter.columns\n",
    "                         else cs_chapter.graded_problem.sum())\n",
    "    df['pct_problem_attempted'] = df.nproblems_attempted / total_problems\n",
    "    \n",
    "    \n",
    "    return  total_videos, total_problems, df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def compute_srp(pc):\n",
    "    # pc for the sampled\n",
    "    pc_sampled = pc.copy()\n",
    "    # pc for the involved\n",
    "    pc_learned = pc[pc.sum_dt>900].copy()\n",
    "    # pc for the passed\n",
    "    pc_passed = pc[pc.grade>=0.5].copy()\n",
    "    # added category column\n",
    "    pc_sampled['category'] = 'Sampled'\n",
    "    pc_learned['category'] = 'Involved'\n",
    "    pc_passed['category'] = 'Passed'\n",
    "    srp = pd.concat([pc_sampled, pc_learned, pc_passed])\n",
    "    # aggregate nforum_posts => # students posted, others by median, by learner type\n",
    "    try:\n",
    "        srp_agg = srp.groupby('category').agg({'nvideos_watched': np.median, \n",
    "                                 'nproblems_attempted': np.median, 'ndays_act': np.median, \n",
    "                                 'sum_dt': np.median, 'nforum_posts': lambda x: (x > 0).sum()})\n",
    "    except:\n",
    "        srp_agg = srp.groupby('category').agg({ \n",
    "                                 'nproblems_attempted': np.median, 'ndays_act': np.median, \n",
    "                                 'sum_dt': np.median, 'nforum_posts': lambda x: (x > 0).sum()})        \n",
    "    srp_agg = srp_agg.reindex(index = ['Sampled', 'Involved', 'Passed'])\n",
    "    \n",
    "    return srp_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "total_videos, total_problems, pc = query_pc()\n",
    "# convert to %\n",
    "pc[['pct_video_watched', 'pct_problem_attempted']] = \\\n",
    "pc[['pct_video_watched', 'pct_problem_attempted']].applymap(lambda x: \"{0:.2f}\".format(x * 100))\n",
    "srp_agg = compute_srp(pc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "def plot_pls(df, course_id=course_id, title=None):\n",
    "    \"\"\"\n",
    "    Plot students' activity: median sum_dt, median ndays_act, # students posted, \n",
    "    nproblems_attempted, nvideos_watched, grouped by passed vs. involved vs. sampled;\n",
    "    \"\"\"\n",
    "    if course_id in pe_list:\n",
    "        query = \\\n",
    "        \"\"\"SELECT Count(*) As Registered, \n",
    "        Sum(Case When sum_dt > 0 Then 1 Else 0 End) As Sampled,\n",
    "        Sum(Case When sum_dt > 900 Then 1 Else 0 End) As Involved, \n",
    "        Sum(Case When grade >= 0.5 Then 1 Else 0 End) As Passed\n",
    "        FROM [%s.person_course]\"\"\" % course_id\n",
    "    else:\n",
    "        query = \\\n",
    "        \"\"\"SELECT Count(*) As Registered, \n",
    "        Sum(Case When sum_dt > 0 Then 1 Else 0 End) As Sampled,\n",
    "        Sum(Case When sum_dt > 900 Then 1 Else 0 End) As Involved, \n",
    "        Sum(Case When grade >= 0.5 Then 1 Else 0 End) As Passed, \n",
    "        Sum(Case When mode='verified' Then 1 Else 0 End) As Verified\n",
    "        FROM [%s.person_course]\"\"\" % course_id\n",
    "    stats =  pd.io.gbq.read_gbq(query, project_id='yourprojectname', verbose=False)\n",
    "    # print stats\n",
    "\n",
    "    trace1 = go.Bar(x=stats.values[0], y=stats.columns, orientation='h', showlegend=False, name='# of conversion')\n",
    "    if 'nvideos_watched' in df:\n",
    "        trace2 = go.Bar(x=df.index, y=df.nvideos_watched, showlegend=True, name='nvideos watched')\n",
    "    trace3 = go.Bar(x=df.index, y=df.nproblems_attempted, showlegend=True, name='nproblems attempted')\n",
    "    trace4 = go.Bar(x=df.index, y=df.sum_dt/3600, showlegend=False, name='median sum_dt (H)')\n",
    "    trace5 = go.Bar(x=df.index, y=df.ndays_act, showlegend=False, name='median days active')\n",
    "    trace6 = go.Bar(x=df.index, y=df.nforum_posts, showlegend=False, name='# students posted')\n",
    "\n",
    "    fig = tls.make_subplots(rows=1, cols=5, shared_xaxes=True, print_grid=False)\n",
    "    fig.append_trace(trace1, 1, 1)\n",
    "    if 'nvideos_watched' in df:\n",
    "        fig.append_trace(trace2, 1, 5)\n",
    "    fig.append_trace(trace3, 1, 5)\n",
    "    fig.append_trace(trace4, 1, 2)\n",
    "    fig.append_trace(trace5, 1, 3)\n",
    "    fig.append_trace(trace6, 1, 4)\n",
    "\n",
    "\n",
    "    fig['layout'].update(barmode='stack', height=300, width=900, margin=go.Margin(t=40), title=title)\n",
    "    fig['layout']['legend'].update(font=dict(size=10))\n",
    "    fig['layout']['xaxis1'].update(title='# students', showgrid=False,\n",
    "                                  titlefont=dict(size=12), tickfont=dict(size=10))\n",
    "    fig['layout']['xaxis2'].update(title='median sum_dt (H)', showgrid=False,\n",
    "                                  titlefont=dict(size=12), tickfont=dict(size=10))\n",
    "    fig['layout']['xaxis3'].update(title='median days active', showgrid=False,\n",
    "                                  titlefont=dict(size=12), tickfont=dict(size=10))\n",
    "    fig['layout']['xaxis4'].update(title='# students posted', showgrid=False,\n",
    "                                  titlefont=dict(size=12), tickfont=dict(size=10))\n",
    "    fig['layout']['xaxis5'].update(title='median events', showgrid=False, \n",
    "                                   titlefont=dict(size=12), tickfont=dict(size=10))\n",
    "    fig['layout']['yaxis1'].update(autorange='reversed', showgrid=False, tickfont=dict(size=10))\n",
    "    fig['layout']['yaxis2'].update(showgrid=False, tickfont=dict(size=10))\n",
    "    fig['layout']['yaxis3'].update(showgrid=False, tickfont=dict(size=10))\n",
    "    fig['layout']['yaxis4'].update(showgrid=False, tickfont=dict(size=10))\n",
    "    fig['layout']['yaxis5'].update(showgrid=False, tickfont=dict(size=10))\n",
    "\n",
    "    config = {\"displayModeBar\": True, \"showLink\": False, \"displaylogo\": False, \"modeBarButtonsToRemove\": ['sendDataToCloud', 'zoomIn2d', 'zoomOut2d', 'autoScale2d', 'resetScale2d', 'hoverClosestCartesian', 'hoverCompareCartesian']}\n",
    "    plot_html, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "    display(HTML(plot_html))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "plot_pls(srp_agg, title=\"Students' engagement: Sampled vs. Involved vs. Passed\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# pc for the involved\n",
    "pc_activity = pc[pc.sum_dt>900].copy()\n",
    "# density map\n",
    "trace2 = go.Histogram2d(x=pc_activity.pct_video_watched, y=pc_activity.pct_problem_attempted,\n",
    "            histnorm='probability',\n",
    "            autobinx=False,\n",
    "            xbins=dict(start=0, end=100, size=10),\n",
    "            autobiny=False,\n",
    "            ybins=dict(start=0, end=100, size=10),\n",
    "            colorscale=[[0, 'rgb(255,255,255)'], [1/1000, 'rgb(255,255,255)'], [1/100, 'rgb(242,211,56)'], \n",
    "                            [1/10, 'rgb(242,143,56)'], [1, 'rgb(217,30,30)']],\n",
    "            zsmooth='false')\n",
    "# illustration on how to read the density map\n",
    "z = [[1, 0.5, 0.5, 0.5], [0.5, 0.5, 0, 0], [0.5, 0, 0.5, 0], [0.5, 0, 0, 1]]\n",
    "z_text = [['Early dropout', '', 'Videos only', ''], ['', 'Progress', '', ''],\n",
    "         ['Problems only', '', 'Progress', ''], ['', '', '', 'Completed']]\n",
    "annotations = []\n",
    "for n, row in enumerate(z):\n",
    "    for m, val in enumerate(row):\n",
    "        text = z_text[n][m]\n",
    "        annotations.append(\n",
    "            dict(\n",
    "                text=str(text),\n",
    "                x=m, y=n,\n",
    "                font=dict(color='black'),\n",
    "                showarrow=False)\n",
    "            )\n",
    "colorscale=[[0, 'rgb(82,82,82)'], [0.5, 'rgb(150,150,150)'], [1, 'rgb(204,204,204)']]\n",
    "trace1 = go.Heatmap(z=z, colorscale=colorscale, showscale=False, hoverinfo='none')\n",
    "\n",
    "fig = tls.make_subplots(rows=1, cols=2, print_grid=False)\n",
    "fig.append_trace(trace1, 1, 1)\n",
    "fig.append_trace(trace2, 1, 2)\n",
    "\n",
    "fig['layout']['xaxis1'].update(ticks='', showticklabels=False, showgrid=False, title='% videos')\n",
    "fig['layout']['xaxis2'].update(title='% videos (total:{0})'.format(int(total_videos)))\n",
    "fig['layout']['yaxis1'].update(ticks='', showticklabels=False, showgrid=False, zeroline=False, title='% problems')\n",
    "fig['layout']['yaxis2'].update(title='% problems (total:{0})'.format(int(total_problems)))\n",
    "fig['layout'].update(\n",
    "    width=850, height=400, annotations=annotations,\n",
    "    title='Density map illustrating pattern of video and problem activity for the involved',\n",
    "    margin=go.Margin(l=40, t=40)\n",
    ")\n",
    "\n",
    "config = {\"displayModeBar\": True, \"showLink\": False, \"displaylogo\": False, \"modeBarButtonsToRemove\": ['sendDataToCloud', 'zoomIn2d', 'zoomOut2d', 'autoScale2d', 'resetScale2d', 'hoverClosestCartesian', 'hoverCompareCartesian']}\n",
    "plot_html, plotdivid, width, height = py.offline._plot_html(fig, config, True, 525, 525, False)\n",
    "display(HTML(plot_html))"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  },
  "toc": {
   "toc_cell": false,
   "toc_number_sections": false,
   "toc_section_display": "none",
   "toc_threshold": 6,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}