About the Project

I have been, and will continue to be, a lifelong learner. The ability to garner actionable insights from and make predictions based on data is, for me, the next step in that process.

The Inspiration

When I started thinking about what kind of data I wanted to get into, as a parent, former substitute teacher, and sometimes moonlight Adjunct professor, public education in California is a topic near and dear to my heart. And, sadly, it's not going well. I knew that this story needed to be told, and I knew the data was all public record.

There is a great group of people doing great work over at TransparentCalifornia.com, where they complete public records requests and publish the data in order to promote transparency. This data is easy to search through in their site if you want to know things like what a given public school teacher makes, who makes the most money in a school or school district, etc.

Teachers generally are making good money here in California, but that in itself isn't a bad thing, and it's not the whole story. I wanted to know, are those dollars being put to good use? Are the students being taken care of? That is a question that is best told, in my opinion, through a map. I decided to make a map of counties or districts and color the map based on the test scores, with a tooltip showing aggregate information about the average, low, and high salary in a given area.

Data Gathering & Web Scraping

The TransparentCalifornia.com website allows downloads, but there is a limit of 10 per day, and there are thousands of files. So, I wrote my first web scraper. I used it to scrape the site for teacher salary data. If a given school had more than 50 pages of data, it would ask you to download. However, it would allow you to sort by highest and lowest, so I actually only needed to download schools that were more than 100 pages. This meant I only needed to download something like 150 files, which I was able to do in 15 days.

The scraper was writing to a series of SQLite databases. When it would get close to a million rows, I would change the name to db_name_part_#. At the end I had 18 databases and needed to combine the databases into one big database and remove duplicates. That was done in this Jupyter notebook. For sanity and size (the "one DB to rule them all" was 14 million rows!), I separated the one SQLite database into one database for each year represented in the data.

Adding Context with CAASPP Test Scores

With that, I had clean, year-separated salary data to work with. Now I needed test scores, which are publicly available for download at CAASPP (California Assessment of Student Performance and Progress).

Mapping the Data (Django & LeafletJS)

I realized the best way to display this information, since schools are representative of their local areas, would be a map. I knew there was a lot of complexity to this data and didn't feel screenshots of a Tableau file or some such would really do it justice, so I decided to try my hand at creating it as a website.

I decided to use Django and set about learning the basics of Django web design. I particularly got a lot of information from the first twenty minutes of this Django CRM Tutorial by Codemy.com and parts of this tutorial where BugBytes implements LeafletJS.

PostgreSQL & Final Aggregation

I have set up PostgreSQL databases before and really liked it. Because the Django CRM tutorial I was learning from was setting up exactly that, I created my models in Django, made the migrations, and migrated them, giving me a robust PostgreSQL framework to put my SQLite and CSV data into.

I had data that matched up but had differences in County/School District naming conventions, and my test scores had different unique IDs from the geographic data. I used the aforementioned crosswalk document to bridge the gap. The main drawback of this approach is that data was self-reported to the NCES and something like 39 schools didn't report in for the 2017-18 data. Still, this was a much more reliable method than joining based solely on names.

In a Jupyter notebook, I cleaned the data and uploaded it to PostgreSQL. I read in the shape data, filtered it for California, trimmed it, and merged it with the crosswalk data. Test score data was also merged with the crosswalk names so everything synced up perfectly.

The rest was just me, the Bootstrap framework, Leaflet, and Google building the site out and getting all the data working together.