3 ms·

Extracting deeply useful information from tens/hundreds/thousands of thousands of scanned PDFs in a way that dumps its information, regardless of structure (eg,

by chaps 4y ago

Extracting deeply useful information from tens/hundreds/thousands of thousands of scanned PDFs in a way that dumps its information, regardless of structure (eg, tables, text), into a relational database that's (mostly) trivially queryable and repeatable. Preferably open source. This is such a hard problem right now.

amenod 4y ago
That sounds doable, but... why opensource? Does this mean that people / companies are not prepared to pay for the product or service?
- chaps 4y ago
  I have around 2 million pages from FOIA requests that need information systematically extracted and I'm not alone in this problem. The costs for the systematization of many pages will be prohibitive. The public good of having a resource like this available to the public for free is beyond unimaginable as far as I'm concerned.
  - Fizzz 4y ago
    How do you currently extract info from the FOIA request pages? What kind of info do you look for? Just thinking how you could standardise this
    - chaps 4y ago
      Mostly tesseract, or uploading it through documentcloud and running manual searches. I do a combination of data analysis and spend many hours reading through documents. Sometimes I use unix tools like grep/awk/etc, sometimes I use SQL. If the PDF isn't scanned, I use tabula for csv extraction, but if it's scanned it becomes a silly ordeal. Mind you, I'm not exactly looking for advice here. It's a supremely difficult problem and gut-ideas more often than not don't pan out.
IceMetalPunk 4y ago
When you say "regardless of structure" -- if it's a relational database, that inherently implies a set structure. Or did you mean the information is consistent enough to be represented in one relational structure, but is presented in the PDFs in different formats?
- chaps 4y ago
  Honestly, I don't know what it would look like, but being able to query would be deeply important. But what I can say is that a lot of the PDFs I work with are auto-generated as PDF forms using queries, after the information was likely inserted into a relational database during some esoteric transcription step. Meaning, the information that formed the PDFs very likely come from a relational database and an inversion back to its original relational form is probably the convenient form. Whether that means it turns to 40 tables, that's fine, so long as it's relational and a query can be written.
  - rudyj03 4y ago
    If querying is the main goal, ingesting the pdf directly into something like elasticsearch or splunk would be much simpler. This of course doesn't meet the checkbox of being open source though.
    - chaps 4y ago
      This is a very... engineer... approach. Not something that I would want to ever do with the intent of exploratory analysis and eventual sharing.
fffobar 4y ago
> Extracting deeply useful information This right there is the difficult part - what do you mean exactly? I cannot come up with anything better than search, as in like Google search. And they did it for books already, it's seriously good.
- chaps 4y ago
  A big problem that happens with FOIA requests is that you're often sent data in the form of a spreadsheet that was converted to a PDF. And then scanned. For thousands of pages. Solve that generally so that you can insert all of that data into a postgres database, with sensible indexes.
pani5ue 4y ago
I'm doing this for a customer right now! Using Textract from Amazon to do the OCR. I dump the raw json in a SQLite database. Information extraction is trickier. I extract useful things like lines and pages, locations of lines, set up various columns and now can search the SQLite db either with python code or SQL queries. There's of course cost for running textract OCR on AWS. There are open source solutions like Tesseract
- chaps 4y ago
  Oh yes, I'm very familiar with these. All these do though is extract information, but don't immediately make them useful. So there's a massive gulf of a middle-step that's not yet done. Textract gets close...ish to that, but it's prohibitively expensive.
  - otoburb 4y ago
    Even with Amazon Textract, the middle step to curate extracted information into some form of meaning is still missing. Didn't realize this is still an unsolved problem.
    - chaps 4y ago
      Very much so. Here's an example document I work with that has information that's difficult to extract from: https://s3.documentcloud.org/documents/6929951/CRID-1061543.pdf https://s3.documentcloud.org/documents/6929951/CRID-1061543.... Lots of missing context from these sheets that has to be interpreted (ie, how do you taxonomize each field of information?). Then asking questions on top of these documents is a step on top: "is the allegation about sexual violence?", "What is the name and rank of the person being accused?", "Is anything anomalous in the review process?", "Has this person's rank changed in the past 5 years?" etc etc. Now expand this problem to hundreds of thousands of different types of document.
      - pani5ue 4y ago
        If you haven't yet, you should look at the full text search capabilities of SQLite and postgres. Could simplify your search part of the workflow a bit
ldenoue 4y ago
Check this project from AllenAI called PdfFigures: they also extract tables https://github.com/allenai/pdffigures2 https://github.com/allenai/pdffigures2

by chaps 4y ago

amenod 4y ago

That sounds doable, but... why opensource? Does this mean that people / companies are not prepared to pay for the product or service?

chaps 4y ago
I have around 2 million pages from FOIA requests that need information systematically extracted and I'm not alone in this problem. The costs for the systematization of many pages will be prohibitive. The public good of having a resource like this available to the public for free is beyond unimaginable as far as I'm concerned.
- Fizzz 4y ago
  How do you currently extract info from the FOIA request pages? What kind of info do you look for? Just thinking how you could standardise this
  - chaps 4y ago
    Mostly tesseract, or uploading it through documentcloud and running manual searches. I do a combination of data analysis and spend many hours reading through documents. Sometimes I use unix tools like grep/awk/etc, sometimes I use SQL. If the PDF isn't scanned, I use tabula for csv extraction, but if it's scanned it becomes a silly ordeal. Mind you, I'm not exactly looking for advice here. It's a supremely difficult problem and gut-ideas more often than not don't pan out.

IceMetalPunk 4y ago

When you say "regardless of structure" -- if it's a relational database, that inherently implies a set structure. Or did you mean the information is consistent enough to be represented in one relational structure, but is presented in the PDFs in different formats?

chaps 4y ago
Honestly, I don't know what it would look like, but being able to query would be deeply important. But what I can say is that a lot of the PDFs I work with are auto-generated as PDF forms using queries, after the information was likely inserted into a relational database during some esoteric transcription step. Meaning, the information that formed the PDFs very likely come from a relational database and an inversion back to its original relational form is probably the convenient form. Whether that means it turns to 40 tables, that's fine, so long as it's relational and a query can be written.
- rudyj03 4y ago
  If querying is the main goal, ingesting the pdf directly into something like elasticsearch or splunk would be much simpler. This of course doesn't meet the checkbox of being open source though.
  - chaps 4y ago
    This is a very... engineer... approach. Not something that I would want to ever do with the intent of exploratory analysis and eventual sharing.

fffobar 4y ago

> Extracting deeply useful information This right there is the difficult part - what do you mean exactly? I cannot come up with anything better than search, as in like Google search. And they did it for books already, it's seriously good.

chaps 4y ago
A big problem that happens with FOIA requests is that you're often sent data in the form of a spreadsheet that was converted to a PDF. And then scanned. For thousands of pages. Solve that generally so that you can insert all of that data into a postgres database, with sensible indexes.

pani5ue 4y ago

I'm doing this for a customer right now! Using Textract from Amazon to do the OCR. I dump the raw json in a SQLite database. Information extraction is trickier. I extract useful things like lines and pages, locations of lines, set up various columns and now can search the SQLite db either with python code or SQL queries. There's of course cost for running textract OCR on AWS. There are open source solutions like Tesseract

chaps 4y ago
Oh yes, I'm very familiar with these. All these do though is extract information, but don't immediately make them useful. So there's a massive gulf of a middle-step that's not yet done. Textract gets close...ish to that, but it's prohibitively expensive.
- otoburb 4y ago
  Even with Amazon Textract, the middle step to curate extracted information into some form of meaning is still missing. Didn't realize this is still an unsolved problem.
  - chaps 4y ago
    Very much so. Here's an example document I work with that has information that's difficult to extract from: https://s3.documentcloud.org/documents/6929951/CRID-1061543.pdf https://s3.documentcloud.org/documents/6929951/CRID-1061543.... Lots of missing context from these sheets that has to be interpreted (ie, how do you taxonomize each field of information?). Then asking questions on top of these documents is a step on top: "is the allegation about sexual violence?", "What is the name and rank of the person being accused?", "Is anything anomalous in the review process?", "Has this person's rank changed in the past 5 years?" etc etc. Now expand this problem to hundreds of thousands of different types of document.
    - pani5ue 4y ago
      If you haven't yet, you should look at the full text search capabilities of SQLite and postgres. Could simplify your search part of the workflow a bit

ldenoue 4y ago

Check this project from AllenAI called PdfFigures: they also extract tables https://github.com/allenai/pdffigures2 https://github.com/allenai/pdffigures2