Hacker News new | comments | show | ask | jobs | submit login
Philadelphia budget data visualization (brettmandel.com)
85 points by bengarvey on Jan 23, 2013 | hide | past | web | favorite | 61 comments

This is a nice visualisation but it's not clear to me what the purpose of it is - other than the immediate "WOW look how much is spent on Police + Prisons!" that struck me. Or is it simply a "check it out, I know what comprises Philadelphia's budget...".

I'm a total outsider so I admit it's entirely likely that this means something to folks living there. But either way it would be nice if there was a little blurb on what he's trying to say or some attempt to explain what the budget would look like if he was elected. As it is this is simply "Look at this budget, just look at it".

It also seemed odd to me that so much was under "Finance Department". I'd figured Finance would cover the actual bookkeepers, interest on bonds, and other things related to money in the abstract. From its size that budget must also be "Stuff that doesn't go anywhere else".

In most cities this also covers revenues, aka tax collection. It would be nice to see the breakdown on how much of the budget is dedicated to collecting taxes to fund the budget.

When you drill down you get to the actual entries in the budget that correspond to the particular thing you are looking at.

It was interesting all the Director of Finance salaries of 900K - 1.4M. Seems like a lot of money to pay a civil servant but I may have been misreading the budget item.

I was looking at things like this:

192 FICA TAXES 0192 DIRECTOR OF FINANCE - - - 2012 - $1,369,489.56 PPE 07/10/2011 FICA MATCH CITY OF PHILADELPHIA

Seems to suggest that this director made $1,369,489.56 in compensation.

Nothing compared to the $15 million it shows as total compensation for the sheriff. (Don't know how to link it; just go to the farthest-right gray square that touches the bottom of the overall rectangle.)

That's to the Sherriff's department. It's all the salaries of deputies and whatnot. If you click on a box after zoomed in it gives an itemization.

Looks like the Sherriff (Barbara Deeley) had two line items for salary and made about 130k or so.

It's the pension costs from retired Police and Firefighters.

$438MM on pensions.

If you had been to Philly recently it would make total sense how much money is spent on police and prisons. They're doing yeoman's work making the city habitable again.

EDIT: No offense to Philly, but it's by a large margin the most dangerous city with more than 1 million people in the U.S. And that danger isn't as segregated as it is in say Chicago or New York, making policing harder.

Just stay out of North, South, West, and East (Camden) Philly.

Due-south/south-east are fine, southwest is awful (~15th and west). West is okay provided you don't go further than, say, 40th. North is a warzone aside from the narrow strip following the Schuylkill river. Fishtown is okay.

Thankfully the Delaware river provides an adequate barrier between east Philly and Baghdad... err, I mean Camden. You can go as far east in Pennsylvania as you like and be fine.

Philadelphia is sort of an inversion of the stereotypical "ghetto in the middle, suburb surrounding" model, the main line being an exception to this.

I'm moving there next year, so this is quite useful information. Thanks.

No problem! I lived in Philly off and on (mostly on) for 5 or 6 years up until about a year ago. The 'grunge'/grime is probably a bit of a culture shock at first (depending on what you are used to) but it's a pretty nice city once you get over that.

If I were to move back, I would probably outside of the 'city' Philly along the main line. Lowest grime-factor there is, as far as I know.

Unless you've got kids or are going to school, I'd live in center city. For a fairly small and completely walkable area, it's got variety, from high rise apartments to "George Washington slept here" houses two blocks south. Best overall option in the city. Or live outside the city, but that's not living in a city.

Yeah, I'll be working in Center City and it seems like there are some nice places to live. What do you think about the area around Penn?

The campus is nice, but it'll push you west. Unless you're going to Penn, there's no advantage to living at, say, 40th and Spruce vs 20th and Spruce.

I walked all over the city looking at neighborhoods. If I were doing it over, I'd start at one river and walk down Market to the other, then back on Walnut, then back on Pine, then back on South. That's like 3-4 hours and gives a good feel for the area.

That's info can be found on the main site http://brettmandel.com

Nice visualization but HOLY CRAP is that normal for the police department suck up so much of a city's budget?

I live about a mile north of City Hall, but I grew up in a small town (pop 3000) in south-central PA. I've been living in Philadelphia for only a few years now, but every single night, I hear sirens, several times a night. And several times a week, I hear helicopters. I'm used to it now, but the police activity is on a level I never could have imagined existed in America when I was living in my tiny little town. Parts of the city near Temple are lit up and patrolled like it's the green zone in Baghdad.

When you stop and think about it, there is a near-constant state of emergency, and this no doubt applies to any major American metropolitan area. I'm not at all surprised that the police department has such a huge budget here.

20-25% is about average for non-pension expenditures on police.

It's easy to lose context of just how massive a part of PA Philadelphia is. The city proper alone has more than 10% of the state's population (1.5MM to PA's ~12.7). Add in the other PA counties and you're close to 4. Add in the out-of-state parts of the Metropolitan area and you're quickly closing in on 6.

I've always felt safe when I've been there but given the sheer volume of people, I'd surprised they're not spending a larger amount of police forces.

Not even the worst of it. All the firefighter and police pension expenses are in the Finance Department.

I was going to say that 20% seems exceptionally low, but I see how the pensions were being backed out.

Fire and public safety, and their corresponding pensions, make up the bulk (like often >50%) of all municipal expenditures.

See: http://www.city-data.com/crime/crime-Philadelphia-Pennsylvan...

Every day Philadelphia has about: 1 murder, 2 rapes, 25 robberies, 25 assaults, 35 burglaries, 110 thefts, and 20 stolen cars.

The active police + prison budget seems to be about $850 million. That's about $570 per person to keep 1.5 million people safe in one of the poorest major cities in the country.

maybe they should increase the 'streets' budget (and not just have 1 company get all the contracts) and then clean up that gigantic pile of trash that the city is. never seen a more filthy city than Philadelphia

New York is definitely dirtier than Philadelphia.

Try running for office in a city with a platform of "cutting the police budget" and see how far you get...

You could end up with the police union buying the house next door to you, staging protests there and posting signs about how dangerous your making the city. You could even end up finding yourself or relatives followed for their protection as well.

It is not the public someone who wants to run on that platform has to fear

In a city with "1 murder, 2 rapes, 25 robberies, 25 assaults, 35 burglaries, 110 thefts, and 20 stolen cars" PER DAY -- the public is exactly who you'd better be afraid of.

Percentage-wise it looks pretty normal. $650 million out of $3.5 billion is a bit under 20%, which is common. I used to live in Santa Cruz, a much smaller town with a much smaller police department, but about the same percentage of city funding: police took $22 million out of $101 million (http://www.cityofsantacruz.com/Modules/ShowDocument.aspx?doc..., pp. 22-23).

You are running for comptroller of Philadelphia?

Best of luck to you. It seems like you have the smarts to get noticed for your merit.

Brett and I worked together on the project.

Great job! Can you speak (or maybe a blog post would be better) about how you put this together? What did you have to do get/massage the data? What tech did you use, etc?

It's nice to see stuff like this coming out of Philly.

The site uses D3.js http://d3js.org. I'll write up a full recap later.

Neat. Was some tool used to construct this, or was it done manually?

What may not be obvious to people initially when they look at it, is that when you see the label "Department of Finance" on the gray rectangle that is actually the label for the gray rectangle plus the green and orange ones adjacent to it -- the different colors represent the expenditure classes described on the "About" page. It would perhaps be clearer if there were thick black lines separating the items (instead of just a thick line of the background color) and zero separation between the colored rectangles representing the breakdown by class within an item (which currently have thin lines of the background color separating them giving the impression that they are separate items).

The treemap was made using D3 with some customizations. All the source and datasets are on github.

The department groupings are done with spacing. They are subtle, I know.

Right at this moment, I am adding color coding to the About modal.

Mind sharing the github link?

If you want to make some budget visualizations of your own, let me link this site we've been working on:


It's powering a variety of budget sites, like http://wheredoesmymoneygo.org/, http://bund.offenerhaushalt.de/ and http://cameroon.openspending.org/en/. People can just go an upload their own data quickly - OpenStreetMap for money :)

Philadelphia needed someone to put this together... it's amazing that with open data, a campaign can help create something of real and lasting value in the course of their pitch.

gov 2.0 <3

> $1,062,921.12 PANASONIC TB19/2GB/TOUGHB

The prisons department needs a lot of toughbooks. How many toughbooks do you get for a million dollars?

265 @$4000. Base price is ~3400, but figure it comes with warranty and service addons and whatnot.

Almost $4,000,000 a year on "sidewalk falls". Does anyone know what % on average an injury lawyer makes from those settlements?

You can see the salaries of individuals, at least within the police dept. Is this normal for US cities? I've seen situations where public servants earning more than a certain threshold (eg 100kpa) had their names & salaries published. Doing so for everyone down to crossing guards seems like an unnecessary breach of privacy.

It's quite common. Chicago just overhauled their website and lists the salary of every single public employee: https://data.cityofchicago.org/Administration-Finance/Curren...

as a Philadelphian, this is great.


Neat. I'd add a legend for the colors and some kind of detail for the lower-right corner tiny stuff that kind of seems like it might as well not be there.

Working on that right now! Should be up shortly.


Those decimals are distracting and dont seem to have any purpose at this scale. Also it may be easier if you just put the $s in millions.

That is the coolest thing I've seen in a while. Really helps you drill down to where the money is going in the budget.

also, interesting he doesn't have anything to say about the incumbent. http://www.philadelphiacontroller.org/biography-of-city-cont...

Is there a term limit or something? They're both dems.

Philadelphia is a one party town. The real race is the primary.

This is just fascinating, nice work!

The detailed listings should be sorted by expenditure to better explain what's in each category.

Where does education fit into this?

Entirely different budget. This is the General Fund which doesn't include the school data.

To add to that, the Philadelphia School District is completely separate from the municipal government: its funds come from separate taxes, and (since 2001) it reports directly to the Governor, not the city government.

Cool, shall share!

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact