Web Scraping for Machine Learning with SQL Database

I thought, how can we angle “Web Scraping for Machine Learning”, and I realized that Web Scraping should be essential to Data Scientists, Data Engineers and Machine Learning Engineers.

The Full Stack AI/ML Engineer toolkit needs to include web scraping, because it can improve predictions with new quality data. Machine Learning inherently requires data, and we would be most comfortable, if we have as much high quality data as possible. But what about when the data you need is not available as a dataset? What then? Do you just go and ask organizations and hope that they kindly will deliver it to you for free?

The answer is: you collect, label and store it yourself.

I made a GitHub repository for scraping the data. I encourage you to try it out and scrape some data yourself, and even trying to make some NLP or other Machine Learning project out of the scraped data.

In this article, we are going to web scrape Reddit – specifically, the /r/DataScience (and a little of /r/MachineLearning) subreddit. There will be no usage of the Reddit API, since we usually web scrape when an API is not available. Furthermore, you are going to learn to combine the knowledge of HTML, Python, Databases, SQL and datasets for Machine Learning. We are doing a small NLP sample project at last, but this is only to showcase that you can pickup the dataset and create a model providing predictions.

Table of Contents (Click To Scroll)

1. Web Scraping in Python - Beautiful Soup and Selenium
   • Be careful - warning
   • Benchmarking How Long It Takes To Scrape
   • Initializing Classes From The Project
   • How To Scrape Reddit URLs
   • Getting Data From URLs
   • Converting Data to Python Dict
   • Extracting Data From Python Dict
     • Scraping The Comments
2. Labelling Scraped Data
   • Naming Conventions
3. Storing Scraped Data
   • Designing SQL Tables
   • Inserting Into SQL Tables
   • Exporting From SQL Tables
4. Small Machine Learning Project on Exported Dataset

Web Scraping in Python With BeautifulSoup and Selenium

The first things we need to do is install BeautifulSoup and Selenium for scraping, but for accessing the whole project (i.e. also the Machine Learning part), we need more packages.

Selenium is basically good for content that changes due to Javascript, while BeautifulSoup is great at capturing static HTML from pages. Both the packages can be downloaded to your environment by a simple pip or conda command.

Install all the packages from the Github (linked at the start)

pip install -r requirements.txt

Alternatively, if you are using Google Colab, you can run the following to install the packages needed:

!pip install -r https://github.com/casperbh96/Web-Scraping-Reddit/raw/master/requirements.txt

Next, you need to download the chromedriver and place it in the core folder of the downloaded repository from GitHub.

Basic Warning of Web Scraping

Scrape responsibly, please! Reddit might update their website and invalidate the current approach of scraping the data from the website. If this is used in production, you would really want to setup an email / sms service, such that you get immediate notice when your web scraper fails.

This is for educational purposes only, please don’t misuse or do anything illegal with the code. It’s provided as-is, by the MIT license of the GitHub repository.

Benchmarking How Long It Takes To Scrape

Scraping takes time. Remember that you have to open each page, letting it load, then scraping the needed data. It can really be a tedious process – even figuring out where to start gathering the data can be hard, or even figuring out exactly what data you want.

There are 5 main steps for scraping reddit data:

1. Collecting links from a subreddit
2. Finding the ‘script’ tag from each link
3. Turning collected data from links into Python dictionaries
4. Getting the data from Python dictionaries
5. Scraping comment data

Especially step 2 and 5 will take you a long time, because they are the hardest to optimize.

An approximate benchmark for:

• Step 2: about 1 second for each post
• Step 5: $n/3$ seconds for each post, where $n$ is number of comments. Scraping 300 comments took about 100 seconds for me, but it varies with internet speed, CPU speed, etc

Initializing Classes From The Project

We are going to be using 4 files with one class each, a total of 4 classes, which I placed in the core folder.

Whenever you see SQL, SelScraper or BSS being called, it means we are calling a method from another class, e.g. BSS.get_title(). We are going to jump forward and over some lines of code, because about 1000 lines have been written (which is too much to explain here).

from core.selenium_scraper import SeleniumScraper
from core.soup_scraper import SoupScraper
from core.progress_bar import ProgressBar
from core.sql_access import SqlAccess

SQL = SqlAccess()
SelScraper = SeleniumScraper()
BSS = SoupScraper(reddit_home,
                  slash,
                  subreddit)

Scraping URLs From A Subreddit

We start off with scraping the actual URLs from a given subreddit, defined at the start of scraper.py. What happens is that we open a browser in headless mode (without opening a window, basically running in the background), and we use some Javascript to scroll the page, while collecting links to posts.

The next snippets are just running in a while loop until the variable scroll_n_times becomes zero

while scroll_n_times:
    # Scrolls browser to the bottom of the page
    self.driver.execute_script(
                "window.scrollTo(0, document.body.scrollHeight);")
    time.sleep(sleep_time)
    scroll_n_times -= 1

    elements = self.driver.find_elements_by_xpath(xpath)

    # Get the link from the href attribute
    self.links = [tag.get_attribute('href') for tag in elements]

The following is the Javascript to scroll a page to the bottom:

window.scrollTo(0, document.body.scrollHeight);

After that, we use xpath to find all tags in the HTML body, which really just returns all the links to us:

xpath = "//a[@data-click-id='body']"
elements = self.driver.find_elements_by_xpath(xpath)
self.links = [tag.get_attribute('href') for tag in elements]

try:
    # while loop code
finally:
    self.driver.quit()

progress = ProgressBar(len(urls))
for url in urls:
    progress.update()
    headers = {'User-Agent': 'Mozilla/5.0'}
    r = requests.get(url, headers=headers)

    soup = BeautifulSoup(r.text, 'html.parser')

    pure_html_data.append(r.text)
    pure_script_data.append(soup.find(id='data').text)

pure_dicts = []

print('Making Python dicts out of script data')

progress = ProgressBar(len(script_data))
for data in script_data:
    progress.update()

    first_index = data.index('{')
    last_index = data.rfind('}') + 1

    json_str = data[first_index:last_index]

    pure_dicts.append(json.loads(json_str))

return pure_dicts

progress = ProgressBar(len(links))
for i, current_data in enumerate(BSS.data):
    progress.update()

    BSS.get_url_id_and_url_title(BSS.urls[i],
                                 current_data, i)
    BSS.get_title()
    BSS.get_upvote_ratio()
    BSS.get_score()
    BSS.get_posted_time()
    BSS.get_author()
    BSS.get_flairs()
    BSS.get_num_gold()
    BSS.get_category()
    BSS.get_total_num_comments()
    BSS.get_links_from_post()
    BSS.get_main_link()
    BSS.get_text()
    BSS.get_comment_ids()

for i, comment_url in enumerate(comment_id_links_array):
    author = None
    text = None
    score = None
    comment_id = array_of_comment_ids[i]

    # Open the url, find the div with classes Comment.t1_comment_id
    r = requests.get(comment_url, headers=headers)
    soup = BeautifulSoup(r.text, 'html.parser')
    div = soup.select('div.Comment.t1_{0}'.format(comment_id))

    # If it found the div, let's extract the text from the comment
    if div is not None and len(div) > 0 :
        author = div[0].find_all('a')[0].get_text()
        spans = div[0].find_all("span")
        score = [spans[i].get_text() for i in range(len(spans)) if 'point' in spans[i].get_text()]

        html_and_text = div[0].find('div', attrs={'data-test-id' : 'comment'})
        if html_and_text is not None:
            text = html_and_text.get_text()

        if len(score) == 0:
            score = None
        else:
            score = score[0]

    # Make useable array for insertion
    array_of_comment_data.append([None,
                                  None,
                                  str(comment_id),
                                  str(score),
                                  array_of_depth[i],
                                  str(array_of_next[i]),
                                  str(array_of_prev[i]),
                                  str(author),
                                  str(text)])

return array_of_comment_data

I used an excellent tool for generating this database diagram, which I want to highlight – currently it’s free and it’s called https://dbdiagram.io/.

Play around with it, if you wish. This is not a paid endorsement of any sorts, just a shoutout to a great, free tool. Here is my code for the above diagram:

Table post as p {
  id int [pk]
  url varchar [not null]
  url_id varchar [not null]
  url_title varchar [not null]
  author varchar
  upvote_ratio uint8
  score int
  time_created datetime
  num_gold int
  num_comments int
  category varchar
  text varchar
  main_link varchar
  flairs int
}

Table link as l {
  id int [pk]
  post_id int
  link varchar [not null]
}

Table comment as c {
  id int [pk]
  post_id int
  comment_id varchar [not null]
  score varchar [not null]
  depth int [not null]
  next varchar
  previous varchar
  comment_author varchar
  text varchar
}

Ref: p.id < l.post_id
Ref: p.id < c.post_id

Actually Inserting The Data

The databases and tables will be automatically generated by some code which I setup to run automatically, so we will not cover that part here, but rather, the part where we insert the actual data.

The first step is creating and/or connecting to the database (which will either automatically generate the database and tables, and/or just connect to the existing database).

After that, we begin inserting data.

try:
    SQL.create_or_connect_db(erase_first=erase_db_first)
    # [0] = post, [1] = comment, [2] = link
    for i in range(len(BSS.post_data)):
        SQL.insert('post', data = BSS.post_data[i])
        SQL.insert('link', data = BSS.link_data[i])

        if scrape_comments:
            SQL.insert('comment', data = BSS.comment_data[i])
except Exception as ex:
    print(ex)
finally:
    SQL.save_changes()

Inserting the data happens in a for loop, as can be seen from the code snippet above. We specify the column and the data which we want to input.

For the next step, we need to get the number of columns in the table we are inserting into. From the number of columns, we have to create an array of question marks – we have one question mark separated with a comma, for each column. This is how data is inserted, by the SQL syntax.

Some data is input into a function which I called insert(), and the data variable is an array in the form of a row. Basically, we already concatenated all the data into an array and now we are ready to insert.

cols = c.execute(('''
                PRAGMA table_info({0})
                ''').format(table))

# Get the number of columns
num_cols = sum([1 for i in cols]) - 1

# Generate question marks for VALUES insertion
question_marks = self._question_mark_creator(num_cols)

if table == 'post':
    c.execute(('''INSERT INTO {0}
                  VALUES ({1})'''
              ).format(table, question_marks), data)

    self.last_post_id = c.lastrowid

elif (table == 'comment' or table == 'link') \
     and data != None and data != []:
    # setting post_id to the last post id, inserted in the post insert
    for table_data in data:
        table_data[1] = self.last_post_id
        c.execute(('''INSERT INTO {0}
                      VALUES ({1})'''
                  ).format(table, question_marks), table_data)

This wraps up scraping and inserting into a database, but how about…

Exporting From SQL Databases

For this project, I made three datasets, one of which I used for a Machine Learning project in the next section of this article.

1. A dataset with posts, comments and links data
2. A dataset with the post only
3. A dataset with comments only

For these three datasets, I made a Python file called make_dataset.py for creating the datasets and saving them using Pandas and some SQL query.

For the first dataset, we used a left join from the SQL syntax (which I won’t go into detail about), and it provides the dataset that we wish for. You do have to filter a lot of nulls if you want to use this dataset for anything, i.e. a lot of data cleaning, but once that’s done, you can use the whole dataset.

all_data = pd.read_sql_query("""
SELECT *
FROM post p 
LEFT JOIN comment c 
    ON p.id = c.post_id
LEFT JOIN link l
	ON p.id = l.post_id;
""", c)

all_data.to_csv('data/post_comment_link_data.csv', columns=all_data.columns, index=False)

For the second and third datasets, a simple select all from table SQL query was made to make the dataset. This needs no further explaining.

post = pd.read_sql_query("""
SELECT *
FROM post;
""", c)

comment = pd.read_sql_query("""
SELECT *
FROM comment;
""", c)

post.to_csv('data/post_data.csv', columns=post.columns, index=False)
comment.to_csv('data/comment_data.csv', columns=comment.columns, index=False)

Machine Learning Project Based On This Dataset

From the three generated datasets, I wanted to show you how to do a basic machine learning project.

The results are not amazing, but we are trying to classify the comment into four categories; exceptional**,** good**,** average and bad – all based on the upvotes on a comment.

Let’s start! Firstly, we import the functions and packages we need, along with the dataset, which is the comments table. But we only import the score (upvotes) and comment text from that dataset.

import copy
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.metrics import accuracy_score
from sklearn.feature_extraction.text import CountVectorizer
from sklearn.linear_model import LogisticRegression

pd.options.mode.chained_assignment = None
df = pd.read_csv('data/comment_data.csv', usecols=['score', 'text'])

The next thing we have to do is cleaning the dataset. Firstly, we start off with only getting the text by using some regular expression (regex). This removes any weird characters like \ | / & % etc.

The next thing we do is with regards to the score feature. The score feature is formatted as a string, and we just need the number from that string. But we also want the minus in front of the string, if some comment has been downvoted a lot. Another regex was used here to do this.

The next ugly thing from our Python script is a None as a string. We replace this string with an actual None in Python, such that we can run df.dropna().

The last thing we need to do is convert the score to a float, since that is required for later.

def prepare_data(df):
    # Remove everything except alphanumeric characters
    df.text = df.text.str.replace('[^a-zA-Z\s]', '')

    # Get only numbers, but allow minus in front
    df.score = df.score.str.extract('(^-?[0-9]*\S+)')

    # Remove rows with None as string
    df.score = df.score.replace('None', np.nan)

    # Remove all None
    df = df.dropna()

    # Convert score feature from string to float
    score = df.score.astype(float)
    df.score = copy.deepcopy(score)

    return df

df = prepare_data(df)

The next part is trying to define how our classification is going to work; so we have to adapt the data. An easy way is using percentiles (perhaps not an ideal way).

For this, we find the fiftieth, seventy-fifth and ninety-fifth quantile of the data and mark the data below the fiftieth quantile. We replace the score feature with this new feature.

def score_to_percentile(df):
    second = df.score.quantile(0.50) # Average
    third = df.score.quantile(0.75) # Good
    fourth = df.score.quantile(0.95) # exceptional

    new_score = []

    for i, row in enumerate(df.score):
        if row > fourth:
            new_score.append('exceptional')
        elif row > third:
            new_score.append('good')
        elif row > second:
            new_score.append('average')
        else:
            new_score.append('bad')

    df.score = new_score

    return df

df = score_to_percentile(df)

We need to split the data and tokenize the text, which we proceed to do in the following code snippet. There is really not much magic happening here, so let’s move on.

def df_split(df):
    y = df[['score']]
    X = df.drop(['score'], axis=1)

    content = [' ' + comment for comment in X.text.values]
    X = CountVectorizer().fit_transform(content).toarray()

    X_train, X_test, y_train, y_test = train_test_split(
            X, y, test_size=0.33, random_state=42)

    return X_train, X_test, y_train, y_test

X_train, X_test, y_train, y_test = df_split(df)

The last part of this machine learning project tutorial is making predictions and scoring the algorithm we choose to go with.

Logistic Regression was used, and perhaps we did not get the best score, but this is merely a boilerplate for future improvement and use.

All we do here is fit the logistic regression model to the training data, make a prediction and then score how well the model predicted.

lr = LogisticRegression(C=0.05, solver='lbfgs', multi_class='multinomial')
lr.fit(X_train, y_train)
pred = lr.predict(X_test)
score = accuracy_score(y_test, pred)

print ("Accuracy: {0}".format(score))

In our case, the predictions were not that great, as the accuracy turned out to be $0.59$.

Future works includes:

• Fine-tuning different algorithms
• Trying different metrics
• Better data cleaning

Originally published by Casper Hansen at https://mlfromscratch.com

#MachineLearning #SQL #Databases #Python