Data Engineering fellows notes

[damonmcc]

a place for links and discussions for fellows @DeaBardhoshi and @athursland

general links

• data-engineering: our new mono repo where we're consolidating code from other repos
  • data-engineering wiki: the wiki in the mono repo (in-progress)
• db-data-library: how we archive source data
• db-cpdb: was used to build the Capital Projects dataset
  • has been copied into the mono repo but not built in it yet

Historical Capital Spending

• original issue with details on data sources and quality: https://github.com/NYCPlanning/edm-overview/issues/819

[AmandaDoyle]

@athursland and @DeaBardhoshi see running list of notes below to guide work for Thursday's meeting. cc @fvankrieken (please feel free to add)

• [x] What is the universe of the Checkbook NYC data?

• How many rows?

• What is a row? Or what does a record represent?

• What are the columns?

• Number of unique projects? (i.e. number of units project ids)

• [x] General summary statistics of the data.

• Breakdown by agency (number of projects, $s spent)

• Spending over time

• [ ] Amount of data that can be mapped as both number of projects and $s and % of projects and % of $s, and any other summary stats (i.e. by agency, by year, or by category if you were able to categorize them into 'Fixed Asset' or?)

• Overview of method(s) for adding spatial data for projects

• Overview of spatial data by source (i.e. Facilities Database, Parks Properties, DDC or?

I think the next steps are to:

• Create visualizations and pull data for the above. Easy to get started on describing the universe of data and creating visualizations for summary statistics
• Diving more into adding spatial data to projects using:
  • Facilities Database
  • Parks properties database
  • DDC data
  • Geosupport? (this may be too much effort for the reward).

Happy to collaborate however is most helpful!

[AmandaDoyle]

https://github.com/NYCPlanning/edm-overview/issues/819#issuecomment-1610085125

[AmandaDoyle]

Next steps from 06/29 meeting 1) Collapse checkbook NYC data to the project level to make it easier to work with (~926,562 records). Do not clean data, as it was indicated that the checks we thought were odd may be valid. 2) Familiarize yourselves with the Capital Projects Database aka CPDB 3) Run Checkbook NYC projects through CPDB categorization process and create report of number of projects in each category and number of $ in each category (1) ITT, Vehicles, and Equipment 2) Lump Sum 3) Fixed Asset or 4) NULL). I'm happy to help walk through the logic. 4) Join Checkbook NYC data onto CPDB. I think that this will best be done / setup with another DE team member to help get it going. 5) Report back results and discuss potential next steps. Future work may focus on specific agencies, or?

[AmandaDoyle]

Morning! Did some digging to 1) help collapse records and 2) join Checkbook NYC onto CPDB

To join checkbook NYC data onto CPDB it appears we need to remove the last three digits and any trailing white space from the Capital Project value. For example, if I search 998CAP2024 005 in CPDB here I do not get a result, but if I search 998CAP2024 I do get a match. Same thing with 841HWMBRT5 801 vs 841HWMBRT5.
Given this, I recommend 1) cleaning the Capital Project values by removing the last three digits and trailing whitespace and renaming the column to FMS ID 2) Group by the following columns: Agency, Budget Code, FMS ID (old Capital Project` value), SUM(Check Amount), Expense Category, Fiscal year, Spending Category. You can drop all other columns.

[AmandaDoyle]

@athursland and @DeaBardhoshi for tracking and note taking For cleaning the CheckbookNYC data please remove negative checks and checks with a value equal to $99,999,999

[athursland]

Going to try joining on cpdb. Jumping off of our conversation during stand-up, is the Digital Ocean file 23_Q2_build/latest/output/cpdb_projects.csv the right file to grab for now? @fvankrieken @damonmcc

[fvankrieken]

Yes, but with the caveat of no geometries in that file. The shapefiles in that folder have geometries, but obviously need to be converted into something you can use. I think you could use gdal/ogr2ogr for that but there's some overhead in that as well. Or actually I guess you could pull those into geopandas, if you're using geopandas already?

[fvankrieken]

Otherwise if it's easier for you, you could point a query at edm data. You'll need a new connection string to supply to sqlalchemy's create_engine function, in the format postgresql://{user}:{password}@{edm_data_url}:25060/defaultdb - args for that can be found with the rest of our secrets

Schema cpdb, table cpdb_opendata_projects_pts or cpdb_opendata_projects_poly (for points or polygons) I believe

[athursland]

I was planning on using geopandas but I think querying the data would probably be a better exercise! Will reach out if I have any roadblocks

[athursland]

Whenever convenient, can someone provide guidance on where to grab archived CPDB with geometries?

[damonmcc]

@athursland if you have access to the edm-publishing S3 bucket, I'm seeing some versions in db-cbdp/main/. but that only goes back to 2021

I'll keep looking for conveniently-located older versions

[athursland]

Notes on this sprint's work, @DeaBardhoshi feel free to fill in details on the categorization work:

• CreatedFMS ID column in Checkbook NYC per Amanda's instructions, and filtered to include only rows where Check Amount < 99,000,000 and >= 0. Then we grouped the data by FMS ID, Budget Code, Agency, Fiscal year, Expense Category and Spending Category, and aggregated the sum of Check Amount for those groups. Where there were different possible values for the grouping categories, we arbitrarily chose the first one. Resulting dataset contains 80,932 rows.
  • file: group_checkbooknyc.ipynb
• First adapted CPDB categorization script to run on Checkbook NYC Budget Code column. Explored ways to improve these assignments by adding key words to SQL query and running on Contract Purpose category as well.
  • file: checkbook_categorization.ipynb
• Got all the CPDB shape files 2017-2023 and merged them together, keeping the most recent geometry where multiple existed for a given capital project. Left joined this onto new Checkbook NYC dataset. 29,813 Checkbook NYC projects were joined with geometries from CPDB.
  • file: cpdb_join.ipynb Outputs can be seen on the slide deck

[athursland]

Possible extensions:

• After joining historical CPDB shape files, some projects had more than 1 geometry (the geometry changed over time). There may be value in adding a column to CPDB shape files noting the year of the build + joining onto Checkbook NYC projects from the same year...?

[DeaBardhoshi]

Further directions/ideas:

1. Building on the categorization work:

• Exploring the differences in the categorizations between the two query methods? E.g why did ITT, Vehicles and Equipment have the most change (+4%), why did the NULL/unassigned decrease by 7%?
• Creating categorization visualizations sliced by other columns, e.g. agencies or year.

2. NLP-related work for Projects pre-2017:
   • Exploring the info in Contract Purpose + incorporating that into adding spatial information
   • Looking into extracting place-name mentions (fuzzy str matching, NER or other methods)

[athursland]

Notes from Tuesday 7/11:

• We are interested in describing rows in the collapsed Checkbook NYC dataset as projects, which is untrue in its current form, so we decided to reduce the number of columns we're grouping by. Namely, we are only grouping Checkbook NYC on FMS ID and instead concatenating all non-null values of Budget Code, Contract Purpose, and Agency within those groups. Fiscal year and all other columns are being dropped. Might return to Fiscal year for future temporal analysis but outside of the scope of the deliverables we need for Capital Planning.

[athursland]

Notes from Wednesday 7/12:

• The final Checkbook NYC dataset collapsed on FMS ID has 16,678 rows, each representing a single project. When the adopted CPDB shape files 2017-2022 and the executive CPDB shape file for 2023 are left joined onto Checkbook NYC, 3,880 projects map to a geometry.
• Tweaked high-sensitivity Fixed Asset categorization method to assign final_category based on only bc_category and cp_category in the cases where Checkbook NYC projects did not join to a CPDB geometry, and therefore can't have a cpdb_category value. Now every row in the final dataset should have a value for final_category regardless of whether it has a geometry from CPDB.

[athursland]

Notes on odd ends of this sprint:

• There is one capital project in the collapsed version of Checkbook NYC that accounts for $20B in spending alone, looks like SCA accounts for most of the agencies in this project - and the FMS ID is just '040A' which is a little suspicious. Worth investigating later

[DeaBardhoshi]

Some notes on further categorization directions with the Checkbook NYC data:

• Keywords categorization
  • Adding new mentions: “accessibility”, “equality” or “ADA” as well as new technologies (electric vehicles)
  • Time patterns, since the project is a historical look: how has the prioritization of these projects happened over time?
• Evaluating the existing categories:
  • Why do projects change categories when using different columns? Which categories gain/lose the most between the two methods (budget code vs contract purpose)
• Can the categorization be formulated as a classification problem in which more columns are used to predict the categories?
  • Adding agencies as features: some agencies might correlate to more fixed asset projects.

[fvankrieken]

So for norms around PRs:

• open a draft PR early in process. Stubs are okay for the description but have a descriptive title and at least a stub or link to an issue with relevant information. Tag other fellow in the description so that they receive notifications for this PR
• request input on specific bits of code whenever you'd like in development process, easiest to do on lines in the PR diffs. For full brainstorm, opening an issue might be easier just to organize the conversation and not overly clutter the PR
• mark as ready and request review when finished. For now, specifically from one engineer, just so feedback can be a tad more focused. Certainly feel free to tag others with questions, I did this in one of my comments in a review today.
• implement changes as needed, request final review (from same reviewer), and if approved merge

[athursland]

Note for YAML file for reading in CPDB geoms: - currently uploading only the shapefile to DO, but we want to update this to include the whole CPDB geom subdirectory with all the relevant files @DeaBardhoshi

[athursland]

Notes on results from unit testing:

• Category assignment based on budget code and contract purpose seems to be failing in the build script, I suspect it has to do with the away we're running the SQL query right now - need to return to this
• We should probably optimize the category assignment code to use a parameterized SQLAlchemy query instead of just replacing the placeholder COLUMN and col_category terms with the column of interest, ref here: https://stackoverflow.com/questions/19314342/python-sqlalchemy-pass-parameters-in-connection-execute

[athursland]

Ideas for extending testing:

• If we move towards ingesting Checkbook NYC data using their API, Pydantic might be a good tool to use for data validation.

[athursland]

Observations from exploring laying in parks properties geometries:

• 930 capital projects that were not mapped joining on CPDB include checks issued by DPR
• Many of the budget codes/contract purposes related to parks have typos in Checkbook NYC (i.e., 'Highline' is consistently spelled as 'Hignline')
• Some names in the Parks Properties dataset are slightly different than those in budget codes/contract purposes in CSDB. For example, there are lots of rows in CSDB where the agency is DPR, the BC/CP references 'Randall's Island', but the parks properties dataset calls it 'Randall's Island Park' )

[athursland]

Notes about potential enhancements (running) :

• Currently we are joining only the subset of CPDB that contains geometries onto Checkbook NYC, however we should probably be joining all of CPDB. The only information we're grabbing from CPDB right now is the geometries + the category, but having a CPDB category for as many of the capital projects in Checkbook as possible will be useful for the final_category assignment as we layer in more source of geometries (like parks properties).
• When we concatenate agency/budget_code/contract_purpose into a ';' delimited string we should absolutely be checking for duplicates and we aren't doing that currently