How We Did It — the Database of Hopeful Voters

WhoWhatWhy explains the technical details of how our reporter created the table in WhoWhatWhy’s Election Day coverage of potential voters who have had their ballots rejected or not yet returned.

The reporter who created this table, Jordan Wilkie, is not a trained data scientist. While WhoWhatWhy would not publish work they did not have extremely high confidence in for accuracy and completeness, it is possible that some voters who should have been included in the table were left off, and some voters who should have been left off were included.

If you find an error, please contact WhoWhatWhy.

IMPORTANT NOTE: The data WhoWhatWhy used comes from the absentee ballot files updated each night on the secretary of state’s website (files include early, in-person voting, mail-in ballots, and electronic voting). As WhoWhatWhy covered, not all counties report the full extent of their data. At least 67 counties do not report the ballots they reject, and there is no guarantee that the remaining counties report all rejections. This is the best data the secretary of state’s office offers the public.

Voters should always check the status of their ballots on their My Voter Page. Despite WhoWhatWhy’s coverage of the security vulnerabilities on that page, it is still the best source of information about your ballot that the secretary of state’s office provides.

What We Used

.

WhoWhatWhy used two programs, one to get the list of names and one to turn them into a searchable database. The first program is called SQLite (pron: sequel lite). It is a basic database management program. The application is “DB Browser for SQLite” which is a GUI for managing databases written in SQLite. 

The second program is a free version of the data analysis and representation program, Tableau.

How We Did It

.

WhoWhatWhy downloaded the latest update of absentee voter files from the secretary of state’s website.

WhoWhatWhy then uploaded the STATEWIDE file into the SQLite application (in the code link below, you will see this file named as MASTERSTATEWIDE181105).

To be able to query on the voter registration number, you’ll need to go to Modify Table, double-click on VoterRegistration#, and rename (we used VoterRegistrationNmb).

We then used SQLite to select all voters who attempted to cast a ballot but were not successful. The vast majority of these voters are people who requested an absentee, mail-in ballot but have not yet returned it (or have mailed their ballots but they have not yet been received or processed). Almost all of the remaining voters in this group successfully sent their ballots in but their ballots were rejected.

WhoWhatWhy also included in the table voters who had their applications for an absentee ballot rejected and who have not yet successfully cast a ballot.

Once we found the voters we were looking for, we exported the data from SQLite and put it into Tableau Public to make our table.

The Code

.

In case you want to recreate our findings, you can use the following SQLite code. The author has included simplified step-by-step directions in his code, which you will see as all-caps text preceded by the “–” marks.

Tableau

.

WhoWhatWhy then imported the CSV file into Tableau Public. No more data manipulation occurred. WhoWhatWhy created a searchable table and incorporated it into their story.

Ryan Thornburg reviewed this explainer article for accuracy and clarity. Thornburg is a journalism professor at the University of North Carolina at Chapel Hill and he heads the Carolina Data Desk, part of the Center for Innovation and Sustainability in Local Media.

Where else do you see journalism of this quality and value?

Please help us do more. Make a tax-deductible contribution now.

Our Comment Policy

Keep it civilized, keep it relevant, keep it clear, keep it short. Please do not post links or promotional material. We reserve the right to edit and to delete comments where necessary.

print

Comments are closed.