Career_Library_Rework.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyPaGN1ucQUCSMXTHmrLMXqw"
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gcbxlPx_CYLf"
      },
      "outputs": [],
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "import csv , json ,os,re ,openpyxl\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df=pd.read_csv('/content/discrepant_career_data_test_v2 - Sheet1.csv')"
      ],
      "metadata": {
        "id": "hEl8B556DP6T"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 724
        },
        "id": "MhcJTRofG7Re",
        "outputId": "42e0c903-5539-4494-8b2a-e537e49853b7"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                 Career Name  \\\n",
              "0      Supervisor and Foreman, Well Drilling   \n",
              "1                Filter Press Operator (Oil)   \n",
              "2                  Fruit Ripening Technician   \n",
              "3  Fruits and Vegetables Selection In-Charge   \n",
              "4                          Greenhouse Fitter   \n",
              "\n",
              "                          Career Cluster New Name Prioritization  \\\n",
              "0  Agriculture, Food & Natural Resources        -              -   \n",
              "1  Agriculture, Food & Natural Resources        -              -   \n",
              "2  Agriculture, Food & Natural Resources        -              -   \n",
              "3  Agriculture, Food & Natural Resources        -              -   \n",
              "4  Agriculture, Food & Natural Resources        -              -   \n",
              "\n",
              "  YT link 1 (Industry) YT link 2 (Career) SEO Status Review Assigned to  \\\n",
              "0                    -                  -          -                  -   \n",
              "1                    -                  -        NaN                NaN   \n",
              "2                    -                  -        NaN                NaN   \n",
              "3                    -                  -        NaN                NaN   \n",
              "4                    -                  -        NaN                NaN   \n",
              "\n",
              "  Review Status                                      Also Known As  ...  \\\n",
              "0             -  Drilling Supervisor \\nWell Drilling Foreman \\n...  ...   \n",
              "1           NaN  Filter Press Operator (Oil) \\nOil Filter Opera...  ...   \n",
              "2           NaN  Fruit Ripening Technician \\nFruit Processing T...  ...   \n",
              "3           NaN  Fruits and Vegetables Selection In-Charge \\nPr...  ...   \n",
              "4           NaN  Greenhouse Fitter \\nGreenhouse Installer \\nGre...  ...   \n",
              "\n",
              "                                   Expected Earnings  \\\n",
              "0  Entry Level: INR 500,000 - 800,000 /year\\nMid ...   \n",
              "1  Entry Level: INR 180,000 - 300,000 /year\\nMid ...   \n",
              "2  Entry Level: 250000 - 400000 /year\\nMid Level:...   \n",
              "3  Entry Level: INR 300,000 - 500,000 /year\\nMid ...   \n",
              "4  Entry Level: INR 250,000 - 400,000 /year\\nMid ...   \n",
              "\n",
              "                                        Top Colleges  \\\n",
              "0                                                NaN   \n",
              "1                                                 \\n   \n",
              "2  Indian Agricultural Research Institute, New De...   \n",
              "3  Indian Agricultural Research Institute, New De...   \n",
              "4  Indian Agricultural Research Institute, New De...   \n",
              "\n",
              "                                           Top Exams  \\\n",
              "0  IWCF Well Control certification\\nHSE Certifica...   \n",
              "1                                                NaN   \n",
              "2  All India Entrance Examination for Admission (...   \n",
              "3  Indian Council of Agricultural Research All In...   \n",
              "4  Indian Council of Agricultural Research (ICAR)...   \n",
              "\n",
              "                                        Work Context  \\\n",
              "0  Environment: Drilling sites, both onshore and ...   \n",
              "1  Environment: Factory setting with oil filtrati...   \n",
              "2  Environment: Fruit ripening facilities and sto...   \n",
              "3  Environment: Office setting with occasional vi...   \n",
              "4  Environment: Onsite greenhouse construction an...   \n",
              "\n",
              "                                           Strengths  \\\n",
              "0  Lucrative earning potential\\nOpportunities for...   \n",
              "1  Handson technical role\\nOpportunities for skil...   \n",
              "2  Direct contribution to food supply chain\\nHand...   \n",
              "3  Opportunities for industry advancement\\nVaried...   \n",
              "4  Handson technical work\\nContribution to sustai...   \n",
              "\n",
              "                                          Weaknesses  \\\n",
              "0  Physically demanding work\\nExposure to hazardo...   \n",
              "1  Exposure to industrial machinery hazards\\nPhys...   \n",
              "2  Potential for irregular hours during peak ripe...   \n",
              "3  Potential for seasonal work demands\\nContinuou...   \n",
              "4  Physical demands of construction and maintenan...   \n",
              "\n",
              "                             Career Progression Path  \\\n",
              "0  Assistant Drilling Supervisor\\nDrilling Superv...   \n",
              "1  Junior Filter Press Operator\\nFilter Press Ope...   \n",
              "2  Assistant Fruit Ripening Technician, Fruit Rip...   \n",
              "3  Assistant Selection InCharge\\nSelection InChar...   \n",
              "4  Junior Greenhouse Fitter\\nGreenhouse Fitter\\nS...   \n",
              "\n",
              "                                Famous Personalities  \\\n",
              "0                                                NaN   \n",
              "1                                                NaN   \n",
              "2                                                NaN   \n",
              "3  Dr. Ashok Kumar Singh\\nDr. R.K. Keshwa\\nDr. In...   \n",
              "4  Dr. M.S. Swaminathan\\nDr. S.K. Malhotra\\nDr. K...   \n",
              "\n",
              "                                       Top Companies Data Source  \n",
              "0  Schlumberger\\nHalliburton\\nBaker Hughes\\nWeath...         NaN  \n",
              "1                                                NaN         NaN  \n",
              "2  National Horticulture Board (NHB)\\nMother Dair...         NaN  \n",
              "3  Farmgate AgroAllied Limited\\nSafal National Ex...         NaN  \n",
              "4  GreenAgro Tech Solutions\\nAgriBuild Structures...         NaN  \n",
              "\n",
              "[5 rows x 27 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b59f7624-0a03-417c-b3eb-826e235b6c4f\" class=\"colab-df-container\">\n",
              "    <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>Career Name</th>\n",
              "      <th>Career Cluster</th>\n",
              "      <th>New Name</th>\n",
              "      <th>Prioritization</th>\n",
              "      <th>YT link 1 (Industry)</th>\n",
              "      <th>YT link 2 (Career)</th>\n",
              "      <th>SEO Status</th>\n",
              "      <th>Review Assigned to</th>\n",
              "      <th>Review Status</th>\n",
              "      <th>Also Known As</th>\n",
              "      <th>...</th>\n",
              "      <th>Expected Earnings</th>\n",
              "      <th>Top Colleges</th>\n",
              "      <th>Top Exams</th>\n",
              "      <th>Work Context</th>\n",
              "      <th>Strengths</th>\n",
              "      <th>Weaknesses</th>\n",
              "      <th>Career Progression Path</th>\n",
              "      <th>Famous Personalities</th>\n",
              "      <th>Top Companies</th>\n",
              "      <th>Data Source</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Supervisor and Foreman, Well Drilling</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>Drilling Supervisor \\nWell Drilling Foreman \\n...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: INR 500,000 - 800,000 /year\\nMid ...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>IWCF Well Control certification\\nHSE Certifica...</td>\n",
              "      <td>Environment: Drilling sites, both onshore and ...</td>\n",
              "      <td>Lucrative earning potential\\nOpportunities for...</td>\n",
              "      <td>Physically demanding work\\nExposure to hazardo...</td>\n",
              "      <td>Assistant Drilling Supervisor\\nDrilling Superv...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Schlumberger\\nHalliburton\\nBaker Hughes\\nWeath...</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Filter Press Operator (Oil)</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Filter Press Operator (Oil) \\nOil Filter Opera...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: INR 180,000 - 300,000 /year\\nMid ...</td>\n",
              "      <td>\\n</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Environment: Factory setting with oil filtrati...</td>\n",
              "      <td>Handson technical role\\nOpportunities for skil...</td>\n",
              "      <td>Exposure to industrial machinery hazards\\nPhys...</td>\n",
              "      <td>Junior Filter Press Operator\\nFilter Press Ope...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Fruit Ripening Technician</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Fruit Ripening Technician \\nFruit Processing T...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: 250000 - 400000 /year\\nMid Level:...</td>\n",
              "      <td>Indian Agricultural Research Institute, New De...</td>\n",
              "      <td>All India Entrance Examination for Admission (...</td>\n",
              "      <td>Environment: Fruit ripening facilities and sto...</td>\n",
              "      <td>Direct contribution to food supply chain\\nHand...</td>\n",
              "      <td>Potential for irregular hours during peak ripe...</td>\n",
              "      <td>Assistant Fruit Ripening Technician, Fruit Rip...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>National Horticulture Board (NHB)\\nMother Dair...</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Fruits and Vegetables Selection In-Charge</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Fruits and Vegetables Selection In-Charge \\nPr...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: INR 300,000 - 500,000 /year\\nMid ...</td>\n",
              "      <td>Indian Agricultural Research Institute, New De...</td>\n",
              "      <td>Indian Council of Agricultural Research All In...</td>\n",
              "      <td>Environment: Office setting with occasional vi...</td>\n",
              "      <td>Opportunities for industry advancement\\nVaried...</td>\n",
              "      <td>Potential for seasonal work demands\\nContinuou...</td>\n",
              "      <td>Assistant Selection InCharge\\nSelection InChar...</td>\n",
              "      <td>Dr. Ashok Kumar Singh\\nDr. R.K. Keshwa\\nDr. In...</td>\n",
              "      <td>Farmgate AgroAllied Limited\\nSafal National Ex...</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Greenhouse Fitter</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Greenhouse Fitter \\nGreenhouse Installer \\nGre...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: INR 250,000 - 400,000 /year\\nMid ...</td>\n",
              "      <td>Indian Agricultural Research Institute, New De...</td>\n",
              "      <td>Indian Council of Agricultural Research (ICAR)...</td>\n",
              "      <td>Environment: Onsite greenhouse construction an...</td>\n",
              "      <td>Handson technical work\\nContribution to sustai...</td>\n",
              "      <td>Physical demands of construction and maintenan...</td>\n",
              "      <td>Junior Greenhouse Fitter\\nGreenhouse Fitter\\nS...</td>\n",
              "      <td>Dr. M.S. Swaminathan\\nDr. S.K. Malhotra\\nDr. K...</td>\n",
              "      <td>GreenAgro Tech Solutions\\nAgriBuild Structures...</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 27 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b59f7624-0a03-417c-b3eb-826e235b6c4f')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-b59f7624-0a03-417c-b3eb-826e235b6c4f button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-b59f7624-0a03-417c-b3eb-826e235b6c4f');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-7a834afc-1243-4bbe-b12f-ef196d3cf70f\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-7a834afc-1243-4bbe-b12f-ef196d3cf70f')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-7a834afc-1243-4bbe-b12f-ef196d3cf70f button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "df"
            }
          },
          "metadata": {},
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# prompt: Make all empty cell , NaN  as  hifens\n",
        "\n",
        "# Replace empty cells and NaN values with hyphens\n",
        "df.fillna('-', inplace=True)\n",
        "\n",
        "df.head()"
      ],
      "metadata": {
        "id": "cXfe2DYmH_H8",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 758
        },
        "outputId": "29ff1c0a-6cd3-4b37-a242-7014d6457ea9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "<ipython-input-4-ca24b09a07f3>:4: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '-' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
            "  df.fillna('-', inplace=True)\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                 Career Name  \\\n",
              "0      Supervisor and Foreman, Well Drilling   \n",
              "1                Filter Press Operator (Oil)   \n",
              "2                  Fruit Ripening Technician   \n",
              "3  Fruits and Vegetables Selection In-Charge   \n",
              "4                          Greenhouse Fitter   \n",
              "\n",
              "                          Career Cluster New Name Prioritization  \\\n",
              "0  Agriculture, Food & Natural Resources        -              -   \n",
              "1  Agriculture, Food & Natural Resources        -              -   \n",
              "2  Agriculture, Food & Natural Resources        -              -   \n",
              "3  Agriculture, Food & Natural Resources        -              -   \n",
              "4  Agriculture, Food & Natural Resources        -              -   \n",
              "\n",
              "  YT link 1 (Industry) YT link 2 (Career) SEO Status Review Assigned to  \\\n",
              "0                    -                  -          -                  -   \n",
              "1                    -                  -          -                  -   \n",
              "2                    -                  -          -                  -   \n",
              "3                    -                  -          -                  -   \n",
              "4                    -                  -          -                  -   \n",
              "\n",
              "  Review Status                                      Also Known As  ...  \\\n",
              "0             -  Drilling Supervisor \\nWell Drilling Foreman \\n...  ...   \n",
              "1             -  Filter Press Operator (Oil) \\nOil Filter Opera...  ...   \n",
              "2             -  Fruit Ripening Technician \\nFruit Processing T...  ...   \n",
              "3             -  Fruits and Vegetables Selection In-Charge \\nPr...  ...   \n",
              "4             -  Greenhouse Fitter \\nGreenhouse Installer \\nGre...  ...   \n",
              "\n",
              "                                   Expected Earnings  \\\n",
              "0  Entry Level: INR 500,000 - 800,000 /year\\nMid ...   \n",
              "1  Entry Level: INR 180,000 - 300,000 /year\\nMid ...   \n",
              "2  Entry Level: 250000 - 400000 /year\\nMid Level:...   \n",
              "3  Entry Level: INR 300,000 - 500,000 /year\\nMid ...   \n",
              "4  Entry Level: INR 250,000 - 400,000 /year\\nMid ...   \n",
              "\n",
              "                                        Top Colleges  \\\n",
              "0                                                  -   \n",
              "1                                                 \\n   \n",
              "2  Indian Agricultural Research Institute, New De...   \n",
              "3  Indian Agricultural Research Institute, New De...   \n",
              "4  Indian Agricultural Research Institute, New De...   \n",
              "\n",
              "                                           Top Exams  \\\n",
              "0  IWCF Well Control certification\\nHSE Certifica...   \n",
              "1                                                  -   \n",
              "2  All India Entrance Examination for Admission (...   \n",
              "3  Indian Council of Agricultural Research All In...   \n",
              "4  Indian Council of Agricultural Research (ICAR)...   \n",
              "\n",
              "                                        Work Context  \\\n",
              "0  Environment: Drilling sites, both onshore and ...   \n",
              "1  Environment: Factory setting with oil filtrati...   \n",
              "2  Environment: Fruit ripening facilities and sto...   \n",
              "3  Environment: Office setting with occasional vi...   \n",
              "4  Environment: Onsite greenhouse construction an...   \n",
              "\n",
              "                                           Strengths  \\\n",
              "0  Lucrative earning potential\\nOpportunities for...   \n",
              "1  Handson technical role\\nOpportunities for skil...   \n",
              "2  Direct contribution to food supply chain\\nHand...   \n",
              "3  Opportunities for industry advancement\\nVaried...   \n",
              "4  Handson technical work\\nContribution to sustai...   \n",
              "\n",
              "                                          Weaknesses  \\\n",
              "0  Physically demanding work\\nExposure to hazardo...   \n",
              "1  Exposure to industrial machinery hazards\\nPhys...   \n",
              "2  Potential for irregular hours during peak ripe...   \n",
              "3  Potential for seasonal work demands\\nContinuou...   \n",
              "4  Physical demands of construction and maintenan...   \n",
              "\n",
              "                             Career Progression Path  \\\n",
              "0  Assistant Drilling Supervisor\\nDrilling Superv...   \n",
              "1  Junior Filter Press Operator\\nFilter Press Ope...   \n",
              "2  Assistant Fruit Ripening Technician, Fruit Rip...   \n",
              "3  Assistant Selection InCharge\\nSelection InChar...   \n",
              "4  Junior Greenhouse Fitter\\nGreenhouse Fitter\\nS...   \n",
              "\n",
              "                                Famous Personalities  \\\n",
              "0                                                  -   \n",
              "1                                                  -   \n",
              "2                                                  -   \n",
              "3  Dr. Ashok Kumar Singh\\nDr. R.K. Keshwa\\nDr. In...   \n",
              "4  Dr. M.S. Swaminathan\\nDr. S.K. Malhotra\\nDr. K...   \n",
              "\n",
              "                                       Top Companies Data Source  \n",
              "0  Schlumberger\\nHalliburton\\nBaker Hughes\\nWeath...           -  \n",
              "1                                                  -           -  \n",
              "2  National Horticulture Board (NHB)\\nMother Dair...           -  \n",
              "3  Farmgate AgroAllied Limited\\nSafal National Ex...           -  \n",
              "4  GreenAgro Tech Solutions\\nAgriBuild Structures...           -  \n",
              "\n",
              "[5 rows x 27 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-a93cc0a6-9f8c-4788-afb9-99d256f3f73a\" class=\"colab-df-container\">\n",
              "    <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>Career Name</th>\n",
              "      <th>Career Cluster</th>\n",
              "      <th>New Name</th>\n",
              "      <th>Prioritization</th>\n",
              "      <th>YT link 1 (Industry)</th>\n",
              "      <th>YT link 2 (Career)</th>\n",
              "      <th>SEO Status</th>\n",
              "      <th>Review Assigned to</th>\n",
              "      <th>Review Status</th>\n",
              "      <th>Also Known As</th>\n",
              "      <th>...</th>\n",
              "      <th>Expected Earnings</th>\n",
              "      <th>Top Colleges</th>\n",
              "      <th>Top Exams</th>\n",
              "      <th>Work Context</th>\n",
              "      <th>Strengths</th>\n",
              "      <th>Weaknesses</th>\n",
              "      <th>Career Progression Path</th>\n",
              "      <th>Famous Personalities</th>\n",
              "      <th>Top Companies</th>\n",
              "      <th>Data Source</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Supervisor and Foreman, Well Drilling</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>Drilling Supervisor \\nWell Drilling Foreman \\n...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: INR 500,000 - 800,000 /year\\nMid ...</td>\n",
              "      <td>-</td>\n",
              "      <td>IWCF Well Control certification\\nHSE Certifica...</td>\n",
              "      <td>Environment: Drilling sites, both onshore and ...</td>\n",
              "      <td>Lucrative earning potential\\nOpportunities for...</td>\n",
              "      <td>Physically demanding work\\nExposure to hazardo...</td>\n",
              "      <td>Assistant Drilling Supervisor\\nDrilling Superv...</td>\n",
              "      <td>-</td>\n",
              "      <td>Schlumberger\\nHalliburton\\nBaker Hughes\\nWeath...</td>\n",
              "      <td>-</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Filter Press Operator (Oil)</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>Filter Press Operator (Oil) \\nOil Filter Opera...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: INR 180,000 - 300,000 /year\\nMid ...</td>\n",
              "      <td>\\n</td>\n",
              "      <td>-</td>\n",
              "      <td>Environment: Factory setting with oil filtrati...</td>\n",
              "      <td>Handson technical role\\nOpportunities for skil...</td>\n",
              "      <td>Exposure to industrial machinery hazards\\nPhys...</td>\n",
              "      <td>Junior Filter Press Operator\\nFilter Press Ope...</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Fruit Ripening Technician</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>Fruit Ripening Technician \\nFruit Processing T...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: 250000 - 400000 /year\\nMid Level:...</td>\n",
              "      <td>Indian Agricultural Research Institute, New De...</td>\n",
              "      <td>All India Entrance Examination for Admission (...</td>\n",
              "      <td>Environment: Fruit ripening facilities and sto...</td>\n",
              "      <td>Direct contribution to food supply chain\\nHand...</td>\n",
              "      <td>Potential for irregular hours during peak ripe...</td>\n",
              "      <td>Assistant Fruit Ripening Technician, Fruit Rip...</td>\n",
              "      <td>-</td>\n",
              "      <td>National Horticulture Board (NHB)\\nMother Dair...</td>\n",
              "      <td>-</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Fruits and Vegetables Selection In-Charge</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>Fruits and Vegetables Selection In-Charge \\nPr...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: INR 300,000 - 500,000 /year\\nMid ...</td>\n",
              "      <td>Indian Agricultural Research Institute, New De...</td>\n",
              "      <td>Indian Council of Agricultural Research All In...</td>\n",
              "      <td>Environment: Office setting with occasional vi...</td>\n",
              "      <td>Opportunities for industry advancement\\nVaried...</td>\n",
              "      <td>Potential for seasonal work demands\\nContinuou...</td>\n",
              "      <td>Assistant Selection InCharge\\nSelection InChar...</td>\n",
              "      <td>Dr. Ashok Kumar Singh\\nDr. R.K. Keshwa\\nDr. In...</td>\n",
              "      <td>Farmgate AgroAllied Limited\\nSafal National Ex...</td>\n",
              "      <td>-</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Greenhouse Fitter</td>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>Greenhouse Fitter \\nGreenhouse Installer \\nGre...</td>\n",
              "      <td>...</td>\n",
              "      <td>Entry Level: INR 250,000 - 400,000 /year\\nMid ...</td>\n",
              "      <td>Indian Agricultural Research Institute, New De...</td>\n",
              "      <td>Indian Council of Agricultural Research (ICAR)...</td>\n",
              "      <td>Environment: Onsite greenhouse construction an...</td>\n",
              "      <td>Handson technical work\\nContribution to sustai...</td>\n",
              "      <td>Physical demands of construction and maintenan...</td>\n",
              "      <td>Junior Greenhouse Fitter\\nGreenhouse Fitter\\nS...</td>\n",
              "      <td>Dr. M.S. Swaminathan\\nDr. S.K. Malhotra\\nDr. K...</td>\n",
              "      <td>GreenAgro Tech Solutions\\nAgriBuild Structures...</td>\n",
              "      <td>-</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 27 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a93cc0a6-9f8c-4788-afb9-99d256f3f73a')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-a93cc0a6-9f8c-4788-afb9-99d256f3f73a button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-a93cc0a6-9f8c-4788-afb9-99d256f3f73a');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-20498d90-964f-42ea-83e5-09c69dfa06da\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-20498d90-964f-42ea-83e5-09c69dfa06da')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-20498d90-964f-42ea-83e5-09c69dfa06da button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "df"
            }
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.to_csv('output_Career_Formatted.csv', index=False)"
      ],
      "metadata": {
        "id": "OCwlKnHJldi0"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df=pd.read_excel('/content/Processed_3668_ncs_25nov_up1_n.xlsx')"
      ],
      "metadata": {
        "id": "ecdUkKQaHqiN"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 810
        },
        "id": "G2Gv4FrSIHVu",
        "outputId": "0d689af6-5b7b-4f7a-a8be-f1994650cb44"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                          Career Cluster Career Path  \\\n",
              "0  Agriculture, Food & Natural Resources           -   \n",
              "1  Agriculture, Food & Natural Resources           -   \n",
              "2  Agriculture, Food & Natural Resources           -   \n",
              "3  Agriculture, Food & Natural Resources           -   \n",
              "4  Agriculture, Food & Natural Resources           -   \n",
              "\n",
              "                       Career Name New Name Prioritization  \\\n",
              "0       Civil Engineer, Irrigation        -              -   \n",
              "1  General Manager, Livestock Farm        -              -   \n",
              "2               Sugar Technologist        -              -   \n",
              "3                Boiling In-Charge        -              -   \n",
              "4            Veterinarian, General        -              -   \n",
              "\n",
              "  YT link 1 (Industry) YT link 2 (Career) SEO Status Review Assigned to  \\\n",
              "0                    -                  -          -                  -   \n",
              "1                    -                  -          -                  -   \n",
              "2                    -                  -          -                  -   \n",
              "3                    -                  -          -                  -   \n",
              "4                    -                  -          -                  -   \n",
              "\n",
              "  Review Status  ...                                  Expected Earnings  \\\n",
              "0             -  ...  Entry Level : 300000 - 600000  /year \\nMid Lev...   \n",
              "1             -  ...  Entry Level : 300000 - 500000  /year \\nMid Lev...   \n",
              "2             -  ...  Entry Level : 200000 - 400000  /year \\nMid Lev...   \n",
              "3             -  ...  Entry Level : 200000 - 300000  /year \\nMid Lev...   \n",
              "4             -  ...  Entry Level : 250000 - 400000  /year \\nMid Lev...   \n",
              "\n",
              "                                        Top Colleges  \\\n",
              "0  Indian Institute of Technology , Bombay\\nIndia...   \n",
              "1  Indian Veterinary Research Institute, Bareilly...   \n",
              "2  National Sugar Institute, Kanpur\\nVasantdada S...   \n",
              "3                                                  -   \n",
              "4  Veterinary College, Bangalore\\nBombay Veterina...   \n",
              "\n",
              "                                           Top Exams  \\\n",
              "0  Joint Entrance Examination (JEE) Advanced \\nBi...   \n",
              "1  All India Pre-Veterinary Test (AIPVT) \\nIndian...   \n",
              "2          All India Common Entrance Test (AICET) \\n   \n",
              "3                                                  -   \n",
              "4  All India Pre-Veterinary Test (AIPVT) \\nIndian...   \n",
              "\n",
              "                                        Work Context  \\\n",
              "0  Environment : Office setting with frequent sit...   \n",
              "1  Environment : Farm setting often outdoors with...   \n",
              "2  Environment : Sugar mills and factories. \\nHou...   \n",
              "3  Environment : Industrial setting with exposure...   \n",
              "4  Environment : Veterinary clinics animal hospit...   \n",
              "\n",
              "                                           Strengths  \\\n",
              "0  High demand for skills \\nInvolvement in sustai...   \n",
              "1  Working with animals \\nInvolvement in food pro...   \n",
              "2  Unique career path with specialized skills \\nO...   \n",
              "3  Essential role in the processing industry \\nOp...   \n",
              "4  Rewarding career helping animals \\nHigh demand...   \n",
              "\n",
              "                                          Weaknesses  \\\n",
              "0  High pressure \\nNeed for continuous learning \\...   \n",
              "1  Physically demanding work \\nIrregular hours \\n...   \n",
              "2  Seasonal nature of work \\nPhysically demanding...   \n",
              "3  High risk due to working in high-temperature c...   \n",
              "4  Emotional stress dealing with sick or dying an...   \n",
              "\n",
              "                             Career Progression Path  \\\n",
              "0  Junior Irrigation Engineer \\nIrrigation Engine...   \n",
              "1  Assistant Farm Manager \\nFarm Manager \\nGenera...   \n",
              "2  Junior Sugar Technologist \\nSugar Technologist...   \n",
              "3  Boiling In-Charge \\nBoiling Manager \\nPlant Ma...   \n",
              "4  Junior Veterinarian \\nVeterinarian \\nSenior Ve...   \n",
              "\n",
              "                                Famous Personalities  \\\n",
              "0  Dr. Mihir Shah  \\nRajendra Singh  \\nAnupam Mis...   \n",
              "1  Verghese Kurien  \\nDr. A. S. Faroda  \\nDr. Man...   \n",
              "2                                                  -   \n",
              "3                                                  -   \n",
              "4  Dr. Vallabhbhai Kathiria  \\nDr. Pawan Kumar An...   \n",
              "\n",
              "                                       Top Companies  \\\n",
              "0  Jain Irrigation Systems \\nNetafim Irrigation I...   \n",
              "1  Amul \\nVenkateshwara Hatcheries Pvt Ltd \\nSugu...   \n",
              "2  Balrampur Chini Mills Ltd \\nDhampur Sugar Mill...   \n",
              "3  Shree Renuka Sugars Ltd \\nBalrampur Chini Mill...   \n",
              "4  Indian Veterinary Research Institute \\nVeterin...   \n",
              "\n",
              "                                         Data Source  \n",
              "0  Michael Page Indian Salary Guide 2024 \\nEdumil...  \n",
              "1  Michael Page Indian Salary Guide 2024 \\nEdumil...  \n",
              "2          Michael Page Indian Salary Guide 2024 \\n-  \n",
              "3                                                  -  \n",
              "4  Michael Page Indian Salary Guide 2024 \\nEdumil...  \n",
              "\n",
              "[5 rows x 28 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-00f78102-05fd-4a2b-8afa-32bfeac219c1\" class=\"colab-df-container\">\n",
              "    <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>Career Cluster</th>\n",
              "      <th>Career Path</th>\n",
              "      <th>Career Name</th>\n",
              "      <th>New Name</th>\n",
              "      <th>Prioritization</th>\n",
              "      <th>YT link 1 (Industry)</th>\n",
              "      <th>YT link 2 (Career)</th>\n",
              "      <th>SEO Status</th>\n",
              "      <th>Review Assigned to</th>\n",
              "      <th>Review Status</th>\n",
              "      <th>...</th>\n",
              "      <th>Expected Earnings</th>\n",
              "      <th>Top Colleges</th>\n",
              "      <th>Top Exams</th>\n",
              "      <th>Work Context</th>\n",
              "      <th>Strengths</th>\n",
              "      <th>Weaknesses</th>\n",
              "      <th>Career Progression Path</th>\n",
              "      <th>Famous Personalities</th>\n",
              "      <th>Top Companies</th>\n",
              "      <th>Data Source</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>Civil Engineer, Irrigation</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>Entry Level : 300000 - 600000  /year \\nMid Lev...</td>\n",
              "      <td>Indian Institute of Technology , Bombay\\nIndia...</td>\n",
              "      <td>Joint Entrance Examination (JEE) Advanced \\nBi...</td>\n",
              "      <td>Environment : Office setting with frequent sit...</td>\n",
              "      <td>High demand for skills \\nInvolvement in sustai...</td>\n",
              "      <td>High pressure \\nNeed for continuous learning \\...</td>\n",
              "      <td>Junior Irrigation Engineer \\nIrrigation Engine...</td>\n",
              "      <td>Dr. Mihir Shah  \\nRajendra Singh  \\nAnupam Mis...</td>\n",
              "      <td>Jain Irrigation Systems \\nNetafim Irrigation I...</td>\n",
              "      <td>Michael Page Indian Salary Guide 2024 \\nEdumil...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>General Manager, Livestock Farm</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>Entry Level : 300000 - 500000  /year \\nMid Lev...</td>\n",
              "      <td>Indian Veterinary Research Institute, Bareilly...</td>\n",
              "      <td>All India Pre-Veterinary Test (AIPVT) \\nIndian...</td>\n",
              "      <td>Environment : Farm setting often outdoors with...</td>\n",
              "      <td>Working with animals \\nInvolvement in food pro...</td>\n",
              "      <td>Physically demanding work \\nIrregular hours \\n...</td>\n",
              "      <td>Assistant Farm Manager \\nFarm Manager \\nGenera...</td>\n",
              "      <td>Verghese Kurien  \\nDr. A. S. Faroda  \\nDr. Man...</td>\n",
              "      <td>Amul \\nVenkateshwara Hatcheries Pvt Ltd \\nSugu...</td>\n",
              "      <td>Michael Page Indian Salary Guide 2024 \\nEdumil...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>Sugar Technologist</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>Entry Level : 200000 - 400000  /year \\nMid Lev...</td>\n",
              "      <td>National Sugar Institute, Kanpur\\nVasantdada S...</td>\n",
              "      <td>All India Common Entrance Test (AICET) \\n</td>\n",
              "      <td>Environment : Sugar mills and factories. \\nHou...</td>\n",
              "      <td>Unique career path with specialized skills \\nO...</td>\n",
              "      <td>Seasonal nature of work \\nPhysically demanding...</td>\n",
              "      <td>Junior Sugar Technologist \\nSugar Technologist...</td>\n",
              "      <td>-</td>\n",
              "      <td>Balrampur Chini Mills Ltd \\nDhampur Sugar Mill...</td>\n",
              "      <td>Michael Page Indian Salary Guide 2024 \\n-</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>Boiling In-Charge</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>Entry Level : 200000 - 300000  /year \\nMid Lev...</td>\n",
              "      <td>-</td>\n",
              "      <td>-</td>\n",
              "      <td>Environment : Industrial setting with exposure...</td>\n",
              "      <td>Essential role in the processing industry \\nOp...</td>\n",
              "      <td>High risk due to working in high-temperature c...</td>\n",
              "      <td>Boiling In-Charge \\nBoiling Manager \\nPlant Ma...</td>\n",
              "      <td>-</td>\n",
              "      <td>Shree Renuka Sugars Ltd \\nBalrampur Chini Mill...</td>\n",
              "      <td>-</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Agriculture, Food &amp; Natural Resources</td>\n",
              "      <td>-</td>\n",
              "      <td>Veterinarian, General</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>Entry Level : 250000 - 400000  /year \\nMid Lev...</td>\n",
              "      <td>Veterinary College, Bangalore\\nBombay Veterina...</td>\n",
              "      <td>All India Pre-Veterinary Test (AIPVT) \\nIndian...</td>\n",
              "      <td>Environment : Veterinary clinics animal hospit...</td>\n",
              "      <td>Rewarding career helping animals \\nHigh demand...</td>\n",
              "      <td>Emotional stress dealing with sick or dying an...</td>\n",
              "      <td>Junior Veterinarian \\nVeterinarian \\nSenior Ve...</td>\n",
              "      <td>Dr. Vallabhbhai Kathiria  \\nDr. Pawan Kumar An...</td>\n",
              "      <td>Indian Veterinary Research Institute \\nVeterin...</td>\n",
              "      <td>Michael Page Indian Salary Guide 2024 \\nEdumil...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 28 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-00f78102-05fd-4a2b-8afa-32bfeac219c1')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-00f78102-05fd-4a2b-8afa-32bfeac219c1 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-00f78102-05fd-4a2b-8afa-32bfeac219c1');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-9a170681-e8fe-4668-b3b4-cb5f8bf73b46\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-9a170681-e8fe-4668-b3b4-cb5f8bf73b46')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-9a170681-e8fe-4668-b3b4-cb5f8bf73b46 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "df"
            }
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "name_list=[\n",
        "\"Accounts Executive (Accounts Payable & Receivables) \",\n",
        "\"Accounts Executive (Recording & Reporting)\",\n",
        "\"Administrative & Executive Official \",\n",
        "\"Assembler (Clock & Watch)\",\n",
        "\"Assistant Bar Bender & Fixer \",\n",
        "\"Assistant Laboratory & Field Technician \",\n",
        "\"Assistant Painter & Decorator\",\n",
        "\"Ball Mill Operator (Cement & Ceramics)\",\n",
        "\"Calibrator (Instrument & Apparatus)\",\n",
        "\"Chargehand False Ceiling & Drywall Installer\",\n",
        "\"Climate Change & Risk Mitigation Manager \",\n",
        "\"Clipper & Embosser \",\n",
        "\"Conche Operator (Sugar & Confectionery) \",\n",
        "\"Dehydrator Tender (Canning & Preservation)\",\n",
        "\"Designer & Sketcher \",\n",
        "\"Digger (Mine & Quarry) \",\n",
        "\"Dyestuff & Chemical Preparation Operator \",\n",
        "\"False Ceiling & Drywall Installer \",\n",
        "\"Fettler (Brick & Tile) \",\n",
        "\"Finance Insurance & Registration Coordinator\",\n",
        "\"Fitter Instrumentation (Iron & Steel Industry) \",\n",
        "\"Food & Beverage Service - Steward \",\n",
        "\"Foreman - Roads & Runways\",\n",
        "\"Furnace Operator (Smelting & Refining) \",\n",
        "\"Gemstone Final Shaper & Calibrator \",\n",
        "\"Helper Bar Bender & Fixer\",\n",
        "\"Helper Carpenter - Shuttering & Scaffolding \",\n",
        "\"Helper Laboratory & Field Technician \",\n",
        "\"Helper Painter & Decorator \",\n",
        "\"Helper-Finishing (Leather Goods & Garments)\",\n",
        "\"Helper-Parts Making (Leather Goods & Garments) \",\n",
        "\"Hydrogenation Operator (Oil & Fats)/ Oil and Fat Plant Operator\",\n",
        "\"Installation Engineer-L2 & L3 \",\n",
        "\"Installation Engineer-SDH & DWDM\",\n",
        "\"Iron & Steel Dumper Operator\",\n",
        "\"Iron & Steel Excavator Operator\",\n",
        "\"Iron & Steel Fitter: Electrical Assembly\",\n",
        "\"Iron & Steel Fitter: Electronic Assembly\",\n",
        "\"Iron & Steel Plasma Cutter: Manual \",\n",
        "\"Machinist (Iron & Steel) \",\n",
        "\"Marker/ Groundsmen & Women\",\n",
        "\"Marketing & Social Media Manager \",\n",
        "\"Merchandiser - Made-Ups & Home Furnishing\",\n",
        "\"Painting & Surface Treatment Shift In Charge\",\n",
        "\"Peeling Machine Operator (Food Canning & Preserving) \",\n",
        "\"Pickler (Can & Preservation)\",\n",
        "\"Piercer & Matcher\",\n",
        "\"Plumber (Maintenance & Servicing)\",\n",
        "\"Plumber (Maintenance & Servicing) Assistant \",\n",
        "\"Plumber (Pumps & EM Mechanic) \",\n",
        "\"Polished Grader & Assorter\",\n",
        "\"Polished Grader & Assorter: Advance \",\n",
        "\"Processing Supervisor (Dyeing & Printing) \",\n",
        "\"Refining-Machine Operator (Oil & Grease) \",\n",
        "\"Singeing & Desizing Machine Operator\",\n",
        "\"Supervisor (Plant & Machinery)\",\n",
        "\"Supervisor R&D Testing \",\n",
        "\"Travel Consultant & Organiser \"\n",
        "]"
      ],
      "metadata": {
        "id": "G4xeEx07IKKB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "for i in range(len(df['Career Name'])):\n",
        "  for j in name_list:\n",
        "    # print(df['Career Name'][i])\n",
        "    if j in df['Career Name'][i]:\n",
        "      print(f\"{j} matched agaianst{df['Career Name'][i]}\")"
      ],
      "metadata": {
        "id": "6ysKWmEeia3c"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# prompt: Give me a new excel  having the roes whole df[Career Name] is same as  names in name_list, ignore ( - , comma , space , case , ', \",)\n",
        "\n",
        "import pandas as pd\n",
        "import re\n",
        "\n",
        "def clean_text(text):\n",
        "    text = str(text)\n",
        "    text = re.sub(r'[^\\w\\s]', '', text)  # Remove punctuation\n",
        "    text = text.lower()  # Convert to lowercase\n",
        "    text = text.strip() # remove leading and trailing spaces\n",
        "    return text\n",
        "\n",
        "\n",
        "#Apply cleaning to name_list\n",
        "cleaned_name_list = [clean_text(name) for name in name_list]\n",
        "\n",
        "\n",
        "# Apply cleaning to the 'Career Name' column\n",
        "df['Cleaned Career Name'] = df['Career Name']\n",
        "# .apply(clean_text)\n",
        "\n",
        "\n",
        "# Filter the DataFrame\n",
        "filtered_df = df[df['Cleaned Career Name'].isin(name_list)]\n",
        "\n",
        "\n",
        "# Create a new Excel file\n",
        "filtered_df.to_excel('filtered_careers.xlsx', index=False)\n",
        "\n",
        "print(\"Filtered Excel file 'filtered_careers.xlsx' created successfully.\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sItwa6MqJRGy",
        "outputId": "ed174b92-9d80-4cba-e93d-d49e57501da7"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Filtered Excel file 'filtered_careers.xlsx' created successfully.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "from sentence_transformers import SentenceTransformer, util\n",
        "import numpy as np\n",
        "\n",
        "# Load the data\n",
        "file_path = \"your_file.csv\"  # Replace with your actual file path\n",
        "df = pd.read_csv(file_path)\n",
        "\n",
        "# Initialize the SentenceTransformer model\n",
        "model = SentenceTransformer('paraphrase-MiniLM-L6-v2')\n",
        "\n",
        "# Extract columns\n",
        "new_names = df[\"NEW\"].dropna().tolist()  # Exclude NaN values\n",
        "career_names = df[\"Career Name\"].tolist()\n",
        "\n",
        "# Encode the career names and new names into vectors\n",
        "new_name_embeddings = model.encode(new_names, convert_to_tensor=True)\n",
        "career_name_embeddings = model.encode(career_names, convert_to_tensor=True)\n",
        "\n",
        "# Store results\n",
        "results = []\n",
        "\n",
        "# Set a similarity threshold\n",
        "similarity_threshold = 0.7  # Adjust as needed\n",
        "\n",
        "# For each new name, find matches in the career names\n",
        "for i, new_name in enumerate(new_names):\n",
        "    # Check for exact match first\n",
        "    if new_name in career_names:\n",
        "        matched_row = df[df[\"Career Name\"] == new_name].iloc[0]\n",
        "        results.append(matched_row)\n",
        "    else:\n",
        "        # Compute cosine similarity\n",
        "        similarities = util.pytorch_cos_sim(new_name_embeddings[i], career_name_embeddings)\n",
        "\n",
        "        # Get the index of the most similar career\n",
        "        top_match_idx = np.argmax(similarities[0])\n",
        "        top_similarity = similarities[0][top_match_idx].item()\n",
        "\n",
        "        if top_similarity >= similarity_threshold:\n",
        "            matched_row = df.iloc[top_match_idx]\n",
        "            results.append(matched_row)\n",
        "\n",
        "# Create a DataFrame from the matched rows\n",
        "matched_df = pd.DataFrame(results)\n",
        "\n",
        "\n",
        "# Save the updated DataFrame to a new CSV file\n",
        "output_file = \"updated_file.csv\"  # Replace with your desired output file name\n",
        "df.to_csv(output_file, index=False)\n",
        "\n",
        "print(f\"Matching and updating complete! Results saved to {output_file}\")\n"
      ],
      "metadata": {
        "id": "GIHJtdxBp3oY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "v2\n"
      ],
      "metadata": {
        "id": "0FkbKPTsrmkA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install pandas sentence-transformers scikit-learn"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "collapsed": true,
        "id": "tidCFRizrstP",
        "outputId": "c2e49939-f7be-4784-adc0-58e34b292db2"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: pandas in /usr/local/lib/python3.10/dist-packages (2.2.2)\n",
            "Requirement already satisfied: sentence-transformers in /usr/local/lib/python3.10/dist-packages (3.3.1)\n",
            "Requirement already satisfied: scikit-learn in /usr/local/lib/python3.10/dist-packages (1.6.0)\n",
            "Requirement already satisfied: numpy>=1.22.4 in /usr/local/lib/python3.10/dist-packages (from pandas) (1.26.4)\n",
            "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.10/dist-packages (from pandas) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2024.2)\n",
            "Requirement already satisfied: tzdata>=2022.7 in /usr/local/lib/python3.10/dist-packages (from pandas) (2024.2)\n",
            "Requirement already satisfied: transformers<5.0.0,>=4.41.0 in /usr/local/lib/python3.10/dist-packages (from sentence-transformers) (4.47.1)\n",
            "Requirement already satisfied: tqdm in /usr/local/lib/python3.10/dist-packages (from sentence-transformers) (4.67.1)\n",
            "Requirement already satisfied: torch>=1.11.0 in /usr/local/lib/python3.10/dist-packages (from sentence-transformers) (2.5.1+cu121)\n",
            "Requirement already satisfied: scipy in /usr/local/lib/python3.10/dist-packages (from sentence-transformers) (1.13.1)\n",
            "Requirement already satisfied: huggingface-hub>=0.20.0 in /usr/local/lib/python3.10/dist-packages (from sentence-transformers) (0.27.0)\n",
            "Requirement already satisfied: Pillow in /usr/local/lib/python3.10/dist-packages (from sentence-transformers) (11.0.0)\n",
            "Requirement already satisfied: joblib>=1.2.0 in /usr/local/lib/python3.10/dist-packages (from scikit-learn) (1.4.2)\n",
            "Requirement already satisfied: threadpoolctl>=3.1.0 in /usr/local/lib/python3.10/dist-packages (from scikit-learn) (3.5.0)\n",
            "Requirement already satisfied: filelock in /usr/local/lib/python3.10/dist-packages (from huggingface-hub>=0.20.0->sentence-transformers) (3.16.1)\n",
            "Requirement already satisfied: fsspec>=2023.5.0 in /usr/local/lib/python3.10/dist-packages (from huggingface-hub>=0.20.0->sentence-transformers) (2024.10.0)\n",
            "Requirement already satisfied: packaging>=20.9 in /usr/local/lib/python3.10/dist-packages (from huggingface-hub>=0.20.0->sentence-transformers) (24.2)\n",
            "Requirement already satisfied: pyyaml>=5.1 in /usr/local/lib/python3.10/dist-packages (from huggingface-hub>=0.20.0->sentence-transformers) (6.0.2)\n",
            "Requirement already satisfied: requests in /usr/local/lib/python3.10/dist-packages (from huggingface-hub>=0.20.0->sentence-transformers) (2.32.3)\n",
            "Requirement already satisfied: typing-extensions>=3.7.4.3 in /usr/local/lib/python3.10/dist-packages (from huggingface-hub>=0.20.0->sentence-transformers) (4.12.2)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.2->pandas) (1.17.0)\n",
            "Requirement already satisfied: networkx in /usr/local/lib/python3.10/dist-packages (from torch>=1.11.0->sentence-transformers) (3.4.2)\n",
            "Requirement already satisfied: jinja2 in /usr/local/lib/python3.10/dist-packages (from torch>=1.11.0->sentence-transformers) (3.1.4)\n",
            "Requirement already satisfied: sympy==1.13.1 in /usr/local/lib/python3.10/dist-packages (from torch>=1.11.0->sentence-transformers) (1.13.1)\n",
            "Requirement already satisfied: mpmath<1.4,>=1.1.0 in /usr/local/lib/python3.10/dist-packages (from sympy==1.13.1->torch>=1.11.0->sentence-transformers) (1.3.0)\n",
            "Requirement already satisfied: regex!=2019.12.17 in /usr/local/lib/python3.10/dist-packages (from transformers<5.0.0,>=4.41.0->sentence-transformers) (2024.11.6)\n",
            "Requirement already satisfied: tokenizers<0.22,>=0.21 in /usr/local/lib/python3.10/dist-packages (from transformers<5.0.0,>=4.41.0->sentence-transformers) (0.21.0)\n",
            "Requirement already satisfied: safetensors>=0.4.1 in /usr/local/lib/python3.10/dist-packages (from transformers<5.0.0,>=4.41.0->sentence-transformers) (0.4.5)\n",
            "Requirement already satisfied: MarkupSafe>=2.0 in /usr/local/lib/python3.10/dist-packages (from jinja2->torch>=1.11.0->sentence-transformers) (3.0.2)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests->huggingface-hub>=0.20.0->sentence-transformers) (3.4.0)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests->huggingface-hub>=0.20.0->sentence-transformers) (3.10)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests->huggingface-hub>=0.20.0->sentence-transformers) (2.2.3)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests->huggingface-hub>=0.20.0->sentence-transformers) (2024.12.14)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "from sentence_transformers import SentenceTransformer, util\n",
        "import numpy as np\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "0Ig9SmPsrwTQ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "from sentence_transformers import SentenceTransformer, util\n",
        "import numpy as np\n",
        "\n",
        "# Load the data\n",
        "file_path = \"/content/Processed_3668_ncs_25nov_up1 - Sheet1 (2).csv\"  # Replace with your actual file path\n",
        "df = pd.read_csv(file_path)\n",
        "\n",
        "# Initialize the SentenceTransformer model\n",
        "model = SentenceTransformer('paraphrase-MiniLM-L6-v2')\n",
        "\n",
        "# Define the name list # Replace with actual names\n",
        "\n",
        "# Encode the name list and career names into vectors\n",
        "name_list_embeddings = model.encode(name_list, convert_to_tensor=True)\n",
        "career_name_embeddings = model.encode(df[\"Career Name\"].tolist(), convert_to_tensor=True)\n",
        "\n",
        "# Store results\n",
        "results = []\n",
        "\n",
        "# Set a similarity threshold\n",
        "similarity_threshold = 0.4  # Adjust as needed\n",
        "\n",
        "# For each name in the name list, find matches in the career names\n",
        "for i, name in enumerate(name_list):\n",
        "    # Compute cosine similarity\n",
        "    similarities = util.pytorch_cos_sim(name_list_embeddings[i], career_name_embeddings)\n",
        "\n",
        "    # Get the index of the most similar career\n",
        "    top_match_idx = int(np.argmax(similarities[0]).item())  # Convert to integer\n",
        "    top_similarity = similarities[0][top_match_idx].item()\n",
        "\n",
        "    if top_similarity >= similarity_threshold:\n",
        "        matched_row = df.iloc[top_match_idx]\n",
        "        results.append(matched_row)\n",
        "\n",
        "# Create a DataFrame from the matched rows\n",
        "matched_df = pd.DataFrame(results)\n",
        "\n",
        "# Save the matched rows to a new CSV file\n",
        "output_file = \"matched_rows_27.csv\"  # Replace with your desired output file name\n",
        "matched_df.to_csv(output_file, index=False)\n",
        "\n",
        "print(f\"Matching complete! Results saved to {output_file}\")\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MUpK1O8ProFY",
        "outputId": "026c7dd6-de06-43cd-eafe-8866e73d6558"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Matching complete! Results saved to matched_rows_27.csv\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "ea1Y30Rgsega"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}