6 ms·

Code to transform Hillary's emails from raw PDF documents to a SQLite database

94 points by shagunsodhani 11y ago

danso 11y ago
FWIW, the Wall Street Journal's data team has also posted its repo of code for processing the Clinton emails: https://github.com/wsjdata/clinton-email-cruncher https://github.com/wsjdata/clinton-email-cruncher Kind of fun to compare methods...converting PDFs can be such drudge work sometimes. I'm interested in the data-cleaning/name-resolution aspect...though that is also another deadly boring programming task.
azinman2 11y ago
Awesome. I didn't know the raw data was available anywhere publicly. I thought only journalists/gov had it
- generic_user 11y ago
  Not all of it is. Only small parts of it.
honksillet 11y ago
Why are the emails being released as PDF's?
- brc 11y ago
  It was quite a FU - I read originally they were delivered as printed emails. Not good form at all.
- spikels 11y ago
  Because Hillary wants to make it hard to analyze them. Drug companies used the same technique to hide their relationships with doctors. https://www.propublica.org/nerds/item/doc-dollars-guides-collecting-the-data https://www.propublica.org/nerds/item/doc-dollars-guides-col...
  - danso 11y ago
    I was the original programmer for that project...while I didn't enjoy spending part of my late 20s learning about PDF to text conversions...I wouldn't necessarily attribute their use of PDF to devious motives. It's easy to forget sometimes that much of the world still operates on paper...and electronic documents that translate directly to paper have some value. These financial records were disclosed as part of legal settlements, and they were also used by hospital compliance officers...While obviously just sending out CSVs or Excel sheets would be great, it adds a possible breakage point between the company and its audience...i.e. hospital officers complaining that the spreadsheet looks funky on their version of Excel or whatever the hell they're using. The thing that makes PDFs so frustrating to parse is what makes them so "secure" and stable to end-users. That's why I advise my students to not just send URLs and web-clips to potential employers, but to have PDF versions of their resumes and work because inevitably, the "boss" wants a printout on his desk...and you don't want his office's shitty printer (and/or whatever IE browser rendered the HTML) to leave a bad first impression. And let's face it, if you are an end-user who is not a programmer, a PDF is very convenient. You can even do Ctrl-F to look for things...and if you aren't a programmer, that's all the search-power you need to do your compliance work. After the investigation was published, I fielded a lot of questions from auditors who were convinced I had built some amazing proprietary search algorithm...such people just don't know the gap between doing Ctrl-F versus "SELECT * FROM doctors where LAST_NAME like ..." :) There were a couple of companies who arguably went out of their way to obfuscate their records. They had built websites that consisted of Flash widgets designed to look and act like a (crappy) HTML table...except with Flash, you can prevent the user from select/copy/etc. Unfortunately, the developers of those widgets never told the companies that their Flash containers operated by making XHR requests to external XML/JSON files...so these companies ended up being the easiest to gather the data from.
    - brc 11y ago
      Handling PDFs and OCR is always troublesome. But in this case, if I understand you correctly, the original source documents were PDF? That they were distributed as PDF for all the reasons you state? That doesn't apply in this case - the original documents were not PDF, and had no reason to be PDF except to make it more difficult.
- monochromatic 11y ago
  She probably wants to delay the inevitable.
- danso 11y ago
  This is standard procedure...the emails go through a vetting process to redact things that are not meant to be released to the public. This can mean anything from classified information to private information that was sent to an official (medical records, personal information in a forwarded application, etc). The PDF workflow is probably the most efficient way to do this...I don't mean efficient in terms of what programmers think of as efficient...I mean efficient in that the vetting/censoring process involves a good number of officials who are reviewing the documents which probably involves some back-and-forth, for which paper is a pretty decent medium for. Also, not all/many of these officials are versed in digital editing workflows [1]. I think in some situations, the emails are printed, physically redacted (i.e. black marker), then scanned and converted to PDF. Keep in mind that many requesters of government documents are perfectly fine with printable documents, even if such documents are not suited for machine parsing. [1] Government officials have been burned before when using what they thought was a PDF redaction tool...i.e. using the box-drawing-tool and drawing a black box...was not actually redaction...Governor Blagojevich's case comes to mind: http://www.wbez.org/blog/update-blagojevich-lawyers-created-pdf-document-faulty-redacted-statements http://www.wbez.org/blog/update-blagojevich-lawyers-created-...
  - brc 11y ago
    How is it not easier to programmatically find/replace specific names/emails and replace with (redacted). In this day and age it takes a lot more work to print, manually redact and scan than it does to digitally redact permanently. Your story about redacting PDFs is interesting these were not originally PDFs, they were emails. I can't see any other reason for delivering documents as printed/scanned PDF than to make it more difficult to analyse considering they were originally not paper or PDF. Occams Razor applies here.
    - BinaryIdiot 11y ago
      > How is it not easier to programmatically find/replace specific names/emails and replace with (redacted). Because this isn't possible. How are you going to account for any possible misspellings, accidental spacing or other unknown unknowns? You can't. To properly disseminate things the documents have to run through actual people. Sure, people fuck up things too but they can be much more precise when it comes to processing real, human language than any computer right now. This is the process the government takes. Convert to PDF, redact, release. > I can't see any other reason for delivering documents as printed/scanned PDF than to make it more difficult to analyse considering they were originally not paper or PDF. Occams Razor applies here. You are misusing the meaning of occam's razor. This is actually the easiest, most correct way to disseminate possibly sensitive information from the government that they have available. It sucks. If you can write something better then do it; you can make a ton of money and save the government a whole lot of time. But it better be really, really accurate.
burritofanatic 11y ago
Python 3!
zac1944 11y ago
is stealing email becoming legal? no matter who they belong to
- deleted 11y ago
  [deleted]
rebootthesystem 11y ago
What a sad moment in US history. "What do you mean? Wipe it with a cloth?" Translation, I have nothing but contempt for the American people. Any one of us would have been in serious legal trouble for much less. Nice code, BTW. How is pdftotext when compared to PyPDF2?
- venomsnake 11y ago
  Think of it as the Petreus doctrine ...
- generic_user 11y ago
  It is clearly an age of open contempt and disregard for the citizenry by by a political class which sees itself as having a hereditary right to rule.
mahmud 11y ago
OT: "Why do we always refer to female public figures by their first name, and male ones by their last?" I heard/read that question somewhere, and it stuck with me, can't stop seeing it. And this is a fine example of that. Google results: "Clinton email scandal" 41M results. "Hillary email scandal" 48M results. Yes, I do realize hers is a special case, and it's necessary to use her first name to disambiguate wrt to Bill. But the phenomenon exists. Just some thing to keep in mind. [Edit: "In the tennis commentary, women athletes were called by only their first names 52.7% of the time, while men were referred to by only their first names 7.8% of the time." http://www.la84.org/gender-stereotyping-in-televised-sports/ http://www.la84.org/gender-stereotyping-in-televised-sports/ ]
- Leszek 11y ago
  To be fair, there was another famous Clinton that could make this confusing. I've never heard of Condoleezza Rice, Sarah Palin or Angela Merkel being called by just their first name, although I admit that I don't often hear them being called by just their last name either (maybe except Merkel).
  - qq66 11y ago
    Condoleezza Rice was often referred to as "Condi" in the media. I think a lot of it has to do with information theory: there are two famous Clintons, several famous Rices, and hundreds of famous Sarahs.
    - Leszek 11y ago
      I stand corrected on that one, I'd personally only ever heard of her referred to by her full name, but that's just my own ignorance.
  - eru 11y ago
    And that might be a German peculiarity. Co-workers who have known each other for longer than they have known their spouses address each other as Herr Schulze and Frau Schmidt.
- 11y ago
tlack 11y ago
Are there any open source tools that would slurp in content like this and develop its own sense of relationships in the data, that I could then explore by hand? Bedarra's Text Analyzer[1] kinda floored me and I'd like to use something similar for various tasks, if there was something good and free. [1] http://www.bedarra.com/movies/textAnalyserMovie.html http://www.bedarra.com/movies/textAnalyserMovie.html
- AdieuToLogic 11y ago
  > Are there any open source tools that would slurp in content like this ... Yes, tesseract[1] can do a pretty good job. Here[2] is a blog post which describes using it to perform OCR on PDF's. As for searching the PDF contents, Solr[3] might be what you are looking for instead. 1 - https://github.com/tesseract-ocr/tesseract https://github.com/tesseract-ocr/tesseract 2 - http://fransdejonge.com/2012/04/ocr-text-in-pdf-with-tesseract/ http://fransdejonge.com/2012/04/ocr-text-in-pdf-with-tessera... 3- http://stackoverflow.com/questions/6694327/indexing-pdf-with-solr http://stackoverflow.com/questions/6694327/indexing-pdf-with...
tiglionabbit 11y ago
I don't get it. She wasn't supposed to use a private email server because it could be compromised and leak classified information. And now they're releasing the emails to the public. How does this make any sense?
- nhebb 11y ago
  I haven't followed all the details of this saga, but my understanding is that this release is the result of a Freedom of Information Act (FOIA) request for the emails. The emails were vetted by the State Department, and only those that do not have security clearance restrictions are being released to the public.
- bdcravens 11y ago
  The FOIA request excludes sensitive info, so that's not at risk here. However, had she not used a private server, no one would care.
- vilhelm_s 11y ago
  Part of the controversy is the concern that classified information may not have been stored securely, but another concern is that Clinton may have deleted emails which legally must be preserved (the federal records act). Compare with the 2013 IRS controversy, which could not be properly investigated because Lois Lerner's hard drive had been erased.
sambrand 11y ago
With zero assurance that all work emails are truly in the dump, I wonder if it will be at all possible to analyze gaps in it (ie. questions by Hilary without a response, regularly recurring interactions missed) to make educated guesses about what confidential emails she purged.
- triggercut 11y ago
  IIRC "Exploring Everyday Things with R and Ruby" By Sau Sheong Chang does something at least similar with the Enron email database.
hoju 11y ago
Has anyone created a web interface yet for browsing these emails?

94 points by shagunsodhani 11y ago

danso 11y ago

FWIW, the Wall Street Journal's data team has also posted its repo of code for processing the Clinton emails: https://github.com/wsjdata/clinton-email-cruncher https://github.com/wsjdata/clinton-email-cruncher Kind of fun to compare methods...converting PDFs can be such drudge work sometimes. I'm interested in the data-cleaning/name-resolution aspect...though that is also another deadly boring programming task.

azinman2 11y ago

Awesome. I didn't know the raw data was available anywhere publicly. I thought only journalists/gov had it

generic_user 11y ago
Not all of it is. Only small parts of it.

honksillet 11y ago

Why are the emails being released as PDF's?

brc 11y ago
It was quite a FU - I read originally they were delivered as printed emails. Not good form at all.
spikels 11y ago
Because Hillary wants to make it hard to analyze them. Drug companies used the same technique to hide their relationships with doctors. https://www.propublica.org/nerds/item/doc-dollars-guides-collecting-the-data https://www.propublica.org/nerds/item/doc-dollars-guides-col...
- danso 11y ago
  I was the original programmer for that project...while I didn't enjoy spending part of my late 20s learning about PDF to text conversions...I wouldn't necessarily attribute their use of PDF to devious motives. It's easy to forget sometimes that much of the world still operates on paper...and electronic documents that translate directly to paper have some value. These financial records were disclosed as part of legal settlements, and they were also used by hospital compliance officers...While obviously just sending out CSVs or Excel sheets would be great, it adds a possible breakage point between the company and its audience...i.e. hospital officers complaining that the spreadsheet looks funky on their version of Excel or whatever the hell they're using. The thing that makes PDFs so frustrating to parse is what makes them so "secure" and stable to end-users. That's why I advise my students to not just send URLs and web-clips to potential employers, but to have PDF versions of their resumes and work because inevitably, the "boss" wants a printout on his desk...and you don't want his office's shitty printer (and/or whatever IE browser rendered the HTML) to leave a bad first impression. And let's face it, if you are an end-user who is not a programmer, a PDF is very convenient. You can even do Ctrl-F to look for things...and if you aren't a programmer, that's all the search-power you need to do your compliance work. After the investigation was published, I fielded a lot of questions from auditors who were convinced I had built some amazing proprietary search algorithm...such people just don't know the gap between doing Ctrl-F versus "SELECT * FROM doctors where LAST_NAME like ..." :) There were a couple of companies who arguably went out of their way to obfuscate their records. They had built websites that consisted of Flash widgets designed to look and act like a (crappy) HTML table...except with Flash, you can prevent the user from select/copy/etc. Unfortunately, the developers of those widgets never told the companies that their Flash containers operated by making XHR requests to external XML/JSON files...so these companies ended up being the easiest to gather the data from.
  - brc 11y ago
    Handling PDFs and OCR is always troublesome. But in this case, if I understand you correctly, the original source documents were PDF? That they were distributed as PDF for all the reasons you state? That doesn't apply in this case - the original documents were not PDF, and had no reason to be PDF except to make it more difficult.
monochromatic 11y ago
She probably wants to delay the inevitable.
danso 11y ago
This is standard procedure...the emails go through a vetting process to redact things that are not meant to be released to the public. This can mean anything from classified information to private information that was sent to an official (medical records, personal information in a forwarded application, etc). The PDF workflow is probably the most efficient way to do this...I don't mean efficient in terms of what programmers think of as efficient...I mean efficient in that the vetting/censoring process involves a good number of officials who are reviewing the documents which probably involves some back-and-forth, for which paper is a pretty decent medium for. Also, not all/many of these officials are versed in digital editing workflows [1]. I think in some situations, the emails are printed, physically redacted (i.e. black marker), then scanned and converted to PDF. Keep in mind that many requesters of government documents are perfectly fine with printable documents, even if such documents are not suited for machine parsing. [1] Government officials have been burned before when using what they thought was a PDF redaction tool...i.e. using the box-drawing-tool and drawing a black box...was not actually redaction...Governor Blagojevich's case comes to mind: http://www.wbez.org/blog/update-blagojevich-lawyers-created-pdf-document-faulty-redacted-statements http://www.wbez.org/blog/update-blagojevich-lawyers-created-...
- brc 11y ago
  How is it not easier to programmatically find/replace specific names/emails and replace with (redacted). In this day and age it takes a lot more work to print, manually redact and scan than it does to digitally redact permanently. Your story about redacting PDFs is interesting these were not originally PDFs, they were emails. I can't see any other reason for delivering documents as printed/scanned PDF than to make it more difficult to analyse considering they were originally not paper or PDF. Occams Razor applies here.
  - BinaryIdiot 11y ago
    > How is it not easier to programmatically find/replace specific names/emails and replace with (redacted). Because this isn't possible. How are you going to account for any possible misspellings, accidental spacing or other unknown unknowns? You can't. To properly disseminate things the documents have to run through actual people. Sure, people fuck up things too but they can be much more precise when it comes to processing real, human language than any computer right now. This is the process the government takes. Convert to PDF, redact, release. > I can't see any other reason for delivering documents as printed/scanned PDF than to make it more difficult to analyse considering they were originally not paper or PDF. Occams Razor applies here. You are misusing the meaning of occam's razor. This is actually the easiest, most correct way to disseminate possibly sensitive information from the government that they have available. It sucks. If you can write something better then do it; you can make a ton of money and save the government a whole lot of time. But it better be really, really accurate.

burritofanatic 11y ago

Python 3!

zac1944 11y ago

is stealing email becoming legal? no matter who they belong to

deleted 11y ago
[deleted]

rebootthesystem 11y ago

What a sad moment in US history. "What do you mean? Wipe it with a cloth?" Translation, I have nothing but contempt for the American people. Any one of us would have been in serious legal trouble for much less. Nice code, BTW. How is pdftotext when compared to PyPDF2?

venomsnake 11y ago
Think of it as the Petreus doctrine ...
generic_user 11y ago
It is clearly an age of open contempt and disregard for the citizenry by by a political class which sees itself as having a hereditary right to rule.

mahmud 11y ago

OT: "Why do we always refer to female public figures by their first name, and male ones by their last?" I heard/read that question somewhere, and it stuck with me, can't stop seeing it. And this is a fine example of that. Google results: "Clinton email scandal" 41M results. "Hillary email scandal" 48M results. Yes, I do realize hers is a special case, and it's necessary to use her first name to disambiguate wrt to Bill. But the phenomenon exists. Just some thing to keep in mind. [Edit: "In the tennis commentary, women athletes were called by only their first names 52.7% of the time, while men were referred to by only their first names 7.8% of the time." http://www.la84.org/gender-stereotyping-in-televised-sports/ http://www.la84.org/gender-stereotyping-in-televised-sports/ ]

Leszek 11y ago
To be fair, there was another famous Clinton that could make this confusing. I've never heard of Condoleezza Rice, Sarah Palin or Angela Merkel being called by just their first name, although I admit that I don't often hear them being called by just their last name either (maybe except Merkel).
- qq66 11y ago
  Condoleezza Rice was often referred to as "Condi" in the media. I think a lot of it has to do with information theory: there are two famous Clintons, several famous Rices, and hundreds of famous Sarahs.
  - Leszek 11y ago
    I stand corrected on that one, I'd personally only ever heard of her referred to by her full name, but that's just my own ignorance.
- eru 11y ago
  And that might be a German peculiarity. Co-workers who have known each other for longer than they have known their spouses address each other as Herr Schulze and Frau Schmidt.
11y ago

tlack 11y ago

Are there any open source tools that would slurp in content like this and develop its own sense of relationships in the data, that I could then explore by hand? Bedarra's Text Analyzer[1] kinda floored me and I'd like to use something similar for various tasks, if there was something good and free. [1] http://www.bedarra.com/movies/textAnalyserMovie.html http://www.bedarra.com/movies/textAnalyserMovie.html

AdieuToLogic 11y ago
> Are there any open source tools that would slurp in content like this ... Yes, tesseract[1] can do a pretty good job. Here[2] is a blog post which describes using it to perform OCR on PDF's. As for searching the PDF contents, Solr[3] might be what you are looking for instead. 1 - https://github.com/tesseract-ocr/tesseract https://github.com/tesseract-ocr/tesseract 2 - http://fransdejonge.com/2012/04/ocr-text-in-pdf-with-tesseract/ http://fransdejonge.com/2012/04/ocr-text-in-pdf-with-tessera... 3- http://stackoverflow.com/questions/6694327/indexing-pdf-with-solr http://stackoverflow.com/questions/6694327/indexing-pdf-with...

tiglionabbit 11y ago

I don't get it. She wasn't supposed to use a private email server because it could be compromised and leak classified information. And now they're releasing the emails to the public. How does this make any sense?

nhebb 11y ago
I haven't followed all the details of this saga, but my understanding is that this release is the result of a Freedom of Information Act (FOIA) request for the emails. The emails were vetted by the State Department, and only those that do not have security clearance restrictions are being released to the public.
bdcravens 11y ago
The FOIA request excludes sensitive info, so that's not at risk here. However, had she not used a private server, no one would care.
vilhelm_s 11y ago
Part of the controversy is the concern that classified information may not have been stored securely, but another concern is that Clinton may have deleted emails which legally must be preserved (the federal records act). Compare with the 2013 IRS controversy, which could not be properly investigated because Lois Lerner's hard drive had been erased.

sambrand 11y ago

With zero assurance that all work emails are truly in the dump, I wonder if it will be at all possible to analyze gaps in it (ie. questions by Hilary without a response, regularly recurring interactions missed) to make educated guesses about what confidential emails she purged.

triggercut 11y ago
IIRC "Exploring Everyday Things with R and Ruby" By Sau Sheong Chang does something at least similar with the Enron email database.

hoju 11y ago

Has anyone created a web interface yet for browsing these emails?