CODEWITHSUNDEEP
pythonghostscriptpython-camelotpdf-extraction
Gabriel Souto
Gabriel Souto

Reputation: 620

Problems to extract table data using camelot without error message

I am trying to extract tables from this pdf link using camelot, however, when a try this follow code:

import camelot

file = 'relacao_medicamentos_rename_2020.pdf'

tables = camelot.read_pdf(file)
tables.export('relacao_medicamentos_rename_2020.csv', f='csv', compress=False)

Simple nothing happens. This is very strange 'cause when I try the same code but with this pdf link works very welll.

Upvotes: 4

Views: 7617

Answers (3)

rosa b.
rosa b.

Reputation: 2146

As Stefano suggested you need to specify the relevant pages and set the option flavor='stream'. The default flavor='lattice' only works if there are lines between the cells.

Additionally, increasing row_tol helps to group rows together. So that, for example, the header of the first table is not read as three separate rows but as one row. Specifically 'Concentração/Composição' is identified as coherent text.

Also you may want to use strip_text='\n' to remove new line characters.

This results in (reading page 17 and 18 as an example):

import camelot
file = 'relacao_medicamentos_rename_2020.pdf'
tables = camelot.read_pdf(file, pages='17, 18', flavor='stream', row_tol=20, strip_text='\n') 
tables.export('foo.csv', f='csv', compress=False)

Still, this way you end up with one table per page and one csv file per table. I.e. in the example above you get two .csv files. This needs to be handled outside camelot. To merge tables spanning multiple pages using pandas:

import pandas as pd
dfs = []  # list to store dataframes
for table in tables:
    df = table.df
    df.columns = df.iloc[0]  # use first row as header
    df = df[1:]  # remove the first row from the dataframe
    dfs.append(df)
df = pd.concat(dfs, axis=0)  # concatenate all dataframes in list 
df.to_csv('foo.csv')  # export dataframe to csv

Also, there are difficulties identifying table areas on pages containing both text and tables (e.g. pdf page 16). In these cases the table area can be specified. For the table on page 16, this would be:

tables = camelot.read_pdf(in_dir + file, pages='16', flavor='stream', row_tol=20, strip_text='\n', table_areas=['35,420,380,65'],)

Note: Throughout the post I referenced pages by 'counting' the pages of the file and not by the page numbers printed on each page (the latter one starts at the second page of the document).

Upvotes: 10

KlausGPaul
KlausGPaul

Reputation: 336

Further to Stefano's comment, you need to specify both "stream" and a page number. To get the number of pages, I used PyPDF2, which should be installed by camelot.

In addition, I also suppressed the "no tables found" warning (which is purely optional).

import camelot
import PyPDF2
import warnings

file = 'relacao_medicamentos_rename_2020.pdf'
reader = PyPDF2.PdfFileReader(file)
num_pages = reader.getNumPages()

warnings.simplefilter('ignore')

for page in range(1,num_pages+1):
    tables = camelot.read_pdf(file,flavor='stream',pages=f'{page}')
    if tables.n > 0:
        tables.export(f'relacao_medicamentos_rename_2020.{page}.csv', f='csv', compress=False)

It is hard to tell why this works on one pdf file, but not on the other. There is so many different ways how pdf's are created and written by the authoring software that one has to do trial and error.

Also, not all tables found are actual tables, and the results are a bit messy, and need some cleansing.

Consider doing this straight away with something like:

   tables = camelot.read_pdf(file,flavor='stream',pages=f'{page}')
   for table in tables:
       df = table.df
       # clean data
       ...
       df.to_csv(....)

Upvotes: 2

K J
K J

Reputation: 11940

In many ways there are no such things as human conventional "Tables" in a PDF when converted from say a spreadsheet or Word Processor the PDF writer needs to add such PDF tagging for Audio Readers (Accessibility) and may need to store the page block of data as one single object. Many PDF writers do not bother as the printer should not be disabled :-) Thus every PDF with tabular data will be built totally differently from any other applications tabular method.

Thus often there is no connection with another partial "table" on the next page (so "headings" will not be associated with "columns" on a secondary page). A PDF has NO everyday CONSTRUCTS for "rows" or "columns" those will have in most cases been neutered by writing to a blank canvas.

So when it comes down to extraction ignore the foreground impression of a table and breakdown the body text.

Take this section from Page 189 enter image description here

This is the order of that text placement (remember there are no such things as line feeds only strings of fresh text) and the gap I have shown is just to highlight the challenge in human terms of what should an extractor do with seemingly random order. How could it know to insert those 5 lines after "comprimido"

Exclusões
Medicamento
Situação clínica
Relatório de
Recomendação da
Conitec
Portaria SCTIE
dasabuvir* 250 mg
comprimido
Hepatite C
Relatório nº 429
– Ombitasvir 12,5
mg/ veruprevir 75
mg/ ritonavir 50
mg comprimido e
dasabuvir 250 mg
comprimido para
o tratamento da
hepatite C
Nº 44, de 10/9/2019

ombitasvir +
veruprevir +
ritonavir* 12,5 mg
+ 75 mg + 50 mg
comprimido

Thus the only way to deal with a challenge like this is to consider proximity of text as blocklike and position it exactly the way it is done in the PDF as x columns and y rows but allow for overlaps in both directions, much like "spanning" or "cell merging". For that to work you need a free flowing spreadsheet that's intelligent enough to self adjust.

Upvotes: 0

Related Questions