Each row represents a ogranizational groups' information. Below is a table with a breakdown for the definitions of all of the columns from the SF controller's database.
| Row Name | Description |
|---|---|
| Fiscal Year | An accounting period of 12 months. The City and County of San Francisco operates on a fiscal year that begins on July 1 and ends on June 30 the following year. The Fiscal Year ending June 30, 2012 is represented as FY2011-2012. |
| Revenue or Spending | Revenue or Spending Amount |
| Related Gov't Unit | In SFOpenBook, these are fiduciary funds and component units that are included in the City’s financial statements but are different in nature from the other funds and organizations in City government. Fiduciary Funds include all Trust and Agency Funds that account for assets held by the City as a trustee or as an agency for individuals or other governmental units. |
| Organization Group Code | Numerical ID for organizational group |
| Organization Group | Org Group is a group of Departments. For example, the Public Protection Org Group includes departments such as the Police, Fire, Adult Probation, District Attorney, and Sheriff. |
| Department Code | Numerical ID for department |
| Department | Departments are the primary organizational unit used by the City and County of San Francisco. Examples include Recreation and Parks, Public Works, and the Police Department. |
| Program Code | Numerical ID for program |
| Program | A program identifies the services a department provides. For example, the Police Department has programs for Patrol, Investigations, and Administration. |
| Character Code | Numerical ID for character |
| Character | In the type hierarchy, Character is the highest level. For example, salaries, benefits, contractual services, and materials & supplies are recorded as different Characters. |
| Object Code | Numerical ID for object |
| Object | In the type hierarchy, Object is the middle level. For example, within the Salaries Character, Objects differentiate between Permanent Salaries, Temporary Salaries, and Overtime pay. |
| Sub-object Code | Numerical ID for sub-object |
| Sub-object | In the type hierarchy, Sub-object is the lowest level of detail. For instance, within the Overtime Object, Sub-object segregates overtime for nurses from overtime for police officers and fire fighters (known as uniformed staff). |
| Fund Type Code | Numerical ID for fund type |
| Fund Type | In the Fund hierarchy, Fund Type is the highest level, and is used to group Funds according to governmental accounting standards. |
| Fund Code | Numerical ID for fund |
| Fund | In the Fund hierarchy, Fund is the middle level. For example, within the Special Revenue Fund Type, you can find the Children’s Fund and the Open Space & Park Fund. |
| Fund Category Code | Numerical ID for fund category |
| Fund Category | In the Fund hierarchy, Fund Category is the lowest level. Within Fund, Fund Categories group activity by their characteristics: Operating, Annual Projects, Continuing Projects, Grants, Interdepartmental/Work Order. |
| Amount | The amount published in the City and County of San Francisco's Annual Appropriation Ordinance. |
Before I attempted to make any visualizations, I used this infation rate calculator to calculate inflation rates to adjust the revenue and spending amounts. After getting the inflation rates for each year, I used this formula: Amount + (Amount * Inflation Rate) in Excel to calculate the adjusted revenue and spending amounts.
For each of the core visualizations, including the heatmap, linked line and bar charts, and stacked bar chart, I protoyped the visualizations in Tableau and exported the data used in the particular visualization to a CSV.
For the hierachy visualization, I added a "sfgov" column in the beginning and deleted all other columns except for organizations and departments in Excel and converted it to a TSV. I stripped any existing commas in the names and replaced them with colons in UltraEdit text editor. I put colons because it was a unique character and was not used in any of the names in the file. Then, I replaced all tabs in the TSV with a period in UltraEdit. Afterwards, I sorted in alphabetical order and kept unique strings by putting the following unix command in Terminal: sort -u filename. Lastly, I converted it to a CSV and added "name" header to the column, so I can refer to the column in my D3 code.
© 2016 Bootstrap HTML Template by WowThemes.net