list of data quality checks for accurate count of units per record in PLUTO

[sf-dcp]

Come up with a list of initial data checks to identify lots with potentially incorrect unit count.

• [x] Review PTS data and understand expected values for different kinds of lots
• [x] Identify what and how is aggregated from PTS to calculate unit counts
• [x] Review #831 for issues and identify major offenders that could be caught

[sf-dcp]

PTS data expectations:

• contains tax lots with unit counts in columns coop_apts & units
• condos:
  • all unit lots matched to a primary bbl.
  • they are expected to have a primary bbl with 75** lot.
  • primary record should have zero in coop_apts & units.
  • condo units have lots 1001 – 6999 and they are expected to have count of 1 in coop_apts & units. Condo units can be floors, individual retail shops or blocks of office space in a commercial condominium.
• coops:
  • 1 record with total unit count per lot
  • lot doesn't start with 75**
• vacant lots should have 0 in unit counts.
• multiple non-condo buildings on 1 lot should have 1 PTS record.
• multiple lots sharing 1 building: shouldn't be the case but happened before.
• building class column in PTS is helpful to understand the type of units on a given lot.

How units are computed

• aggregate counts from pluto_rpad_geo table in create_allocated.sql script:
  • group non-75-lot records by billing bbl and sum up unit values
  • values are aggregated into pluto_allocated table

Checks

The mentioned GH issue in the description outlines different kinds of issues. Here are some:

• common issue with condos where they were likely converted from coops and unit lots list total # of building units.
• vacant lots with plans for future development being recorded in PTS as an actual building with non-zero units.
• one building spanning over multiple lots and each lot lists total # of units, multiplying total count by the number of lots.

The challenge is these issues can be difficult to identify and they require research on exact patterns. Therefore, we will start with one check from the first bullet point: we will create a check, research failed cases for patterns, and develop a strategy what should be done with these cases.

Check description

• create a test for (unit bbl) coop units > 1 for > 1 records, (unit bbl) total units > 1 for > 1 records, primary bbl total coop/non-coop units > zero
• how many of those have been corrected historically?
• crosscheck remaining units with housing db and calculate overcounting
• check for any patters to see if there is a way to identify correct # of units programmatically
• share results with Amanda
• next step for the failed records: send list to DOB, investigate offenders and correct as necessary
• It's a primary offender for overcounting and it's a result of coop -> condo.