Convert ranked choice output from Qualtrics CSV to ES&S Excel format, which can be fed into the RCV Universal Tabulator.
Usage, from a terminal or command shell: qualtrics2ess.py qualtricsfile.csv
Qualtrics is a popular cloud platform providing survey tools, including the ability to rank items, e-mail participation links to individuals, and record their choices anonymously. It can therefore be used for ranked choice voting elections. Amherst College has written a set of instructions for using Qualtrics for RCV elections.
At this time the RCV Universal Tabulator cannot read the Qualtrics format — instead the most similar format is the ES&S format, after the company whose equipment generates it. The former is in Candidate by Choice format, while the latter is Choice by Candidate. But it’s a relatively simple transformation to convert one to the other, provided by this script.
This script will run on any computer that has the Python interpreter installed on it.
A Qualtrics survey / ballot document is provided here for testing: What+Are+Your+Favorite+Fruits+and+Vegetables%3F_April+13%2C+2021_23.28.csv:
The script will convert such files to a set of ES&S-format Cast Vote Record (CVR) documents, one for each election on the ballot:
ES&S CVR files are actually provided as Excel workbook (.xlsx) files, so the RCV Universal Tabulator is only designed to input that format. The qualtrics2ess.py script will generate Excel files, ending in _cvr.xlsx. This does require a non-standard Python library, but if it isn’t present it will output files in CSV format instead, ending in _cvr.csv. Any CSV file can be opened in Excel (usually the result if it’s installed and you double-click the file) and then saved as File Format: Excel Workbook (.xlsx).
The qualtrics2ess.py script will also generate a common data format (CDF) file for each election, which provides the basic information about the ballot contained in the Qualtrics input file. They will be in JSON format, a standard format for distributing datasets, and will have a file name ending in _cdf.json. This file can be loaded into the RCV Universal Tabulator as a starting point. Importantly, this includes all of the candidate names on all of the ballots, including write-ins. It does not, however, include the election rules, which must be specified in the Tabulator.
-
Write-in values are filled in, in the correct choice column.
-
Blank or -99 values are replaced by the keyword "undervote". Qualtrics will by itself prevent overvotes.
-
Separate elections are output into different Excel files (CSV if necessary).
-
Config files are also generated with information from the Qualtrics file, including candidate names.
-
The base name of these files will be the same as the Qualtrics file. To make the file names easier to read the ‘+’ and “%XX” characters are either replaced with allowed filename characters or removed.
-
Extra header and voter identification information is dropped.
-
Compatible with Python 2.5 or later, including Python 3.x.
-
a. Download and install Python
b. To create Excel workbooks, qualtrics2ess.py depends on the library openpyxl, which can be installed by running the program pip in the terminal (command shell):
pip install openpyxl
If pip isn't available: Download and install pip
Otherwise, CSV files will be generated, and Excel can be used to convert them.
c. Alternative to steps 2a and 2b: Download and install Anaconda, providing a single python installation along with a useful collection of Python libraries. -
Install the RCV Universal Tabulator.
-
qualtrics2ess.py is a python-language script. You must use python to run it in a command-line environment: the terminal on Macintosh, the CMD or PowerShell on Windows, or a shell on Unix. The command is:
python qualtrics2ess.py qualtricsfile.cvr
wherequaltricsfile.cvr
is your Qualtrics cast-vote-records file. -
On Mac or Unix, you can also make the program executable
chmod +x qualtrics2ess.py
and then you don't need to precede it withpython
. -
Be aware that in a command-line environment, quotes, spaces, and other special characters in a file name can be interpreted as something different, so if you have any characters other than letters or numbers in the file name, enclose it between single quotes, or if it contains a single quote enclose it between double quotes.
Created on Mon Apr 5 01:40:21 2021
@author: Andy Anderson, Amherst College, [email protected]