JobsDataCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c6c094f8",
   "metadata": {},
   "source": [
    "# Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2d46f71d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2c542d8",
   "metadata": {},
   "source": [
    "# Files Loading"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b89b00d0",
   "metadata": {},
   "source": [
    "We load all the files in the folder \"data\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e1ef19f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "folder = 'data_jobs'\n",
    "files = os.listdir(folder)\n",
    "\n",
    "dfs = []\n",
    "\n",
    "headers = ['Job','URL','Company','Location','Salary','Posted','ID','Job_Description']\n",
    "\n",
    "for file in files:\n",
    "    path = os.path.join(folder, file)\n",
    "    \n",
    "    df = pd.read_excel(path, names=headers)\n",
    "    df.dropna(subset=['Company'], inplace=True)\n",
    "    dfs.append(df)\n",
    "\n",
    "Jobs = pd.concat(dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c524bd04",
   "metadata": {},
   "source": [
    "# Data Cleaning\n",
    "\n",
    "First of all, we need to delete all the duplicated values we could have. The best column to do so is the \"ID\" column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4d5145eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs = Jobs.drop_duplicates(subset='ID')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dc8977d",
   "metadata": {},
   "source": [
    "Let's see how it looks like the df:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "309f2958",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Posted</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>146392</td>\n",
       "      <td>146392</td>\n",
       "      <td>146392</td>\n",
       "      <td>146366</td>\n",
       "      <td>76339</td>\n",
       "      <td>132123</td>\n",
       "      <td>146392</td>\n",
       "      <td>146392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>62951</td>\n",
       "      <td>146392</td>\n",
       "      <td>40437</td>\n",
       "      <td>33485</td>\n",
       "      <td>21898</td>\n",
       "      <td>340</td>\n",
       "      <td>146392</td>\n",
       "      <td>144673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>Financial Analyst</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>JPMorgan Chase &amp; Co</td>\n",
       "      <td>Remote</td>\n",
       "      <td>Pay information not provided</td>\n",
       "      <td>PostedHoy</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>La página que solicitaste no fue encontrada._x...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>4081</td>\n",
       "      <td>1</td>\n",
       "      <td>664</td>\n",
       "      <td>8358</td>\n",
       "      <td>8971</td>\n",
       "      <td>40498</td>\n",
       "      <td>1</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Job                                                URL  \\\n",
       "count              146392                                             146392   \n",
       "unique              62951                                             146392   \n",
       "top     Financial Analyst  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "freq                 4081                                                  1   \n",
       "\n",
       "                    Company Location                        Salary     Posted  \\\n",
       "count                146392   146366                         76339     132123   \n",
       "unique                40437    33485                         21898        340   \n",
       "top     JPMorgan Chase & Co   Remote  Pay information not provided  PostedHoy   \n",
       "freq                    664     8358                          8971      40498   \n",
       "\n",
       "                         ID                                    Job_Description  \n",
       "count                146392                                             146392  \n",
       "unique               146392                                             144673  \n",
       "top     sj_cd2707991b8bc653  La página que solicitaste no fue encontrada._x...  \n",
       "freq                      1                                                 26  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ec2d85c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 146392 entries, 0 to 987\n",
      "Data columns (total 8 columns):\n",
      " #   Column           Non-Null Count   Dtype \n",
      "---  ------           --------------   ----- \n",
      " 0   Job              146392 non-null  object\n",
      " 1   URL              146392 non-null  object\n",
      " 2   Company          146392 non-null  object\n",
      " 3   Location         146366 non-null  object\n",
      " 4   Salary           76339 non-null   object\n",
      " 5   Posted           132123 non-null  object\n",
      " 6   ID               146392 non-null  object\n",
      " 7   Job_Description  146392 non-null  object\n",
      "dtypes: object(8)\n",
      "memory usage: 10.1+ MB\n"
     ]
    }
   ],
   "source": [
    "Jobs.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9db3608d",
   "metadata": {},
   "source": [
    "We see we have almost all the columns with non-null values, except Salary and Posted. The column Posted is not critic, since it's a column that shows when the job posting was published in terms of the extraction date. It doesn't seem very useful, so we can't drop it.\n",
    "\n",
    "With regards to the Salary column, we see we have aprox 30-35% of non-null values.\n",
    "#### Could we estimate the Salary null values?\n",
    "Let's see it later!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cd6641a3",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=bd203b586ac89...</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>969</th>\n",
       "      <td>Property Controller- Graduate East Lansing</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=a88f74a87f1c6...</td>\n",
       "      <td>Schulte Companies</td>\n",
       "      <td>East Lansing, MI 48823+1 ubicación</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_a88f74a87f1c6e48</td>\n",
       "      <td>Ir directamente al contenido principal_x000D_\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972</th>\n",
       "      <td>Assistant Controller</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=6fe37642cf957...</td>\n",
       "      <td>Home State Insurance Group, Inc.</td>\n",
       "      <td>Waco, TX 76710+1 ubicación</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_6fe37642cf95726f</td>\n",
       "      <td>Ir directamente al contenido principal_x000D_\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>980</th>\n",
       "      <td>Plant Controller</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=094beaf0996c9...</td>\n",
       "      <td>Dometic Corporation</td>\n",
       "      <td>Cerritos, CA 90703</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_094beaf0996c95fe</td>\n",
       "      <td>Ir directamente al contenido principal_x000D_\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>981</th>\n",
       "      <td>Controller</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=f3027b081096f...</td>\n",
       "      <td>The Talance Group, LP</td>\n",
       "      <td>Houston, TX 77002 (Downtown area)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_f3027b081096ff4c</td>\n",
       "      <td>Ir directamente al contenido principal_x000D_\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>987</th>\n",
       "      <td>Plant Controller</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=f6fc5130f7639...</td>\n",
       "      <td>Johnson Controls</td>\n",
       "      <td>Marinette, WI+5 ubicaciones</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_f6fc5130f7639a6e</td>\n",
       "      <td>Ir directamente al contenido principal_x000D_\\...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>146392 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Job  \\\n",
       "0              Market Development Representative 16702   \n",
       "1                     Vice President, Quality Services   \n",
       "2                                     Business Analyst   \n",
       "3    Corporate Strategy and Business Development Ma...   \n",
       "4                  Data Analyst, Business Intelligence   \n",
       "..                                                 ...   \n",
       "969         Property Controller- Graduate East Lansing   \n",
       "972                               Assistant Controller   \n",
       "980                                   Plant Controller   \n",
       "981                                         Controller   \n",
       "987                                   Plant Controller   \n",
       "\n",
       "                                                   URL  \\\n",
       "0    https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "1    https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "2    https://www.indeed.com/rc/clk?jk=bd203b586ac89...   \n",
       "3    https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "4    https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "..                                                 ...   \n",
       "969  https://www.indeed.com/rc/clk?jk=a88f74a87f1c6...   \n",
       "972  https://www.indeed.com/rc/clk?jk=6fe37642cf957...   \n",
       "980  https://www.indeed.com/rc/clk?jk=094beaf0996c9...   \n",
       "981  https://www.indeed.com/rc/clk?jk=f3027b081096f...   \n",
       "987  https://www.indeed.com/rc/clk?jk=f6fc5130f7639...   \n",
       "\n",
       "                              Company                            Location  \\\n",
       "0            Corps Team Services, LLC                             Arizona   \n",
       "1             Family Support Services               Tampa, FL+2 locations   \n",
       "2                         Leonardo247                              Remote   \n",
       "3                 United Therapeutics          Research Triangle Park, NC   \n",
       "4                       Land O' Frost  Madisonville, KY 42431+2 locations   \n",
       "..                                ...                                 ...   \n",
       "969                 Schulte Companies  East Lansing, MI 48823+1 ubicación   \n",
       "972  Home State Insurance Group, Inc.          Waco, TX 76710+1 ubicación   \n",
       "980               Dometic Corporation                  Cerritos, CA 90703   \n",
       "981             The Talance Group, LP   Houston, TX 77002 (Downtown area)   \n",
       "987                  Johnson Controls         Marinette, WI+5 ubicaciones   \n",
       "\n",
       "                         Salary                    ID  \\\n",
       "0      $26.17 - $28.17 por hora   sj_cd2707991b8bc653   \n",
       "1                           NaN   sj_aa0f5247ff3deca7   \n",
       "2                           NaN  job_bd203b586ac89e05   \n",
       "3                           NaN   sj_8cb506d1ac43bc43   \n",
       "4    $89,100 - $108,900 por año   sj_bdfad91ca716ac0f   \n",
       "..                          ...                   ...   \n",
       "969                         NaN  job_a88f74a87f1c6e48   \n",
       "972                         NaN  job_6fe37642cf95726f   \n",
       "980                         NaN  job_094beaf0996c95fe   \n",
       "981                         NaN  job_f3027b081096ff4c   \n",
       "987                         NaN  job_f6fc5130f7639a6e   \n",
       "\n",
       "                                       Job_Description  \n",
       "0    Iniciar sesión_x000D_\\nInicio del contenido pr...  \n",
       "1    Iniciar sesión_x000D_\\nInicio del contenido pr...  \n",
       "2    Iniciar sesión_x000D_\\nInicio del contenido pr...  \n",
       "3    Iniciar sesión_x000D_\\nInicio del contenido pr...  \n",
       "4    Iniciar sesión_x000D_\\nInicio del contenido pr...  \n",
       "..                                                 ...  \n",
       "969  Ir directamente al contenido principal_x000D_\\...  \n",
       "972  Ir directamente al contenido principal_x000D_\\...  \n",
       "980  Ir directamente al contenido principal_x000D_\\...  \n",
       "981  Ir directamente al contenido principal_x000D_\\...  \n",
       "987  Ir directamente al contenido principal_x000D_\\...  \n",
       "\n",
       "[146392 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.drop(columns=['Posted'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65385a97",
   "metadata": {},
   "source": [
    "We saw all the Dtypes are object. However, the Salary column would be interesting to be a numeric column."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cb35821",
   "metadata": {},
   "source": [
    "## Salary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70016f72",
   "metadata": {},
   "source": [
    "Let's see how it looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ac6d4d8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['$26.17 - $28.17 por hora',\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " '$89,100 - $108,900 por año',\n",
       " '$58,723 - $131,682 por año',\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " '$80,000 - $120,000 por año',\n",
       " nan,\n",
       " nan,\n",
       " '$63,108 - $95,512 por año',\n",
       " nan,\n",
       " nan,\n",
       " '$58,760 - $90,000 por año',\n",
       " nan,\n",
       " '$80,000 - $125,000 por año',\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " '$55,843 - $83,765 por año',\n",
       " nan,\n",
       " '$53,000 - $62,000 por año',\n",
       " '$90,806 - $114,444 por año',\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " '$49 - $53 por hora',\n",
       " nan,\n",
       " '$71,052 - $113,807 por año',\n",
       " '$75,157 - $127,767 por año',\n",
       " nan,\n",
       " nan,\n",
       " '$65,395 - $101,150 por año',\n",
       " nan,\n",
       " nan,\n",
       " '$41.18 - $52.00 por hora',\n",
       " '$115,000 - $120,000 por año',\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " nan,\n",
       " 'Hasta $57 por hora',\n",
       " '$45 - $60 por hora',\n",
       " nan,\n",
       " 'Hasta $40 por hora']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs['Salary'].head(50).values.tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf62cc7e",
   "metadata": {},
   "source": [
    "It looks horrible! We have single values, we have ranges, we have salary per year (año), month (mes), week (semana), day (día), and hourly (hora). Moreover we have the $ symbol in all the values, and in some cases we have the words Desde (From), and Hasta (Up to). Let's clean it!\n",
    "\n",
    "First of all, the frecuency pay could be interesting in order to standardize the salaries. Let's split it into a new column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9619c663",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs['Frecuency_Salary'] = Jobs['Salary'].str.split(' por ').str.get(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bdcd8cf5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['hora', nan, 'año', 'mes', 'día', 'semana'], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs['Frecuency_Salary'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "377a05ca",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Posted</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "      <th>Frecuency_Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>hora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=bd203b586ac89...</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>año</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Job  \\\n",
       "0            Market Development Representative 16702   \n",
       "1                   Vice President, Quality Services   \n",
       "2                                   Business Analyst   \n",
       "3  Corporate Strategy and Business Development Ma...   \n",
       "4                Data Analyst, Business Intelligence   \n",
       "\n",
       "                                                 URL  \\\n",
       "0  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "1  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "2  https://www.indeed.com/rc/clk?jk=bd203b586ac89...   \n",
       "3  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "4  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "\n",
       "                    Company                            Location  \\\n",
       "0  Corps Team Services, LLC                             Arizona   \n",
       "1   Family Support Services               Tampa, FL+2 locations   \n",
       "2               Leonardo247                              Remote   \n",
       "3       United Therapeutics          Research Triangle Park, NC   \n",
       "4             Land O' Frost  Madisonville, KY 42431+2 locations   \n",
       "\n",
       "                       Salary Posted                    ID  \\\n",
       "0    $26.17 - $28.17 por hora    NaN   sj_cd2707991b8bc653   \n",
       "1                         NaN    NaN   sj_aa0f5247ff3deca7   \n",
       "2                         NaN    NaN  job_bd203b586ac89e05   \n",
       "3                         NaN    NaN   sj_8cb506d1ac43bc43   \n",
       "4  $89,100 - $108,900 por año    NaN   sj_bdfad91ca716ac0f   \n",
       "\n",
       "                                     Job_Description Frecuency_Salary  \n",
       "0  Iniciar sesión_x000D_\\nInicio del contenido pr...             hora  \n",
       "1  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN  \n",
       "2  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN  \n",
       "3  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN  \n",
       "4  Iniciar sesión_x000D_\\nInicio del contenido pr...              año  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "433b22c6",
   "metadata": {},
   "source": [
    "Nice! Now let's delete the words \"Desde \" and \"Hasta \":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ce603b29",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs['Salary'] = Jobs['Salary'].str.replace('Desde ','')\n",
    "Jobs['Salary'] = Jobs['Salary'].str.replace('Hasta ','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f77598b7",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Posted</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "      <th>Frecuency_Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>hora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=bd203b586ac89...</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>año</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Job  \\\n",
       "0            Market Development Representative 16702   \n",
       "1                   Vice President, Quality Services   \n",
       "2                                   Business Analyst   \n",
       "3  Corporate Strategy and Business Development Ma...   \n",
       "4                Data Analyst, Business Intelligence   \n",
       "\n",
       "                                                 URL  \\\n",
       "0  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "1  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "2  https://www.indeed.com/rc/clk?jk=bd203b586ac89...   \n",
       "3  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "4  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "\n",
       "                    Company                            Location  \\\n",
       "0  Corps Team Services, LLC                             Arizona   \n",
       "1   Family Support Services               Tampa, FL+2 locations   \n",
       "2               Leonardo247                              Remote   \n",
       "3       United Therapeutics          Research Triangle Park, NC   \n",
       "4             Land O' Frost  Madisonville, KY 42431+2 locations   \n",
       "\n",
       "                       Salary Posted                    ID  \\\n",
       "0    $26.17 - $28.17 por hora    NaN   sj_cd2707991b8bc653   \n",
       "1                         NaN    NaN   sj_aa0f5247ff3deca7   \n",
       "2                         NaN    NaN  job_bd203b586ac89e05   \n",
       "3                         NaN    NaN   sj_8cb506d1ac43bc43   \n",
       "4  $89,100 - $108,900 por año    NaN   sj_bdfad91ca716ac0f   \n",
       "\n",
       "                                     Job_Description Frecuency_Salary  \n",
       "0  Iniciar sesión_x000D_\\nInicio del contenido pr...             hora  \n",
       "1  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN  \n",
       "2  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN  \n",
       "3  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN  \n",
       "4  Iniciar sesión_x000D_\\nInicio del contenido pr...              año  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f67eaa8",
   "metadata": {},
   "source": [
    "Let's work on the range values. An interesting approach would be to have two new columns, for the low part of the range, and another for the high part of the range:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b44b2bf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_split = Jobs['Salary'].str.split('-', expand=True)\n",
    "Jobs['Low_Salary'] = salary_split[0].str.strip()\n",
    "Jobs['High_Salary'] = salary_split[1].str.strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ee3e95e",
   "metadata": {},
   "source": [
    "With these split, all the non-range values, will have value only in the Low_Salary column. In this cases, let's set the same values for all low and high salary columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "19a9c959",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs['High_Salary'] = Jobs['High_Salary'].fillna(Jobs['Low_Salary'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7b4e7fe0",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Posted</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "      <th>Frecuency_Salary</th>\n",
       "      <th>Low_Salary</th>\n",
       "      <th>High_Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>hora</td>\n",
       "      <td>$26.17</td>\n",
       "      <td>$28.17 por hora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=bd203b586ac89...</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>año</td>\n",
       "      <td>$89,100</td>\n",
       "      <td>$108,900 por año</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Job  \\\n",
       "0            Market Development Representative 16702   \n",
       "1                   Vice President, Quality Services   \n",
       "2                                   Business Analyst   \n",
       "3  Corporate Strategy and Business Development Ma...   \n",
       "4                Data Analyst, Business Intelligence   \n",
       "\n",
       "                                                 URL  \\\n",
       "0  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "1  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "2  https://www.indeed.com/rc/clk?jk=bd203b586ac89...   \n",
       "3  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "4  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "\n",
       "                    Company                            Location  \\\n",
       "0  Corps Team Services, LLC                             Arizona   \n",
       "1   Family Support Services               Tampa, FL+2 locations   \n",
       "2               Leonardo247                              Remote   \n",
       "3       United Therapeutics          Research Triangle Park, NC   \n",
       "4             Land O' Frost  Madisonville, KY 42431+2 locations   \n",
       "\n",
       "                       Salary Posted                    ID  \\\n",
       "0    $26.17 - $28.17 por hora    NaN   sj_cd2707991b8bc653   \n",
       "1                         NaN    NaN   sj_aa0f5247ff3deca7   \n",
       "2                         NaN    NaN  job_bd203b586ac89e05   \n",
       "3                         NaN    NaN   sj_8cb506d1ac43bc43   \n",
       "4  $89,100 - $108,900 por año    NaN   sj_bdfad91ca716ac0f   \n",
       "\n",
       "                                     Job_Description Frecuency_Salary  \\\n",
       "0  Iniciar sesión_x000D_\\nInicio del contenido pr...             hora   \n",
       "1  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "2  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "3  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "4  Iniciar sesión_x000D_\\nInicio del contenido pr...              año   \n",
       "\n",
       "  Low_Salary       High_Salary  \n",
       "0     $26.17   $28.17 por hora  \n",
       "1        NaN               NaN  \n",
       "2        NaN               NaN  \n",
       "3        NaN               NaN  \n",
       "4    $89,100  $108,900 por año  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f4a1e5c",
   "metadata": {},
   "source": [
    "We see how it looks better, but we need to clean the non numeric values for both columns. So let's replace all the non numeric strings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "96e793f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs['Low_Salary'] = Jobs['Low_Salary'].replace('[\\$,]', '', regex=True)\n",
    "Jobs['High_Salary'] = Jobs['High_Salary'].replace('[\\$,]', '', regex=True)\n",
    "Jobs['Low_Salary'] = Jobs['Low_Salary'].replace(' por hora', '', regex=True)\n",
    "Jobs['High_Salary'] = Jobs['High_Salary'].replace(' por hora', '', regex=True)\n",
    "Jobs['Low_Salary'] = Jobs['Low_Salary'].replace(' por día', '', regex=True)\n",
    "Jobs['High_Salary'] = Jobs['High_Salary'].replace(' por día', '', regex=True)\n",
    "Jobs['Low_Salary'] = Jobs['Low_Salary'].replace(' por año', '', regex=True)\n",
    "Jobs['High_Salary'] = Jobs['High_Salary'].replace(' por año', '', regex=True)\n",
    "Jobs['Low_Salary'] = Jobs['Low_Salary'].replace(' por mes', '', regex=True)\n",
    "Jobs['High_Salary'] = Jobs['High_Salary'].replace(' por mes', '', regex=True)\n",
    "Jobs['Low_Salary'] = Jobs['Low_Salary'].replace(' por semana', '', regex=True)\n",
    "Jobs['High_Salary'] = Jobs['High_Salary'].replace(' por semana', '', regex=True)\n",
    "Jobs['Low_Salary'] = pd.to_numeric(Jobs['Low_Salary'], errors='coerce')\n",
    "Jobs['High_Salary'] = pd.to_numeric(Jobs['High_Salary'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c0485e60",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Posted</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "      <th>Frecuency_Salary</th>\n",
       "      <th>Low_Salary</th>\n",
       "      <th>High_Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>hora</td>\n",
       "      <td>26.17</td>\n",
       "      <td>28.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=bd203b586ac89...</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>año</td>\n",
       "      <td>89100.00</td>\n",
       "      <td>108900.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Job  \\\n",
       "0            Market Development Representative 16702   \n",
       "1                   Vice President, Quality Services   \n",
       "2                                   Business Analyst   \n",
       "3  Corporate Strategy and Business Development Ma...   \n",
       "4                Data Analyst, Business Intelligence   \n",
       "\n",
       "                                                 URL  \\\n",
       "0  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "1  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "2  https://www.indeed.com/rc/clk?jk=bd203b586ac89...   \n",
       "3  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "4  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "\n",
       "                    Company                            Location  \\\n",
       "0  Corps Team Services, LLC                             Arizona   \n",
       "1   Family Support Services               Tampa, FL+2 locations   \n",
       "2               Leonardo247                              Remote   \n",
       "3       United Therapeutics          Research Triangle Park, NC   \n",
       "4             Land O' Frost  Madisonville, KY 42431+2 locations   \n",
       "\n",
       "                       Salary Posted                    ID  \\\n",
       "0    $26.17 - $28.17 por hora    NaN   sj_cd2707991b8bc653   \n",
       "1                         NaN    NaN   sj_aa0f5247ff3deca7   \n",
       "2                         NaN    NaN  job_bd203b586ac89e05   \n",
       "3                         NaN    NaN   sj_8cb506d1ac43bc43   \n",
       "4  $89,100 - $108,900 por año    NaN   sj_bdfad91ca716ac0f   \n",
       "\n",
       "                                     Job_Description Frecuency_Salary  \\\n",
       "0  Iniciar sesión_x000D_\\nInicio del contenido pr...             hora   \n",
       "1  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "2  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "3  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "4  Iniciar sesión_x000D_\\nInicio del contenido pr...              año   \n",
       "\n",
       "   Low_Salary  High_Salary  \n",
       "0       26.17        28.17  \n",
       "1         NaN          NaN  \n",
       "2         NaN          NaN  \n",
       "3         NaN          NaN  \n",
       "4    89100.00    108900.00  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc183d72",
   "metadata": {},
   "source": [
    "Good! Now we need to standardize the Salaries, into a unique frecuency. The most common is to show the salary yearly. So let's transform all the non-yearly salaries into a yearly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4a5bfdbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs['Low_Salary'] = Jobs['Low_Salary'].astype(float)\n",
    "Jobs['High_Salary'] = Jobs['High_Salary'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6bef3178",
   "metadata": {},
   "outputs": [],
   "source": [
    "def transform_salary(row):\n",
    "    if row['Frecuency_Salary'] == 'año':\n",
    "        factor = 1\n",
    "    elif row['Frecuency_Salary'] == 'mes':\n",
    "        factor = 12\n",
    "    elif row['Frecuency_Salary'] == 'semana':\n",
    "        factor = 49\n",
    "    elif row['Frecuency_Salary'] == 'día':\n",
    "        factor = 230\n",
    "    elif row['Frecuency_Salary'] == 'hora':\n",
    "        factor = 1840\n",
    "    else:\n",
    "        factor = 1\n",
    "    row['Low_Salary'] *= factor\n",
    "    row['High_Salary'] *= factor\n",
    "    return row\n",
    "\n",
    "Jobs = Jobs.apply(transform_salary, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5e831fff",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Posted</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "      <th>Frecuency_Salary</th>\n",
       "      <th>Low_Salary</th>\n",
       "      <th>High_Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>hora</td>\n",
       "      <td>48152.8</td>\n",
       "      <td>51832.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=bd203b586ac89...</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>año</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>108900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Job  \\\n",
       "0            Market Development Representative 16702   \n",
       "1                   Vice President, Quality Services   \n",
       "2                                   Business Analyst   \n",
       "3  Corporate Strategy and Business Development Ma...   \n",
       "4                Data Analyst, Business Intelligence   \n",
       "\n",
       "                                                 URL  \\\n",
       "0  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "1  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "2  https://www.indeed.com/rc/clk?jk=bd203b586ac89...   \n",
       "3  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "4  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "\n",
       "                    Company                            Location  \\\n",
       "0  Corps Team Services, LLC                             Arizona   \n",
       "1   Family Support Services               Tampa, FL+2 locations   \n",
       "2               Leonardo247                              Remote   \n",
       "3       United Therapeutics          Research Triangle Park, NC   \n",
       "4             Land O' Frost  Madisonville, KY 42431+2 locations   \n",
       "\n",
       "                       Salary Posted                    ID  \\\n",
       "0    $26.17 - $28.17 por hora    NaN   sj_cd2707991b8bc653   \n",
       "1                         NaN    NaN   sj_aa0f5247ff3deca7   \n",
       "2                         NaN    NaN  job_bd203b586ac89e05   \n",
       "3                         NaN    NaN   sj_8cb506d1ac43bc43   \n",
       "4  $89,100 - $108,900 por año    NaN   sj_bdfad91ca716ac0f   \n",
       "\n",
       "                                     Job_Description Frecuency_Salary  \\\n",
       "0  Iniciar sesión_x000D_\\nInicio del contenido pr...             hora   \n",
       "1  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "2  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "3  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "4  Iniciar sesión_x000D_\\nInicio del contenido pr...              año   \n",
       "\n",
       "   Low_Salary  High_Salary  \n",
       "0     48152.8      51832.8  \n",
       "1         NaN          NaN  \n",
       "2         NaN          NaN  \n",
       "3         NaN          NaN  \n",
       "4     89100.0     108900.0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a27554cc",
   "metadata": {},
   "source": [
    "Nice! The last step here, is to create a unique column with salaries. The purpose of this is to compare and analyze, since some of the salaries are ranges and some others are not. The best approach for this is to have a mean salary column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5f3b137b",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs['Mean_Salary'] = (Jobs['Low_Salary'] + Jobs['High_Salary']) / 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "5e7c3882",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Posted</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "      <th>Frecuency_Salary</th>\n",
       "      <th>Low_Salary</th>\n",
       "      <th>High_Salary</th>\n",
       "      <th>Mean_Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>hora</td>\n",
       "      <td>48152.8</td>\n",
       "      <td>51832.8</td>\n",
       "      <td>49992.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=bd203b586ac89...</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>año</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>108900.0</td>\n",
       "      <td>99000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Job  \\\n",
       "0            Market Development Representative 16702   \n",
       "1                   Vice President, Quality Services   \n",
       "2                                   Business Analyst   \n",
       "3  Corporate Strategy and Business Development Ma...   \n",
       "4                Data Analyst, Business Intelligence   \n",
       "\n",
       "                                                 URL  \\\n",
       "0  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "1  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "2  https://www.indeed.com/rc/clk?jk=bd203b586ac89...   \n",
       "3  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "4  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "\n",
       "                    Company                            Location  \\\n",
       "0  Corps Team Services, LLC                             Arizona   \n",
       "1   Family Support Services               Tampa, FL+2 locations   \n",
       "2               Leonardo247                              Remote   \n",
       "3       United Therapeutics          Research Triangle Park, NC   \n",
       "4             Land O' Frost  Madisonville, KY 42431+2 locations   \n",
       "\n",
       "                       Salary Posted                    ID  \\\n",
       "0    $26.17 - $28.17 por hora    NaN   sj_cd2707991b8bc653   \n",
       "1                         NaN    NaN   sj_aa0f5247ff3deca7   \n",
       "2                         NaN    NaN  job_bd203b586ac89e05   \n",
       "3                         NaN    NaN   sj_8cb506d1ac43bc43   \n",
       "4  $89,100 - $108,900 por año    NaN   sj_bdfad91ca716ac0f   \n",
       "\n",
       "                                     Job_Description Frecuency_Salary  \\\n",
       "0  Iniciar sesión_x000D_\\nInicio del contenido pr...             hora   \n",
       "1  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "2  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "3  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "4  Iniciar sesión_x000D_\\nInicio del contenido pr...              año   \n",
       "\n",
       "   Low_Salary  High_Salary  Mean_Salary  \n",
       "0     48152.8      51832.8      49992.8  \n",
       "1         NaN          NaN          NaN  \n",
       "2         NaN          NaN          NaN  \n",
       "3         NaN          NaN          NaN  \n",
       "4     89100.0     108900.0      99000.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70ec860b",
   "metadata": {},
   "source": [
    "PERFECT!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6825d0b",
   "metadata": {},
   "source": [
    "## Jobs Group\n",
    "\n",
    "Since the Job names are quite heterogeneus, it's mandatory to group them in a few job groups. Let's do it with the dictionary categories.xlsx: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f6993041",
   "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>keyword</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>data engineer</td>\n",
       "      <td>Data Engineer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>data scientist</td>\n",
       "      <td>Data Scientist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>data science</td>\n",
       "      <td>Data Scientist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ds</td>\n",
       "      <td>Data Scientist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>machine learning</td>\n",
       "      <td>ML/AI Engineer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            keyword        category\n",
       "0     data engineer   Data Engineer\n",
       "1    data scientist  Data Scientist\n",
       "2      data science  Data Scientist\n",
       "3               ds   Data Scientist\n",
       "4  machine learning  ML/AI Engineer"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "category_df = pd.read_excel('master_data\\categories.xlsx')\n",
    "\n",
    "category_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4d980992",
   "metadata": {},
   "outputs": [],
   "source": [
    "categories = category_df.set_index('keyword')['category'].to_dict()\n",
    "\n",
    "def assign_category(job):\n",
    "    job = job.lower()\n",
    "    for keyword, category in categories.items():\n",
    "        if keyword in job:\n",
    "            return category\n",
    "    return 'Others'\n",
    "\n",
    "Jobs['Jobs_Group'] = Jobs['Job'].apply(assign_category)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d974ec59",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Financial Analyst            24931\n",
      "Business Analyst             22309\n",
      "Data Analyst                 16786\n",
      "Data Engineer                14098\n",
      "Controller                   13592\n",
      "Data Scientist                9797\n",
      "Business Intelligence         8337\n",
      "CFO                           7663\n",
      "Analyst                       7510\n",
      "Finance                       7495\n",
      "Operations Analyst            4945\n",
      "ML/AI Engineer                3878\n",
      "Others                        3814\n",
      "Statistician/Mathemathics     1237\n",
      "Name: Jobs_Group, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "category_counts = Jobs['Jobs_Group'].value_counts()\n",
    "\n",
    "print(category_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "848bb216",
   "metadata": {},
   "source": [
    "Nice!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b45c2c2",
   "metadata": {},
   "source": [
    "## Location"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db3b39b7",
   "metadata": {},
   "source": [
    "The next step would be to clean the Location column. Would be nice to have a City and State column. The best way to split it, is to split it two columns by comma, getting all the string before, and the two letters after comma. The first column will be the city, and the second one the state. Let's work on it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "edec70a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs[['City', 'State']] = Jobs['Location'].str.extract(r'^(.+),\\s([A-Z]{2})', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "cce8d56f",
   "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</th>\n",
       "      <th>URL</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Posted</th>\n",
       "      <th>ID</th>\n",
       "      <th>Job_Description</th>\n",
       "      <th>Frecuency_Salary</th>\n",
       "      <th>Low_Salary</th>\n",
       "      <th>High_Salary</th>\n",
       "      <th>Mean_Salary</th>\n",
       "      <th>Jobs_Group</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>hora</td>\n",
       "      <td>48152.8</td>\n",
       "      <td>51832.8</td>\n",
       "      <td>49992.8</td>\n",
       "      <td>Data Analyst</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CFO</td>\n",
       "      <td>Tampa</td>\n",
       "      <td>FL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>https://www.indeed.com/rc/clk?jk=bd203b586ac89...</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>Research Triangle Park</td>\n",
       "      <td>NC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>https://www.indeed.com/pagead/clk?mo=r&amp;ad=-6NY...</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Iniciar sesión_x000D_\\nInicio del contenido pr...</td>\n",
       "      <td>año</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>108900.0</td>\n",
       "      <td>99000.0</td>\n",
       "      <td>Data Analyst</td>\n",
       "      <td>Madisonville</td>\n",
       "      <td>KY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 Job  \\\n",
       "0            Market Development Representative 16702   \n",
       "1                   Vice President, Quality Services   \n",
       "2                                   Business Analyst   \n",
       "3  Corporate Strategy and Business Development Ma...   \n",
       "4                Data Analyst, Business Intelligence   \n",
       "\n",
       "                                                 URL  \\\n",
       "0  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "1  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "2  https://www.indeed.com/rc/clk?jk=bd203b586ac89...   \n",
       "3  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "4  https://www.indeed.com/pagead/clk?mo=r&ad=-6NY...   \n",
       "\n",
       "                    Company                            Location  \\\n",
       "0  Corps Team Services, LLC                             Arizona   \n",
       "1   Family Support Services               Tampa, FL+2 locations   \n",
       "2               Leonardo247                              Remote   \n",
       "3       United Therapeutics          Research Triangle Park, NC   \n",
       "4             Land O' Frost  Madisonville, KY 42431+2 locations   \n",
       "\n",
       "                       Salary Posted                    ID  \\\n",
       "0    $26.17 - $28.17 por hora    NaN   sj_cd2707991b8bc653   \n",
       "1                         NaN    NaN   sj_aa0f5247ff3deca7   \n",
       "2                         NaN    NaN  job_bd203b586ac89e05   \n",
       "3                         NaN    NaN   sj_8cb506d1ac43bc43   \n",
       "4  $89,100 - $108,900 por año    NaN   sj_bdfad91ca716ac0f   \n",
       "\n",
       "                                     Job_Description Frecuency_Salary  \\\n",
       "0  Iniciar sesión_x000D_\\nInicio del contenido pr...             hora   \n",
       "1  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "2  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "3  Iniciar sesión_x000D_\\nInicio del contenido pr...              NaN   \n",
       "4  Iniciar sesión_x000D_\\nInicio del contenido pr...              año   \n",
       "\n",
       "   Low_Salary  High_Salary  Mean_Salary        Jobs_Group  \\\n",
       "0     48152.8      51832.8      49992.8      Data Analyst   \n",
       "1         NaN          NaN          NaN               CFO   \n",
       "2         NaN          NaN          NaN  Business Analyst   \n",
       "3         NaN          NaN          NaN  Business Analyst   \n",
       "4     89100.0     108900.0      99000.0      Data Analyst   \n",
       "\n",
       "                     City State  \n",
       "0                     NaN   NaN  \n",
       "1                   Tampa    FL  \n",
       "2                     NaN   NaN  \n",
       "3  Research Triangle Park    NC  \n",
       "4            Madisonville    KY  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f71f5a62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, 'Tampa', nan, ..., 'Cerritos', 'Houston', 'Marinette'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs['City'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "10c196aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, 'FL', nan, ..., 'CA', 'TX', 'WI'], dtype=object)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs['State'].values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a6e8031",
   "metadata": {},
   "source": [
    "Nice! Now a minor problem, is that there is some Locations, that are directy the state (e.g. California). In this cases, let's create a function, using the dictionary states.xlsx:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "074723db",
   "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>State</th>\n",
       "      <th>Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State Code\n",
       "0     Alabama   AL\n",
       "1      Alaska   AK\n",
       "2     Arizona   AZ\n",
       "3    Arkansas   AR\n",
       "4  California   CA"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_df = pd.read_excel('master_data\\states.xlsx')\n",
    "\n",
    "states_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0a675b87",
   "metadata": {},
   "outputs": [],
   "source": [
    "states_dict = states_df.set_index('State')['Code'].to_dict()\n",
    "\n",
    "def assign_state(row):\n",
    "    if pd.isnull(row['State']):\n",
    "        location = str(row['Location'])\n",
    "        for state, code in states_dict.items():\n",
    "            if state.lower() in location.lower():\n",
    "                return code\n",
    "    return row['State']\n",
    "\n",
    "Jobs['State'] = Jobs.apply(assign_state, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bac2b562",
   "metadata": {},
   "source": [
    "Perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df34c3a9",
   "metadata": {},
   "source": [
    "## Remote\n",
    "\n",
    "An interesting column would be if a job is remote or hybrid or none of these. In order to do so, we can use a dictionary with keywords:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "35eb5124",
   "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>Value</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hybrid</td>\n",
       "      <td>Hybrid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hybrid</td>\n",
       "      <td>Hybrid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Remote</td>\n",
       "      <td>Remote</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>remote</td>\n",
       "      <td>Remote</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Work From Home</td>\n",
       "      <td>Remote</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Value Category\n",
       "0          Hybrid   Hybrid\n",
       "1          hybrid   Hybrid\n",
       "2          Remote   Remote\n",
       "3          remote   Remote\n",
       "4  Work From Home   Remote"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "remote_df = pd.read_excel('master_data/remote.xlsx')\n",
    "\n",
    "remote_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "0aca6fea",
   "metadata": {},
   "outputs": [],
   "source": [
    "remote_dict = remote_df.set_index('Value')['Category'].to_dict()\n",
    "\n",
    "def assign_remote(row):\n",
    "    for col in ['Job', 'Location', 'Job_Description']:\n",
    "        col_value = str(row[col])\n",
    "        for value, category in remote_dict.items():\n",
    "            if value.lower() in col_value.lower():\n",
    "                return category\n",
    "    return None\n",
    "\n",
    "Jobs['Remote'] = Jobs.apply(assign_remote, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "2997f5a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Remote    29600\n",
      "Hybrid    27393\n",
      "Name: Remote, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "remote_counts = Jobs['Remote'].value_counts()\n",
    "\n",
    "print(remote_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4a6baae",
   "metadata": {},
   "source": [
    "Looks great!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8dfae8af",
   "metadata": {},
   "source": [
    "## Profile\n",
    "\n",
    "Another interesting column is the profile. In this column we will show if it's a Lead, Senior or a Junior position. As we did in the remote column, we will use a dictionary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "7305fdc5",
   "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>Value</th>\n",
       "      <th>Profile</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>junior</td>\n",
       "      <td>Junior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Junior</td>\n",
       "      <td>Junior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jr</td>\n",
       "      <td>Junior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jr.</td>\n",
       "      <td>Junior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>senior</td>\n",
       "      <td>Senior</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Value Profile\n",
       "0  junior  Junior\n",
       "1  Junior  Junior\n",
       "2      Jr  Junior\n",
       "3     Jr.  Junior\n",
       "4  senior  Senior"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "profile_df = pd.read_excel('master_data\\profile.xlsx')\n",
    "\n",
    "profile_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "e288bae3",
   "metadata": {},
   "outputs": [],
   "source": [
    "profile_dict = profile_df.set_index('Value')['Profile'].to_dict()\n",
    "\n",
    "def assign_profile(row):\n",
    "    for col in ['Job']:\n",
    "        for value, profile in profile_dict.items():\n",
    "            if value.lower() in row[col].lower():\n",
    "                return profile\n",
    "    return None\n",
    "\n",
    "Jobs['Profile'] = Jobs.apply(assign_profile, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "fee84a62",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Senior    28842\n",
      "Lead      23333\n",
      "Junior     1738\n",
      "Name: Profile, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "profile_counts = Jobs['Profile'].value_counts()\n",
    "\n",
    "print(profile_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3532d686",
   "metadata": {},
   "source": [
    "Perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "109873ff",
   "metadata": {},
   "source": [
    "## Skills\n",
    "\n",
    "Now, let's deep into the job descriptions. Would be nice to have a Skills column. We can have it using a dictionary skills.xlsx:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "5a51f2b1",
   "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>Skills</th>\n",
       "      <th>Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Access</td>\n",
       "      <td>Access</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adobe Analytics</td>\n",
       "      <td>Adobe Analytics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AWS</td>\n",
       "      <td>AWS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Amazon Web Services</td>\n",
       "      <td>AWS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Amazon Web</td>\n",
       "      <td>AWS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Skills            Group\n",
       "0               Access           Access\n",
       "1      Adobe Analytics  Adobe Analytics\n",
       "2                  AWS              AWS\n",
       "3  Amazon Web Services              AWS\n",
       "4           Amazon Web              AWS"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "skills_df = pd.read_excel('master_data\\skills.xlsx')\n",
    "\n",
    "skills_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "c9f148be",
   "metadata": {},
   "outputs": [],
   "source": [
    "skills_dict = skills_df.set_index('Skills')['Group'].to_dict()\n",
    "\n",
    "Jobs['Skills'] = Jobs['Job_Description'].apply(lambda x: list(set([skills_dict[skill] for skill in skills_dict.keys() if skill in x])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "af89757a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type=\"text/css\">\n",
       "</style>\n",
       "<table id=\"T_8056c\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th class=\"blank level0\" >&nbsp;</th>\n",
       "      <th id=\"T_8056c_level0_col0\" class=\"col_heading level0 col0\" >Percentage</th>\n",
       "      <th id=\"T_8056c_level0_col1\" class=\"col_heading level0 col1\" >Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row0\" class=\"row_heading level0 row0\" >Bachelor</th>\n",
       "      <td id=\"T_8056c_row0_col0\" class=\"data row0 col0\" >65.30%</td>\n",
       "      <td id=\"T_8056c_row0_col1\" class=\"data row0 col1\" >95592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row1\" class=\"row_heading level0 row1\" >Office</th>\n",
       "      <td id=\"T_8056c_row1_col0\" class=\"data row1 col0\" >33.64%</td>\n",
       "      <td id=\"T_8056c_row1_col1\" class=\"data row1 col1\" >49247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row2\" class=\"row_heading level0 row2\" >Excel</th>\n",
       "      <td id=\"T_8056c_row2_col0\" class=\"data row2 col0\" >28.32%</td>\n",
       "      <td id=\"T_8056c_row2_col1\" class=\"data row2 col1\" >41455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row3\" class=\"row_heading level0 row3\" >SQL</th>\n",
       "      <td id=\"T_8056c_row3_col0\" class=\"data row3 col0\" >28.19%</td>\n",
       "      <td id=\"T_8056c_row3_col1\" class=\"data row3 col1\" >41269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row4\" class=\"row_heading level0 row4\" >Python</th>\n",
       "      <td id=\"T_8056c_row4_col0\" class=\"data row4 col0\" >19.00%</td>\n",
       "      <td id=\"T_8056c_row4_col1\" class=\"data row4 col1\" >27813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row5\" class=\"row_heading level0 row5\" >Master</th>\n",
       "      <td id=\"T_8056c_row5_col0\" class=\"data row5 col0\" >18.48%</td>\n",
       "      <td id=\"T_8056c_row5_col1\" class=\"data row5 col1\" >27050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row6\" class=\"row_heading level0 row6\" >Access</th>\n",
       "      <td id=\"T_8056c_row6_col0\" class=\"data row6 col0\" >16.52%</td>\n",
       "      <td id=\"T_8056c_row6_col1\" class=\"data row6 col1\" >24184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row7\" class=\"row_heading level0 row7\" >PowerPoint</th>\n",
       "      <td id=\"T_8056c_row7_col0\" class=\"data row7 col0\" >12.75%</td>\n",
       "      <td id=\"T_8056c_row7_col1\" class=\"data row7 col1\" >18663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row8\" class=\"row_heading level0 row8\" >Tableau</th>\n",
       "      <td id=\"T_8056c_row8_col0\" class=\"data row8 col0\" >12.67%</td>\n",
       "      <td id=\"T_8056c_row8_col1\" class=\"data row8 col1\" >18546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row9\" class=\"row_heading level0 row9\" >Power BI</th>\n",
       "      <td id=\"T_8056c_row9_col0\" class=\"data row9 col0\" >12.49%</td>\n",
       "      <td id=\"T_8056c_row9_col1\" class=\"data row9 col1\" >18279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row10\" class=\"row_heading level0 row10\" >CPA</th>\n",
       "      <td id=\"T_8056c_row10_col0\" class=\"data row10 col0\" >12.09%</td>\n",
       "      <td id=\"T_8056c_row10_col1\" class=\"data row10 col1\" >17692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row11\" class=\"row_heading level0 row11\" >Word</th>\n",
       "      <td id=\"T_8056c_row11_col0\" class=\"data row11 col0\" >9.81%</td>\n",
       "      <td id=\"T_8056c_row11_col1\" class=\"data row11 col1\" >14367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row12\" class=\"row_heading level0 row12\" >Agile</th>\n",
       "      <td id=\"T_8056c_row12_col0\" class=\"data row12 col0\" >8.00%</td>\n",
       "      <td id=\"T_8056c_row12_col1\" class=\"data row12 col1\" >11706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row13\" class=\"row_heading level0 row13\" >AWS</th>\n",
       "      <td id=\"T_8056c_row13_col0\" class=\"data row13 col0\" >7.72%</td>\n",
       "      <td id=\"T_8056c_row13_col1\" class=\"data row13 col1\" >11307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row14\" class=\"row_heading level0 row14\" >ERP</th>\n",
       "      <td id=\"T_8056c_row14_col0\" class=\"data row14 col0\" >7.65%</td>\n",
       "      <td id=\"T_8056c_row14_col1\" class=\"data row14 col1\" >11201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row15\" class=\"row_heading level0 row15\" >R</th>\n",
       "      <td id=\"T_8056c_row15_col0\" class=\"data row15 col0\" >7.16%</td>\n",
       "      <td id=\"T_8056c_row15_col1\" class=\"data row15 col1\" >10480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row16\" class=\"row_heading level0 row16\" >SAP</th>\n",
       "      <td id=\"T_8056c_row16_col0\" class=\"data row16 col0\" >6.90%</td>\n",
       "      <td id=\"T_8056c_row16_col1\" class=\"data row16 col1\" >10108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row17\" class=\"row_heading level0 row17\" >Azure</th>\n",
       "      <td id=\"T_8056c_row17_col0\" class=\"data row17 col0\" >6.41%</td>\n",
       "      <td id=\"T_8056c_row17_col1\" class=\"data row17 col1\" >9379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row18\" class=\"row_heading level0 row18\" >Oracle</th>\n",
       "      <td id=\"T_8056c_row18_col0\" class=\"data row18 col0\" >6.15%</td>\n",
       "      <td id=\"T_8056c_row18_col1\" class=\"data row18 col1\" >8997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row19\" class=\"row_heading level0 row19\" >Artificial Intelligence</th>\n",
       "      <td id=\"T_8056c_row19_col0\" class=\"data row19 col0\" >6.03%</td>\n",
       "      <td id=\"T_8056c_row19_col1\" class=\"data row19 col1\" >8831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row20\" class=\"row_heading level0 row20\" >MBA</th>\n",
       "      <td id=\"T_8056c_row20_col0\" class=\"data row20 col0\" >5.78%</td>\n",
       "      <td id=\"T_8056c_row20_col1\" class=\"data row20 col1\" >8458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row21\" class=\"row_heading level0 row21\" >Spark</th>\n",
       "      <td id=\"T_8056c_row21_col0\" class=\"data row21 col0\" >4.95%</td>\n",
       "      <td id=\"T_8056c_row21_col1\" class=\"data row21 col1\" >7243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row22\" class=\"row_heading level0 row22\" >English</th>\n",
       "      <td id=\"T_8056c_row22_col0\" class=\"data row22 col0\" >4.91%</td>\n",
       "      <td id=\"T_8056c_row22_col1\" class=\"data row22 col1\" >7182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row23\" class=\"row_heading level0 row23\" >Machine Learning</th>\n",
       "      <td id=\"T_8056c_row23_col0\" class=\"data row23 col0\" >4.86%</td>\n",
       "      <td id=\"T_8056c_row23_col1\" class=\"data row23 col1\" >7111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row24\" class=\"row_heading level0 row24\" >Java</th>\n",
       "      <td id=\"T_8056c_row24_col0\" class=\"data row24 col0\" >3.80%</td>\n",
       "      <td id=\"T_8056c_row24_col1\" class=\"data row24 col1\" >5570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row25\" class=\"row_heading level0 row25\" >Snowflake</th>\n",
       "      <td id=\"T_8056c_row25_col0\" class=\"data row25 col0\" >3.41%</td>\n",
       "      <td id=\"T_8056c_row25_col1\" class=\"data row25 col1\" >4986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row26\" class=\"row_heading level0 row26\" >PhD</th>\n",
       "      <td id=\"T_8056c_row26_col0\" class=\"data row26 col0\" >3.14%</td>\n",
       "      <td id=\"T_8056c_row26_col1\" class=\"data row26 col1\" >4601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row27\" class=\"row_heading level0 row27\" >Salesforce</th>\n",
       "      <td id=\"T_8056c_row27_col0\" class=\"data row27 col0\" >3.04%</td>\n",
       "      <td id=\"T_8056c_row27_col1\" class=\"data row27 col1\" >4455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row28\" class=\"row_heading level0 row28\" >Hadoop</th>\n",
       "      <td id=\"T_8056c_row28_col0\" class=\"data row28 col0\" >2.70%</td>\n",
       "      <td id=\"T_8056c_row28_col1\" class=\"data row28 col1\" >3959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row29\" class=\"row_heading level0 row29\" >Tensor Flow</th>\n",
       "      <td id=\"T_8056c_row29_col0\" class=\"data row29 col0\" >2.30%</td>\n",
       "      <td id=\"T_8056c_row29_col1\" class=\"data row29 col1\" >3361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row30\" class=\"row_heading level0 row30\" >Databricks</th>\n",
       "      <td id=\"T_8056c_row30_col0\" class=\"data row30 col0\" >2.20%</td>\n",
       "      <td id=\"T_8056c_row30_col1\" class=\"data row30 col1\" >3218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row31\" class=\"row_heading level0 row31\" >C++</th>\n",
       "      <td id=\"T_8056c_row31_col0\" class=\"data row31 col0\" >2.18%</td>\n",
       "      <td id=\"T_8056c_row31_col1\" class=\"data row31 col1\" >3192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row32\" class=\"row_heading level0 row32\" >HTML</th>\n",
       "      <td id=\"T_8056c_row32_col0\" class=\"data row32 col0\" >1.98%</td>\n",
       "      <td id=\"T_8056c_row32_col1\" class=\"data row32 col1\" >2899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row33\" class=\"row_heading level0 row33\" >Jira</th>\n",
       "      <td id=\"T_8056c_row33_col0\" class=\"data row33 col0\" >1.93%</td>\n",
       "      <td id=\"T_8056c_row33_col1\" class=\"data row33 col1\" >2821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row34\" class=\"row_heading level0 row34\" >SSIS</th>\n",
       "      <td id=\"T_8056c_row34_col0\" class=\"data row34 col0\" >1.90%</td>\n",
       "      <td id=\"T_8056c_row34_col1\" class=\"data row34 col1\" >2783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row35\" class=\"row_heading level0 row35\" >JavaScript</th>\n",
       "      <td id=\"T_8056c_row35_col0\" class=\"data row35 col0\" >1.88%</td>\n",
       "      <td id=\"T_8056c_row35_col1\" class=\"data row35 col1\" >2759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row36\" class=\"row_heading level0 row36\" >QuickBooks</th>\n",
       "      <td id=\"T_8056c_row36_col0\" class=\"data row36 col0\" >1.85%</td>\n",
       "      <td id=\"T_8056c_row36_col1\" class=\"data row36 col1\" >2709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row37\" class=\"row_heading level0 row37\" >CMA</th>\n",
       "      <td id=\"T_8056c_row37_col0\" class=\"data row37 col0\" >1.83%</td>\n",
       "      <td id=\"T_8056c_row37_col1\" class=\"data row37 col1\" >2679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row38\" class=\"row_heading level0 row38\" >VBA</th>\n",
       "      <td id=\"T_8056c_row38_col0\" class=\"data row38 col0\" >1.78%</td>\n",
       "      <td id=\"T_8056c_row38_col1\" class=\"data row38 col1\" >2604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row39\" class=\"row_heading level0 row39\" >Dynamics 365</th>\n",
       "      <td id=\"T_8056c_row39_col0\" class=\"data row39 col0\" >1.63%</td>\n",
       "      <td id=\"T_8056c_row39_col1\" class=\"data row39 col1\" >2385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row40\" class=\"row_heading level0 row40\" >Qlik</th>\n",
       "      <td id=\"T_8056c_row40_col0\" class=\"data row40 col0\" >1.62%</td>\n",
       "      <td id=\"T_8056c_row40_col1\" class=\"data row40 col1\" >2378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row41\" class=\"row_heading level0 row41\" >Docker</th>\n",
       "      <td id=\"T_8056c_row41_col0\" class=\"data row41 col0\" >1.54%</td>\n",
       "      <td id=\"T_8056c_row41_col1\" class=\"data row41 col1\" >2254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row42\" class=\"row_heading level0 row42\" >SSRS</th>\n",
       "      <td id=\"T_8056c_row42_col0\" class=\"data row42 col0\" >1.51%</td>\n",
       "      <td id=\"T_8056c_row42_col1\" class=\"data row42 col1\" >2204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row43\" class=\"row_heading level0 row43\" >Airflow</th>\n",
       "      <td id=\"T_8056c_row43_col0\" class=\"data row43 col0\" >1.48%</td>\n",
       "      <td id=\"T_8056c_row43_col1\" class=\"data row43 col1\" >2167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row44\" class=\"row_heading level0 row44\" >Looker</th>\n",
       "      <td id=\"T_8056c_row44_col0\" class=\"data row44 col0\" >1.42%</td>\n",
       "      <td id=\"T_8056c_row44_col1\" class=\"data row44 col1\" >2079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row45\" class=\"row_heading level0 row45\" >Hyperion</th>\n",
       "      <td id=\"T_8056c_row45_col0\" class=\"data row45 col0\" >1.42%</td>\n",
       "      <td id=\"T_8056c_row45_col1\" class=\"data row45 col1\" >2076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row46\" class=\"row_heading level0 row46\" >Spanish</th>\n",
       "      <td id=\"T_8056c_row46_col0\" class=\"data row46 col0\" >1.33%</td>\n",
       "      <td id=\"T_8056c_row46_col1\" class=\"data row46 col1\" >1941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row47\" class=\"row_heading level0 row47\" >Cognos</th>\n",
       "      <td id=\"T_8056c_row47_col0\" class=\"data row47 col0\" >1.30%</td>\n",
       "      <td id=\"T_8056c_row47_col1\" class=\"data row47 col1\" >1905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row48\" class=\"row_heading level0 row48\" >Sage</th>\n",
       "      <td id=\"T_8056c_row48_col0\" class=\"data row48 col0\" >1.29%</td>\n",
       "      <td id=\"T_8056c_row48_col1\" class=\"data row48 col1\" >1891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row49\" class=\"row_heading level0 row49\" >Google Cloud</th>\n",
       "      <td id=\"T_8056c_row49_col0\" class=\"data row49 col0\" >1.27%</td>\n",
       "      <td id=\"T_8056c_row49_col1\" class=\"data row49 col1\" >1865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row50\" class=\"row_heading level0 row50\" >C#</th>\n",
       "      <td id=\"T_8056c_row50_col0\" class=\"data row50 col0\" >1.26%</td>\n",
       "      <td id=\"T_8056c_row50_col1\" class=\"data row50 col1\" >1840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row51\" class=\"row_heading level0 row51\" >.NET</th>\n",
       "      <td id=\"T_8056c_row51_col0\" class=\"data row51 col0\" >1.16%</td>\n",
       "      <td id=\"T_8056c_row51_col1\" class=\"data row51 col1\" >1696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row52\" class=\"row_heading level0 row52\" >CFA</th>\n",
       "      <td id=\"T_8056c_row52_col0\" class=\"data row52 col0\" >1.09%</td>\n",
       "      <td id=\"T_8056c_row52_col1\" class=\"data row52 col1\" >1601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row53\" class=\"row_heading level0 row53\" >DAX</th>\n",
       "      <td id=\"T_8056c_row53_col0\" class=\"data row53 col0\" >0.94%</td>\n",
       "      <td id=\"T_8056c_row53_col1\" class=\"data row53 col1\" >1379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row54\" class=\"row_heading level0 row54\" >GitHub</th>\n",
       "      <td id=\"T_8056c_row54_col0\" class=\"data row54 col0\" >0.92%</td>\n",
       "      <td id=\"T_8056c_row54_col1\" class=\"data row54 col1\" >1353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row55\" class=\"row_heading level0 row55\" >CSS</th>\n",
       "      <td id=\"T_8056c_row55_col0\" class=\"data row55 col0\" >0.84%</td>\n",
       "      <td id=\"T_8056c_row55_col1\" class=\"data row55 col1\" >1227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row56\" class=\"row_heading level0 row56\" >Pandas</th>\n",
       "      <td id=\"T_8056c_row56_col0\" class=\"data row56 col0\" >0.82%</td>\n",
       "      <td id=\"T_8056c_row56_col1\" class=\"data row56 col1\" >1206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row57\" class=\"row_heading level0 row57\" >Teradata</th>\n",
       "      <td id=\"T_8056c_row57_col0\" class=\"data row57 col0\" >0.78%</td>\n",
       "      <td id=\"T_8056c_row57_col1\" class=\"data row57 col1\" >1146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row58\" class=\"row_heading level0 row58\" >Power Query</th>\n",
       "      <td id=\"T_8056c_row58_col0\" class=\"data row58 col0\" >0.74%</td>\n",
       "      <td id=\"T_8056c_row58_col1\" class=\"data row58 col1\" >1085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row59\" class=\"row_heading level0 row59\" >Deep Learning</th>\n",
       "      <td id=\"T_8056c_row59_col0\" class=\"data row59 col0\" >0.72%</td>\n",
       "      <td id=\"T_8056c_row59_col1\" class=\"data row59 col1\" >1053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row60\" class=\"row_heading level0 row60\" >MongoDB</th>\n",
       "      <td id=\"T_8056c_row60_col0\" class=\"data row60 col0\" >0.71%</td>\n",
       "      <td id=\"T_8056c_row60_col1\" class=\"data row60 col1\" >1044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row61\" class=\"row_heading level0 row61\" >Essbase</th>\n",
       "      <td id=\"T_8056c_row61_col0\" class=\"data row61 col0\" >0.59%</td>\n",
       "      <td id=\"T_8056c_row61_col1\" class=\"data row61 col1\" >866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row62\" class=\"row_heading level0 row62\" >Power Automate</th>\n",
       "      <td id=\"T_8056c_row62_col0\" class=\"data row62 col0\" >0.57%</td>\n",
       "      <td id=\"T_8056c_row62_col1\" class=\"data row62 col1\" >830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row63\" class=\"row_heading level0 row63\" >SSAS</th>\n",
       "      <td id=\"T_8056c_row63_col0\" class=\"data row63 col0\" >0.57%</td>\n",
       "      <td id=\"T_8056c_row63_col1\" class=\"data row63 col1\" >828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row64\" class=\"row_heading level0 row64\" >PHP</th>\n",
       "      <td id=\"T_8056c_row64_col0\" class=\"data row64 col0\" >0.55%</td>\n",
       "      <td id=\"T_8056c_row64_col1\" class=\"data row64 col1\" >800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row65\" class=\"row_heading level0 row65\" >React</th>\n",
       "      <td id=\"T_8056c_row65_col0\" class=\"data row65 col0\" >0.55%</td>\n",
       "      <td id=\"T_8056c_row65_col1\" class=\"data row65 col1\" >798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row66\" class=\"row_heading level0 row66\" >Jupyter</th>\n",
       "      <td id=\"T_8056c_row66_col0\" class=\"data row66 col0\" >0.53%</td>\n",
       "      <td id=\"T_8056c_row66_col1\" class=\"data row66 col1\" >774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row67\" class=\"row_heading level0 row67\" >Power Apps</th>\n",
       "      <td id=\"T_8056c_row67_col0\" class=\"data row67 col0\" >0.48%</td>\n",
       "      <td id=\"T_8056c_row67_col1\" class=\"data row67 col1\" >698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row68\" class=\"row_heading level0 row68\" >SciKit</th>\n",
       "      <td id=\"T_8056c_row68_col0\" class=\"data row68 col0\" >0.47%</td>\n",
       "      <td id=\"T_8056c_row68_col1\" class=\"data row68 col1\" >688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row69\" class=\"row_heading level0 row69\" >Google Sheets</th>\n",
       "      <td id=\"T_8056c_row69_col0\" class=\"data row69 col0\" >0.43%</td>\n",
       "      <td id=\"T_8056c_row69_col1\" class=\"data row69 col1\" >629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row70\" class=\"row_heading level0 row70\" >Matlab</th>\n",
       "      <td id=\"T_8056c_row70_col0\" class=\"data row70 col0\" >0.42%</td>\n",
       "      <td id=\"T_8056c_row70_col1\" class=\"data row70 col1\" >610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row71\" class=\"row_heading level0 row71\" >NumPy</th>\n",
       "      <td id=\"T_8056c_row71_col0\" class=\"data row71 col0\" >0.40%</td>\n",
       "      <td id=\"T_8056c_row71_col1\" class=\"data row71 col1\" >583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row72\" class=\"row_heading level0 row72\" >Matplotlib</th>\n",
       "      <td id=\"T_8056c_row72_col0\" class=\"data row72 col0\" >0.37%</td>\n",
       "      <td id=\"T_8056c_row72_col1\" class=\"data row72 col1\" >541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row73\" class=\"row_heading level0 row73\" >Chat GPT</th>\n",
       "      <td id=\"T_8056c_row73_col0\" class=\"data row73 col0\" >0.32%</td>\n",
       "      <td id=\"T_8056c_row73_col1\" class=\"data row73 col1\" >471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row74\" class=\"row_heading level0 row74\" >Angular</th>\n",
       "      <td id=\"T_8056c_row74_col0\" class=\"data row74 col0\" >0.32%</td>\n",
       "      <td id=\"T_8056c_row74_col1\" class=\"data row74 col1\" >468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row75\" class=\"row_heading level0 row75\" >Adobe Analytics</th>\n",
       "      <td id=\"T_8056c_row75_col0\" class=\"data row75 col0\" >0.30%</td>\n",
       "      <td id=\"T_8056c_row75_col1\" class=\"data row75 col1\" >432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row76\" class=\"row_heading level0 row76\" >French</th>\n",
       "      <td id=\"T_8056c_row76_col0\" class=\"data row76 col0\" >0.24%</td>\n",
       "      <td id=\"T_8056c_row76_col1\" class=\"data row76 col1\" >350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row77\" class=\"row_heading level0 row77\" >SAP Analytics Cloud</th>\n",
       "      <td id=\"T_8056c_row77_col0\" class=\"data row77 col0\" >0.22%</td>\n",
       "      <td id=\"T_8056c_row77_col1\" class=\"data row77 col1\" >324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row78\" class=\"row_heading level0 row78\" >Neural Network</th>\n",
       "      <td id=\"T_8056c_row78_col0\" class=\"data row78 col0\" >0.22%</td>\n",
       "      <td id=\"T_8056c_row78_col1\" class=\"data row78 col1\" >321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row79\" class=\"row_heading level0 row79\" >Power Pivot</th>\n",
       "      <td id=\"T_8056c_row79_col0\" class=\"data row79 col0\" >0.22%</td>\n",
       "      <td id=\"T_8056c_row79_col1\" class=\"data row79 col1\" >316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row80\" class=\"row_heading level0 row80\" >Fabric</th>\n",
       "      <td id=\"T_8056c_row80_col0\" class=\"data row80 col0\" >0.17%</td>\n",
       "      <td id=\"T_8056c_row80_col1\" class=\"data row80 col1\" >249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row81\" class=\"row_heading level0 row81\" >Plotly</th>\n",
       "      <td id=\"T_8056c_row81_col0\" class=\"data row81 col0\" >0.17%</td>\n",
       "      <td id=\"T_8056c_row81_col1\" class=\"data row81 col1\" >243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row82\" class=\"row_heading level0 row82\" >German</th>\n",
       "      <td id=\"T_8056c_row82_col0\" class=\"data row82 col0\" >0.16%</td>\n",
       "      <td id=\"T_8056c_row82_col1\" class=\"data row82 col1\" >230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row83\" class=\"row_heading level0 row83\" >Dash</th>\n",
       "      <td id=\"T_8056c_row83_col0\" class=\"data row83 col0\" >0.16%</td>\n",
       "      <td id=\"T_8056c_row83_col1\" class=\"data row83 col1\" >229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row84\" class=\"row_heading level0 row84\" >Seaborn</th>\n",
       "      <td id=\"T_8056c_row84_col0\" class=\"data row84 col0\" >0.16%</td>\n",
       "      <td id=\"T_8056c_row84_col1\" class=\"data row84 col1\" >227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row85\" class=\"row_heading level0 row85\" >Japanese</th>\n",
       "      <td id=\"T_8056c_row85_col0\" class=\"data row85 col0\" >0.15%</td>\n",
       "      <td id=\"T_8056c_row85_col1\" class=\"data row85 col1\" >215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row86\" class=\"row_heading level0 row86\" >Chinese</th>\n",
       "      <td id=\"T_8056c_row86_col0\" class=\"data row86 col0\" >0.14%</td>\n",
       "      <td id=\"T_8056c_row86_col1\" class=\"data row86 col1\" >205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row87\" class=\"row_heading level0 row87\" >Rust</th>\n",
       "      <td id=\"T_8056c_row87_col0\" class=\"data row87 col0\" >0.13%</td>\n",
       "      <td id=\"T_8056c_row87_col1\" class=\"data row87 col1\" >194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row88\" class=\"row_heading level0 row88\" >DOMO</th>\n",
       "      <td id=\"T_8056c_row88_col0\" class=\"data row88 col0\" >0.11%</td>\n",
       "      <td id=\"T_8056c_row88_col1\" class=\"data row88 col1\" >161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row89\" class=\"row_heading level0 row89\" >ABAP</th>\n",
       "      <td id=\"T_8056c_row89_col0\" class=\"data row89 col0\" >0.11%</td>\n",
       "      <td id=\"T_8056c_row89_col1\" class=\"data row89 col1\" >161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row90\" class=\"row_heading level0 row90\" >Streamlit</th>\n",
       "      <td id=\"T_8056c_row90_col0\" class=\"data row90 col0\" >0.11%</td>\n",
       "      <td id=\"T_8056c_row90_col1\" class=\"data row90 col1\" >154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row91\" class=\"row_heading level0 row91\" >D3</th>\n",
       "      <td id=\"T_8056c_row91_col0\" class=\"data row91 col0\" >0.10%</td>\n",
       "      <td id=\"T_8056c_row91_col1\" class=\"data row91 col1\" >146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row92\" class=\"row_heading level0 row92\" >Sisense</th>\n",
       "      <td id=\"T_8056c_row92_col0\" class=\"data row92 col0\" >0.10%</td>\n",
       "      <td id=\"T_8056c_row92_col1\" class=\"data row92 col1\" >142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row93\" class=\"row_heading level0 row93\" >Navision</th>\n",
       "      <td id=\"T_8056c_row93_col0\" class=\"data row93 col0\" >0.07%</td>\n",
       "      <td id=\"T_8056c_row93_col1\" class=\"data row93 col1\" >108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row94\" class=\"row_heading level0 row94\" >Kaggle</th>\n",
       "      <td id=\"T_8056c_row94_col0\" class=\"data row94 col0\" >0.06%</td>\n",
       "      <td id=\"T_8056c_row94_col1\" class=\"data row94 col1\" >83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row95\" class=\"row_heading level0 row95\" >Italian</th>\n",
       "      <td id=\"T_8056c_row95_col0\" class=\"data row95 col0\" >0.05%</td>\n",
       "      <td id=\"T_8056c_row95_col1\" class=\"data row95 col1\" >80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row96\" class=\"row_heading level0 row96\" >Russian</th>\n",
       "      <td id=\"T_8056c_row96_col0\" class=\"data row96 col0\" >0.04%</td>\n",
       "      <td id=\"T_8056c_row96_col1\" class=\"data row96 col1\" >56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row97\" class=\"row_heading level0 row97\" >Snaplogic</th>\n",
       "      <td id=\"T_8056c_row97_col0\" class=\"data row97 col0\" >0.04%</td>\n",
       "      <td id=\"T_8056c_row97_col1\" class=\"data row97 col1\" >53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row98\" class=\"row_heading level0 row98\" >Polars</th>\n",
       "      <td id=\"T_8056c_row98_col0\" class=\"data row98 col0\" >0.01%</td>\n",
       "      <td id=\"T_8056c_row98_col1\" class=\"data row98 col1\" >18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th id=\"T_8056c_level0_row99\" class=\"row_heading level0 row99\" >Hindi</th>\n",
       "      <td id=\"T_8056c_row99_col0\" class=\"data row99 col0\" >0.01%</td>\n",
       "      <td id=\"T_8056c_row99_col1\" class=\"data row99 col1\" >14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x2096d51e5b0>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "skills_freq = Jobs['Skills'].explode().value_counts()\n",
    "\n",
    "skills_percent = skills_freq / len(Jobs) * 100\n",
    "\n",
    "top_skills = skills_percent.sort_values(ascending=False)\n",
    "\n",
    "skills_df_freq = pd.DataFrame({'Percentage': top_skills, 'Count': skills_freq[top_skills.index]})\n",
    "\n",
    "skills_df_freq.style.format({'Percentage': '{:.2f}%'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fa478c8",
   "metadata": {},
   "source": [
    "Perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fb31e28",
   "metadata": {},
   "source": [
    "# Generating the csv file\n",
    "\n",
    "The last part is generate the final file. We are going to generate a csv format file. But first of all, we need to reorganize the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "36f47a5f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "Jobs_csv = Jobs[['ID','Job','Jobs_Group','Profile','Remote','Company','Location','City','State','Salary','Frecuency_Salary','Low_Salary','High_Salary','Mean_Salary','Skills']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "24035e9f",
   "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>ID</th>\n",
       "      <th>Job</th>\n",
       "      <th>Jobs_Group</th>\n",
       "      <th>Profile</th>\n",
       "      <th>Remote</th>\n",
       "      <th>Company</th>\n",
       "      <th>Location</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Frecuency_Salary</th>\n",
       "      <th>Low_Salary</th>\n",
       "      <th>High_Salary</th>\n",
       "      <th>Mean_Salary</th>\n",
       "      <th>Skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sj_cd2707991b8bc653</td>\n",
       "      <td>Market Development Representative 16702</td>\n",
       "      <td>Data Analyst</td>\n",
       "      <td>None</td>\n",
       "      <td>Remote</td>\n",
       "      <td>Corps Team Services, LLC</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AZ</td>\n",
       "      <td>$26.17 - $28.17 por hora</td>\n",
       "      <td>hora</td>\n",
       "      <td>48152.8</td>\n",
       "      <td>51832.8</td>\n",
       "      <td>49992.8</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sj_aa0f5247ff3deca7</td>\n",
       "      <td>Vice President, Quality Services</td>\n",
       "      <td>CFO</td>\n",
       "      <td>None</td>\n",
       "      <td>Hybrid</td>\n",
       "      <td>Family Support Services</td>\n",
       "      <td>Tampa, FL+2 locations</td>\n",
       "      <td>Tampa</td>\n",
       "      <td>FL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PHP, .NET, Bachelor]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>job_bd203b586ac89e05</td>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>None</td>\n",
       "      <td>Remote</td>\n",
       "      <td>Leonardo247</td>\n",
       "      <td>Remote</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sj_8cb506d1ac43bc43</td>\n",
       "      <td>Corporate Strategy and Business Development Ma...</td>\n",
       "      <td>Business Analyst</td>\n",
       "      <td>Lead</td>\n",
       "      <td>Remote</td>\n",
       "      <td>United Therapeutics</td>\n",
       "      <td>Research Triangle Park, NC</td>\n",
       "      <td>Research Triangle Park</td>\n",
       "      <td>NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[PhD, Office, Master]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sj_bdfad91ca716ac0f</td>\n",
       "      <td>Data Analyst, Business Intelligence</td>\n",
       "      <td>Data Analyst</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Land O' Frost</td>\n",
       "      <td>Madisonville, KY 42431+2 locations</td>\n",
       "      <td>Madisonville</td>\n",
       "      <td>KY</td>\n",
       "      <td>$89,100 - $108,900 por año</td>\n",
       "      <td>año</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>108900.0</td>\n",
       "      <td>99000.0</td>\n",
       "      <td>[C#, Bachelor, VBA, HTML, SSRS, .NET, SQL]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>969</th>\n",
       "      <td>job_a88f74a87f1c6e48</td>\n",
       "      <td>Property Controller- Graduate East Lansing</td>\n",
       "      <td>Controller</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Schulte Companies</td>\n",
       "      <td>East Lansing, MI 48823+1 ubicación</td>\n",
       "      <td>East Lansing</td>\n",
       "      <td>MI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Office, Bachelor]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972</th>\n",
       "      <td>job_6fe37642cf95726f</td>\n",
       "      <td>Assistant Controller</td>\n",
       "      <td>Controller</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Home State Insurance Group, Inc.</td>\n",
       "      <td>Waco, TX 76710+1 ubicación</td>\n",
       "      <td>Waco</td>\n",
       "      <td>TX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Word, Excel, Office, Bachelor]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>980</th>\n",
       "      <td>job_094beaf0996c95fe</td>\n",
       "      <td>Plant Controller</td>\n",
       "      <td>Controller</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Dometic Corporation</td>\n",
       "      <td>Cerritos, CA 90703</td>\n",
       "      <td>Cerritos</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Master, Dynamics 365, Access, Excel, Qlik, Of...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>981</th>\n",
       "      <td>job_f3027b081096ff4c</td>\n",
       "      <td>Controller</td>\n",
       "      <td>Controller</td>\n",
       "      <td>None</td>\n",
       "      <td>Remote</td>\n",
       "      <td>The Talance Group, LP</td>\n",
       "      <td>Houston, TX 77002 (Downtown area)</td>\n",
       "      <td>Houston</td>\n",
       "      <td>TX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[CPA]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>987</th>\n",
       "      <td>job_f6fc5130f7639a6e</td>\n",
       "      <td>Plant Controller</td>\n",
       "      <td>Controller</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Johnson Controls</td>\n",
       "      <td>Marinette, WI+5 ubicaciones</td>\n",
       "      <td>Marinette</td>\n",
       "      <td>WI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[MBA, Word, SAP, Excel, ERP, PowerPoint, Bache...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>146392 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       ID                                                Job  \\\n",
       "0     sj_cd2707991b8bc653            Market Development Representative 16702   \n",
       "1     sj_aa0f5247ff3deca7                   Vice President, Quality Services   \n",
       "2    job_bd203b586ac89e05                                   Business Analyst   \n",
       "3     sj_8cb506d1ac43bc43  Corporate Strategy and Business Development Ma...   \n",
       "4     sj_bdfad91ca716ac0f                Data Analyst, Business Intelligence   \n",
       "..                    ...                                                ...   \n",
       "969  job_a88f74a87f1c6e48         Property Controller- Graduate East Lansing   \n",
       "972  job_6fe37642cf95726f                               Assistant Controller   \n",
       "980  job_094beaf0996c95fe                                   Plant Controller   \n",
       "981  job_f3027b081096ff4c                                         Controller   \n",
       "987  job_f6fc5130f7639a6e                                   Plant Controller   \n",
       "\n",
       "           Jobs_Group Profile  Remote                           Company  \\\n",
       "0        Data Analyst    None  Remote          Corps Team Services, LLC   \n",
       "1                 CFO    None  Hybrid           Family Support Services   \n",
       "2    Business Analyst    None  Remote                       Leonardo247   \n",
       "3    Business Analyst    Lead  Remote               United Therapeutics   \n",
       "4        Data Analyst    None    None                     Land O' Frost   \n",
       "..                ...     ...     ...                               ...   \n",
       "969        Controller    None    None                 Schulte Companies   \n",
       "972        Controller    None    None  Home State Insurance Group, Inc.   \n",
       "980        Controller    None    None               Dometic Corporation   \n",
       "981        Controller    None  Remote             The Talance Group, LP   \n",
       "987        Controller    None    None                  Johnson Controls   \n",
       "\n",
       "                               Location                    City State  \\\n",
       "0                               Arizona                     NaN    AZ   \n",
       "1                 Tampa, FL+2 locations                   Tampa    FL   \n",
       "2                                Remote                     NaN   NaN   \n",
       "3            Research Triangle Park, NC  Research Triangle Park    NC   \n",
       "4    Madisonville, KY 42431+2 locations            Madisonville    KY   \n",
       "..                                  ...                     ...   ...   \n",
       "969  East Lansing, MI 48823+1 ubicación            East Lansing    MI   \n",
       "972          Waco, TX 76710+1 ubicación                    Waco    TX   \n",
       "980                  Cerritos, CA 90703                Cerritos    CA   \n",
       "981   Houston, TX 77002 (Downtown area)                 Houston    TX   \n",
       "987         Marinette, WI+5 ubicaciones               Marinette    WI   \n",
       "\n",
       "                         Salary Frecuency_Salary  Low_Salary  High_Salary  \\\n",
       "0      $26.17 - $28.17 por hora             hora     48152.8      51832.8   \n",
       "1                           NaN              NaN         NaN          NaN   \n",
       "2                           NaN              NaN         NaN          NaN   \n",
       "3                           NaN              NaN         NaN          NaN   \n",
       "4    $89,100 - $108,900 por año              año     89100.0     108900.0   \n",
       "..                          ...              ...         ...          ...   \n",
       "969                         NaN              NaN         NaN          NaN   \n",
       "972                         NaN              NaN         NaN          NaN   \n",
       "980                         NaN              NaN         NaN          NaN   \n",
       "981                         NaN              NaN         NaN          NaN   \n",
       "987                         NaN              NaN         NaN          NaN   \n",
       "\n",
       "     Mean_Salary                                             Skills  \n",
       "0        49992.8                                                 []  \n",
       "1            NaN                              [PHP, .NET, Bachelor]  \n",
       "2            NaN                                                 []  \n",
       "3            NaN                              [PhD, Office, Master]  \n",
       "4        99000.0         [C#, Bachelor, VBA, HTML, SSRS, .NET, SQL]  \n",
       "..           ...                                                ...  \n",
       "969          NaN                                 [Office, Bachelor]  \n",
       "972          NaN                    [Word, Excel, Office, Bachelor]  \n",
       "980          NaN  [Master, Dynamics 365, Access, Excel, Qlik, Of...  \n",
       "981          NaN                                              [CPA]  \n",
       "987          NaN  [MBA, Word, SAP, Excel, ERP, PowerPoint, Bache...  \n",
       "\n",
       "[146392 rows x 15 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Jobs_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "ad65cd64",
   "metadata": {},
   "outputs": [],
   "source": [
    "Jobs_csv.to_csv('jobs.csv', index=False, encoding=\"utf-16\")\n",
    "Jobs_csv.to_excel('jobs.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f2d2df4",
   "metadata": {},
   "source": [
    "### THANK YOU!!"
   ]
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}