The Changes in Student Population and Per Pupil Expenditure in the Hartford Region

by Robert E. Groebel IV and Shayla Whitaker

Last updated on 12/1/2022

for Data Visualization for All
with Prof. Jack Dougherty
Trinity College, Hartford CT, USA

The Question

We are working with the Center for Leadership and Justice to look at the disparities in educational funding.

Within the state of Connecticut there is an issue of towns being divided by race and wealth leading to discriminatory practices. An example of these practices is the inequitable funding of schools. The School + State Finance Project found that “districts with greater needs, and more students of color, tend [to] have fewer financial resources” (“Segregation & Education”). There is an inequity among the funding of schools and race within Connecticut.

In our research we are specifically looking at a 33-town Hartford Region. We want to look closer at how the population of school-age youth residing in each town has changed over a decade. We also want to look at how the spending per student varies by school district and how it has changed over time. By looking at this data and mapping it will allow us to see if the per-student spending has changed with the migration of students.

In the state of Connecticut’s primary education system, there are many inconsistencies with the distribution of funding to different districts. The financial health of a district is measured by the state through a metric known as “per pupil expenditures”, this metric clumps together the entirety of funding a district receives and divides it by the number of students enrolled in that district. This gauge measures how much funding can theoretically be allocated to each student. Schools that have the ability to spend more on their students fare much better than those who cannot. Our community partners have tasked us with finding out how the per-pupil expenditures vary by school district in the greater Hartford region. This is a vital question to understanding the deeply seeded disparities in equality of educational access in the Hartford region as they inform viewers of the truth that certain districts are underfunded and have been historically underfunded while others continue to thrive.

Visualizing the Data

How many school-age youth reside in each town, and how has it changed over a decade?

Over a decade, from the years 2010 to 2020, all but two towns, East Granbury and New Britain, had an overall increase in population size while the rest had a decrease in population. East Granby saw a 9.53% increase and New Britain saw a 15.4% increase in population. On the other hand, Granby, Tolland, and Plainville had the greatest decreases in population in the last decade.

Figure 1: The percent change of school-aged childern over the last decade in the 33-town Hartford Region showed an overall decrease in the population of school-aged childern.

We found this out using data on the population of different age groups in each of the 33 towns in the Hartford Region that we are looking out. We found this data on Social Explorer, a website that contains census data on all types of demographics among the US. Social Explorer provided us with data for the population of children ages 5-17 for the years 2010 and 2020. We decided it was best to represent this data using a split bar graph of the percent changes in population of children ages 5-17 in Hartford Region over a decade. Using percent change allows us to account for the differing population size of each town and to normalize the data to make it comparable to each town. When you get a negative percent change then the population decreases, and if it is positive then there was an increase in population. We used a 5 step natural break to color code the percent changes by how great or little their population changed.

The Center for Justice also asked us to juxtapose the change in child population to the overall population of a town so we looked at the proportion of children in the total population and the percent change of the total population. We found that in 2010 school-aged children made up on average 17 percent of the total town population with a range of 13 to 22 percent. In 2020 school-aged children on average made up 15 percent of the town's total population ranging from 12 to 20 percent. When looking at the percent change of overall population it ranged from -1.46 to 7.35 percent which shows a much less dramatic change compared to percent change in the population of children which ranged from -33.04 to 15.4 percent. These dramatic changes in the child population make it more important to visualize than to look at how it relates to the overall change in population.

Using the same data in Figure 1 we made a map below that allows you to see the data spatially. You can see that rural towns towards the outer edge of the region such as Tolland, Coventry, Plainville, Canton, and Granby had the greatest decreases in the population of school-aged children. Although there is a trend showing rural towns with greater decreases in the population of children East Hartford is an exception. Once again we can see by looking at this map that East Granby and New Britain had the greatest increases in school-aged children. East Granby is rural and showed an increase in school-aged children population making it an exception to the trend we saw earlier of rural towns having a decrease in the population of children. New Britain is an urban town that showed an increase in the population of school-aged children however other urban towns like Harford did not show a gain in the population of children.

Figure 2: Towns with the greatest percent change in the school-aged childern population tended to be rural towns on the outskirts of the 33-town Hartford region.

In Figure 2 above is a choropleth map that allows one to spatially visualize the percent change in population of children's ages 5-17 in Hartford Region from 2010-2020, which is the same data represented in Figure 1. It also uses the same divergent color palette as Figure 1. When looking at this map the dark blue represents a positive percent change or an increase in population. While the red represents a negative percent change or a decrease in population. You can hover over each town and see the raw population data for the years 2010 and 2020. While hovering you can also find the percent change from 2010-2020.

How does per-student spending vary by school district?

Robert's Visualization Figure 3: The above chart displays the Average Per Pupil Expenditures in the many districts surrounding the Hartford region. Districts that are shaded darker recieve more per pupil funding on average and lighter districts recieve less on average. This chart serves to display the notable disparities in district level funding.

By utilizing a continuously lightening shading of the data points, the disparities between the funding levels of different school districts become more apparent to the viewer's eye. The chart's main purpose is to make the viewer aware of the fact that there are incredibly large gaps in funding between districts, which is quantified through the mean PPE data points. By bolding the three districts with the highest and lowest amounts of PPE spending, the divide in spending is made even more apparent. One of the most important takeaways from the chart is the fact that many of the schools with the lowest amounts of PPE spending happen to be largely comprised of charter schools (Acheivement First, Jumoke, Odyssey).

Creating Visualizations

In creating this Figure 1 and Figure 2 representing the changes in school aged children we used the website Social Explorer. We used it to find their American Community Survey (ACS) 5 year estimates for 2010 and 2020 (most recent year) to find the population of all ages of Connecticut's Subdivisions. When the data comes out of the website it has all the towns in Connecticut and age ranges of “5 and under” all the way to “85 and over” so we cleaned up the data in a Google Sheet to select the towns in Hartford Region and the age ranges 5 to 9, 10 to 14, and 15 to 17. We then calculated the total population of 5 to 17 year olds for each year we were observing. We then normalized the data using the percent change formula, (New value - Old value) / Old value, in order to compare the populations of 2010 to 2020. We then used the website Datawrapper to create Figure 1, the split bar graph, by pasting in the towns and their corresponding percent changes of 2010 and 2020.

To create Figure 2, the choropleth map, we first used a website called Mapshapper to select the towns in Hartford Region and create a geojson file that we could drop into Datawrapper to show a map of only Harford Region. We then used the same percent change data comparing 2010 to 2020 to create the choropleth map. As a caution people should keep in mind that this map uses data from the American Community Survey 5 year estimates meaning there is an “uncertainty associated with them as a result of being based on a sample of the population rather than the full population” (Understanding and Using). Furthermore, the most recent data for 2020 was during the COVID-19 Pandemic which led to the inability to collect data from certain areas and populations causing the data to not be fully representative of the US population (Our Commitment).

To assess the question of how funding varies by school district, I turned to EdSight from the Connecticut department of education to find school funding data. I found “per-pupil-expenditures” data for the years 2017, 2019, and 2021 which I then combined in a sheets document. With these data points on hand I created a column to display the mean PPE data to get a better gauge at how much each district was spending on their students on average. I then made a percentage change formula column where I found the percentage growth / decline of funding in each district from 2017 to 2021. With all the data I needed on hand, I utilized the VLOOKUP function to find the data points for the select school districts in the greater Hartford region and I began thinking about visualizing the data. Data and calculations can be found here.

To visualize differences in levels of PPE spending, I chose to create a chart in hopes of detailing the funding disparities (PPE differences) from school district to school district. By having the range of colors on the chart represent different levels of PPE, viewers are able to easily gauge what districts are underfunded and which are not. By visualizing differences in PPE spending on a district wide basis, I feel as though the question of how student-spending varies by district was accurately answered.

To tackle the second question regarding funding changes over time, I thought it best to visualize both aspects of the data through a dual column chart. The chart displays districts with higher mean PPE’s at the top and lower at the bottom, on the left hand side of the chart is the percentage change from 17 to 21’. One would think that districts with higher mean PPE’s would have smaller percentage changes in funding which is the basic trend on the chart, but, there are multiple outliers that defy this trend and make you wonder if there are some questionable practices occurring with the distribution of school funding. Seeing certain schools with small mean PPE’s have virtually no increase in mean PPE over the years and then seeing districts with large mean PPE’s having large increases raises the question of how the fiscal budget of CT’s public schools should be fairly managed. Through my map and chart, I feel as though the question of how per-student spending varies by school district, and how it changes over time has been accurately addressed.


Work Cited

“Our Commitment to Quality: A Revised ACS Estimation Methodology.” The United States Census Bureau, Accessed 16 Nov. 2022.

“Segregation & Education.” School+State Finance Project, Accessed 11 Nov. 2022.

“Understanding and Using American Community Survey Data: What All Data Users Need to Know.” Census.Gov, Accessed 16 Nov. 2022.