(I)_general_preprocessing_and_translation.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyN9SdJEdIBl/DWdyKXNuwbX",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    },
    "widgets": {
      "application/vnd.jupyter.widget-state+json": {
        "4f5f67ab39db449ab1deb04420507982": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "ButtonModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "ButtonModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "ButtonView",
            "button_style": "",
            "description": "Download Excel",
            "disabled": false,
            "icon": "",
            "layout": "IPY_MODEL_8591772443614ad89d5825b70074fd00",
            "style": "IPY_MODEL_c5fe4968ec794bd38294910bb6d3194b",
            "tooltip": ""
          }
        },
        "8591772443614ad89d5825b70074fd00": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": null
          }
        },
        "c5fe4968ec794bd38294910bb6d3194b": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "ButtonStyleModel",
          "model_module_version": "1.5.0",
          "state": {
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "ButtonStyleModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "StyleView",
            "button_color": null,
            "font_weight": ""
          }
        }
      }
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/schmcklr/skill_extractor/blob/main/(I)_general_preprocessing_and_translation.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Part I:** General Preprocessing and Translation\n",
        "\n",
        "The following program is designed to filter out duplicate job postings, translate non-English job postings into English, and perform initial general preprocessing. The results will be outputted in an Excel file, which can be used for further analysis.\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "UmqKGadCs1bK"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 1. Load raw data\n",
        "*   Import of job advertisements\n"
      ],
      "metadata": {
        "id": "bo9dPUkx7szX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "# Fetching raw data\n",
        "workbook = 'https://github.com/schmcklr/skill_extractor/blob/main/job_data/job_advertisements.xlsx?raw=true'\n",
        "\n",
        "# Import of tabs\n",
        "job_data = pd.read_excel(workbook, sheet_name=\"data\")"
      ],
      "metadata": {
        "id": "Q_x_Pq4B4kz6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 2. General Preprocessing\n",
        "\n",
        "*   Convert to lower case\n",
        "*   Convert dates to datetime\n",
        "*   Elimination of duplicates\n"
      ],
      "metadata": {
        "id": "BdtP2AgC0Trm"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nsoMnCSUzfqA"
      },
      "outputs": [],
      "source": [
        "# Initial preprocessing of job advertisements\n",
        "import pandas as pd\n",
        "from nltk.corpus import stopwords\n",
        "\n",
        "# Convert text to lower case\n",
        "job_data = job_data.apply(lambda x: x.astype(str).str.lower())\n",
        "\n",
        "# Convert 'created_at' to datetime\n",
        "job_data['created_at'] = pd.to_datetime(job_data['created_at'])\n",
        "\n",
        "# Extract the year from the 'created_at'\n",
        "job_data['year'] = job_data['created_at'].dt.year\n",
        "\n",
        "# Elimination of duplicates\n",
        "job_data = job_data[~job_data.duplicated(subset=['title', 'year'], keep='first')]\n",
        "job_data = job_data[~job_data.duplicated(subset=['description', 'year'], keep='first')]"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "print(len(job_data['created_at']))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bZooe0sZeZjo",
        "outputId": "3ea5af6e-4580-4a27-8f2d-fa933f11b85c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "3062\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#3. Translation"
      ],
      "metadata": {
        "id": "3SbUgFvF6NFc"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "3.1 Function for data translation"
      ],
      "metadata": {
        "id": "zLAqtBtMz7tQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#!pip install --upgrade googletrans --quiet\n",
        "!pip install --upgrade translatepy --quiet\n",
        "!pip install langdetect --quiet\n",
        "from bs4 import BeautifulSoup\n",
        "from translatepy import Translator\n",
        "from langdetect import detect\n",
        "import langdetect\n",
        "\n",
        "# Function for language detection\n",
        "def detect_language(text):\n",
        "    try:\n",
        "        return detect(text)\n",
        "    except langdetect.lang_detect_exception.LangDetectException:\n",
        "        return 'unknown'\n",
        "\n",
        "# Initialization of global variables\n",
        "translated_job_ads = 0\n",
        "all_job_adds = 0\n",
        "\n",
        "# Initialization of translator\n",
        "translator = Translator()\n",
        "\n",
        "# Function for translation of job description\n",
        "def translate_job_description(text, count, html):\n",
        "    # Global keyword to access variables global\n",
        "    global all_job_adds\n",
        "    global translated_job_des\n",
        "\n",
        "    if count == 'y':\n",
        "        all_job_adds += 1\n",
        "\n",
        "    if html:\n",
        "      # Erstellen eines BeautifulSoup-Objekts\n",
        "      soup = BeautifulSoup(text, 'html.parser')\n",
        "      # Entfernen von HTML-Tags und Ersetzen durch Leerzeichen\n",
        "      cleaned_text = soup.get_text(separator=' ')\n",
        "    else:\n",
        "      cleaned_text = text\n",
        "\n",
        "    # Translate if text not in English\n",
        "    if detect_language(cleaned_text) != 'en':\n",
        "        # Global keyword to access global variables\n",
        "        global translated_job_ads\n",
        "        # Variable to count number of translated job ads\n",
        "        if count == 'y':\n",
        "            translated_job_ads += 1\n",
        "        try:\n",
        "          translation = translator.translate(text, \"English\")\n",
        "          translated = translation.result\n",
        "        except Exception as e:\n",
        "          translated = text\n",
        "\n",
        "        # Store translated job description (for development purposes only)\n",
        "        if count == 'n':\n",
        "          translated_job_des.append([text, translated])\n",
        "    else:\n",
        "        translated = text\n",
        "    return translated"
      ],
      "metadata": {
        "id": "HUcTVvuo3m7L"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "3.2 Function to translate the content within HTML tags when there are no spaces between the tags and the enclosed text (disabled by default, because of higher runtime)"
      ],
      "metadata": {
        "id": "5z0vUNKVyDPz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from bs4 import BeautifulSoup\n",
        "import pandas as pd\n",
        "\n",
        "def process_html(html_text):\n",
        "    soup = BeautifulSoup(html_text, 'html.parser')\n",
        "\n",
        "    # Iterate over the relevant tags and apply the function to the element string\n",
        "    for element in soup.find_all():\n",
        "        if element.string is not None:\n",
        "            modified_text = translate_job_description(element.string, 'n')\n",
        "            element.string.replace_with(modified_text)\n",
        "\n",
        "    # Format the HTML txext to improve readability and structure\n",
        "    formatted_html_text = soup.prettify()\n",
        "    return formatted_html_text"
      ],
      "metadata": {
        "id": "YVJNNLKUniQP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "3.3 Translation of job advertisements"
      ],
      "metadata": {
        "id": "VwoLYXSo0c2g"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Initialize list to store translated job descriptions\n",
        "translated_job_des = []\n",
        "\n",
        "# Translation of job ads (columns: title, description, describtion with html tags)\n",
        "job_data['title'] = job_data['title'].apply(lambda x: translate_job_description(x, 'n', False))\n",
        "job_data['description_translated'] = job_data['description'].apply(lambda x: translate_job_description(x, 'y', True))\n",
        "\n",
        "# Needed to also translate the headlines and tags without spaces (disabled by default)\n",
        "#job_data['rawDescriptionTranslatedWithTags'] = job_data['rawDescriptionTranslated'].apply(lambda x: process_html(x))\n",
        "\n",
        "# User info (number of ads that have been translated )\n",
        "print('Translation successful! ' + str(translated_job_ads) + '/' + str(all_job_adds) + ' job advertisements were translated.')\n",
        "\n",
        "# create dataframe that includes text before and after translation (for development purposes only)\n",
        "#translated_descriptions = pd.DataFrame(translated_job_des, columns=['Original Text', x'Translated Text'])\n",
        "#translated_descriptions.to_excel('translated_job_adx.xlsx', index=False)\n",
        "\n",
        "# Export translated text (for development purposes only)\n",
        "#translated_descriptions.to_excel('translated_job_adx.xlsx', index=False)"
      ],
      "metadata": {
        "id": "PkgtBFtM0ZVl",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "28f9358d-6e45-4c39-9d42-2d54d5d7f5a1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Translation successful! 382/3062 job advertisements were translated.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 4. Export\n",
        "\n",
        "*   Export dataframe to excel"
      ],
      "metadata": {
        "id": "g9dS2lUf5t0T"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Export dataframe to excel\n",
        "job_data.to_excel('job_data_general_preprocessed_and_translated.xlsx', index=False)"
      ],
      "metadata": {
        "id": "1qwXDcfoCAvZ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "* Download excel"
      ],
      "metadata": {
        "id": "fo8AmicGx8sj"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import files\n",
        "import ipywidgets as widgets\n",
        "from IPython.display import display\n",
        "\n",
        "# Create the path for the saved model file\n",
        "path = 'job_data_general_preprocessed_and_translated.xlsx'\n",
        "\n",
        "# Create a button for downloading the model\n",
        "download_button = widgets.Button(description=\"Download Excel\")\n",
        "\n",
        "# Link the download function to the button click event\n",
        "def on_download_button_click(b):\n",
        "    # Download the model file\n",
        "    files.download(path)\n",
        "\n",
        "download_button.on_click(on_download_button_click)\n",
        "\n",
        "# Display the download button\n",
        "display(download_button)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 49,
          "referenced_widgets": [
            "4f5f67ab39db449ab1deb04420507982",
            "8591772443614ad89d5825b70074fd00",
            "c5fe4968ec794bd38294910bb6d3194b"
          ]
        },
        "id": "TVPOia7nx765",
        "outputId": "453cec4d-9e82-4e4c-c189-9ee6430bb752"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "Button(description='Download Excel', style=ButtonStyle())"
            ],
            "application/vnd.jupyter.widget-view+json": {
              "version_major": 2,
              "version_minor": 0,
              "model_id": "4f5f67ab39db449ab1deb04420507982"
            }
          },
          "metadata": {}
        }
      ]
    }
  ]
}