Where is Matthew going, and why is he going there? (Or, Looking at UPEI Travel Expenses)

The University of Prince Edward Island publishes reports of employee travel expenses every month. Unfortunately these reports are published as PDF files, making any sort of data analysis unreasonable.

Because I am interested in learning more about university travel, primarily with an eye to understanding–and working to mitigate–its carbon footprint, it’s data analysis I want to do.

So, on November 11, 2019, I submitted an access request for the raw machine-readable data lurking underneath these PDF files:

There are PDF files, released under proactive disclosure, of UPEI employee travel expenses that are not machine-readable. I request machine-readable data, in CSV format, for employee travel expenses for the period January 1, 2016 until the most recent month available at the time of this request. I further request that this data format be added to the proactive disclosure page.

Thirty days later, on December 10, 2019, I received the data I requested, albeit only for the period from April 2019 to October 2019, along with an explanatory letter.

The shorter time-frame was explained to me like this:

Unfortunately, access to some of the information that you requested is outside of the scope of the Freedom of Information and Protection of Privacy Act. Section 4(1.1) makes the Act effective for UPEI from April 1, 2019 going forward. Records created prior to April 1, 2019 will not be released through a request made under the Act.

There are processes for accessing certain information created before April 1, 2019. However, there is no process available for requesting information which has already been made publicly available on the UPEI website.

My mother, who worked as a law librarian, once explained the difference between the “letter of the law” and the “spirit of the law,” and this response clearly falls into the “letter of the law” bucket.

Regardless, I now had some data to analyze.

One of the things you learn as a frequent access-requester is that you can learn a lot about data management systems from the quality of the data you receive: in the case of UPEI travel expenses data, there are myriad inconsistencies that suggest, taken together, that this data is gathered with little concern for quality control nor anticipating aggregate analysis:

  • Names are inconsistent, both in spelling and in form (sometimes “Doug” and sometimes “Douglas,” for example).
  • Department names are inconsistent (sometimes “Procurement” and sometimes “Procurement Services,” for example).
  • Departments within the Atlantic Veterinary College, like “Health Management,” are listed separately, without an explicit connection to the AVC.

I had to do a lot of manual editing of the data to get it normalized to the point where I could do accurate analysis.

Here’s what I found.

Expenses by Person, April to October 2019

After normalizing the data, I loaded the edited CSV file into LibreOffice and created a pivot table by name, summarizing the total of the “transportation” and “accommodations” columns into a single “total travel expenses” column. I then sorted this result in descending order of expenses, converted to an HTML table, and then used DataTables to render the result (you can search–by person or department–sort, and page through the data).

One thing to note: whereas all other employee travel was conducted in 2019, Louis Doiron’s travel expenses claims included travel going as far back as 2016, which contributes to his higher total.

Expenses by Department, April to October 2019

This is a similar pivot table, but by department. Again, you can search (for a department name), sort and page through the data.

Calculating Carbon Footprint

Because of the varied formats that travel destinations are provided in the travel expenses data files, it’s a challenge automatically calculate carbon footprint–destinations include, for example “East, West and Central, PE” and “Portland, ME, Linthicum, MD and New York, NY, US” and “Various Northeastern States, US.”

It’s possible to make some manual calculations, however.

For example, Jackie Podger, Vice-President Administration and Finance, submitted claims for 6 trips:

  • March 28-29, meeting in Halifax, NS
  • May 22-23, meeting in Toronto, ON
  • June 9-11, conference in Halifax, NS
  • July 7-13, conference in London, UK
  • August 21-22, meeting in Toronto, ON
  • October 4-9, meeting in Cairo, Egypt

Assuming driving to Halifax and flying everywhere else, the carbon footprint, in CO2E from flying (calculated by Atmosfair, assuming economy travel on scheduled flights) is:

  • Charlottetown to London, via Montreal: 2.8 tonnes
  • Charlottetown to Toronto: 0.80 tonnes
  • Charlottetown to Toronto: 0.80 tonnes
  • Charlottetown to Cairo, via Montreal: 5.2 tonnes

For a total footprint of 9.6 tonnes.

Adam Fenech, Director of the Climate Lab, submitted claims for 5 trips:

  • January 4-10, meeting in Dubai, UAE
  • February 2-6, conference in Toronto, ON
  • April 21-28, conference in Amsterdam, NL and Ghent, BE
  • October 19-30, conference in Beijing, China
  • September 15 to October 13, meetings on PEI

Leaving out the on-Island travel, a guess at the carbon footprint breakdown from flying, again calculated by Atmosfair:

  • Charlottetown to Dubai, via Montreal: 6.6 tonnes
  • Charlottetown to Toronto: 0.80 tonnes
  • Charlottetown to Amsterdam, via Montreal: 3.1 tonnes
  • Charlottetown to Beijing, via Montreal: 6.4 tonnes

For a total footprint of 16.9 tonnes.

(Atmosfair’s figure for the “climate compatible annual emissions budget for one person” is 2.3 tonnes).

Is university travel sustainable?

Travel is, at present, a necessary aspect of academic and administrative life: face-to-face meetings and conferences are central to the way the academy works, part of its operating system.

It’s clear that this isn’t sustainable, and that we’re going to need to find alternatives.

The University of PEI doesn’t publish data on the carbon footprint of employee travel, but it should: while at some point soon it might be feasible, and important, to place limits on employee travel, requiring the carbon footprint of all travel to be recorded and published would be a positive, easily-implemented first step.

The public scrutiny of this information, combined with the self-reflection it would engender, would, I believe, result in reduction in travel, travel in a more efficient manner, and would stimulate research into workable alternatives to frequent travel that could keep the wheels of the academy turning.

The Data

If you’re interested in exploring the raw data yourself, I’ve published it to a GitHub repository.

(“Where is Matthew going, and why is he going there?” is a line from the song Where is Matthew going? from Anne of Green Gables–The Musical, lyrics by Don Harron and Norman Campbell.)

Charlottetown’s First Public Fast EV Charger

The first public high-speed electric vehicle charger in Charlottetown is on the brink of opening. You’ll find it in the corner of the Canadian Tire parking lot.

While it can take up to 24 hours to charge our Kia Soul EV from a standard outlet, 5 hours from the level 2 charger pending installation off our driveway, this new DC charger should get the Soul to 80% charged in about 30 minutes.

Fast charging like this is the infrastructure that enables longer-distance EV trips, and the network of 6 DC chargers that Efficiency PEI is about to launch will be a welcome addition for EV owners looking to visit friends east and west.

Visualizing Election Results: Geography vs. Population

The BBC News website presented two different visualizations of yesterday’s UK elections.

The first is strictly geographical, with the land area of each constituency accurately represented on the map:

Screen shot from BBC news showing geographic representation of results

By flipping a switched at the bottom of the map you can see the same results rendered as a “cartogram,” which presents each constituency as the same geographic size, located in roughly the relative area of the country, but not geographically accurate:

UK election results from the BBC presented as a cartogram

The Guardian presented a similar treatment, with each constituency the same size, but presented in a more malleable, and thus more familiar form:

The Guardian visualization of the UK election results as a cartogram

Because of differences in population density, the two approaches tell very different stories.

The Electoral Cartogram of Canada website presents a similar treatment for the 2019 Federal Election here in Canada; in this case, because of higher MP-to-population ratio in places like PEI, the Island has an outsized appearance.

Parish Activity Coordinator

St. Paul’s Anglican Church has created a new position of Parish Activity Coordinator; Archdeacon John Clarke described the genesis of this position in this blog post, and you can read the full job description here.

As a happy and content secular tenant of the parish, I can attest that this is a welcoming, caring community; for the right person, taking on this position could not only represent a satisfying job, but also an opportunity to profit from the personal and spiritual growth that being in a community of dedicated parishioners in a progressive church can bring.

Talking to PROBUS about Blogging

I had a lovely time this morning talking to a local chapter of the PROBUS social club. Whereas I’m usually call to present on matters technical or policy, this morning I was given the chance to speak about my blog, and I used the opportunity to do a kind of single-person “songwriters’ circle,” reading from selected blog posts I’ve made over the last 20 years, and then chatting about the context that gave rise to them, and answering questions.

Here are the posts I selected, in the order I read them:

This format could have gone horribly wrong–reading blog posts in front of an audience? what folly is that!–but I think it worked out okay. I realized that when I write and revise I more often than not read posts aloud to myself, and try to inject a musicality into them, so they translate to the stage better than I thought they might.

The proceedings were also aided greatly by the curiosity of those present: they asked good questions. As it happened, I knew more than a few of those in the room, and, indeed, had written blog posts about more than a few of them as well (fortunately in a generally positive light).

Beyond anything else, I was happy for the opportunity to reflect on why and how and about what I write; it reconfirmed for me that I love this place, that’s it’s become integral to how I process my life. Who knew that the simple act of writing could be so powerful.

MOTHERLOAD Screening in Charlottetown on January 12, 2020

I was in Peterborough, New Hampshire in September and, as is my habit, I checked out the community notice board that’s located between Toadstool Books and Twelve Pine.

Peterborough is an active, progressive community, and there’s always a lot going on, so the notice board never disappoints (it’s where I saw the notice for Cafeteria Man, which inspired me to see the film and then invite its subject to visit PEI, which helped to move the school food project ahead here).

On the notice board in September I saw a poster for a screening of a film called MOTHERLOAD that looked intriguing: what’s not to love about a film about parenting and cargo bikes. When I got back to my motel I watched the trailer and read the reviews, and when I got back to PEI I proposed to my colleagues on the Mayor’s Task Force on Active Transportation that we sponsor a screening.

I’m happy to report that they agreed, that the city came forward with funding to support this, and that a free public screening of MOTHERLOAD will be held Sunday, January 12, 2020 at 2:00 p.m. at City Cinema.

It’s a remarkable film–I previewed it last month–and if you’re interested in learning about the intersection of cycling, family, cargo bikes, urban planning, and the human spirit, I encourage you to save the date.

Still from MOTHERLOAD.

A Reminder: You Can Subscribe to this Blog by Email

If you’d rather consume this blog by email, a reminder that you can sign up for a daily digest of posts.

The digest gets sent daily at 6:00 a.m. (a shift forward at the suggestion of my friend Ray, my trusted source for all things early-morning-related).

Here’s what it will look like in your inbox:

Screen shot of the email digest of this blog, in Mail.app

There are 62 subscribers as I type. All you receive is what I post here (no ads or other promotions), and I don’t share the list of subscribers with anyone.