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.)