<jupyter_start><jupyter_text>DatasetThe dataset used here seems to have some duplicates (see `unique` vs. `count`), and the dataset shows the same UEN with varying entity names - probably because company has been renamed, can confirm this through www.bizfile.gov.sg.<jupyter_code>df.describe()
df<jupyter_output><empty_output><jupyter_text>So let's remove the duplicates, and only take the last item of entities with duplicate entries...<jupyter_code>df = df.drop_duplicates(subset="Entity Profile UEN", keep="last")
df
df.to_csv("{}-filtered.csv".format(input_file), index=False) # re-write to new file, don't include dataframe's index<jupyter_output><empty_output><jupyter_text>Part 1We first begin by crawling for the website addresses from existing data sources.<jupyter_code>import requests
import bs4
import re
import time
import random
import os
import unittest
import json
from urllib.parse import urlparse, parse_qs
from dotenv import load_dotenv
input_file = "20210311_v0.2-filtered" # no need for file extension, will be used later for file output
df = pd.read_csv("{}.csv".format(input_file))
load_dotenv()

PROXIES = os.getenv("PROXIES")

if not PROXIES:
    print("No proxies found, please enter them in CSV format in .env")
url = "https://httpbin.org/ip"

response = requests.get(url)
my_ip = response.json()['origin']

proxies = {}

proxies_string = PROXIES.split(",") # split our proxies into array e.g. ["proxy1.com", "proxy2.com"]

for i, p in enumerate(proxies_string):
    proxies[i] = {
        "http": p,
        "https": p
    }

proxy_count = len(proxies)
# Checking if proxies are ok
# for i in range(proxy_count):
#     try:
#         response = requests.get(url, proxies=proxies[i])
#         ip = response.json()['origin']
#         # print(ip)
#         # if ip is not my_ip:
#     except Exception as e:
#         print("Proxy {} is down, error={}".format(i, e))
def get_random_user_agent():
    ua_strings = [
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.82 Safari/537.36",
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36",
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.90 Safari/537.36"
        "Mozilla/5.0 (iPhone; CPU iPhone OS 12_2 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148",
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/11.1.2 Safari/605.1.15",
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 11_2_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.90 Safari/537.36",
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1 Safari/605.1.15",
        "Mozilla/5.0 (iPad; CPU OS 14_4 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) CriOS/87.0.4280.77 Mobile/15E148 Safari/604.1",
        "Mozilla/5.0 (Linux; Android 10; SM-A205U) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.90 Mobile Safari/537.36",
        "Mozilla/5.0 (Linux; Android 10; SM-N960U) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.90 Mobile Safari/537.36"
    ]
 
    return random.choice(ua_strings)
def get_entity_url_slug(entity_name):
    entity_url_slug = re.sub("[^a-zA-Z0-9 ]", "", entity_name.lower())
    entity_url_slug = re.sub(" +", " ", entity_url_slug).replace(" ", "-")
    return entity_url_slug

class TestEntityUrlSlug(unittest.TestCase):
    
    def test_brackets(self):
        self.assertEqual(get_entity_url_slug("POLY GLOBAL COMMERCIAL (AU) HOLDINGS PTE. LTD."), "poly-global-commercial-au-holdings-pte-ltd")
    
    def test_brackets_2(self):
        self.assertEqual(get_entity_url_slug("ES SOLUTIONS (S) PTE. LTD."), "es-solutions-s-pte-ltd")
    
    def test_brackets_3(self):
        self.assertEqual(get_entity_url_slug("JEM(S) WATERPLUS PTE. LTD."), "jems-waterplus-pte-ltd")
        
    def test_special_characters(self):
        self.assertEqual(get_entity_url_slug("G+ PTE. LTD."), "g-pte-ltd")
        
    def test_period_in_entity_name(self):
        self.assertEqual(get_entity_url_slug("IK.SG PTE. LTD."), "iksg-pte-ltd")
        
    def test_period_in_entity_name_2(self):
        self.assertEqual(get_entity_url_slug("JIUZHANG TECHNOLOGIES (S.E.A.) PTE. LTD."), "jiuzhang-technologies-sea-pte-ltd")
    
    def test_dashes_and_ampersands(self):
        self.assertEqual(get_entity_url_slug("PRO-FIN CONSULT & COMPANY PTE. LTD."), "profin-consult-company-pte-ltd")
        
    def test_pte_limited(self):
        self.assertEqual(get_entity_url_slug("ASIA OPPORTUNITIES (SINGAPORE) PTE. LIMITED"), "asia-opportunities-singapore-pte-limited")
    
    def test_brackets_2(self):
        self.assertEqual(get_entity_url_slug("ES SOLUTIONS (S) PTE. LTD."), "es-solutions-s-pte-ltd")
    
        
unittest.main(argv=[''], verbosity=2, exit=False)
# Target URL to scrape

url = "https://sgpgrid.com/company-details/"
output_file = "{}-result.csv".format(input_file)
size = len(df) # Size of dataset

start = 0
end = 1 # size

max_tries = 1

print("Beginning job with id={} of size={}".format(start, size))

for i in range(start, end):
    entity = df.loc[i] # current row record
    entity_uen = entity[1] # entity UEN
    entity_name = entity[2] # entity name
    
    # Prepare the CSV to write out
    formatted_csv_row = "\"{}\",\"{}\",\"{}\",\"{}\"".format(
        entity[0],
        entity[1],
        entity[2],
        entity[3]
    )

    # Accessing the webpage
    print("Searching for id={} {} {}".format(i, entity_uen, entity_name))
    
    for j in range(max_tries):
        # Pick a random rotating proxy
#         id = random.randint(0, proxy_count)
        entity_url_slug = get_entity_url_slug(entity_name)
        
        try:
            random_stuff = "?ra={}&utm_source=facebook&utm_medium=facebook&utm_campaign=".format(str(random.random()))
            url_new = url + entity_url_slug + random_stuff

            # Setup User Agent headers, attempt to imitate a "browser-like" request to the webpage
            headers = requests.utils.default_headers()
            headers.update({
                "User-Agent": get_random_user_agent(),
                "Referer": "http://3.0.205.74/search-results",
                "api-key": "<KEY>"
            })
            
            # Now, we query the target URL using a random proxy
            #resp = requests.get(url_new, data=payload, headers=headers, proxies=proxies[id])
            resp = requests.get(url_new, headers=headers)
            
            # We know that Next.js's __NEXT_DATA__ holds the info we need...
            soup = bs4.BeautifulSoup(resp.text, "html.parser")
            
            # Now look for the specific elements
            next_data = soup.find_all("script", {"id": "__NEXT_DATA__"})
            print(next_data)
            
            # Then append SGPGrid's data...
            formatted_csv_row += ",\"{}\",\"{}\",\"{}\"".format(description, website, ssic)

            # Finally we write out to file by appending
            f = open(output_file, "a")
            f.write(formatted_csv_row + "\n")
            f.close()
            
            print("    [SUCCESS] Found company info for id={} on retry={}".format(i, j), description, website, ssic)
    
            break # next for loop
        except Exception as e:
            print("    [ERROR] Failed at id={} retry={} error={} using proxy id={}, retrying...".format(i, j, e, id))
            
            if j == max_tries - 1:
                f = open("{}-failed.csv".format(input_file), "a")
                f.write(formatted_csv_row + "\n")
                f.close()
            continue # retry

    time.sleep(random.randint(1, 8)) # Randomise the waiting time<jupyter_output>Beginning job with id=0 of size=23231
Searching for id=0 53407676M GRAVITY FILM
[<script id="__NEXT_DATA__" type="application/json">{"props":{"isServer":true,"initialState":{"seo":{"title":"Too Many Requests","description":"","robots":"noindex, nofollow","canonicalHref":"https://sgpgrid.com/too-many"},"user":{"isAuth":false,"logouted":false,"profileChanged":false,"id":null,"email":"","expiryDate":"","contributor":false,"verifyErr":"","requestCount":0,"companyResult":[],"claimedCompanies":[],"savingResult":"","savingResultError":false,"userData":{},"isPremium":false,"subscriptionType":"","referralHash":"","usedTrial":false,"profileFirstName":"","profileLastName":"","profileJobTitle":"","profileCompany":"","firstName":"","lastName":"","company":"","jobTitle":"","credit":0},"login":{"email":"","errEmail":"","password":"","errPassword":""},"signup":{"isFinished":false,"isResended":false,"isPremium":false,"isClaim":false,"claimErr":"","firstName":"","firstNameErr":"","lastName":"","lastName[...]<jupyter_text>Old CodesThe following code attempts to:- crawl Google Search for the first 10 results- then it grabs the URLs so that we can crawl them again for the metadata The following code scrapes Google Search for the first 10 search results.<jupyter_code># # Target URL to scrape

# url = "https://www.google.com/search?q={}&sourceid=chrome&ie=UTF-8"
# output_file = "{}-result.csv".format(input_file)
# size = len(df) # Size of dataset

# max_tries = 15

# for i in range(0, size):
#     entity = df.loc[i] # current row record
#     entity_uen = entity[1] # entity UEN
#     entity_name = entity[2] # entity name
    
#     # Randomise search terms
#     search_terms = [
#         "{} singapore website".format(entity_name),
#         "{} sg website".format(entity_name)
#     ]
    
#     search_terms_count = len(search_terms)
    
#     # Prepare the CSV to write out
#     formatted_csv_row = "\"{}\",\"{}\",\"{}\",\"{}\"".format(
#         entity[0],
#         entity[1],
#         entity[2],
#         entity[3]
#     )

#     # Accessing the webpage
#     print("Searching for id={} {} {}".format(i, entity_uen, entity_name))
    
#     for j in range(max_tries):
#         try:
#             id = random.randint(0, search_terms_count)
#             search_term = search_terms[id]
#             url_new = url.format(search_term)
            
#             # Setup User Agent headers, attempt to imitate a "browser-like" request to the webpage
#             headers = requests.utils.default_headers()
#             headers.update({
#                 'User-Agent': get_random_user_agent()
#             })

#             # Pick a random rotating proxy
#             id = random.randint(0, proxy_count)
            
#             # Now, we query the target URL using a random proxy
#             resp = requests.get(url_new, headers=headers, proxies=proxies[id])
            
#             # Use BeautifulSoup to parse the HTML
#             soup = bs4.BeautifulSoup(resp.text, "html.parser")
#             print(soup)
            
#             # Now look for the specific elements
#             headers = soup.find_all("h3")
#             links = soup.find_all(href=re.compile(r'\/url\?q=')) # pick top 10 search results & its link
            
#             for i in range(10):
#                 google_url = links[i].get("href").strip()
#                 parsed_url = parse_qs(google_url)
#                 formatted_csv_row += ",{}".format(parsed_url["/url?q"][0])
            
#             # Finally we write out to file by appending
#             f = open(output_file, "a")
#             f.write(formatted_csv_row + "\n")
#             f.close()
            
#             print("    [SUCCESS] Wrote to file for id={} on retry={}".format(i, j))
    
#             break # next for loop
#         except Exception as e:
#             print("    [ERROR] Failed at id={} retry={} error={}, retrying...".format(i, j, e))
            
#             if j == max_tries - 1:
#                 f = open("{}-failed.csv".format(input_file), "a")
#                 f.write(formatted_csv_row)
#                 f.close()
#             continue # retry

#     time.sleep(random.randint(1, 3)) # Randomise the waiting time<jupyter_output><empty_output><jupyter_text>The following code scrapes SGPGrid's search results page.<jupyter_code># # Target URL to scrape

# url = "http://3.0.205.74/search-results?target={\"value\":\"Registration Number\",\"label\":\"Registration Number\",\"searchTarget\":\"registrationNumber\"}&value="
# output_file = "{}-result.csv".format(input_file)
# size = len(df) # Size of dataset

# start = 70
# print("Beginning job with id={} of size={}".format(start, size))

# max_tries = 15

# for i in range(start, size):
#     entity = df.loc[i] # current row record
#     entity_uen = entity[1] # entity UEN
#     entity_name = entity[2] # entity name
    
#     # Prepare the CSV to write out
#     formatted_csv_row = "\"{}\",\"{}\",\"{}\",\"{}\"".format(
#         entity[0],
#         entity[1],
#         entity[2],
#         entity[3]
#     )

#     # Accessing the webpage
#     print("Searching for id={} {} {}".format(i, entity_uen, entity_name))
    
#     for j in range(max_tries):
#         # Pick a random rotating proxy
#         id = random.randint(0, proxy_count)
        
#         try:
#             random_stuff = "&ra={}&utm_source=facebook&utm_medium=facebook&utm_campaign=".format(str(random.random()))
#             url_new = url + entity_uen + random_stuff

#             # Setup User Agent headers, attempt to imitate a "browser-like" request to the webpage
#             headers = requests.utils.default_headers()
#             headers.update({
#                 "User-Agent": get_random_user_agent()
#             })
            
#             # Now, we query the target URL using a random proxy
#             resp = requests.get(url_new, headers=headers, proxies=proxies[id])
            
#             # Use BeautifulSoup to parse the HTML
#             soup = bs4.BeautifulSoup(resp.text, "html.parser")
#             # print(soup)
            
#             # Now look for the specific elements
#             columns = soup.find_all("div", {"class": "rt-td table-cell"})
            
#             description = columns[0].get_text()
#             website = columns[3].get_text()
#             ssic = columns[5].get_text()
            
#             # Then append SGPGrid's data...
#             formatted_csv_row += ",\"{}\",\"{}\",\"{}\"".format(description, website, ssic)

#             # Finally we write out to file by appending
#             f = open(output_file, "a")
#             f.write(formatted_csv_row + "\n")
#             f.close()
            
#             print("    [SUCCESS] Found company info for id={} on retry={}".format(i, j), description, website, ssic)
    
#             break # next for loop
#         except Exception as e:
#             print("    [ERROR] Failed at id={} retry={} error={} using proxy id={}, retrying...".format(i, j, e, id))
            
#             if j == max_tries - 1:
#                 f = open("{}-failed.csv".format(input_file), "a")
#                 f.write(formatted_csv_row + "\n")
#                 f.close()
#             continue # retry

#     time.sleep(random.randint(1, 8)) # Randomise the waiting time<jupyter_output><empty_output><jupyter_text>The following code scrapes the API data.<jupyter_code># Target URL to scrape

url = "https://sgpgrid.com/api/search/filters"
output_file = "{}-result.csv".format(input_file)
size = len(df) # Size of dataset

start = 0
end = 1 # size

max_tries = 15

print("Beginning job with id={} of size={}".format(start, size))

for i in range(start, end):
    entity = df.loc[i] # current row record
    entity_uen = entity[1] # entity UEN
    entity_name = entity[2] # entity name
    
    # Prepare the CSV to write out
    formatted_csv_row = "\"{}\",\"{}\",\"{}\",\"{}\"".format(
        entity[0],
        entity[1],
        entity[2],
        entity[3]
    )

    # Accessing the webpage
    print("Searching for id={} {} {}".format(i, entity_uen, entity_name))
    
    for j in range(max_tries):
        # Pick a random rotating proxy
        #id = random.randint(0, proxy_count)
        
        try:
            random_stuff = "?ra={}&utm_source=facebook&utm_medium=facebook&utm_campaign=".format(str(random.random()))
            url_new = url + random_stuff

            # Setup User Agent headers, attempt to imitate a "browser-like" request to the webpage
            headers = requests.utils.default_headers()
            headers.update({
                "User-Agent": get_random_user_agent(),
                "Referer": "https://sgpgrid.com/search-results",
                "api-key": "<KEY>"
            })
            
            # Prepare the payload...
            payload = {
                "email": "",
                "limit": 1,
                "offset": 0,
                "order": {},
                "registrationNumber": entity_uen
            }
            
            # Now, we query the target URL using a random proxy
            #resp = requests.post(url_new, data=payload, headers=headers, proxies=proxies[id])
#             resp = requests.post(url_new, data=payload, headers=headers)
            
#             sgpgrid = resp.json()

            company_info = {"id":179931,"logo":"https://s3.ap-southeast-1.amazonaws.com/sgpgrid.company-edit/a1f9dee6-521c-4e50-8f60-3d43d6c563c61599205054766.png","urlName":"ifast-corporation-ltd","fullName":"IFAST CORPORATION LTD.","businessActivity":"iFAST Corporation is a leading wealth management fintech platform, with assets under administration of approximately S$11.15 billion as at 30 June 2020. Incorporated in the year 2000 in Singapore, iFAST Corp provides a comprehensive range of investment products and services to financial advisory firms, financial institutions, banks, multinational companies, as well as retail and high net worth investors in Asia. \n\nThe Group offers access to over 11,000 investment products including unit trusts, bonds and Singapore Government Securities, stocks and exchange traded funds, and insurance products; while services offered include online discretionary portfolio management services, research and investment seminars, IT solutions, and investment administration and transaction services.","primaryDescribedActivity":"SOFTWARE DEVELOPMENT","addressBlock":"10","addressStreet":"COLLYER QUAY","addressLevel":"26","addressUnit":"01","addressBuilding":"OCEAN FINANCIAL CENTRE","postalCode":"049315","registrationNumber":"200007899C","registrationIncorporationDate":"2000-09-11T12:00:00.000Z","primarySsicDescription":"DEVELOPMENT OF OTHER SOFTWARE AND PROGRAMMING ACTIVITIES N.E.C.","shareCapDelta":-68690822.17,"contactNum":"+65 6535 8033","email":"<EMAIL>","url":"http://www.ifastcorp.com/","numberOfStaff":"501-1,000","tags":"Large Enterprise","ratingGoogle":0,"mpIndex":"undefined(undefined)"}
            company_info['businessActivity'] = company_info['businessActivity'].replace("\\n", " ")
            print(company_info)
            df_tmp = pd.DataFrame(company_info, index=[0])
            df_tmp.to_csv(output_file, mode="a", header=False, index=False, escapechar="\\", quoting=csv.QUOTE_ALL)
            print(df_tmp)
    #             if "success" in sgpgrid and sgpgrid['success']:
#                 # We filtered to show only 1 company at a time, can safely access the first row
#                 company_info = sgpgrid['companies']['rows'][0]
                
#                 df_tmp = pd.DataFrame.from_dict(company_info)
#                 print(df_tmp)
                
#                 df_tmp.to_csv(output_file, mode="a", header=False)
                
#             else:
#                 raise Exception("SGPGrid's API success flag is false, most likely got blocked")
           
            
#             # Then append SGPGrid's data...
#             formatted_csv_row += ",\"{}\",\"{}\",\"{}\"".format(description, website, ssic)

#             # Finally we write out to file by appending
#             f = open(output_file, "a")
#             f.write(formatted_csv_row + "\n")
#             f.close()
            
#             print("    [SUCCESS] Found company info for id={} on retry={}".format(i, j), description, website, ssic)
    
            break # next for loop
        except Exception as e:
            print("    [ERROR] Failed at id={} retry={} error={} using proxy id={}, retrying...".format(i, j, e, id))
            
            if j == max_tries - 1:
                f = open("{}-failed.csv".format(input_file), "a")
                f.write(formatted_csv_row + "\n")
                f.close()
            continue # retry

    time.sleep(random.randint(1, 8)) # Randomise the waiting time<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14334602593145998, 'fraction_numerical': 0.02443726803875259, 'mean_word_length': 2.612116991643454, 'pattern_counts': {'":': 92, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 7, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '625739', 'license': 'Apache-2.0', 'n_tokens_mistral': 6903, 'n_tokens_neox': 6446, 'n_words': 1532, 'path': 'Finder.ipynb', 'repo_name': 'gjj/finder', 'size': '20885'}