data_wrangle.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Libraries and Constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "import ast\n",
    "import nltk\n",
    "import functools\n",
    "import re\n",
    "import json\n",
    "import statistics\n",
    "from collections import Counter\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "WORD_COUNTS_NAME = 'word_counts'\n",
    "WORD_COUNTS_LIST_NAME = 'word_counts_list'\n",
    "TOTAL_NAME = 'total_words'\n",
    "COMMENTS_COLUMN = 'comments'\n",
    "FREQUENCY_NAME = 'frequency'\n",
    "MAGNIFY_LIST = ['too', 'very', 'that', 'so', 'as']\n",
    "NEGATIVE_LIST = ['not', 'aren\\'t', 'isn\\'t', 'wasn\\'t', 'didn\\'t']\n",
    "with open('constants/word_categories.json', 'r') as f:\n",
    "    WORD_CATEGORIES = json.load(f)\n",
    "IMPORTANT_WORDS = reduce(lambda x, y: x + y, WORD_CATEGORIES.values())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# HIGH MEMORY: Don't load unless you have to.  The code to reproduces this is below.\n",
    "with open('overall_list.json', 'r') as f:\n",
    "    ALL_WORKLOAD_LIST = json.load(f)\n",
    "    \n",
    "# TODO: Finish this for other metrics\n",
    "# with open('overall_list.json', 'r') as f:\n",
    "#     ALL_RECOMMEND_LIST = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('raw_data/final.csv').drop('Unnamed: 0', 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''Get a list of words from a string sentence.'''\n",
    "def get_words(text):\n",
    "    return re.compile('\\w+').findall(text)\n",
    "\n",
    "'''See if a word was used in a negative context'''\n",
    "def probe_if_negative(word1, word2):\n",
    "    if word1 in NEGATIVE_LIST:\n",
    "        return True\n",
    "    elif (word2 in NEGATIVE_LIST) and (word1 in MAGNIFY_LIST):\n",
    "        return True\n",
    "    else:\n",
    "        return False\n",
    "\n",
    "def get_word_dict(comments, to_return):\n",
    "    d = {}\n",
    "    t = 0\n",
    "    for comment in comments:\n",
    "        last_word = ''\n",
    "        two_words_ago = ''\n",
    "        for word in get_words(comment):\n",
    "            word = word.lower() # Make lower case\n",
    "            t += 1\n",
    "            try:\n",
    "                if probe_if_negative(last_word, two_words_ago):\n",
    "                    d['not_' + word] += 1\n",
    "                else:\n",
    "                    d[word] += 1\n",
    "            except: \n",
    "                if probe_if_negative(last_word, two_words_ago):\n",
    "                    d['not_' + word] = 1\n",
    "                else:\n",
    "                    d[word] = 1\n",
    "            two_words_ago = last_word\n",
    "            last_word = word\n",
    "    if to_return == 'words':\n",
    "        return d\n",
    "    elif to_return == 'count':\n",
    "        return t\n",
    "\n",
    "def preprocessing(to_return, row):\n",
    "    raw_comments = row[COMMENTS_COLUMN]\n",
    "    return get_word_dict(ast.literal_eval(raw_comments), to_return)\n",
    "\n",
    "def count_words(in_df):\n",
    "    in_df = in_df.copy()\n",
    "    in_df[TOTAL_NAME] = in_df.apply(functools.partial(preprocessing, 'count'), axis=1)\n",
    "    in_df[WORD_COUNTS_NAME] = in_df.apply(functools.partial(preprocessing, 'words'), axis=1)\n",
    "    return in_df\n",
    "\n",
    "'''Calculates the frequency of a word in the comments of row'''\n",
    "def calculate_frequency(word, row):\n",
    "    try:\n",
    "        return float(row[WORD_COUNTS_NAME][word]) / float(row[TOTAL_NAME])\n",
    "    except:\n",
    "        return 0\n",
    "    \n",
    "'''Weighted mean'''\n",
    "def weighted_mean(x, w):\n",
    "    return np.sum(x * w) / np.sum(w)\n",
    "\n",
    "'''Weighted covariance'''\n",
    "def weighted_cov(x, y, w):\n",
    "    return np.sum(w * (x - weighted_mean(x, w)) * (y - weighted_mean(y, w))) / np.sum(w)\n",
    "\n",
    "'''Weighted correlation'''\n",
    "def corr(x, y, w):\n",
    "    return weighted_cov(x, y, w) / np.sqrt(weighted_cov(x, x, w) * weighted_cov(y, y, w))\n",
    "\n",
    "''' Returns two column dataframe consisting of df[column] and the frequency of the word.\n",
    "    Weights according to the number of comments. '''\n",
    "def find_word_correlations(in_df, word, column):\n",
    "    freq = in_df.apply(functools.partial(calculate_frequency, word), axis=1)\n",
    "    possibly_nan = pd.concat([freq, in_df[column], in_df[COMMENTS_COLUMN]], \n",
    "                             axis=1, \n",
    "                             keys=[FREQUENCY_NAME, column, COMMENTS_COLUMN])\n",
    "    clean_df = possibly_nan.dropna(axis=0, how='any')\n",
    "    return corr(clean_df[FREQUENCY_NAME], \n",
    "                clean_df[column],\n",
    "                clean_df[COMMENTS_COLUMN].apply(lambda x: len(ast.literal_eval(x))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sum_dict_values(some_dict):\n",
    "    return sum(some_dict.values())\n",
    "\n",
    "'''Finds overall frequency of words in df by constructing total count and total dict'''\n",
    "def find_word_freqs_over_df(in_df, agg_method):\n",
    "    in_df = in_df.copy()\n",
    "    if agg_method == 'counter':\n",
    "        D = Counter({})\n",
    "        for index, row in in_df.iterrows():\n",
    "            D += Counter(row[WORD_COUNTS_NAME])\n",
    "        return dict(D)\n",
    "    elif agg_method == 'list':\n",
    "        D = {}\n",
    "        keys = set({})\n",
    "        for index, row in in_df.iterrows():\n",
    "            r = row[WORD_COUNTS_LIST_NAME]\n",
    "            \n",
    "            keys = keys.union(set(r.keys()))\n",
    "            for key in keys:\n",
    "                try:\n",
    "                    Dval = D[key]\n",
    "                except:\n",
    "                    Dval = []\n",
    "                try:\n",
    "                    rval = r[key]\n",
    "                except:\n",
    "                    rval = []\n",
    "                D[key] = Dval + rval\n",
    "        return D\n",
    "    \n",
    "'''Breaks df into subdfs, then finds frequencies for subdfs.'''\n",
    "def find_group_word_freqs(whole_df, gb):\n",
    "    whole_df = whole_df.copy()\n",
    "    gb = whole_df.groupby(gb)    \n",
    "    return [(x, find_word_freqs_over_df(gb.get_group(x))) for x in gb.groups]\n",
    "\n",
    "def create_group_name(tuple_or_string):\n",
    "    if type(tuple_or_string) == tuple:\n",
    "        return tuple_or_string[1] + '_' + str(tuple_or_string[0])\n",
    "    else:\n",
    "        return tuple_or_string\n",
    "\n",
    "'''Truncates to most frequent 50 words in each dict.  Puts dicts in '''\n",
    "def package_word_freqs(freqs_list_tuples):\n",
    "    output = {}\n",
    "    for category_name, freqs_list in freqs_list_tuples:\n",
    "        for grouping, freqs in freqs_list:\n",
    "            name = create_group_name(grouping)\n",
    "            freq_list = straighten_list(freqs, 50)\n",
    "            try:\n",
    "                output[category_name][name] = freq_list\n",
    "            except:\n",
    "                output[category_name] = {}\n",
    "                output[category_name][name] = freq_list\n",
    "    return output\n",
    "\n",
    "'''Turns dict k, v set into list and truncates to '''\n",
    "def straighten_list(in_dict, truncate_value):\n",
    "    return sorted(in_dict.items(), key=lambda word_count: -word_count[1])[:truncate_value]\n",
    "\n",
    "'''Turns a dict of occurence count form into a dict of target_value_list form'''\n",
    "def convert_dict(in_dict, value):\n",
    "    in_dict = dict(in_dict) # Make a copy so nothing bad happens\n",
    "    new_dict = {}\n",
    "    for k, v in in_dict.iteritems():\n",
    "        new_dict[k] = [value] * v\n",
    "    return new_dict\n",
    "\n",
    "'''Applies convert dict to each row of the in_df with values according to column'''\n",
    "def apply_list_conversion(in_df, column):\n",
    "    in_df = in_df.copy() # Copy so nothing bad happens\n",
    "    in_df[WORD_COUNTS_LIST_NAME] = (in_df[[WORD_COUNTS_NAME, column]]\n",
    "                                    .apply(lambda x: convert_dict(x[WORD_COUNTS_NAME], x[column]), axis=1))\n",
    "    return in_df\n",
    "\n",
    "def find_specific_word_values(word_list, metric):\n",
    "    D = {}\n",
    "    if metric == 'workload':\n",
    "        for word in word_list:\n",
    "            D[word] = [x for x in ALL_WORKLOAD_LIST[word] if not np.isnan(x)]\n",
    "    return D"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2 = count_words(df)\n",
    "df3 = apply_list_conversion(df2, 'Course_Workload_Rating')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "by_department = find_group_word_freqs(df2, 'department1')\n",
    "by_year = find_group_word_freqs(df2, ['year', 'term'])\n",
    "cdf_data = package_word_freqs([('department', by_department), ('year', by_year)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "overall_counter = find_word_freqs_over_df(df3, 'counter')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('cdf_data.json', 'w') as outfile:\n",
    "        json.dump(cdf_data, outfile)\n",
    "\n",
    "with open('data/all_workload_list.json', 'w') as outfile:\n",
    "        json.dump(ALL_WORKLOAD_LIST, outfile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('data/short_workload_list.json', 'w') as outfile:\n",
    "        json.dump(find_specific_word_values(IMPORTANT_WORDS, 'workload'), outfile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocessing for Enrollment Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# USEFUL FUNCTIONS FOR THIS PART\n",
    "def filter_nans(val):\n",
    "    if val == [] or val == '[]':\n",
    "        return 0\n",
    "    else:\n",
    "        return int(val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "of_interest = df[['department1', 'year', 'term', \n",
    "                  'enrollment', 'name_key1', 'course_title']]\n",
    "of_interest.loc[:, 'enrollment'] = (of_interest['enrollment']\n",
    "                                     .apply(lambda x: filter_nans(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "top_depts = (of_interest.groupby('department1')['enrollment'].sum())\n",
    "top_depts_list = top_depts.astype('int').sort_values()[-30:].index.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "of_more_interest = (of_interest[of_interest['department1']\n",
    "                                .isin(top_depts_list)])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Preparing Enrollment Data for Histogram"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def make_enrollment_json(in_df):\n",
    "    in_df = in_df.copy()\n",
    "    d = {}\n",
    "    for department in in_df['department1'].unique():\n",
    "        filtered = in_df[in_df.department1 == department]['enrollment'].tolist()\n",
    "        d[department] = filtered\n",
    "    return d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('data/enrollment.json', 'w') as outfile:\n",
    "    json.dump(make_enrollment_json(of_more_interest), outfile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department1</th>\n",
       "      <th>year</th>\n",
       "      <th>term</th>\n",
       "      <th>enrollment</th>\n",
       "      <th>name_key1</th>\n",
       "      <th>course_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>59</td>\n",
       "      <td>AESTHINT 13</td>\n",
       "      <td>Cultural Agents</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>48</td>\n",
       "      <td>AESTHINT 15</td>\n",
       "      <td>Elements of Rhetoric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>111</td>\n",
       "      <td>AESTHINT 24</td>\n",
       "      <td>First Nights: Five Performance Premieres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>99</td>\n",
       "      <td>AESTHINT 26</td>\n",
       "      <td>Race, Gender, and Performance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>29</td>\n",
       "      <td>AESTHINT 30</td>\n",
       "      <td>Love In A Dead Language: Classical Indian Lite...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>81</td>\n",
       "      <td>AESTHINT 33</td>\n",
       "      <td>Ancient Fictions: The Ancient Novel in Context</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>9</td>\n",
       "      <td>AESTHINT 35</td>\n",
       "      <td>Forms in Korean Cultural History</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>182</td>\n",
       "      <td>AESTHINT 37</td>\n",
       "      <td>Introduction to the Bible in the Humanities an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>16</td>\n",
       "      <td>AESTHINT 38</td>\n",
       "      <td>The English Language as Literature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>12</td>\n",
       "      <td>AESTHINT 40</td>\n",
       "      <td>Monuments of Islamic Architecture</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AESTHINT</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>12</td>\n",
       "      <td>AESTHINT 49</td>\n",
       "      <td>The Medieval Imagination: Visions, Dreams, and...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>6</td>\n",
       "      <td>AFRAMER 103</td>\n",
       "      <td>From Plantations to Prisons: An Overview of th...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>5</td>\n",
       "      <td>AFRAMER 109</td>\n",
       "      <td>Using Film for Social Change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>58</td>\n",
       "      <td>AFRAMER 10</td>\n",
       "      <td>Introduction to African American Studies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>26</td>\n",
       "      <td>AFRAMER 115</td>\n",
       "      <td>HBO's The Wire and its Contribution to Underst...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>88</td>\n",
       "      <td>AFRAMER 117X</td>\n",
       "      <td>Of Mean Streets and Jungle Fevers: Race, Gende...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>21</td>\n",
       "      <td>AFRAMER 131</td>\n",
       "      <td>African American Literature from the Beginning...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>7</td>\n",
       "      <td>AFRAMER 137</td>\n",
       "      <td>Literature and Its Cultural `Others' - America...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>8</td>\n",
       "      <td>AFRAMER 138</td>\n",
       "      <td>The Child Left Behind: Language, Race, and Edu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>10</td>\n",
       "      <td>AFRAMER 178</td>\n",
       "      <td>Health, Society, and Subjectivity in the Ameri...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>112</td>\n",
       "      <td>AFRAMER 182</td>\n",
       "      <td>From R &amp; B to Neo Soul: Black Popular Music an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>6</td>\n",
       "      <td>AFRAMER 187</td>\n",
       "      <td>African Religions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>36</td>\n",
       "      <td>AFRAMER 20</td>\n",
       "      <td>Introduction to African Languages and Cultures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>6</td>\n",
       "      <td>AFRAMER 301</td>\n",
       "      <td>Graduate Proseminar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>5</td>\n",
       "      <td>AFRAMER 90R.A</td>\n",
       "      <td>Amharic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>1</td>\n",
       "      <td>AFRAMER 90R.B</td>\n",
       "      <td>Bamanakan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>2</td>\n",
       "      <td>AFRAMER 90R.D</td>\n",
       "      <td>Chichewa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>12</td>\n",
       "      <td>AFRAMER 90R.G</td>\n",
       "      <td>Haitian Creole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>1</td>\n",
       "      <td>AFRAMER 90R.H</td>\n",
       "      <td>Hausa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AFRAMER</td>\n",
       "      <td>2011</td>\n",
       "      <td>fall</td>\n",
       "      <td>12</td>\n",
       "      <td>AFRAMER 90R.I</td>\n",
       "      <td>Igbo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14973</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>142</td>\n",
       "      <td>STAT 102</td>\n",
       "      <td>Introduction to Statistics for Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14974</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>336</td>\n",
       "      <td>STAT 104</td>\n",
       "      <td>Introduction to Quantitative Methods for Econo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14975</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>159</td>\n",
       "      <td>STAT 111</td>\n",
       "      <td>Introduction to Theoretical Statistics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14976</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>9</td>\n",
       "      <td>STAT 115</td>\n",
       "      <td>Introduction to Computational Biology and Bioi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14977</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>5</td>\n",
       "      <td>STAT 117</td>\n",
       "      <td>Data Analysis in Modern Biostatistics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14978</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>108</td>\n",
       "      <td>STAT 123</td>\n",
       "      <td>Applied Quantitative Finance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14979</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>96</td>\n",
       "      <td>STAT 139</td>\n",
       "      <td>Statistical Sleuthing Through Linear Models</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14980</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>109</td>\n",
       "      <td>STAT 149</td>\n",
       "      <td>Statistical Sleuthing through Generalized Line...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14981</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>76</td>\n",
       "      <td>STAT 171</td>\n",
       "      <td>Introduction to Stochastic Processes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14982</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>52</td>\n",
       "      <td>STAT 186</td>\n",
       "      <td>Statistical Methods for Evaluating Causal Effects</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14983</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>15</td>\n",
       "      <td>STAT 212</td>\n",
       "      <td>Probability II</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14984</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>33</td>\n",
       "      <td>STAT 213</td>\n",
       "      <td>Statistical Inference II</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14985</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>12</td>\n",
       "      <td>STAT 215</td>\n",
       "      <td>Introduction to Computational Biology and Bioi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14986</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>32</td>\n",
       "      <td>STAT 220</td>\n",
       "      <td>Bayesian Data Analysis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14987</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>18</td>\n",
       "      <td>STAT 300HFRB</td>\n",
       "      <td>Research in Statistics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14988</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>5</td>\n",
       "      <td>STAT 303HFB</td>\n",
       "      <td>The Art and Practice of Teaching Statistics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14989</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>6</td>\n",
       "      <td>STAT 310HFRB</td>\n",
       "      <td>Topics in Astrostatistics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14990</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>5</td>\n",
       "      <td>STAT 316</td>\n",
       "      <td>Big data statistics in genomic and genetic res...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14991</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>24</td>\n",
       "      <td>STAT 365R</td>\n",
       "      <td>Philosophical Foundations of Statistics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14992</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>6</td>\n",
       "      <td>STAT 366HFRB</td>\n",
       "      <td>Introduction to Research</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14993</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>8</td>\n",
       "      <td>STAT 388R</td>\n",
       "      <td>Design and Analysis of Experiments at Scale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14994</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>6</td>\n",
       "      <td>STAT 392</td>\n",
       "      <td>Research Topics in Missing Data, Matching and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14995</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>18</td>\n",
       "      <td>STAT 98</td>\n",
       "      <td>Tutorial - Junior Year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14996</th>\n",
       "      <td>STAT</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>0</td>\n",
       "      <td>STAT 99</td>\n",
       "      <td>Tutorial - Senior Year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15035</th>\n",
       "      <td>US-WORLD</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>45</td>\n",
       "      <td>US-WORLD 19</td>\n",
       "      <td>American Food: A Global History</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15036</th>\n",
       "      <td>US-WORLD</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>20</td>\n",
       "      <td>US-WORLD 28</td>\n",
       "      <td>Slavery/Capitalism/Imperialism: The US in the ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15037</th>\n",
       "      <td>US-WORLD</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>48</td>\n",
       "      <td>US-WORLD 29</td>\n",
       "      <td>Designing the American City: Civic Aspirations...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15038</th>\n",
       "      <td>US-WORLD</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>158</td>\n",
       "      <td>US-WORLD 34</td>\n",
       "      <td>The Civil War from Nat Turner to Birth of a Na...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15039</th>\n",
       "      <td>US-WORLD</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>72</td>\n",
       "      <td>US-WORLD 42</td>\n",
       "      <td>The Great Experiment: A History of the United ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15040</th>\n",
       "      <td>US-WORLD</td>\n",
       "      <td>2017</td>\n",
       "      <td>spring</td>\n",
       "      <td>75</td>\n",
       "      <td>US-WORLD 43</td>\n",
       "      <td>Ancestry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8892 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      department1  year    term  enrollment      name_key1  \\\n",
       "0        AESTHINT  2011    fall          59    AESTHINT 13   \n",
       "1        AESTHINT  2011    fall          48    AESTHINT 15   \n",
       "2        AESTHINT  2011    fall         111    AESTHINT 24   \n",
       "3        AESTHINT  2011    fall          99    AESTHINT 26   \n",
       "4        AESTHINT  2011    fall          29    AESTHINT 30   \n",
       "5        AESTHINT  2011    fall          81    AESTHINT 33   \n",
       "6        AESTHINT  2011    fall           9    AESTHINT 35   \n",
       "7        AESTHINT  2011    fall         182    AESTHINT 37   \n",
       "8        AESTHINT  2011    fall          16    AESTHINT 38   \n",
       "9        AESTHINT  2011    fall          12    AESTHINT 40   \n",
       "10       AESTHINT  2011    fall          12    AESTHINT 49   \n",
       "11        AFRAMER  2011    fall           6    AFRAMER 103   \n",
       "12        AFRAMER  2011    fall           5    AFRAMER 109   \n",
       "13        AFRAMER  2011    fall          58     AFRAMER 10   \n",
       "14        AFRAMER  2011    fall          26    AFRAMER 115   \n",
       "15        AFRAMER  2011    fall          88   AFRAMER 117X   \n",
       "16        AFRAMER  2011    fall          21    AFRAMER 131   \n",
       "17        AFRAMER  2011    fall           7    AFRAMER 137   \n",
       "18        AFRAMER  2011    fall           8    AFRAMER 138   \n",
       "19        AFRAMER  2011    fall          10    AFRAMER 178   \n",
       "20        AFRAMER  2011    fall         112    AFRAMER 182   \n",
       "21        AFRAMER  2011    fall           6    AFRAMER 187   \n",
       "22        AFRAMER  2011    fall          36     AFRAMER 20   \n",
       "23        AFRAMER  2011    fall           6    AFRAMER 301   \n",
       "24        AFRAMER  2011    fall           5  AFRAMER 90R.A   \n",
       "25        AFRAMER  2011    fall           1  AFRAMER 90R.B   \n",
       "26        AFRAMER  2011    fall           2  AFRAMER 90R.D   \n",
       "27        AFRAMER  2011    fall          12  AFRAMER 90R.G   \n",
       "28        AFRAMER  2011    fall           1  AFRAMER 90R.H   \n",
       "29        AFRAMER  2011    fall          12  AFRAMER 90R.I   \n",
       "...           ...   ...     ...         ...            ...   \n",
       "14973        STAT  2017  spring         142       STAT 102   \n",
       "14974        STAT  2017  spring         336       STAT 104   \n",
       "14975        STAT  2017  spring         159       STAT 111   \n",
       "14976        STAT  2017  spring           9       STAT 115   \n",
       "14977        STAT  2017  spring           5       STAT 117   \n",
       "14978        STAT  2017  spring         108       STAT 123   \n",
       "14979        STAT  2017  spring          96       STAT 139   \n",
       "14980        STAT  2017  spring         109       STAT 149   \n",
       "14981        STAT  2017  spring          76       STAT 171   \n",
       "14982        STAT  2017  spring          52       STAT 186   \n",
       "14983        STAT  2017  spring          15       STAT 212   \n",
       "14984        STAT  2017  spring          33       STAT 213   \n",
       "14985        STAT  2017  spring          12       STAT 215   \n",
       "14986        STAT  2017  spring          32       STAT 220   \n",
       "14987        STAT  2017  spring          18   STAT 300HFRB   \n",
       "14988        STAT  2017  spring           5    STAT 303HFB   \n",
       "14989        STAT  2017  spring           6   STAT 310HFRB   \n",
       "14990        STAT  2017  spring           5       STAT 316   \n",
       "14991        STAT  2017  spring          24      STAT 365R   \n",
       "14992        STAT  2017  spring           6   STAT 366HFRB   \n",
       "14993        STAT  2017  spring           8      STAT 388R   \n",
       "14994        STAT  2017  spring           6       STAT 392   \n",
       "14995        STAT  2017  spring          18        STAT 98   \n",
       "14996        STAT  2017  spring           0        STAT 99   \n",
       "15035    US-WORLD  2017  spring          45    US-WORLD 19   \n",
       "15036    US-WORLD  2017  spring          20    US-WORLD 28   \n",
       "15037    US-WORLD  2017  spring          48    US-WORLD 29   \n",
       "15038    US-WORLD  2017  spring         158    US-WORLD 34   \n",
       "15039    US-WORLD  2017  spring          72    US-WORLD 42   \n",
       "15040    US-WORLD  2017  spring          75    US-WORLD 43   \n",
       "\n",
       "                                            course_title  \n",
       "0                                        Cultural Agents  \n",
       "1                                   Elements of Rhetoric  \n",
       "2               First Nights: Five Performance Premieres  \n",
       "3                          Race, Gender, and Performance  \n",
       "4      Love In A Dead Language: Classical Indian Lite...  \n",
       "5         Ancient Fictions: The Ancient Novel in Context  \n",
       "6                       Forms in Korean Cultural History  \n",
       "7      Introduction to the Bible in the Humanities an...  \n",
       "8                     The English Language as Literature  \n",
       "9                      Monuments of Islamic Architecture  \n",
       "10     The Medieval Imagination: Visions, Dreams, and...  \n",
       "11     From Plantations to Prisons: An Overview of th...  \n",
       "12                          Using Film for Social Change  \n",
       "13              Introduction to African American Studies  \n",
       "14     HBO's The Wire and its Contribution to Underst...  \n",
       "15     Of Mean Streets and Jungle Fevers: Race, Gende...  \n",
       "16     African American Literature from the Beginning...  \n",
       "17     Literature and Its Cultural `Others' - America...  \n",
       "18     The Child Left Behind: Language, Race, and Edu...  \n",
       "19     Health, Society, and Subjectivity in the Ameri...  \n",
       "20     From R & B to Neo Soul: Black Popular Music an...  \n",
       "21                                     African Religions  \n",
       "22        Introduction to African Languages and Cultures  \n",
       "23                                   Graduate Proseminar  \n",
       "24                                               Amharic  \n",
       "25                                             Bamanakan  \n",
       "26                                              Chichewa  \n",
       "27                                        Haitian Creole  \n",
       "28                                                 Hausa  \n",
       "29                                                  Igbo  \n",
       "...                                                  ...  \n",
       "14973       Introduction to Statistics for Life Sciences  \n",
       "14974  Introduction to Quantitative Methods for Econo...  \n",
       "14975             Introduction to Theoretical Statistics  \n",
       "14976  Introduction to Computational Biology and Bioi...  \n",
       "14977              Data Analysis in Modern Biostatistics  \n",
       "14978                       Applied Quantitative Finance  \n",
       "14979        Statistical Sleuthing Through Linear Models  \n",
       "14980  Statistical Sleuthing through Generalized Line...  \n",
       "14981               Introduction to Stochastic Processes  \n",
       "14982  Statistical Methods for Evaluating Causal Effects  \n",
       "14983                                     Probability II  \n",
       "14984                           Statistical Inference II  \n",
       "14985  Introduction to Computational Biology and Bioi...  \n",
       "14986                             Bayesian Data Analysis  \n",
       "14987                             Research in Statistics  \n",
       "14988        The Art and Practice of Teaching Statistics  \n",
       "14989                          Topics in Astrostatistics  \n",
       "14990  Big data statistics in genomic and genetic res...  \n",
       "14991            Philosophical Foundations of Statistics  \n",
       "14992                           Introduction to Research  \n",
       "14993        Design and Analysis of Experiments at Scale  \n",
       "14994  Research Topics in Missing Data, Matching and ...  \n",
       "14995                             Tutorial - Junior Year  \n",
       "14996                             Tutorial - Senior Year  \n",
       "15035                    American Food: A Global History  \n",
       "15036  Slavery/Capitalism/Imperialism: The US in the ...  \n",
       "15037  Designing the American City: Civic Aspirations...  \n",
       "15038  The Civil War from Nat Turner to Birth of a Na...  \n",
       "15039  The Great Experiment: A History of the United ...  \n",
       "15040                                           Ancestry  \n",
       "\n",
       "[8892 rows x 6 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "of_more_interest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocessing for Difficulty Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "RELEVANT_COLS = ['Course_Workload_Distribution', \n",
    "                 'Course_Overall_Distribution',\n",
    "                 'Course_Overall_Respondents',\n",
    "                 'Course_Workload_Rating',\n",
    "                 'Course_Overall_Rating',\n",
    "                 'Course_Workload_Respondents',\n",
    "                 'enrollment', \n",
    "                 'name_key1',\n",
    "                 'course_title', \n",
    "                 'year',\n",
    "                 'term',\n",
    "                 'department1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('constants/conventions.json', 'r') as f:\n",
    "    CONVENTIONS =  json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_ith_list_val(i, lst):\n",
    "    if type(lst) == type(np.nan):\n",
    "        return 0\n",
    "    else:\n",
    "        lst = ast.literal_eval(lst)\n",
    "        if len(lst) == 0:\n",
    "            return 0\n",
    "        else:\n",
    "            return lst[i]\n",
    "        \n",
    "def get_list_stat_nice(lst, func, std_multiplier):\n",
    "    big_list = []\n",
    "    for i in range(len(lst)):\n",
    "        big_list += ([i] * lst[i])\n",
    "    if len(big_list) == 1 and func == statistics.stdev: # Need 2+ data pts for variance\n",
    "        return -1\n",
    "    if func == statistics.stdev:\n",
    "        return func(big_list) * std_multiplier\n",
    "    else:\n",
    "        return func(big_list)\n",
    "\n",
    "def get_list_stat(lst, func, std_multiplier):\n",
    "    if type(lst) == type(np.nan):\n",
    "        return -1\n",
    "    else:\n",
    "        lst = ast.literal_eval(lst)\n",
    "        if len(lst) == 0 or reduce(lambda x, y: x+y, lst) == 0:\n",
    "            return -1\n",
    "        else:\n",
    "            return get_list_stat_nice(lst, func, std_multiplier)\n",
    "\n",
    "def get_stat_score(statistic, desired, lst):\n",
    "    if desired == 'median':\n",
    "        intervals = CONVENTIONS[statistic]['intervals']\n",
    "        m = get_list_stat(lst, statistics.median, 1)\n",
    "        if m == -1:\n",
    "            return np.nan\n",
    "        if float(m).is_integer():\n",
    "            i = int(m)\n",
    "            return 0.5 * (intervals[i]['max'] + intervals[i]['min'])\n",
    "        else:\n",
    "            m = int(m-0.5)\n",
    "            return 0.5 * (intervals[m]['max'] + intervals[m + 1]['min'])\n",
    "    elif desired == 'stdev':\n",
    "        std_multiplier = CONVENTIONS[statistic]['std_multiplier']\n",
    "        return get_list_stat(lst, statistics.stdev, std_multiplier)\n",
    "\n",
    "def add_distribution_data(in_df, statistics):\n",
    "    in_df = in_df.copy()\n",
    "    for statistic in statistics:\n",
    "        column = CONVENTIONS[statistic]['distribution']\n",
    "        for i in range(5):\n",
    "            in_df[statistic + '_s' + str(i+1)] = (in_df[column]\n",
    "                .apply(functools.partial(get_ith_list_val, i)))\n",
    "    return in_df\n",
    "\n",
    "def add_mean_data(in_df, statistics):\n",
    "    in_df = in_df.copy()\n",
    "    for statistic in statistics:\n",
    "        in_df[statistic + '_mean'] = in_df[CONVENTIONS[statistic]['mean']]\n",
    "    return in_df\n",
    "\n",
    "def add_respondents_data(in_df, statistics):\n",
    "    in_df = in_df.copy()\n",
    "    for statistic in statistics:\n",
    "        in_df[statistic + '_respondents'] = in_df[CONVENTIONS[statistic]['respondents']]\n",
    "    return in_df\n",
    "\n",
    "def add_instdev_data(in_df, statistics):\n",
    "    in_df = in_df.copy()\n",
    "    for statistic in statistics:\n",
    "        column = CONVENTIONS[statistic]['distribution']\n",
    "        in_df[statistic + '_instdev'] = (in_df[column]\n",
    "                                        .apply(functools.partial(get_stat_score, statistic, 'stdev')))\n",
    "    return in_df\n",
    "\n",
    "\n",
    "def add_additional_data(in_df, statistics):\n",
    "    in_df = in_df.copy()\n",
    "    in_df = add_respondents_data(in_df, statistics)\n",
    "    in_df = add_distribution_data(in_df, statistics)\n",
    "    in_df = add_mean_data(in_df, statistics)\n",
    "    in_df = add_instdev_data(in_df, statistics)\n",
    "    return in_df\n",
    "\n",
    "'''Computes weighted mean'''\n",
    "def compute_grouped_mean(in_df, val_col, weight_col):\n",
    "    in_df = in_df.copy()\n",
    "    return weighted_mean(x=in_df[val_col], w=in_df[weight_col])\n",
    "\n",
    "'''Computes the inter-group std (std of means weighted according to num respondants)'''\n",
    "def compute_grouped_outstd(in_df, mean_col, weight_col):\n",
    "    in_df = in_df.copy()\n",
    "    return np.sqrt(weighted_cov(w=in_df[weight_col], x=in_df[mean_col], y=in_df[mean_col]))\n",
    "\n",
    "def add_grouped_data(in_df, gb, statistics):\n",
    "    in_df = in_df.copy()\n",
    "    distribution_columns = []\n",
    "    for statistic in statistics:\n",
    "        for num in [1, 2, 3, 4, 5]:\n",
    "            distribution_columns += [statistic + '_s' + str(num)]\n",
    "    gp_df = in_df.groupby(gb)[distribution_columns].sum()\n",
    "    for statistic in statistics:\n",
    "        gp_df = (gp_df.join(in_df.groupby(gb)[[statistic + '_mean', statistic + '_respondents']]\n",
    "                           .apply(compute_grouped_mean, statistic + '_mean', statistic + '_respondents')\n",
    "                           .to_frame(statistic + '_mean'))\n",
    "                 .join(in_df.groupby(gb)[[statistic + '_instdev', statistic + '_respondents']]\n",
    "                       .apply(compute_grouped_mean, statistic + '_instdev', statistic + '_respondents')\n",
    "                       .to_frame(statistic + '_instdev'))\n",
    "                 .join(in_df.groupby(gb)[[statistic + '_mean', statistic + '_respondents']]\n",
    "                       .apply(compute_grouped_outstd, statistic + '_mean', statistic + '_respondents')\n",
    "                       .to_frame(statistic + '_outstdev')))\n",
    "    return gp_df\n",
    "\n",
    "def make_all_stats_for_year(in_df, year):\n",
    "    in_df = in_df.copy()\n",
    "    relevant_df = in_df[in_df.year == year][RELEVANT_COLS]\n",
    "    basic_stats_df = add_additional_data(relevant_df, ['workload', 'overall'])\n",
    "    grouped_stats_df = add_grouped_data(basic_stats_df, 'department1', ['workload', 'overall'])\n",
    "    grouped_stats_df = grouped_stats_df[grouped_stats_df.index.isin(top_depts_list)]\n",
    "    grouped_stats_df['year'] = year\n",
    "    return grouped_stats_df.reset_index().set_index(['year', 'department1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "all_stats = pd.concat(make_all_stats_for_year(df, year) for year in df.year.unique().tolist())\n",
    "all_stats.to_csv('data/statistics.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}