<jupyter_start><jupyter_text>Extracting tabular data from OCR-processed PDFs with PythonThis notebook explores a method of extracting tabular data from OCR-processed PDF files using Python. We will use two key software libraries: [poppler](https://poppler.freedesktop.org/) and [pdftabextract](https://pypi.python.org/pypi/pdftabextract/). Poppler will be used to extract the images from the PDF files and pdftabextract will be used to extract the tabular data. We will then anaylse the successfulness of this method, how feasible it is to perform on a large scale and what might be done to improve it.The dataset that we will use contains samples of digitised material from the [India Office Medical Archives](https://www.bl.uk/collection-guides/india-office-medical-archive-collections). The dataset was created by <NAME> (2017) and is available for [download](https://data.bl.uk/indiaofficemedicalarchives/ioma3.html) via data.bl.uk. The printed material in this dataset contains tabular data on medical topography, which we will attempt to extract for further analysis. An example of a digitised image from the collection is presented below.![An example of a digitised image from the collection](https://data.bl.uk/images/ioma2.jpg)  PrerequisitesThis tutorial assumes that you already have [Python](https://www.python.org/) installed and have some familiarity with running Python scripts.With that in mind, we will start by installing the required software libraries via [PyPi](https://pypi.python.org/pypi). Open up a command-line interface and run the code below. There is no need to include the exclamation mark at the start of the code block, which is used to install the libraries within this notebook.<jupyter_code>!pip install requests tqdm pdftabextract numpy pandas opencv-python --quiet<jupyter_output><empty_output><jupyter_text>Import the librariesWe can now start writing our Python script. Using a text editor, create a new file and save it as `run.py`, then enter the following.<jupyter_code>import os
import re
import requests
import tqdm
import zipfile
import cv2
import pandas
import numpy as np
import pdftabextract
from pdftabextract import imgproc
from pdftabextract.geom import pt
from pdftabextract.common import read_xml, parse_pages, all_a_in_b, save_page_grids
from pdftabextract.common import DIRECTION_VERTICAL, ROTATION, SKEW_X, SKEW_Y
from pdftabextract.textboxes import rotate_textboxes, deskew_textboxes
from pdftabextract.textboxes import border_positions_from_texts, split_texts_by_positions, join_texts
from pdftabextract.clustering import calc_cluster_centers_1d, find_clusters_1d_break_dist, zip_clusters_and_values
from pdftabextract.extract import make_grid_from_positions, fit_texts_into_grid, datatable_to_dataframe

from math import radians, degrees<jupyter_output><empty_output><jupyter_text>Declare some common variablesThere are some common variables that will be used in various places throughout the tutorial. We will declare these below the imports for easier reference. The comments above each variable indicate their purpose.<jupyter_code># The directory to which we will download our dataset.
DATA_DIR = '../data'

# An HTTP header that we add to identify our application over a network.
USER_AGENT = 'bl-digischol-notebooks'

# The name of the dataset collection
COLLECTION = 'indiaofficemedicalarchives'

# The name of the dataset
DATASET = 'ioma-samples-small'<jupyter_output><empty_output><jupyter_text>Prepare the datasetWe now need to download our dataset and extract the files is contains. The code block below will handle this programmatically. Copy the code into your Python script, save the file, then open up a command-line interface, navigate to the location of your script and run the following:```python run.py```Assuming the dataset does not already exist in the correct location it will be downloaded and the files extracted. For more details about how the process works, see [Downloading datasets with Python](downloading_datasets_with_python.ipynb).<jupyter_code>def create_data_dir(directory):
    if not os.path.exists(directory):
        os.mkdir(directory)
    

def download_dataset(collection, dataset, directory, user_agent):
    url = 'https://data.bl.uk/{0}/{1}.zip'.format(collection, dataset)
    download_fn = url.split('/')[-1]
    download_path = os.path.join(directory, download_fn)
    if not os.path.exists(download_path):
        headers = {'User-agent': user_agent}
        r = requests.get(url, stream=True, headers=headers)
        total_length = int(r.headers.get('Content-Length'))
        total_size = (total_length/1024) + 1
        with open(download_path, 'wb') as f:
            for chunk in tqdm.tqdm(r.iter_content(chunk_size=1024), 
                                   total=total_size, 
                                   desc='Downloading', 
                                   unit='kb',
                                   unit_scale=True, 
                                   miniters=1, 
                                   leave=False): 
                if chunk:
                    f.write(chunk)


def extract_dataset(dataset, collection, data_dir):
    fn = '{}.zip'.format(dataset)
    in_path = os.path.join(data_dir, fn)
    out_path = os.path.join(data_dir, collection)
    with zipfile.ZipFile(in_path) as archive:
        unextracted = [name for name in archive.namelist() 
                       if not os.path.exists(os.path.join(out_path, name))]
        if unextracted:
            for i in tqdm.tqdm(range(len(unextracted)), 
                               desc='Extracting', 
                               unit='file', 
                               leave=False):
                archive.extract(unextracted[i], path=out_path)


create_data_dir(DATA_DIR)
download_dataset(COLLECTION, DATASET, DATA_DIR, USER_AGENT)
extract_dataset(DATASET, COLLECTION, DATA_DIR)<jupyter_output><empty_output><jupyter_text>Extract the images from the PDF filesAs a first step to accessing OCR data embedded in the image files we need to extract the images from our PDF files and convert the OCR data to XML. To do this we use a PDF rendering library called [poppler](https://github.com/davidben/poppler), which is available in most Linux distributions. Note that if running this notebook on a Windows machine, the latest binaries for poppler can be downloaded [here](http://blog.alivate.com.au/poppler-windows/). For Mac, install [Homebrew](https://brew.sh/), open a terminal and run `brew install poppler`.<jupyter_code>def extract_images(data_dir, collection):
    base_dir = os.path.join(data_dir, collection)
    pdfs = [fn for fn in os.listdir(base_dir) if fn.endswith('.pdf')] 
    xmls = ['{}.xml'.format(os.path.splitext(pdf)[-2]) for pdf in pdfs]
    unconverted = [fn for fn in xmls if not os.path.exists(os.path.join(base_dir, fn))]

    if unconverted:
        for fn in tqdm.tqdm(unconverted, desc='Converting', unit='file', leave=False):
            pdf_path = os.path.join(base_dir, '{}.pdf'.format(os.path.splitext(fn)[-2]))
            xml_path = os.path.join(base_dir, fn)
            script = 'C:/users/amendes/downloads/poppler-0.51/bin/pdftohtml -c -q -hidden -xml "{0}" "{1}"'.format(pdf_path, xml_path)
            !{script}

extract_images(DATA_DIR, COLLECTION)<jupyter_output><empty_output><jupyter_text>We should now have an XML file containing our OCR data, along with a JPEG image for each page of the PDF.  Detect lines in the imagesThe following code is largely taken from the online tutorial [Data Mining OCR PDFs — Using pdftabextract to Liberate Tabular Data from Scanned Documents](https://datascience.blog.wzb.eu/2017/02/16/data-mining-ocr-pdfs-using-pdftabextract-to-liberate-tabular-data-from-scanned-documents/) (<NAME>, 2017), which includes a detailed explanation about the programatic process below. Essentially, we identify the lines in each image, attempt to fix any rotation or skewing, cluster similar lines, identify any grids and output the data contanied within those grids to a set of tables. The result should be the conversion of data from printed to electronic tables.<jupyter_code># helper function to save an image 
def save_image_w_lines(iproc_obj, imgfilebasename):
    img_lines = iproc_obj.draw_lines(orig_img_as_background=True)
    img_lines_file = os.path.join('%s-lines-orig.png' % imgfilebasename)
    cv2.imwrite(img_lines_file, img_lines)
def detect_lines(iproc_obj, img_path, metadata):

    # calculate the scaling of the image file in relation to the text boxes
    page_scaling_x = iproc_obj.img_w / metadata['width']
    page_scaling_y = iproc_obj.img_h / metadata['height']

    # detect the lines
    lines_hough = iproc_obj.detect_lines(canny_kernel_size=3, canny_low_thresh=50, canny_high_thresh=150,
                                         hough_rho_res=1,
                                         hough_theta_res=np.pi/500,
                                         hough_votes_thresh=int(round(0.2 * iproc_obj.img_w)))

    # save the image with detected lines
    save_image_w_lines(iproc_obj, img_base_fn)<jupyter_output><empty_output><jupyter_text>Fix rotation or skewIf the tables are rotated or skewed we'll have a hard time extracting the data from them. The following code fixes this...<jupyter_code>def fix_rotation_and_skew(iproc_obj, img_base_fn, metadata, xml, xmltree):
    rot_or_skew_type, rot_or_skew_radians = iproc_obj.find_rotation_or_skew(radians(0.5),
                                                                            radians(1),
                                                                            omit_on_rot_thresh=radians(0.5))
    # rotate back or deskew text boxes
    needs_fix = True
    if rot_or_skew_type == ROTATION:
        rotate_textboxes(metadata, -rot_or_skew_radians, pt(0, 0))
    elif rot_or_skew_type in (SKEW_X, SKEW_Y):
        deskew_textboxes(metadata, -rot_or_skew_radians, rot_or_skew_type, pt(0, 0))
    else:
        needs_fix = False

    if needs_fix:
        # rotate back or deskew detected lines
        lines_hough = iproc_obj.apply_found_rotation_or_skew(rot_or_skew_type, 
                                                             -rot_or_skew_radians)

        save_image_w_lines(iproc_obj, img_base_fn + '-repaired')

    out_base_fn = xml[:xml.rindex('.')]
    repaired_xmlfile = os.path.join(out_base_fn + '.repaired.xml')
    xmltree.write(repaired_xmlfile)
def cluster_lines(iproc_obj, img_base_fn, metadata):
    MIN_COL_WIDTH = 60 # minimum width of a column in pixels

    # cluster the detected *vertical* lines using find_clusters_1d_break_dist as simple clustering function
    page_scaling_x = iproc_obj.img_w / metadata['width']
    vertical_clusters = iproc_obj.find_clusters(imgproc.DIRECTION_VERTICAL, find_clusters_1d_break_dist,
                                                remove_empty_cluster_sections_use_texts=metadata['texts'],
                                                remove_empty_cluster_sections_n_texts_ratio=0.1,   
                                                remove_empty_cluster_sections_scaling=page_scaling_x,
                                                dist_thresh=MIN_COL_WIDTH/2)

    # draw the clusters
    try:
        img_w_clusters = iproc_obj.draw_line_clusters(imgproc.DIRECTION_VERTICAL, vertical_clusters)
    except Exception as e:
        print('ERROR:' + e.message)
        return
    
    save_img_file = os.path.join('%s-vertical-clusters.png' % img_base_fn)
    cv2.imwrite(save_img_file, img_w_clusters)
    return vertical_clusters
def find_row_positions(iproc_obj, metadata, vertical_clusters):
    page_scaling_x = iproc_obj.img_w / metadata['width']
    page_colpos = np.array(calc_cluster_centers_1d(vertical_clusters)) / page_scaling_x
    
    # right border of the second column
    col2_rightborder = page_colpos[2]

    # calculate median text box height
    median_text_height = np.median([t['height'] for t in metadata['texts']])

    # get all texts in the first two columns with a "usual" textbox height
    # we will only use these text boxes in order to determine the line positions because they are more "stable"
    # otherwise, especially the right side of the column header can lead to problems detecting the first table row
    text_height_deviation_thresh = median_text_height / 2
    texts_cols_1_2 = [t for t in metadata['texts']
                      if t['right'] <= col2_rightborder
                         and abs(t['height'] - median_text_height) <= text_height_deviation_thresh]
    
    # get all textboxes' top and bottom border positions
    borders_y = border_positions_from_texts(texts_cols_1_2, DIRECTION_VERTICAL)

    # break into clusters using half of the median text height as break distance
    clusters_y = find_clusters_1d_break_dist(borders_y, dist_thresh=median_text_height/2)
    clusters_w_vals = zip_clusters_and_values(clusters_y, borders_y)

    # for each cluster, calculate the median as center
    pos_y = calc_cluster_centers_1d(clusters_w_vals)
    pos_y.append(metadata['height'])

    print('number of line positions:', len(pos_y))

    # a (possibly malformed) population number + space + start of city name
    pttrn_table_row_beginning = re.compile(r'^[\d Oo][\d Oo]{2,} +[A-ZÄÖÜ]')

    # 1. try to find the top row of the table
    texts_cols_1_2_per_line = split_texts_by_positions(texts_cols_1_2, pos_y, DIRECTION_VERTICAL,
                                                       alignment='middle',
                                                       enrich_with_positions=True)

    # go through the texts line per line
    for line_texts, (line_top, line_bottom) in texts_cols_1_2_per_line:
        line_str = join_texts(line_texts)
        if pttrn_table_row_beginning.match(line_str):  # check if the line content matches the given pattern
            top_y = line_top
            break
    else:
        top_y = 0
    
    # hints for a footer text box
    words_in_footer = ('anzeige', 'annahme', 'ala')

    # 2. try to find the bottom row of the table
    min_footer_text_height = median_text_height * 1.5
    min_footer_y_pos = metadata['height'] * 0.7
    # get all texts in the lower 30% of the page that have are at least 50% bigger than the median textbox height
    bottom_texts = [t for t in metadata['texts']
                    if t['top'] >= min_footer_y_pos and t['height'] >= min_footer_text_height]
    bottom_texts_per_line = split_texts_by_positions(bottom_texts,
                                                     pos_y + [metadata['height']],   # always down to the end of the page
                                                     DIRECTION_VERTICAL,
                                                     alignment='middle',
                                                     enrich_with_positions=True)
    # go through the texts at the bottom line per line
    page_span = page_colpos[-1] - page_colpos[0]
    min_footer_text_width = page_span * 0.8
    for line_texts, (line_top, line_bottom) in bottom_texts_per_line:
        line_str = join_texts(line_texts)
        has_wide_footer_text = any(t['width'] >= min_footer_text_width for t in line_texts)
        # check if there's at least one wide text or if all of the required words for a footer match
        if has_wide_footer_text or all_a_in_b(words_in_footer, line_str):
            bottom_y = line_top
            break
    else:
        bottom_y = metadata['height']
    
    page_rowpos = [y for y in pos_y if top_y <= y <= bottom_y]
    return page_colpos, page_rowpos

def create_grid(page_colpos, page_rowpos, page_num, img_base_fn):
    grid = make_grid_from_positions(page_colpos, page_rowpos)
    if not grid:
        return
    
    n_rows = len(grid)
    n_cols = len(grid[0])
    
    page_grids_file = '{0}.pagegrids_p{1}.json'.format(img_base_fn, page_num)
    save_page_grids({page_num: grid}, page_grids_file)
    return grid
def extract_tables(data_dir, collection):
    base_dir = os.path.join(data_dir, collection)
    xmls = [fn for fn in os.listdir(base_dir) if os.path.splitext(fn)[-1] == '.xml']
    data = []
    
    for xml in xmls:
        xml_path = os.path.join(base_dir, xml)
        xmltree, xmlroot = read_xml(xml_path)
        pages = parse_pages(xmlroot)
        
        for page_num, page in tqdm.tqdm(enumerate(pages),
                                        desc='Processing',
                                        unit='page',
                                        unit_scale=True,
                                        miniters=1,
                                        leave=False):
            if page_num != 27:
                continue
                
            metadata = pages[page_num]
            img_path = metadata['image']
            img_base_fn = img_path[:img_path.rindex('.')]
            print img_base_fn
            iproc_obj = imgproc.ImageProc(img_path)
            
            detect_lines(iproc_obj, img_base_fn, metadata)
            fix_rotation_and_skew(iproc_obj, img_base_fn, metadata, xml, xmltree)
            vertical_clusters = cluster_lines(iproc_obj, img_base_fn, metadata)
            page_colpos, page_rowpos = find_row_positions(iproc_obj, metadata, vertical_clusters)
            grid = create_grid(page_colpos, page_rowpos, page_num, img_base_fn)
            if grid:
                datatable = fit_texts_into_grid(metadata['texts'], grid)
                df = datatable_to_dataframe(datatable)
                print df

extract_tables(DATA_DIR, COLLECTION)<jupyter_output>Processing: 0.00page [00:00, ?page/s]
{'fraction_non_alphanumeric': 0.07708049113233288, 'fraction_numerical': 0.00676443838108231, 'mean_word_length': 2.8819505736981466, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 33, 'https://': 11, 'lorem ipsum': 0, 'www.': 2, 'xml': 28}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 61, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '495186', 'license': 'MIT', 'n_tokens_mistral': 5298, 'n_tokens_neox': 5111, 'n_words': 1483, 'path': 'notebooks/extracting_tabular_data_with_python.ipynb', 'repo_name': 'alexandermendes/bl-digischol-notebooks', 'size': '17605'}