clean_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import json\n",
    "import re\n",
    "import math\n",
    "import codecs\n",
    "from IPython.core.display import display"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Info\n",
    "\n",
    "This code was developed by Russell Pekala and Sara Valente in Fall '17 to clean and package raw scraped data from the Harvard Q Guide.  It is in no way perfect - it takes only a minute to completely run so performance optimization was not a priority.  It is supposed to be clear and easy to update/customize if we (or someone else) scrapes the Q-Guide again."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Instructions:** \n",
    "* Execute the cells in order.  If looking at only several semesters, then comment out the ones you don't want below. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "semesters = [('fall', 2006),\n",
    "             ('spring', 2007),\n",
    "             ('fall', 2007),\n",
    "             ('spring', 2008),\n",
    "             ('fall', 2008),\n",
    "             ('spring', 2009),\n",
    "             ('fall', 2009),\n",
    "             ('spring', 2010),\n",
    "             ('fall', 2010),\n",
    "             ('spring', 2011),\n",
    "             ('fall', 2011),\n",
    "             ('spring', 2012),\n",
    "             ('fall', 2012),\n",
    "             ('spring', 2013),\n",
    "             ('fall', 2013),\n",
    "             ('spring', 2014),\n",
    "             ('fall', 2014),\n",
    "             ('spring', 2015),\n",
    "             ('fall', 2015),\n",
    "             ('spring', 2016),\n",
    "             ('fall', 2016),\n",
    "             ('spring', 2017),\n",
    "             ('fall', 2017)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Columns we won't analyze\n",
    "drop_cols = ['view_actual_comments', 'view_comments-href', 'view_comments', \n",
    "             'class-href', 'view_instructor', 'view_instructor-href', 'exists2instructors?',\n",
    "             'view_actual_comments-href']\n",
    "\n",
    "# Make up for gaps in our scraping convention by renaming.\n",
    "rename_cols = {\n",
    "    # Main cols.  Python thinks the word class means something different \n",
    "    'all': 'all_course',\n",
    "    'class': 'course',\n",
    "    \n",
    "    # Cols related to the course overall\n",
    "    'overall_img-src': 'course_img1', # From div 1\n",
    "    'materials_img-src': 'course_img2',\n",
    "    'assignments_img-src': 'course_img3',\n",
    "    'feedback_img-src': 'course_img4',\n",
    "    'section_img-src': 'course_img5', # Still from div 1\n",
    "    'workload_img-src': 'course_img6', # From div 2\n",
    "    'recommend_img-src': 'course_img7', # From div 3\n",
    "    'money_img-src': 'course_img8', # From div 4\n",
    "    \n",
    "    # Not sure how this changes by year but it should be generalized anyway\n",
    "    'elective_img-src': 'reason1',\n",
    "    'concentration_img-src': 'reason2', \n",
    "    'secondary_img-src': 'reason3', \n",
    "    'gened_img-src': 'reason4', \n",
    "    'expos_img-src': 'reason5', \n",
    "    'foreign_img-src': 'reason6', \n",
    "    'premed_img-src': 'reason7',\n",
    "    \n",
    "    # For the professor\n",
    "    'overall_img_instructor-src': 'instructor_img1',\n",
    "    'lectures_img_instructor-src': 'instructor_img2',\n",
    "    'accessible_img_instructor-src': 'instructor_img3', \n",
    "    'enthusiasm_img_instructor-src': 'instructor_img4',\n",
    "    'discussion_img_instructor-src': 'instructor_img5', \n",
    "    'feedback_img_instructor-src': 'instructor_img6',\n",
    "    'assignments_img_instructor-src': 'instructor_img7'\n",
    "}\n",
    "\n",
    "# We will immediately convert instances of key to value for less messiness\n",
    "translation = {\n",
    "    # For overall course questions\n",
    "    'Course Overall': 'Overall',\n",
    "    'Workload (hours per week)': 'Workload',\n",
    "    'Met Gen Ed Goals': 'Goals',\n",
    "    'Would You Recommend': 'Recommend',\n",
    "    'Money spent on course': 'Money',\n",
    "\n",
    "    # Translations for metrics in teacher evaluation\n",
    "    'Returns Assignments in Timely Fashion': 'Return',\n",
    "    'Gives Useful Feedback': 'Feedback',\n",
    "    'Facilitates Discussion & Encourages Participation': 'Participation',\n",
    "    'Generates Enthusiasm': 'Enthusiasm',\n",
    "    'Accessible Outside Class': 'Accessible',\n",
    "    'Effective Lectures or Presentations': 'Lectures',\n",
    "    'Instructor Overall': 'Overall'\n",
    "}\n",
    "\n",
    "# Columns we DO want to analyze.\n",
    "whitelists = {'course': [r'Overall', r'Materials', r'Assignments', r'Feedback', \n",
    "                         r'Section', r'Workload', r'Goals', r'Recommend', r'Money'],\n",
    "              'instructor': [r'Return', r'Feedback', r'Participation', r'Enthusiasm', \n",
    "                             r'Accessible', r'Lectures', r'Overall']}\n",
    "\n",
    "img_sources = (['course_img' + str(i) for i in range(1, 9)] + \n",
    "               ['instructor_img' + str(i) for i in range(1, 8)] +\n",
    "               ['reason' + str(i) for i in range(1, 8)])\n",
    "\n",
    "DATA_PATH = 'raw_data/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def read_in_raw_data(year, term):\n",
    "    path = DATA_PATH + term + '_' + str(year) + '.csv'\n",
    "    return (pd.read_csv(path, na_values=['null'])\n",
    "            .drop(drop_cols, 1)\n",
    "            .rename(columns=rename_cols)\n",
    "            .assign(year=year)\n",
    "            .assign(term=term))\n",
    "\n",
    "# Takes a df with two rows describing the same course and merges rows to avoid nans\n",
    "def fix_duplicate_courses(sel):\n",
    "    final = sel.iloc[0]\n",
    "    for i in range(1, len(sel)):\n",
    "        final = final.fillna(sel.iloc[i])\n",
    "    return final\n",
    "\n",
    "# Processes summary stats into lists.\n",
    "def clean_summary(s):\n",
    "    if type(s) == type(np.nan):\n",
    "        return []\n",
    "    else:\n",
    "        n = remove_bad_whitespace(s).split('\\n')\n",
    "        return [int(n[1].strip()), int(n[3].strip())]\n",
    "\n",
    "# Gets int list of histogram for a particular graph.\n",
    "def clean_img(link):\n",
    "    if type(link) == type(np.nan):\n",
    "        return []\n",
    "    else:\n",
    "        # Fixes a weird bug where zero becomes blank\n",
    "        link = (link.replace('--', '-0-')\n",
    "                .replace('--', '-0-')\n",
    "                .replace('-.', '-0.')) # Fix 'histobar---1-2-3' like issues\n",
    "        lst = (link.split('/')[-1:][0]\n",
    "               .replace('histobar-', '') # For the histogram charts\n",
    "               .replace('barPercentage-', '') # For the bar charts\n",
    "               .replace('.png', '')\n",
    "               .split('-'))\n",
    "        return [int(x) for x in lst]\n",
    "\n",
    "def remove_bad_whitespace(s):\n",
    "    return s.replace('\\xc2', '').replace('\\xa0', '')\n",
    "\n",
    "def provide_shorthands(s):\n",
    "    for k, v in translation.iteritems():\n",
    "        s = s.replace(k, v)\n",
    "    return s\n",
    "\n",
    "def clean_overall(s, whitelist):\n",
    "    if type(s) == type(np.nan):\n",
    "        return []\n",
    "    else:\n",
    "        s = provide_shorthands(remove_bad_whitespace(s)).replace('\\n', '').replace(' ','')\n",
    "        # Fixes issue with skipping over data if no responses to question\n",
    "        s = re.sub('(?<=[a-z])0(?=[A-Z])', '00.0', s)\n",
    "        # Last three characters must be decimal average.  Rest to be split to name, #respondents\n",
    "        f = lambda x:  re.split('(\\d+)', x[:-3])[:2] + [x[-3:]]\n",
    "        # Serious problems with backslash\n",
    "        pats = [(whitie + '[0-9]+[^0-9][0-9]') for whitie in whitelist]\n",
    "        found = [itm for lst in [re.findall(pat, s) for pat in pats] for itm in lst]\n",
    "        return map(f, found)\n",
    "\n",
    "def clean_comment(s):\n",
    "    if type(s) == type(np.nan):\n",
    "        return []\n",
    "    else:\n",
    "        # Engineered to just work.\n",
    "        filtrated = filter(lambda x: x != '', map(lambda x: x[31:], s.split('\\n')[9:-7]))\n",
    "        return map(lambda x: x.strip(), filtrated)\n",
    "\n",
    "def clean_instructor(s):\n",
    "    # First extract first and last name\n",
    "    if type(s) == type(np.nan) or s == 'No data available':\n",
    "        return []\n",
    "    else:\n",
    "        s = provide_shorthands(s)\n",
    "        # Some names end with ) or .\n",
    "        matches = re.findall('\\A.*\\w\\.*\\)*(?=Overall)', s)\n",
    "        if len(matches) == 0:\n",
    "            raw_names = '\",\"'\n",
    "        else:\n",
    "            raw_names = matches[0]\n",
    "        names = raw_names.replace(' ', '').split(',') # first and last names\n",
    "        rest = clean_overall(s[len(raw_names):], whitelists['instructor'])\n",
    "        return [names, rest]\n",
    "    \n",
    "# Gets all distribution information and puts it in one dict.\n",
    "def make_img_dict(row):\n",
    "    d = {}\n",
    "    for img_link in img_sources:\n",
    "        d[img_link] = clean_img(row[img_link])\n",
    "    row['img_dict'] = d\n",
    "    return row\n",
    "\n",
    "def print_debug(d):\n",
    "    print d\n",
    "    return d\n",
    "\n",
    "def add_instructor_info(df):\n",
    "    df = df.copy()\n",
    "    # Add instructor first and last name.\n",
    "    df['instructor_first_name'] = (df['all_instructor']\n",
    "                                   .apply(lambda x: '' if x == [] else x[0][1]))\n",
    "    df['instructor_last_name'] = (df['all_instructor']\n",
    "                                   .apply(lambda x: '' if x == [] else x[0][0]))\n",
    "    # Simplify the data in all_instructor column\n",
    "    df['all_instructor'] = (df['all_instructor']\n",
    "                            .apply(lambda x: [] if x == [] else x[1]))\n",
    "    return df\n",
    "\n",
    "def add_number_of_comments(df):\n",
    "    df = df.copy()\n",
    "    invalid_cond = df['comments'] == [] or df['comments'] == np.nan\n",
    "    df['number_of_comments'] = 0 if invalid_cond else len(df['comments'])\n",
    "    return df\n",
    "\n",
    "def add_summary_stat_info(df):\n",
    "    df['enrollment'] = df['summary_stats'].apply(lambda x: [] if x==[] else x[0])\n",
    "    df['responses'] = df['summary_stats'].apply(lambda x: [] if x==[] else x[1])\n",
    "    return df.drop('summary_stats', 1)\n",
    "\n",
    "# Takes column with list of lists in each entry and makes dict in each entry w/same data.\n",
    "def convert_to_dict(frame, src): # src is either course or instructor\n",
    "    list_of_lists = frame['all_' + src]\n",
    "    img_dict = frame['img_dict']\n",
    "    limit = len(whitelists[src])\n",
    "    d = {}\n",
    "    if type(list_of_lists) == type(np.nan):\n",
    "        return d\n",
    "    else:\n",
    "        for idx, lst in enumerate(list_of_lists):\n",
    "            if len(lst) == 3: # There is some bad data here.  No time to figure it out.\n",
    "                name = lst[0]\n",
    "                d[name + '_Respondents'] = int(lst[1])\n",
    "                d[name + '_Rating'] = float(lst[2])\n",
    "                # Because some pages have more than 8 things...they get cut off\n",
    "                if (idx < limit) & (idx != 8): # Without this redundancy it crashes.  Python has bugs.\n",
    "                    d[name + '_Distribution'] = img_dict[src + '_img' + str(idx + 1)]\n",
    "        return d\n",
    "    \n",
    "def add_focus_columns(df):\n",
    "    df = df.copy()\n",
    "    for datum in ['_Rating', '_Respondents', '_Distribution']:\n",
    "        for col in whitelists['course']:\n",
    "            df['Course_' + \n",
    "               col + datum] = (df['all_course']\n",
    "                                .apply(lambda d: (d[col + datum] \n",
    "                                                  if col + datum in d.keys()\n",
    "                                                  else np.nan)))\n",
    "        for col in whitelists['instructor']:\n",
    "            df['Instructor_' + \n",
    "               col + datum] = (df['all_instructor']\n",
    "                                .apply(lambda d: (d[col + datum] \n",
    "                                                  if col + datum in d.keys()\n",
    "                                                  else np.nan)))\n",
    "    return df\n",
    "\n",
    "def sum_careful(x):\n",
    "    '''Sum a list but return nan if the list was empty (something python should do but doesn't)'''\n",
    "    return np.nan if (x==[] or type(x) == type(np.nan)) else sum(x)\n",
    "\n",
    "def separate_course_name_number(df):\n",
    "    # Get rid of straight duplicates then collect non-nan values to a single column\n",
    "    df = df.drop_duplicates()\n",
    "    df = df.groupby('course').apply(fix_duplicate_courses).reset_index(drop=True)\n",
    "    # Get info on department and course number/title separated.  Difficult because some courses have two depts\n",
    "    split_colon = pd.DataFrame(df['course'].str.split(':', 1).tolist(), \n",
    "                               columns=['to_be_split_again', 'title'])\n",
    "    # strip removes leading whitespace.\n",
    "    df.loc[:, 'course_title'] = split_colon['title'].str.strip().values \n",
    "    # Splitting in case there are more than one departments offering the course. \n",
    "    list_of_lists = split_colon['to_be_split_again'].str.split('/', 1).tolist()\n",
    "    split_courses = pd.DataFrame([x + [' '] if len(x) < 2 else x for x in list_of_lists], \n",
    "                                 columns=['col1', 'col2'])\n",
    "    split_dept1 = pd.DataFrame(split_courses['col1'].str.split(' ', 1).tolist(), \n",
    "                              columns=['department1', 'course_number1'])\n",
    "    split_dept2 = pd.DataFrame(split_courses['col2'].str.split(' ', 1).tolist(), \n",
    "                              columns=['department2', 'course_number2'])\n",
    "    df.loc[:, 'course_number1'] = split_dept1['course_number1'].values\n",
    "    df.loc[:, 'department1'] = split_dept1['department1'].values\n",
    "    df.loc[:, 'course_number2'] = split_dept2['course_number2'].values\n",
    "    df.loc[:, 'department2'] = split_dept2['department2'].values\n",
    "    # Make a course identifier being name and department.  Easier database stuff later on.  - RP\n",
    "    df.loc[:, 'name_key1'] = (df['department1'] + ' ' + df['course_number1']).values\n",
    "    return df\n",
    "\n",
    "def fix_workload(df):\n",
    "    '''Fixes problem whereby workloads of more than 10 hours are parsed incorrectly.  Since we matched\n",
    "       on d.d we only get one digit by decimal and other goes (incorrectly) to respondents.  This fixes.'''\n",
    "    df = df.copy()\n",
    "    condition = ((df['Course_Workload_Distribution'].apply(sum_careful) != \n",
    "                  df['Course_Workload_Respondents']))\n",
    "    df['Course_Workload_Rating'] = np.where(condition, \n",
    "                                             (df['Course_Workload_Rating'] + \n",
    "                                             (10 * df['Course_Workload_Respondents']\n",
    "                                              .fillna(0).astype(int).apply(lambda x: x % 10))),\n",
    "                                             df['Course_Workload_Rating'])\n",
    "    df['Course_Workload_Respondents'] = np.where(condition, \n",
    "                                             (df['Course_Workload_Respondents']\n",
    "                                              .apply(lambda x: math.floor(x / 10))),\n",
    "                                             df['Course_Workload_Respondents'])\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def wrangle(term, year):\n",
    "    print 'Wrangling data for ' + term + ' ' + str(year)\n",
    "    # Read in raw data and parse course column for alternate courses. \n",
    "    df = read_in_raw_data(year, term)\n",
    "    df = separate_course_name_number(df)\n",
    "\n",
    "    # Process our complicated columns\n",
    "    df['all_course'] = df['all_course'].apply(clean_overall, args=(whitelists['course'], ))\n",
    "    df['all_instructor'] = df['all_instructor'].apply(clean_instructor)\n",
    "    df['comments'] = df['comments'].apply(clean_comment)\n",
    "    df['summary_stats'] = df['summary_stats'].apply(clean_summary)\n",
    "\n",
    "    # Let's simplify things considerably.  No reason for all those columns. \n",
    "    df = df.apply(make_img_dict, axis=1).drop(img_sources, 1)\n",
    "\n",
    "    # Now take consolodated data and put in columns\n",
    "    df = add_instructor_info(df)\n",
    "    df = add_summary_stat_info(df)\n",
    "    df = df.apply(add_number_of_comments, axis=1)\n",
    "    df['all_course'] = df.apply(convert_to_dict, axis=1, args=('course',))\n",
    "    df['all_instructor'] = df.apply(convert_to_dict, axis=1, args=('instructor',))\n",
    "    df = add_focus_columns(df)\n",
    "    df = fix_workload(df)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def aggregate_and_trim():\n",
    "    df = wrangle(*semesters[0])\n",
    "    for semester in semesters[1:]:\n",
    "        df = df.append(wrangle(*semester))\n",
    "    # Parsed all info from these already.\n",
    "    df = df.drop(['img_dict', 'all_instructor', 'all_course'], 1)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wrangling data for fall 2006\n",
      "Wrangling data for spring 2007\n",
      "Wrangling data for fall 2007\n",
      "Wrangling data for spring 2008\n",
      "Wrangling data for fall 2008\n",
      "Wrangling data for spring 2009\n",
      "Wrangling data for fall 2009\n",
      "Wrangling data for spring 2010\n",
      "Wrangling data for fall 2010\n",
      "Wrangling data for spring 2011\n",
      "Wrangling data for fall 2011\n",
      "Wrangling data for spring 2012\n",
      "Wrangling data for fall 2012\n",
      "Wrangling data for spring 2013\n",
      "Wrangling data for fall 2013\n",
      "Wrangling data for spring 2014\n",
      "Wrangling data for fall 2014\n",
      "Wrangling data for spring 2015\n",
      "Wrangling data for fall 2015\n",
      "Wrangling data for spring 2016\n",
      "Wrangling data for fall 2016\n",
      "Wrangling data for spring 2017\n",
      "Wrangling data for fall 2017\n"
     ]
    }
   ],
   "source": [
    "final = aggregate_and_trim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year\n",
       "2006    1001\n",
       "2007    2042\n",
       "2008    2065\n",
       "2009    2188\n",
       "2010    2351\n",
       "2011    2412\n",
       "2012    2464\n",
       "2013    2565\n",
       "2014    2570\n",
       "2015    2515\n",
       "2016    2518\n",
       "2017    2520\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.groupby('year')['year'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>web-scraper-order</th>\n",
       "      <th>web-scraper-start-url</th>\n",
       "      <th>course</th>\n",
       "      <th>comments</th>\n",
       "      <th>year</th>\n",
       "      <th>term</th>\n",
       "      <th>course_title</th>\n",
       "      <th>course_number1</th>\n",
       "      <th>department1</th>\n",
       "      <th>course_number2</th>\n",
       "      <th>...</th>\n",
       "      <th>Course_Goals_Distribution</th>\n",
       "      <th>Course_Recommend_Distribution</th>\n",
       "      <th>Course_Money_Distribution</th>\n",
       "      <th>Instructor_Return_Distribution</th>\n",
       "      <th>Instructor_Feedback_Distribution</th>\n",
       "      <th>Instructor_Participation_Distribution</th>\n",
       "      <th>Instructor_Enthusiasm_Distribution</th>\n",
       "      <th>Instructor_Accessible_Distribution</th>\n",
       "      <th>Instructor_Lectures_Distribution</th>\n",
       "      <th>Instructor_Overall_Distribution</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1522891683-18234</td>\n",
       "      <td>https://course-evaluation-reports.fas.harvard....</td>\n",
       "      <td>AFRAMER 112:  Black Humor: Performance Art and...</td>\n",
       "      <td>[]</td>\n",
       "      <td>2006</td>\n",
       "      <td>fall</td>\n",
       "      <td>Black Humor: Performance Art and Literature</td>\n",
       "      <td>112</td>\n",
       "      <td>AFRAMER</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[3, 1, 0, 2, 15]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 1, 2, 2, 15]</td>\n",
       "      <td>[0, 1, 2, 7, 10]</td>\n",
       "      <td>[0, 0, 1, 3, 16]</td>\n",
       "      <td>[0, 0, 3, 1, 16]</td>\n",
       "      <td>[0, 0, 2, 3, 14]</td>\n",
       "      <td>[1, 0, 1, 4, 3]</td>\n",
       "      <td>[0, 0, 1, 3, 3]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1522891674-18223</td>\n",
       "      <td>https://course-evaluation-reports.fas.harvard....</td>\n",
       "      <td>AFRAMER 113:  Fictions of Race, Facts of Racis...</td>\n",
       "      <td>[]</td>\n",
       "      <td>2006</td>\n",
       "      <td>fall</td>\n",
       "      <td>Fictions of Race, Facts of Racism: Perspective...</td>\n",
       "      <td>113</td>\n",
       "      <td>AFRAMER</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1522891670-18217</td>\n",
       "      <td>https://course-evaluation-reports.fas.harvard....</td>\n",
       "      <td>AFRAMER 116:  Autobiography and Literary Imagi...</td>\n",
       "      <td>[]</td>\n",
       "      <td>2006</td>\n",
       "      <td>fall</td>\n",
       "      <td>Autobiography and Literary Imagination</td>\n",
       "      <td>116</td>\n",
       "      <td>AFRAMER</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0, 0, 1, 3]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0, 0, 2, 2]</td>\n",
       "      <td>[0, 1, 0, 1, 2]</td>\n",
       "      <td>[0, 0, 1, 2, 1]</td>\n",
       "      <td>[0, 0, 0, 1, 3]</td>\n",
       "      <td>[0, 1, 0, 0, 3]</td>\n",
       "      <td>[0, 1, 0, 0, 2]</td>\n",
       "      <td>[0, 1, 0, 0, 2]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1522891657-18200</td>\n",
       "      <td>https://course-evaluation-reports.fas.harvard....</td>\n",
       "      <td>AFRAMER 175:  Introduction to African Music</td>\n",
       "      <td>[]</td>\n",
       "      <td>2006</td>\n",
       "      <td>fall</td>\n",
       "      <td>Introduction to African Music</td>\n",
       "      <td>175</td>\n",
       "      <td>AFRAMER</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0, 0, 4, 9]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0, 1, 3, 9]</td>\n",
       "      <td>[0, 0, 2, 3, 7]</td>\n",
       "      <td>[0, 1, 5, 1, 2]</td>\n",
       "      <td>[0, 0, 1, 4, 8]</td>\n",
       "      <td>[0, 1, 0, 3, 6]</td>\n",
       "      <td>[0, 2, 2, 6, 2]</td>\n",
       "      <td>[0, 1, 3, 4, 2]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1522891495-18013</td>\n",
       "      <td>https://course-evaluation-reports.fas.harvard....</td>\n",
       "      <td>AFRAMER 190:  African-American Families: Polit...</td>\n",
       "      <td>[]</td>\n",
       "      <td>2006</td>\n",
       "      <td>fall</td>\n",
       "      <td>African-American Families: Politics, Culture, ...</td>\n",
       "      <td>190</td>\n",
       "      <td>AFRAMER</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0, 1, 2, 4]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0, 1, 2, 4]</td>\n",
       "      <td>[0, 0, 1, 4, 2]</td>\n",
       "      <td>[0, 1, 1, 0, 4]</td>\n",
       "      <td>[0, 0, 1, 3, 3]</td>\n",
       "      <td>[0, 0, 1, 1, 5]</td>\n",
       "      <td>[0, 0, 1, 2, 3]</td>\n",
       "      <td>[0, 0, 1, 1, 4]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 65 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  web-scraper-order                              web-scraper-start-url  \\\n",
       "0  1522891683-18234  https://course-evaluation-reports.fas.harvard....   \n",
       "1  1522891674-18223  https://course-evaluation-reports.fas.harvard....   \n",
       "2  1522891670-18217  https://course-evaluation-reports.fas.harvard....   \n",
       "3  1522891657-18200  https://course-evaluation-reports.fas.harvard....   \n",
       "4  1522891495-18013  https://course-evaluation-reports.fas.harvard....   \n",
       "\n",
       "                                              course comments  year  term  \\\n",
       "0  AFRAMER 112:  Black Humor: Performance Art and...       []  2006  fall   \n",
       "1  AFRAMER 113:  Fictions of Race, Facts of Racis...       []  2006  fall   \n",
       "2  AFRAMER 116:  Autobiography and Literary Imagi...       []  2006  fall   \n",
       "3        AFRAMER 175:  Introduction to African Music       []  2006  fall   \n",
       "4  AFRAMER 190:  African-American Families: Polit...       []  2006  fall   \n",
       "\n",
       "                                        course_title course_number1  \\\n",
       "0        Black Humor: Performance Art and Literature            112   \n",
       "1  Fictions of Race, Facts of Racism: Perspective...            113   \n",
       "2             Autobiography and Literary Imagination            116   \n",
       "3                      Introduction to African Music            175   \n",
       "4  African-American Families: Politics, Culture, ...            190   \n",
       "\n",
       "  department1 course_number2               ...                 \\\n",
       "0     AFRAMER                              ...                  \n",
       "1     AFRAMER                              ...                  \n",
       "2     AFRAMER                              ...                  \n",
       "3     AFRAMER                              ...                  \n",
       "4     AFRAMER                              ...                  \n",
       "\n",
       "  Course_Goals_Distribution Course_Recommend_Distribution  \\\n",
       "0                       NaN              [3, 1, 0, 2, 15]   \n",
       "1                       NaN                           NaN   \n",
       "2                       NaN               [0, 0, 0, 1, 3]   \n",
       "3                       NaN               [0, 0, 0, 4, 9]   \n",
       "4                       NaN               [0, 0, 1, 2, 4]   \n",
       "\n",
       "  Course_Money_Distribution Instructor_Return_Distribution  \\\n",
       "0                       NaN               [0, 1, 2, 2, 15]   \n",
       "1                       NaN                            NaN   \n",
       "2                       NaN                [0, 0, 0, 2, 2]   \n",
       "3                       NaN                [0, 0, 1, 3, 9]   \n",
       "4                       NaN                [0, 0, 1, 2, 4]   \n",
       "\n",
       "  Instructor_Feedback_Distribution Instructor_Participation_Distribution  \\\n",
       "0                 [0, 1, 2, 7, 10]                      [0, 0, 1, 3, 16]   \n",
       "1                              NaN                                   NaN   \n",
       "2                  [0, 1, 0, 1, 2]                       [0, 0, 1, 2, 1]   \n",
       "3                  [0, 0, 2, 3, 7]                       [0, 1, 5, 1, 2]   \n",
       "4                  [0, 0, 1, 4, 2]                       [0, 1, 1, 0, 4]   \n",
       "\n",
       "   Instructor_Enthusiasm_Distribution  Instructor_Accessible_Distribution  \\\n",
       "0                    [0, 0, 3, 1, 16]                    [0, 0, 2, 3, 14]   \n",
       "1                                 NaN                                 NaN   \n",
       "2                     [0, 0, 0, 1, 3]                     [0, 1, 0, 0, 3]   \n",
       "3                     [0, 0, 1, 4, 8]                     [0, 1, 0, 3, 6]   \n",
       "4                     [0, 0, 1, 3, 3]                     [0, 0, 1, 1, 5]   \n",
       "\n",
       "   Instructor_Lectures_Distribution  Instructor_Overall_Distribution  \n",
       "0                   [1, 0, 1, 4, 3]                  [0, 0, 1, 3, 3]  \n",
       "1                               NaN                              NaN  \n",
       "2                   [0, 1, 0, 0, 2]                  [0, 1, 0, 0, 2]  \n",
       "3                   [0, 2, 2, 6, 2]                  [0, 1, 3, 4, 2]  \n",
       "4                   [0, 0, 1, 2, 3]                  [0, 0, 1, 1, 4]  \n",
       "\n",
       "[5 rows x 65 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "final.to_csv('raw_data/final.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Making a list for the website of each class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('final.csv')\n",
    "df['dept_num'] = df['department1'] + ' ' + df['course_number1']\n",
    "lst = list(set(df['dept_num']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('data/course_list.json', 'wb') as outfile:\n",
    "    json.dump(lst, outfile)"
   ]
  }
 ],
 "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
}