The non-profit I am involved with, Madison Bikes, has been doing candidate questionnaires for local elections for several election cycles now. With each iteration, we learn some things about the best process to make this happen. How do you keep track of candidates? How do you best collect responses? And then how do you present those responses to your community?
For the 2023 spring primary and general elections, we used a new process that takes some of those lessons learned and saved us a lot of work. In this post, I will describe the process with the goal of documenting for ourselves and also allowing others to adapt it to their own purposes. The first sections are about the logistics and of interest to anyone who wants to do candidate questionnaires. The second part gets into the technical process of using R programming to take the responses and format them.
If you want to see what the end result looks like before reading the post, here’s the link.
Identifying candidates and their contact information
The first step to a candidate questionnaire is to keep track of who is running. The timeline for filing for candidacy is short:
| December 1, 2022 | First day nomination papers may be circulated. |
| December 23, 2022 | Deadline for incumbents not seeking re-election to file Notice of Non-Candidacy. |
| January 3, 2023 | All papers and forms due in City Clerk’s Office at 5 p.m. |
| January 6, 2023 | Deadline to challenge nomination papers. |
I set up a Google Sheet early on and manually kept it up to date as I saw new candidates announcing (or incumbents announcing their non-candidacy), mostly relying on the Clerk’s Office’s candidate filings but also media coverage. As we got closer to the deadline, I also started collecting candidates’ websites, social media links, and email addresses. This involved a lot of internet searching and was very time consuming. I later found out that candidates provide an email address to the Clerk’s Office on their official paperwork. When we do this again for the next election cycle, I would no longer manually search and just wait until after the deadline.
Developing questions
In previous years Madison Bikes did their own questionnaire. When we learned that Madison is for People, a housing advocacy organization, was also interested in doing a questionnaire, we connected with them to coordinate efforts. We also got Madison Area Bus Advocates on board and developed a joint questionnaire. Partnering with other organization has the advantage of being able to share some of the logistical work, but it also creates additional coordination work. Overall, this went very smoothly. We needed to make sure that the questionnaire didn’t become too burdensome for the candidates, while still providing meaningful content for our organizations’ respective audiences. In the end, each organization provided three to four questions, plus three short joint questions about how candidates get around the city. We had slightly different questions for mayoral candidates and candidate for the common council.
Distributing the questions, collecting answers
We split up the work of emailing candidates between three people (Thanks, Connor, Will, and Jonathan!). Emails were sent individually, and I think this is something that in the future we could consider automating, e.g. by using Mailchimp or Qualtrics.1 We tracked the send-out in the same Google Sheet where we kept the contact information.
We discussed several options for receiving candidate responses. Should they just sent their responses in an email? Put them into a Google Doc? Based on our prior experience we decided to go with a Google Form. With over 40 candidates total, anything else would likely have turned into a nightmare. The Google Form had a text field for the candidate’s name, a dropdown menu for their district (don’t use a text field for this!), and text fields for each candidate question. We carefully tested the survey, for example making sure that the respondents didn’t need a Google account. Then a link to the survey was included in the emails.
Turning a form into HTML
The deadline for candidates to respond was only a week before the primary election date.
Turning the form responses into nicely formatted content for our website quickly was therefore very important.
I ended up using RMarkdown and knitr as the main tools for this, relying heavily on the wonderful “R Markdown: The Definitive Guide” and the “R Markdown Cookbook”.
It’s very much possible that there are better/different tools for this out there, but RMarkdown is what I was familiar with and the final product turned out well.
You can find the repository with all code on Github: https://github.com/vgXhc/sheets-2-wp
The Madison Bikes website runs on WordPress, which limited our options of what we could do. In the last round of candidate questionnaires, Ben had created a script to create html tags around the responses read in from a csv file. We then copied that html into a WordPress custom html block. This seemed to work well enough and so I also aimed to create an html document to paste into WordPress, but instead of bare html it would be an html document generated by knitr.
Let’s walk through this step by step. There are three main files:
combined-report.Rmd: This is the file that eventually gets knitted and produces the html and pdf outputoutput-council-child.Rmd: The template for generating content blocks withincombined-report.Rmdfor council candidates.outout-mayor-child.Rmd: Same, but for mayoral candidates
combined-report.Rmd has a full yaml header, both for html and pdf output.2
---
title: "Candidate Q&A"
output:
pdf_document:
toc: TRUE
toc_depth: 1
html_document:
toc: TRUE
toc_depth: 1
toc_float: true
documentclass: scrreport
---One thing we didn’t have last time was a table of contents (TOC), which made it difficult to navigate through the Q&A.
The toc: true option easily generates the TOC.
A floating TOC would have been even better (it really helps to keep track of where in the document you are) , but since that relies on Bootstrap and jQuery, it doesn’t work in a WordPress html block.
Since Madison is for People and Madison Area Bus Advocates use different content management systems, I rendered a version with and without the floating TOC.
For the pdf version, I specified the LaTeX document class scrreport from the KOMA-Script bundle.
After the yaml header there is a bunch of code to prep the data.
The form responses are read in with the googlesheets4 package.
They come in as wide data, with one row for each candidate and one column for each question.
The code then transforms the data into a long format, with one row for each question and answer.
To help with formatting, the question is split into a “topic” (everything before the colon) and the actual question.3
There’s some duplication in the code that could be cleaned up with a function.
library(tidyverse)
library(googlesheets4)
library(here)
library(knitr)
responses_council <-
read_sheet(
"https://docs.google.com/spreadsheets/d/1zMTl2BQzQ231SFjN8RRKvZm3PidXSoS2tVDFXSn5AwU/",
col_types = "cccccccccccccccc"
) |>
mutate(
district = factor(
`Which district are you running for?`,
c(
"District 1",
"District 2",
"District 3",
"District 4",
"District 5",
"District 6",
"District 7",
"District 8",
"District 9",
"District 10",
"District 11",
"District 12",
"District 13",
"District 14",
"District 15",
"District 16",
"District 17",
"District 18",
"District 19",
"District 20"
)
),
district_short = str_remove(district, "istrict ")
) |>
arrange(district, `Your name`)
responses_mayor <-
read_sheet(
"https://docs.google.com/spreadsheets/d/1T3hQ20IDetkjQ71qj5nZqRiZ8fZ8XHoKDCiUKaIPs9Y/"
)
responses_council_long <- responses_council |>
pivot_longer(
cols = 6:ncol(responses_council) - 2,
names_to = "question",
values_to = "answer"
) |>
rename(name = `Your name`) |>
mutate(
topic = str_extract(question, "^(.*?)\\:"),
# everything before the colon
question = if_else(is.na(topic), question, str_remove(question, topic))
)
responses_mayor_long <- responses_mayor |>
mutate(district = "Mayoral candidate",
district_short = "mayoral cand.") |>
pivot_longer(
cols = 3:ncol(responses_mayor),
names_to = "question",
values_to = "answer"
) |>
rename(name = `Your name`) |>
mutate(
topic = str_extract(question, "^(.*?)\\:"),
# everything before the colon
question = if_else(is.na(topic), question, str_remove(question, topic))
)str(responses_council)tibble [19 × 18] (S3: tbl_df/tbl/data.frame)
$ Timestamp : chr [1:19] "2/14/2023 22:30:06" "2/14/2023 19:08:25" "2/5/2023 22:06:43" "2/11/2023 8:42:33" ...
$ Your name : chr [1:19] "John W. Duncan" "Colin Barushok" "Juliana Bennett" "Derek Field" ...
$ Which district are you running for? : chr [1:19] "District 1" "District 2" "District 2" "District 3" ...
$ When was the last time you took the bus? : chr [1:19] "The last time I rode a public transit bus was when I lived in Boston, where commuter routes were convenient for"| __truncated__ "September 2022" "Early this week, 1/31/2023" "Thursday, two days ago" ...
$ When was the last time you rode your bike to work, to school, or for an errand? : chr [1:19] "A lack of designated bike lanes along busy roads does not allow for safe commuting to work or to run errands in this area." "During summer 2022" "Today, 2/2/2023" "Three years ago when I lived closer to downtown" ...
$ What is the primary way you move around the city? : chr [1:19] "The safest and best option for commuting for those of us on the far-west side is usually by car." "Walking" "walk or carpool" "Bus for work, walking around the neighborhood, and car for errands" ...
$ General Vision: 2023 brings many significant changes for Metro including the beginning of Bus Rapid Transit implementation, a complete redesign of the transit network, and policy changes such as Transportation Demand Management and Transit Oriented Development. Do you support the current direction of Madison’s transit plans, and what is your vision for Madison’s transit system in the mid and long term?: chr [1:19] "I support the decision to increase the frequency and capacity of bus service along high-demand routes in the ci"| __truncated__ "I support Bus Rapid Transit, efforts toward Transportation Demand Management, and Transit Oriented Development."| __truncated__ "Over the past two years, I have been supportive of BRT, Metro Network Redesign, Vision Zero, TOD and more that "| __truncated__ "I'm excited for the areas of the city whose transit service is improving with higher frequency, including along"| __truncated__ ...
$ Sustainable Funding: In the Metro redesign, funding constraints limiting the budget to 2019 levels required compromises in network coverage, hours of service, and frequency outside a handful of core routes. What would you do to establish more sustainable funding to improve Metro’s quality of service? : chr [1:19] "The unfortunate reality facing growing cities throughout the country, including Madison, is that budget dollars"| __truncated__ "We should make sure BRT is efficient and convenient to encourage more riders for a wider variety of trips. This"| __truncated__ "We are in a budget deficit, yes. We are also at a point where we need to get our priorities in order of what we"| __truncated__ "Transit service must be a budget priority going into some tough upcoming budget years and I'll remain an advoca"| __truncated__ ...
$ Accessibility: The Metro network redesign has increased the distance to the nearest bus stop for some residents. This is a major concern for bus riders with limited mobility. What measures would you take to ensure riders with mobility limitations are well-served by our transit system? What do you think the role of paratransit is in this regard? : chr [1:19] "The goal of our transit system should be to provide equitable access to fixed-route buses. In the long term, I "| __truncated__ "Madison is required to provide transportation for everybody regardless of their ability. The city should invest"| __truncated__ "From my time on Council, I have seen how the city is not always good at including community voices, especially "| __truncated__ "This is a concern in my district as well - one that I've discussed with a few residents at their doors while ca"| __truncated__ ...
$ Historic Preservation: There have been conflicts between the priorities of promoting new housing development and preserving historically significant buildings and neighborhoods in recent years. What specific factors would you consider when balancing new development against preservation, and how much weight would you give to the different factors? : chr [1:19] "Historic preservation, while desirable, has often been used as a tool for excluding historically marginalized c"| __truncated__ "District 2 is unique because it is the most dense district in the city and the district with the highest concen"| __truncated__ "Oftentimes, we view historic preservation as binary subjects. Supporting historic preservation and the city’s n"| __truncated__ "Madison is experiencing a housing crisis. We need to balance preservation of historically significant sites wit"| __truncated__ ...
$ Housing Affordability: The City's 2022 Housing Snapshot indicated that more housing was needed at all income levels, including both affordable housing and market rate housing. What is your plan to ensure housing is built that is available at all income levels? : chr [1:19] "Meeting our city's housing needs will require a multipronged approach that involves collaborating with develope"| __truncated__ "I would support policies that encourage density in more parts of the city, along transit routes, and in unused "| __truncated__ "Increasing affordable housing is one of my top priorities for the upcoming term. My plan to increase housing is"| __truncated__ "Housing is one of my core campaign issues. My partner and I experienced Madison's housing crisis for ourselves "| __truncated__ ...
$ Zoning Reform: Municipalities across the country, including Portland, Minneapolis, and Charlotte have taken steps to reform zoning by eliminating parking minimums and allowing for small multi-family buildings by-right throughout the city. Would you support similar reforms in Madison? Why or why not? : chr [1:19] "Yes, I support these types of initiatives. I believe that zoning reform is necessary to meet future housing dem"| __truncated__ "Yes I support these policies. We are trying to encourage less driving for environmental reasons, and we are als"| __truncated__ "Yes of course! I have been supportive of such reforms, including zoning reforms for missing middle housing. In "| __truncated__ "I believe that zoning is a policy tool, and that it was implemented by the community many decades ago in an att"| __truncated__ ...
$ Complete Green Streets: Madison recently adopted a Complete Green Streets policy that prioritizes walking, biking, transit, and green infrastructure over driving and car parking when it comes to allocating our public right of way. Are you committed to implementing this policy, especially when a project requires the removal of car parking or inconveniencing drivers? : chr [1:19] "I support the Complete Green Streets policy that the city has pursued, and I am committed to implementing it. W"| __truncated__ "I'm committed to implementing complete green streets, encouraging less single occupancy modes of travel, improv"| __truncated__ "Yes definitely! I was happy to support Complete Green Streets. Madison should be a 15 minute city, meaning anyo"| __truncated__ "Yes. I believe in the objectives of this policy." ...
$ Vision Zero: Madison committed to eliminating all fatalities and serious injuries from traffic crashes by 2035. Yet in 2022, 14 people were killed, including 3 people riding bikes, and 74 were seriously injured. Which roadways and intersections in your district should be prioritized for safety improvements, and what strategies would you use to ensure improvements are implemented? : chr [1:19] "Rapid development in my district continues to prioritize the needs of drivers. Long stretches of increasingly b"| __truncated__ "The most concerning intersection is W Gorham Street becomes University Avenue at Basset Street. Cars routinely "| __truncated__ "Vision Zero is an excellent program, but it is still in its pilot stage. In the next two years, I would like to"| __truncated__ "Traffic safety is one of the top issues residents raise with me as I canvass neighborhoods in my district. I ap"| __truncated__ ...
$ Bike Network: Madison Bikes wants all residents to have access to a low-stress bike network that makes biking safe and convenient for people of all ages and abilities, no matter where they live in the city. Where in your district do you see major gaps in this network and how would you propose to fix these gaps? : chr [1:19] "Significant gaps currently exist in the bike network on most major roads in my district. As the city plans the "| __truncated__ "Westbound University Avenue has a non protected bike lane that is between auto lanes and bus lanes. This has lo"| __truncated__ "My district downtown is fairly bike and pedestrian friendly. I believe that we could add protected bike lanes a"| __truncated__ "I see major gaps in the network right in my district. Neighborhoods on the far east side are rigidly segmented "| __truncated__ ...
$ Transportation Climate Impact: In Madison, about 40% of greenhouse gas emissions come from transportation. How do you think the city should go about reducing emissions from that sector over the next 5 years? : chr [1:19] "In addition to making investments in expanding our public transit system and bike network, I believe the city s"| __truncated__ "We must remain steadfast in our efforts to provide the best possible infrastructure for bicycles, safer streets"| __truncated__ "I like that Bus Rapid uses electric buses. It is a great start to ensure that our entire city fleet uses electr"| __truncated__ "Within transportation, I think Madison should be reducing its greenhouse gas emissions by offering clear, conve"| __truncated__ ...
$ district : Factor w/ 20 levels "District 1","District 2",..: 1 2 2 3 3 4 5 6 6 9 ...
$ district_short : chr [1:19] "D1" "D2" "D2" "D3" ...str(responses_council_long)tibble [247 × 8] (S3: tbl_df/tbl/data.frame)
$ Timestamp : chr [1:247] "2/14/2023 22:30:06" "2/14/2023 22:30:06" "2/14/2023 22:30:06" "2/14/2023 22:30:06" ...
$ name : chr [1:247] "John W. Duncan" "John W. Duncan" "John W. Duncan" "John W. Duncan" ...
$ Which district are you running for?: chr [1:247] "District 1" "District 1" "District 1" "District 1" ...
$ district : Factor w/ 20 levels "District 1","District 2",..: 1 1 1 1 1 1 1 1 1 1 ...
$ district_short : chr [1:247] "D1" "D1" "D1" "D1" ...
$ question : chr [1:247] "When was the last time you took the bus?" "When was the last time you rode your bike to work, to school, or for an errand?" "What is the primary way you move around the city?" " 2023 brings many significant changes for Metro including the beginning of Bus Rapid Transit implementation, a"| __truncated__ ...
$ answer : chr [1:247] "The last time I rode a public transit bus was when I lived in Boston, where commuter routes were convenient for"| __truncated__ "A lack of designated bike lanes along busy roads does not allow for safe commuting to work or to run errands in this area." "The safest and best option for commuting for those of us on the far-west side is usually by car." "I support the decision to increase the frequency and capacity of bus service along high-demand routes in the ci"| __truncated__ ...
$ topic : chr [1:247] NA NA NA "General Vision:" ...The next step took me a while to figure out: How can I use the long data frame and iterate both over each candidate but also over each question to produce the desired markdown content?
I first tried rendering separate html documents for each candidate, but the downside of that approach is that then I’d have to set up separate pages on WordPress and copy-and-paste dozens of html files.
Rendering a single html file required a different approach: the knitr::knit_child function.
The documentation in the “R Markdown Cookbook” wasn’t super easy to follow, but eventually I got it!
First we define a function that take the candidate name as its input.
The responses are then filtered to just those of that candidate.
The district and district_short variables for that candidate are created–those are used for formatting the output.
Then comes the key piece: we ask knitr to knit a child document using the output_council_child.Rmd template.
The child template is pretty simple: No yaml header and just a bunch of markdown plus inline R code to format the output.
Here are the first few lines.
Now that we have the function to knit the child document defined, we can just iterate over all the candidate names with lapply and then turn the list output into simple text separated by line breaks (the \n).
Because of the chunk option results='asis' the Markdown generated just gets inserted into the rest of the Markdown in combined-report.Rmd and then knitted into html (or pdf).
{r results='asis', echo=FALSE}
res <- lapply(responses_mayor$`Your name`, knit_answer_child_mayor)
cat(unlist(res), sep = '\n')Now we just do the same for the common council candidates (different child template because the number of questions is different) and that’s it! Now you can open the generated html file in your text editor (or your web browser’s source viewer) and copy the content into the custom html block in WordPress! And this is what the end result looks like: https://www.madisonbikes.org/madison-spring-elections-2023/
Enhancements
One key enhancement would be to automate the publishing to WordPress. Rather than copying-and-pasting the html into WordPress, wouldn’t it be nice if the content could be pushed through the WordPress API? There are some packages and tutorials on how to do this within R, but the packages are very old and I didn’t have much luck with the tutorials.