1_etl-text.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9c483d5f",
   "metadata": {},
   "source": [
    "<span style=\"font-size: 120%\">Obtain required project data. Identify then extract, transform, load the relevant\n",
    "HTML sections of the job postings for further analysis.</span>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5e1aca52",
   "metadata": {},
   "outputs": [],
   "source": [
    "import bs4\n",
    "import pandas  as pd\n",
    "import pathlib as pl\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f0ac7431",
   "metadata": {
    "scrolled": true
   },
   "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>title</th>\n",
       "      <th>body</th>\n",
       "      <th>li_elems</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Staff Software Engineer - Security - Redwood C...</td>\n",
       "      <td>Staff Software Engineer - Security - Redwood C...</td>\n",
       "      <td>[Design and develop platform security solution...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Data Analyst, Customer Support - Boston, MA 02210</td>\n",
       "      <td>Data Analyst, Customer Support - Boston, MA 02...</td>\n",
       "      <td>[Work with the Customer Support teams to unify...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SQL Database Administrator - San Francisco, CA...</td>\n",
       "      <td>SQL Database Administrator - San Francisco, CA...</td>\n",
       "      <td>[Monitors capacity and performance for product...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Senior Data Scientist - Risk Mitigation - Jers...</td>\n",
       "      <td>Senior Data Scientist - Risk Mitigation - Jers...</td>\n",
       "      <td>[3+ years of experience crafting, developing, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Personal Care Assistant - Yuba City, CA</td>\n",
       "      <td>Personal Care Assistant - Yuba City, CA\\nKNOWL...</td>\n",
       "      <td>[Actively participates in the resident's plan ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1332</th>\n",
       "      <td>Data Scientist, Models and Algorithms - San Fr...</td>\n",
       "      <td>Data Scientist, Models and Algorithms - San Fr...</td>\n",
       "      <td>[Risk and Fraud - Gusto processes &gt;$10B of pay...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1333</th>\n",
       "      <td>Data Scientist - New York, NY</td>\n",
       "      <td>Data Scientist - New York, NY\\nDS/ML stack:\\nL...</td>\n",
       "      <td>[Languages: Python, PySpark, SQL, Data Tools: ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1334</th>\n",
       "      <td>Business Planning and Operations Associate - S...</td>\n",
       "      <td>Business Planning and Operations Associate - S...</td>\n",
       "      <td>[Use qualitative and quantitative inputs to pa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1335</th>\n",
       "      <td>Capital Work Readiness Analyst (Career) - San ...</td>\n",
       "      <td>Capital Work Readiness Analyst (Career) - San ...</td>\n",
       "      <td>[Communicates with peers and management inside...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1336</th>\n",
       "      <td>SENIOR DATA SCIENTIST - Burlington, VT 05401</td>\n",
       "      <td>SENIOR DATA SCIENTIST - Burlington, VT 05401\\n...</td>\n",
       "      <td>[Design and execute quantitative analyses to e...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1337 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  title  \\\n",
       "0     Staff Software Engineer - Security - Redwood C...   \n",
       "1     Data Analyst, Customer Support - Boston, MA 02210   \n",
       "2     SQL Database Administrator - San Francisco, CA...   \n",
       "3     Senior Data Scientist - Risk Mitigation - Jers...   \n",
       "4               Personal Care Assistant - Yuba City, CA   \n",
       "...                                                 ...   \n",
       "1332  Data Scientist, Models and Algorithms - San Fr...   \n",
       "1333                      Data Scientist - New York, NY   \n",
       "1334  Business Planning and Operations Associate - S...   \n",
       "1335  Capital Work Readiness Analyst (Career) - San ...   \n",
       "1336       SENIOR DATA SCIENTIST - Burlington, VT 05401   \n",
       "\n",
       "                                                   body  \\\n",
       "0     Staff Software Engineer - Security - Redwood C...   \n",
       "1     Data Analyst, Customer Support - Boston, MA 02...   \n",
       "2     SQL Database Administrator - San Francisco, CA...   \n",
       "3     Senior Data Scientist - Risk Mitigation - Jers...   \n",
       "4     Personal Care Assistant - Yuba City, CA\\nKNOWL...   \n",
       "...                                                 ...   \n",
       "1332  Data Scientist, Models and Algorithms - San Fr...   \n",
       "1333  Data Scientist - New York, NY\\nDS/ML stack:\\nL...   \n",
       "1334  Business Planning and Operations Associate - S...   \n",
       "1335  Capital Work Readiness Analyst (Career) - San ...   \n",
       "1336  SENIOR DATA SCIENTIST - Burlington, VT 05401\\n...   \n",
       "\n",
       "                                               li_elems  \n",
       "0     [Design and develop platform security solution...  \n",
       "1     [Work with the Customer Support teams to unify...  \n",
       "2     [Monitors capacity and performance for product...  \n",
       "3     [3+ years of experience crafting, developing, ...  \n",
       "4     [Actively participates in the resident's plan ...  \n",
       "...                                                 ...  \n",
       "1332  [Risk and Fraud - Gusto processes >$10B of pay...  \n",
       "1333  [Languages: Python, PySpark, SQL, Data Tools: ...  \n",
       "1334  [Use qualitative and quantitative inputs to pa...  \n",
       "1335  [Communicates with peers and management inside...  \n",
       "1336  [Design and execute quantitative analyses to e...  \n",
       "\n",
       "[1337 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load HTML, store relevant HTML sections in pandas data frame\n",
    "\n",
    "def parse_sections(html):\n",
    "    soup = bs4.BeautifulSoup(html, 'lxml')\n",
    "    title = soup.title.text.strip()\n",
    "    body = soup.body.text.strip()\n",
    "    li_elems = [ li.text.strip() for li in soup.find_all('li') ]\n",
    "    return (title, body, li_elems)\n",
    "\n",
    "parsed_pages = []\n",
    "for path in pl.Path('data/html_job_postings/').glob('*'):\n",
    "    file = open(path)\n",
    "    raw_page = file.read()\n",
    "    row = parse_sections(raw_page)\n",
    "    parsed_pages.append(row)\n",
    "    file.close()\n",
    "\n",
    "jobs_df = pd.DataFrame(parsed_pages, columns=['title', 'body', 'li_elems'])\n",
    "jobs_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1875ab06",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1337, 3)\n",
      "(497, 3)\n",
      "(492, 3)\n"
     ]
    }
   ],
   "source": [
    "# data cleaning (non-exhaustive)\n",
    "# - remove duplicates (trivially)\n",
    "# - filter for data science jobs given presence of non data science roles \n",
    "#   in the dataset: analyst, database admin, etc.\n",
    "\n",
    "# additionally, more extensive cleaning would involve filtering for li elements \n",
    "# containing data about job benefits and other things not related to job requirements\n",
    "\n",
    "print(jobs_df.shape)\n",
    "\n",
    "jobs_df = (jobs_df\n",
    "    .query('title.str.contains(r\"data scien(?:ce|tist)\", case=False)')\n",
    "    .copy())\n",
    "print(jobs_df.shape)\n",
    "\n",
    "jobs_df['li_elems'] = jobs_df['li_elems'].apply(tuple)\n",
    "jobs_df = jobs_df.drop_duplicates()\n",
    "print(jobs_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9425fe26",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3       Senior Data Scientist - Risk Mitigation - Jers...\n",
       "9       Director, Data Scientist, Natural Language Pro...\n",
       "10        eCommerce Senior Data Scientist - United States\n",
       "11      Associate Scientist, Data Science - Irving, TX...\n",
       "12                           Data Scientist - Seattle, WA\n",
       "                              ...                        \n",
       "1329    Data Science Intern (BS / MS) - Intern - Sprin...\n",
       "1330              PhD Data Science Intern - San Diego, CA\n",
       "1332    Data Scientist, Models and Algorithms - San Fr...\n",
       "1333                        Data Scientist - New York, NY\n",
       "1336         SENIOR DATA SCIENTIST - Burlington, VT 05401\n",
       "Name: title, Length: 492, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jobs_df['title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "db4ad336",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save data frame for subsequent notebooks\n",
    "jobs_df.to_pickle('data/jobs_df.pkl', compression='zip')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.6.9"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}