scrape_q.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "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 September 2017 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. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Constants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "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",
    "semesters = [('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",
    "DATA_PATH = 'data/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "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": 29,
   "metadata": {},
   "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": 30,
   "metadata": {},
   "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": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "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"
     ]
    }
   ],
   "source": [
    "final = aggregate_and_trim()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "final.to_csv('final.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Making a list for the website of each class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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": {},
   "outputs": [],
   "source": [
    "with open('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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}