Home / Python / Database

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

hillary-clinton-emails This is a work in progress - any help normalizing and extracting this data's much appreciated! This repo contains code to transform Hillary Clinton's emails released through the FOIA request from raw PDF d
Information
Category: Python / Database
Watchers: 22
Star: 163
Fork: 63
Last update: Sep 25, 2023
Resource links
https://github.com/benhamner/hillary-clinton-emails

hillary-clinton-emails

This is a work in progress - any help normalizing and extracting this data's much appreciated!

This repo contains code to transform Hillary Clinton's emails released through the FOIA request from raw PDF documents to CSV files and a SQLite database, making it easier to understand and analyze the documents.

A zip of the extracted data is available for download on Kaggle.

Check out some analytics on this data on Kaggle Scripts.

Note that conversion is very imprecise: there's plenty of room to improve the PDF conversion, the sender/receiver extraction, and the body text extraction.

Extracted data

There are five main output files this produces: four CSV files and one SQLite database.

Note that each table contains a numeric Id column. This Id column is only meant to be used to join the tables: it is internally consistent, but each entity may have a different Id when the data's updated.

Emails.csv

This file currently contains the following fields:

  • Id - unique identifier for internal reference
  • DocNumber - FOIA document number
  • MetadataSubject - Email SUBJECT field (from the FOIA metadata)
  • MetadataTo - Email TO field (from the FOIA metadata)
  • MetadataFrom - Email FROM field (from the FOIA metadata)
  • SenderPersonId - PersonId of the email sender (linking to Persons table)
  • MetadataDateSent - Date the email was sent (from the FOIA metadata)
  • MetadataDateReleased - Date the email was released (from the FOIA metadata)
  • MetadataPdfLink - Link to the original PDF document (from the FOIA metadata)
  • MetadataCaseNumber - Case number (from the FOIA metadata)
  • MetadataDocumentClass - Document class (from the FOIA metadata)
  • ExtractedSubject - Email SUBJECT field (extracted from the PDF)
  • ExtractedTo - Email TO field (extracted from the PDF)
  • ExtractedFrom - Email FROM field (extracted from the PDF)
  • ExtractedCc - Email CC field (extracted from the PDF)
  • ExtractedDateSent - Date the email was sent (extracted from the PDF)
  • ExtractedCaseNumber - Case number (extracted from the PDF)
  • ExtractedDocNumber - Doc number (extracted from the PDF)
  • ExtractedDateReleased - Date the email was released (extracted from the PDF)
  • ExtractedReleaseInPartOrFull - Whether the email was partially censored (extracted from the PDF)
  • ExtractedBodyText - Attempt to only pull out the text in the body that the email sender wrote (extracted from the PDF)
  • RawText - Raw email text (extracted from the PDF)

Persons.csv

  • Id - unique identifier for internal reference
  • Name - person's name

Aliases.csv

  • Id - unique identifier for internal reference
  • Alias - text in the From/To email fields that refers to the person
  • PersonId - person that the alias refers to

EmailReceivers.csv

  • Id - unique identifier for internal reference
  • EmailId - Id of the email
  • PersonId - Id of the person that received the email

database.sqlite

This SQLite database contains all of the above tables (Emails, Persons, Aliases, and EmailReceivers) with their corresponding fields. You can see the schema and ingest code under scripts/sqlImport.sql

Contributing: next steps

  • Improve the From/To address extraction mechanisms
  • Normalize various email address representations to people
  • Improve the BodyText extraction

Running the download and extraction code

Running make all in the root directory will download the data (~162mb total) and create the output files, assuming you have all the requirements installed.

Requirements

This has only been tested on OS X, it may or may not work on other operating systems.

  • python3
    • pandas
    • arrow
    • numpy
  • pdftotext (utility to transform a PDF document to text)
  • GNU make
  • sqlite3

References

The source PDF documents for this repo were downlaoded from the WSJ Clinton Inbox search.

I created this project before I realized the WSJ also open-sourced some code they used to create the Inbox Search. Subsequently, I've included some material from their open source project as well: I used their HRCEMAIL_names.csv to seed alias_person.csv. I also scraped metadata from foia.state.gov in a similar fashion as they did in downloadMetadata.py.

4 Open More issues
Closed

why has WSJ 29444 docs in search and kaggle download covers 7945 emails

Hi @benhamner

long time ago that you published this :)

Do you remember, where the difference in document-count comes from?

  • http://graphics.wsj.com/hillary-clinton-email-documents/ compared to
  • https://www.kaggle.com/kaggle/hillary-clinton-emails ?

regards Peter (=PA=)

opened Apr 19, 2018 by spex66 1
Closed

added script to go through output/Emails.csv and extract sentences

This is a quick script to extract sentences from output/Emails.csv (assuming the file is downloaded). Looks at every email body from the .csv file, removes meta-information and tokenizes into sentences using spaCy.

Each sentence is on one line and lower cased. e.g.

great teamwork -- great leadership !
both gates and i said the same on the sunday shows .
first presidential debate wednesday night , top republican operatives are primed to unleash a new come up with evidence that the obama administration had positive

Grabs about 6600 sentences.

opened Sep 13, 2015 by tokestermw 0
Open

Link in Kaggle is broken

Hi Ben:

Might wanna update the link to the kaggle dataset. It's now: https://www.kaggle.com/kaggle/hillary-clinton-emails

opened Aug 31, 2016 by GusSand 0
Open

Updated for September, October, November releases.

This primarily is an update for the September, October, and November releases.

Additionally I add a requirements step to the makefile to ensure that pdftotext is installed, along with the Python requirements (pandas, arrow, numpy, requests).

Fixed a minor bug with certain files in metadata.py.

opened Dec 6, 2015 by chmullig 0
Open

fixing text encoding issues and linux-specific implementation issues

I'm running the code on Ubuntu 15.04 and have run into a few minor issues. The changes in the pull request are able to get everything to work for me ok. To summarize:

  • My default python is version 2.7.9 which uses ascii encoding by default, while the raw text and the code (i.e. the list of characters in the normalize_address function of scripts/outputCsvs.py) contain some non-ascii characters.
  • For me, the md5 command is md5sum
  • Linux uses a slightly different syntax for the tail command (see https://stackoverflow.com/questions/6185543/is-tail-2-supported-by-linux)
opened Sep 13, 2015 by cbdavis 0
Open

Fix typo downlaod -> downloaded

why not :P

opened Sep 12, 2015 by aaronorosen 0

Contents

    incredibleindishell This code is vulnerable to SQL Injection and having SQLite database. For SQLite database, SQL Injection payloads are different so it is for fun. Just enjoy it \m/
    simonw Convert CSV files into a SQLite database
    sqlitebrowser Official home of the DB Browser for SQLite (DB4S) project. Previously known as "SQLite Database Browser" and "Database Browser for SQLite". Website at:
    FranckFreiburger vue-pdf - A pdf viewer based on mozilla's PDF.js
    JakubMelka All-in-one library and application for processing and rendering PDF documents
    dpapathanasiou This is a simple graph database in SQLite, inspired by "SQLite as a document database"
    thombashi A CLI tool to convert CSV / Excel / HTML / JSON / Jupyter Notebook / LDJSON / LTSV / Markdown / SQLite / SSV / TSV / Google-Sheets to a SQLite database file.
    simonw CLI tool for exporting tables or queries from any SQL database to a SQLite file
    coleifer Web-based SQLite database browser written in Python
    JonathanLink Converts a pdf file into a text file while keeping the layout of the original pdf. Useful to extract the content from a table in a pdf file for instance. This is a subclass of PDFTextStripper class (from the Apache PDFBox library).
    mileszs PDF generator (from HTML) plugin for Ruby on Rails
    ovvn Generates a printable paginated pdf from DOM node using HTML5 canvas and svg
    bvinc SQLite driver for the Go programming language
    nalgeon All the missing SQLite functions
    elau1004 SQLite external module to read any structured text file according to your parsing specification.
    tforkmann Thin F# API for Sqlite for easy data access to sqlite database with functional seasoning on top
    balacode A minimalist Go PDF writer in 1982 lines. Draws text, images and shapes. Helps understand the PDF format. Used in production for reports.
    arkokoley A PDF viewer for Vue using Mozilla's PDF.js
    arkokoley pdfvuer Demopdfvuer - A PDF viewer for Vue using Mozilla's PDF.js with text support. Demo
    danfickle An HTML to PDF library for the JVM. Based on Flying Saucer and Apache PDF-BOX 2. With SVG image support. Now also with accessible PDF support (WCAG, Section 508, PDF/UA)!
    foundation Quickly create responsive HTML emails that work on any device and client. Even Outlook.
    Share this repo

    Related Repos


    Database
    137
    PostgreSQL replication monitoring and failover daemon
    aiven pglookout pglookout is a PostgreSQL replication monitoring and failover daemon. pglookout monitors PG database nodes and their replication status and
     
    Database
    957
    PostgreSQL configuration wizard
    gregs1104 Introduction pgtune takes the wimpy default postgresql.conf and expands the database server to be as powerful as the hardware it's being deployed on.
     
    Database
    1.2k
    Lanyrd's MySQL to PostgreSQL conversion script
    lanyrd MySQL to PostgreSQL Converter Lanyrd's MySQL to PostgreSQL conversion script. Use with care. This script was designed for our specific database and co
     
    Database
    90
    A command line client for the PostgreSQL Extension Network
    pgxn PGXN Client A command line tool to interact with the PostgreSQL Extension Network The PGXN Client is a command line tool designed to interact with the
     
    Database
    3
    A database scraper created with mechanical soup and sqlite
    MariyaSha WebscrapingDatabases a database scraper created with mechanical soup and sqlite author: Mariya Sha Watch on YouTube: This repository was created to su
     
    Database
    9
    create a database, insert data and easily select it with Sqlite
    MariyaSha sqliteBasics create a database, insert data and easily select it with Sqlite Watch on YouTube a step by step tutorial explaining this code: https://yo
     
    Database
    34
    Python async MongoDB Object Document Mapper
    aminalaee MongoX MongoX is an async python ODM (Object Document Mapper) for MongoDB which is built on top Motor and Pydantic. The main features include: Fully t
     
    Database
    9
    Galvanalyser is a system for automatically storing data generated by battery cycling machi...
    Battery-Intelligence-Lab Galvanalyser is a system for automatically storing data generated by battery cycling machines in a database, using a set of "harvesters", whose job it is to monitor the datafiles produced by the battery testers and upload it in a standard format to the server database.
     
    Database
    6
    A Randomizer Oracle
    asbjornenge Tezos Randomizer Tezod Randomizer "Oracle". It's a smart contract that you can call to get a random number between X and Y (for now). It uses entropy
     
    Database
    50
    A Python wheel containing PostgreSQL
    michelp A Python wheel for Linux containing a complete, self-contained, locally installable PostgreSQL database server.
     
    Database
    53
    An Entity Relationship diagram (ERD) visualization tool, with various filters and inputs t...
    azimuttapp An Entity Relationship diagram (ERD) visualization tool, with various filters and inputs to help understand your database schema
     
    Database
    17
    sqlite medicine database
    WSAyan medicinedb SQLite medicine database 💊 💊 💊 Please note that this database is almost 4 years old and mostly 🇧🇩 Bangladeshi brands related. Although
     
    Database
    104
    Python function to query SQLite files stored on S3
    michalc Python function to query a SQLite file stored on S3. It uses multiple HTTP range requests per query to avoid downloading the entire file, and so is suitable for large databases.
     
    Database
    13
    Write reproducible code for getting and processing ChEMBL
    cthoyt Don't worry about downloading/extracting ChEMBL or versioning - just use chembl_downloader to write code that knows how to download it and use it automatically.
     
    Database
    20
    A deployer and package manager for OceanBase open-source software.
    oceanbase A deployer and package manager for OceanBase open-source software.