Extracting tables with horizontal lines only

[DoubleCortado]

Hello.

Could you please advise if it's possible to extract table located under caption 4.1.1.1 (table 1) and 4.1.2.1 (table 2)? I need to extract both tables seperately, not as one table.

I'm able to extract 3rd table (under caption 5.1) but can't find a way to extract first two tables.

Here is pdf for reference:
FS_onepage_borders_an.pdf

I would appreciate any suggestions
Thank you!

[cmdlineluser]

Perhaps you could:

• isolate the bold section headers e.g. 4.1 Section
• extract all horizontal lines inside a section
• use the first / last lines to create your own vertical lines

sections = [ 
    word['bottom'] for word in page.search(r'(\d[.])+') 
    if word['chars'][0]['fontname'].endswith('Bold') 
]
sections.append(page.height)

explicit_vertical_lines = []
for top, bottom in itertools.pairwise(sections):
    for edge in page.horizontal_edges:
        if top <= edge['top'] <= bottom:
            # left vertcial line
            line = dict(
                x0 = edge['x0'],
                x1 = edge['x0'],
                top = top,
                bottom = bottom,
                height = bottom - top,
                orientation = 'v',
                object_type = 'line'
            )

            explicit_vertical_lines.append(line.copy())

            # right vertical line
            line['x0'] = edge['x1']
            line['x1'] = edge['x1']

            explicit_vertical_lines.append(line)

>>> tables = page.extract_tables(dict(explicit_vertical_lines=explicit_vertical_lines))
>>> len(tables)
3
>>> tables[0]
[['', '', '31 December 2021\nEUR', '31 December 2020\nEUR'],
 ['fees receivable', '', '2,519,013', '1,190,688'],
 ['Total', '', '7,519,013', '6,190,688']]
>>> tables[1]
[['', '', '31 December 2021\nEUR', '', '31 December 2020\nEUR'],
 ['Taxes paid in advance\nOther receivables',
  '',
  '2,407,655\n12,349',
  '',
  '1,475,421\n-'],
 ['Total', '', '1,440,004', '', '1,475,421']]

[DoubleCortado]

Thanks a lot! that works great!
just two questions:

1. the amount 12,349 is kept in the same line as 2,407,655, instead of seperate line below. Is there way to avoid such issues?
2. the first column (where we have word 'Total') is not extracted, is there way to include it?

Thank you in advance for your help.

[cmdlineluser]

1. I guess you want horizontal_strategy="text" but that will break table 3, perhaps a 2-pass solution is needed.

2. D'oh - missed that. It's because the line variable is being modified - using line.copy() fixes that.

Update: For the 2-pass approach, perhaps you could check if there are no vertical edges in the section.

A horizontal edge is itself a vertical edge, so you could use a minimum height check e.g.

for table in tables:
    crop = page.crop(table.bbox)
    max_height = max(edge['height'] for edge in crop.vertical_edges)
    ts = dict(explicit_vertical_lines=explicit_vertical_lines)
    if max_height < 1:
        ts['horizontal_strategy'] = 'text'
    crop.extract_table(ts)

[['', '', '31 December 2021', '31 December 2020'],
 ['', '', '', ''],
 ['', '', 'EUR', 'EUR'],
 ['', '', '', ''],
 ['fees receivable', '', '2,519,013', '1,190,688'],
 ['', '', '', ''],
 ['Total', '', '7,519,013', '6,190,688']]
----
[['', '', '31 December 2021', '', '31 December 2020'],
 ['', '', '', '', ''],
 ['', '', 'EUR', '', 'EUR'],
 ['', '', '', '', ''],
 ['Taxes paid in advance', '', '2,407,655', '', '1,475,421'],
 ['', '', '', '', ''],
 ['Other receivables', '', '12,349', '', '-'],
 ['', '', '', '', ''],
 ['Total', '', '1,440,004', '', '1,475,421']]
----
...

[DoubleCortado]

Hello. thank you that's very helpful.

Would you be able to share entire code? for some reason when I run it, after adding for loop, I get:

AttributeError: 'list' object has no attribute 'bbox'

Thanks in advance

[cmdlineluser]

sections = [ 
    word['bottom'] for word in page.search(r'(\d[.])+') 
    if word['chars'][0]['fontname'].endswith('Bold') 
]
sections.append(page.height)

explicit_vertical_lines = []
for top, bottom in itertools.pairwise(sections):
    for edge in page.horizontal_edges:
        if top <= edge['top'] <= bottom:
            line = dict(
                x0 = edge['x0'],
                x1 = edge['x0'],
                top = top,
                bottom = bottom,
                height = bottom - top,
                orientation = 'v',
                object_type = 'line'
            )
            explicit_vertical_lines.append(line.copy())
            line['x0'] = edge['x1']
            line['x1'] = edge['x1']
            explicit_vertical_lines.append(line)
            
tables = page.find_tables(dict(explicit_vertical_lines=explicit_vertical_lines))

for table in tables:
    crop = page.crop(table.bbox)
    max_height = max(edge['height'] for edge in crop.vertical_edges)
    ts = dict(explicit_vertical_lines=explicit_vertical_lines)
    if max_height < 1:
        ts['horizontal_strategy'] = 'text'
        
    print(crop.extract_table(ts))
    print('-' * 42)

[DoubleCortado]

Thank you!

[DoubleCortado]

Hello @cmdlineluser Thank you again for your assistance earlier this month. I was wondering if you could help me with following.

From page 1 - 3 each table is extracted to seperate dataframe. Whereas on page 4 all tables are extracted to one dataframe. On the page 5, the same all tables are taken to one dataframe. Unfortuantaly I do need each table in seperate dataframe.

Can't find the reason why on page 4 and 5 they are merged into one df.

1. I made sure that headers (which are guidance for horizontal lines drawings) are recognized properly by regex.
2. I did visual debugging for both horizontal and vertical lines and I can see that they are drawn properly. No diff between first 3 pages and last two.
3. I can't see any differences between pages which would suggest why on last two pages tables are in one df.4.

I would really appreciate your help!

The code is:

import pdfplumber
import itertools
import pandas as pd
import tabula
import openpyxl
from tabula.io import read_pdf
import re

pdf = pdfplumber.open("Test_ano.pdf")

start_page = 1
end_page = 5

all_tables_dict = {}
all_tables_totals_dict = {}
table_coor = []

for page_number in range(start_page - 1, end_page):
page = pdf.pages[page_number]

# Define a list to store the vertical positions (bottom coordinates) of each section in the table.
sections = [
    word['bottom'] for word in page.search(r'(\d{1,5}\.){1,5}(\s){0,5}(\d\s){0,5}(\b[A-z].*).*')
    if word['chars'][0]['fontname'].endswith('Bold')]
# re.compile(r'(\d\.){1,5}(\.){0,5}(\s){0,5}(\d\s){0,5}(\b[A-z].*){1,100}')
sections.append(page.height)
print(sections)

# Create explicit_vertical_lines, a list to store vertical lines that will be used to define the table boundaries.
explicit_vertical_lines = []
for top, bottom in itertools.pairwise(sections):
    for edge in page.horizontal_edges:
        if top <= edge['top'] <= bottom:
            line = dict(
                x0=edge['x0'],
                x1=edge['x0'],
                top=top,
                bottom=bottom,
                height=bottom - top,
                orientation='v',
                object_type='line'
            )
            explicit_vertical_lines.append(line.copy())
            line['x0'] = edge['x1']
            line['x1'] = edge['x1']
            explicit_vertical_lines.append(line)

tables = page.find_tables(dict(explicit_vertical_lines=explicit_vertical_lines, explicit_horizontal_lines=sections))

# im = page.to_image()
# # Page 19:
# # im.draw_hlines([118, 249, 370, 842])
# # Page 20:
# im.draw_hlines([118, 273, 466, 842])
# # Page 18:
# # im.draw_hlines([118, 138, 263, 283, 304, 414, 434, 574, 842])
# im.debug_tablefinder().show()
# im.show()

for i, table in enumerate(tables, 1):
    crop = page.crop(table.bbox)
    max_height = max(edge['height'] for edge in crop.vertical_edges)
    ts = dict(explicit_vertical_lines=explicit_vertical_lines)
    if max_height < 1:
        ts['horizontal_strategy'] = 'text'

    table_data = crop.extract_table(ts)
    df = pd.DataFrame(table_data[1:], columns=table_data[0])  # Assuming the first row contains column headers
    all_tables_dict[f'table{page_number + 1}_{i}'] = df

    df.to_excel(f'df{page_number + 1}_{i}.xlsx', index=False)

Test_ano.pdf

[cmdlineluser]

Not sure why I skipped the recommended approach of cropping areas before table extraction.

Perhaps this is a better place to start from:

tables = []

for page in pdf.pages:
    sections = [ 
        word['bottom'] for word in page.search(r'(\d[.])+') 
        if word['chars'][0]['fontname'].endswith('Bold') 
    ]
    sections.append(page.height)

    for top, bottom in itertools.pairwise(sections):
        bbox = page.bbox[0], top, page.bbox[2], bottom
        
        try:
            crop = page.within_bbox(bbox)
        except ValueError:
            # invalid crop - skip
            continue
            
        # skip section if contains no horizontal lines
        if crop.horizontal_edges:
            explicit_vertical_lines = { crop.chars[0]['x0'] }
            
            for edge in crop.horizontal_edges:
                explicit_vertical_lines.update((edge['x0'], edge['x1']))
                
            table = crop.extract_table(dict(
               explicit_vertical_lines = explicit_vertical_lines,
               horizontal_strategy = "text",
            ))

            tables.append(table)

That gets you the 13 tables plus one false positive:

>>> len(tables)
14
>>> print(tables[-2])
None