Help parsing semi-structured data with multiple indentations

[saveyak]

I'm parsing a PDF that shows books removed or lost from various school libraries in a public school district. (See sample pages here.) The format is tricky to parse because there are multiple indentations, and also headers that cover multiple books:

• The first line shows the date and number of all books removed from any library in the district. There may be multiple books listed under this header.
• Second line is indented and shows the title of the book
• Third line is indented again and shows the author/ISBN/publish date
• Four line is indented again and shows for the school the book was located in (this may spill onto another line) and other information
• Final line shows whether book was weeded, deleted or lost.

My current strategy mostly relies on regular expressions. I convert everything to text, then I plan to use regex to find the author, title, etc. I have gotten this far so far:

pdf = pdfplumber.open("/content/Atlanta_Public_Schools_GA_sample.pdf")
page = pdf.pages[0].crop((0, 60, page.width, 750)) #Cutting off the header/footer
text = page.extract_text()

sections = re.split(r'(?=\n\d{1,2}/\d{1,2}/\d{4} - Copies Removed: \d+)\n', text)

for section in sections:
  #Code will go here to go through each book in each section and use regex to grab the author, title, number of copies removed, date removed, ISBN number, site from which book was removed, and weeded/lost status. 

Before I spend a lot of time crafting regexes, I want to know if there is an alternative way to do this. I used debug_tablefinder just to see what it would look like, and I did notice that the program was able to recognize rectangles around at least some of the variables I want to grab. Is there a way to get it to recognize what essentially tables within tables within tables?

im = page.to_image()
im.reset().debug_tablefinder()

If it seems like regex will be my best best, then I'd like tips on how to grab the site from which the book was removed and the weeded/lost status. Both seem much harder to grab than the author or the title because the site can spill over multiple lines, and then weeded/lost status doesn't follow any particular keyword. (Right now every status says the same thing but in the larger PDF, some of them say stuff like "Was lost by ____ on ____" or "Was deleted." I do think they all start with "Was" but I'm not sure.)

[samkit-jain]

Hi @saveyak Appreciate your interest in the library. Regex is a good idea. But, you could also do a mix of both regex and tables.

• You could use the layout parameter in extract_text like page.extract_text(layout=True). It will give you the indentation as well.
• You can find sections by dividing based on the horizontal lines font size or bold font.
• The data that is site, barcode, can also be extracted by using table extraction. You can define explicit horizontal lines and extract the data. There will be noise, but would have to filter out.

[saveyak]

Thank you for the response, this is quite helpful.

For your third bullet point -- how can I define explicit horizontal lines, if the tables are in different locations on every pages depending on the number and size of entries? Maybe it would be better to define explicit vertical lines instead as that is more consistent?

Also, how do I actually find the right coordinate to define an explicit line to begin with? How can I tell what the x/y coordinate of a line or point on the table is?

[saveyak]

Here are table settings which help me get closer to grabbing site/barcode/price/acquired by as a table, but still not sure how to get it to grab the right horizontal lines.

table_settings = {
    "vertical_strategy": "explicit",
    "explicit_vertical_lines": [45, 150, 240, 280, 395, 475],
    "intersection_x_tolerance": 5,
}

im.reset().debug_tablefinder(table_settings)

Also, I just figured out the coordinates by guessing/checking -- I don't know if there is a more efficient way to do this.

[samkit-jain]

Hi @saveyak Sorry for the late response. And it seems that @cmdlineluser has provided a great solution. To add to that, maybe you can also leverage the fact that a few the things you need are wrapped in rects as can be seen in the image below:

Using a very naive approach, you can get the results as

rects = sorted(p.rects, key=lambda x: (x["top"], x["x0"]))
i = 8
while i < len(rects):
    print(f'Site = {" ".join(p.crop((rects[i]["x0"], rects[i]["top"], rects[i]["x1"], rects[i]["bottom"])).extract_text().splitlines())}')
    print(f'Barcode = {p.crop((rects[i+1]["x0"], rects[i+1]["top"], rects[i+1]["x1"], rects[i+1]["bottom"])).extract_text()}')
    print(f'Price = {p.crop((rects[i+2]["x0"], rects[i+2]["top"], rects[i+2]["x1"], rects[i+2]["bottom"])).extract_text()}')
    print(f'Acquired = {p.crop((rects[i+3]["x0"], rects[i+3]["top"], rects[i+3]["x1"], rects[i+3]["bottom"])).extract_text()}')
    print(f'Removed By = {p.crop((rects[i+4]["x0"], rects[i+4]["top"], rects[i+4]["x1"], rects[i+4]["bottom"])).extract_text()}')
    i += 10
    print()

Site = Joseph Humphries Elementary School
Barcode = 32441014018707
Price = 
Acquired = 6/11/2021
Removed By = 113396-42441

Site = Joseph Humphries Elementary School
Barcode = 32441012580849
Price = $15.00
Acquired = 6/12/2023
Removed By = 113396-42441

Site = Midtown High School
Barcode = 33343000017835
Price = $10.00
Acquired = 4/19/2018
Removed By = christen.mcclain

Site = Midtown High School
Barcode = *3431000028742
Price = $19.95
Acquired = 3/21/2006
Removed By = christen.mcclain

Site = Centennial Place Academy (Charter)
Barcode = 33170000506628
Price = $16.50
Acquired = 2/21/2000
Removed By = 33554-43170

Site = Centennial Place Academy (Charter)
Barcode = 33170011581578
Price = $24.00
Acquired = 4/19/2018
Removed By = 33554-43170

Site = Centennial Place Academy (Charter)
Barcode = 33170011059377
Price = $19.79
Acquired = 4/19/2018
Removed By = 33554-43170

[cmdlineluser]

It's also a bit tricky as individual records can span multiple pages e.g. "The blaze engulfs" starts on page 2 and continues to page 3.

There may be some useful ideas here:

---

Merge all pages into a 1-page PDF document

An approach I've found helpful is to combine everything into a single page pdf, the simplest way I found was to use pypdf

import pypdf

pdf = pypdf.PdfReader('Downloads/Atlanta_Public_Schools_GA_sample.pdf')

width  = max(page.mediabox.right for page in pdf.pages)
height = sum(page.mediabox.top   for page in pdf.pages)

blank = pypdf.PageObject.create_blank_page(None, width, height)

blank.merge_page(pdf.pages[-1])

bottom = pdf.pages[-1].mediabox.bottom
height = pdf.pages[-1].mediabox.height

for page in reversed(pdf.pages[:-1]):
    blank.merge_translated_page(page, bottom, height)
    
    bottom = page.mediabox.bottom
    height += page.mediabox.height

out = pypdf.PdfWriter()

out.add_page(blank)

out.write('page.pdf')

---

page.search() + page.filter()

With everything in a single page, we can locate "header" sections that we want to "remove".

I've used regex search here, and we can use their top and bottom coords as part of a check in page.filter.

import pdfplumber
from operator import itemgetter

pdf = pdfplumber.open('page.pdf')

headers = page.search(r'((Report generated on.+\n)?Library Weeding Log.+\nFrom.+)?\n.+Copies Removed:')

# keep items that are not inside any header coord range
filtered_page = page.filter(lambda obj: 
   not any(h['top'] <= obj['top'] <= h['bottom'] for h in headers)
)

authors = filtered_page.search(r'Author:\s*(.+?)\s*ISBN:\s*(.+?)\s*Published:\s*(.+)')
titles  = filtered_page.search(r'.+(?=\nAuthor: (.+?)\s*ISBN: (.+?)\s*Published: (.+))')
sites   = filtered_page.search(r'Site Barcode Price Acquired Removed By')

We can then search for the author/isbn, title and "site" sections.

(It's probably more robust to extract each "block" and split them up instead of separate searches.)

---

page.crop() + splitting into columns

The second entry is interesting as its ISBN spans multiple lines, so we'll use that as an example.

page.search() returns "objects" that contain coordinate/positional information.

We can use this to "crop" out specific sections of the page to help with parsing.

The second Author/ISBN section is located below the title and above the site - so we use their positions in our crop bbox.

bbox = filtered_page.bbox[0], titles[1]['bottom'], filtered_page.bbox[2], sites[1]['top']
crop = filtered_page.within_bbox(bbox)

Which leaves us:

This can then be split up into "columns" by using the label positions as vertical lines.

labels = 'Author', 'ISBN', 'Published'

explicit_vertical_lines = [ crop.search(f'{label}:')[0]['x0'] for label in labels ]
# find right-most char to use as closing vertical line
explicit_vertical_lines.append(max(crop.chars, key=itemgetter('x1'))['x1'])

author_table = crop.extract_table(dict(
   explicit_vertical_lines = explicit_vertical_lines,
   horizontal_strategy = "text",
   vertical_strategy = "explicit"
))

# ┌──────────────────────────┬──────────────────────────────┬─────────────────┐
# │ column_0                 ┆ column_1                     ┆ column_2        │
# │ ---                      ┆ ---                          ┆ ---             │
# │ str                      ┆ str                          ┆ str             │
# ╞══════════════════════════╪══════════════════════════════╪═════════════════╡
# │ Author: Lamana, Julie T. ┆ ISBN: 978-1-45212456-8 (alk. ┆ Published: 2014 │
# │                          ┆ paper)                       ┆                 │
# └──────────────────────────┴──────────────────────────────┴─────────────────┘

The site table section ends when we hit the next entry title, so we can use its top as the bottom of our crop area.

(For the last site table on the page, there is no title following, so the bottom of the page is used instead.)

bbox = filtered_page.bbox[0], sites[1]['top'], filtered_page.bbox[2], titles[2]['top']
crop = filtered_page.within_bbox(bbox)

We can use the same approach to use the column name positions as markers for vertical lines.

The columns are not entirely aligned, I've used - 3 which works for the sample data, but it may need tweaking.

columns = 'Site', 'Barcode', 'Price', 'Acquired', 'Removed By'

explicit_vertical_lines = [ crop.search(column)[0]['x0'] - 3 for column in columns ]
# find right-most char in area to use as closing vertical line
explicit_vertical_lines.append(max(crop.chars, key=itemgetter('x1'))['x1'])

site_table = crop.extract_table(dict(
   explicit_vertical_lines = explicit_vertical_lines,
   horizontal_strategy = "text",
   vertical_strategy = "explicit"
))

# ┌─────────────────────┬────────────────┬────────┬───────────┬──────────────┐
# │ Site                ┆ Barcode        ┆ Price  ┆ Acquired  ┆ Removed By   │
# │ ---                 ┆ ---            ┆ ---    ┆ ---       ┆ ---          │
# │ str                 ┆ str            ┆ str    ┆ str       ┆ str          │
# ╞═════════════════════╪════════════════╪════════╪═══════════╪══════════════╡
# │ Joseph Humphries    ┆ 32441012580849 ┆ $15.00 ┆ 6/12/2023 ┆ 113396-42441 │
# │ Elementary School   ┆                ┆        ┆           ┆              │
# │ Was Available -- We ┆ eded           ┆        ┆           ┆              │
# └─────────────────────┴────────────────┴────────┴───────────┴──────────────┘

The "last row" can be joined together, or perhaps it's simpler to extract that separately if it's always the last line of text in the crop area.

>>> crop.extract_text().splitlines()[-1]
'Was Available -- Weeded'

You'll probably want to loop through all the records and apply the cropping methods, e.g. something like:

for n, (author, title, site) in enumerate(zip(authors, titles, sites)):
    ...

[saveyak]

Update on this: Jeremy Singer-Vine very kindly reached out to me and helped with this problem. We were able to find a solution that uses this strategy:
-- Divide each page into sections based on the header, which is always bold and size 12
-- Within that section, divide the page into each individual book based on the title, which is always bold and size 10
-- Use vertical lines to find the author, ISBN, and publish date. (We determined through trial and error that these three data points were divided by vertical lines at x = 235 and x = 395.)
-- Use rectangles to find all the other data

More detail:

First, find the subheader (the part that says the date and number of books removed.) Find the range of vertical positions that defines where that section sits on the page.

import pdfplumber
import pandas as pd
pdf = pdfplumber.open("Atlanta_Public_Schools_GA_sample.pdf")

def get_subheader_spans(page):
    subheaders = (
        pd.DataFrame(page.chars)
        .loc[lambda df: df["size"] == 12]
        .groupby("top")
        .pipe(lambda grp: pd.DataFrame({
            "text": grp["text"].apply("".join)
        }))
    )

    return list(zip(
        subheaders.index,
        list(subheaders.index[1:]) + [ page.lines[-1]["top"] ]
    ))

Next, crop the page to that section.

def crop_to_span(page, start, end):
    return page.within_bbox((0, start - 1, page.width, end + 1))

Next, within that section, find the vertical positions of each individual book.

def get_book_spans(cropped_section):
    books = (
        pd.DataFrame(cropped_section.chars)
        .loc[lambda df: df["size"] == 10]
        .loc[lambda df: df["fontname"].str.contains("B+")]
        .groupby("top")
        .pipe(lambda grp: pd.DataFrame({
            "top": grp["text"].apply("".join)
        }))
    )

    return list(zip(
        books.index,
        list(books.index[1:]) + [ cropped_section.bbox[3] ]
    ))

Within each individual book, identify the title, the middle section, and the bottom section.

def extract_data_for_book(cropped_book):
    title_chars = [ c for c in cropped_book.chars if "B+" in c["fontname"] and c["size"] == 10 ]
    small_bold_chars = [ c for c in cropped_book.chars if "B+" in c["fontname"] and c["size"] < 10 ]
    middle_chars = [ c for c in cropped_book.chars if (
        (c["top"] > title_chars[-1]["top"]) and
        (c["top"] < small_bold_chars[0]["top"])
    )]
    
    box_text = [
        (cropped_book.crop if i < 9 else cropped_book.within_bbox)
        (pdfplumber.utils.obj_to_bbox(rect)).extract_text()
        for i, rect in enumerate(cropped_book.rects) ]
    
    #If a book is cut off at the bottom of the PDF, we won't be able to grab the data. Put "ERROR" for the value.
    def get_box_text(index, default_value="ERROR"):
        try:
            return box_text[index]
        except IndexError:
            return default_value
    
    return dict(
        title = pdfplumber.utils.extract_text(title_chars),
        author = pdfplumber.utils.extract_text([ c for c in middle_chars if c["x0"] < 235 ]), 
        isbn = pdfplumber.utils.extract_text([ c for c in middle_chars if c["x0"] > 235 and c["x0"] < 395 ]),
        published = pdfplumber.utils.extract_text([ c for c in middle_chars if c["x0"] > 395 ]),
        site = get_box_text(4),
        barcode = get_box_text(5),
        price = get_box_text(6),
        acquired = get_box_text(7),
        removed_by = get_box_text(8),
        status = get_box_text(9)
    )

Finally, put all the functions together.

def parse_page(page):
    for subheader_span in get_subheader_spans(page):
        page_number = page.objects['line'][0]['page_number']
        cropped_section = crop_to_span(page, *subheader_span)
        date_chars = [ c for c in cropped_section.chars if "B+" in c["fontname"] and c["size"] == 12 ]
        date = pdfplumber.utils.extract_text(date_chars)
        for book_span in get_book_spans(cropped_section):
            cropped_book = crop_to_span(page, *book_span)
            book_data = extract_data_for_book(cropped_book)
            book_data["removal_date"] = date
            book_data["page_number"] = page_number
            yield book_data

dfs = [pd.DataFrame(parse_page(page)) for page in pdf.pages]

full_df = pd.concat(dfs, ignore_index=True)

The only issue remaining is that about 6% of books listed get cut off because they're at the bottom of the page, so as a result some data is missing. But, since we at least still get the title, author and date removed, this isn't a big deal to me.

I very much appreciate everyone's help with this problem!

[cmdlineluser]

Appreciate the update @saveyak

The only issue remaining is that about 6% of books listed get cut off

Yeah, that part can be tricky. This is where the merging into a 1-page PDF has been helpful for me as it can avoid the need for dealing with half-finished / spanning "records".

If it's of any use, I've adapted the previous comment #1000 (comment) into a full example.

It requires creating the merged PDF first.

It catches all the "ERROR" rows in the sample pdf:

>>> db['authors'][-8]
[['Author: Pinkney, Andrea Davis.',
  'ISBN: 0-15-201892-1 (pbk.)',
  'Published: 1998']]
>>> db['sites'][-8]
[['Site', 'Barcode', 'Price', 'Acquired', 'Removed By'],
 ['', '', '', '', ''],
 ['Centennial Place', '33170000562167', '$13.10', '11/5/1999', '33554-43170'],
 ['Academy (Charter)', '', '', '', ''],
 ['Was Available -- Wee', 'ded', '', '', '']]
>>> db['titles'][-8]
'Dear Benjamin Banneker (Removed: 1)'

Code:

import bisect
import pdfplumber
from   operator import itemgetter

def find_table_with_columns(page, top, bottom, columns, tolerance=0):
    bbox = page.bbox[0], top, page.bbox[2], bottom
    crop = page.within_bbox(bbox)
    
    explicit_vertical_lines = [ 
        crop.search(col)[0]['x0'] - tolerance for col in columns
    ]

    explicit_vertical_lines.append(
        max(crop.chars, key=itemgetter('x1'))['x1']
    )
    
    return crop.extract_table(dict(
       explicit_vertical_lines = explicit_vertical_lines,
       horizontal_strategy = "text",
       vertical_strategy = "explicit"
    ))


def parse_page(page):
    # find these first as we will filter them out from the page
    removals = page.search(r'\d+/\d+/\d+ - Copies Removed: \d.*')

    # filter out unwanted sections in between records/pages
    sections = page.search(
        r'((Report generated on.+\n)?'
        r'Library Weeding Log.+\nFrom.+)?'
        r'\n.+Copies Removed:'
    )

    page = page.filter(lambda obj: not any(
        s['top'] <= obj['top'] <= s['bottom'] for s in sections
    ))

    sites = page.search(r'Site Barcode Price Acquired Removed By')
    titles = page.search(r'.+(?=\nAuthor: (.+?)\s*ISBN: (.+?)\s*Published: (.+))')
    authors = page.search(r'Author:\s*(.+?)\s*ISBN:\s*(.+?)\s*Published:\s*(.+)')

    # Should all have same length
    assert len(set(map(len, [sites, titles, authors]))) == 1

    return page, removals, titles, authors, sites


pdf = pdfplumber.open("merged-into-1-page.pdf")

page, removals, titles, authors, sites = parse_page(pdf.pages[0])

db = {"authors": [], "sites": []}

for idx in range(len(titles)):
    top = titles[idx]['bottom']
    bottom = sites[idx]['top']
    
    author = find_table_with_columns(
        page,
        top, 
        bottom, 
        columns = ['Author', 'ISBN', 'Published']
    )
    db["authors"].append(author) 
    
    top = bottom
    if idx + 1 < len(titles):
        bottom = titles[idx + 1]['top'] - 1
    else:
        bottom = max(page.search('Report generated on'), key=itemgetter('bottom'))['top']
        
    site = find_table_with_columns(
        page,
        top, 
        bottom, 
        columns = ['Site', 'Barcode', 'Price', 'Acquired', 'Removed By'], 
        tolerance = 1 # Barcode is not fully aligned
    )
    db["sites"].append(site) 

db['titles'] = [title['text'] for title in titles]
db['removed'] = [None] * len(titles)

# this can then be forward filled
for removal in removals:
    idx = bisect.bisect_left(titles, removal['bottom'], key=itemgetter('bottom'))
    db['removed'][idx] = removal['text']