<jupyter_start><jupyter_text>Word CountsWe pull out word counts using a SpaCy tokenizer (smarter than just separating by space) and write out into a SQLite3 (relational) database. The database table has columns to support a few more things down the line, but this notebook is only for word counts.<jupyter_code>import matplotlib.pyplot as plt
import nltk
import numpy as np
import os
import re
import spacy
import sqlite3
import string

from nltk.corpus import stopwords

%matplotlib inline
DATA_DIR = "../data/"
TEXTFILES_DIR = os.path.join(DATA_DIR, "textfiles")

TOKENS_FILE = os.path.join(DATA_DIR, "tokens.tsv")
WORDCOUNT_DB = os.path.join(DATA_DIR, "wordcounts.db")<jupyter_output><empty_output><jupyter_text>Tokenize text and dump into flat fileTokenizing the text with SpaCy is a slower process than simply splitting on whitespace, but hopefully the accuracy is worth the time taken. We will pretend that this step is already done, and use the flat file tokens.tsv that we downloaded instead.<jupyter_code>nlp = spacy.load("en")
def read_content(filename):
    lines = []
    with open(filename, "r") as f:
        for line in f:
            line = line.strip()
            if len(line) == 0:
                continue
            lines.append(line)
    lines = [line+"." if not line.endswith(".") else line for line in lines]
    return " ".join(lines)

if not os.path.exists(TOKENS_FILE):
    # LONG PROCESS! only run if not run before, ie, if no entries in DB table
    fout = open(TOKENS_FILE, "w")
    textfiles = os.listdir(TEXTFILES_DIR)
    total_docs = len(textfiles)
    num_words, num_docs = 0, 0
    for textfile in textfiles:
        if num_docs % 500 == 0:
            print("{:d}/{:d} documents processed, extracted {:d} tokens"
                 .format(num_docs, total_docs, num_words))
        doc_id = int(textfile.split(".")[0])
        filename = os.path.join(TEXTFILES_DIR, textfile)
        text = read_content(filename)
        doc = nlp(text)
        for token in doc:
            fout.write("{:d}\t{:s}\t{:d}\n".format(num_words, token.text.lower(), doc_id))
            num_words += 1
        num_docs += 1
    print("{:d}/{:d} documents processed, extracted {:d} tokens, COMPLETE"
          .format(num_docs, total_docs, num_words))
    fout.close()<jupyter_output><empty_output><jupyter_text>Loading to Database tableWe use a lightweight RDBMS (SQLite3) to offload some of the memory load (for example, if we were trying to build an in-memory dictionary of words to counts). Specifically, we just insert all the raw data we downloaded to file.We then create some indexes to speed up access.<jupyter_code>def table_exists(conn, table_name):
    cur = conn.cursor()
    cur.execute("select name from sqlite_master where type='table' and name = ?", 
                [table_name])
    rows = cur.fetchall()
    cur.close()
    return len(rows) > 0


def create_wordcounts_table(conn):
    if not table_exists(conn, "wordcounts"):
        cur = conn.cursor()
        create_table = """create table wordcounts(
            id INTEGER NOT NULL,
            word VARCHAR(50) NOT NULL, 
            doc_id INTEGER NOT NULL)
        """
        cur.execute(create_table)
        cur.close()


def index_exists(conn, index_name):
    cur = conn.cursor()
    cur.execute("select name from sqlite_master where type='index' and name = ?", 
                [index_name])
    rows = cur.fetchall()
    cur.close()
    return len(rows) > 0


def create_wordcounts_indexes(conn):
    cur = conn.cursor()
    index_names = ["ix_wordcounts", "ax1_wordcounts", "ax2_wordcounts"]
    create_indexes = [
        "create unique index ix_wordcounts on wordcounts(id)",
        "create index ax1_wordcounts on wordcounts(word)",
        "create index ax2_wordcounts on wordcounts(word, doc_id)"
    ]
    for index_name, create_index in zip(index_names, create_indexes):
        if not index_exists(conn, index_name):
            cur.execute(create_index)
    cur.close()


def insert_word(conn, id, word, doc_id, commit=False):
    cur = conn.cursor()
    cur.execute("insert into wordcounts(id, word, doc_id) values (?, ?, ?)", 
                [id, word, doc_id])
    if commit:
        conn.commit()
    cur.close()

        
def count_words_in_table(conn):
    cur = conn.cursor()
    cur.execute("select count(*) as cnt from wordcounts")
    rows = cur.fetchone()
    return int(rows[0])
    cur.close()


conn = sqlite3.connect(WORDCOUNT_DB)
create_wordcounts_table(conn)
num_words = count_words_in_table(conn)
if num_words == 0:
    num_read = 0
    should_commit = True
    ftoks = open(TOKENS_FILE, "r")
    for line in ftoks:
        if num_read % 1000 == 0:
            print("{:d} words loaded".format(num_read))
            should_commit = True
        else:
            should_commit = False
        cols = line.strip().split("\t")
        try:
            id = int(cols[0])
            word = cols[1]
            doc_id = int(cols[2])
            insert_word(conn, id, word, doc_id, should_commit)
        except ValueError:
            # ignore them, they are spaces and special chars
            continue 
        num_read += 1
    ftoks.close()
    print("{:d} words loaded".format(num_read))
    conn.commit()
create_wordcounts_indexes(conn)<jupyter_output><empty_output><jupyter_text>Most common termsWe pull up the 1000 terms with the highest frequency. Output shows lots of junk, for example punctuation characters, stop words, etc.<jupyter_code>def word_counts(conn, top_n):
    cur = conn.cursor()
    count_sql = """
        select word, count(word) as word_count 
        from wordcounts 
        group by word 
        order by word_count desc"""
    if top_n != -1:
        count_sql += " limit {:d}".format(top_n)
    cur.execute(count_sql)
    rows = cur.fetchall()
    return [(row[0], row[1]) for row in rows]
    
top_n = word_counts(conn, 50)
for word, count in top_n:
    print(word, count)<jupyter_output>. 5107669
, 2257816
the 1960197
? 1510757
) 1097449
of 1001307
( 884228
and 746652
a 661768
in 622168
to 593634
- 506861
is 504759
for 405409
we 351814
] 336460
[ 326642
1 313542
= 313274
that 300599
with 232010
: 231680
on 209176
as 205143
2 203857
by 195557
this 189965
are 184099
be 172591
0 139209
can 132284
an 127718
+ 127107
from 127093
x 120524
t 110426
i 108454
which 107900
learning 105867
model 104103
it 96898
k 95817
3 94849
data 94142
our 90981
n 87813
each 87187
algorithm 82145
at 81045
4 80238<jupyter_text>Some simple filtersWe set up a few simple filters to get rid of the obvious noise and see what our top terms are. As expected, top terms do correspond to Deep Learning stuff -- with learning, model, data and algorithm the top 4 words in the corpus.<jupyter_code>english_stopwords = set(stopwords.words('english'))


def is_number_or_fraction(word):
    return re.match(r"(\d)+(\.(\d)+)*", word) is not None


def is_all_punctuation(word):
    chars = [c for c in word if c not in string.punctuation]
    return len(chars) == 0
for word, count in top_n:
    if len(word) < 2:
        continue
    if len(word) == 2 and word.endswith("."):
        continue
    if is_number_or_fraction(word):
        continue
    if is_all_punctuation(word):
        continue
    if word in english_stopwords:
        continue
    print(word, count)<jupyter_output>learning 105867
model 104103
data 94142
algorithm 82145
{'fraction_non_alphanumeric': 0.09800732004880032, 'fraction_numerical': 0.050155889928155076, 'mean_word_length': 3.05607476635514, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '413632', 'license': 'Apache-2.0', 'n_tokens_mistral': 2695, 'n_tokens_neox': 2371, 'n_words': 754, 'path': 'notebooks/02-wordcounts.ipynb', 'repo_name': 'sujitpal/content-engineering-tutorial', 'size': '7377'}