a_data_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Forecasting AI and ML Job Trends\n",
    "\n",
    "Here, we perform **Exploratory Data Analysys & Preprocessing**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from tabulate import tabulate\n",
    "\n",
    "import kagglehub"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataset Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Path to dataset files: /Users/mzitoh/.cache/kagglehub/datasets/asaniczka/1-3m-linkedin-jobs-and-skills-2024/versions/2\n"
     ]
    }
   ],
   "source": [
    "path = kagglehub.dataset_download(\"asaniczka/1-3m-linkedin-jobs-and-skills-2024\")\n",
    "print(\"Path to dataset files:\", path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_names = [\"job_skills.csv\", \"job_summary.csv\", \"linkedin_job_postings.csv\"]\n",
    "\n",
    "job_skills_path, job_summary_path, job_postings_path = [\n",
    "    os.path.join(path, file_name) for file_name in file_names\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------------+------------+\n",
      "|         File Name         |    Size    |\n",
      "+---------------------------+------------+\n",
      "|      job_skills.csv       | 641.55 MB  |\n",
      "|      job_summary.csv      | 4865.66 MB |\n",
      "| linkedin_job_postings.csv | 396.09 MB  |\n",
      "+---------------------------+------------+\n"
     ]
    }
   ],
   "source": [
    "table = []\n",
    "for file_name in file_names:\n",
    "    file_path = os.path.join(path, file_name)\n",
    "    size_mb = os.path.getsize(file_path) / (1024**2)\n",
    "    table.append([file_name, f\"{size_mb:.2f} MB\"])\n",
    "\n",
    "print(tabulate(table, headers=[\"File Name\", \"Size\"], tablefmt=\"pretty\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_skills_df = pd.read_csv(job_skills_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_summary_df = pd.read_csv(job_summary_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_postings_df = pd.read_csv(job_postings_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of Job Skills: (1296381, 2)\n",
      "Shape of Job Summary: (1297332, 2)\n",
      "Shape of Job Postings: (1348454, 14)\n"
     ]
    }
   ],
   "source": [
    "print(f\"Shape of Job Skills: {job_skills_df.shape}\")\n",
    "print(f\"Shape of Job Summary: {job_summary_df.shape}\")\n",
    "print(f\"Shape of Job Postings: {job_postings_df.shape}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>job_link</th>\n",
       "      <th>job_skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/housekeeper...</td>\n",
       "      <td>Building Custodial Services, Cleaning, Janitor...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/assistant-g...</td>\n",
       "      <td>Customer service, Restaurant management, Food ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/school-base...</td>\n",
       "      <td>Applied Behavior Analysis (ABA), Data analysis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/electrical-...</td>\n",
       "      <td>Electrical Engineering, Project Controls, Sche...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/electrical-...</td>\n",
       "      <td>Electrical Assembly, Point to point wiring, St...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            job_link  \\\n",
       "0  https://www.linkedin.com/jobs/view/housekeeper...   \n",
       "1  https://www.linkedin.com/jobs/view/assistant-g...   \n",
       "2  https://www.linkedin.com/jobs/view/school-base...   \n",
       "3  https://www.linkedin.com/jobs/view/electrical-...   \n",
       "4  https://www.linkedin.com/jobs/view/electrical-...   \n",
       "\n",
       "                                          job_skills  \n",
       "0  Building Custodial Services, Cleaning, Janitor...  \n",
       "1  Customer service, Restaurant management, Food ...  \n",
       "2  Applied Behavior Analysis (ABA), Data analysis...  \n",
       "3  Electrical Engineering, Project Controls, Sche...  \n",
       "4  Electrical Assembly, Point to point wiring, St...  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_skills_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>job_link</th>\n",
       "      <th>job_skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1296381</td>\n",
       "      <td>1294296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>1296381</td>\n",
       "      <td>1287101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/housekeeper...</td>\n",
       "      <td>Front Counter, DriveThru, Outside Order Taker,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 job_link  \\\n",
       "count                                             1296381   \n",
       "unique                                            1296381   \n",
       "top     https://www.linkedin.com/jobs/view/housekeeper...   \n",
       "freq                                                    1   \n",
       "\n",
       "                                               job_skills  \n",
       "count                                             1294296  \n",
       "unique                                            1287101  \n",
       "top     Front Counter, DriveThru, Outside Order Taker,...  \n",
       "freq                                                  169  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_skills_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>job_link</th>\n",
       "      <th>job_summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1105937</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/rn-inpatien...</td>\n",
       "      <td>Description****Introduction**Do you have the c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>898212</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/per-diem-re...</td>\n",
       "      <td>Overview\\nProvides high quality comprehensive ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1289526</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/fashion-acc...</td>\n",
       "      <td>Must Haves\\n7+ years-experience as an agency A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1242687</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/cook-i-pit-...</td>\n",
       "      <td>This position is responsible for creating dail...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067037</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/sr-project-...</td>\n",
       "      <td>SENIOR PROJECT ENGINEER\\nJob Summary\\nProvide ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  job_link  \\\n",
       "1105937  https://www.linkedin.com/jobs/view/rn-inpatien...   \n",
       "898212   https://www.linkedin.com/jobs/view/per-diem-re...   \n",
       "1289526  https://www.linkedin.com/jobs/view/fashion-acc...   \n",
       "1242687  https://www.linkedin.com/jobs/view/cook-i-pit-...   \n",
       "1067037  https://www.linkedin.com/jobs/view/sr-project-...   \n",
       "\n",
       "                                               job_summary  \n",
       "1105937  Description****Introduction**Do you have the c...  \n",
       "898212   Overview\\nProvides high quality comprehensive ...  \n",
       "1289526  Must Haves\\n7+ years-experience as an agency A...  \n",
       "1242687  This position is responsible for creating dail...  \n",
       "1067037  SENIOR PROJECT ENGINEER\\nJob Summary\\nProvide ...  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_summary_df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>job_link</th>\n",
       "      <th>job_summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1297332</td>\n",
       "      <td>1297332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>1297332</td>\n",
       "      <td>958192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/restaurant-...</td>\n",
       "      <td>Dollar General Corporation has been delivering...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>4571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 job_link  \\\n",
       "count                                             1297332   \n",
       "unique                                            1297332   \n",
       "top     https://www.linkedin.com/jobs/view/restaurant-...   \n",
       "freq                                                    1   \n",
       "\n",
       "                                              job_summary  \n",
       "count                                             1297332  \n",
       "unique                                             958192  \n",
       "top     Dollar General Corporation has been delivering...  \n",
       "freq                                                 4571  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_summary_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>job_link</th>\n",
       "      <th>last_processed_time</th>\n",
       "      <th>got_summary</th>\n",
       "      <th>got_ner</th>\n",
       "      <th>is_being_worked</th>\n",
       "      <th>job_title</th>\n",
       "      <th>company</th>\n",
       "      <th>job_location</th>\n",
       "      <th>first_seen</th>\n",
       "      <th>search_city</th>\n",
       "      <th>search_country</th>\n",
       "      <th>search_position</th>\n",
       "      <th>job_level</th>\n",
       "      <th>job_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/account-exe...</td>\n",
       "      <td>2024-01-21 07:12:29.00256+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>Account Executive - Dispensing (NorCal/Norther...</td>\n",
       "      <td>BD</td>\n",
       "      <td>San Diego, CA</td>\n",
       "      <td>2024-01-15</td>\n",
       "      <td>Coronado</td>\n",
       "      <td>United States</td>\n",
       "      <td>Color Maker</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>Onsite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/registered-...</td>\n",
       "      <td>2024-01-21 07:39:58.88137+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>Registered Nurse - RN Care Manager</td>\n",
       "      <td>Trinity Health MI</td>\n",
       "      <td>Norton Shores, MI</td>\n",
       "      <td>2024-01-14</td>\n",
       "      <td>Grand Haven</td>\n",
       "      <td>United States</td>\n",
       "      <td>Director Nursing Service</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>Onsite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/restaurant-...</td>\n",
       "      <td>2024-01-21 07:40:00.251126+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>RESTAURANT SUPERVISOR - THE FORKLIFT</td>\n",
       "      <td>Wasatch Adaptive Sports</td>\n",
       "      <td>Sandy, UT</td>\n",
       "      <td>2024-01-14</td>\n",
       "      <td>Tooele</td>\n",
       "      <td>United States</td>\n",
       "      <td>Stand-In</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>Onsite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/independent...</td>\n",
       "      <td>2024-01-21 07:40:00.308133+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>Independent Real Estate Agent</td>\n",
       "      <td>Howard Hanna | Rand Realty</td>\n",
       "      <td>Englewood Cliffs, NJ</td>\n",
       "      <td>2024-01-16</td>\n",
       "      <td>Pinehurst</td>\n",
       "      <td>United States</td>\n",
       "      <td>Real-Estate Clerk</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>Onsite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/group-unit-...</td>\n",
       "      <td>2024-01-19 09:45:09.215838+00</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>Group/Unit Supervisor (Systems Support Manager...</td>\n",
       "      <td>IRS, Office of Chief Counsel</td>\n",
       "      <td>Chamblee, GA</td>\n",
       "      <td>2024-01-17</td>\n",
       "      <td>Gadsden</td>\n",
       "      <td>United States</td>\n",
       "      <td>Supervisor Travel-Information Center</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>Onsite</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            job_link  \\\n",
       "0  https://www.linkedin.com/jobs/view/account-exe...   \n",
       "1  https://www.linkedin.com/jobs/view/registered-...   \n",
       "2  https://www.linkedin.com/jobs/view/restaurant-...   \n",
       "3  https://www.linkedin.com/jobs/view/independent...   \n",
       "4  https://www.linkedin.com/jobs/view/group-unit-...   \n",
       "\n",
       "             last_processed_time got_summary got_ner is_being_worked  \\\n",
       "0   2024-01-21 07:12:29.00256+00           t       t               f   \n",
       "1   2024-01-21 07:39:58.88137+00           t       t               f   \n",
       "2  2024-01-21 07:40:00.251126+00           t       t               f   \n",
       "3  2024-01-21 07:40:00.308133+00           t       t               f   \n",
       "4  2024-01-19 09:45:09.215838+00           f       f               f   \n",
       "\n",
       "                                           job_title  \\\n",
       "0  Account Executive - Dispensing (NorCal/Norther...   \n",
       "1                 Registered Nurse - RN Care Manager   \n",
       "2               RESTAURANT SUPERVISOR - THE FORKLIFT   \n",
       "3                      Independent Real Estate Agent   \n",
       "4  Group/Unit Supervisor (Systems Support Manager...   \n",
       "\n",
       "                        company          job_location  first_seen  \\\n",
       "0                            BD         San Diego, CA  2024-01-15   \n",
       "1             Trinity Health MI     Norton Shores, MI  2024-01-14   \n",
       "2       Wasatch Adaptive Sports             Sandy, UT  2024-01-14   \n",
       "3    Howard Hanna | Rand Realty  Englewood Cliffs, NJ  2024-01-16   \n",
       "4  IRS, Office of Chief Counsel          Chamblee, GA  2024-01-17   \n",
       "\n",
       "   search_city search_country                       search_position  \\\n",
       "0     Coronado  United States                           Color Maker   \n",
       "1  Grand Haven  United States              Director Nursing Service   \n",
       "2       Tooele  United States                              Stand-In   \n",
       "3    Pinehurst  United States                     Real-Estate Clerk   \n",
       "4      Gadsden  United States  Supervisor Travel-Information Center   \n",
       "\n",
       "    job_level job_type  \n",
       "0  Mid senior   Onsite  \n",
       "1  Mid senior   Onsite  \n",
       "2  Mid senior   Onsite  \n",
       "3  Mid senior   Onsite  \n",
       "4  Mid senior   Onsite  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_postings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>job_link</th>\n",
       "      <th>last_processed_time</th>\n",
       "      <th>got_summary</th>\n",
       "      <th>got_ner</th>\n",
       "      <th>is_being_worked</th>\n",
       "      <th>job_title</th>\n",
       "      <th>company</th>\n",
       "      <th>job_location</th>\n",
       "      <th>first_seen</th>\n",
       "      <th>search_city</th>\n",
       "      <th>search_country</th>\n",
       "      <th>search_position</th>\n",
       "      <th>job_level</th>\n",
       "      <th>job_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348443</td>\n",
       "      <td>1348435</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "      <td>1348454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>1348454</td>\n",
       "      <td>722748</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>584544</td>\n",
       "      <td>90605</td>\n",
       "      <td>29153</td>\n",
       "      <td>6</td>\n",
       "      <td>1018</td>\n",
       "      <td>4</td>\n",
       "      <td>1993</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>https://www.linkedin.com/jobs/view/account-exe...</td>\n",
       "      <td>2024-01-19 09:45:09.215838+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>LEAD SALES ASSOCIATE-FT</td>\n",
       "      <td>Health eCareers</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>2024-01-14</td>\n",
       "      <td>Baytown</td>\n",
       "      <td>United States</td>\n",
       "      <td>Account Executive</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>Onsite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>625540</td>\n",
       "      <td>1297877</td>\n",
       "      <td>1296401</td>\n",
       "      <td>1346978</td>\n",
       "      <td>7325</td>\n",
       "      <td>41598</td>\n",
       "      <td>13436</td>\n",
       "      <td>460035</td>\n",
       "      <td>10052</td>\n",
       "      <td>1149342</td>\n",
       "      <td>19468</td>\n",
       "      <td>1204445</td>\n",
       "      <td>1337633</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 job_link  \\\n",
       "count                                             1348454   \n",
       "unique                                            1348454   \n",
       "top     https://www.linkedin.com/jobs/view/account-exe...   \n",
       "freq                                                    1   \n",
       "\n",
       "                  last_processed_time got_summary  got_ner is_being_worked  \\\n",
       "count                         1348454     1348454  1348454         1348454   \n",
       "unique                         722748           2        2               2   \n",
       "top     2024-01-19 09:45:09.215838+00           t        t               f   \n",
       "freq                           625540     1297877  1296401         1346978   \n",
       "\n",
       "                      job_title          company  job_location  first_seen  \\\n",
       "count                   1348454          1348443       1348435     1348454   \n",
       "unique                   584544            90605         29153           6   \n",
       "top     LEAD SALES ASSOCIATE-FT  Health eCareers  New York, NY  2024-01-14   \n",
       "freq                       7325            41598         13436      460035   \n",
       "\n",
       "       search_city search_country    search_position   job_level job_type  \n",
       "count      1348454        1348454            1348454     1348454  1348454  \n",
       "unique        1018              4               1993           2        3  \n",
       "top        Baytown  United States  Account Executive  Mid senior   Onsite  \n",
       "freq         10052        1149342              19468     1204445  1337633  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_postings_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data enrichment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Enriching Job Postings with Skills Information for Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_postings_raw = pd.merge(job_postings_df, job_skills_df, on=\"job_link\", how=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove Duplicates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check for and remove any duplicate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of duplicates: 0\n"
     ]
    }
   ],
   "source": [
    "# Check for duplicates\n",
    "duplicates = job_postings_raw.duplicated().sum()\n",
    "print(f\"Number of duplicates: {duplicates}\")\n",
    "\n",
    "# Removing duplicates\n",
    "job_postings_no_duplicates = job_postings_raw.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Missing Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Review the number of missing values for each column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_missing_values(df):\n",
    "    missing_values = df.isnull().sum()\n",
    "    missing_values_percentage = round(missing_values / len(df), 2) * 100\n",
    "    missing_data = pd.DataFrame(\n",
    "        {\"Missing Values\": missing_values, \"Percentage\": missing_values_percentage}\n",
    "    )\n",
    "\n",
    "    return missing_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     Missing Values  Percentage\n",
      "job_link                          0         0.0\n",
      "last_processed_time               0         0.0\n",
      "got_summary                       0         0.0\n",
      "got_ner                           0         0.0\n",
      "is_being_worked                   0         0.0\n",
      "job_title                         0         0.0\n",
      "company                           9         0.0\n",
      "job_location                     19         0.0\n",
      "first_seen                        0         0.0\n",
      "search_city                       0         0.0\n",
      "search_country                    0         0.0\n",
      "search_position                   0         0.0\n",
      "job_level                         0         0.0\n",
      "job_type                          0         0.0\n",
      "job_skills                     2085         0.0\n"
     ]
    }
   ],
   "source": [
    "print(check_missing_values(job_postings_no_duplicates))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fill missing values in `company`, `job_location`, and `job_skills` and drop `job_link`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_missing_values(df):\n",
    "    df_filled = df.copy().drop(columns=[\"job_link\"])\n",
    "\n",
    "    df_filled[\"company\"] = df_filled[\"company\"].fillna(\"Unknown\")\n",
    "    df_filled[\"job_location\"] = df_filled[\"job_location\"].fillna(\"Unknown\")\n",
    "    df_filled[\"job_skills\"] = df_filled[\"job_skills\"].fillna(\"No Skills Provided\")\n",
    "\n",
    "    return df_filled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_postings_no_missing = fill_missing_values(job_postings_no_duplicates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     Missing Values  Percentage\n",
      "last_processed_time               0         0.0\n",
      "got_summary                       0         0.0\n",
      "got_ner                           0         0.0\n",
      "is_being_worked                   0         0.0\n",
      "job_title                         0         0.0\n",
      "company                           0         0.0\n",
      "job_location                      0         0.0\n",
      "first_seen                        0         0.0\n",
      "search_city                       0         0.0\n",
      "search_country                    0         0.0\n",
      "search_position                   0         0.0\n",
      "job_level                         0         0.0\n",
      "job_type                          0         0.0\n",
      "job_skills                        0         0.0\n"
     ]
    }
   ],
   "source": [
    "print(check_missing_values(job_postings_no_missing))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Text Normalization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Convert text to lowercase.\n",
    "- Remove any leading or trailing whitespace.\n",
    "- Replace ampersands with 'and'\n",
    "\n",
    "These steps will introduce consistency into the data for easier handling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def normalize_text(text):\n",
    "    if isinstance(text, str):\n",
    "        text = text.lower().strip()\n",
    "    return text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apply normalization all the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_postings_normalized = job_postings_no_missing.copy()\n",
    "\n",
    "for col in job_postings_normalized.columns:\n",
    "    job_postings_normalized[col] = job_postings_normalized[col].apply(normalize_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>last_processed_time</th>\n",
       "      <th>got_summary</th>\n",
       "      <th>got_ner</th>\n",
       "      <th>is_being_worked</th>\n",
       "      <th>job_title</th>\n",
       "      <th>company</th>\n",
       "      <th>job_location</th>\n",
       "      <th>first_seen</th>\n",
       "      <th>search_city</th>\n",
       "      <th>search_country</th>\n",
       "      <th>search_position</th>\n",
       "      <th>job_level</th>\n",
       "      <th>job_type</th>\n",
       "      <th>job_skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-01-21 07:12:29.00256+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>account executive - dispensing (norcal/norther...</td>\n",
       "      <td>bd</td>\n",
       "      <td>san diego, ca</td>\n",
       "      <td>2024-01-15</td>\n",
       "      <td>coronado</td>\n",
       "      <td>united states</td>\n",
       "      <td>color maker</td>\n",
       "      <td>mid senior</td>\n",
       "      <td>onsite</td>\n",
       "      <td>medical equipment sales, key competitors, term...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-01-21 07:39:58.88137+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>registered nurse - rn care manager</td>\n",
       "      <td>trinity health mi</td>\n",
       "      <td>norton shores, mi</td>\n",
       "      <td>2024-01-14</td>\n",
       "      <td>grand haven</td>\n",
       "      <td>united states</td>\n",
       "      <td>director nursing service</td>\n",
       "      <td>mid senior</td>\n",
       "      <td>onsite</td>\n",
       "      <td>nursing, bachelor of science in nursing, maste...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-01-21 07:40:00.251126+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>restaurant supervisor - the forklift</td>\n",
       "      <td>wasatch adaptive sports</td>\n",
       "      <td>sandy, ut</td>\n",
       "      <td>2024-01-14</td>\n",
       "      <td>tooele</td>\n",
       "      <td>united states</td>\n",
       "      <td>stand-in</td>\n",
       "      <td>mid senior</td>\n",
       "      <td>onsite</td>\n",
       "      <td>restaurant operations management, inventory ma...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             last_processed_time got_summary got_ner is_being_worked  \\\n",
       "0   2024-01-21 07:12:29.00256+00           t       t               f   \n",
       "1   2024-01-21 07:39:58.88137+00           t       t               f   \n",
       "2  2024-01-21 07:40:00.251126+00           t       t               f   \n",
       "\n",
       "                                           job_title                  company  \\\n",
       "0  account executive - dispensing (norcal/norther...                       bd   \n",
       "1                 registered nurse - rn care manager        trinity health mi   \n",
       "2               restaurant supervisor - the forklift  wasatch adaptive sports   \n",
       "\n",
       "        job_location  first_seen  search_city search_country  \\\n",
       "0      san diego, ca  2024-01-15     coronado  united states   \n",
       "1  norton shores, mi  2024-01-14  grand haven  united states   \n",
       "2          sandy, ut  2024-01-14       tooele  united states   \n",
       "\n",
       "            search_position   job_level job_type  \\\n",
       "0               color maker  mid senior   onsite   \n",
       "1  director nursing service  mid senior   onsite   \n",
       "2                  stand-in  mid senior   onsite   \n",
       "\n",
       "                                          job_skills  \n",
       "0  medical equipment sales, key competitors, term...  \n",
       "1  nursing, bachelor of science in nursing, maste...  \n",
       "2  restaurant operations management, inventory ma...  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_postings_normalized.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of jobs: 1,296,381\n"
     ]
    }
   ],
   "source": [
    "total_jobs = job_postings_normalized.shape[0]\n",
    "print(f\"Total number of jobs: {total_jobs:,}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Feature Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Split the skills into a list\n",
    "- Filter out 2-letter words and unwanted specific words like 'is', 'it', 'am'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_skills(row):\n",
    "    skills = row[\"job_skills\"].split(\", \")\n",
    "    skills = [skill for skill in skills if len(skill) >= 3]\n",
    "\n",
    "    return skills"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_postings_normalized['skills'] = job_postings_normalized.apply(extract_skills, axis=1)\n",
    "job_postings_normalized['skills_count'] = job_postings_normalized['skills'].apply(len)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Update the `job_skills` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_postings_normalized.drop(['job_skills'], axis=1, inplace=True)\n",
    "job_postings_normalized = job_postings_normalized.rename(columns={'skills': 'job_skills'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>last_processed_time</th>\n",
       "      <th>got_summary</th>\n",
       "      <th>got_ner</th>\n",
       "      <th>is_being_worked</th>\n",
       "      <th>job_title</th>\n",
       "      <th>company</th>\n",
       "      <th>job_location</th>\n",
       "      <th>first_seen</th>\n",
       "      <th>search_city</th>\n",
       "      <th>search_country</th>\n",
       "      <th>search_position</th>\n",
       "      <th>job_level</th>\n",
       "      <th>job_type</th>\n",
       "      <th>job_skills</th>\n",
       "      <th>skills_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-01-21 07:12:29.00256+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>account executive - dispensing (norcal/norther...</td>\n",
       "      <td>bd</td>\n",
       "      <td>san diego, ca</td>\n",
       "      <td>2024-01-15</td>\n",
       "      <td>coronado</td>\n",
       "      <td>united states</td>\n",
       "      <td>color maker</td>\n",
       "      <td>mid senior</td>\n",
       "      <td>onsite</td>\n",
       "      <td>[medical equipment sales, key competitors, ter...</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-01-21 07:39:58.88137+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>registered nurse - rn care manager</td>\n",
       "      <td>trinity health mi</td>\n",
       "      <td>norton shores, mi</td>\n",
       "      <td>2024-01-14</td>\n",
       "      <td>grand haven</td>\n",
       "      <td>united states</td>\n",
       "      <td>director nursing service</td>\n",
       "      <td>mid senior</td>\n",
       "      <td>onsite</td>\n",
       "      <td>[nursing, bachelor of science in nursing, mast...</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-01-21 07:40:00.251126+00</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>restaurant supervisor - the forklift</td>\n",
       "      <td>wasatch adaptive sports</td>\n",
       "      <td>sandy, ut</td>\n",
       "      <td>2024-01-14</td>\n",
       "      <td>tooele</td>\n",
       "      <td>united states</td>\n",
       "      <td>stand-in</td>\n",
       "      <td>mid senior</td>\n",
       "      <td>onsite</td>\n",
       "      <td>[restaurant operations management, inventory m...</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             last_processed_time got_summary got_ner is_being_worked  \\\n",
       "0   2024-01-21 07:12:29.00256+00           t       t               f   \n",
       "1   2024-01-21 07:39:58.88137+00           t       t               f   \n",
       "2  2024-01-21 07:40:00.251126+00           t       t               f   \n",
       "\n",
       "                                           job_title                  company  \\\n",
       "0  account executive - dispensing (norcal/norther...                       bd   \n",
       "1                 registered nurse - rn care manager        trinity health mi   \n",
       "2               restaurant supervisor - the forklift  wasatch adaptive sports   \n",
       "\n",
       "        job_location  first_seen  search_city search_country  \\\n",
       "0      san diego, ca  2024-01-15     coronado  united states   \n",
       "1  norton shores, mi  2024-01-14  grand haven  united states   \n",
       "2          sandy, ut  2024-01-14       tooele  united states   \n",
       "\n",
       "            search_position   job_level job_type  \\\n",
       "0               color maker  mid senior   onsite   \n",
       "1  director nursing service  mid senior   onsite   \n",
       "2                  stand-in  mid senior   onsite   \n",
       "\n",
       "                                          job_skills  skills_count  \n",
       "0  [medical equipment sales, key competitors, ter...            25  \n",
       "1  [nursing, bachelor of science in nursing, mast...            17  \n",
       "2  [restaurant operations management, inventory m...            43  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_postings_normalized.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Changing the ‘search_country’ column into categories.\n",
    "- This will make the Dataframe work faster and use less memory.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['australia', 'canada', 'united kingdom', 'united states'], dtype='object')"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_postings_normalized['search_country'] = job_postings_normalized['search_country'].astype('category')\n",
    "job_postings_normalized['search_country'].cat.categories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save as a numpy array for further analysys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1,296,381 jobs saved to data/job_postings_normalized.parquet\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    filename=\"data/a_job_postings_normalized\"\n",
    "    job_postings_normalized.to_parquet(f\"{filename}.parquet\")\n",
    "    print(f\"{job_postings_normalized.shape[0]:,} jobs saved to {filename}.parquet\")\n",
    "except Exception as e:\n",
    "    print(f\"Error saving DataFrame: {e}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}