Filling a customer’s gaps from their own other months

For a credit-score project during my NeoSOFT internship, the dataset looked like 100,000 people. It was 12,500 customers, each recorded once a month from January to August. That one fact decided how every gap should be filled.

The problem: gaps in disguise

The blanks didn’t all look like blanks:

• Occupation was _______ in 7,062 rows.
• Credit mix was _ in 20,195.
• Annual income had stray characters, like a trailing underscore, so 6,980 values couldn’t be read as numbers.
• Monthly investment was missing or __10000__ in 8,784.

The usual moves are to drop incomplete rows or fill each blank with the column’s median. Dropping throws away a customer’s good months along with the bad one. A median is worse: it hands a customer someone else’s income. Meanwhile the answer was usually a row away, in the same customer’s other seven months.

First try: copy the previous month forward

My first notebook grouped rows by customer and used a forward fill, which copies the last known value into the next blank. It helped, but it only looks backwards. When the blank fell in a customer’s first month, there was nothing earlier to copy, so 930 incomes and 1,231 investment amounts stayed empty.

The fix: one lookup per customer

The final notebook builds a lookup first: for each customer, a value seen in any of their eight months. Then every blank is filled from that customer’s own entry, whichever month it came from.

for each column:
    known = {customer: a value seen in any of their months}
    for each blank row:
        fill it with known[customer], if there is one

I wrote that rule as a plain-English prompt for an AI assistant. The prompt is still in the notebook next to the loop it produced, and the loop was reused for nine columns. Median and mean fills are in there too, written for seven columns and commented out. That’s the decision, recorded in code.

The result

• Occupation: 7,062 → 0 blanks.
• Credit mix: 20,195 → 0.
• Annual income: 6,980 → 0.
• Monthly investment: 8,784 → 0.

Age took one more step. After ruling out impossible ages (anything outside 18 to 80) there were 13,068 blanks, and the lookup brought that down to 5,512. Those rows belonged to 689 customers who were under 18 in every month, below the minimum age for a credit card. There was nothing of their own to borrow, so they were dropped rather than given a guess.

What I’d tighten

Copied code was the weak spot. Going back through the notebook with Claude Code for this write-up, I found two copied lines still pointing at the age column. One filled missing salaries with ages. The other overwrote the loan count with ages, and because loan counts were then limited to 0 to 50 and refilled per customer, the 857 customers who were over 50 in every month had no value left and were dropped. The model was trained and tested without them.

The fix is cheap: one function that takes the column name, so there’s no copy to get wrong, and a count of rows after every step, so 6,856 rows can’t vanish quietly.

Rules I keep from this

1. Find the grain first. Ask what one row is before cleaning anything. Here it was a customer-month, not a person.
2. Borrow from the same entity, not the crowd. A person’s own history beats a population average.
3. If there’s nothing to borrow, drop it. Don’t invent a value.
4. Write each cleaning step once, as a function, and reuse the function rather than the code.