2_1_2018_Foong_Education Data Formatter.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import httplib2\n",
    "import oauth2\n",
    "import urllib3\n",
    "import types\n",
    "import re\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from gender_detector import GenderDetector \n",
    "import psycopg2, psycopg2.extras\n",
    "from causalinference import CausalModel\n",
    "from causalinference.utils import random_data\n",
    "import httplib\n",
    "import base64\n",
    "import json # For Microsoft Face API\n",
    "import urllib as urllib # For Microsoft Face API\n",
    "import time \n",
    "import csv\n",
    "import datetime \n",
    "\n",
    "\n",
    "class UpworkDataFormatter:\n",
    "    \n",
    "    def __init__(self):\n",
    "        # Settings\n",
    "        self.edu_data_file_name = './csv_files/edu_2017_12_12_upwork_analysis_unitedstates_allskills.csv' # Filename for all data\n",
    "        self.edu_data_log_file_name = './log_files/edu_log_upwork_data_analysis_2017_12_12_unitedstates_allskills.txt'\n",
    "        \n",
    "        # Write a log\n",
    "        self.log = open(self.edu_data_log_file_name, 'a')\n",
    "        self.log.write(\"We have started analyzing data!\" + \"\\n\")\n",
    "        self.log.flush()\n",
    "\n",
    "        # Connect to the database \n",
    "        self.conn = psycopg2.connect(\"dbname=eureka01\")\n",
    "        self.cur = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)\n",
    "        psycopg2.extensions.register_adapter(dict, psycopg2.extras.Json)\n",
    "        \n",
    "        # Get detailed_info from workers in our database\n",
    "        self.cur.execute(\"SELECT detailed_info FROM upwork_unitedstates_allskills_2017_12_12;\")\n",
    "        \n",
    "        # Initialize values \n",
    "        self.user_count = 1\n",
    "        self.education_levels = [\"Bachelor or Higher\", \"Some College or Associate\",\n",
    "                                 \"High School\", \"Less Than High School\", \"Other\", \"None\"]\n",
    "    \n",
    "    def trial(self):\n",
    "        print \"hello world\"\n",
    "    def show_education(self):\n",
    "        print \"in Show education\"\n",
    "        with open(self.edu_data_file_name, 'w') as csvfile:\n",
    "            fieldnames = ['user_count','education','education_id']\n",
    "            writer = csv.DictWriter(csvfile, fieldnames=fieldnames)\n",
    "            writer.writeheader()\n",
    "            \n",
    "            for user in self.cur:\n",
    "                try: \n",
    "                    print \"This is the user count: {0}\".format(self.user_count)\n",
    "                    user_count = self.user_count\n",
    "                    education_list = self.return_edu_experience(user)\n",
    "                    highest_education = self.find_highest_education(education_list)\n",
    "                    education_id = self.education_levels.index(highest_education)\n",
    "                    \n",
    "                    writer.writerow({'user_count': self.user_count, 'education': highest_education, \n",
    "                                 'education_id': education_id})\n",
    "                    print \"Finished writing data for {0}\".format(self.user_count)\n",
    "                \n",
    "                except:\n",
    "                    print \"Ran into some error at user {0}\".format(self.user_count)\n",
    "                    \n",
    "                    writer.writerow({'user_count': self.user_count, 'education': \"error\", \n",
    "                                 'education_id': \"error\"})\n",
    "                \n",
    "                self.user_count += 1\n",
    "                \n",
    "                    \n",
    "    def find_highest_education(self, edu_list):\n",
    "        for item in edu_list:\n",
    "            for level in self.education_levels:\n",
    "                if item == level:\n",
    "                    return level\n",
    "            else:\n",
    "                return \"None\"\n",
    "                \n",
    "    def return_edu_experience(self, user):\n",
    "        all_education = []\n",
    "        try: \n",
    "            ed_history = user[0][\"education\"][\"institution\"]\n",
    "            \n",
    "            if (ed_history == \"\"):\n",
    "                print \"No ed history\"\n",
    "                return all_education\n",
    "            \n",
    "            if (type(ed_history) == dict):\n",
    "                print \"Found a single ed history\"\n",
    "                degree = str(ed_history[\"ed_degree\"])\n",
    "                school = str(ed_history[\"ed_school\"])\n",
    "                comment = str(ed_history[\"ed_comment\"])\n",
    "                \n",
    "                print \"Degree: {0}\".format(degree)\n",
    "                print \"School: {0}\".format(school)\n",
    "                print \"Comment: {0}\".format(comment)\n",
    "                \n",
    "                education = self.check_education(degree, school, comment)\n",
    "                \n",
    "                has_bachelor_or_higher = re.search(\"Professional|Doctorate|Master|Bachelor\", education)\n",
    "                has_some_college = re.search(\"Associate|Certificate|Some College\", education)\n",
    "                has_high_school = re.match(\"High School\", education)\n",
    "                has_less_than_high_school = re.match(\"Less Than High School\", education)\n",
    "                \n",
    "                if (has_high_school): # Check for high school first \n",
    "                    all_education.append(\"High School\")\n",
    "                elif (has_less_than_high_school):\n",
    "                    all_education.append(\"Less Than High School\")\n",
    "                elif (has_bachelor_or_higher):\n",
    "                    all_education.append(\"Bachelor or Higher\")\n",
    "                elif (has_some_college):\n",
    "                    all_education.append(\"Some College or Associate\")\n",
    "                else:\n",
    "                    all_education.append(\"Other\")\n",
    "                \n",
    "                return all_education\n",
    "\n",
    "            elif (type(ed_history) == list):\n",
    "                for item in ed_history:\n",
    "                    degree = str(item[\"ed_degree\"])\n",
    "                    school = str(item[\"ed_school\"])\n",
    "                    comment = str(item[\"ed_comment\"])\n",
    "                    \n",
    "                    print \"Degree: {0}\".format(degree)\n",
    "                    print \"School: {0}\".format(school)\n",
    "                    print \"Comment: {0}\".format(comment)\n",
    "                \n",
    "                    education = self.check_education(degree, school, comment)\n",
    "                    has_bachelor_or_higher = re.search(\"Professional|Doctorate|Master|Bachelor\", education)\n",
    "                    has_some_college = re.search(\"Associate|Certificate|Some College\", education)\n",
    "                    has_less_than_high_school = re.search(\"Less Than High School\", education)\n",
    "                    has_high_school = re.search(\"High School\", education)\n",
    "                    \n",
    "                    if (has_high_school): # Check for high school first \n",
    "                        all_education.append(\"High School\")\n",
    "                    elif (has_less_than_high_school):\n",
    "                        all_education.append(\"Less Than High School\")\n",
    "                    elif (has_bachelor_or_higher):\n",
    "                        all_education.append(\"Bachelor or Higher\")\n",
    "                    elif (has_some_college):\n",
    "                        all_education.append(\"Some College or Associate\")\n",
    "                    else:\n",
    "                        all_education.append(\"Other\")\n",
    "                        \n",
    "                return all_education\n",
    "        \n",
    "        except Exception as error:\n",
    "            print \"We ran into an error at return edu experience\"\n",
    "            print error\n",
    "            return all_education\n",
    "        \n",
    "    def check_education(self, degree, school, comment): # Do a quick check that the user has a high school degree\n",
    "        try:\n",
    "            hs_degree_mentioned = False\n",
    "            diploma_degree_mentioned = False\n",
    "            hs_word_mentioned = False\n",
    "            graduated_mentioned = False\n",
    "            \n",
    "            if degree != \"\":\n",
    "                hs_degree_mentioned = re.search(\"high school\", degree.lower())\n",
    "                diploma_degree_mentioned = re.search(\"diploma\", degree.lower())\n",
    "            \n",
    "            if school != \"\":\n",
    "                hs_word_mentioned = re.search(\"high school\", school.lower())\n",
    "            \n",
    "            if comment != \"\":\n",
    "                graduated_mentioned = re.search(\"graduated\", comment.lower())\n",
    "\n",
    "            if (hs_degree_mentioned or (hs_word_mentioned and (graduated_mentioned or diploma_degree_mentioned))):\n",
    "                return \"High School\"\n",
    "            elif (hs_word_mentioned and degree == \"\"):\n",
    "                return \"Less Than High School\"\n",
    "            elif (self.check_school_by_name(\"high_school\", school)): # Check if the school name is in the database\n",
    "                print \"We found the high school!\"\n",
    "                if (hs_degree_mentioned or diploma_degree_mentioned or graduated_mentioned):\n",
    "                    return \"High School\"\n",
    "                elif (degree == \"\"):\n",
    "                    return \"Less Than High School\"\n",
    "            else:\n",
    "                print \"We did not find the high school; We should be checking the specific degree now!! \"\n",
    "                return self.check_specific_degree(degree, school, comment)\n",
    "        except Exception as error:\n",
    "            print \"Something went wrong\"\n",
    "            print error\n",
    "            return \"Error\"\n",
    "        \n",
    "    def check_specific_degree(self, degree, school, comment): # Do a quick check that the user has a bachelor or associate degree\n",
    "\n",
    "        try:\n",
    "            \n",
    "            # If there is no degree, then check if they are enrolled in a college in the US\n",
    "            if (degree == \"\"):\n",
    "                print \"Person has no degree; checking if they are in a college in the US\"\n",
    "                if (self.check_school_by_name(\"college\", school)):\n",
    "                    return \"Some College\"\n",
    "                else:\n",
    "                    return \"Ambiguous\"\n",
    "            \n",
    "            # Professional degree\n",
    "            if (re.search('d\\.c|d\\.c\\.m|d\\.d\\.s|d\\.m\\.d|ll\\.b|ll\\.m|l\\.l\\.m|l\\.l\\.b|j\\.d\\.|m\\.d\\.|o\\.d\\.|d\\.o\\.|pharm\\.d', degree.lower()) \n",
    "                or re.search('d\\.p\\.m|d\\.p|pod\\.d|m\\.div|m\\.h\\.l|b\\.d|ordination|d\\.v\\.m| law', degree.lower())\n",
    "                or re.search('DC|DCM|DDS|DMD|LLB|LLM|LLB|JD|MD|OD|DO|PharmD|Ed\\.D|EdD|CFA', degree)\n",
    "                or re.search('DPM|DP|PodD|MDiv|MHL|BD|DVM', degree)):\n",
    "                return \"Professional\"\n",
    "\n",
    "            # PhD\n",
    "            elif (re.search('Ph\\.D|PhD', degree)\n",
    "                 or re.search('doctor|licentiate', degree.lower())): # Doctorate placed here in case MDs are also called doctors \n",
    "                return \"Doctorate\"\n",
    "\n",
    "            # Masters\n",
    "            elif (re.search('M\\.B\\.A|MBA|M\\.A|M\\.S|M\\.Sc|M\\.E|MA|MS|MSc|ME|M\\.A\\.Ed|MAEd|MST|M\\.S\\.T|MEd|M\\.Ed|MPA|M\\.P\\.A', degree)\n",
    "                 or re.search('master|engineer\\'s degree', degree.lower())\n",
    "                 or re.search('MFA|M\\.F\\.A|MSC|MCS', degree)):\n",
    "                return \"Master\"\n",
    "\n",
    "            # Bachelors\n",
    "            elif (re.search('B\\.A|B\\.S|B\\.Sc|BA|BS|BSc|BFA|AB|A\\.B|BPharm|B\\.Pharm', degree)\n",
    "                 or re.search('bachelor|bpharm|bachlor|batchelor', degree.lower())):\n",
    "                return \"Bachelor\"\n",
    "\n",
    "            # Associate's Degree\n",
    "            elif (re.search('AA|AS|A\\.A|A\\.S|A\\.A\\.S|AAS', degree)\n",
    "                 or re.search('associate|foundation', degree.lower())):\n",
    "                return \"Associate\"\n",
    "\n",
    "            # Certificate\n",
    "            elif (re.search('certificate', degree.lower())):\n",
    "                return \"Certificate\"\n",
    "            \n",
    "            else:\n",
    "                print \"Ambiguous\" \n",
    "                return \"Ambiguous\"\n",
    "                \n",
    "            \n",
    "        except Exception as error:\n",
    "            print \"We ran into an error specifying the degree\"\n",
    "            print error\n",
    "            return \"Error\"\n",
    "\n",
    "            \n",
    "    def check_school_by_name(self, dataset, name): # Checks if school name is in a dataset of schools \n",
    "        name = name.lower()\n",
    "        name = self.remove_punctuation(name)\n",
    "        private_schools = 'high_schools_in_us.csv'\n",
    "        public_schools = 'high_schools_in_us.csv'\n",
    "        colleges = 'colleges_in_us.csv'\n",
    "        \n",
    "        if (dataset == \"high_school\"): # Check for \"high school\" and against list of private and public high schools\n",
    "            if re.search('highschool', name):\n",
    "                return True\n",
    "            \n",
    "            with open(private_schools, 'r') as f:\n",
    "                private_school_data = csv.DictReader(f)\n",
    "                for row in private_school_data:\n",
    "                    school = row[\"pinst\"].lower()\n",
    "                    school = self.remove_punctuation(school)\n",
    "                    if (name == school):\n",
    "                        return True\n",
    "                    \n",
    "            with open(public_schools, 'r') as f: # Haven't been able to find a suitable dataset \n",
    "                public_school_data = csv.DictReader(f)\n",
    "                for row in public_school_data:\n",
    "                    school = row[\"pinst\"].lower()\n",
    "                    school = self.remove_punctuation(school)\n",
    "                    if (name == school):\n",
    "                        return True\n",
    "            \n",
    "            return False # Return false if we can't find the school name in any of the lists\n",
    "                \n",
    "        elif (dataset == \"college\"): # Check for keywords and against list of colleges\n",
    "            if (re.search('college|university|institute', name)):\n",
    "                return True \n",
    "            with open(colleges, 'r') as f:\n",
    "                college_data = csv.DictReader(f)\n",
    "                for row in college_data:\n",
    "                    school = row[\"INSTNM\"].lower()\n",
    "                    school = self.remove_punctuation(school)\n",
    "                    if (name == school):\n",
    "                        print \"Found a matching college!\"\n",
    "                        print name\n",
    "                        return True\n",
    "            \n",
    "            return False # Return false if we can't find the school name in any of the lists\n",
    "        \n",
    "        else:\n",
    "            return False # Return false if all else fails \n",
    "    \n",
    "    def remove_punctuation(self, word): # Removes all punctuation and blank spaces from a string\n",
    "        modified_word = re.sub(r'[^\\w\\s]','',word)\n",
    "        modified_word = re.sub(' ', '', modified_word)\n",
    "        return modified_word\n",
    "                    \n",
    "    def show_degree(self, user):\n",
    "        try: \n",
    "            ed_history = user[0][\"education\"][\"institution\"]\n",
    "            if(ed_history == \"\"):\n",
    "                return \"No edu listed\"\n",
    "            elif(type(ed_history) == dict):\n",
    "                return ed_history[\"ed_degree\"]\n",
    "            elif(type(ed_history) == list):\n",
    "                degree_list = []\n",
    "                for ed_experience in user[0][\"education\"][\"institution\"]:\n",
    "                    if (ed_experience[\"ed_degree\"] == \"\"):\n",
    "                        degree_list.append(\"none\")\n",
    "                    elif (ed_experience[\"ed_degree\"] != \"\"):\n",
    "                        degree_list.append(ed_experience[\"ed_degree\"])\n",
    "                return degree_list\n",
    "            else:\n",
    "                return \"Error: Some other error\"\n",
    "                \n",
    "        except:\n",
    "            return \"Error: No education\"\n",
    "                    \n",
    "                    \n",
    "myObject = UpworkDataFormatter()\n",
    "myObject.trial()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}