How WBEZ analyzed water debt in Chicago

https://ift.tt/3obIAl4

In 2019, WBEZ published an investigation into the rising cost of water in Chicago and how water shutoffs were disproportionately concentrated in low-income, mostly-Black and mostly-Latino neighborhoods in the city. Since then, at least six homeowners contacted WBEZ asking for help. Their stories were similar: Homeowners couldn’t keep up with the rising cost of water, fell behind on their bills and within a few years the debt became unmanageable.

Where did WBEZ get the data?

To understand whether this was a city-wide trend, WBEZ submitted 31 Freedom of Information Act requests to multiple city departments and the county since January 2020.

WBEZ obtained multiple databases, including:

  • delinquent water accounts

  • debt collector payments

  • every water service address across the city, indicating if the home had a meter or not

  • administrative law hearings cases between 2010 and 2021

  • statutory tax liens filed against homeowners between 2010 and 2012

WBEZ also obtained individual billing data with the homeowners’ permission.

To summarize wage garnishment data, WBEZ obtained monthly collection reports debt collectors have to submit to the city of Chicago as it is described in their retainment letter. The city required a FOIA request for this information.

We built a relational database to store these and other data sets so we could join and analyze them in a broader context.

How much do Chicagoans owe for past-due water bills?

Responding to a FOIA request, the city finance department delivered a spreadsheet of “delinquent accounts” that included more than half a million rows of data. A water customer who fails to pay a bill within 60-90 days is considered delinquent by the city of Chicago. WBEZ filtered the data to include only accounts labeled “active,” and further narrowed the list to only accounts with an actual balance due. This resulted in 222,000 accounts owing a total of $421.9 million.

Some accounts owed $0 or even had a negative balance, which the city described as “a credit balance” as of the run-date of the data. According to the city’s data, active accounts with credit balances totalled $10 million; inactive accounts showed another $10 million in credit balances. WBEZ did not include credit balances in its tabulation of water debt.

In addition to providing summary balances, the city also provided itemized breakdowns of balances, including water, sewer, taxes, garbage and other fees. When WBEZ pointed out that itemized balances did not always equal the total balances provided in the data, the city explained that the records were pulled separately on different dates but offered no guidance on which was more up to date. WBEZ tabulated the two data sets separately using the method described above, arriving at $421.9 million for the sum of the total balances and $426.6 million for the sum of the itemized balances. We reported the smaller of the two figures.

Who is most impacted?

WBEZ used demographic information by ZIP code from the U.S. Census Bureau to determine which communities were hit the hardest. We pulled table B03002 from the American Community Survey, which estimates detailed racial and ethnic characteristics based on sample surveys of residents taken from 2015 through 2019. This allowed us to look at population estimates along the general categories of American Indian, Asian, Black, Latino, white. We then determined whether a ZIP code had a majority of residents identifying with a particular racial or ethnic group.

We imported this demographic data into our database, alongside other data sets on water service and debts. Using ZIP codes as a key, we were able to join these data to determine the racial makeup of neighborhoods that were most impacted by water debt. We found, for instance, that the 11 ZIP codes with the most delinquent accounts per household are all majority Black, and the nine ZIP codes with the fewest delinquent accounts per household are majority white.

How did we find vacant homes with water debt?

Using Sylvia Taylor’s story as an example, WBEZ sought to determine whether there were other vacant properties with delinquent water debt. We downloaded a list of vacant properties from the city data portal. This included records of administrative notices of violations, the city’s enforcement mechanism for various code violations, including failure to secure a vacant building. It’s a limited data set that only includes a subset of vacant property, so the numbers we found could be considered a floor and not a ceiling.

We loaded the vacancy records into our database and looked for a way to join them to the debt records to see where we could find a match. Without an explicit key to connect the two tables, we made our own using street addresses. This involved a simple rules-based algorithm that parsed the address into component parts — number, direction, street name and suffix — and stored those parts into separate fields in the database. We then queried each of the 4,909 records in the vacancy table for corresponding records in the debt table that matched on address and where the debt was incurred within 90 days of the vacancy violation. We identified 160 such instances, including 48 where the home was unmetered. This raises questions as to whether the city may have billed homes for using water where it could have reasonably determined the homes were vacant.

WBEZ shows its work

We’re making a limited subset of the data available for download. Some of the files provided by the city contained extraneous information, such as inactive accounts or accounts with credit balances. Other information could potentially identify water customers with past-due accounts. WBEZ removed those data points.

Debt records

Administrative hearing records

Matt Kiefer is WBEZ’s data editor. Follow him @matt_kiefer. María Inés Zamudio is a reporter for WBEZ’s Race, Class and Communities desk. Follow her @mizamudio.

via WBEZ Chicago

November 8, 2021 at 08:19AM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s