A Python Library to extract tabular data from PDFs

https://blog.socialcops.com/technology/engineering/camelot-python-library-pdf-data/

by leenasoni99 • 8 years ago

501 99 8 years ago

blog.socialcops.com

berti3• 8 years ago

I hope this is good at extracting register maps from datasheets. That would save a lot of tedious driver work.

vortex_ape16• 8 years ago

Hi berti! I wrote the library and the blog post. Can you point me to some PDFs which have these register maps?

berti3• 8 years ago

Try these: Page 233 http://ww1.microchip.com/downloads/en/DeviceDoc/Atmel-8351-M...

Page 45 https://ae-bst.resource.bosch.com/media/_tech/media/datashee...

Is the library able to handle cells that span multiple columns?

suba_selvandran2• 8 years ago

Hi Berti,

We are building a tool that can extract register map from a data sheet. You may take a look at the beta version of the tool at http://exportrm.soliton.ai/. The tool supports register map table formats similar to ones found on page 14 in https://www.nxp.com/docs/en/data-sheet/PCA9685.pdf datasheet. The table format that you have mentioned in the example is not supported at this time. We would be adding it soon. If you have any questions or would like to share any feedback, feel free to reach out to us at the email id provided on tool web page.

aidos4• 8 years ago

Cool! That's a good intro too.

Many people don't realise the general weird disconnect in PDFs between real content and what you see on the screen that makes it hard to recover source data. In extreme cases you have subset fonts with glyphs ordered completely differently from how they are in the original and no mapping back to the character they represent. Then the graphics stream is instructions to draw glyphs at coordinates. As you can imagine it's quite a battle to get back to something "raw" (assuming you even had fonts to start with).

vortex_ape16• 8 years ago

The extreme cases (where there is no/incorrect mapping between the glyph and the character they represent) are a real pain! This mapping is stored as a ToUnicode map inside the PDF. In the past I've used OCR to handle such cases but I'm planning to create an experimental interface where anyone can modify the ToUnicode map. The challenge would be to make the modifications automated/user friendly.

aidos4• 8 years ago

Wicked. Sometimes the ToUnicode map is missing so we actually rebuild it ourselves from other information we find in the PDF.

aasasd14• 8 years ago

If PDF is just "characters at coordinates" then getting the data out seems to require all functions of an OCR engine outside of character recognition per se (namely, layout detection). And with botched fonts, you essentially need the full package.

I so much want to see the day when PDF is dead like Flash.

spaceandshit• 8 years ago

And replaced with what?

vortex_ape16• 8 years ago

Adobe released a way to attach data tables with PDFs. But I think it hasn't been adopted fully, since many organizations that release open data as PDFs don't tag the accompanying data tables. https://www.w3.org/TR/WCAG20-TECHS/PDF6.html

TeMPOraL• 8 years ago

Sounds like OCR is really the universal method. I'm guessing it shouldn't even be as hard as full-blown OCR, since you have access to fonts used, so you can render known characters as a reference and pretty much run a per-pixel matching on rendered PDF.

aidos4• 8 years ago

I think it's probably Stockholm syndrome but I quite enjoying digging into PDFs. There are actually other ways of embedding the text where you have more information to go on. "Characters at coordinates" is a particularly rough hand to be dealt in a PDF (though it definitely happens).

svat2• 8 years ago

The great thing about PDF, even the reason for its existence and adoption, is that a (valid) PDF file will look exactly the same — the same characters in the same fonts at exactly the same positions on every page — on any printer or display across the world, and across time. With HTML (what you propose) it is hard even to get something to look the same at different browser window widths, let alone different devices or different versions of browsers.

Sometimes that's what you want (and when the visual appearance is not important, it may make sense to not use PDF), but I definitely wouldn't want to see PDF “dead”.

agumonkey• 8 years ago

PDF (especially full spec'd PDF) seems bad, but I never like reading epub or other formats. PDFs are the static builds of documents.

pwg• 8 years ago

all functions of an OCR engine outside of character recognition per se

Actually, depending upon how 'obfuscated' an author was attempting to be, you might need that OCR engine itself.

PDF allows for defining arbitrary mappings from byte values to font glyphs. So one could define byte value 32 (decimal, usually ASCII space) to actually map to printing, say, a capitol letter Z instead. One is supposed to provide a reverse mapping table when one does this that says "a decimal 32 byte prints a capitol letter Z" to allow for search and extraction purposes. But the PDF spec. does not require this reverse table be present.

So it is quite possible to randomly assign font glyphs to arbitrary byte values, and omit the reverse mapping table. The result would be that extracting data back out of that PDF results in garbage if one does not know beforehand what the mapping from byte value to glyph was.

So, if a 'bad actor' did this, one's only recourse to retrieving data would be to rasterize the PDF to a bitmap, then OCR the resulting bitmap to extract the content back out.

maxxxxx• 8 years ago

"I so much want to see the day when PDF is dead like Flash. "

Totally agree. A while ago I had to write code to import a ton of PDF files and it was just infuriating to realize that we have data in highly structured documents, throw all structure away to create a PDF and then we somehow have to divine that structure back from the PDF with enormous effort and only partial success. It's just a horrible, horrible file format for what it's used now.

ppod• 8 years ago

This is a really good example of how to briefly introduce/sell a library. What it does, why, how, how to install it, with concrete examples.

vortex_ape16• 8 years ago

The API and docs (on which the blog post built upon) were inspired from pandas and requests!

danimolina• 8 years ago

This is a very interesting software. In research community still many results are only in pdf tables in papers, so obtaining them in dataframe is very useful, good job!. By the way, I would like export also in Excel files in the command line.

vortex_ape16• 8 years ago

Hey danimolina! You can export the data into an excel by specifying it as the export format, Camelot comes with a command-line interface too! https://camelot-py.readthedocs.io/en/master/user/cli.html#cl...

You can simple do: camelot --output data.xlsx --format excel lattice input.pdf (lattice can be replaced with stream based on the type of tables in your PDF)

amelius• 8 years ago

Wouldn't it be easier and more generic to have an OCR solution for this task?

vortex_ape16• 8 years ago

Hey amelius! Though OCR would provide a generic solution, it would be an overkill for text-based PDFs. I'm working on getting a OCR solution up since there's still a lot of data that is trapped inside scanned PDFs and not text-based ones.

If you have any pointers in the OCR route, do suggest them here, or on this GitHub issue! https://github.com/socialcopsdev/camelot/issues/101

kumartanmay4• 8 years ago

Hey vortex_ape, we're also working on extracting data trapped inside scanned PDFs and recently, we've begun to get good results using DL algos. I am based in Gurugram, would you like to catch up and exchange experiences?

pjc50• 8 years ago

OCR is less reliable than looking at the character data directly, if it's available.

aasasd14• 8 years ago

It seems that generally you'd want all functions of an OCR engine aside from the character recognition itself—namely layout detection. (And sometimes you'll need the character recognition too.)

I'd bet that commercial OCR packages that are long in the game have unified code for these functions between regular OCR and PDF processing.

sandGorgon2• 8 years ago

Quick suggestion - you should integrate the functions to extract signature data inside PDF. This is a huge issue and everyone has to write their own.

for example, this is my sample piece of code to extract data from Aadhaar signed PDF https://pastebin.com/dg8p98T1

vortex_ape16• 8 years ago

Thanks for the suggestion sandGorgon! Can you also point me to an example of a PDF with signature data?

sandGorgon2• 8 years ago

unfortunately i cannot share without running afoul of all the laws out there. but you can create your own here - https://app.digio.in/#/authenticate

squaresmile• 8 years ago

This sounds neat. Thanks for the work, vortex_ape and others. When I last needed this, I used tabula via tabula-py. Tried camelot on the PDF [0] I worked on and unfortunately the default option returned less-workable dataframe than tabula-py. I think it's just the area detection of stream and you are working on it anyway so I'm really looking forward to see the results.

btw, I think the pip install requirements missed opencv-python (on Windows?). And in this doc [2], it should be "top left and bottom right" instead of "left-top and right-bottom".

[1] https://www.boj.or.jp/en/statistics/set/kess/release/2018/ke...

[2] https://camelot-py.readthedocs.io/en/master/user/advanced.ht...

vortex_ape16• 8 years ago

Hey squaresmile! Yes, right now table detection with Stream doesn't work nicely if the table is not present on the full page, for which you can use the table_area kwarg from [2].

You should use "pip install camelot-py[all]" to install Camelot (which will install opencv-python too). I had to take it out of the requirements since it wasn't available in any conda channels while I was creating the conda package. I'm looking to remove opencv as a requirement altogether by either vendorizing the opencv code that is being used inside Camelot or reimplementing the code using something lightweight like pillow.

Thanks for the catch in [2], I'll correct it!

kumartanmay4• 8 years ago

Are you also working on extracting tabular data from scanned image files?

jacquesm2• 8 years ago

Oh that is so timely. I've been putting that part of a pipeline I built off for a while due to the complexity and now I can just plug this in. Super neat. Thank you very much!

vortex_ape16• 8 years ago

What does this pipeline do and what software have you used to implement it?

I have used Airflow in the past to create ETL pipelines, and plugged in Camelot in one of them to extract tables from PDFs. I also wrote a blog post about it in case you might be interested. https://hackernoon.com/how-to-create-a-workflow-in-apache-ai...

jacquesm2• 8 years ago

Compress scientific papers.

Thank you for the pointers!

KhalilK• 8 years ago

A few months ago I was looking for a similar solution but couldn't find one that handles empty cells very well. I ended up writing my own program[0] that is specific to my files' layout.

This library works perfectly and could've saved me a lot of time! Looking at some of the source code, we used similar logic to parse the tables. Pretty neat!

0.https://github.com/khllkcm/pdf2calendar

vortex_ape16• 8 years ago

Will check out pdf2calendar!

johnyesberg• 8 years ago

https://github.com/invoice-x/invoice2data is another one.

decasteve• 8 years ago

This is nice. I do quite a bit of tabular data extraction and pdf tables are often a sticking point. It is absolutely correct in describing it as a "fuzzy" problem.

My go-to solution has been 'pdftotext -layout' with a bit of hackery before giving it to pandas.read_fwf. That usually gets me 80% of the way there 80% of the time. The upside is that this tends to fail "better" than some other options.

I look forward to kicking-the-tires with this on my test cases.

vortex_ape16• 8 years ago

Do submit bugs on GitHub if you face any issues! https://github.com/socialcopsdev/camelot

plaidfuji2• 8 years ago

However, OpenCV’s Hough Line Transform returned only line equations.

Did you try HoughLinesP? https://docs.opencv.org/2.4/modules/imgproc/doc/feature_dete...

Returns line segment endpoints with a probabilistic Hough Transform. I'm fully confident your solution works, just wondering if you tried this and why it was rejected.

vortex_ape16• 8 years ago

Hi plaidfuji! I did try HoughLinesP during experimentation. I vaguely remember (since this was almost 2 years back) getting the actual line segment as a combination of multiple smaller line segments in all cases (which could then be combined to form the actual segment using some heuristic). It came down to getting the actual table line segment out which a combination morphological transformations and cv2.findContours provided (without the need for another combining step).

plaidfuji2• 8 years ago

Interesting. I noticed you mentioned below that you're trying to get rid of OpenCV as a dependency - that's really tough. I came from a Matlab background where image processing was really well-packaged and Python is a total mess.

If you managed to vendor a small portion of OpenCV that contained image i/o, basic colorspace conversion, thresholding, scaling/rotating, shape drawing/insertion, HoughLines and findContours, I think you could release that as its own package and it would be quite popular. OpenCV is such a bloated dependency...

nerdponx• 8 years ago

Interesting. I've used Tabula [0] in the past with great success. I wonder how this compares.

[0] https://github.com/tabulapdf/tabula

chedar• 8 years ago

They have a detailed comparison with other tools (including Tabula) in the wiki:

https://github.com/socialcopsdev/camelot/wiki/Comparison-wit...

smittywerben• 8 years ago

This just worked for me, thank you!

radarsat1• 8 years ago

When using Stream, tables aren’t autodetected. Stream treats the whole page as a single table

I've often wondered if image semantic segmentation methods as used in the ML community could successfully identify things like "there is a table (or figure) here, it's not part of the main text". I mean, it seems that humans should be able to do this even without reading the text so I don't see why a CNN couldn't.

worldexplorer• 8 years ago

Yes it should work. Definitely worth trying.

guyinthebackr0w• 8 years ago

I'm curious why the authors didn't contribute this directly to Tabula instead.

andrew_chris• 8 years ago

Is there any decent tool for tabluar data extraction from scanned PDFs?

kumartanmay4• 8 years ago

Hey andrew_chris, we're working on it and will be interested to help you. Please contact me: tanmay [at] inkredo [dot] in

sdiepend• 8 years ago

An alternative: https://github.com/jsvine/pdfplumber

suba_selvandran2• 8 years ago

Yeah. pdfplumber is also good for digital pdfs. Curious to know the advantages of camelot over this.!

danso4• 8 years ago

Great work and write up! HN submissions about PDF extraction seem to be as reliably popular as threads mentioning bees or bashing Mongo, which I guess goes to show how pervasive a problem it is.

JDWolf• 8 years ago

So many times I have wanted to get this type of data. Visa would send reporting this way and it would have to be manually copied over. They offered CSV but there were extra charges associated. There were some pretty good libraries for paragraph text extraction but the graphs were too tough to deal with.

fredley• 8 years ago

Can't wait to try this out with Percollate!

vortex_ape16• 8 years ago

Doesn't Percollate save web pages as PDFs? If you have a web page with tables, you can directly use pandas.read_html to extract them!

burtonator• 8 years ago

You should also check out pdf.js:

We use it in Polar:

https://getpolarized.io/

for our PDF management.

It's a pretty robust library and it renders everything on canvas BUT you also get the raw text in the DOM so you can play with it more as an API for managing PDFs.

REALLY nice to be able to use web standards when working with pdf.js.

The downside is that the graphics are rendered to canvas so you're only really getting an image.

just_myles• 8 years ago

I'm always skeptical of these kind of libraries. Whenever I try to use them, it ends up feeling like a broken promise.

kumartanmay4• 8 years ago

Ever researched why it breaks?

catacombs• 8 years ago

A Python library to do this is cool, but there's already Tabula: https://tabula.technology/

taylorwc• 8 years ago

They address Tabula in the post:

The first tool that we tried was Tabula, which has nice user and command-line interfaces, but it either worked perfectly or failed miserably. When it failed, it was difficult to tweak the settings — such as the image thresholding parameters, which influence table detection and can lead to a better output.

A Python Library to extract tabular data from PDFs

Related Stories