Cleaning ages of -500 with rules, not formulas

The first summary table of the credit data read like a prank. Ages ran from -500 to 8,698. Someone had 1,798 bank accounts and someone else 1,499 credit cards. One interest rate was 5,797, and one customer had made 2,597 credit inquiries. For those last four columns, three quarters of rows sat at or below 7, 7, 20 and 9.

Placeholders first

Before any outlier work, fake values had to become honest blanks. Credit mix used _ for unknown in 20,195 rows, and occupation used _______ in 7,062. Nine monthly balances read __-333333333333333333333333333__, and 4,305 investment amounts were __10000__. Converting each column to numbers, with anything unreadable turned into a blank, and then swapping the placeholders for blanks, meant every rule after that saw either a real number or a real gap.

First try: one formula for every column

My first notebook ran an IQR rule over every numeric column. The IQR, or interquartile range, measures the spread of the middle of the data, and values far outside it are cut. The band was widened to the 15th and 85th percentiles, a gentler cut than the usual 25th and 75th, and 79,119 rows survived.

The trouble with a formula is that it measures distance from the middle, not whether a value is possible. It treats a rare but real value and an impossible one the same way, and it can’t tell you why a row went.

The fix: limits set by what’s possible

The final notebook replaced the formula with a plain limit per column, set by what each value can actually be:

• Age 18 to 80. 18 is the youngest you can get a credit card, and 80 sits above a typical issuer’s upper age of about 70.
• Credit cards 0 to 15.
• Delayed payments 0 to 200. About 90% of the values sit in that range.
• Days late, never negative. 591 negative delays became blanks.
• Credit inquiries: the most extreme 5% at each end trimmed.
• Interest rate: a 0 to 25 limit is written but commented out, so rates in the thousands stayed in. How that showed up later.

For age and card counts, out-of-range values became blanks rather than capped guesses, so the per-customer lookup could refill them from the same person’s other months.

The result

After the age rule, ages ran from 18 to 56, and the average fell from 110.9 to 34.4. Once rows that were still blank had been dropped and inquiries trimmed, 69,283 of 100,000 rows remained. Standard stayed at 53% of rows; Poor moved from 29% to 31%, and Good from 18% to 16%.

The rule that bit back

Rereading the notebook for this write-up, one limit turned out to have a side effect. The delayed-payments floor took the larger of zero and each value. In Python, max(0, blank) returns zero, so all 9,746 missing counts quietly became 0: “unknown” turned into “never late”. The column’s count of values jumped from 90,254 to 100,000 in one step. The notebook printed that jump, and nothing flagged it.

The fix is one line: leave blanks alone when flooring, and fill them on purpose later. The habit matters more than the line.

Rules I keep from this

1. Placeholders become blanks before anything else.
2. Every limit gets a sentence. If you can’t explain it, it isn’t a rule yet.
3. A blank beats a guess when the same customer can fill it later.
4. Count blanks before and after every rule. A sudden jump is a question, not a success.