-
Notifications
You must be signed in to change notification settings - Fork 0
/
README.qmd
64 lines (42 loc) · 5.46 KB
/
README.qmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
---
format: gfm
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r, include = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>"
)
```
# Baltimore City Capital Improvement Program (CIP) Data
<!-- badges: start -->
[](https://lifecycle.r-lib.org/articles/stages.html#experimental)
[](https://opensource.org/licenses/MIT)
[](https://www.repostatus.org/#wip)
<!-- badges: end -->
The goal of baltimoreCIP is to combine legacy reports and spatial data for the [Baltimore City Capital Improvement Program](https://planning.baltimorecity.gov/planning-capital-improvement) into a tidy data source for internal and public use.
::: callout-tip
This project is built using {targets} and {tarchetypes}: two R packages designed to support the reproducible analytical pipelines (RAPs). For more information on {targets}, see [The {targets} R package user manual](https://books.ropensci.org/targets/).
:::
For questions, please contact Eli Pousson, Data Lead with the Baltimore City Department of Planning, at eli.pousson\@baltimorecity.gov.
## Organization
This project is organized around three main folders:
- `files`: Required files including public Microsoft Excel and PDF reports downloaded from the [CIP Reports and Resources page](https://planning.baltimorecity.gov/planning-capital-improvement/maps) on the Baltimore City Department of Planning website. Files are organized into sub-folder (`agency`, `budget`, and `program`) based on report type. PDF program reports for 2008-2013 can also be downloaded using the `download_pre_2014_program_reports()` function.
- `R`: Scripts and functions for processing the files into a tidy data format using the `{tidyverse}` family of packages, `{pdftools}`, `{sf}`, and other packages.
- `data`: Output data based on the reports in both CSV and GeoJSON format.
The [data folder](https://github.com/city-of-baltimore/baltimoreCIP/tree/main/data) contains the following files for public and City use:
- combined CSV files for the 2008-2013 reports (`FY08-FY13_CIP-Budgets.csv`),
- 2014-2024 reports (`FY14-FY24_CIP-Requests_Source.csv`) including both original data and summary fields,
- a GeoJSON file (`FY14-FY24_CIP-Requests_Locations.geojson`) with the locations associated with the 2014-2024 period,
- and a combined CSV file (`FY08-FY24_CIP-Budgets.csv`) with reports from both time periods (including only the totals from the 2014-2024 reports and a summary total for the 2008-2013 reports)
As of July 2024, DOP staff are working to compile spatial data for the 2008-2013 period. The folder also includes a data dictionary for the 2014-2024 data (`FY14-FY24_CIP-Requests_Source_Dictionary.csv`).
## Background
The Capital Improvement Program is a six-year plan for funding capital projects by City agencies. The program is updated and adopted each year as part of the Baltimore City Budget.
Prior to FY2024, Baltimore City used a custom SQL database that allowed limited reporting with Excel and PDF exports. These exported reports are used in this data analysis pipeline to create a flat file of CIP requests and approved capital budgets for agency research and reporting needs.
It is important to note that data created based on these reports several significant limitations:
- The budgeted amount and previously appropriated amounts do *not* account for transfers between accounts (transfers are reviewed and approved by the Planning Commission outside the CIP process).
- The year funds are *appropriated* in the capital budget is often not the same year funds are *spent* or the year that work is completed. Due to constraints on the size of the capital budget, agencies have often needed to set aside funding over a multi-year period to fully fund a large scale capital project.
- The number and amount of the requested funds and approved capital budget is contingent on the availability of funding from local, state, and federal sources as well as agency-identified needs and commitments.
- Locations are identified by agencies as part of the initial capital requests submitted to the Department of Planning. These prospective locations may include sidewalks, alleys, or buildings where work *may or may not* have been completed if the budgeted capital funding could not cover the project's full scope and cost.
There are also some differences between the 2008-2013 data and the 2014-2024 data. The earlier period is based on program reports listing budgeted and programmed funding by source and fiscal year at the point of Board of Estimates review and approval. The later data includes both the earlier stages of agency requests and Planning Commission review and the later stages of Ordinance of Estimates adoption.
Between 2023 and 2024, Baltimore City migrated contract and financial management systems to a cloud-based application known as [Workday](https://www.workday.com/). Data on the Capital Improvement Program is now stored in both Workday and a second integrated application, known as [Workday Adaptive Planning](https://www.workday.com/en-us/products/adaptive-planning/overview.html) (or Adaptive for short), that combines project information entered by agency staff with financial data.