Linkedin_DA_jobs.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1792 entries, 0 to 1791\n",
      "Data columns (total 10 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   country        1792 non-null   object\n",
      " 1   title          1792 non-null   object\n",
      " 2   company        1792 non-null   object\n",
      " 3   location       1792 non-null   object\n",
      " 4   onsite_remote  1792 non-null   int64 \n",
      " 5   salary         78 non-null     object\n",
      " 6   description    1734 non-null   object\n",
      " 7   criteria       1792 non-null   object\n",
      " 8   posted_date    1792 non-null   object\n",
      " 9   link           1792 non-null   object\n",
      "dtypes: int64(1), object(9)\n",
      "memory usage: 140.1+ KB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "pd.options.display.max_columns = 50\n",
    "pd.options.display.max_rows = 50\n",
    "\n",
    "jobs = pd.read_csv(r\"Datasets\\linkedin-jobs.csv\")\n",
    "jobs.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1792 entries, 0 to 1791\n",
      "Data columns (total 14 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   country          1792 non-null   object\n",
      " 1   title            1792 non-null   object\n",
      " 2   company          1792 non-null   object\n",
      " 3   location         1792 non-null   object\n",
      " 4   onsite_remote    1792 non-null   int64 \n",
      " 5   salary           78 non-null     object\n",
      " 6   description      1734 non-null   object\n",
      " 7   criteria         1792 non-null   object\n",
      " 8   posted_date      1792 non-null   object\n",
      " 9   link             1792 non-null   object\n",
      " 10  seniority_level  1619 non-null   object\n",
      " 11  employment_type  1725 non-null   object\n",
      " 12  job_function     1619 non-null   object\n",
      " 13  industries       1613 non-null   object\n",
      "dtypes: int64(1), object(13)\n",
      "memory usage: 196.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# Extract from column \"criteria\" each value and take it to its own column\n",
    "def extract_criteria(x, crit):\n",
    "    trans = {\n",
    "        \"Seniority level\": \"Nivel de antigüedad\",\n",
    "        \"Employment type\": \"Tipo de empleo\",\n",
    "        \"Job function\": \"Función laboral\",\n",
    "        \"Industries\": \"Sectores\",\n",
    "        \"Nível de experiência\": \"Nivel de antigüedad\",\n",
    "        \"Tipo de emprego\": \"Tipo de empleo\",\n",
    "        \"Função\": \"Función laboral\",\n",
    "        \"Setores\": \"Sectores\"\n",
    "        }\n",
    "\n",
    "    for orig, dest in trans.items():\n",
    "        x = x.replace(orig, dest)\n",
    "\n",
    "    d = json.loads(x.replace(\"\\'\", \"\\\"\"))\n",
    "\n",
    "    exists = []\n",
    "    for item in d:\n",
    "        exists.append(crit in item in d)\n",
    "\n",
    "    if bool(True in exists):\n",
    "        pos = exists.index(True)\n",
    "        return d[pos][crit]\n",
    "    \n",
    "jobs[\"seniority_level\"] = jobs[\"criteria\"].apply(extract_criteria, crit=\"Nivel de antigüedad\")\n",
    "jobs[\"employment_type\"] = jobs[\"criteria\"].apply(extract_criteria, crit=\"Tipo de empleo\")\n",
    "jobs[\"job_function\"] = jobs[\"criteria\"].apply(extract_criteria, crit=\"Función laboral\")\n",
    "jobs[\"industries\"] = jobs[\"criteria\"].apply(extract_criteria, crit=\"Sectores\")\n",
    "\n",
    "jobs.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1126 entries, 0 to 1125\n",
      "Data columns (total 11 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   country          1126 non-null   object\n",
      " 1   title            1126 non-null   object\n",
      " 2   company          1126 non-null   object\n",
      " 3   onsite_remote    1126 non-null   int64 \n",
      " 4   description      1126 non-null   object\n",
      " 5   posted_date      1126 non-null   object\n",
      " 6   link             1126 non-null   object\n",
      " 7   seniority_level  1066 non-null   object\n",
      " 8   employment_type  1117 non-null   object\n",
      " 9   job_function     1066 non-null   object\n",
      " 10  industries       1061 non-null   object\n",
      "dtypes: int64(1), object(10)\n",
      "memory usage: 96.9+ KB\n"
     ]
    }
   ],
   "source": [
    "# Remove records with missing and duplicates\n",
    "jobs = jobs.drop(columns=[\"location\", \"criteria\", \"salary\"])\n",
    "\n",
    "jobs.drop_duplicates(subset=jobs.drop(columns=\"link\").columns, inplace=True)\n",
    "\n",
    "jobs[\"description\"].replace(\"N/A\", None, inplace=True)\n",
    "jobs = jobs.dropna(axis=0, subset=\"description\")\n",
    "\n",
    "# Keep only relevant job titles\n",
    "jobs = jobs.loc[jobs[\"title\"].str.contains(\"Analyst|Analista|Data|Datos|Dados\", case=False, regex=True)]\n",
    "\n",
    "jobs.reset_index(drop=True, inplace=True)\n",
    "\n",
    "jobs.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define skills to be analyzed, and how can they be found in text\n",
    "tech_stack_dicts = {\n",
    "    \"Visualization\": {\n",
    "        \"Power BI\": [\"Power BI\",\"PowerBI\"],\n",
    "        \"Microstrategy\": \"Microstrategy\",\n",
    "        \"Tableau\": \"Tableau\",\n",
    "        \"Qlik Sense\": [\"Qlik Sense\", \"Qliksense\", \"Qlikview\", \"Qlik View\"],\n",
    "        \"Klipfolio\": \"Klipfolio\",\n",
    "        \"Looker\": \"Looker\",\n",
    "        \"Zoho\": \"Zoho\",\n",
    "        \"Domo\": \"Domo\"\n",
    "        },\n",
    "    \"ETL\" : {\n",
    "        \"Excel\": [\"Excel \", \"Excel,\"],\n",
    "        \"Python\": \"Python\",\n",
    "        \"Bash\": \"Bash\",\n",
    "        \"Perl\": \"Perl\",\n",
    "        \"PySpark\": \"PySpark\"\n",
    "        },\n",
    "    \"Data Base\": {\n",
    "        \"MySQL\": \"MySQL\",\n",
    "        \"SQL\": [\"SQL, \", \" SQL \"],\n",
    "        \"SQLite\": \"SQLite\",\n",
    "        \"PostgreSQL\": \"Postgre\",\n",
    "        \"MariaDB\": \"MariaDB\",\n",
    "        \"Redis\": \"Redis\",\n",
    "        \"MongoDB\": \"MongoDB\",\n",
    "        \"Oracle\": \"Oracle\",\n",
    "        \"Firebase\": \"Firebase\",\n",
    "        \"Elasticsearch\": \"Elasticsearch\",\n",
    "        \"Cassandra\": \"Cassandra\",\n",
    "        \"DynamoDB\": \"DynamoDB\",\n",
    "        \"IBM Db2\": \"Db2\"\n",
    "        },\n",
    "    \"Data Warehouse\": {\n",
    "        \"Snowflake\": \"Snowflake\",\n",
    "        \"AWS\": [\"Redshift\", \"AWS\", \"Amazon Web Services\", \"S3\"],\n",
    "        \"Synapse\": \"Synapse\",\n",
    "        \"Firebolt\": \"Firebolt\",\n",
    "        \"IBM Netezza\":\"Netezza\",\n",
    "        \"Vertica\": \" Vertica \",\n",
    "        \"Databricks\": \"Databricks\",\n",
    "        \"Oracle Exadata\": \"Exadata\",\n",
    "        \"Google Cloud\": [\"Google Cloud\", \"BigQuery\"]\n",
    "    }\n",
    "}\n",
    "\n",
    "skills = pd.DataFrame.from_dict(tech_stack_dicts, orient=\"index\").stack().to_frame()\n",
    "\n",
    "# Check if the stack is in the description\n",
    "def includes_stack(x, to_check):\n",
    "    if x is not None:\n",
    "        return (str.casefold(to_check) in str.casefold(x))\n",
    "\n",
    "# Variation when stack has several ways to be written\n",
    "def includes_stack_list(x, to_check):\n",
    "    if x is not None:\n",
    "        result = []\n",
    "        for item in to_check:\n",
    "            result.append((str.casefold(item) in str.casefold(x)))\n",
    "        return any(result)\n",
    "\n",
    "# Add a column for each stack, filling with True if the stack is included in the job description. False if not.\n",
    "for skill_list, skill in skills[0].items():\n",
    "    if not isinstance(skill, list):\n",
    "        jobs[skill_list[1]] = jobs[\"description\"].apply(includes_stack, to_check=skill)\n",
    "    else:\n",
    "        jobs[skill_list[1]] = jobs[\"description\"].apply(includes_stack_list, to_check=skill)\n",
    "\n",
    "# Format skills df\n",
    "skills.reset_index(inplace=True)\n",
    "skills.rename(columns={\"level_0\": \"type\", \"level_1\": \"skill\", 0: \"writing\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export dfs for use in Power BI\n",
    "\n",
    "# Columns to extract as separate table\n",
    "entities = [\"country\", \"company\", \"industries\"]\n",
    "\n",
    "# Extract columns and export to csv \n",
    "def replace_entities(x, df):\n",
    "    if x is not None:\n",
    "        return df[df == x].index[0]\n",
    "\n",
    "for entity in entities:\n",
    "    df = jobs[entity]\n",
    "    df.drop_duplicates(inplace=True)\n",
    "    df = df.reset_index(drop=True)\n",
    "    df.to_csv(r\"Datasets\\{}.csv\".format(entity), index_label=\"{}_id\".format(entity))\n",
    "    jobs[entity] = jobs[entity].apply(replace_entities, df=df)\n",
    "\n",
    "\n",
    "skills.to_csv(r\"Datasets\\skill_type.csv\", columns=[\"type\" ,\"skill\"], sep=\",\", index_label=\"skill_id\")\n",
    "\n",
    "# Separate skills and jobs into different tables\n",
    "jobs_skills = jobs.iloc[:,11:]\n",
    "jobs_skills.to_csv(r\"Datasets\\skills.csv\", sep=\",\", index_label=\"job_id\")\n",
    "\n",
    "jobs = jobs.iloc[:,:11]\n",
    "jobs.to_csv(r\"Datasets\\jobs.csv\", sep=\",\", index_label=\"job_id\")"
   ]
  }
 ],
 "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.10.11"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "26de051ba29f2982a8de78e945f0abaf191376122a1563185a90213a26c5da77"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}