Dataset Creation using AmbitionBox Web Scraping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ea75b575",
   "metadata": {},
   "source": [
    "## A Step-by-Step Guide to Web Scraping using Selenium and Python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85b17e0f",
   "metadata": {},
   "source": [
    "The first and most important requirement in the field of data science is data. However, it's not always possible to find the required datasets on websites like Kaggle or Datahub. If you are working for a company, you may need to figure out how to create your own datasets. There are a few techniques to do this, such as loading available data using APIs or Databases. But, sometimes, the data may not be readily available, and you may have to create it from scratch. In such cases, web scraping can be a useful tool to gather the required data effectively.\n",
    "\n",
    "Web scraping is a technique that extracts information from websites or web pages. This process uses automated software tools to scan the HTML of a website and gather various types of information, such as text, images, and links. The collected data can be examined, manipulated, and used for a variety of purposes like data mining, market research, and machine learning.\n",
    "\n",
    "Nowadays, there are many tools available to scrape websites, but some of them may not work if websites generate content dynamically using JavaScript. This is why Selenium is becoming increasingly popular as it offers customized configurations and behaves like a real person interacting with the website through a browser."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4d543a4",
   "metadata": {},
   "source": [
    "### Problem statement:\n",
    "###### \"Conduct an analysis of all the leading IT companies operating in India.\"\n",
    "There are various websites available from which you can obtain this data, but we will be using one of the best among them, AmbitionBox. It is a career advisory and job search platform based in India. That also provides reviews and details about companies."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "affbddf6",
   "metadata": {},
   "source": [
    "Let's explore the AmbitionBox website using browser.\n",
    "- [https://www.ambitionbox.com/list-of-companies?IndustryName=it-services-and-consulting&sort_by=popularity&page=1](https://www.ambitionbox.com/list-of-companies?IndustryName=it-services-and-consulting&sort_by=popularity&page=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7cb6141",
   "metadata": {},
   "source": [
    "![alternate image name](ScreenshotAmbitionbox.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b7420e8",
   "metadata": {},
   "source": [
    "**Observe the webpage carefully and Lets see how can we extract the data.**\n",
    "\n",
    "1. Ambitionbox has details available for around 14,588 companies, but we will be focusing mainly on only IT companies.\n",
    "2. Each company is assigned with a separate box along with all the details regarding the company\n",
    "3. Each page has around 30 boxes meaning one page represent data about 30 companies.\n",
    "4. we have around 333 pages so we will have around 333 * 30 = 9990 companies."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb68ebd4",
   "metadata": {},
   "source": [
    "Importing all required libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cc6749a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.chrome.options import Options\n",
    "from selenium.webdriver.chrome.service import Service\n",
    "from time import sleep\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c05e0c40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setting up the driver path where we have installed the chromedriver\n",
    "\n",
    "DRIVER_PATH = 'D:\\Applications\\Chromium\\chromedriver_win32'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6f1793ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating a Service object using the specified driver_path which enables communication\n",
    "# between the Selenium WebDriver and the browser.\n",
    "\n",
    "s=Service(DRIVER_PATH)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a06166d",
   "metadata": {},
   "source": [
    "### Extracting the data\n",
    "\n",
    "In the next code block, we will use Selenium to iterate through all web pages and extract all the data using BeautifulSoup along with regular expressions. The extracted data will be saved into a dataset. Additionally, we will print the page number after reading each page so that if any issues occur, we can identify the page where the problem occurred."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bdc23ffa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31|32|33|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48|49|50|51|52|53|54|55|56|57|58|59|60|61|62|63|64|65|66|67|68|69|70|71|72|73|74|75|76|77|78|79|80|81|82|83|84|85|86|87|88|89|90|91|92|93|94|95|96|97|98|99|100|101|102|103|104|105|106|107|108|109|110|111|112|113|114|115|116|117|118|119|120|121|122|123|124|125|126|127|128|129|130|131|132|133|134|135|136|137|138|139|140|141|142|143|144|145|146|147|148|149|150|151|152|153|154|155|156|157|158|159|160|161|162|163|164|165|166|167|168|169|170|171|172|173|174|175|176|177|178|179|180|181|182|183|184|185|186|187|188|189|190|191|192|193|194|195|196|197|198|199|200|201|202|203|204|205|206|207|208|209|210|211|212|213|214|215|216|217|218|219|220|221|222|223|224|225|226|227|228|229|230|231|232|233|234|235|236|237|238|239|240|241|242|243|244|245|246|247|248|249|250|251|252|253|254|255|256|257|258|259|260|261|262|263|264|265|266|267|268|269|270|271|272|273|274|275|276|277|278|279|280|281|282|283|284|285|286|287|288|289|290|291|292|293|294|295|296|297|298|299|300|301|302|303|304|305|306|307|308|309|310|311|312|313|314|315|316|317|318|319|320|321|322|323|324|325|326|327|328|329|330|331|332|333|done\n",
      "CPU times: total: 31.3 s\n",
      "Wall time: 44min 14s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "company_df = pd.DataFrame(columns = [\n",
    "    'name','rating','review','tags','company_type','headquarters','age','total_emp','about'\n",
    "])\n",
    "\n",
    "options = Options()\n",
    "options.add_argument('--disable-blink-features=AutomationControlled')\n",
    "options.add_argument(\"--window-size=1920,1200\")\n",
    "\n",
    "\n",
    "for p in range(1,334):\n",
    "    try:\n",
    "        driver = webdriver.Chrome(service=s,options=options)\n",
    "        url=f\"https://www.ambitionbox.com/list-of-companies?IndustryName=it-services-and-consulting&sort_by=popularity&page={p}\"\n",
    "        driver.get(url)\n",
    "        soup = BeautifulSoup(driver.page_source,'html.parser')\n",
    "        companies = soup.find_all(\"div\", class_=\"company-content-wrapper\")\n",
    "\n",
    "        for i in companies:\n",
    "            name = None\n",
    "            rating = None\n",
    "            review = None\n",
    "            tags = None\n",
    "\n",
    "            company_type = None\n",
    "            headquarters = None\n",
    "            age = None\n",
    "            total_emp = None\n",
    "\n",
    "            about = None\n",
    "\n",
    "            try:\n",
    "\n",
    "                name = i.find('h2',class_='company-name bold-title-l').text.strip()\n",
    "                rating = i.find('p',class_='rating').text.strip()\n",
    "                review = i.find('a',class_='review-count sbold-Labels').text.strip()\n",
    "                tags = ','.join([t.find('a',class_='ab_chip body-medium').text.strip() for t in i.find('ul',class_='chips-block')])\n",
    "\n",
    "                names = i.find('div',class_='company-basic-info')\n",
    "\n",
    "                for n in names.find_all('p'):\n",
    "                    try:\n",
    "                        if n.find('i',class_='icon-domain'): company_type = n.text.strip()\n",
    "                        if n.find('i',class_='icon-pin-drop'): headquarters = n.text.strip()\n",
    "                        if n.find('i',class_='icon-access-time'): age = n.text.strip()\n",
    "                        if n.find('i',class_='icon-supervisor-account'): total_emp = n.text.strip()\n",
    "                    except:\n",
    "                        continue\n",
    "\n",
    "                about = i.find('p',class_='description').text.strip()\n",
    "                \n",
    "            except:\n",
    "                continue\n",
    "            finally:\n",
    "                company_df.loc[len(company_df)] = [name,rating,review,tags,company_type,headquarters,age,total_emp,about]\n",
    "                \n",
    "        \n",
    "        sleep(1)\n",
    "    except Exception as e:\n",
    "        print(f\"Something went wrong: {e}\")\n",
    "    finally:\n",
    "        driver.quit()\n",
    "        print(p, end='|')\n",
    "    \n",
    "print('done')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcaa2a16",
   "metadata": {},
   "source": [
    "Lets explore the dataset created"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aa97b35a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>rating</th>\n",
       "      <th>review</th>\n",
       "      <th>tags</th>\n",
       "      <th>company_type</th>\n",
       "      <th>headquarters</th>\n",
       "      <th>age</th>\n",
       "      <th>total_emp</th>\n",
       "      <th>about</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TCS</td>\n",
       "      <td>3.9</td>\n",
       "      <td>(56k Reviews)</td>\n",
       "      <td>BPO,IT Services &amp; Consulting,Forbes Global 200...</td>\n",
       "      <td>Public</td>\n",
       "      <td>Mumbai,Maharashtra + 274 more</td>\n",
       "      <td>55 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>We ensure the highest levels of certainty and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Accenture</td>\n",
       "      <td>4.1</td>\n",
       "      <td>(36k Reviews)</td>\n",
       "      <td>IT Services &amp; Consulting,Software Product,Forb...</td>\n",
       "      <td>Public</td>\n",
       "      <td>Dublin + 134 more</td>\n",
       "      <td>34 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>Accenture is a global professional services co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cognizant</td>\n",
       "      <td>3.9</td>\n",
       "      <td>(33k Reviews)</td>\n",
       "      <td>IT Services &amp; Consulting,Forbes Global 2000</td>\n",
       "      <td>Private</td>\n",
       "      <td>Teaneck. New Jersey. + 105 more</td>\n",
       "      <td>29 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>At Cognizant, we give organizations the insigh...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wipro</td>\n",
       "      <td>3.9</td>\n",
       "      <td>(26.2k Reviews)</td>\n",
       "      <td>IT Services &amp; Consulting,Conglomerate,Forbes G...</td>\n",
       "      <td>Public</td>\n",
       "      <td>Bangalore/Bengaluru,Karnataka + 232 more</td>\n",
       "      <td>78 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>Wipro Limited (NYSE: WIT, BSE: 507685, NSE: WI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Infosys</td>\n",
       "      <td>3.9</td>\n",
       "      <td>(24.6k Reviews)</td>\n",
       "      <td>IT Services &amp; Consulting,Forbes Global 2000,Fo...</td>\n",
       "      <td>Public</td>\n",
       "      <td>Bengaluru/Bangalore,Karnataka + 129 more</td>\n",
       "      <td>42 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>Infosys is a global leader in next-generation ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name rating           review  \\\n",
       "0        TCS    3.9    (56k Reviews)   \n",
       "1  Accenture    4.1    (36k Reviews)   \n",
       "2  Cognizant    3.9    (33k Reviews)   \n",
       "3      Wipro    3.9  (26.2k Reviews)   \n",
       "4    Infosys    3.9  (24.6k Reviews)   \n",
       "\n",
       "                                                tags company_type  \\\n",
       "0  BPO,IT Services & Consulting,Forbes Global 200...       Public   \n",
       "1  IT Services & Consulting,Software Product,Forb...       Public   \n",
       "2        IT Services & Consulting,Forbes Global 2000      Private   \n",
       "3  IT Services & Consulting,Conglomerate,Forbes G...       Public   \n",
       "4  IT Services & Consulting,Forbes Global 2000,Fo...       Public   \n",
       "\n",
       "                               headquarters           age  \\\n",
       "0             Mumbai,Maharashtra + 274 more  55 years old   \n",
       "1                         Dublin + 134 more  34 years old   \n",
       "2           Teaneck. New Jersey. + 105 more  29 years old   \n",
       "3  Bangalore/Bengaluru,Karnataka + 232 more  78 years old   \n",
       "4  Bengaluru/Bangalore,Karnataka + 129 more  42 years old   \n",
       "\n",
       "                   total_emp  \\\n",
       "0  1 Lakh+ Employees (India)   \n",
       "1  1 Lakh+ Employees (India)   \n",
       "2  1 Lakh+ Employees (India)   \n",
       "3  1 Lakh+ Employees (India)   \n",
       "4  1 Lakh+ Employees (India)   \n",
       "\n",
       "                                               about  \n",
       "0  We ensure the highest levels of certainty and ...  \n",
       "1  Accenture is a global professional services co...  \n",
       "2  At Cognizant, we give organizations the insigh...  \n",
       "3  Wipro Limited (NYSE: WIT, BSE: 507685, NSE: WI...  \n",
       "4  Infosys is a global leader in next-generation ...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "company_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6bff6d0",
   "metadata": {},
   "source": [
    "### Manipulating the Data\n",
    "\n",
    "We have obtained the data, but it is not in a format that will be useful for analysis. Therefore, we need to clean the data and retain only the necessary information.\n",
    "To ensure that we do not lose our original data in case anything goes wrong, we will perform these manipulations on a copy of the original dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "80e8c31d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = company_df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67daf77c",
   "metadata": {},
   "source": [
    "#### 1. review\n",
    "This column represents numerical data, but Pandas has assigned it as a string. We will create a function that extracts only numerical data from the string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3ae1089a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def transform_review(data):\n",
    "    try:\n",
    "        if re.search(r'\\d+k',data):\n",
    "            return float(re.search(r'\\d+\\.\\d+|\\d+',data).group()) * 1000\n",
    "        elif re.search(r'\\d+',data):\n",
    "            return float(re.search(r'\\d+',data).group())\n",
    "        else:\n",
    "            return 0\n",
    "    except:\n",
    "        return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dad13465",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['review'] = df['review'].apply(transform_review)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2c38e1fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>rating</th>\n",
       "      <th>review</th>\n",
       "      <th>tags</th>\n",
       "      <th>company_type</th>\n",
       "      <th>headquarters</th>\n",
       "      <th>age</th>\n",
       "      <th>total_emp</th>\n",
       "      <th>about</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TCS</td>\n",
       "      <td>3.9</td>\n",
       "      <td>56000.0</td>\n",
       "      <td>BPO,IT Services &amp; Consulting,Forbes Global 200...</td>\n",
       "      <td>Public</td>\n",
       "      <td>Mumbai,Maharashtra + 274 more</td>\n",
       "      <td>55 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>We ensure the highest levels of certainty and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Accenture</td>\n",
       "      <td>4.1</td>\n",
       "      <td>36000.0</td>\n",
       "      <td>IT Services &amp; Consulting,Software Product,Forb...</td>\n",
       "      <td>Public</td>\n",
       "      <td>Dublin + 134 more</td>\n",
       "      <td>34 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>Accenture is a global professional services co...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name rating   review  \\\n",
       "0        TCS    3.9  56000.0   \n",
       "1  Accenture    4.1  36000.0   \n",
       "\n",
       "                                                tags company_type  \\\n",
       "0  BPO,IT Services & Consulting,Forbes Global 200...       Public   \n",
       "1  IT Services & Consulting,Software Product,Forb...       Public   \n",
       "\n",
       "                    headquarters           age                  total_emp  \\\n",
       "0  Mumbai,Maharashtra + 274 more  55 years old  1 Lakh+ Employees (India)   \n",
       "1              Dublin + 134 more  34 years old  1 Lakh+ Employees (India)   \n",
       "\n",
       "                                               about  \n",
       "0  We ensure the highest levels of certainty and ...  \n",
       "1  Accenture is a global professional services co...  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "539ba3de",
   "metadata": {},
   "source": [
    "#### 2. headquarters\n",
    "headquarters column consist of both the actual headquarters location and the number of offices that the company has. We can separate this information into two distinct columns, namely \"headquarter\" and \"total_offices\"."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39f0c9a1",
   "metadata": {},
   "source": [
    "- a. headquarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "85f70add",
   "metadata": {},
   "outputs": [],
   "source": [
    "def transform_headquarter(data):\n",
    "    try:\n",
    "        return data.split('+') [0].strip()\n",
    "    except:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f85e7fd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['headquarter'] = df['headquarters'].apply(transform_headquarter)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d71bec5b",
   "metadata": {},
   "source": [
    "- b. total_offices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "aaf6fc5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def transform_total_offices(data):\n",
    "    try:\n",
    "        if re.search(r'\\d+',data):\n",
    "            data = data.split('+') [1].strip()\n",
    "            return int(re.search(r'\\d+',data).group()) + 1\n",
    "        else:\n",
    "            return 1\n",
    "    except:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ece12b0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['total_offices'] = df['headquarters'].apply(transform_total_offices)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5b9963d",
   "metadata": {},
   "source": [
    "Now that we have successfully separated and added our columns, we can delete the unnecessary column 'headquarters'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4747f150",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['headquarters']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fb479b2c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>rating</th>\n",
       "      <th>review</th>\n",
       "      <th>tags</th>\n",
       "      <th>company_type</th>\n",
       "      <th>age</th>\n",
       "      <th>total_emp</th>\n",
       "      <th>about</th>\n",
       "      <th>headquarter</th>\n",
       "      <th>total_offices</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TCS</td>\n",
       "      <td>3.9</td>\n",
       "      <td>56000.0</td>\n",
       "      <td>BPO,IT Services &amp; Consulting,Forbes Global 200...</td>\n",
       "      <td>Public</td>\n",
       "      <td>55 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>We ensure the highest levels of certainty and ...</td>\n",
       "      <td>Mumbai,Maharashtra</td>\n",
       "      <td>275.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Accenture</td>\n",
       "      <td>4.1</td>\n",
       "      <td>36000.0</td>\n",
       "      <td>IT Services &amp; Consulting,Software Product,Forb...</td>\n",
       "      <td>Public</td>\n",
       "      <td>34 years old</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>Accenture is a global professional services co...</td>\n",
       "      <td>Dublin</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name rating   review  \\\n",
       "0        TCS    3.9  56000.0   \n",
       "1  Accenture    4.1  36000.0   \n",
       "\n",
       "                                                tags company_type  \\\n",
       "0  BPO,IT Services & Consulting,Forbes Global 200...       Public   \n",
       "1  IT Services & Consulting,Software Product,Forb...       Public   \n",
       "\n",
       "            age                  total_emp  \\\n",
       "0  55 years old  1 Lakh+ Employees (India)   \n",
       "1  34 years old  1 Lakh+ Employees (India)   \n",
       "\n",
       "                                               about         headquarter  \\\n",
       "0  We ensure the highest levels of certainty and ...  Mumbai,Maharashtra   \n",
       "1  Accenture is a global professional services co...              Dublin   \n",
       "\n",
       "   total_offices  \n",
       "0          275.0  \n",
       "1          135.0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "637a758b",
   "metadata": {},
   "source": [
    "#### 3. age\n",
    "This simply displays the age of the company from the year of its establishment. We can convert this numerical data into a date format and label it as 'founded_year', to gain a better understanding."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8face2c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def transform_age(data):\n",
    "    try:\n",
    "        return int(pd.to_datetime('today').date().year - int(re.search(r'\\d+',data).group()))\n",
    "    except:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5b4c0390",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['founded_year'] = df['age'].apply(transform_age)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "c7fa5dc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['age']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "657e1f68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>rating</th>\n",
       "      <th>review</th>\n",
       "      <th>tags</th>\n",
       "      <th>company_type</th>\n",
       "      <th>total_emp</th>\n",
       "      <th>about</th>\n",
       "      <th>headquarter</th>\n",
       "      <th>total_offices</th>\n",
       "      <th>founded_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TCS</td>\n",
       "      <td>3.9</td>\n",
       "      <td>56000.0</td>\n",
       "      <td>BPO,IT Services &amp; Consulting,Forbes Global 200...</td>\n",
       "      <td>Public</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>We ensure the highest levels of certainty and ...</td>\n",
       "      <td>Mumbai,Maharashtra</td>\n",
       "      <td>275.0</td>\n",
       "      <td>1968.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Accenture</td>\n",
       "      <td>4.1</td>\n",
       "      <td>36000.0</td>\n",
       "      <td>IT Services &amp; Consulting,Software Product,Forb...</td>\n",
       "      <td>Public</td>\n",
       "      <td>1 Lakh+ Employees (India)</td>\n",
       "      <td>Accenture is a global professional services co...</td>\n",
       "      <td>Dublin</td>\n",
       "      <td>135.0</td>\n",
       "      <td>1989.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name rating   review  \\\n",
       "0        TCS    3.9  56000.0   \n",
       "1  Accenture    4.1  36000.0   \n",
       "\n",
       "                                                tags company_type  \\\n",
       "0  BPO,IT Services & Consulting,Forbes Global 200...       Public   \n",
       "1  IT Services & Consulting,Software Product,Forb...       Public   \n",
       "\n",
       "                   total_emp  \\\n",
       "0  1 Lakh+ Employees (India)   \n",
       "1  1 Lakh+ Employees (India)   \n",
       "\n",
       "                                               about         headquarter  \\\n",
       "0  We ensure the highest levels of certainty and ...  Mumbai,Maharashtra   \n",
       "1  Accenture is a global professional services co...              Dublin   \n",
       "\n",
       "   total_offices  founded_year  \n",
       "0          275.0        1968.0  \n",
       "1          135.0        1989.0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "515ae0b7",
   "metadata": {},
   "source": [
    "#### 4. total_emp\n",
    "Due to the mixture of text and numbers, the data is displayed as string values instead of numeric values. We will convert this data into numeric format. Please note that this is not the actual number of employees, but instead represents the minimum count of employees working in India."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a908e46d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def transform_total_emp(data):\n",
    "    try:\n",
    "        values = list(map(int,re.findall(r'\\d+\\.\\d+|\\d+',data)))\n",
    "        units = re.findall(r'Lakh|k',data)\n",
    "        if len(values)==0:\n",
    "            return 0\n",
    "        elif len(values)==1:\n",
    "            if units[0] == 'Lakh':\n",
    "                return values[0] * 100000\n",
    "            elif units[0] == 'k':\n",
    "                return values[0] * 1000\n",
    "            else:\n",
    "                return values[0]\n",
    "        elif len(values)==2:\n",
    "            if len(units)==0:\n",
    "                return (values[0]+values[1])/2\n",
    "            elif len(units)==1:\n",
    "                return values[0]+(values[1]*1000)/2\n",
    "            elif len(units)==2:\n",
    "                if units[0] == units[1] == 'Lakh':\n",
    "                    return (values[0]+values[1])/2 * 100000\n",
    "                elif units[0] == units[1] == 'k':\n",
    "                    return (values[0]+values[1])/2 * 1000\n",
    "                else:\n",
    "                    return ((values[0]*1000) + (values[1]*100000))/2 \n",
    "        else:\n",
    "            return 0\n",
    "    except:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e31bfc8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['total_emp'] = df['total_emp'].apply(transform_total_emp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d74412aa",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>rating</th>\n",
       "      <th>review</th>\n",
       "      <th>tags</th>\n",
       "      <th>company_type</th>\n",
       "      <th>total_emp</th>\n",
       "      <th>about</th>\n",
       "      <th>headquarter</th>\n",
       "      <th>total_offices</th>\n",
       "      <th>founded_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TCS</td>\n",
       "      <td>3.9</td>\n",
       "      <td>56000.0</td>\n",
       "      <td>BPO,IT Services &amp; Consulting,Forbes Global 200...</td>\n",
       "      <td>Public</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>We ensure the highest levels of certainty and ...</td>\n",
       "      <td>Mumbai,Maharashtra</td>\n",
       "      <td>275.0</td>\n",
       "      <td>1968.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Accenture</td>\n",
       "      <td>4.1</td>\n",
       "      <td>36000.0</td>\n",
       "      <td>IT Services &amp; Consulting,Software Product,Forb...</td>\n",
       "      <td>Public</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>Accenture is a global professional services co...</td>\n",
       "      <td>Dublin</td>\n",
       "      <td>135.0</td>\n",
       "      <td>1989.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name rating   review  \\\n",
       "0        TCS    3.9  56000.0   \n",
       "1  Accenture    4.1  36000.0   \n",
       "\n",
       "                                                tags company_type  total_emp  \\\n",
       "0  BPO,IT Services & Consulting,Forbes Global 200...       Public   100000.0   \n",
       "1  IT Services & Consulting,Software Product,Forb...       Public   100000.0   \n",
       "\n",
       "                                               about         headquarter  \\\n",
       "0  We ensure the highest levels of certainty and ...  Mumbai,Maharashtra   \n",
       "1  Accenture is a global professional services co...              Dublin   \n",
       "\n",
       "   total_offices  founded_year  \n",
       "0          275.0        1968.0  \n",
       "1          135.0        1989.0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a9875c4b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 9990 entries, 0 to 9989\n",
      "Data columns (total 10 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   name           9990 non-null   object \n",
      " 1   rating         9990 non-null   object \n",
      " 2   review         9990 non-null   float64\n",
      " 3   tags           9990 non-null   object \n",
      " 4   company_type   7344 non-null   object \n",
      " 5   total_emp      9101 non-null   float64\n",
      " 6   about          6699 non-null   object \n",
      " 7   headquarter    9171 non-null   object \n",
      " 8   total_offices  9166 non-null   float64\n",
      " 9   founded_year   7675 non-null   float64\n",
      "dtypes: float64(4), object(6)\n",
      "memory usage: 858.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c537c39",
   "metadata": {},
   "source": [
    "Data has been cleaned successfuly lets correct the datatype to store it in efficient way"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "5bca0e52",
   "metadata": {},
   "outputs": [],
   "source": [
    "col_dtypes = {\n",
    "                'name': str,\n",
    "                'rating': int,\n",
    "                'review': float,\n",
    "                'tags': str,\n",
    "                'company_type': str,\n",
    "                'total_emp': float,\n",
    "                'about': str,\n",
    "                'headquarter': str,\n",
    "                'total_offices': int,\n",
    "                'founded_year': int,\n",
    "            }\n",
    "df = df.astype(col_dtypes,errors='ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ceb9e986",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 9990 entries, 0 to 9989\n",
      "Data columns (total 10 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   name           9990 non-null   object \n",
      " 1   rating         9990 non-null   object \n",
      " 2   review         9990 non-null   float64\n",
      " 3   tags           9990 non-null   object \n",
      " 4   company_type   9990 non-null   object \n",
      " 5   total_emp      9101 non-null   float64\n",
      " 6   about          9990 non-null   object \n",
      " 7   headquarter    9990 non-null   object \n",
      " 8   total_offices  9166 non-null   float64\n",
      " 9   founded_year   7675 non-null   float64\n",
      "dtypes: float64(4), object(6)\n",
      "memory usage: 858.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ceee4ce",
   "metadata": {},
   "source": [
    "Lets check the whole data once again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "6447f2b1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>rating</th>\n",
       "      <th>review</th>\n",
       "      <th>tags</th>\n",
       "      <th>company_type</th>\n",
       "      <th>total_emp</th>\n",
       "      <th>about</th>\n",
       "      <th>headquarter</th>\n",
       "      <th>total_offices</th>\n",
       "      <th>founded_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TCS</td>\n",
       "      <td>3.9</td>\n",
       "      <td>56000.0</td>\n",
       "      <td>BPO,IT Services &amp; Consulting,Forbes Global 200...</td>\n",
       "      <td>Public</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>We ensure the highest levels of certainty and ...</td>\n",
       "      <td>Mumbai,Maharashtra</td>\n",
       "      <td>275.0</td>\n",
       "      <td>1968.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Accenture</td>\n",
       "      <td>4.1</td>\n",
       "      <td>36000.0</td>\n",
       "      <td>IT Services &amp; Consulting,Software Product,Forb...</td>\n",
       "      <td>Public</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>Accenture is a global professional services co...</td>\n",
       "      <td>Dublin</td>\n",
       "      <td>135.0</td>\n",
       "      <td>1989.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cognizant</td>\n",
       "      <td>3.9</td>\n",
       "      <td>33000.0</td>\n",
       "      <td>IT Services &amp; Consulting,Forbes Global 2000</td>\n",
       "      <td>Private</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>At Cognizant, we give organizations the insigh...</td>\n",
       "      <td>Teaneck. New Jersey.</td>\n",
       "      <td>106.0</td>\n",
       "      <td>1994.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wipro</td>\n",
       "      <td>3.9</td>\n",
       "      <td>26200.0</td>\n",
       "      <td>IT Services &amp; Consulting,Conglomerate,Forbes G...</td>\n",
       "      <td>Public</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>Wipro Limited (NYSE: WIT, BSE: 507685, NSE: WI...</td>\n",
       "      <td>Bangalore/Bengaluru,Karnataka</td>\n",
       "      <td>233.0</td>\n",
       "      <td>1945.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Infosys</td>\n",
       "      <td>3.9</td>\n",
       "      <td>24600.0</td>\n",
       "      <td>IT Services &amp; Consulting,Forbes Global 2000,Fo...</td>\n",
       "      <td>Public</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>Infosys is a global leader in next-generation ...</td>\n",
       "      <td>Bengaluru/Bangalore,Karnataka</td>\n",
       "      <td>130.0</td>\n",
       "      <td>1981.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        name rating   review  \\\n",
       "0        TCS    3.9  56000.0   \n",
       "1  Accenture    4.1  36000.0   \n",
       "2  Cognizant    3.9  33000.0   \n",
       "3      Wipro    3.9  26200.0   \n",
       "4    Infosys    3.9  24600.0   \n",
       "\n",
       "                                                tags company_type  total_emp  \\\n",
       "0  BPO,IT Services & Consulting,Forbes Global 200...       Public   100000.0   \n",
       "1  IT Services & Consulting,Software Product,Forb...       Public   100000.0   \n",
       "2        IT Services & Consulting,Forbes Global 2000      Private   100000.0   \n",
       "3  IT Services & Consulting,Conglomerate,Forbes G...       Public   100000.0   \n",
       "4  IT Services & Consulting,Forbes Global 2000,Fo...       Public   100000.0   \n",
       "\n",
       "                                               about  \\\n",
       "0  We ensure the highest levels of certainty and ...   \n",
       "1  Accenture is a global professional services co...   \n",
       "2  At Cognizant, we give organizations the insigh...   \n",
       "3  Wipro Limited (NYSE: WIT, BSE: 507685, NSE: WI...   \n",
       "4  Infosys is a global leader in next-generation ...   \n",
       "\n",
       "                     headquarter  total_offices  founded_year  \n",
       "0             Mumbai,Maharashtra          275.0        1968.0  \n",
       "1                         Dublin          135.0        1989.0  \n",
       "2           Teaneck. New Jersey.          106.0        1994.0  \n",
       "3  Bangalore/Bengaluru,Karnataka          233.0        1945.0  \n",
       "4  Bengaluru/Bangalore,Karnataka          130.0        1981.0  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24232d13",
   "metadata": {},
   "source": [
    "We have all the necessary details, so now we can save the dataset in the desired format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c02443b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('AmbitionBox_Dataset.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eddf0d11",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}