I recently released my San Francisco Budget GPT. This is based on open data from DataSF, and while it can answer many questions it helps to have enough context to know what to ask. The published dataset has a data dictionary but it can be frustratingly vague. As well as the downloadable data there is a helpful site where you can generate some reports. This post should help fill in some of the gaps.
Overview
Budget data is available from the 2009-2010 to the 2024 to 2025 fiscal years (which run from July 1 to June 30). In the file the fiscal year is at the end of the period. Every dollar has a source and a use, there is a 'Revenue or Spending' column that is Revenue for sources and Spending for uses. The actual dollar values are in a 'Budget' column. There are three hierarchies that allow you to analyze the data - organization, object and fund. Each hierarchy has three levels, and each level has a code and a description. For the rest of this post I'm using data from the 2023-2024 fiscal year.
The total budget for 2023-2024 is $14.6 billion dollars. That's a little hard to put into context. On the one hand it's about two dimes for every week the universe has existed. On the other, it's only a quarter of Elon Musk's Tesla bonus.
Organization Hierarchy
This is the organizational structure of the city - Organization Group, Department and Program. One example is Public Safety, Police, Operating. Here's the full Organization Group and Department structure:
Organization Group and Department
(A quick note on charts - there is a lot of information to present here. Each chart has a full screen button at the top right and I recommend using this to see the most detail. Charts also have tooltips and so you'll have a much better time with this post if you use a big screen and a mouse. On sunburst (nested pie) charts click any segment to filter to that parent only.)
I haven't included Program here. It sounds like it should be useful, the data dictionary says: "For example, the Police Department has programs for Patrol, Investigations, and Administration." But it doesn't:
Police Department Programs
Maybe this worked at some point, but it's not a lot of help for the current year (It's a bit of a mystery, but not like that time I found out someone redistricted the Farallon Islands.)
It's also helpful to know that the department code has been prefixed to every department. So in the data the Police Department is 'POL Police'. This doesn't seem to happen elsewhere in the dataset. It probably helps to find monstrosities like 'MTA Municipal Transprtn Agncy' because we presumably can't find the spare change for longer column names.
Object Hierarchy
My favorite hierarchy is what we're spending money on at a reasonably granular level, broken down into Character, Object and Sub-object. This works both ways and you can look up sources as well as uses. An example on the revenue side is 'Fines, Forfeitures & Penalties', 'Traffic Fines', 'Traffic Fines - Parking' ($98 million).
Character, Object, Sub-object for Revenue
Character, Object, Sub-object for Spending
Sub-objects for the Police Department
While Program doesn't work, Sub-Object allows us to break down Police department spending in more detail:
Fund Hierarchy
Last but not least the fund hierarchy is where the money comes from. The three levels are Fund Type, Fund and Fund Category. As with objects you can also use this on the Spending side to see where funds are being spent.
Fund Type is critical to understanding the budget. There are three main buckets - General Fund, Enterprise Funds and Special Revenue Funds. Enterprise Funds are for self funding departments, so while $1.2 billion of the budget is for San Francisco International Airport that's not coming out of my pocket. The General Fund is discretionary spending and Special Revenue Funds are dollars that have been restricted to specific uses, for example by the endless ballot measures that San Francisco and California are so fond of.
Fund Type and Fund
This treemap shows the first two levels of the fund hierarchy:
Fund Type to Organization Group
Lastly this chart shows the flow of Fund Types to Organization Groups for Spending, excluding Enterprise Funds:
(Published to the Fediverse as:
San Francisco Budget - The Missing Manual #politics#sanfrancisco#budget Detailed guide to the three hierarchies of the San Francisco Budget from DataSF.)
I just published a San Francisco Budget GPT to the ChatGPT store. This is free to use, you just need an OpenAI account.
The chatbot is constructed from the 2010-2025 data available on DataSF, the most recent 2025-2026 budget draft and a five year revenue projection. I added some instructions to help interpret the data and I've tested against a range of queries. This is generative AI and so be cautious about what it says. It's pretty good when it can find the right data and will happily invent things if it can't.
Not that this is limited to chatbots. There was a lot of press yesterday around San Francisco being the worst run city in the nation based on this 'analysis' from WalletHub. They are dividing a measure of service quality by budget dollars per capita. This fails to take into account that San Francisco is a city and a county and so the budget includes county services like a Sheriff department. It also ignores that San Francisco runs an international airport and port, not paid for by taxpayers but still in the budget. And it doesn't adjust for regional differences in income that make services more expensive to provide (and higher total dollar taxes to provide them). So I'll take an occasional chatbot glitch over a willfully incurious press pushing out a PR piece that tickles their confirmation biases.
By Robert Ellison. Updated on Saturday, January 18, 2025.
Google Pixel 8 Pro 2mm f2.0 1/1,900s ISO41
Google Pixel 8 Pro 18mm f2.8 1/200s ISO16
I heard that we have over 2,000 Sea Lions at Fisherman's Wharf and had to check it out. There are thousands of them sleeping off their anchovy comas, a handful swimming around, and overflow onto many nearby docks. Worth checking out.
(Published to the Fediverse as:
2,000 Sea Lions #photo#sealion#sanfrancisco Photo of the thousands of Sea Lions currently hanging out at Pier 39 in San Francisco.)
(Published to the Fediverse as:
Sutro Island #photo#sutrotower#sanfrancisco#bolinas Photo of Sutro Tower appearing to be on a mysterious island, from Bolinas Beach in Marin County, California.)
Nope: "To unlock the device with a passcode, hold out your hand to project a green laser onto your palm. Pulling your hand outward increases the number while pulling it inward decreases it, and you select each digit by pinching two fingers on the same hand." #ml#humane
“The system is currently working just fine, but we know that with each increasing year, risk of data degradation on the floppy disks increases and that at some point there will be a catastrophic failure,” - maybe save $400M with a floppy emulator and a USB stick? #sfmta#muni#sanfrancisco
While waiting for the horror of Weekend at Bernie's vs. A Clockwork Orange in November there is time to contemplate another slate of job-outsourcing ballot measures. Just one for California and seven for San Francisco so it could be worse. Here goes...
California Proposition 1, Behavioral Health Services Program and Bond Measure
Yes. This funds housing and treatment for the mentally ill, homeless and veterans in need. It also requires counties to put more of their existing funds into housing.
San Francisco, California, Proposition A, Affordable Housing Bond Measure
Yes. $300M in bonds to build, buy and repair affordable housing. Homelessness is driven by a lack of affordable housing. In addition to building more I'd love to see us cut more of the red tape, but this is a necessary measure to meet our existing obligations.
San Francisco, California, Proposition B, Minimum Police Staffing Amendment
No. I voted against minimum numbers in 2020, and I don't see a good reason to bring them back today. I also recoil at the thought of a dedicated police recruitment tax as this measure suggests. Funding the police is a very basic city service, as is determining the appropriate staffing levels at any particular moment in time. I'm not against recruiting more police at all, but this is a bad proposal.
San Francisco, California, Proposition C, Real Estate Transfer Tax Exemption for Properties Converted from Commercial to Residential Use Initiative
Yes. I voted in favor of this tax in 2020. Post pandemic San Francisco has one of the worst return to office rates and a huge decline in retail. We need to rethink what downtown is for and I love the idea of bringing in more universities and more homes. So this is a tax break that makes sense for now.
San Francisco, California, Proposition D, Amend City Ethics Laws and Expand Restrictions on Gifts to City Officers and Employees Initiative
Yes. Tougher ethics rules are needed. Can't find any reason to oppose this package. We have bribing of inspection personnel, theft of public funds, corruption in Public Works, that inspector who inspected his own building, etc.
San Francisco, California, Proposition E, Limit Police Department Administrative Task Time and Increase Use of Camera and Drone Technology Initiative
Yes. Apparently when SFPD decides to chase someone they crash 38% of the time, about twice the state average. This bill would let them chase more people and use drones and GPS taggers to do it. I'd like them to go on an advanced driving course or two, but if you can avoid consequences just by running away then we don't really have a law enforcement system. Maybe I'll regret this in a few years but it seems mostly common sense to me right now.
San Francisco, California, Proposition F, Require Drug Screening for Certain Beneficiaries of the County Adult Assistance Program Initiative
No. It looks like the recipients of most of these funds are not homeless and have plenty of hoops to jump through already. This seems like it would risk making their situation worse.
San Francisco, California, Proposition G, Declaration of Policy Urging San Francisco Unified School District to Offer Algebra 1 to Students by Eighth Grade Measure
Yes. This is pointless as it has no teeth and they're moving this way anyway. I still want to help make the pointless point.
(Published to the Fediverse as:
San Francisco and California March 2024 Ballot Measures #politics#sanfrancisco#propositions#election#california ITHCWY voter guide to the San Francisco and California March 2024 primary election propositions and ballot measures.)
My poor street has had the works over the past few years. PG&E upgraded the gas lines. Then everything got ripped up for new water mains. We got two new poles. And then new sewers. I'm a little surprised the houses are still standing.
Over the last couple of months the train tracks for the L Taraval have been replaced. They're only two years younger than me, but I'm happy to report that they looked in much worse shape. Here's a time lapse of the whole process:
You'll see that they opened a chasm right in front of my garage early in the project and the plan called for it to be almost the last hole to fill in at the end. I'd be more excited if the L Taraval went anywhere useful. When I moved into my house it stopped right in front and then whisked you quickly downtown. Since then that stop was removed, and then the L terminated at West Portal to make room for M and N people and has been replaced by a bus for the duration of the pandemic and track replacement service.
With perfect comedic timing we also just got a letter from PG&E saying that they now need to dig everything up all over again. Probably we need medium rather than high pressure?
I have a tradition of making a time lapse of San Francisco on New Year's Eve. This year I focused on the Embarcadero:
The time lapse opens with a view of the Bay Bridge from Cupid's Span in Rincon Park. Then behind the Ferry Building for two departures and one arrival. Next is a view of downtown from the middle of Pier 7. From there some Embarcadero proper from the perspective of the glass tiles looking towards the Exploratorium and then looking across the street at Coit Tower and the Transamerica Pyramid from Pier 35. After that we have the Richmond Bridge visible behind Alcatraz and Angel Island and the SkyStar Wheel in its new home, both shot from the end of Fisherman's Wharf. Finally the Golden Gate Bridge with some brave swimmers in the foreground at Aquatic Park. This is just past the Embarcadero but I broke the theme a little to get all three bridges.
(Published to the Fediverse as:
Embarcadero #timelapse#video#sanfrancisco Time lapse of the Embarcadero in San Francisco on New Year's Eve 2023, from Cupid's Span to Fisherman's Wharf.)
