Automate Your City Data with Python

Hello and welcome to this special session of the Django Khan US National Improvement District. It is October 16th, 2023. Our session topic today is how to automate your city data with Python. I am your duly appointed district clerk, Philip James. You can find Find me on the internet there. We're gonna get right started. So, in lieu of the national anthem that we would normally sing or the uh Pledge of Allegiance that we would normally say, please take a moment to commit some act of nationalism within your heart Let's start with some math. For most of us, we live in an area that has a city council and a county board of supervisors or some equivalent, and if we assume that those groups meet two meetings per month and And there are roughly five decisions made at each of those meetings, then we are looking at 20 decisions made by civic government groups per month that affect us.

These include things like Tax rates, rent control, housing policy, police spending, fire department spending, library spending, public health. But really It's more like one city council and one county board of supervisors with three committees times two meetings per month, times five decisions per meeting. And again, we're being very conservative here. Now we're up to 50 decisions per month. That includes things like Parks, school spending, education policy, transportation. However, really, like I said, we were being conservative, it's more like 10 committees, and now we're up to 120 decisions per month and we're including things like public art, civil rights, voting rights, historical preservation, traffic patterns, city pensions, street naming. If you don't see something on this list that affects you, I would love to know about it because

I think that all of these things affect some of us to some degree. Which brings me to point 1. 2. Do you know your current city council members? Your current county supervisors? Do you know their voting record on rent control and housing? Do you know their voting record on COVID protections? Do you know their voting record on civil rights? 1. 3. Would you like to? So who am I? Like I said, I am your duly appointed district clerk. My name is Philip James. I have been a speaker at a number of Django Cons and PyCons and Python Cons conferences around the world. I'm also very grateful to serve as a Python Software Foundation fellow and as my day job I'm the CEO of CrowdAlert. CrowdAlert is trying to make DevOps and SecOps and dev secops and everything around security operations

better for you and your business, please reach out to me if that is at all interesting to you. So, how are we going to answer the questions that we have? First of all, the data to answer these questions exists. The data is mostly accessible, but it's in weird formats and locations. Here's how most civic government meetings work. A clerk, like myself, will put together an agenda, a meeting will happen, minutes are taken from that meeting, the clerk puts together a PDF, they upload the PDF and meeting details to legislative management software. If You have done any research in this space, you've probably come across Legistar. And then that's kind of it. We're right here. A lot of these documents end up being write-only documents. They are committed and then no one ever reads them. We're going to change that, but

first a quick note on legality. I am not a lawyer, but this data is in most cases public domain by default. This is your government. making decisions that affect you, they have to share this information. That said, we're dealing with websites. Please carefully check for terms of service anywhere you're getting This data. When in doubt, you can email the civic body. Often, if you email the clerk for these organizations, they will happily point you at the the information online, or in some cases, they will just send you a PDF full of all the minutes going back as far as they have. Sorry, a zip file full of PDFs with all of those things. Also, scraping the web is legal. The Ninth Circuit has ruled so and has affirmed that decision. Again, I am not a lawyer, but you're probably safe scraping public data to be used for the good of the public.

Item 4. 1. Our goal. Our goal is to get this civic data into a normalized searchable queryable form. If that sounds like a database, you're in luck. That's what it is. We're trying to get all of this data into a database that we can do something with We're going to use two tools. One, we're going to use Python. Hopefully that was obvious. We're at a conference that's talking about Python. We're also going to use a fabulous tool called Dataset. If you're not familiar with dataset, that's fine, you're going to get familiar here. I highly encourage you to look into it. It is maybe the best lightweight data exploration tool that's been written in the past decade We're also going to use a methodology known as ETL. This is probably a phrase that you've heard in your day jobs or across your career. ETL stands for extract, transform, and load, extract, get the data from where it currently lives, transform, do things to get it into the form you want, load, put the transform data into the place you want.

It's a divide and conquer strategy deliberately. We separate these steps so that we can separate the steps and have each step be independent and testable. More on this later. We need to start by extracting. We need to get this data out of the Civic Body website. We want a folder of folders of PDFs with some easily derived date data. Why do we want a folder of folders of PDFs? So we can double check it. Remember we're trying to To be item potent and chestable at each step. We have Python, we need an API, we need an API that gets us PDFs, and that means we have to go look at The web. So I'm doing this for the city of Petaluma. I start by searching for the city of Petaluma. Okay, this looks likely. This looks like, yep, this looks like the official city of Petaluma website.

It's got interesting information meetings seems like what I want here now there's city council meetings that sounds good but where are they hosted aha current and upcoming meetings I don't want current upcoming meetings because that's going to tell me the agenda but not the minutes. I don't know wanna know what's coming. I want to know what happened. Okay, archived meetings. Archive meetings I can do something with. Let's see how many we have here. Okay, there's a lot going on here, but really what I want is the city council and don't need the agenda. I want minutes. Don't know why the minutes haven't been updated in this long, but that's okay. We're going to keep going with what we have. Now notice the agenda is what I said it was. The clerk puts together here's what's going to happen at The meeting, but it doesn't give any decisions. It doesn't say what people did, and we want to know how people voted and what decisions were made, so we're going to look at the minutes instead.

And the minutes are going to tell us Hopefully exactly what was captured. Now we're copying and pasting this URL because that URL is going to be important later. We need to see it. see what's the structure of how this data is stored on the web. So we're gonna look at that one and then we're probably gonna look at some other ones. Yep, great, here's minutes and it's got a bit of of resolutions and yes they voted votes awesome this is exactly what we want now we want to be able to fetch all of these Now we could click through and we could download all of these by hand, but that's pretty inefficient.

We want to let Python do the work for us. But in order to get Python do the work for us, we need some sort of API that's going to tell us here's how to get at it. So again, let's like a look at this link address one more time. This is a different one. Nope, it's the same one again Again, and so we need to see, okay, what's the structure here? Is it consistent? And it looks Aha, that meeting template ID changed. Okay, so we probably need to figure out how to get all of the meeting template IDs so that we can figure out the put like iterate over them and fetch that URL with the meeting ID interpolated in over and over again. This is where we're going to start using the Chrome or Firefox dev tools to really figure out what's going on.

going on under the hood. So I'm going to start by looking at the thing that I care about, which is this section that has the minutes. I notice there's an iframe. That's pretty nice and also pretty common. It's an iframe to something called PrimeGov, which is seems to be where the city of Petaluma is hosting their agendas and minutes and so if I go look at that oop not too many slashes we're gonna try this again uh And we're going to paste it in and get rid of those lashes at the beginning. Yes, excellent. This is the exact process I follow whenever I'm trying to figure out what's going on with a civic government meeting. And look, this iframe that I've loaded. here is exactly what's on the page. There's nothing different. Okay, this is useful because if I ever want to be able to look at this information without going to the City of Petaluma

website first, I can just go to the iframe and kind of skip all the information. and all the kind of border and and filler at the top. Now I want to go to the network tab and see exactly what requests are being made. I don't care about HTML or jet JavaScript or CSS, I just care about third-party API requests that are being made by the page to fill out this information. So when I look at those and then I refresh the page again I start seeing okay here's a lot of things that look pretty interesting. There's list of upcoming meetings that looks pretty good although again upcoming Meetings is gonna give me agendas but not minutes. What else do I have here? Um I guess at this point I should look. Hey, is there a PrimeGov API? And

no, not really Um it's interesting that uh you know there are other cities using this, but there doesn't seem to be an easy API for PrimeGov. That's fine, we can build an API using, like I said, the dev tools and seeing exactly what's happening when the web page is making these requests. That's a list of upcoming meetings. Oh HTML packet, that's interesting, that's interesting, but I still think that's probably not what I want here. Oh, get committees list. Now this is interesting if I ever want to fetch all of the committees. I can probably iterate through this. So I'm going to make a note of this to use for the future. But what I really want, yes, this is what I want. right here can see agenda and uh I think I saw it minutes this is going to be the API that I'm going to hit in order to get the minutes out of

the PrimeGov API and therefore be able to refill that URL that I got earlier so that I can fetch all of the PDFs. Now I've got a template ID there and now I can see Does that template ID actually do what I want it to do? Oh, looking pretty good. And if I open that, awesome. Okay, I now have everything I need to continue. So we have an API. Now we need to use Python to get all the PDFs out of that API. So how is that going to work? For body in bodies, for year in years, you can read this pseudocode, but effectively we're going to iterate through all the AP that full API response. until we've gotten all of the PDFs

for every committee, or in the case a civic body, for every year that we care about. We may not care about everything, but I'm always interested in the full set so that I can compare year over year. We're going to get the matching documents and then we're going to save PDFs using this date format as the title. Why do we use this date format as the title? So that we can easily extract date information. When we're parsing this later without having to go into the PDF for date information. And when I do this, I get something like this. This is actually for Alameda, the city where I live. And you can see that I've got a folder of folders. and each of these is filled with PDFs. Now we come to transform. We need these PDFs to be db insertible text. They need to be not a PDF, they need to be just text strings that can go straight into a database.

We need a folder of folders of text files again with some easily derived date data. We have Python, we need a tool for exporting. So again, I'm just going to keep mentioning this. We are separating concerns. We are trying to be recoverable and usable at every step so that we can see whether the previous step worked the way We're gonna use two things here. Fits, which is going to turn PDFs into PNGs of pages, and then Tesseract, which is going to turn those PNGs of pages into text files of pages. Tesseract is a project uh uh done by Google that allows you to do OCR on your laptop pretty easily. When I first did this I used an uh Amazon product that would do OCR off of text files in an S3 bucket. That actually got too expensive for my taste and I will show you why in a little bit.

So we're gonna start by splitting those PEDFs into pages, again iterating over the whole set, doing an open and then X exporting each page of the doc as a PNG and then OCRing each page into text that we can then use in the database. One quick side note, isn't this slow? Is a question that I often get. And whenever somebody asks you this question, the response that you should give should always be Compared to what? This is not a real-time application. We are doing processing on our laptops to go into a database that then the database will be hosted in real time, but it does not need to be updated in real time. And we can use sentinel values that we can store either in the file system or in memory to kind of track our progress and therefore recover if any stuff

step fails and so slowness kind of only matters if you're going over the entire set but you're not going to be going over the entire set very often. Okay, we have at this point everything we need. Now we need to load it. We need to make this data queryable and searchable. So we need a database of all our civic meeting data. What we have is Python and data set. So how do we load this database? This is the actual code for loading, for creating the database. It's just that easy to create the database and notice this enable FTS True that's going to allow us to do the full text search on the data that we've put into our dataset database. As far as loading pages, we're going to iterate over every page in the database and do this insert call. And then we're going to start up dataset. And we're gonna get a working city minutes database.

And you can see here why I realized when I was doing Berkeley I had to move from AWS to doing this on my laptop. Tens of thousands of pages of minutes were really racking up the AWS. US bill, which is great for small sets, but for this, when we're, you know, most of the way to 100,000 pages of minutes, it just became too expensive. So now what can we do with this? First and foremost, we can look at how many bodies we're dealing with if we've done multiple body searches. We can then see how many pages per body are we looking at. Obviously for most groups, the city council, for most cities, city council is going to be highest, but then you get to say like, okay, how frequently did these groups meet? Can we learn anything from them? This data. And then when you have a city like Berkeley or other cities where the data goes back far enough, you get really interesting results

like this from May 27, 1941. Or Republican form of government. Academic debate on such matters may be justified in a period of international tranquility, but not when the world is in flames. This is someone asking the Berkeley City Council to take a strong position against Hitler and Germany in World War II. That's the kind of cool thing you can pull at when you have access to this data. Or perhaps you want to know when was the first time that transgender rights were mentioned in a particular city? council meeting. And so here we see that for Berkeley in 1997 they talked about transgender rights as part of celebrating pride. Now that we have this data, we need to deploy it and make it useful to other people. Luckily, Dataset provides some great options for deploying.

Both for cell and fly. io are new platform as a service companies. That dataset has great target. For deploy to. You can also build this database and do a lot of the processing via GitHub Actions. If the dataset is small enough, you can store the PDFs right in GitHub. Otherwise you can put it in S3 and again use sentinel values to keep track of how far you have processed. You can do crons in GitHub Action, so if you want this to run once a day, you can set that up pretty easily in your GitHub Action YAML. Where do we go from here? The Council Data Project is the project that inspired my work. They are doing similar work, but instead of operating off of the minutes, they're operating off of the recorded videos because so many city councils are on Zoom and we're recording videos before that and they're doing uh speech to text on those videos so that you can actually search directly what was said.

They have a different set of results and they're providing a different lens but are still doing incredibly useful work for the city. they're working with. Also check out the dataset plugins. There's a rich plugin ecosystem that will provide you a ton of ways to better visualize and play with this data. And with that, I'm going to adjourn this special session of the Django Khan U. S. National Improvement District. Thank you so much for attending. We will post these minutes online, hopefully. Otherwise, thank you so much and have a great day. Day.