104_high_salary.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 總時間約25分鐘\n",
    "# 上市櫃公司平均薪資要高於多少? 請在這邊調整，單位是千元\n",
    "salary_criteria = 1200"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import requests\n",
    "from bs4 import BeautifulSoup as bs\n",
    "from webdriver_manager.chrome import ChromeDriverManager\n",
    "from selenium.webdriver.chrome.service import Service\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.chrome.options import Options\n",
    "from selenium.webdriver.common.by import By\n",
    "import pandas as pd\n",
    "import time\n",
    "import difflib\n",
    "import winsound"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 讀取上市櫃薪資排行榜，找出高薪資公司\n",
    "# 不論員工人數，所以控股公司也會被列入\n",
    "salary = pd.read_excel('salary_data.xlsx', sheet_name='上市櫃5年薪資中位數')\n",
    "salary_high = salary[salary['非擔任主管職務之全時員工資訊-員工薪資-中位數(仟元/人)-111年'] > salary_criteria]\n",
    "high_company_list = salary_high['公司全名'].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove nan\n",
    "# high_company_list_original變數不可覆蓋，要給後面比對公司用\n",
    "high_company_list_original = [x for x in high_company_list if str(x) != 'nan']\n",
    "\n",
    "# 只取前面5個字，模糊搜尋，比較容易找到公司名稱\n",
    "# 日月光投資控股股份有限公司 - 全名會找不到，所以只取前5個字，識別機率更高\n",
    "high_company_list = [i[:5] for i in high_company_list_original]\n",
    "search_company = ['https://www.104.com.tw/company/search/?keyword={}'.format(i) for i in high_company_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用difflib，找出最相似的公司名稱\n",
    "def similar_diff_qk_ratio(str1, str2):\n",
    "    return difflib.SequenceMatcher(None, str1, str2).quick_ratio()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 自動翻頁\n",
    "def scroll(driver, timeout):\n",
    "    scroll_pause_time = timeout\n",
    "\n",
    "    # Get scroll height\n",
    "    last_height = driver.execute_script('return document.body.scrollHeight')\n",
    "\n",
    "    while True:\n",
    "        # Scroll down to bottom\n",
    "        driver.execute_script('window.scrollTo(0, document.body.scrollHeight)')\n",
    "\n",
    "        # Wait to load page\n",
    "        time.sleep(scroll_pause_time)\n",
    "\n",
    "        # Calculate new scroll height and compare with last scroll height\n",
    "        new_height = driver.execute_script('return document.body.scrollHeight')\n",
    "        if new_height == last_height:\n",
    "            # If heights are the same it will exit the function\n",
    "            break\n",
    "        last_height = new_height"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 抓公司專頁的網址\n",
    "company_dict = {}\n",
    "\n",
    "for original_company_name, url in zip(high_company_list_original, search_company):\n",
    "    res = requests.get(url)\n",
    "    soup = bs(res.text, 'lxml')\n",
    "\n",
    "    # 搜尋結果，只抓前面6個公司名稱進行比對，抓太多可能會識別到其他更相似公司\n",
    "    # 日月光投資控股股份有限公司，每次搜尋排名都不一樣，容易抓錯\n",
    "    catch_company = []\n",
    "    for _ in soup.select('div > a.d-none.d-md-inline.jb-link.jb-link-blue.jb-link-blue--visited.h2.mb-1')[:6]:\n",
    "        catch_company.append(_.text)\n",
    "\n",
    "    similar_company = []\n",
    "    for random_name in catch_company:\n",
    "        similar_company.append(similar_diff_qk_ratio(random_name, original_company_name))\n",
    "\n",
    "    # 抓取正確的公司名稱及網址\n",
    "    try:\n",
    "        # 返回最大值的索引，即最相似的公司名稱順位\n",
    "        index = similar_company.index(max(similar_company))\n",
    "\n",
    "        correct_name = soup.select('div > a.d-none.d-md-inline.jb-link.jb-link-blue.jb-link-blue--visited.h2.mb-1')[index].text\n",
    "        correct_page = soup.select('div > a.d-none.d-md-inline.jb-link.jb-link-blue.jb-link-blue--visited.h2.mb-1')[index]['href']\n",
    "        company_dict.update({correct_name : correct_page})\n",
    "        print('爬完' + '\\t\\t' + correct_name)\n",
    "    except:\n",
    "        print('找不到' + '\\t' + original_company_name)\n",
    "        \n",
    "print('共' + str(len(company_dict)) + '家公司')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "chrome_options = Options()\n",
    "chrome_options.add_argument('--headless')           # 指定headless，并且禁用gpu\n",
    "chrome_options.add_argument('--disable-gpu')\n",
    "chrome_options.add_argument('window-size=1920,1080') # 設定瀏覽器尺寸，假如無法滾動的話\n",
    "\n",
    "service = Service(ChromeDriverManager().install())\n",
    "driver = webdriver.Chrome(service=service, options=chrome_options)\n",
    "\n",
    "driver.get(\"https://www.104.com.tw/jobs/main/\")\n",
    "# driver.maximize_window()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "company = []\n",
    "job = []\n",
    "location = []\n",
    "salary = []\n",
    "requirement = []\n",
    "education = []\n",
    "descirption = []\n",
    "link = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 頁碼規則\n",
    "# https://www.104.com.tw/company/12noppgo?job=&roleJobCat=0_0&area=0&page=1&pageSize=20&order=8&asc=0&jobsource=cs_custlist&tab=job\n",
    "# https://www.104.com.tw/company/12noppgo?job=&roleJobCat=0_0&area=0&page=2&pageSize=20&order=8&asc=0&jobsource=cs_custlist&tab=job"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for name, url in zip(company_dict.keys(), company_dict.values()):\n",
    "    driver.get(url)\n",
    "    time.sleep(1)   # 等待網頁載入\n",
    "\n",
    "    # 預設顯示20個工作，找出總頁數\n",
    "    try:\n",
    "        text = driver.find_element(By.CSS_SELECTOR, 'div.col.main > div.joblist.rounded > div.joblist__footer > div > div:nth-child(2) > label > select').text\n",
    "        total_page = int(re.findall(r'\\d+', text)[-1])\n",
    "    except:\n",
    "        #如果找不到每筆100頁選單，則總頁數為1頁\n",
    "        total_page = 1\n",
    "\n",
    "    print(name + '\\t' + '總頁數' + str(total_page))\n",
    "\n",
    "    # 先寫爬取第一頁，之後再翻頁\n",
    "    for i in range(1, total_page+1):\n",
    "        driver.get(url + '?job=&roleJobCat=0_0&area=0&page={}&pageSize=20&order=8&asc=0&jobsource=cs_custlist&tab=job'.format(i))\n",
    "        # 自動翻頁\n",
    "        scroll(driver, 1)\n",
    "        for i in range(1, 105): # 避免中間有廣告或是在頂端的急徵工作，所以最大值>100\n",
    "            try:\n",
    "                job_tag = driver.find_element(By.CSS_SELECTOR, \"div:nth-child({}) > div > div.info.col > div > div.info-job.text-break.mb-2 > a\".format(i)).text\n",
    "                link_tag = driver.find_element(By.CSS_SELECTOR, \"div:nth-child({}) > div > div.info.col > div > div.info-job.text-break.mb-2 > a\".format(i)).get_attribute(\"href\")\n",
    "                location_tag = driver.find_element(By.CSS_SELECTOR, 'div:nth-child({}) > div > div.info.col > div > div.info-tags.gray-deep-dark > span:nth-child(1)'.format(i)).text\n",
    "                requirement_tag = driver.find_element(By.CSS_SELECTOR, 'div:nth-child({}) > div > div.info.col > div > div.info-tags.gray-deep-dark > span:nth-child(2)'.format(i)).text\n",
    "                education_tag = driver.find_element(By.CSS_SELECTOR, 'div:nth-child({}) > div > div.info.col > div > div.info-tags.gray-deep-dark > span:nth-child(3)'.format(i)).text\n",
    "                descirption_tag = driver.find_element(By.CSS_SELECTOR, 'div:nth-child({}) > div > div.info.col > div > div.info-description.text-gray-darker.t4.text-break.mt-2.position-relative.info-description__line2'.format(i)).text\n",
    "                salary_tag = driver.find_element(By.CSS_SELECTOR, 'div:nth-child({}) > div > div.info.col > div > div.info-othertags > span'.format(i)).text\n",
    "\n",
    "                job.append(job_tag)\n",
    "                link.append(link_tag)\n",
    "                location.append(location_tag)\n",
    "                requirement.append(requirement_tag)\n",
    "                education.append(education_tag)\n",
    "                descirption.append(descirption_tag)\n",
    "                salary.append(salary_tag)\n",
    "                company.append(name)\n",
    "            except:\n",
    "                pass\n",
    "\n",
    "    print(name + '\\t' + '爬完')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    'company':      company,\n",
    "    'job_name':     job,\n",
    "    'location':     location,\n",
    "    'salary':       salary,\n",
    "    'requirement':  requirement,\n",
    "    'education':    education,\n",
    "    'descirption':  descirption,\n",
    "    'link':         link,\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "df.drop_duplicates(subset='link',inplace=True)   #link一樣，內容就會一樣，刪除重複的置頂急徵工作\n",
    "df.to_excel('./result/104_上市櫃高薪工作.xlsx', sheet_name= '上市櫃高薪工作')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "print(len(company))\n",
    "print(len(job))\n",
    "print(len(location))\n",
    "print(len(salary))\n",
    "print(len(requirement))\n",
    "print(len(education))\n",
    "print(len(descirption))\n",
    "print(len(link))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "end = time.time()\n",
    "print('完成共花費',round((end - start)/60,2),'分')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "winsound.Beep(300,500)\n",
    "winsound.Beep(360,500)\n",
    "winsound.Beep(500,500)\n",
    "winsound.PlaySound('alert', winsound.SND_ASYNC)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  },
  "orig_nbformat": 2,
  "vscode": {
   "interpreter": {
    "hash": "d542fb4b5932ef659bba1dba0712c6f5cfaa6da8306f5f43c301df680d9e5e5a"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}