<jupyter_start><jupyter_text>1) Data Collection: Scraping Boiling Point Data from a PDF In this notebook, we'll scrape boiling point data from a PDF copy of [The Yaws Handbook](https://www.sciencedirect.com/book/9780128008348/the-yaws-handbook-of-physical-properties-for-hydrocarbons-and-chemicals). The data tables also contain a wide variety of other chemical information. A quick inspection reveals that the tables of interest are located on pages 3-683.*Note:* For copyright reasons, the PDF is not included in this repository.*Note:* In this data source, the editor's confidence in the tabulated data is indicated with a code (e.g.: "Boiling Point Code"), which is a digit from 1-3. A value of 1 indicates the highest confidence in the reported value, while a value of 3 is a "rough approximation only." After collecting this data, we'll need to filter out the low-confidence data.  Import the necessary modules.<jupyter_code>import camelot # Reads PDF data tables
import pandas as pd # For basic data cleaning
from sqlalchemy import create_engine # Easy access to SQL database<jupyter_output><empty_output><jupyter_text>Trial PDF data table parsing with Camelot....before turning it loose on all 680 pages<jupyter_code>trial_table = camelot.read_pdf("yaws2015.pdf", pages = "3")
type(trial_table)<jupyter_output><empty_output><jupyter_text>Camelot returns a TableLlist object, with one entry for each table detected.<jupyter_code>len(trial_table)
trial_data = trial_table[0].df
trial_data<jupyter_output><empty_output><jupyter_text>This is pretty good! We just need to clean up the column labels a bit. It looks like the first two rows are going to be detected as data rather than labels.  Helper functionsThese functions will streamline 2 key processes:* Scrape the data table from one PDF page using Camelot. Return results as a dataframe.* Clean & write the data: drop unnecessary rows/columns, handle missing values, etc.), then write it to an SQL databaseFor the sake of both memory management & preventing data loss during unforeseen failures, we'll read one page at a time, extracting the data and saving it to the SQL database before moving on to the next page.<jupyter_code>def get_table(page_num_as_str):
    
    # Get the crude data table.
    scraped_table_list = camelot.read_pdf("yaws2015.pdf", pages = page_num_as_str)
    
    # There should be exactly 1 table detected per page. If not, print an error
    # and move on using only the first table found.
    if len(scraped_table_list) != 1:
        print("Possible error reading page: ", page_num_as_str)
        
    return scraped_table_list[0].df
# Specify the column & row numbers to drop from each raw dataframe.
# These were identified using the exploratory scraping above.
drop_cols = [0, 4, 9, 10, 11, 12, 13, 14]
drop_rows = [0, 1]

# Specify more informative column headers.
col_labels = ["Formula", "Name", "CAS No", "Melt Pt (K)", "Melt Pt (code)", "Boil Pt (K)", "Boil Pt (code)"]

# Specify which columns should be forced to numeric data.
numeric_cols = ["Melt Pt (K)", "Melt Pt (code)", "Boil Pt (K)", "Boil Pt (code)"]

# Create a database connection via SQLAlchemy.
db_engine = create_engine("sqlite:///physical_properties.db")

def clean_and_write(raw_dataframe):
    
    # Drop unwanted rows and columns.
    clean_dataframe = raw_dataframe.drop(drop_cols, axis = 1)
    clean_dataframe = clean_dataframe.drop(drop_rows, axis = 0)
    
    # Rename the remaining columns.
    clean_dataframe.columns = col_labels
    
    # Force selected columns to numeric data. Incompatible entries such as
    # ">100" or the missing value indicator "---" are converted to NaN.
    for col in numeric_cols:
        clean_dataframe[col] = pd.to_numeric(clean_dataframe[col], errors = "coerce")
        
    # Write the data to the SQLite database.
    clean_dataframe.to_sql("yaws_2015", con = db_engine, if_exists = "append")
    
    return<jupyter_output><empty_output><jupyter_text>Collect the data!Using the helper functions above, scrape the data of interest from the PDF file.<jupyter_code>for page_num in range(3, 684, 1):
    
    # Cast the page number as a string, ultimately as required by Camelot.
    page_num = str(page_num)
    
    # Scrape the data, then clean & write to the database.
    raw_data = get_table(page_num)
    clean_and_write(raw_data)
    
    # Print a friendly message.
    print("Completed page {}.".format(page_num), end = "\r")
    
print("Tally-ho. All done with this batch.")<jupyter_output>Tally-ho. All done with this batch.
{'fraction_non_alphanumeric': 0.08291180998460523, 'fraction_numerical': 0.014954915328788213, 'mean_word_length': 4.2822299651567945, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 21, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '399143', 'license': 'MIT', 'n_tokens_mistral': 1425, 'n_tokens_neox': 1322, 'n_words': 542, 'path': '1_data_collection_bp.ipynb', 'repo_name': 'jhirner/boiling-pt-prediction', 'size': '4547'}