3
\$\begingroup\$

I'm trying to scrape arrival data from this website. My script takes extremely long time to scrape the data. Is there any way I can speed up the scraping process?

Here's my script:

import json
import pandas as pd
import copy
import requests
from bs4 import BeautifulSoup

def getBalanceofTrade():
    cookies = {
        '_ga': 'GA1.3.672481365.1630462756',
        'sc_is_visitor_unique': 'rx12421317.1631602001.FE09E031FA464FEEAD095B438DD92829.2.2.2.2.2.2.2.2.2',
        '_ga_41205092': 'GS1.1.1631601995.2.0.1631602551.0',
        'varient_csrf_cookie': 'c34f577f894740d92106453d293f69b4',
        'ci_session': 'q9qvice0p2dgnp5f50veghgrqeveavrm',
        '_gid': 'GA1.3.542540031.1635307774',
        '_gat_gtag_UA_155890962_1': '1',
        'f5avr0338662302aaaaaaaaaaaaaaaa_cspm_': 'DMPCJNDKGKPBLIDLCKDBLLDGAEPJPBKDHBPPMIDGKFDEMKGDHFNEBJBDOLAMKFJACFDCDGKEHKFEFIPPNNKAJEACAPLNMFADDKOEHBJBKJGGDMKBNHHFMJBOEGLPMEPA',}

    headers = {
        'Connection': 'keep-alive',
        'sec-ch-ua': '"Chromium";v="94", "Google Chrome";v="94", ";Not A Brand";v="99"',
        'Accept': 'application/json, text/javascript, */*; q=0.01',
        'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
        'X-Requested-With': 'XMLHttpRequest',
        'sec-ch-ua-mobile': '?0',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36',
        'sec-ch-ua-platform': '"Windows"',
        'Origin': 'https://satudata.kemendag.go.id',
        'Sec-Fetch-Site': 'same-origin',
        'Sec-Fetch-Mode': 'cors',
        'Sec-Fetch-Dest': 'empty',
        'Referer': 'https://satudata.kemendag.go.id/balance-of-trade-with-trade-partner-country',
        'Accept-Language': 'en-US,en;q=0.9',}
    
    base_params = {
        'varient_csrf_token': 'c34f577f894740d92106453d293f69b4',
        'category': 'country'}
    
    value_id = pd.read_excel('Value ID Countries.xlsx')
    value_dict = dict(zip(value_id['VALUE ID'], value_id['COUNTRY']))
    
    params_list = []
    
    for key, value in value_dict.items():
        new_params = copy.copy(base_params)
        new_params['region_id'] = key
        params_list.append(new_params)
    df_all = pd.DataFrame()
    
    for params in params_list:
        response = requests.post('https://satudata.kemendag.go.id/balance-query', headers=headers, cookies=cookies, data=params)
        json_data = json.loads(response.text)
        df1 = pd.json_normalize(json_data)
        #print(df1)
        table_list = df1['data']
        for table in table_list:
            df = pd.DataFrame.from_records(table)
            df_all = df_all.append(df, ignore_index=True)
            
    country = []
    for i in value_id['COUNTRY']:
        x = [i] * len(df)
        country.append(x)
        
    country_list = [item for s in country for item in s]
    
    df_all['Country'] = country_list
    
    df_new = df_all.iloc[:, 0:6]
    df_new1 = df_all.iloc[:, 8:9]
    df_new2 = df_all.iloc[:, 10:11]
    result = pd.concat([df_new, df_new1, df_new2], axis=1)
    
    URL = 'https://satudata.kemendag.go.id/balance-of-trade-with-trade-partner-country'
    page = requests.get(URL)
    soup = BeautifulSoup(page.content, 'html.parser')
    table =soup.find('table', id="table-balance")
    rows = table.find_all('th')
    cols = [item.text.strip() for item in rows]
     #column name uraian
    column_name = [cols[0]]
    column_name1 = cols[1:6]
    column_name2 = [cols[10]]
    tahun = column_name1 + column_name2
    final_column = column_name + column_name1 + column_name2 + ['Country']
    result.columns = final_column
    
    desc_1 = 3*(result['Uraian'][0],)
    desc_2 = 3*(result['Uraian'][3],)
    desc_3 = 3*(result['Uraian'][6],)
    desc_4 = 3*(result['Uraian'][9],)
    x = desc_1 + desc_2 + desc_3 + desc_4
    y = list(x)
    z = y * len(value_id['COUNTRY'])
    result = result.replace(['TOTAL PERDAGANGAN', 'EKSPOR', 'IMPOR', 'NERACA PERDAGANGAN'], ['Total', 'Total', 'Total', 'Total']) 
    df2 = pd.DataFrame(z, columns=['description'])
    
    result = result.rename(columns = {'Uraian': 'tipe'}, inplace = False)
    table = pd.concat ([df2, result], axis=1)
    
    df_table = table.melt(id_vars=['description', 'tipe', 'Country'], value_vars=tahun, var_name='year')
    
    return(df_table)

Excel file:

VALUE ID | COUNTRY
147         ADEN
137         AFGANISTAN
299         AFRIKA LAINNYA
...           ...
\$\endgroup\$
4
  • \$\begingroup\$ What is the reason that your output is in Pandas format? \$\endgroup\$
    – Reinderien
    Nov 4 at 13:44
  • \$\begingroup\$ Should we presume the Excel file has 195 countries in it? The first solution to speedup I'd try is to parallelize that for loop; use threads or asyncio to request 20 at a time or so. But without a bit more detail I can't run or mess with the code in a meaningful way to test this hypothesis. \$\endgroup\$
    – ggorlen
    Nov 4 at 14:45
  • 1
    \$\begingroup\$ @ggorlen it has 279 countries in it \$\endgroup\$
    – lockey
    Nov 5 at 3:52
  • 1
    \$\begingroup\$ when you click this website then right-click on that page then click view-source, on that page it contains the ids and the countries @ggorlen \$\endgroup\$
    – lockey
    Nov 5 at 3:54
4
\$\begingroup\$

It won't be practical to speed this process up since you're scraping a third-party website. Parallelizing requests and loading the server is ethically dubious so I think you don't have many options other than to be patient.

getBalanceofTrade should be get_balance_of_trade by PEP8.

This function mixes together far too many concerns:

  • session cookie & header construction (which is hard-coded when it should not be)
  • fetching and parsing two different pages
  • loading a country database
  • data cleaning
  • construction of a dataframe

Each of these should be separate. Your requests should use an actual Session object. If you first run a request to balance-of-trade-with-trade-partner-country before the actual data fetching, it can serve two purposes: initialize a fresh (not hard-coded) CSRF token, and fetch your country database. You then won't need an Excel file at all (which should not have been an Excel file - it should have been something more machine-readable like a CSV).

The country database get should use a strainer to only parse the portion of the HTML document containing region IDs. If you want to be fancy, you can also pre-compile your CSS selectors via soupsieve.

You should drop nearly all of your hard-coded headers and cookies and pass only

  • cookies given to you automatically via Session, and
  • an Accept header correctly communicating what content type you're expecting (even though the server is broken and returns the wrong content type for JSON).

You should not json.loads, and should instead call response.json(). The response should also be used in a context management with.

An example implementation that addresses some of these concerns:

from pprint import pprint
from typing import Iterator, Tuple, Any, Dict
from urllib.parse import urljoin

import soupsieve
from bs4 import SoupStrainer, BeautifulSoup, ResultSet
from requests import Session


BASE = 'https://satudata.kemendag.go.id'


COUNTRY_DROPDOWN = SoupStrainer(name='select', id='country')
OPTION_SIEVE = soupsieve.compile(pattern=':root > option:not([value=""])')


def get_regions(session: Session) -> Iterator[Tuple[str, int]]:
    with session.get(
        url=urljoin(BASE, 'balance-of-trade-with-trade-partner-country'),
        headers={'Accept': 'text/html'},
    ) as resp:
        resp.raise_for_status()
        doc = BeautifulSoup(
            markup=resp.text,
            features='html.parser', parse_only=COUNTRY_DROPDOWN,
        )

    for option in OPTION_SIEVE.select(doc):
        name = option.text.strip().title()
        value = int(option['value'])
        yield name, value


def query_balance(session: Session, region_id: int) -> Dict[str, Any]:
    with session.post(
        urljoin(BASE, 'balance-query'),
        headers={'Accept': 'application/json'},
        data={
            'region_id': region_id,
            'category': 'country',
            'varient_csrf_token': session.cookies['varient_csrf_cookie'],
        },
    ) as resp:
        resp.raise_for_status()
        return resp.json()


def main() -> None:
    with Session() as session:
        regions = dict(get_regions(session))
        data = query_balance(session, regions['Kanada'])  # 412
        pprint(data)


if __name__ == '__main__':
    main()

For the one country queried, this prints

{'country': 'KANADA',
 'data': [{'cumulative_1': '1,597,724.5',
           'cumulative_2': '2,049,001.3',
           'growth': '28.24',
           'period_1': '2,115,477.3',
           'period_2': '2,374,859.0',
           'period_3': '2,754,638.7',
           'period_4': '2,696,923.0',
           'period_5': '2,404,576.7',
           'trend': '3.91',
           'uraian': 'TOTAL PERDAGANGAN'},
          {'cumulative_1': '276.7',
           'cumulative_2': '528.8',
           'growth': '91.10',
           'period_1': '633.8',
           'period_2': '772.1',
           'period_3': '1,155.0',
           'period_4': '709.6',
           'period_5': '407.8',
           'trend': '-9.21',
           'uraian': 'MIGAS'},
          {'cumulative_1': '1,597,447.8',
           'cumulative_2': '2,048,472.5',
           'growth': '28.23',
           'period_1': '2,114,843.4',
           'period_2': '2,374,086.9',
           'period_3': '2,753,483.7',
           'period_4': '2,696,213.4',
           'period_5': '2,404,168.9',
           'trend': '3.91',
           'uraian': 'NON MIGAS'},
          {'cumulative_1': '497,670.5',
           'cumulative_2': '675,889.8',
           'growth': '35.81',
           'period_1': '732,447.3',
           'period_2': '821,233.1',
           'period_3': '913,889.2',
           'period_4': '858,206.0',
           'period_5': '789,116.4',
           'trend': '1.95',
           'uraian': 'EKSPOR'},
          {'cumulative_1': '24.4',
           'cumulative_2': 0,
           'growth': '0.00',
           'period_1': '121.4',
           'period_2': '100.0',
           'period_3': '123.1',
           'period_4': '86.3',
           'period_5': '24.4',
           'trend': '-28.52',
           'uraian': 'MIGAS'},
          {'cumulative_1': '497,646.1',
           'cumulative_2': '675,889.8',
           'growth': '35.82',
           'period_1': '732,326.0',
           'period_2': '821,133.1',
           'period_3': '913,766.1',
           'period_4': '858,119.7',
           'period_5': '789,092.0',
           'trend': '1.95',
           'uraian': 'NON MIGAS'},
          {'cumulative_1': '1,100,054.0',
           'cumulative_2': '1,373,111.5',
           'growth': '24.82',
           'period_1': '1,383,029.9',
           'period_2': '1,553,625.8',
           'period_3': '1,840,749.5',
           'period_4': '1,838,717.0',
           'period_5': '1,615,460.4',
           'trend': '4.91',
           'uraian': 'IMPOR'},
          {'cumulative_1': '252.3',
           'cumulative_2': '528.8',
           'growth': '109.57',
           'period_1': '512.5',
           'period_2': '672.1',
           'period_3': '1,031.9',
           'period_4': '623.3',
           'period_5': '383.5',
           'trend': '-6.34',
           'uraian': 'MIGAS'},
          {'cumulative_1': '1,099,801.6',
           'cumulative_2': '1,372,582.7',
           'growth': '24.80',
           'period_1': '1,382,517.4',
           'period_2': '1,552,953.8',
           'period_3': '1,839,717.6',
           'period_4': '1,838,093.7',
           'period_5': '1,615,076.9',
           'trend': '4.91',
           'uraian': 'NON MIGAS'},
          {'cumulative_1': '-602,383.5',
           'cumulative_2': '-697,221.7',
           'growth': '-15.74',
           'period_1': '-650,582.6',
           'period_2': '-732,392.7',
           'period_3': '-926,860.2',
           'period_4': '-980,511.0',
           'period_5': '-826,344.0',
           'trend': '-8.00',
           'uraian': 'NERACA PERDAGANGAN'},
          {'cumulative_1': '-228.0',
           'cumulative_2': '-528.8',
           'growth': '-131.99',
           'period_1': '-391.1',
           'period_2': '-572.0',
           'period_3': '-908.8',
           'period_4': '-537.0',
           'period_5': '-359.1',
           'trend': '2.31',
           'uraian': 'MIGAS'},
          {'cumulative_1': '-602,155.5',
           'cumulative_2': '-696,692.8',
           'growth': '-15.70',
           'period_1': '-650,191.5',
           'period_2': '-731,820.6',
           'period_3': '-925,951.5',
           'period_4': '-979,974.0',
           'period_5': '-825,984.9',
           'trend': '-8.01',
           'uraian': 'NON MIGAS'}],
 'period': '2016 - 2021'}
\$\endgroup\$
7
  • \$\begingroup\$ what if my expected output is dataframe of all the country data? @Reinderien \$\endgroup\$
    – lockey
    Nov 5 at 3:59
  • \$\begingroup\$ @lockey You never answered : why a dataframe? \$\endgroup\$
    – Reinderien
    Nov 5 at 11:59
  • \$\begingroup\$ because it will store to postgres base on that dataframe format \$\endgroup\$
    – lockey
    Nov 5 at 12:00
  • \$\begingroup\$ You're concerned about performance: a more performant solution will skip Pandas altogether, use a direct engine library like psycopg, and split off an asynchronous insert while the next web record is being fetched. \$\endgroup\$
    – Reinderien
    Nov 5 at 14:00
  • 3
    \$\begingroup\$ what if scraping all countries in one time? - If you don't own the website, don't do that. It's discourteous and can be construed as a DOS. \$\endgroup\$
    – Reinderien
    Nov 5 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.