Objective1_c.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EDA for `SYSTEMS ANALYST 1596 102717.txt` with a functional approach"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, import relevant modules\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import os module to allow us to interface with the underlying operating system that python is running on\n",
    "import os\n",
    "\n",
    "# Define path to look at\n",
    "path = 'CityofLA/Job Bulletins/'\n",
    "\n",
    "# Get a list of all txt files in this path\n",
    "all_txt_files = os.listdir(path) # files won't be in the order shown in their actual folders\n",
    "all_txt_files.sort() # sort files alphabetically. WARNING: this mutates the list. sorted(all_txt_files) won't\n",
    "\n",
    "# Note2self: Some people use os.walk which goes through every root, and their directories, to list all files.\n",
    "# We probably don't need it for now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "683\n",
      "683\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# Do some checks here\n",
    "print(len(all_txt_files))      # length should be 683 as manually verified\n",
    "print(len(set(all_txt_files))) # see if each file is unique. hopefully 683 as well!\n",
    "print('SYSTEMS ANALYST 1596 102717.txt' in all_txt_files) # should be True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "609\n"
     ]
    }
   ],
   "source": [
    "# Find `SYSTEMS ANALYST 1596 102717.txt` (which exists). This is legit since we prove above that each file is unqiue\n",
    "print(all_txt_files.index('SYSTEMS ANALYST 1596 102717.txt'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import this text file as a string.\n",
    "`sa` stands for systems analyst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SYSTEMS ANALYST\\n\\nClass Code: 1596\\nOpen Date: 10-27-17\\n(Exam Open to All, including Current City Employees)\\n\\nANNUAL SALARY\\n\\n$68,611 to $100,307\\nThe salary in the Department of Water and Power is $70,908 to $88,092 and $83,770 to $104,065.\\n\\nNOTES:\\n\\n1. Candidates from the eligible list are normally appointed to vacancies in the lower pay grade positions.  \\n2. Annual salary is at the start of the pay range. The current salary range is subject to change. Please confirm the starting salary with the hiring department before accepting a job offer.\\n3. For information regarding reciprocity between the City of Los Angeles departments and LADWP, go to http://per.lacity.org/Reciprocity_CityDepts_and_DWP.pdf.\\n\\n\\nDUTIES\\n\\nA Systems Analyst analyzes procedures, methods and operations of computer-based information systems; designs, implements, and recommends information systems to improve the efficiency and economy of City operations; performs cost benefit and feasibility analyses related to the modification of existing computer-based information systems, the maintenance and support of information systems, or the implementation of new computer-based systems; accesses and analyzes information from automated files using high-level retrieval languages.\\n\\nREQUIREMENTS/MINIMUM QUALIFICATIONS\\n\\n1. Graduation from an accredited four-year college or university with a major in Computer Science, Information Systems, or Geographical Information Systems; or\\n2. Graduation from an accredited four-year college or university and two years of full-time paid experience in a class at the level of Management Assistant which provides experience in:\\na. the development, analysis, implementation or major modification of new or existing computer-based information systems or relational databases; or\\nb. performing cost benefit, feasibility and requirements analysis for a large-scale computer-based information system; or\\nc. performing system implementation and support activities including software and hardware acquisition, installation, modifications to system configuration, system and application upgrade installation; or\\n3. Two years of full-time paid experience as a Systems Aide with the City of Los Angeles; and\\na. Satisfactory completion of four courses, of at least three semester or four quarter units each, in Information Systems, Systems Analysis, or a closely related degree program, professional designation, or certificate program from an accredited college or university.\\nb. At least three of the courses must be from the core courses required in the program, and one course may be from either the required core courses or the prescribed elective courses of the program.  A course in systems analysis and design is especially desired, but not required. \\n\\nPROCESS NOTES\\n\\n1. Some positions may require a valid California driver\\'s license. Candidates may not be eligible for appointment to these positions if their record within the last 36 months reflects three or more moving violations and/or at-fault accidents, or a conviction of a major moving violation (such as DUI).\\n2. Please note that qualifying education must be from a college or university accredited by a City of Los Angeles recognized agency. A list of approved accrediting agencies can be found at http://per.lacity.org/Accredited%20Institutions%2008-21-08.pdf.\\n3. Applicants qualifying under Requirement #2 above must identify in the Supplemental Questions section of the application the specific projects that provide qualifying experience. The minor application, modification or use of existing systems is not considered to be qualifying experience.\\n4. Four years of full-time paid experience in any of the areas noted in Requirement #2 above may be substituted for the required degree only. The two years\\' experience in the areas noted is still required to fully meet Requirement #2.\\n5. Applicants qualifying under Requirement #3 above must list in the Supplemental Question section of the application the name of the school attended, course titles, number of units, completion dates, and grades. General education courses will not meet the education requirement outlined in Requirement #3. A copy of the offering institution\\'s catalog program description and course list must be attached to the on-line application and submitted to the Personnel Department at the time of filing. Failure to submit the requested documents may result in processing delays and/or disqualification.\\n6. Candidates completing the exam process will be contacted by the Personnel Department to provide required proof of qualifying four-year degree.\\n\\n\\nSELECTIVE CERTIFICATION\\n\\nIn accordance with Charter Section 1010 (b) and Civil Service Rule 5.31, selective certification will be used for some positions that require special skills and/or training. For such positions, only applicants possessing the specific professional certification(s) at the time of filing may be considered. Some positions will require a professional industry certification(s) in one or more of the following:\\n\\n1. Adobe Dreamweaver CS6 ACE\\n2. Adobe Photoshop CS5 or CS6 ACE\\n3. ArcGIS Desktop Developer Associate\\n4. BMC Certified Administrator\\n5. Cisco Certified Entry Networking Technician (CCENT) \\n6. Cisco Certified Network Associate (CCNA)\\n7. Citrix Certified Associate - Virtualization (CCA-V)\\n8. CompTIA A+ \\n9. Delphi Developer Certification\\n10. Enterprise Administration Associate \\n11. Enterprise System Design Associate\\n12. Enterprise Geodata Management Associate\\n13. Enterprise Geodata Management Professional\\n14. HP Service Manager \\n15. IBM Certified Solutions Expert \\n16. IBM Certified Operator\\n17. IBM Certified Systems Administrator\\n18. JAVA SE Programmer Certification\\n19. Linux Foundation Certified System Administrator  (LFCS) \\n20. Linux Foundation Certified Engineer (LFCE)\\n21. MCTS: Administering and Deploying System Center 2012 Configuration Manager\\n1. \\n22. Microsoft Certified Solutions Associate (MCSA)\\n23. Microsoft Certified Solutions Developer (MCSD) \\n24. Microsoft Certified Solutions Expert (MCSE)\\n25. Microsoft Certified Systems Administrator (MCSA) \\n26. Microsoft Certified Systems Engineer (MCSE)\\n27. Microsoft Technology Associate (MTA)\\n28. Microsoft Technology Associate: IT Infrastructure\\n29. Microsoft: Core Solutions of Microsoft SharePoint Server 2013\\n30. Microsoft: Programming in HTML5 with JavaScript and CSS3\\n31. Microsoft: Programming in C#\\n32. Microsoft: Developing ASP.NET MVC Web Applications\\n33. Microsoft: Windows Server 2008 Active Directory, Configuring \\n34. Microsoft: Windows Server 2008, Server Administrator\\n35. Microsoft: Windows 7, Configuring\\n36. Microsoft: Windows 7, Enterprise Desktop Support Technician\\n37. Microsoft: Installing and Configuring Windows 10\\n38. Microsoft: Installation, Storage, and Compute with Windows Server 2016\\n39. Microsoft: Database Fundamentals\\n40. Certified Novell Administrator (CNA)\\n41. Oracle E-Business Suite R12: E-Business Essentials\\n42. Oracle Database SQL Certified Associate \\n43. Red Hat Certified System Administrator (RHCSA)\\n44. Unix System Administration\\n45. VMware Certified Associate 6 - Data Center Virtualization\\n46. VMware Certified Professional 6.5 - Data Center Virtualization\\n\\t\\t\\nApplicants must attach a copy of the certificate(s) to the on-line application at the time of filing. The certificate(s) must be attached to the on-line application BEFORE the application is submitted. If the certificate(s) is attached AFTER the on-line application has been submitted, it will not be available to the City and applicants WILL NOT be credited with having submitted the certificate(s). Applicants who fail to submit the certificate(s) at the time of filing by not attaching it to the on-line application will not be considered for this part of selective certification.\\n\\nWHERE TO APPLY\\n\\nApplications will only be accepted on-line. When you are viewing the on-line job bulletin of your choice, simply scroll to the top of the page and select the \"Apply\" icon. On-line job bulletins are also available at https://www.governmentjobs.com/careers/lacity for Open Competitive Examinations and at https://www.governmentjobs.com/careers/lacity/promotionaljobs for Promotional Examinations.\\n\\nNOTE:\\n\\nApplicants are urged to apply early to ensure you have time to resolve any technical issues you may encounter.\\n\\nAPPLICATION DEADLINE\\n\\nThe examination may close without prior notice at any time after a sufficient number of applications have been received. For administrative purposes, filing may close periodically and reopen periodically.\\n\\nIn accordance with Civil Service Rule 4.2, all applicants who apply may not be tested in this examination. To meet anticipated hiring needs, only a limited number of qualified applicants will be invited to participate in the weighted multiple-choice test in the following order: 1) Los Angeles City Promotional applicants who meet the minimum requirements. You must have received a regular appointment to a City position or be on a reserve list to apply for this examination as a promotional candidate; 2) Applicants currently employed by the City of Los Angeles on a part-time or exempt basis who meet the minimum requirements; 3) Remaining applicants who meet the minimum requirements in sufficient numbers to meet hiring needs in the order that applications were received. Applications submitted during the filing period will be kept on file for two years from the date of filing in the event that additional applicants need to be tested to meet hiring needs.\\nSELECTION PROCESS\\n\\nAfter meeting minimum qualifications, and in accordance with Rule 4.2 as stated above, candidates will be scheduled for the following:\\n\\nExamination Weight: Multiple-Choice Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .100%\\n\\t\\t\\nThe examination will consist entirely of a weighted multiple-choice test administered and proctored online. In the online multiple-choice test, the following competencies may be evaluated: Analytical Ability, Interpersonal Skills, Teamwork, Written Communication, and Technology Application, including: office productivity tools and/or software; local area network (LAN) configurations and communication protocols; computer hardware and their use, networking devices, and software tools; various operating systems including their use, interoperability, capabilities, and limitations; basic principles and concepts of database design; systems development life cycle (SDLC) methodology including the associated steps and phases; cloud-based computing services and their use; disaster recovery systems, procedures, and/or best practices; Information Technology Service Management (ITSM) frameworks and methodologies; and other necessary skills, knowledge, and abilities.\\n\\nAdditional information can be obtained by going to http://per.lacity.org/index.cfm?content=jobanalyses and clicking on Competencies under Systems Analyst.\\n\\nThe multiple-choice test will be proctored and administered on-line during a single session. Candidates invited to participate in the online multiple-choice test will be able to take the test from any location using a computer with a webcam and a reliable internet connection. Candidates will receive an email from the City of Los Angeles outlining the dates and specific steps on how to take the multiple-choice test on-line. It is anticipated that candidates invited to participate in the first administration of the weighted multiple-choice test will be able to take the multiple-choice test online starting SATURDAY, JANUARY 6, 2018 THROUGH SATURDAY, JANUARY 13, 2018. Candidates who do not complete and submit the multiple-choice test on-line by SATURDAY, JANUARY 13, 2018 at 11:59 p.m., Pacific Standard Time, will not be considered further in this examination. Additional test dates may be added as needed.\\n\\n\\nNOTES:\\t\\n\\n1. This examination is based on a validation study, and as provided by Civil Service Commission Rule 4.20, the multiple-choice test will not be subject to candidate inspection.\\n2. As a covered entity under the Fair Employment and Housing Act and Title II of the Americans with Disabilities Act, the City of Los Angeles does not discriminate on the basis of disability and upon request, will provide reasonable accommodations to ensure equal access to its programs, services, and activities.  To request a disability accommodation, please complete the Disability Accommodation Form within 14 calendar days of the submittal of the City application.  The Disability Accommodation Form can be obtained at http://per.lacity.org/exams/verify_disability.pdf.\\n3. A final average score of 70% or higher is required to be placed on the eligible list.\\n4. Applications are accepted subject to review to ensure that minimum qualifications are met. Candidates may be disqualified at any time if is determined that they do not possess the minimum qualifications stated on this bulletin.\\n5. In accordance with Civil Service Rule, Sec. 4.24, review periods may be combined. Candidates in the examination process may file protests as provided in Sec. 4.20, 4.22 and 4.23 as applicable and within the required time frame; however, the Personnel Department may respond to and resolve protests prior to the establishment of the eligible list.\\n6. In conjunction with Civil Service Rules, applicants who have received a regular appointment to a City position or are on a reserve list will be considered Promotional candidates while all other applicants will be considered Open candidates.\\n7. The promotional list will ordinarily be used ahead of the open competitive list. However, if open competitive candidates receive a higher score, without military credits, than the highest available promotional candidate, after adding 0.25 of a point seniority credit for each year of continuous classified City service, the Civil Service Commission, upon request of the appointing authority, may approve certification of such open competitive candidates ahead of the promotional candidates.\\n8. You may take this examination only once every 365 days. Your name may be removed from the eligible list after six (6) months.\\n9. Your rank on the employment list may change as scores of candidates from other administrations of this examination are merged onto one list.\\n\\n\\nNOTICE: \\n\\nIf you receive and accept an offer of employment to a regular position with the City of Los Angeles, your employee benefit coverage (including health and dental coverage as well as life insurance) will commence approximately six weeks after your original regular appointment. Not all positions in the City receive benefit coverage; you should inquire regarding the availability of employee benefits prior to accepting a position.\\nTHIS EXAMINATION IS TO BE GIVEN BOTH ON AN\\nINTERDEPARTMENTAL PROMOTIONAL AND AN OPEN COMPETITIVE BASIS\\n\\nThe City of Los Angeles does not discriminate on the basis of race, religion, national origin, sex, age, marital status, sexual orientation, gender identity, gender expression, disability, creed, color, ancestry, medical condition (cancer), or Acquired Immune Deficiency Syndrome.\\nClass Code 1596 CONT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10-27-17\\n\\n\\nAN EQUAL EMPLOYMENT OPPORTUNITY EMPLOYER\\nFor additional information and FAQs regarding the City\\'s hiring process, please go to: http://per.lacity.org/index.cfm?content=employmenttestingprocess\\n\\n'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sa_path = path + all_txt_files[609]\n",
    "sa = open(sa_path, 'rt').read()\n",
    "sa # display"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get JOB_TITLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SYSTEMS ANALYST'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = sa[:sa.find('Class Code')]\n",
    "jt   = temp.split('\\n')[0]\n",
    "jt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SYSTEMS ANALYST'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def job_class_title(job):\n",
    "    '''Returns the field JOB_CLASS_TITLE (jct)'''\n",
    "    # From the start to the first newline character is where the information located\n",
    "    temp = job[:job.find('Class Code')]\n",
    "    # Split at newline character and get the first element in the list\n",
    "    jct = temp.split('\\n')[0]\n",
    "    return jct\n",
    "\n",
    "# Test\n",
    "job_class_title(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get JOB_CLASS_NO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1596"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Good thing is that str.split() ignores newline characters!\n",
    "temp = sa[sa.find('Class Code'):sa.find('Open Date')]\n",
    "jcn  = [int(string_num) for string_num in temp.split() if string_num.isdigit()][0]\n",
    "jcn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1596'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "# Need to modify this function to satisfy the required format. Some jobs have only three digit code,\n",
    "# so need to be in the form 0###\n",
    "def job_class_no(job):\n",
    "    '''Returns the field JOB_CLASS_NO (jcn)'''\n",
    "    # From Class Code to Open Date is where the information located.\n",
    "    # Good thing is that str.split() ignores newline characters!\n",
    "    temp = job[job.find('Class Code'):job.find('Open Date')]\n",
    "    # Check if anything in temp is a digit via isdigit(). If it is, get it\n",
    "    jcn  = [string_num for string_num in temp.split() if string_num.isdigit()][0] # first element is what we want\n",
    "    \n",
    "    return jcn\n",
    "\n",
    "# Test\n",
    "job_class_no(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get REQUIREMENT_SET_ID\n",
    "<font color='red'>For now, just treat every txt has the word PROCESS NOTES</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color='green'>First, define a helper function called, `_base`.</font>**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'REQUIREMENTS/MINIMUM QUALIFICATIONS\\n\\n1. Graduation from an accredited four-year college or university with a major in Computer Science, Information Systems, or Geographical Information Systems; or\\n2. Graduation from an accredited four-year college or university and two years of full-time paid experience in a class at the level of Management Assistant which provides experience in:\\na. the development, analysis, implementation or major modification of new or existing computer-based information systems or relational databases; or\\nb. performing cost benefit, feasibility and requirements analysis for a large-scale computer-based information system; or\\nc. performing system implementation and support activities including software and hardware acquisition, installation, modifications to system configuration, system and application upgrade installation; or\\n3. Two years of full-time paid experience as a Systems Aide with the City of Los Angeles; and\\na. Satisfactory completion of four courses, of at least three semester or four quarter units each, in Information Systems, Systems Analysis, or a closely related degree program, professional designation, or certificate program from an accredited college or university.\\nb. At least three of the courses must be from the core courses required in the program, and one course may be from either the required core courses or the prescribed elective courses of the program.  A course in systems analysis and design is especially desired, but not required. \\n\\n'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Locate the information\n",
    "temp = sa[sa.find('REQUIREMENTS/MINIMUM QUALIFICATIONS'):sa.find('PROCESS NOTES')]\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'REQUIREMENTS/MINIMUM QUALIFICATIONS\\n\\n1 Graduation from an accredited four-year college or university with a major in Computer Science, Information Systems, or Geographical Information Systems; or\\n2 Graduation from an accredited four-year college or university and two years of full-time paid experience in a class at the level of Management Assistant which provides experience in:\\na the development, analysis, implementation or major modification of new or existing computer-based information systems or relational databases; or\\nb performing cost benefit, feasibility and requirements analysis for a large-scale computer-based information system; or\\nc performing system implementation and support activities including software and hardware acquisition, installation, modifications to system configuration, system and application upgrade installation; or\\n3 Two years of full-time paid experience as a Systems Aide with the City of Los Angeles; and\\na Satisfactory completion of four courses, of at least three semester or four quarter units each, in Information Systems, Systems Analysis, or a closely related degree program, professional designation, or certificate program from an accredited college or university\\nb At least three of the courses must be from the core courses required in the program, and one course may be from either the required core courses or the prescribed elective courses of the program  A course in systems analysis and design is especially desired, but not required \\n\\n'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove the dots in 1., 2., a., etc.\n",
    "temp = temp.replace('.', '')\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['1', '2', '3']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the digits that marks one part from the other\n",
    "digits = [str(int(string_num)) for string_num in temp.split() if string_num.isdigit()]\n",
    "digits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['1 Graduation from an accredited four-year college or university with a major in Computer Science, Information Systems, or Geographical Information Systems; or\\n',\n",
       " '2 Graduation from an accredited four-year college or university and two years of full-time paid experience in a class at the level of Management Assistant which provides experience in:\\na the development, analysis, implementation or major modification of new or existing computer-based information systems or relational databases; or\\nb performing cost benefit, feasibility and requirements analysis for a large-scale computer-based information system; or\\nc performing system implementation and support activities including software and hardware acquisition, installation, modifications to system configuration, system and application upgrade installation; or\\n',\n",
       " '3 Two years of full-time paid experience as a Systems Aide with the City of Los Angeles; and\\na Satisfactory completion of four courses, of at least three semester or four quarter units each, in Information Systems, Systems Analysis, or a closely related degree program, professional designation, or certificate program from an accredited college or university\\nb At least three of the courses must be from the core courses required in the program, and one course may be from either the required core courses or the prescribed elective courses of the program  A course in systems analysis and design is especially desired, but not required \\n\\n']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Separate requirements one from the others\n",
    "requirement_set = []\n",
    "for idx in range(len(digits)):\n",
    "    try:                                                # this works fine until the last index as it can't move on\n",
    "        mask1 = digits[idx]                             # mark the first position\n",
    "        mask2 = digits[idx+1]                           # mark the second position\n",
    "        req   = temp[temp.find(mask1):temp.find(mask2)] # get the part from the first pos. to the second pos.\n",
    "        requirement_set.append(req)                     # put that part into the container\n",
    "    except:                                             # a pythonic way to get the last part w/o excessive coding\n",
    "        mask  = digits[idx]              \n",
    "        req   = temp[temp.find(mask):]\n",
    "        requirement_set.append(req)\n",
    "\n",
    "requirement_set # display results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['1 Graduation from an accredited four-year college or university with a major in Computer Science, Information Systems, or Geographical Information Systems; or'],\n",
       " ['2 Graduation from an accredited four-year college or university and two years of full-time paid experience in a class at the level of Management Assistant which provides experience in:',\n",
       "  'a the development, analysis, implementation or major modification of new or existing computer-based information systems or relational databases; or',\n",
       "  'b performing cost benefit, feasibility and requirements analysis for a large-scale computer-based information system; or',\n",
       "  'c performing system implementation and support activities including software and hardware acquisition, installation, modifications to system configuration, system and application upgrade installation; or'],\n",
       " ['3 Two years of full-time paid experience as a Systems Aide with the City of Los Angeles; and',\n",
       "  'a Satisfactory completion of four courses, of at least three semester or four quarter units each, in Information Systems, Systems Analysis, or a closely related degree program, professional designation, or certificate program from an accredited college or university',\n",
       "  'b At least three of the courses must be from the core courses required in the program, and one course may be from either the required core courses or the prescribed elective courses of the program  A course in systems analysis and design is especially desired, but not required ']]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First, let's simplify requirement_set. We still need to keep the newline characters (\\n) as a mark from one sentence\n",
    "# to another. However, we also need to remove those \\n at the end of the sentences to foster easier analysis.\n",
    "simplified_requirement_set = [[item for item in element.split('\\n') if len(item) > 1] for element in requirement_set]\n",
    "simplified_requirement_set\n",
    "\n",
    "## Note: nested list is exactly what I need because it provides a natural mark from the sentences with digits"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Algorithm:\n",
    "\n",
    "for `aList` in the list of `simplified_requirement_set`:\n",
    "* Step 1: if length is 1, then it *may be* a requirement_id. This is to handle situations: \"1. and 2.\" or \"1. or 2.\"\n",
    "    * Step 1a: if it ends with \"and\", then this line is related to the next line\n",
    "    * Step 1b: else, then it is indeed a requirement_id\n",
    "* Step 2: if length is more than 1, then it defines a sub-requirement. This is to handle situations: \"2a or 2b\" or \"3 and a or b\"\n",
    "    * Step 2a: if all the sub-requirements end with 'or', then each defines a sub_requirement_id\n",
    "    * Step 2b: else, then if defines a *nested* sub_requirement_id, such as 3ab via the word *and* at the end of the sentence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('1',\n",
       "  '1 Graduation from an accredited four-year college or university with a major in Computer Science, Information Systems, or Geographical Information Systems; or',\n",
       "  'A'),\n",
       " ('2',\n",
       "  '2 Graduation from an accredited four-year college or university and two years of full-time paid experience in a class at the level of Management Assistant which provides experience in:',\n",
       "  'A',\n",
       "  'a the development, analysis, implementation or major modification of new or existing computer-based information systems or relational databases; or'),\n",
       " ('2',\n",
       "  '2 Graduation from an accredited four-year college or university and two years of full-time paid experience in a class at the level of Management Assistant which provides experience in:',\n",
       "  'B',\n",
       "  'b performing cost benefit, feasibility and requirements analysis for a large-scale computer-based information system; or'),\n",
       " ('2',\n",
       "  '2 Graduation from an accredited four-year college or university and two years of full-time paid experience in a class at the level of Management Assistant which provides experience in:',\n",
       "  'C',\n",
       "  'c performing system implementation and support activities including software and hardware acquisition, installation, modifications to system configuration, system and application upgrade installation; or'),\n",
       " ('3',\n",
       "  '3 Two years of full-time paid experience as a Systems Aide with the City of Los Angeles; and',\n",
       "  'A',\n",
       "  None,\n",
       "  'a Satisfactory completion of four courses, of at least three semester or four quarter units each, in Information Systems, Systems Analysis, or a closely related degree program, professional designation, or certificate program from an accredited college or university',\n",
       "  'b At least three of the courses must be from the core courses required in the program, and one course may be from either the required core courses or the prescribed elective courses of the program  A course in systems analysis and design is especially desired, but not required ')]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Initialize r as an empty list. \n",
    "# r contains tuples of the form (digit, text-related-to-digit, alphabet-letter, (None), (text-related-to-alphabet-letter), mis), \n",
    "# where digit is the requirement, text-related-to-digit is used to mine EDUCATION_YEARS, SCHOOL_TYPE, etc.\n",
    "# alphabet letter is its sub-requirement, text-related-to-alphabet-letter is the related information is used to\n",
    "# mine other information such as EXP_JOB_CLAS_TITLE, COURSE_COUNT, etc., and mis (miscellaneous) is used for extra\n",
    "# information, such as requirements that such and such courses must satistfy, or to catch any final information.\n",
    "r = []\n",
    "for aList in simplified_requirement_set:\n",
    "    # Step 1\n",
    "    if len(aList) == 1:\n",
    "        possibly_a_requirement = aList[0]\n",
    "        # Step 1a\n",
    "        if possibly_a_requirement.endswith('and'):\n",
    "            next_item_idx = simplified_requirement_set.index(aList) + 1\n",
    "            next_digit    = simplified_requirement_set[next_item_idx][0]\n",
    "            nested_requirement = possibly_a_requirement[0] + next_digit\n",
    "            r.append(tuple(nested_requirement))\n",
    "        # Step 1b\n",
    "        else:\n",
    "            requirement_id = possibly_a_requirement[0]\n",
    "            # Append 'A' for sub-requirement field if no nested sub-requirement found\n",
    "            # Append the related information for further text mining purpose. Raw format is OK.\n",
    "            r.append((requirement_id, possibly_a_requirement, 'A'))\n",
    "    # Step 2\n",
    "    elif len(aList) > 1:\n",
    "        main_sentence  = aList[0]\n",
    "        requirement_id = main_sentence[0]\n",
    "        # Step 2a\n",
    "        if all([sub.endswith('or') for sub in aList[1:-1]]):\n",
    "            requirement_subset = aList[1:]\n",
    "            for sub_requirement in requirement_subset:\n",
    "                sub_requirement_id = sub_requirement[0].upper() # 'a' becomes 'A', for example\n",
    "                # Append the related information for further text mining purpose. Raw format is OK.\n",
    "                r.append((requirement_id, main_sentence, sub_requirement_id, sub_requirement))\n",
    "        # Step 2b\n",
    "        else:\n",
    "            nested_requirement = (requirement_id, main_sentence)\n",
    "            for possibly_a_sub_requirement in aList[1:]:\n",
    "                previous_item_idx = aList.index(possibly_a_sub_requirement) - 1\n",
    "                if aList[previous_item_idx].endswith('and'):\n",
    "                    sub_requirement     = possibly_a_sub_requirement\n",
    "                    sub_requirement_id  = sub_requirement[0].upper()\n",
    "                    nested_requirement += (sub_requirement_id, None, sub_requirement)\n",
    "                else:\n",
    "                    miscellaneous       = possibly_a_sub_requirement\n",
    "                    nested_requirement += (miscellaneous, ) # either tuple() or comma; else, treated as a single.\n",
    "            r.append(nested_requirement)\n",
    "\n",
    "# Display results\n",
    "r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1 Graduation from an accredited four-year coll...</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2 Graduation from an accredited four-year coll...</td>\n",
       "      <td>A</td>\n",
       "      <td>a the development, analysis, implementation or...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2 Graduation from an accredited four-year coll...</td>\n",
       "      <td>B</td>\n",
       "      <td>b performing cost benefit, feasibility and req...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2 Graduation from an accredited four-year coll...</td>\n",
       "      <td>C</td>\n",
       "      <td>c performing system implementation and support...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>3 Two years of full-time paid experience as a ...</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>a Satisfactory completion of four courses, of ...</td>\n",
       "      <td>b At least three of the courses must be from t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0                                                  1  2  \\\n",
       "0  1  1 Graduation from an accredited four-year coll...  A   \n",
       "1  2  2 Graduation from an accredited four-year coll...  A   \n",
       "2  2  2 Graduation from an accredited four-year coll...  B   \n",
       "3  2  2 Graduation from an accredited four-year coll...  C   \n",
       "4  3  3 Two years of full-time paid experience as a ...  A   \n",
       "\n",
       "                                                   3  \\\n",
       "0                                               None   \n",
       "1  a the development, analysis, implementation or...   \n",
       "2  b performing cost benefit, feasibility and req...   \n",
       "3  c performing system implementation and support...   \n",
       "4                                               None   \n",
       "\n",
       "                                                   4  \\\n",
       "0                                               None   \n",
       "1                                               None   \n",
       "2                                               None   \n",
       "3                                               None   \n",
       "4  a Satisfactory completion of four courses, of ...   \n",
       "\n",
       "                                                   5  \n",
       "0                                               None  \n",
       "1                                               None  \n",
       "2                                               None  \n",
       "3                                               None  \n",
       "4  b At least three of the courses must be from t...  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Next, let's apply pd.DataFrame on r and see what happens\n",
    "pd.DataFrame(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Very nice! This is exactly what we need. Finally, let's wrap all of these into a helper function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(5, 6)\n"
     ]
    },
    {
     "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>REQUIREMENT_SET_ID</th>\n",
       "      <th>MINE_1</th>\n",
       "      <th>REQUIREMENT_SUBSET_ID</th>\n",
       "      <th>EXP_JOB_CLASS_FUNCTION</th>\n",
       "      <th>MINE_2</th>\n",
       "      <th>MISC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Graduation from an accredited four-year colleg...</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Graduation from an accredited four-year colleg...</td>\n",
       "      <td>A</td>\n",
       "      <td>the development, analysis, implementation or m...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Graduation from an accredited four-year colleg...</td>\n",
       "      <td>B</td>\n",
       "      <td>performing cost benefit, feasibility and requi...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Graduation from an accredited four-year colleg...</td>\n",
       "      <td>C</td>\n",
       "      <td>performing system implementation and support a...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>Two years of full-time paid experience as a Sy...</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>Satisfactory completion of four courses, of at...</td>\n",
       "      <td>At least three of the courses must be from the...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  REQUIREMENT_SET_ID                                             MINE_1  \\\n",
       "0                  1  Graduation from an accredited four-year colleg...   \n",
       "1                  2  Graduation from an accredited four-year colleg...   \n",
       "2                  2  Graduation from an accredited four-year colleg...   \n",
       "3                  2  Graduation from an accredited four-year colleg...   \n",
       "4                  3  Two years of full-time paid experience as a Sy...   \n",
       "\n",
       "  REQUIREMENT_SUBSET_ID                             EXP_JOB_CLASS_FUNCTION  \\\n",
       "0                     A                                               None   \n",
       "1                     A  the development, analysis, implementation or m...   \n",
       "2                     B  performing cost benefit, feasibility and requi...   \n",
       "3                     C  performing system implementation and support a...   \n",
       "4                     A                                               None   \n",
       "\n",
       "                                              MINE_2  \\\n",
       "0                                               None   \n",
       "1                                               None   \n",
       "2                                               None   \n",
       "3                                               None   \n",
       "4  Satisfactory completion of four courses, of at...   \n",
       "\n",
       "                                                MISC  \n",
       "0                                               None  \n",
       "1                                               None  \n",
       "2                                               None  \n",
       "3                                               None  \n",
       "4  At least three of the courses must be from the...  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def _base(job, use_df=True):\n",
    "    '''\n",
    "    _BASE attempts to parse the structure in REQUIREMENTS/MINIMUM QUALIFICATIONS. \n",
    "    It takes in a job and returns a dataframe of information, which supports other functions.\n",
    "    \n",
    "    It is intended to use inside other functions (hence started with an underscore), not for stand-alone use.\n",
    "    '''\n",
    "    # Each hash # below is the code in one of the cell above. For output/logic, see above cells.\n",
    "    \n",
    "    # From REQUIREMENTS/MINIMUM QUALIFICATIONS to PROCESS NOTES is where the information located\n",
    "    temp = sa[sa.find('REQUIREMENTS/MINIMUM QUALIFICATIONS'):sa.find('PROCESS NOTES')]\n",
    "    # Get the digits that marks one part from the other\n",
    "    temp1  = temp.replace('.', '') # don't want to lose the periods in temp\n",
    "    digits = [string_num for string_num in temp1.split() if string_num.isdigit()]\n",
    "    # Use these digits to separate requirements\n",
    "    requirement_set = []\n",
    "    for idx in range(len(digits)):\n",
    "        try:                                                # this works fine until the last index as it can't move on\n",
    "            mask1 = digits[idx]                             # mark the first position\n",
    "            mask2 = digits[idx+1]                           # mark the second position\n",
    "            req   = temp[temp.find(mask1):temp.find(mask2)] # get the part from the first pos. to the second pos.\n",
    "            requirement_set.append(req)                     # put that part into the container\n",
    "        except:                                             # a pythonic way to get the last part w/o excessive coding\n",
    "            mask  = digits[idx]              \n",
    "            req   = temp[temp.find(mask):]\n",
    "            requirement_set.append(req)\n",
    "    # Simplify requirement_set. We still need to keep the newline characters (\\n) as a mark from one sentence\n",
    "    # to another. However, we also need to remove those \\n at the end of the sentences to foster easier analysis.\n",
    "    simplified_requirement_set = [[item \n",
    "                                   for item in element.split('\\n') \n",
    "                                   if len(item) > 1] \n",
    "                                  for element in requirement_set]\n",
    "    # Implementation of the algorithm above\n",
    "    r = []\n",
    "    for aList in simplified_requirement_set:\n",
    "        # Step 1\n",
    "        if len(aList) == 1:\n",
    "            possibly_a_requirement = aList[0]\n",
    "            # Step 1a\n",
    "            if possibly_a_requirement.endswith('and'):\n",
    "                next_item_idx = simplified_requirement_set.index(aList) + 1\n",
    "                next_digit    = simplified_requirement_set[next_item_idx][0]\n",
    "                nested_requirement = possibly_a_requirement[0] + next_digit\n",
    "                r.append(tuple(nested_requirement))\n",
    "            # Step 1b\n",
    "            else:\n",
    "                requirement_id = possibly_a_requirement[0]\n",
    "                # Append 'A' for sub-requirement field if no nested sub-requirement found\n",
    "                # Append the related information for further text mining purpose. Raw format is OK.\n",
    "                r.append((requirement_id, possibly_a_requirement[3:], 'A')) # 1. Graduation..., so 3 characters\n",
    "        # Step 2\n",
    "        elif len(aList) > 1:\n",
    "            main_sentence  = aList[0]\n",
    "            requirement_id = main_sentence[0]\n",
    "            # Step 2a\n",
    "            if all([sub.endswith('or') for sub in aList[1:-1]]):\n",
    "                requirement_subset = aList[1:]\n",
    "                for sub_requirement in requirement_subset:\n",
    "                    sub_requirement_id = sub_requirement[0].upper() # 'a' becomes 'A', for example\n",
    "                    # Append the related information for further text mining purpose. Raw format is OK.\n",
    "                    r.append((requirement_id, main_sentence[3:], sub_requirement_id, sub_requirement[3:])) # 3 chars\n",
    "            # Step 2b\n",
    "            else:\n",
    "                nested_requirement = (requirement_id, main_sentence[3:])\n",
    "                for possibly_a_sub_requirement in aList[1:]:\n",
    "                    previous_item_idx = aList.index(possibly_a_sub_requirement) - 1\n",
    "                    if aList[previous_item_idx].endswith('and'):\n",
    "                        sub_requirement     = possibly_a_sub_requirement\n",
    "                        sub_requirement_id  = sub_requirement[0].upper()\n",
    "                        nested_requirement += (sub_requirement_id, None, sub_requirement[3:]) # 3 spaces\n",
    "                    else:\n",
    "                        miscellaneous       = possibly_a_sub_requirement\n",
    "                        nested_requirement += (miscellaneous[3:], ) # must use either tuple() or comma. Here, comma.\n",
    "                r.append(nested_requirement)\n",
    "    \n",
    "    # Returns\n",
    "    if use_df:\n",
    "        df = pd.DataFrame(data=r, columns=['REQUIREMENT_SET_ID',\n",
    "                                           'MINE_1',\n",
    "                                           'REQUIREMENT_SUBSET_ID',\n",
    "                                           'EXP_JOB_CLASS_FUNCTION',\n",
    "                                           'MINE_2',\n",
    "                                           'MISC'])\n",
    "        return df\n",
    "    else:\n",
    "        return r\n",
    "\n",
    "# Test\n",
    "print(_base(job=sa).shape)\n",
    "_base(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color='green'>Now, we can define the function that achieves this task.</font>**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['1', '2', '2', '2', '3']"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def requirement_set_id(job):\n",
    "    '''Returns REQUIREMENT_SET_ID (rsid)'''\n",
    "    # Invoke _base function to obtain raw information\n",
    "    temp = _base(job)\n",
    "    # Get requirement_set_id's.\n",
    "    rsid = list(temp['REQUIREMENT_SET_ID'])\n",
    "    \n",
    "    return rsid\n",
    "\n",
    "# Test\n",
    "requirement_set_id(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get REQUIREMENT_SUBSET_ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['A', 'A', 'B', 'C', 'A']"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def requirement_subset_id(job):\n",
    "    '''Returns REQUIREMENT_SUBSET_ID (rsubsetid)'''\n",
    "    # Invoke _base function to obtain raw information\n",
    "    temp = _base(job)\n",
    "    # Get requirement_subset_id's\n",
    "    rsubsetid = list(temp['REQUIREMENT_SUBSET_ID'])\n",
    "    \n",
    "    return rsubsetid\n",
    "\n",
    "# Test\n",
    "requirement_subset_id(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get DUTIES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'A Systems Analyst analyzes procedures, methods and operations of computer-based information systems; designs, implements, and recommends information systems to improve the efficiency and economy of City operations; performs cost benefit and feasibility analyses related to the modification of existing computer-based information systems, the maintenance and support of information systems, or the implementation of new computer-based systems; accesses and analyzes information from automated files using high-level retrieval languages.'"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = sa[sa.find('DUTIES'):sa.find('REQUIREMENTS/MINIMUM QUALIFICATIONS')]\n",
    "temp = temp.split()[1:]\n",
    "duties = ' '.join(temp)\n",
    "duties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'A Systems Analyst analyzes procedures, methods and operations of computer-based information systems; designs, implements, and recommends information systems to improve the efficiency and economy of City operations; performs cost benefit and feasibility analyses related to the modification of existing computer-based information systems, the maintenance and support of information systems, or the implementation of new computer-based systems; accesses and analyzes information from automated files using high-level retrieval languages.'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def job_duties(job):\n",
    "    '''Returns JOB_DUTIES (jd)'''\n",
    "    # From DUTIES to REQUIREMENTS/MINIMUM QUALIFICATIONS is where the information located\n",
    "    temp = job[job.find('DUTIES'):job.find('REQUIREMENTS/MINIMUM QUALIFICATIONS')]\n",
    "    # Split at white space and ignore the 0th element (the word 'DUTIES')\n",
    "    temp = temp.split()[1:]\n",
    "    # Join with white space to get the required format\n",
    "    duties = ' '.join(temp)\n",
    "    \n",
    "    return duties\n",
    "\n",
    "# Test\n",
    "job_duties(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get EDUCATION_YEARS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build a (word, number) dictionary\n",
    "number_dict = {'one': 1, 'two': 2, 'three': 3, 'four': 4, 'five': 5, \n",
    "               'six': 6, 'seven': 7, 'eight': 8, 'nine': 9, 'ten': 10}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[4, 4, 4, 4, None]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get education years. The idea is that they are USUALLY written with a dash in the middle: four-year, two-year,\n",
    "\n",
    "## Extract a list of related sentences\n",
    "temp = [l[1] for l in r]\n",
    "## Main code for education years\n",
    "ey= []                                          # initialization: ey = EDUCATION_YEARS\n",
    "for possibly_contains_ey in temp:               # loop through each sentence in temp\n",
    "    for word in possibly_contains_ey.split():   # loop through each word in the list. Ex: ['graduation', 'four-year']\n",
    "        if '-' in word:                         # if there's a dash in the word. Ex: 'four-year', 'full-time'\n",
    "            term = word.split('-')              # split at the dash. Ex: ['four', 'year'], ['full', 'time']\n",
    "            if term[0] in number_dict.keys():   # first element in term is in number dict. Ex: 'four'\n",
    "                unit     = term[1]              # define unit. Ex: month, year,\n",
    "                duration = number_dict[term[0]] # define duration of education. Ex: 'four', 'six'\n",
    "                if unit  == 'year':             # if unit is in year. Ex: year\n",
    "                    ey.append(duration)         # then append duration to ey\n",
    "                else:                           # otherwise. Ex: month\n",
    "                    ey.append(duration/12)      # divide by 12 before appending to ey. So that 6 months = 0.5 year\n",
    "                break                           # break immediately. otherwise, it would go to ['full', 'time']...\n",
    "            else:                               # and then execute the else part, which yields: 4, None, 4, None\n",
    "                ey.append(None)\n",
    "\n",
    "ey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[4, 4, 4, 4, None]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Defind a function\n",
    "def education_years(job):\n",
    "    '''Returns EDUCATION_YEARS (ey)'''\n",
    "    # Invoke _base function to obtain raw information\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_1' column\n",
    "    temp = list(temp['MINE_1'])\n",
    "    # Get education years based on the cell above\n",
    "    ey= []                                          # initialization: ey = EDUCATION_YEARS\n",
    "    for possibly_contains_ey in temp:               # loop through each sentence in temp\n",
    "        for word in possibly_contains_ey.split():   # loop through each word. Ex: ['graduation', 'four-year']\n",
    "            if '-' in word:                         # if there's a dash in the word. Ex: 'four-year', 'full-time'\n",
    "                term = word.split('-')              # split at the dash. Ex: ['four', 'year'], ['full', 'time']\n",
    "                if term[0] in number_dict.keys():   # first element in term is in number dict. Ex: 'four'\n",
    "                    unit     = term[1]              # define unit. Ex: month, year,\n",
    "                    duration = number_dict[term[0]] # define duration of education. Ex: 'four', 'six'\n",
    "                    if unit  == 'year':             # if unit is in year. Ex: year\n",
    "                        ey.append(duration)         # then append duration to ey\n",
    "                    else:                           # otherwise. Ex: month\n",
    "                        ey.append(duration/12)      # divide by 12 before appending to ey. So that 6 months = 0.5 year\n",
    "                    break                           # break immediately. otherwise, it would go to ['full', 'time']...\n",
    "                else:                               # and then execute the else part, which yields: 4, None, 4, None\n",
    "                    ey.append(None)\n",
    "    \n",
    "    # Returns\n",
    "    return ey\n",
    "\n",
    "# Test\n",
    "education_years(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get SCHOOL_TYPE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build a list of school type\n",
    "type_of_school = ['college', 'university', 'high', 'trade', 'apprenticeship']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['COLLEGE OR UNIVERSITY',\n",
       " 'COLLEGE OR UNIVERSITY',\n",
       " 'COLLEGE OR UNIVERSITY',\n",
       " 'COLLEGE OR UNIVERSITY',\n",
       " None]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get school type by looking up\n",
    "\n",
    "## Extract a list of related sentences\n",
    "temp = [l[1] for l in r]\n",
    "## Main code for education years\n",
    "st = []                                                 # initialization: st = school type\n",
    "for possibly_contains_st in temp:                       # loop through each sentence in temp\n",
    "    possibly_contains_st = possibly_contains_st.lower() # convert to lower case for easier analysis\n",
    "    all_type_of_education = []                          # initialization: a container that holds everything found\n",
    "    for word in possibly_contains_st.split():           # loop through each word. Ex: 'Graduation', 'from'\n",
    "        if word in type_of_school:                      # match. Ex: 'college', 'university'\n",
    "            if word == 'high':                          # if 'high', make it 'high school'\n",
    "                word += ' school'                       \n",
    "            elif word == 'trade':                       # if 'trade', make it 'trade school'\n",
    "                word += ' school'\n",
    "            all_type_of_education.append(word.upper())  # append to pre-defined list. Ex: ['COLLEGE', 'UNIVERSITY']\n",
    "    if len(all_type_of_education) > 1:                  # if list is not empty, then join with OR\n",
    "        joining = ' OR '.join(all_type_of_education)    # Ex: 'COLLEGE OR UNIVERSITY'\n",
    "    else:                                               \n",
    "        joining = None                                  # Otherwise, put None there\n",
    "    st.append(joining)                                  # Finally, append to the big list st\n",
    "\n",
    "st"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['COLLEGE OR UNIVERSITY',\n",
       " 'COLLEGE OR UNIVERSITY',\n",
       " 'COLLEGE OR UNIVERSITY',\n",
       " 'COLLEGE OR UNIVERSITY',\n",
       " None]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def school_type(job):\n",
    "    '''Returns SCHOOL_TYPE st'''\n",
    "    # Invoke _base function to obtain raw information\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_1' column\n",
    "    temp = list(temp['MINE_1'])\n",
    "    # Get school type based on cell above\n",
    "    st = []                                                 # initialization: st = school type\n",
    "    for possibly_contains_st in temp:                       # loop through each sentence in temp\n",
    "        possibly_contains_st = possibly_contains_st.lower() # convert to lower case for easier analysis\n",
    "        all_type_of_education = []                          # initialization: a container that holds everything found\n",
    "        for word in possibly_contains_st.split():           # loop through each word. Ex: 'Graduation', 'from'\n",
    "            if word in type_of_school:                      # match. Ex: 'college', 'university'\n",
    "                if word == 'high':                          # if 'high', make it 'high school'\n",
    "                    word += ' school'                       \n",
    "                elif word == 'trade':                       # if 'trade', make it 'trade school'\n",
    "                    word += ' school'\n",
    "                all_type_of_education.append(word.upper())  # append to predefined list. Ex: ['COLLEGE', 'UNIVERSITY']\n",
    "        if len(all_type_of_education) > 1:                  # if list is not empty, then join with OR\n",
    "            joining = ' OR '.join(all_type_of_education)    # Ex: 'COLLEGE OR UNIVERSITY'\n",
    "        else:\n",
    "            joining = None                                  # Otherwise: put None there\n",
    "        st.append(joining)                              # Finally, append to the big list st\n",
    "    \n",
    "    # Returns\n",
    "    return st\n",
    "\n",
    "# Test\n",
    "school_type(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get EDUCATION_MAJOR\n",
    "Idea: build a list of majors and just look things up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build a list of majors. To be expanded later\n",
    "majors = ['Computer Science', 'Information Systems', 'Geographical Information Systems']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['COMPUTER SCIENCE|INFORMATION SYSTEMS|GEOGRAPHICAL INFORMATION SYSTEMS',\n",
       " None,\n",
       " None,\n",
       " None,\n",
       " None]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get major by looking it up.\n",
    "\n",
    "## Extract a list of related sentences\n",
    "temp = [l[1] for l in r]\n",
    "## Main code for education majors\n",
    "em = []                                                  # initialization: em = education majors\n",
    "for possibly_contains_em in temp:                        # loop through each sentence in temp\n",
    "    all_type_of_majors = []                              # initialization: a container that holds everything found\n",
    "    try:                                                 # use try/except because we have None in our data\n",
    "        for major in majors:                             # for each major in major list. Ex: 'Computer Science'\n",
    "            if possibly_contains_em.find(major) != -1:   # if major is found\n",
    "                all_type_of_majors.append(major.upper()) # append to the inside-loop list\n",
    "        if len(all_type_of_majors) > 0:                  # if there is a major in all_type_of_majors container\n",
    "            em.append('|'.join(all_type_of_majors))      # then, join as required and append to em list\n",
    "        else:\n",
    "            em.append(None)                              # otherwise, no major, so append None\n",
    "    except:\n",
    "        pass\n",
    "\n",
    "em"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['COMPUTER SCIENCE|INFORMATION SYSTEMS|GEOGRAPHICAL INFORMATION SYSTEMS',\n",
       " None,\n",
       " None,\n",
       " None,\n",
       " None]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def education_majors(job):\n",
    "    '''Returns EDUCATION_MAJORS (em)'''\n",
    "    # Invoke _base function to obtain raw information\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_1' column\n",
    "    temp = list(temp['MINE_1'])\n",
    "    # Get education majors based on cell above\n",
    "    em = []                                                  # initialization: em = education majors\n",
    "    for possibly_contains_em in temp:                        # loop through each sentence in temp\n",
    "        all_type_of_majors = []                              # initialization: a container that holds everything found\n",
    "        try:                                                 # use try/except because we have None in our data\n",
    "            for major in majors:                             # for each major in major list. Ex: 'Computer Science'\n",
    "                if possibly_contains_em.find(major) != -1:   # if major is found\n",
    "                    all_type_of_majors.append(major.upper()) # append to the inside-loop list\n",
    "            if len(all_type_of_majors) > 0:                  # if there is a major in all_type_of_majors container\n",
    "                em.append('|'.join(all_type_of_majors))      # then, join as required and append to em list\n",
    "            else:\n",
    "                em.append(None)                              # otherwise, no major, so append None\n",
    "        except:\n",
    "            pass\n",
    "\n",
    "    # Returns\n",
    "    return em\n",
    "\n",
    "# Test\n",
    "education_majors(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get EXPERIENCE_LENGTH\n",
    "Idea: Build a dictionary such as,\n",
    "{'one': 1, 'two': two,...} and look up the word in each sentence. So shouldn't be too hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None, 2, 2, 2, 2]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get length of having experience by looking it up. The idea is that they have the format two years (space between)\n",
    "\n",
    "## Extract a list of related sentences\n",
    "temp = [l[1] for l in r]\n",
    "## Main code for experience length\n",
    "el = []                                                       # initialization: el=experience_length\n",
    "for possibly_contains_el in temp:                             # for each sentence in temp\n",
    "    flag = 0                                                  # initialization: flag. see flag = 1\n",
    "    possibly_contains_el_lower = possibly_contains_el.lower() # lower case a word. So 'Two' becomes 'two'\n",
    "    for word in possibly_contains_el_lower.split():           # for each word. Ex: '1', 'Graduation', etc.\n",
    "        if word in number_dict.keys():                        # if word found in number_dict.keys(). Ex: 'one', 'two'\n",
    "            flag = 1                                          # signal that setence has been looped through\n",
    "            if 'year' in possibly_contains_el_lower:          # if 'year' is found,...\n",
    "                el.append(number_dict[word])                  # put in el\n",
    "            elif 'month' in possibly_contains_el_lower:\n",
    "                el.append(number_dict[word]/12)               # else, convert to fractions of year     \n",
    "    if not flag:                                              # if sentence has not been looped through...\n",
    "        el.append(None)                                       # append None because no key in number_dict can be found\n",
    "\n",
    "el"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None, 2, 2, 2, 2]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def experience_length(job):\n",
    "    '''Returns EXPERIENCE_LENGTH (el)'''\n",
    "    # Invoke _base function to obtain raw information\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_1' column\n",
    "    temp = list(temp['MINE_1'])\n",
    "    # Get length of experience based on cell above\n",
    "    el = []                                                       # initialization: el=experience_length\n",
    "    for possibly_contains_el in temp:                             # for each sentence in temp\n",
    "        flag = 0                                                  # initialization: flag. see flag = 1\n",
    "        possibly_contains_el_lower = possibly_contains_el.lower() # lower case a word. So 'Two' becomes 'two'\n",
    "        for word in possibly_contains_el_lower.split():           # for each word. Ex: '1', 'Graduation', etc.\n",
    "            if word in number_dict.keys():                        # if word found in number_dict. Ex: 'one', 'two'\n",
    "                flag = 1                                          # signal that setence has been looped through\n",
    "                if 'year' in possibly_contains_el_lower:          # if 'year' is found,...\n",
    "                    el.append(number_dict[word])                  # put in el\n",
    "                elif 'month' in possibly_contains_el_lower:\n",
    "                    el.append(number_dict[word]/12)               # else, convert to fractions of year     \n",
    "        if not flag:                                              # if sentence has not been looped through...\n",
    "            el.append(None)                                       # append None: no key in number_dict can be found\n",
    "    \n",
    "    # Returns\n",
    "    return el\n",
    "\n",
    "# Test\n",
    "experience_length(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get FULL_TIME_PART_TIME\n",
    "Isn't that \"annual\" means full time?\n",
    "\n",
    "<font color=red> NOOOOO!!! It's the required experience.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None, 'FULL-TIME', 'FULL-TIME', 'FULL-TIME', 'FULL-TIME']"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract a list of related sentences\n",
    "temp = [l[1] for l in r]\n",
    "# Get full_time/part_time\n",
    "ftpt = []                                                         # initialization: ftpt=FULL_TIME_PART_TIME\n",
    "for possibly_contains_ftpt in temp:                               # for each sentence in temp\n",
    "    possibly_contains_ftpt_lower = possibly_contains_ftpt.lower() # lower case to make sure things are right\n",
    "    if 'experience' in possibly_contains_ftpt_lower:              # only look for ones that have the word 'experience'\n",
    "        if 'full-time' in possibly_contains_ftpt_lower:           # if 'full-time' found...\n",
    "            ftpt.append('full-time'.upper())                      # append to ftpt\n",
    "        elif 'part-time' in possibly_contains_ftpt_lower:\n",
    "            ftpt.append('part-time'.upper())                      # elif 'part-time', append to ftpt\n",
    "    else:\n",
    "        ftpt.append(None)                                         # else: No experience required\n",
    "    \n",
    "ftpt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None, 'FULL-TIME', 'FULL-TIME', 'FULL-TIME', 'FULL-TIME']"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a functioin\n",
    "def full_time_part_time(job):\n",
    "    '''Returns FULL_TIME_PART_TIME (ftpt)'''\n",
    "    # Invoke _base function to know how many rows needed\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_1' column\n",
    "    temp = list(temp['MINE_1'])\n",
    "    # Get full_time/part_time based on cell above\n",
    "    ftpt = []                                                         # initialization: ftpt=FULL_TIME_PART_TIME\n",
    "    for possibly_contains_ftpt in temp:                               # for each sentence in temp\n",
    "        possibly_contains_ftpt_lower = possibly_contains_ftpt.lower() # lower case to make sure things are right\n",
    "        if 'experience' in possibly_contains_ftpt_lower:              # only search for 'experience'\n",
    "            if 'full-time' in possibly_contains_ftpt_lower:           # if 'full-time' found...\n",
    "                ftpt.append('full-time'.upper())                      # append to ftpt\n",
    "            elif 'part-time' in possibly_contains_ftpt_lower:\n",
    "                ftpt.append('part-time'.upper())                      # elif 'part-time', append to ftpt\n",
    "        else:\n",
    "            ftpt.append(None)                                         # else: No experience required\n",
    "    \n",
    "    # Returns\n",
    "    return ftpt\n",
    "\n",
    "# Test\n",
    "full_time_part_time(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get EXP_JOB_CLASS_TITLE\n",
    "See Notebook `Objective1_a`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "307\n",
      "296\n"
     ]
    }
   ],
   "source": [
    "exp_job_class_title_keywords = ['Body', 'Stenographer', 'Advisor', 'Specialist', 'Electrician', 'I', \n",
    "                                'Director', 'Occupational', 'Motor', 'Representative', 'Garage', 'Signal', \n",
    "                                'Manager', 'Painter', 'Marking', 'Vehicle', 'Auditor', 'Superintendent', \n",
    "                                'Management', 'Mate', 'Helicopter', 'Vessel', 'Personnel', 'IT', 'Processing', \n",
    "                                'Procurement', 'Control', 'Sales', 'Zoo', 'Coordinator', 'Geologist', 'Attendant', \n",
    "                                'Information', 'Examiner', 'Polygraph', 'Refuse', 'Laboratory', 'Stores', 'Steam', \n",
    "                                'Fleet', 'Fiscal', 'Claims', 'Quality', 'Community', 'Carpenter', 'Duty', \n",
    "                                'Applications', 'Press', 'Executive', 'Surveying', 'Sweeper', 'Technician', 'Port', \n",
    "                                'Instructor', 'Internal', 'Principal', 'Security', 'Machine', 'Recreation', \n",
    "                                'Compensation', 'Operating', 'Deck', 'Zoning', 'Distribution', 'Street', \n",
    "                                'Lieutenant', 'Operator', 'Commander', 'Water', 'Officer', 'Special', 'Detention', \n",
    "                                'Relations', 'Land', 'Maintenance', 'Auto', 'Trainee', 'Examiner', 'Typist', \n",
    "                                'Housing', 'Dispatcher', 'Aid', 'Craft', 'Treatment', 'Aide', 'Utility', 'General', \n",
    "                                'Controller', 'Fire', 'Assistant', 'IV', 'Heating', 'Collection', 'Wharfinger', \n",
    "                                'Health', 'Automotive', 'Supply', 'Messenger', 'Industrial', 'Departmental', \n",
    "                                'Laborer', 'Print', 'Window', 'Instrument', 'Construction', 'Plumbing', 'Commercial', \n",
    "                                'Cabinet', 'Accountant', 'Resources', 'Communications', 'Worker', 'Administrator', \n",
    "                                'Designer', 'Secretary', 'Keeper', 'Metal', 'V', 'Conditioning', 'Chief', 'Events', \n",
    "                                'Cartographer', 'Architectural', 'Waterworks', 'III', 'Field', 'Repairer', 'Project', \n",
    "                                'Polygraph', 'Repair', 'Drafting', 'Hand', 'Computer', 'Environmental', 'Electric', \n",
    "                                'Inspector', 'Estate', 'Programs', 'Custodian', 'Supervising', 'Technical', \n",
    "                                'Pumping', 'Starter', 'Material', 'Services', 'Bindery', 'Surveys', 'Education', \n",
    "                                'Research', 'Engineering', 'Technician', 'Environmental', 'Driver', 'Apparatus', \n",
    "                                'Real', 'Grounds', 'Truck', 'Accounting', 'Solid', 'Firefighter', 'Labor', \n",
    "                                'Geographic', 'Bus', 'Biologist', 'Machinist', 'Caretaker', 'Shovel', 'Pipefitter', \n",
    "                                'Division', 'Clerk', 'Fireboat', 'Gardener', 'Equipment', 'Development', \n",
    "                                'Compliance', 'Helper', 'Lot', 'Apprentice', 'II', 'Wastewater', 'Payroll', \n",
    "                                'Traffic', 'Poster', 'Load', 'Nurse', 'Pressure', 'Records', 'Service', 'Locksmith', \n",
    "                                'Building', 'Sheet', 'Boat', 'Captain', 'Party', 'Planner', 'Workers', 'Park', \n",
    "                                'Station', 'Compressor', 'Event', 'Structural', 'Reptiles', 'Sergeant', 'Airport', \n",
    "                                'Refrigeration', 'Delivery', 'Facility', 'Storekeeper', 'Public', 'Deputy', \n",
    "                                'Engineer', 'Administrative', 'Head', 'Lighting', 'Senior', 'Testing', 'Toolroom', \n",
    "                                'Civil', 'Worker', 'Airports', 'Rehabilitation', 'Power', 'Harbor', 'Cleaning', \n",
    "                                'Sign', 'Data', 'Meter', 'Transmission', 'System', 'Battalion', 'Librarian', \n",
    "                                'Warehouse', 'Bureau', 'Forensic', 'Builder', 'Elevators', 'Tax', 'Property', \n",
    "                                'Airports', 'Veterinary', 'Photographer', 'Curator', 'Elevator', 'Financial', \n",
    "                                'Light', 'Mechanic', 'Heavy', 'Irrigation', 'Telecommunications', 'Buyer', \n",
    "                                'Supervisor', 'Graphics', 'Cement', 'Materials', 'Plant', 'Reader', \n",
    "                                'Planning', 'District', 'Associate', 'Air', 'Parking', 'Building', 'Custodial', \n",
    "                                'Printing', 'Marketing', 'Transportation', 'Plumber', 'Detective', 'Police', \n",
    "                                'Ranger', 'Systems', 'Mechanical', 'Vessels', 'Birds', 'Managing', 'Duplicating', \n",
    "                                'Finisher', 'Analyst', 'Survey', 'Waste', 'Protection', 'Shop', \n",
    "                                'Commission', 'Cleaner', 'Electrical', 'Safety', 'Maker', 'Office', 'Golf', \n",
    "                                'Operations', 'Auditor', 'Senior', 'Operator', 'Senior', 'Sanitation', 'Line', \n",
    "                                'Crew', 'Surgeon', 'Animal', 'Title', 'Disposal', 'Tree', 'Care', 'Masonry', \n",
    "                                'Pilot', 'Programmer', 'Utilization']\n",
    "print(len(exp_job_class_title_keywords))\n",
    "exp_job_class_title_keywords = list(set(exp_job_class_title_keywords))\n",
    "print(len(exp_job_class_title_keywords))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "99\n",
      "94\n"
     ]
    }
   ],
   "source": [
    "occupations = ['Stenographer', 'Advisor', 'Specialist', 'Electrician', 'Director', 'Representative', 'Manager', \n",
    "               'Painter', 'Auditor', 'Superintendent', 'Hand', 'Coordinator', 'Geologist', 'Lieutenant', 'Reptiles',\n",
    "               'Attendant', 'Examiner', 'Carpenter', 'Personnel', 'Technician', 'Instructor', 'Vessels', 'Vessel',\n",
    "               'Security', 'Operator', 'Commander', 'Officer', 'Trainee', 'Examiner', 'Typist', 'Dispatcher', 'Aide', \n",
    "               'Controller', 'Assistant', 'Wharfinger', 'Messenger', 'Laborer', 'Accountant', 'Worker', 'Operations',\n",
    "               'Administrator', 'Designer', 'Secretary', 'Keeper', 'Cartographer', 'Architect', 'Repairer', 'Aid',\n",
    "               'Inspector', 'Starter', 'Technician', 'Driver', 'Firefighter', 'Biologist', 'Machinist', 'Caretaker', \n",
    "               'Pipefitter', 'Clerk', 'Gardener', 'Helper', 'Apprentice', 'Poster', 'Nurse', 'Locksmith', 'Captain', \n",
    "               'Planner', 'Sergeant', 'Storekeeper', 'Deputy', 'Engineer', 'Worker', 'Elevator', 'Elevators', 'Birds',\n",
    "               'Librarian', 'Builder', 'Photographer', 'Curator', 'Mechanic', 'Buyer', 'Supervisor', \n",
    "               'Reader', 'Associate', 'Custodian', 'Plumber', 'Detective', 'Police', 'Ranger', 'Finisher', 'Analyst',  \n",
    "               'Cleaner', 'Safety', 'Maker', 'Auditor', 'Operator', 'Senior', 'Surgeon', 'Pilot', 'Programmer'\n",
    "              ]\n",
    "print(len(occupations))\n",
    "# Drop duplicates\n",
    "occupations = list(set(occupations))\n",
    "print(len(occupations))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "seniority_levels = ['I', 'II', 'III', 'IV', 'V']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None,\n",
       " 'Management Assistant',\n",
       " 'Management Assistant',\n",
       " 'Management Assistant',\n",
       " 'Systems Aide']"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get title of the job class that requires experience associated with\n",
    "\n",
    "## Extract a list of related sentences\n",
    "temp = [l[1] for l in r]\n",
    "## Main code for getting title\n",
    "ejct = []                                                        # initialization: ejct=experience_job_class_title\n",
    "for possibly_contains_ejct in temp:                              # for each sentence in temp\n",
    "    # Fill all relevant words in a container l\n",
    "    l = []                                                       # initialize a container that holds relevant words in title\n",
    "    flag = 0                                                     # initialize a flag. see flag = 1\n",
    "    possibly_contains_ejct = (possibly_contains_ejct             # strip off unwanted symbos\n",
    "                              .replace('.', '')                  # is there a way to do this in one line?\n",
    "                              .replace(',', '')\n",
    "                              .replace(';', '')\n",
    "                              .replace(':', '')\n",
    "                             )\n",
    "    for word in possibly_contains_ejct.split():                  # for each word in the splitter. Ex: '1', 'Graduation'\n",
    "        if word in exp_job_class_title_keywords:                 # if word is in list of keywords. Ex: Systems, Information\n",
    "            l.append(word)                                       # then append to l\n",
    "    # Analyze list l and get title\n",
    "    temp_list = list(l); temp_list.reverse()                     # reverse l. list(l) for actual copy since reverse mutates l\n",
    "    for possibly_an_occupation in temp_list:                     # for each word in temp_list. Ex: 'Assitant', 'Management'\n",
    "        if possibly_an_occupation in occupations:                # see if that word is in the exhausted list of occupations\n",
    "            flag = 1                                             # if it is, change flag to signal that\n",
    "            title = l[:l.index(possibly_an_occupation)+1]        # get title. Ex: ['Management', 'Assitant']. Need +1.\n",
    "            seniority  = [s for s in l if s in seniority_levels] # get seniority. Ex: 'I', 'II'\n",
    "            full_title = ' '.join(title + seniority)             # get full title: title and seniority\n",
    "            ejct.append(full_title)                              # append full title to ejct\n",
    "        else:\n",
    "            continue                                             # otherwise, jump to the next word\n",
    "    if not flag:\n",
    "        ejct.append(None)                                        # if never found an occupation, then None for no occupation\n",
    "\n",
    "ejct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None,\n",
       " 'Management Assistant',\n",
       " 'Management Assistant',\n",
       " 'Management Assistant',\n",
       " 'Systems Aide']"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def exp_job_class_title(job):\n",
    "    '''Returns EXP_JOB_CLASS_TITLE (ejct)'''\n",
    "    # Invoke _base function to know how many rows needed\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_1' column\n",
    "    temp = list(temp['MINE_1'])\n",
    "    # Get title of the job class based on code above\n",
    "    ejct = []                                                        # initialization: ejct=experience_job_class_title\n",
    "    for possibly_contains_ejct in temp:                              # for each sentence in temp\n",
    "        # Fill all relevant words in a container l\n",
    "        l = []                                                       # initialize container that holds relevant words in title\n",
    "        flag = 0                                                     # initialize a flag. see flag = 1\n",
    "        possibly_contains_ejct = (possibly_contains_ejct.            # strip off unwanted symbols\n",
    "                                  replace('.', '').\n",
    "                                  replace(',', '').\n",
    "                                  replace(';', '').\n",
    "                                  replace(':', ''))                   \n",
    "        for word in possibly_contains_ejct.split():                  # for each word in the splitter. Ex: '1', 'Graduation'\n",
    "            if word in exp_job_class_title_keywords:                 # if word is in list of keywords. Ex: Systems, Information\n",
    "                l.append(word)                                       # then append to l\n",
    "        # Analyze list l and get title\n",
    "        temp_list = list(l); temp_list.reverse()                     # list(l) for actual copy since reverse mutates l\n",
    "        for possibly_an_occupation in temp_list:                     # for each word in temp_list. Ex: 'Assitant', 'Management'\n",
    "            if possibly_an_occupation in occupations:                # see if that word is in the exhausted list of occupations\n",
    "                flag = 1                                             # if it is, change flag to signal that\n",
    "                title = l[:l.index(possibly_an_occupation)+1]        # get title. Ex: ['Management', 'Assitant']. Need +1.\n",
    "                seniority  = [s for s in l if s in seniority_levels] # get seniority. Ex: 'I', 'II'\n",
    "                full_title = ' '.join(title + seniority)             # get full title: title and seniority\n",
    "                ejct.append(full_title)                              # append full title to ejct\n",
    "            else:\n",
    "                continue                                             # otherwise, jump to the next word\n",
    "        if not flag:\n",
    "            ejct.append(None)                                        # never found an occupation, then None for no occupation\n",
    "    \n",
    "    # Returns\n",
    "    return ejct\n",
    "\n",
    "# Test\n",
    "exp_job_class_title(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get EXP_JOB_CLASS_ALT_RESP\n",
    "I have no idea how it would look like since Systems Analyst doesn't have it (see annotations), so, just return `None` for now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None, None, None, None, None]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def exp_job_class_alt_resp(job):\n",
    "    '''Returns EXP_JOB_CLASS_ALT_RESP (ejcar)'''\n",
    "    # Invoke _base function to know how many rows needed\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_1' column\n",
    "    temp = list(temp['MINE_1'])\n",
    "    \n",
    "    n = len(temp)\n",
    "    ejcar = [None]*n\n",
    "    \n",
    "    return ejcar\n",
    "\n",
    "# Test\n",
    "exp_job_class_alt_resp(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get EXP_JOB_CLASS_FUNCTION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None,\n",
       " 'the development, analysis, implementation or major modification of new or existing computer-based information systems or relational databases; or',\n",
       " 'performing cost benefit, feasibility and requirements analysis for a large-scale computer-based information system; or',\n",
       " 'performing system implementation and support activities including software and hardware acquisition, installation, modifications to system configuration, system and application upgrade installation; or',\n",
       " None]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def exp_job_class_function(job):\n",
    "    '''Returns EXP_JOB_CLASS_FUNCTION (ejcf)'''\n",
    "    # Invoke _base function to know how many rows needed\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees.\n",
    "    temp = list(temp['EXP_JOB_CLASS_FUNCTION'])\n",
    "    \n",
    "    # Returns\n",
    "    return temp\n",
    "\n",
    "# Test\n",
    "exp_job_class_function(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get COURSE_COUNT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None, None, None, None, 4]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Define a function\n",
    "def course_count(job):\n",
    "    '''Returns COURSE_COUNT (cc)'''\n",
    "    # Invoke _base function to know how many rows needed\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_2' column\n",
    "    temp = list(temp['MINE_2'])\n",
    "    # Main code for getting the counts of courses\n",
    "    cc = []                                                     # intialization: cc=course_count\n",
    "    for possibly_contains_cc in temp:                           # for each sentence. Ex: None, 'Satisfactory...'\n",
    "        flag = 0                                                # Create a flag. see flag=1\n",
    "        if possibly_contains_cc!=None:                          # if sentence is not None, so can be stripped. \n",
    "                                                                # avoid using try/except\n",
    "            possibly_contains_cc = (possibly_contains_cc.       # strip off unwanted symbols\n",
    "                                    replace('.', '').\n",
    "                                    replace(',', '').\n",
    "                                    replace(';', '').\n",
    "                                    replace(':', ''))\n",
    "            possibly_contains_cc = possibly_contains_cc.split() # split the sentence...\n",
    "            if 'courses' in possibly_contains_cc:               # to look for the word courses\n",
    "                flag = 1                                        # if there is, flag to signal\n",
    "                for word in possibly_contains_cc:               # for each word in splitted sentence\n",
    "                    if word in number_dict.keys():              # if that word can be found in number_dict\n",
    "                        cc.append(number_dict[word])            # append to cc...\n",
    "                        break                                   # and then break loop because of, e.g, three semesters\n",
    "        if not flag:\n",
    "            cc.append(None)                                     # append 0 if never flagged\n",
    "            \n",
    "    # Returns\n",
    "    return cc\n",
    "\n",
    "# Test\n",
    "course_count(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get COURSE_LENGTH"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['3S|4Q']\n"
     ]
    }
   ],
   "source": [
    "temp = [r[4][4]]\n",
    "cl = []\n",
    "for possibly_contains_cl in temp:\n",
    "    flag = 0\n",
    "    if possibly_contains_cl!=None:\n",
    "        possibly_contains_cl = (possibly_contains_cl. \n",
    "                        replace('.', '').\n",
    "                        replace(',', '').\n",
    "                        replace(';', '').\n",
    "                        replace(':', ''))\n",
    "        possibly_contains_cl = possibly_contains_cl.split()\n",
    "        if ('semester' in possibly_contains_cl) | ('quarter' in possibly_contains_cl):\n",
    "            flag = 1\n",
    "            l    = []\n",
    "            semester_index = possibly_contains_cl.index('semester')\n",
    "            quarter_index  = possibly_contains_cl.index('quarter')\n",
    "            relevant_info  = possibly_contains_cl[semester_index-2:quarter_index+2] # 2 just to make sure\n",
    "            for word in relevant_info:\n",
    "                if word in number_dict.keys():\n",
    "                    l.append(number_dict[word])\n",
    "            cl.append('|'.join([str(l[0])+'S', str(l[1])+'Q']))\n",
    "            print(cl)\n",
    "    \n",
    "    if not flag:\n",
    "        cl.append(None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None, None, None, None, '3S|4Q']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def course_length(job):\n",
    "    '''Returns COURSE_LENGTH (cl)'''\n",
    "    # Invoke _base function to know how many rows needed\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_2' column\n",
    "    temp = list(temp['MINE_2'])\n",
    "    # Main code for getting the length of courses\n",
    "    cl = []\n",
    "    for possibly_contains_cl in temp:\n",
    "        flag = 0\n",
    "        if possibly_contains_cl!=None:\n",
    "            possibly_contains_cl = (possibly_contains_cl. \n",
    "                            replace('.', '').\n",
    "                            replace(',', '').\n",
    "                            replace(';', '').\n",
    "                            replace(':', ''))\n",
    "            possibly_contains_cl = possibly_contains_cl.split()\n",
    "            if ('semester' in possibly_contains_cl) | ('quarter' in possibly_contains_cl):\n",
    "                flag = 1\n",
    "                l    = []\n",
    "                semester_index = possibly_contains_cl.index('semester')\n",
    "                quarter_index  = possibly_contains_cl.index('quarter')\n",
    "                relevant_info  = possibly_contains_cl[semester_index-2:quarter_index+2] # 2 just to make sure\n",
    "                for word in relevant_info:\n",
    "                    if word in number_dict.keys():\n",
    "                        l.append(number_dict[word])\n",
    "                cl.append('|'.join([str(l[0])+'S', str(l[1])+'Q']))\n",
    "\n",
    "        if not flag:\n",
    "            cl.append(None)\n",
    "    # Returns\n",
    "    return cl\n",
    "# Test\n",
    "course_length(cl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get COURSE_SUBJECT\n",
    "The approach is very similar to setting up EDUCATION_MAJORS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "courses = ['information systems', 'systems analysis'] # there are MANY jobs with lower case courses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['INFORMATION SYSTEMS|SYSTEMS ANALYSIS|closely related degree program professional designation or certificate program from an accredited college or university']"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = [r[4][4]]\n",
    "cs = []\n",
    "for possibly_contains_cs in temp:\n",
    "    flag = 0\n",
    "    all_types_of_courses = []\n",
    "    if possibly_contains_cs != None:\n",
    "        possibly_contains_cs = (possibly_contains_cs. \n",
    "                                replace('.', '').\n",
    "                                replace(',', '').\n",
    "                                replace(';', '').\n",
    "                                replace(':', ''))\n",
    "        possibly_contains_cs = possibly_contains_cs.lower()\n",
    "        if 'courses' in possibly_contains_cs.split():\n",
    "            flag = 1\n",
    "            for course_name in courses:\n",
    "                if possibly_contains_cs.find(course_name) != -1:\n",
    "                    all_types_of_courses.append(course_name.upper())\n",
    "            if 'closely' in possibly_contains_cs.split():\n",
    "                possibly_contains_cs = possibly_contains_cs.split()\n",
    "                mask = possibly_contains_cs.index('closely')\n",
    "                all_types_of_courses.append(' '.join(possibly_contains_cs[mask:]))\n",
    "            if len(all_types_of_courses) > 0:\n",
    "                cs.append('|'.join(all_types_of_courses))\n",
    "    if not flag:\n",
    "        cs.append(None)\n",
    "        \n",
    "cs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None,\n",
       " None,\n",
       " None,\n",
       " None,\n",
       " 'INFORMATION SYSTEMS|SYSTEMS ANALYSIS|closely related degree program professional designation or certificate program from an accredited college or university']"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def course_subject(job):\n",
    "    '''Returns COURSE_SUBJECT (cs)'''\n",
    "    # Invoke _base function to know how many rows needed\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_2' column\n",
    "    temp = list(temp['MINE_2'])\n",
    "    # Main code for getting the subjects of courses\n",
    "    cs = []\n",
    "    for possibly_contains_cs in temp:\n",
    "        flag = 0\n",
    "        all_types_of_courses = []\n",
    "        if possibly_contains_cs != None:\n",
    "            possibly_contains_cs = (possibly_contains_cs. \n",
    "                                    replace('.', '').\n",
    "                                    replace(',', '').\n",
    "                                    replace(';', '').\n",
    "                                    replace(':', ''))\n",
    "            possibly_contains_cs = possibly_contains_cs.lower()\n",
    "            if 'courses' in possibly_contains_cs.split():\n",
    "                flag = 1\n",
    "                for course_name in courses:\n",
    "                    if possibly_contains_cs.find(course_name) != -1:\n",
    "                        all_types_of_courses.append(course_name.upper())\n",
    "                if 'closely' in possibly_contains_cs.split():\n",
    "                    possibly_contains_cs = possibly_contains_cs.split()\n",
    "                    mask = possibly_contains_cs.index('closely')\n",
    "                    all_types_of_courses.append(' '.join(possibly_contains_cs[mask:]))\n",
    "                if len(all_types_of_courses) > 0:\n",
    "                    cs.append('|'.join(all_types_of_courses))\n",
    "        if not flag:\n",
    "            cs.append(None)\n",
    "        \n",
    "    # Returns\n",
    "    return cs\n",
    "\n",
    "# Test\n",
    "course_subject(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get MISC_COURSE_DETAILS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None,\n",
       " None,\n",
       " None,\n",
       " None,\n",
       " 'At least three of the courses must be from the core courses required in the program, and one course may be from either the required core courses or the prescribed elective courses of the program.  A course in systems analysis and design is especially desired, but not required. ']"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def misc_course_details( job):\n",
    "    '''Returns MISC_COURSE_DETAILS (mcd)'''\n",
    "    # Invoke _base function to know how many rows needed\n",
    "    temp = _base(job)\n",
    "    # Extract a list of related sentencees. This information is in the 'MINE_2' column\n",
    "    temp = list(temp['MISC'])\n",
    "    \n",
    "    # Returns\n",
    "    return temp\n",
    "\n",
    "# Test\n",
    "misc_course_details(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get DRIVERS_LICENSE_REQ\n",
    "This is quite tricky because sometimes, this information is given at the end of the job description."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1. Some positions may require a valid California driver's license. Candidates may not be eligible for appointment to these positions if their record within the last 36 months reflects three or more moving violations and/or at-fault accidents, or a conviction of a major moving violation (such as DUI).\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['P']"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Locate the information\n",
    "temp = sa[sa.find('PROCESS NOTES'):]\n",
    "temp = temp.split('\\n')\n",
    "\n",
    "# Determine whether driver license is required for the job\n",
    "# The approach I took here was to look at each sentence based on their index in temp.\n",
    "dlr = []\n",
    "for idx in range(len(temp)):\n",
    "    if 'driver' in temp[idx]:\n",
    "        print(temp[idx])\n",
    "        splitted_sentence = temp[idx].split()\n",
    "        if 'require' in splitted_sentence:\n",
    "            dlr.append('P')\n",
    "        elif 'required' in splitted_sentence:\n",
    "            dlr.append('R')\n",
    "        break\n",
    "\n",
    "dlr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['P']"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def drivers_license_req(job):\n",
    "    '''Returns DRIVERS_LICENSE_REQ(dlr)'''\n",
    "    # Locate the information\n",
    "    temp = sa[sa.find('PROCESS NOTES'):]\n",
    "    temp = temp.split('\\n')\n",
    "\n",
    "    # Determine whether driver license is required for the job\n",
    "    # The approach I took here was to look at each sentence based on their index in temp.\n",
    "    dlr = []\n",
    "    for idx in range(len(temp)):\n",
    "        if 'driver' in temp[idx]:\n",
    "            splitted_sentence = temp[idx].split()\n",
    "            if 'require' in splitted_sentence:\n",
    "                dlr.append('P')\n",
    "            elif 'required' in splitted_sentence:\n",
    "                dlr.append('R')\n",
    "            break\n",
    "    \n",
    "    # Returns\n",
    "    return dlr\n",
    "\n",
    "# Test\n",
    "drivers_license_req(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get DRIV_LIC_TYPE\n",
    "* This depends on the information mined from DRIVERS_LICENSE_REQ, so first we have to invoke that function.\n",
    "* For now, I just want to take it easy. Thus the function below will need to be modified to accomodate, for example, `TRUCK OPERATOR 3583 012618.txt`. Hopefully, this will not be too difficult."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def driv_lic_type(job):\n",
    "    '''Returns DRIVE_LIC_TYPE (dlt)'''\n",
    "    # Invoke drivers_license_req to obtain relevant information\n",
    "    temp = drivers_license_req(job)\n",
    "    # Get type of driver license based on the information \n",
    "    dlt=[]\n",
    "    if temp[0]=='P':\n",
    "        dlt.append(None)\n",
    "    else:\n",
    "        pass\n",
    "    \n",
    "    # Returns\n",
    "    return dlt\n",
    "\n",
    "# Test\n",
    "driv_lic_type(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get ADDTL_LIC\n",
    "For now, just give Nones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[None]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def addtl_lic(job):\n",
    "    '''Returns ADDTL_LIC (al)'''\n",
    "    al = []\n",
    "    al.append(None)\n",
    "    \n",
    "    return al\n",
    "\n",
    "# Test\n",
    "addtl_lic(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get EXAM_TYPE\n",
    "We have two options:\n",
    "* Copy the code on Kaggle. I remember there was a guy who did a very good job at this, but used regular expression. The downside of this option is that I have to learn what he wrote. I got stuck in writing my own function, so here's the link [danielbecker](https://www.kaggle.com/danielbecker/l-a-jobs-data-exctraction-eda).\n",
    "* Write my own function. The upside of this option is that it's fairly easy to understand.\n",
    "\n",
    "Let's use Kaggle because I'm too tired now. Actually, it's quite similar to option 1. Fundamentally, it uses a dictionary to look up the key words. The difference is, on the other hand, reg ex manipulations rather than string manipulations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['OPEN_INT_PROM']"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def exam_type(job):\n",
    "    '''\n",
    "    Returns EXAM_TYPE (et). \n",
    "    This code is borrowed from Daniel Becker on Kaggle, with some variables' renaming \n",
    "    to fit into my code style for this project.\n",
    "    ''' \n",
    "    regex_dic = {'OPEN_INT_PROM':r'BOTH.*INTERDEPARTMENTAL.*PROMOTIONAL', \n",
    "                 'INT_DEPT_PROM':r'INTERDEPARTMENTAL.*PROMOTIONAL', \n",
    "                 'DEPT_PROM':r'DEPARTMENTAL.*PROMOTIONAL',\n",
    "                 'OPEN':r'OPEN.*COMPETITIVE.*BASIS'\n",
    "                }\n",
    "    et = []\n",
    "    for key, value in regex_dic.items():\n",
    "        regex = value\n",
    "        regex_find = re.findall(regex, job, re.DOTALL)\n",
    "        if regex_find:\n",
    "            et.append(key)\n",
    "            break\n",
    "    return et\n",
    "\n",
    "# Test\n",
    "exam_type(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get ENTRY_SALARY_GEN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['68,611', '100,307', '70,908', '88,092', '83,770', '104,065']\n",
      "[]\n"
     ]
    }
   ],
   "source": [
    "# Locate the info location\n",
    "temp = sa[sa.find('ANNUAL SALARY'):sa.find('NOTES')]\n",
    "# Next, we'll replace '.' and '$' with white spaces, before splitting this string at white space.\n",
    "# Luckily, we can chain these operations for readability.\n",
    "temp = temp.replace('.', ' ').replace('$', ' ').split()\n",
    "# Finally, let's get the salary range using the same approach that isolated\n",
    "# job_titles. Per Bob's suggestion, let's have a catch list manual_check.\n",
    "salary_range = []\n",
    "manual_check = []\n",
    "for word in temp:\n",
    "    ## Some jobs are like this: $##,### to $##,###, $##,### to $##,###.\n",
    "    ## Also, watch out for ROOFER!\n",
    "    if (',' in word):\n",
    "        try: # This is like an extra layer of checking: avoiding, e.g., \"t,o\"\n",
    "            int(word.replace(',', ''))\n",
    "            salary_range.append(word)\n",
    "        except:\n",
    "            manual_check.append(word)\n",
    "print(salary_range)\n",
    "print(manual_check)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['68,611', '100,307', '70,908', '88,092', '83,770', '104,065']\n"
     ]
    }
   ],
   "source": [
    "# Define functions\n",
    "# Need to modify to account for flat-rated salary (ROOFER)\n",
    "def get_all_salaries(job):\n",
    "    '''Returns all salaries that can be found'''\n",
    "    # From ANNUAL SALARY to NOTES is where the information located\n",
    "    temp = job[job.find('ANNUAL SALARY'):job.find('NOTES')]\n",
    "    # Next, we'll replace '.' and '$' with white spaces, before splitting this string at white space.\n",
    "    # Luckily, we can chain these operations for readability.\n",
    "    temp = temp.replace('.', ' ').replace('$', ' ').split()\n",
    "    # Finally, get the salary range by trying to convert each word in temp into an integer\n",
    "    salary_range = []\n",
    "    for word in temp:\n",
    "        ## Some jobs are like this: $##,### to $##,###, $##,### to $##,###.\n",
    "        ## Also, watch out for ROOFER!\n",
    "        if (',' in word):\n",
    "            try: # This is like an extra layer of checking: avoiding, e.g., \"t,o\"\n",
    "                int(word.replace(',', ''))\n",
    "                salary_range.append(word)\n",
    "            except:\n",
    "                manual_check.append(word)\n",
    "    \n",
    "    return salary_range\n",
    "\n",
    "# Test\n",
    "print(get_all_salaries(job=sa))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that this approach doesn't really work because we have flat-rated salary, general entry level salary ranges, and DWP (Department of Water and Power) salary ranges, while the dataframe requirement is to select only the first listed salary range. Thus, we need to define functions and write very simple but error-traceable code, even though this may mean a lot of repetitions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a helper function\n",
    "def _get_salary(salary_text):\n",
    "    '''Returns job's salary in the form of $#####-$#####, $##### (flat-rated)'''\n",
    "    # The idea is to use isdigit() function to recognize a number. So need to strip off everthing that fails this.\n",
    "    # Replace '.' with white space. This resolves '#####.' (dot at the end)\n",
    "    # Replace '$' with white space. This resolves '$#####' (dollar sign in the beginning)\n",
    "    # Replace ',' with empty space. This resolves '$##,###' (comma in the middle of the number)\n",
    "    # Empty space because we will split at white space later\n",
    "    temp = salary_text.replace('.', ' ').replace('$', ' ').replace(',', '')\n",
    "    \n",
    "    # Get salaries in temp by using the isdigit() function. \n",
    "    salary_range = []\n",
    "    for word in temp.split():      # split here\n",
    "        if len(salary_range) >= 2: # break to make sure that only the first listed salary range is included\n",
    "            break\n",
    "        else:                      # otherwise, put it in the salary_range list\n",
    "            if word.isdigit():\n",
    "                salary_range.append(word)\n",
    "    \n",
    "    # Returns the required format\n",
    "    return '-'.join(salary_range)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'68611-100307'"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now use helper function to get ENTRY_SALARY_GEN\n",
    "def entry_salary_gen(job):\n",
    "    '''Returns ENTRY_SALARY_GEN (esg)'''\n",
    "    # From ANNUAL SALARY to Department of Water and Power is where the information located\n",
    "    # Instead of find, I switch to index to take advantage of try/except later.\n",
    "    temp = job[job.index('ANNUAL SALARY'):job.index('Department of Water and Power')]\n",
    "    \n",
    "    # Returns\n",
    "    esg = _get_salary(salary_text=temp)\n",
    "    return esg\n",
    "\n",
    "# Test\n",
    "entry_salary_gen(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get ENTRY_SALARY_DWP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'70908-88092'"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now use helper function to get ENTRY_SALARY_DWP\n",
    "def entry_salary_dwp(job):\n",
    "    '''Returns ENTRY_SALARY_DWP (esd)'''\n",
    "    # From Department of Water and Power to NOTES is where the information located\n",
    "    # Instead of find, switch to index to take advantage of try/except later.\n",
    "    temp = job[job.index('Department of Water and Power'):job.index('NOTES')]\n",
    "    \n",
    "    # Returns\n",
    "    esd = _get_salary(salary_text=temp)\n",
    "    return esd\n",
    "\n",
    "# Test\n",
    "entry_salary_dwp(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: Later on, when filling out the dataframe, we'll use `try/except/finally` (try the specific function, except then use get_all_salaries(), finally then return an null value."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get OPEN_DATE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'10-27-17'"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = sa[sa.find('Open Date'):sa.find('(')]\n",
    "od   = temp.split()[-1]\n",
    "od"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'10-27-17'"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define a function\n",
    "def open_date(job):\n",
    "    '''Returns OPEN_DATE (od)'''\n",
    "    # Open Date: 10-27-17\\n(Exam Open to All\n",
    "    # From Open Date to the first '(' is where the information located\n",
    "    temp = job[job.find('Open Date'):job.find('(')]\n",
    "    # Get the last element\n",
    "    od   = temp.split()[-1]\n",
    "    \n",
    "    return od\n",
    "\n",
    "# Test\n",
    "open_date(job=sa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='red'> ~~TO BE CONTINUED~~</font>. <font color='green'>DONE ON Sunday, 6/2/2019.</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}