Data Blog: Methodology for analyzing Boston Public Schools Air Systems Data

Analyzing the state of HVAC systems in Boston Public Schools

Download the data discussed in this post here.

Check out the narrative portion of our story here .

Part I: Finding Data

Data was collected from www.buildBPS.org, a project organized by the City of Boston, the Boston School Committee and Boston Public Schools with assistance from outside data analysis companies. SMMA is a facility design firm that marks facilities viable or not. All of the data was found on this website, but there is no access to a collective .cvs sheet. 

We selected the data points that we want to analyze, focusing mostly on HVAC systems and air quality-related data points. The data we pulled for every listed school included: 

• School name 
• Address (to be translated into latitude and longitude at a later time) 
• Enrollment 
• Date built 

Facility Assessment: Building 

• Heating Distribution System: state of current heating systems (boilers, furnaces, etc.) 
• Ventilation Distribution System: state of current ventilation systems (exhaust fans, air-conditioning equipment, etc) 
• Security at Entry (cameras, buzzer systems, etc) 
• Fire Protection – Sprinkler System 

Educational Facility Effectiveness: Learning Environments

• Air Quality
  • “Different ventilation systems provide varying levels of outdoor air percentages and filtration (e.g., unit ventilators vs. central air ventilation vs. no mechanical ventilation provided). What appears to be the quality being provided by the mechanical system?”
• Building Ventilation 
  • “Fresh air is an important component for good brain activity and overall student performance. An even distribution of ventilated air is also important. Is mechanical ventilation provided? What appears to be the quality of the system?”
• Environment 
  • “Is this a building that is aesthetically pleasing? One in which students and teachers feel comfortable and want to spend time, day after day?”

We omitted a few schools that had a recorded enrollment of zero. A few of these schools seem to either be phasing in or phasing out, so we left them out of the data set as it is not affecting any students at this time. 

There were also a few schools that had no data for some of our categories. These were put into the formulated .csv file but we ultimately omitted out of the visualizations when created. 

Part II: Working with the Data

After the data entry, we used the addresses in the data set to get the latitude and longitude. There are a few ways to calculate latitude and longitude using an address. After a quick Google, we found that there is a way to calculate this in Excel by using an add in like this one A.CRE Geocoding Excel Add-in. 

Here are the steps this website gave to calculate once we had the add-in installed. 

We tried to use that formula and kept receiving an error message. 

We decided it would be easier to run the code in Python, so with the assistance of a friend, we calculated the latitude and longitude there. 

Even in Python we found some errors with the data set, about 30 of the addresses weren’t registering. It turns out that many Boston neighborhood addresses needed to be input as Boston versus the specific neighborhood. In many instances Dorchester addresses were actually Boston, or East Boston was just Boston. We managed to get the latitude and longitude for every one of the 125 schools except for one that for some reason the system didn’t recognize no matter how many times we tried fixing it. 

We downloaded the appended csv. file and opened it in Tableau. 

In Tableau we began to create some preliminary visualizations with the specific categories extracted from the Build BPS data. 

The categories extracted were:

• Heating Distribution System
• Ventilation Distribution System
• Air Quality
• Building Ventilation
• Environment
• Fire Protection: Sprinkler Systems
• Security at Front

We want to focus on the quality of the air in schools and whether or not BPS has adequate systems in place to keep students safe. We added Fire Protection and Security at Front as other interesting data points when it comes to student safety and well-being and to show how resources are being used. 

Here are some preliminary visualizations we came up with:

Figure 1:

First, we looked at Ventilation Quality and sorted it by school grade level. It was interesting to see how much worse elementary schools fared in this category compared to some of the other school levels. With 17 systems that need to be replaced, it shows how bad the ventilation is for the youngest population who may have trouble speaking up for themselves if they feel uncomfortable.

Figure 2: 

Next we looked at the condition of Heat Distribution Systems paired with the enrollment size of the schools. I decided to use enrollment size to emphasize how many students would be affected by poor conditions. I wanted to add an extra element to the visualization for reference. The visualization isn’t particularly effective, but does show a need for new heating systems around Dorchester and the further south. 

Figure 3: 

Next, we looked at a similar style map but compared School Air Quality. To show how many students would be affected by the quality of the air in each building we added enrollment as a size filter. 

We continued to play with different combinations of qualifiers on the map. We initially chose the traffic light color scheme as a universal indicator of green is good and red is bad, but then realized that is the absolute worst combination for the colorblind community. We want our visualizations to be completely accessible so we changed the color palette to Tableau’s colorblind friendly option. 

Now our maps looked a bit more like this when it came to the color palette:

A few other changes on these maps:

• Color palette change from traffic light to colorblind friendly for accessibility
• Map Layers: Turned the background dark, with 39% opacity
  • Zoomed in to create neighborhood labels and zip code boundaries also under map layers.
• Instead of using Enrollment as a size filter, we used the qualifiers as size filter. The worse the state of each qualifier, the larger the dot on the map. Making this change helps the discrepancies stand out just a bit more. 

We decided to include Enrollment as a separate bar chart where there is an interactive element. Viewers can search the bar chart to look at enrollment at all BPS school’s just to have context before diving into the more complicated vuslizations. This is what the enrollment chart looks like:

The colors represent the grade level of the school or Type so that those interacting with the chart know just by searching a school what age range school it is. 

Once we had created the maps for each of the categories, we put them into Story pages so that they look clean and publishable. Tableau also gives the option to go into presentation mode, and the story pages help give space to create a narrative around the data. 

For example the chart above is given more context on the story page:

We included highlight search bars so that those interacting with the data can search specific schools or specific types of schools to help filter through over 120 schools. The story page also gives space for a comment or caption at the top of the chart to give more detail about what is going on here. 

We published the visualizations on Tableau Public, so that those who view the data can look through our workbook chronologically. The workbook starts with an enrollment bar chart to show the size of schools and what grade level they are. This can be used to reference how many students would be affected by the data shown in the next slides that reference the specific qualifiers we’ve looked at. The next story pages show the data of the physical systems: ventilation and heating distribution. Next we show similar maps that showcase how the environment ranks for learning, the air quality, and the ranking of building ventilation. 

Check out our published work on Tableau Public here (insert link). 

Results:

The visualizations all show huge issues when it comes to building ventilation and heating systems in Boston Public Schools. We found elementary schools fare the worst in most categories we analyzed. 

Further Considerations for continuing to pursue our project:

There are a few things we’re still thinking about moving forward with the project:

• We’d like to take a deeper look into funding distribution 
• We need to talk to more sources to understand the issue more deeply
• Looking at new source angles
  • Pam Rose: Racial inequality in learning environments 
    • A history of redlining continues to contribute to modern day inequality because of investments made in some communities and not others.
    • Advocating for these issues is a full time job, who has time to advocate
  • Mike Ritter: Solving other environmental issues 
    • BPS is the #1 carbon emitter of an municipal buildings in Boston 
• Looking at other story angles 
  • Combination: BPS “myth busting”
  • Single: Resources for students (academic + non-academic resources)
• How would a state takeover impact these systems and improvement?
  • Receivership 
• Using other cities a models: Comparing BPS to a school system like Washington, DC
  • DC has a live monitoring system of HVAC conditions and work orders in the system.

How we tackled our project:

We divided the project responsibilities to be able to cover more ground. We all worked on outreach and looking for sources. We did the initial interview with Mike Ritter that inspired this project, as a group. After data was collected, we evaluated which kinds of visualizations would work best together. 

Cori’s role: I evaluated the Build BPS website, decided which criteria would support the argument we were trying to make, and created a spreadsheet of all the data. I worked on the research surrounding pilot programs, which came from a few conversations with my friend and a few of my clients who are teachers. While they led us to the idea of pilot schools, they ultimately were not the best people to talk to for formal interviews, so we nixed that. I researched and wrote up the narrative revolving budgeting, and funding. I also researched and wrote the piece surrounding state takeover status revolving BPS. 

Brianna’s role: I reviewed the transcripts from Mike Ritter and Pam Rose, highlighting important quotes and information to use in the narrative. In addition to designing the outline for the narrative, I wrote the opening and closing sections including quotes from sources and important context I researched on the physical environment of classrooms. I also rearranged the structure of specific sections to make the narrative flow better. 

Elena’s role: I took the data entry that Cori input from BuildBPS and calculated the latitude and longitude so it would function in a map in Tableau. I worked on creating the visualizations for our project and getting them published to Tableau. I wrote my methodology for creating the visualizations for the blog post. I interviewed Pam Rose, a BPS teacher and also got a statement from city council candidate Tania del Rio. I helped with the midsection of the narrative about why it’s important for students to have a comfortable environment to reach their highest potential.