351 lines
17 KiB
Plaintext
351 lines
17 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "a8479860",
|
|
"metadata": {},
|
|
"source": [
|
|
"# Scraping Harga Beras Medium BI\n",
|
|
"\n",
|
|
"Mengambil harga terbaru untuk **Beras Kualitas Medium I dan II** pada **Jawa Timur - Kab. Banyuwangi** dari situs PIHPS Bank Indonesia. ID komoditas dan wilayah diambil dari endpoint referensi agar tetap mengikuti perubahan data di situs."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 1,
|
|
"id": "c3db673e",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"<selenium.webdriver.remote.webelement.WebElement (session=\"8e127cd331be0c4b70b419cca364b1b3\", element=\"f.5733A7B02DBE6EF2537F2F51FB65F501.d.61B78F598BD215CED9E06D85340C50EB.e.55\")>"
|
|
]
|
|
},
|
|
"execution_count": 1,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"from datetime import date, datetime, timedelta\n",
|
|
"from pathlib import Path\n",
|
|
"import sqlite3\n",
|
|
"import time\n",
|
|
"\n",
|
|
"import pandas as pd\n",
|
|
"from IPython.display import display\n",
|
|
"from selenium import webdriver\n",
|
|
"from selenium.common.exceptions import TimeoutException\n",
|
|
"from selenium.webdriver.common.by import By\n",
|
|
"from selenium.webdriver.chrome.options import Options\n",
|
|
"from selenium.webdriver.support.ui import WebDriverWait\n",
|
|
"\n",
|
|
"BASE_URL = 'https://www.bi.go.id/hargapangan'\n",
|
|
"HALAMAN_URL = f'{BASE_URL}/TabelHarga/PasarTradisionalDaerah'\n",
|
|
"OUTPUT_FILE = Path('deploy_data.xlsx')\n",
|
|
"RESULT_DB_FILE = Path('data deploy hasil.sqlite')\n",
|
|
"RESULT_TABLE = 'data_deploy_hasil'\n",
|
|
"LEGACY_RESULT_FILE = Path('data deploy hasil.xlsx')\n",
|
|
"COMMODITIES = ('Beras Kualitas Medium I', 'Beras Kualitas Medium II')\n",
|
|
"PROVINCE = 'Jawa Timur'\n",
|
|
"REGENCY = 'Kab. Banyuwangi'\n",
|
|
"\n",
|
|
"options = Options()\n",
|
|
"options.add_argument('--headless=new')\n",
|
|
"options.add_argument('--window-size=1920,1080')\n",
|
|
"driver = webdriver.Chrome(options=options)\n",
|
|
"driver.get(HALAMAN_URL)\n",
|
|
"wait = WebDriverWait(driver, 30)\n",
|
|
"wait.until(lambda browser: browser.find_element(By.TAG_NAME, 'body'))"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 2,
|
|
"id": "adabf437",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"def click_tree_item(tree_id, item_name):\n",
|
|
" def find_checkbox(browser):\n",
|
|
" rows = browser.find_elements(By.CSS_SELECTOR, f'#{tree_id} tr.dx-data-row')\n",
|
|
" for row in rows:\n",
|
|
" if row.text.replace('\\u2003', ' ').strip() == item_name:\n",
|
|
" return row.find_element(By.CSS_SELECTOR, '.dx-select-checkbox')\n",
|
|
" return False\n",
|
|
"\n",
|
|
" checkbox = wait.until(find_checkbox)\n",
|
|
" if checkbox.get_attribute('aria-checked') != 'true':\n",
|
|
" driver.execute_script(\"arguments[0].scrollIntoView({block: 'center'});\", checkbox)\n",
|
|
" checkbox.click()\n",
|
|
" wait.until(lambda browser: checkbox.get_attribute('aria-checked') == 'true')\n",
|
|
"\n",
|
|
"\n",
|
|
"def set_date(date_box_id, value):\n",
|
|
" driver.execute_script(\n",
|
|
" \"window.jQuery(arguments[0]).dxDateBox('instance').option('value', arguments[1]);\",\n",
|
|
" f'#{date_box_id}',\n",
|
|
" value.isoformat(),\n",
|
|
" )\n",
|
|
"\n",
|
|
"\n",
|
|
"def configure_filters():\n",
|
|
" for commodity in COMMODITIES:\n",
|
|
" click_tree_item('CommodityTree', commodity)\n",
|
|
" click_tree_item('cboProvince', PROVINCE)\n",
|
|
" wait.until(lambda browser: browser.find_elements(By.CSS_SELECTOR, '#cboRegency tr.dx-data-row'))\n",
|
|
" click_tree_item('cboRegency', REGENCY)\n",
|
|
"\n",
|
|
"\n",
|
|
"def read_visible_prices():\n",
|
|
" header_row = next(\n",
|
|
" row for row in driver.find_elements(By.CSS_SELECTOR, '.dx-datagrid-headers tr.dx-header-row')\n",
|
|
" if any('/' in (cell.get_attribute('aria-label') or '') for cell in row.find_elements(By.CSS_SELECTOR, 'td[role=\"columnheader\"]'))\n",
|
|
" )\n",
|
|
" headers = [\n",
|
|
" (cell.get_attribute('aria-label') or '').replace('Column ', '').strip()\n",
|
|
" for cell in header_row.find_elements(By.CSS_SELECTOR, 'td[role=\"columnheader\"]')\n",
|
|
" if (cell.get_attribute('aria-label') or '').startswith('Column ')\n",
|
|
" ]\n",
|
|
"\n",
|
|
" tables = driver.find_elements(By.CSS_SELECTOR, '.dx-datagrid-rowsview table')\n",
|
|
" label_rows = []\n",
|
|
" value_rows = []\n",
|
|
" for table in tables:\n",
|
|
" rows = table.find_elements(By.CSS_SELECTOR, 'tbody tr.dx-data-row')\n",
|
|
" parsed_rows = [\n",
|
|
" [cell.text.replace('\\u2003', ' ').strip() for cell in row.find_elements(By.CSS_SELECTOR, 'td')]\n",
|
|
" for row in rows\n",
|
|
" ]\n",
|
|
" if any(any(commodity in ' '.join(row) for commodity in COMMODITIES) for row in parsed_rows):\n",
|
|
" label_rows = parsed_rows\n",
|
|
" elif parsed_rows:\n",
|
|
" value_rows = parsed_rows\n",
|
|
"\n",
|
|
" if not headers or not label_rows or not value_rows:\n",
|
|
" raise RuntimeError('Tabel harga belum menampilkan data.')\n",
|
|
"\n",
|
|
" records = []\n",
|
|
" for label_row, value_row in zip(label_rows, value_rows):\n",
|
|
" if len(label_row) < 2:\n",
|
|
" continue\n",
|
|
" commodity = label_row[1]\n",
|
|
" commodity = commodity.split(' ', 1)[1] if commodity[:1].isdigit() else commodity\n",
|
|
" if commodity not in COMMODITIES:\n",
|
|
" continue\n",
|
|
" for header, value in zip(headers[2:], value_row[2:]):\n",
|
|
" if value and value != '-':\n",
|
|
" records.append({'komoditas': commodity, 'tanggal': header, 'harga': value})\n",
|
|
" return pd.DataFrame(records)\n",
|
|
"\n",
|
|
"\n",
|
|
"def scrape_harga(tanggal_mulai, tanggal_selesai):\n",
|
|
" set_date('dboDateMulai', tanggal_mulai)\n",
|
|
" set_date('dboDateSelesai', tanggal_selesai)\n",
|
|
" driver.find_element(By.ID, 'btnReport').click()\n",
|
|
" wait.until(\n",
|
|
" lambda browser: browser.find_elements(\n",
|
|
" By.CSS_SELECTOR,\n",
|
|
" '.dx-datagrid-rowsview table tbody tr.dx-data-row',\n",
|
|
" )\n",
|
|
" )\n",
|
|
" time.sleep(1)\n",
|
|
" return read_visible_prices()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 3,
|
|
"id": "651fb356",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Filter Selenium siap: Beras Kualitas Medium I, Beras Kualitas Medium II | Jawa Timur | Kab. Banyuwangi\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"configure_filters()\n",
|
|
"print('Filter Selenium siap:', ', '.join(COMMODITIES), '|', PROVINCE, '|', REGENCY)"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 4,
|
|
"id": "f31d1817",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"ename": "RuntimeError",
|
|
"evalue": "Tabel harga belum menampilkan data.",
|
|
"output_type": "error",
|
|
"traceback": [
|
|
"\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
|
|
"\u001b[1;31mRuntimeError\u001b[0m Traceback (most recent call last)",
|
|
"Cell \u001b[1;32mIn[4], line 112\u001b[0m\n\u001b[0;32m 110\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m last_date \u001b[38;5;241m<\u001b[39m tanggal_terakhir_diizinkan:\n\u001b[0;32m 111\u001b[0m tanggal_mulai \u001b[38;5;241m=\u001b[39m last_date \u001b[38;5;241m+\u001b[39m timedelta(days\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m1\u001b[39m)\n\u001b[1;32m--> 112\u001b[0m hasil \u001b[38;5;241m=\u001b[39m \u001b[43mscrape_harga\u001b[49m\u001b[43m(\u001b[49m\u001b[43mtanggal_mulai\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mtanggal_terakhir_diizinkan\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m 113\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m hasil\u001b[38;5;241m.\u001b[39mempty:\n\u001b[0;32m 114\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mRuntimeError\u001b[39;00m(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mTidak ada harga pada rentang tanggal tersebut.\u001b[39m\u001b[38;5;124m'\u001b[39m)\n",
|
|
"Cell \u001b[1;32mIn[2], line 85\u001b[0m, in \u001b[0;36mscrape_harga\u001b[1;34m(tanggal_mulai, tanggal_selesai)\u001b[0m\n\u001b[0;32m 78\u001b[0m wait\u001b[38;5;241m.\u001b[39muntil(\n\u001b[0;32m 79\u001b[0m \u001b[38;5;28;01mlambda\u001b[39;00m browser: browser\u001b[38;5;241m.\u001b[39mfind_elements(\n\u001b[0;32m 80\u001b[0m By\u001b[38;5;241m.\u001b[39mCSS_SELECTOR,\n\u001b[0;32m 81\u001b[0m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m.dx-datagrid-rowsview table tbody tr.dx-data-row\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[0;32m 82\u001b[0m )\n\u001b[0;32m 83\u001b[0m )\n\u001b[0;32m 84\u001b[0m time\u001b[38;5;241m.\u001b[39msleep(\u001b[38;5;241m1\u001b[39m)\n\u001b[1;32m---> 85\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mread_visible_prices\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
|
|
"Cell \u001b[1;32mIn[2], line 58\u001b[0m, in \u001b[0;36mread_visible_prices\u001b[1;34m()\u001b[0m\n\u001b[0;32m 55\u001b[0m value_rows \u001b[38;5;241m=\u001b[39m parsed_rows\n\u001b[0;32m 57\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m headers \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m label_rows \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m value_rows:\n\u001b[1;32m---> 58\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mRuntimeError\u001b[39;00m(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mTabel harga belum menampilkan data.\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m 60\u001b[0m records \u001b[38;5;241m=\u001b[39m []\n\u001b[0;32m 61\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m label_row, value_row \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28mzip\u001b[39m(label_rows, value_rows):\n",
|
|
"\u001b[1;31mRuntimeError\u001b[0m: Tabel harga belum menampilkan data."
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"def parse_deploy_date(value):\n",
|
|
" return pd.to_datetime(str(value).replace(' ', ''), format='%d/%m/%Y', errors='coerce')\n",
|
|
"\n",
|
|
"\n",
|
|
"def format_price(value):\n",
|
|
" number = pd.to_numeric(str(value).replace(',', '').replace('Rp', '').strip(), errors='coerce')\n",
|
|
" if pd.isna(number):\n",
|
|
" return value\n",
|
|
" return f'{number:,.0f}'\n",
|
|
"\n",
|
|
"\n",
|
|
"def update_deploy_workbook(deploy, data):\n",
|
|
" deploy_labels = deploy['Komoditas (Rp)'].astype(str).str.strip()\n",
|
|
" updated_columns = []\n",
|
|
" for record in data.to_dict('records'):\n",
|
|
" commodity = record['komoditas'].strip()\n",
|
|
" date_column = record['tanggal'].strip()\n",
|
|
" row_index = deploy.index[deploy_labels.eq(commodity)]\n",
|
|
" if row_index.empty:\n",
|
|
" raise ValueError(f'Baris {commodity!r} tidak ditemukan di {OUTPUT_FILE}.')\n",
|
|
" deploy.loc[row_index[0], date_column] = format_price(record['harga'])\n",
|
|
" if date_column not in updated_columns:\n",
|
|
" updated_columns.append(date_column)\n",
|
|
" return updated_columns\n",
|
|
"\n",
|
|
"\n",
|
|
"def load_result_store():\n",
|
|
" if RESULT_DB_FILE.exists():\n",
|
|
" with sqlite3.connect(RESULT_DB_FILE) as connection:\n",
|
|
" result = pd.read_sql_query(\n",
|
|
" f'SELECT \"Tanggal\", \"Harga Medium 1\", \"Harga Medium 2\", \"rata2\" FROM {RESULT_TABLE}',\n",
|
|
" connection,\n",
|
|
" )\n",
|
|
" else:\n",
|
|
" if not LEGACY_RESULT_FILE.exists():\n",
|
|
" raise FileNotFoundError(f'Sumber migrasi tidak ditemukan: {LEGACY_RESULT_FILE}')\n",
|
|
" result = pd.read_excel(LEGACY_RESULT_FILE)\n",
|
|
" result['Tanggal'] = pd.to_datetime(result['Tanggal'], errors='coerce')\n",
|
|
" return result.dropna(subset=['Tanggal'])\n",
|
|
"\n",
|
|
"\n",
|
|
"def save_result_store(result):\n",
|
|
" stored = result.copy()\n",
|
|
" stored['Tanggal'] = pd.to_datetime(stored['Tanggal']).dt.strftime('%Y-%m-%d')\n",
|
|
" with sqlite3.connect(RESULT_DB_FILE) as connection:\n",
|
|
" stored.to_sql(RESULT_TABLE, connection, if_exists='replace', index=False)\n",
|
|
"\n",
|
|
"\n",
|
|
"def reconstruct_deploy_result(scraped_data):\n",
|
|
" expected_columns = ['Tanggal', 'Harga Medium 1', 'Harga Medium 2', 'rata2']\n",
|
|
" existing = load_result_store()[expected_columns].set_index('Tanggal')\n",
|
|
"\n",
|
|
" if scraped_data.empty:\n",
|
|
" combined = existing\n",
|
|
" else:\n",
|
|
" scraped = scraped_data.copy()\n",
|
|
" scraped['Tanggal'] = pd.to_datetime(\n",
|
|
" scraped['tanggal'].astype(str).str.replace(' ', '', regex=False),\n",
|
|
" format='%d/%m/%Y',\n",
|
|
" errors='coerce',\n",
|
|
" )\n",
|
|
" scraped['harga'] = pd.to_numeric(\n",
|
|
" scraped['harga'].astype(str).str.replace(',', '', regex=False).str.replace('Rp', '', regex=False),\n",
|
|
" errors='coerce',\n",
|
|
" )\n",
|
|
" scraped = scraped.dropna(subset=['Tanggal', 'harga'])\n",
|
|
" new_values = scraped.pivot_table(\n",
|
|
" index='Tanggal',\n",
|
|
" columns='komoditas',\n",
|
|
" values='harga',\n",
|
|
" aggfunc='last',\n",
|
|
" ).rename(columns={\n",
|
|
" 'Beras Kualitas Medium I': 'Harga Medium 1',\n",
|
|
" 'Beras Kualitas Medium II': 'Harga Medium 2',\n",
|
|
" })\n",
|
|
" new_values = new_values.reindex(columns=['Harga Medium 1', 'Harga Medium 2'])\n",
|
|
" combined = pd.concat([\n",
|
|
" existing.drop(index=new_values.index, errors='ignore'),\n",
|
|
" new_values,\n",
|
|
" ], axis=0)\n",
|
|
"\n",
|
|
" combined = combined.sort_index()\n",
|
|
" full_dates = pd.date_range(combined.index.min(), combined.index.max(), freq='D')\n",
|
|
" combined = combined.reindex(full_dates)\n",
|
|
" combined.index.name = 'Tanggal'\n",
|
|
" price_columns = ['Harga Medium 1', 'Harga Medium 2']\n",
|
|
" combined[price_columns] = combined[price_columns].interpolate(method='linear', limit_direction='both')\n",
|
|
" combined['rata2'] = combined[price_columns].mean(axis=1)\n",
|
|
" result = combined.reset_index()[expected_columns]\n",
|
|
" save_result_store(result)\n",
|
|
" return result\n",
|
|
"\n",
|
|
"\n",
|
|
"deploy = pd.read_excel(OUTPUT_FILE)\n",
|
|
"date_columns = [column for column in deploy.columns if pd.notna(parse_deploy_date(column))]\n",
|
|
"if not date_columns:\n",
|
|
" raise ValueError(f'Tidak ada kolom tanggal yang ditemukan di {OUTPUT_FILE}.')\n",
|
|
"\n",
|
|
"last_date = max(parse_deploy_date(column).date() for column in date_columns)\n",
|
|
"tanggal_sekarang = date.today()\n",
|
|
"tanggal_terakhir_diizinkan = (\n",
|
|
" tanggal_sekarang\n",
|
|
" if datetime.now().hour >= 13\n",
|
|
" else tanggal_sekarang - timedelta(days=1)\n",
|
|
")\n",
|
|
"hasil = pd.DataFrame(columns=['komoditas', 'tanggal', 'harga'])\n",
|
|
"tanggal_diisi = []\n",
|
|
"\n",
|
|
"try:\n",
|
|
" if last_date < tanggal_terakhir_diizinkan:\n",
|
|
" tanggal_mulai = last_date + timedelta(days=1)\n",
|
|
" hasil = scrape_harga(tanggal_mulai, tanggal_terakhir_diizinkan)\n",
|
|
" if hasil.empty:\n",
|
|
" raise RuntimeError('Tidak ada harga pada rentang tanggal tersebut.')\n",
|
|
" tanggal_diisi = update_deploy_workbook(deploy, hasil)\n",
|
|
" print(f'{tanggal_mulai} sampai {tanggal_terakhir_diizinkan}: {len(hasil)} harga diterima.')\n",
|
|
" else:\n",
|
|
" print(f'Excel sudah terisi sampai batas data: {tanggal_terakhir_diizinkan}.')\n",
|
|
"finally:\n",
|
|
" driver.quit()\n",
|
|
"\n",
|
|
"deploy.to_excel(OUTPUT_FILE, index=False)\n",
|
|
"result = reconstruct_deploy_result(hasil)\n",
|
|
"print(f'Workbook wide diperbarui: {OUTPUT_FILE.resolve()}')\n",
|
|
"print(f'Data hasil scraping disimpan ke SQLite: {RESULT_DB_FILE.resolve()}')\n",
|
|
"print(f'Tabel SQLite: {RESULT_TABLE}')\n",
|
|
"print(f'Tanggal baru/diperbarui: {len(tanggal_diisi)}')\n",
|
|
"print(f'Ukuran data deploy hasil: {result.shape[0]} baris')\n",
|
|
"display(result.tail())"
|
|
]
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "Python 3",
|
|
"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.6"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|