extraction without defining vertical lines #907
Replies: 5 comments 31 replies
-
|
Hi @88arvin Appreciate your interest in the library. The PDF you have provided appears to be a scanned PDF and I am unable to do proper analysis on it. Assuming this is because you tried redacting sensitive information from it, and have access to the text PDF. Have you considered using the |
Beta Was this translation helpful? Give feedback.
-
|
I have tried |
Beta Was this translation helpful? Give feedback.
-
|
If you can use the column names, you could draw lines at the start of each column, apart from
I've used You can use columns = 'Vr.Date', 'Vr.No', 'Vr.Type', 'Particulars', 'Dr.Amt', 'Cr.Amt', 'Balance'
# x0 = start, x1 = end
borders = dict.fromkeys(columns, 'x0')
borders['Vr.Type'] = 'x1'
rows = []
for page in pdf.pages:
vlines = [
page.search(column, regex=False)[0][position] for column, position in borders.items()
] + [ page.bbox[-2] - 70 ]
table = page.extract_table(
dict(explicit_vertical_lines=vlines, horizontal_strategy='text')
)
# skip blank line and column names
rows.extend(table[3:])
# drop any rows with empty `Vr.Date`
df = pd.DataFrame(rows, columns=columns).mask(lambda df: df['Vr.Date'] == '').dropna(subset='Vr.Date')Result: |
Beta Was this translation helpful? Give feedback.
-
Beta Was this translation helpful? Give feedback.
-
|
I guess you mean page 2. For pages like that, it may be better to use import pdfplumber
import re
import pandas as pd
page = pdf.pages[1]
text = page.extract_text(layout=True, keep_blank_chars=True, x_density=1)
df = pd.DataFrame(
[
re.split(r'\s{3,}', line.strip())
for line in text.splitlines() if line.strip()
]
)You could clean up the |
Beta Was this translation helpful? Give feedback.
-
|
You mean I have to split these kinds of pages into separate pdf? |
Beta Was this translation helpful? Give feedback.
-
I am getting the result with this but not the desired result. Because it is creating 9 columns so when I passing |
Beta Was this translation helpful? Give feedback.
-
|
It works on You can try locate what page is creating 9 columns and see what's different about it. You could check |
Beta Was this translation helpful? Give feedback.
-
|
Yes. The code works fine on
|
Beta Was this translation helpful? Give feedback.
-
|
There are so many variations in this PDF which make it really awkward. Perhaps it makes sense to try to use some of the content to help finding the columns. Here we try to isolate the date/type columns and the balance columns and draw lines either side of them. So for example:
import pdfplumber
from operator import itemgetter
pdf = pdfplumber.open('sample4.pdf')
page = pdf.pages[1]
DATE = r'(?im)^\d{2}-(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)-\d{2}'
DR = r'\s+\d+(?:[.]\d+)?'
CR = r'\s+\d+(?:[.]\d+)?'
BALANCE = r'\s+\d+(?:[.]\d+)?\s*[CD]r[.]\n'
VR_NO = r'\d+\s*[A-Z]+'
VR_TYPE = r'[A-Z]'
positions = [
min(page.search(rf'{DR}{CR}{BALANCE}'), key=itemgetter('x0')),
min(page.search(rf'{DR}(?={CR}{BALANCE})'), key=itemgetter('x0')),
min(page.search(rf'{BALANCE}'), key=itemgetter('x0')),
max(page.search(rf'{DATE}\s+{VR_NO}\s+{VR_TYPE}'), key=itemgetter('x1')),
max(page.search(rf'{DATE}(?=\s+{VR_NO}\s+{VR_TYPE})'), key=itemgetter('x1')),
max(page.search(rf'{DATE}\s+{VR_NO}(?=\s+{VR_TYPE})'), key=itemgetter('x1')),
]
vlines = set().union(*[[pos['x0'], pos['x1']] for pos in positions])
im = page.to_image(300)
im.draw_vlines(vlines, stroke_width=3)
im.save('lines.png')We only need to find the date/type/balance columns as those lines will automatically leave the
So if there are no headers you can fallback to using these table = page.extract_table(dict(
explicit_vertical_lines = vlines,
horizontal_strategy = 'text'
))You could also try use the dates for the horizontal lines instead of
It seems to work for the samples you've provided from a quick test. |
Beta Was this translation helpful? Give feedback.
-
|
It probably makes sense to put each approach in its own function. So you could do something like this: import pdfplumber
import pandas as pd
from operator import itemgetter
def extract_table_by_regex(page):
DATE = r'(?im)^\d{2}-(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)-\d{2}'
DR = r'\s+\d+(?:[.]\d+)?'
CR = r'\s+\d+(?:[.]\d+)?'
BALANCE = r'\s+\d+(?:[.]\d+)?\s*[CD]r[.]\n'
VR_NO = r'\d+\s*[A-Z]+'
VR_TYPE = r'[A-Z]'
positions = [
min(page.search(rf'{DR}{CR}{BALANCE}'), key=itemgetter('x0')),
min(page.search(rf'{DR}(?={CR}{BALANCE})'), key=itemgetter('x0')),
min(page.search(rf'{BALANCE}'), key=itemgetter('x0')),
max(page.search(rf'{DATE}\s+{VR_NO}\s+{VR_TYPE}'), key=itemgetter('x1')),
max(page.search(rf'{DATE}(?=\s+{VR_NO}\s+{VR_TYPE})'), key=itemgetter('x1')),
max(page.search(rf'{DATE}\s+{VR_NO}(?=\s+{VR_TYPE})'), key=itemgetter('x1')),
]
vlines = set().union(*[[pos['x0'], pos['x1']] for pos in positions])
dates = page.search(DATE) # also add last bottom line
hlines = [ date['top'] for date in dates ] + [ dates[-1]['bottom'] ]
table = page.extract_table(dict(
explicit_vertical_lines = vlines,
explicit_horizontal_lines = hlines,
))
return table
def extract_table_by_column_names(page):
positions = dict.fromkeys(columns, 'x0')
positions['Vr.Type'] = 'x1'
first_col = page.search(columns[0], regex=False)[0]
name = page.extract_text_lines(return_chars=False)[1]['text']
bbox = first_col['x0'], first_col['top'], *page.bbox[-2:]
crop = page.crop(bbox)
# right-most Dr. on page (used to try match end of last column)
right = max(page.search('Dr.',regex=False), key=itemgetter('x1'))['x1']
vlines = [
crop.search(column, regex=False)[0][position]
for column, position in positions.items()
] + [ right ]
table = crop.extract_table(dict(
explicit_vertical_lines = vlines,
horizontal_strategy = 'text',
))
return name, table[1:] # skip header
columns = ['Vr.Date', 'Vr.No', 'Vr.Type', 'Particulars', 'Dr.Amt', 'Cr.Amt', 'Balance']
pdf = 'Downloads/sample4.pdf'
name = None
data = []
with pdfplumber.open(pdf) as pdf:
for page in pdf.pages:
try:
name, table = extract_table_by_column_names(page)
except:
table = extract_table_by_regex(page)
data.extend([name] + row for row in table)
df = pd.DataFrame(data, columns=['Name'] + columns)Some of the balance columns don't have a space before |
Beta Was this translation helpful? Give feedback.
-
|
Yes, this PDF is really complicated. Now getting new error. |
Beta Was this translation helpful? Give feedback.
-
|
Your point is valid. There will also be a problem if there are values in the Balance columns that don't have Dr. or Cr. at the end. |
Beta Was this translation helpful? Give feedback.
-
|
Indeed. Infact, none of the current regexes match anything on page 1 of This is why using the column names was the initial approach as it's much less prone to error. This is the latest version of the code I've tested and it seems to work on all the previous PDF samples you've provided: import pdfplumber
import pandas as pd
from operator import itemgetter
DATE = r'(?im)^\d{2}-(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)-\d{2}'
DR = r'\s+\d+(?:[.]\d+)?'
CR = r'\s+\d+(?:[.]\d+)?'
BALANCE = r'\s+\d+(?:[.]\d+)?\s*[CD]r[.]\s*\n?'
VR_NO = r'\d+\s*[A-Z]+'
VR_TYPE = r'[A-Z]'
CR_OR_DR = '[CD]r.(?!Amt)\n?'
NUMBER = r'\d'
def extract_table_by_regex(page):
positions = [
min(page.search(rf'{DR}{CR}{BALANCE}'), key=itemgetter('x0')),
min(page.search(rf'{DR}(?={CR}{BALANCE})'), key=itemgetter('x0')),
min(page.search(rf'{BALANCE}'), key=itemgetter('x0')),
min(page.search(rf'{DATE}\s+{VR_NO}\s+{VR_TYPE}'), key=itemgetter('x0')),
min(page.search(rf'{DATE}(?=\s+{VR_NO}\s+{VR_TYPE})'), key=itemgetter('x0')),
min(page.search(rf'{DATE}\s+{VR_NO}(?=\s+{VR_TYPE})'), key=itemgetter('x0')),
]
vlines = set().union(*[[pos['x0'], pos['x1']] for pos in positions])
dates = page.search(DATE) # also add last bottom line
hlines = [ date['top'] for date in dates ] + [ dates[-1]['bottom'] ]
table = page.extract_table(dict(
explicit_vertical_lines = vlines,
explicit_horizontal_lines = hlines,
intersection_tolerance = 1
))
return table
def extract_table_by_column_names(page):
positions = dict.fromkeys(columns, 'x0')
positions['Vr.Type'] = 'x1'
first_col = page.search(columns[0], regex=False)[0]
name = page.extract_text_lines(return_chars=False)[1]['text']
bbox = first_col['x0'], first_col['top'], *page.bbox[-2:]
crop = page.crop(bbox)
# Look for Cr. Dr. in balance column, else look for right-most number
right = page.search(CR_OR_DR) or page.search(NUMBER)
right = max(right, key=itemgetter('x1'))['x1']
vlines = [
crop.search(column, regex=False)[0][position]
for column, position in positions.items()
] + [ right ]
dates = page.search(DATE) # also add last bottom line
hlines = [ date['top'] for date in dates ] + [ dates[-1]['bottom'] ]
table = crop.extract_table(dict(
explicit_vertical_lines = vlines,
explicit_horizontal_lines = hlines,
intersection_tolerance = 1
))
return name, table
columns = ['Vr.Date', 'Vr.No', 'Vr.Type', 'Particulars', 'Dr.Amt', 'Cr.Amt', 'Balance']
pdf = 'Downloads/min.pdf'
name = None
data = []
with pdfplumber.open(pdf) as pdf:
for page in pdf.pages:
try:
name, table = extract_table_by_column_names(page)
except:
table = extract_table_by_regex(page)
data.extend([name] + row for row in table)
df = pd.DataFrame(data, columns=['Name'] + columns) |
Beta Was this translation helpful? Give feedback.
-
|
works perfectly, but stopped at |
Beta Was this translation helpful? Give feedback.
-
|
It probably makes sense to crop in the regex function too, it looks like some blank lines above/below are getting picked up and adding in None values. import pdfplumber
import pandas as pd
from operator import itemgetter
DATE = r'(?im)^\d{2}-(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)-\d{2}'
DR = r'\s+\d+(?:[.]\d+)?'
CR = r'\s+\d+(?:[.]\d+)?'
BALANCE = r'\s+\d+(?:[.]\d+)?\s*[CD]r[.]\s*\n?'
VR_NO = r'\d+\s*[A-Z]+'
VR_TYPE = r'[A-Z]'
CR_OR_DR = r'[CD]r.(?!Amt)\n?'
NUMBER = r'\d'
def extract_table_by_regex(page):
positions = [
min(page.search(rf'{DR}{CR}{BALANCE}'), key=itemgetter('x0')),
min(page.search(rf'{DR}(?={CR}{BALANCE})'), key=itemgetter('x0')),
min(page.search(rf'{BALANCE}'), key=itemgetter('x0')),
min(page.search(rf'{DATE}\s+{VR_NO}\s+{VR_TYPE}'), key=itemgetter('x0')),
min(page.search(rf'{DATE}(?=\s+{VR_NO}\s+{VR_TYPE})'), key=itemgetter('x0')),
min(page.search(rf'{DATE}\s+{VR_NO}(?=\s+{VR_TYPE})'), key=itemgetter('x0')),
]
dates = page.search(DATE)
vlines = set().union(*[[pos['x0'], pos['x1']] for pos in positions])
hlines = [ date['top'] for date in dates ] + [ dates[-1]['bottom'] ]
bbox = min(vlines), dates[0]['top'], max(vlines), dates[-1]['bottom']
crop = page.crop(bbox)
table = crop.extract_table(dict(
explicit_vertical_lines = vlines,
explicit_horizontal_lines = hlines,
intersection_tolerance = 1
))
return table
def extract_table_by_column_names(page):
positions = dict.fromkeys(columns, 'x0')
positions['Vr.Type'] = 'x1'
dates = page.search(DATE)
first_col = page.search(columns[0], regex=False)[0]
name = page.extract_text_lines(return_chars=False)[1]['text']
# Look for Cr. Dr. in balance column, else look for right-most number
right = page.search(CR_OR_DR) or page.search(NUMBER)
right = max(right, key=itemgetter('x1'))['x1']
bbox = first_col['x0'], first_col['top'], right, dates[-1]['bottom']
crop = page.crop(bbox)
vlines = [
crop.search(column, regex=False)[0][position]
for column, position in positions.items()
] + [ right ]
hlines = [ date['top'] for date in dates ] + [ dates[-1]['bottom'] ]
table = crop.extract_table(dict(
explicit_vertical_lines = vlines,
explicit_horizontal_lines = hlines,
intersection_tolerance = 1
))
return name, table
columns = [
'Vr.Date', 'Vr.No', 'Vr.Type', 'Particulars', 'Dr.Amt', 'Cr.Amt', 'Balance'
]
pdf = 'Downloads/1-2.pdf'
name = None
data = []
with pdfplumber.open(pdf) as pdf:
for page in pdf.pages:
try:
name, table = extract_table_by_column_names(page)
except:
table = extract_table_by_regex(page)
data.extend([name] + row for row in table)
df = pd.DataFrame(data, columns=['Name'] + columns) |
Beta Was this translation helpful? Give feedback.
-
|
I appreciate your support and assistance. |
Beta Was this translation helpful? Give feedback.
-
I have attached the sample PDF. I want to convert it into a Pandas dataframe. I know it can be done by explicitly defining the vertical lines. However, I want to know if there is any other way of doing it because same columns are placed differently on each page.
sample.pdf
Beta Was this translation helpful? Give feedback.
All reactions