Home

Visualizing the San Francisco budget (2013 - 2017)

The data

The original dataset is from SF OpenData, the City and County of San Francisco's offical open data portal. The dataset of interest is the budgetary data from the San Francisco Controller's office, published in the City and County of San Francisco's Annual Appropriation Ordiance (AAO) each fiscal year since 2010. The data shows spending and revenue at various levels of detail and is stated in nominal terms (i.e. not adjusted for inflation).

Data preprocessing

For our project, we have chosen to focus on revenues from 2013 to 2017. We are mainly interested in visualizing the sources and growth of revenue and how that revenue is allocated. To help us achieve this goal, we have applied the following filters when pulling data from SF OpenData:

  • Filter for 2013 onwards (2013 - 2017)
  • Filter out the spending portion of the dataset
  • Filter out amounts used for adjusting for double-counting. Specifically, we filtered out the following character codes:
    • Transfer adjustments (910 - 950, ELS)
    • General fund support (GFS)
    • Work order recoveries (865)
    The net sum of these character codes is zero.

As previously-stated, the data are in nominal terms. According to the BLS, the value of a dollar in 2014 and 2015 is on parity with that of a dollar in 2016, thus no adjustments would have been necessary. The only adjustment would have been made to 2013, where $1.00 is worth $1.02 in 2016 terms.

Description of filtered data

Our filtered data contains 5,615 rows and 22 columns. The "Amount" column holds the revenue (or spending) amount published in the AAO, stated in nominal dollar amount. Each "Amount" entry is associated with various attributes in the remaining columns. The columns/attributes we are using for our visualizations are:

  • Fiscal Year: 12-months period beginning on July 1 and ending on June 30 the following year.
    • Data type: numeric
    • Data range: 2013 to 2017
  • Organization Group: Highest level grouping of organizational units within the City government.
    • Data type: string
    • Data range: 7 groups
  • Character: Highest level grouping of revenue and spending activity types.
    • Data type: string
    • Data range: 17 activity types
  • Fund Type: Highest level grouping of fund accounts. The City allocates money for particular uses by putting them into different fund acounts.
    • Data type: string
    • Data range: 22 fund types

For more details regarding these attributes, please see sfgov's glossary of terms.

Our visualizations

rev by character

Revenue source

A visualization of the total amount of revenue by year and how that revenue is broken out by revenue source.

yoy rev growth

Revenue growth

A visualization of the year-over-year growth rate of the top 5 revenue drivers in comparison with total San Francisco.

rev allocation by fund

Revenue allocation

A visualization of how revenue, on average between 2013 and 2017, is allocated between organization groups within each fund type.

The Data Vizzards

Marissa headshot

Marissa Masangcay

email: mjmasangcay@dons.usfca.edu

Bio

I’m currently a senior at the University of San Francisco. I’m pursuing my second Bachelor’s degree in Computer Science. Despite being born and raised in the heart of Silicon Valley it wasn’t until I moved up to San Francisco that I was exposed to the field of Computer Science. Once I was introduced to it I found it to be so interesting and intriguing I decided to make the switch to programming and I haven’t looked back since!

Contributions

I worked on the revenue source vertical normalized stacked bar and line combo chart.

Helen headshot

Helen Chen

email: hhchen@dons.usfca.edu

Bio

I am currently a second bachelor's student in Computer Science at USF. I received my first bachelor's degree in Economics and spent six years working extensively with data in the retail sector as an inventory planner. I also spent one year teaching middle school students, where I developed a strong appreciation for data-driven instruction. As a consumer of data through my seven years of work experiences, I am particularly interested in seeing how data visualization can help drive better decision making.

Contributions

I worked on the revenue growth small multiples chart as well as this home page.

Mindy headshot

Mindy Huang

email: mhuang22@dons.usfca.edu

Bio

I am a senior majoring in Advertising and minoring in Computer Science at the University of San Francisco. I started college as an Advertising major, knowing nothing about programming or any language that only CS people and computers can understand. Going to college in San Francisco, the heart of the most innovative high-tech hub in the world, has changed my perception about CS. I took CS classes at school, I felt a door to a brand new world had opened for me. My mind was exposed to the fascinating world of logic, coding and programming. After a Python class in the following semester, I declared my minor as Computer Science. I learned Java and use it to develop a basic search engine. And I am currently learning Java Script, CSS, HTML for my Data Visualization class.

Contributions

I worked on the revenue allocation horizontal normalized stacked bar chart.