Live data · public source

Cleaning a 6,000-row business list

A lead list arrives with eight different phone formats, duplicate companies under separate licence numbers, ZIP codes missing a leading zero, and dates set to the year 1900. This is what came back, and what I refused to guess at.

Result

6,000
rows received: 5,792
delivered clean: 208
flagged for review: 0.23s
processing time

SourceNYC Legally Operating BusinessesPublic dataset. Anyone can download the same rows and check this output.

Before and after

Seven real rows, one defect each

Every row below is in the source file. Switch the view to see which cells changed, and which were emptied on purpose.

Defect	Business name	Phone	Address	ZIP	Licensed since
Phone stored as raw digits	JAMES ROBINSON, INC.	2127526166	480 PARK AVENUE	10022	2003-10-31T00:00:00.000
Phone with an extension	200 EAST PARKING CORP.	2127367171X1014	1081 3 AVENUE	10065	2004-09-27T00:00:00.000
ZIP+4 run together	ARS NATIONAL SERVICES, INC.	7607352700	7960 BAYMEADOWS WAY	322567518	2013-11-15T00:00:00.000
ZIP lost its leading zero	SITEL ARM CORP	3033040912	WORLDWDE CORPORATE CENTER	1552	2023-06-29T00:00:00.000
Trailing and doubled spaces	MCMILLEN, INC.	2127535600	155 EAST 56 STREET	10022	2003-07-28T00:00:00.000
Placeholder date posing as data	CHURCH OF SAINT MARY	2126743266	440 GRAND STREET	10002	1900-12-31T00:00:00.000
Phone too short to be real	TRIDEV CORPORATION	3050	3504 BELL BLVD	11361	2001-01-03T00:00:00.000

Every row above is real, taken from the public dataset below. Switch to “as delivered” to see which cells changed and why.

Duplicates

Two licences, one business

Matching on licence number finds nothing — every licence number in the file is unique. Matching on business name alone over-merges genuine separate branches. The key that works is name plus street address, which found 153 groups covering 158 redundant rows.

One group, as an example

Kept

168 Fruit & Vegetable Corp.

168 7th Ave · 10011 · licence 1148577-DCA

More complete record

Removed

168 Fruit & Vegetable Corp.

168 7th Ave · 10011 · licence 1148555-DCA

Listed in the flagged file, not deleted silently

Rule ledger

Every change, counted

This table is written by the script itself, not by me. It ships with the delivery so the client can see the scale of each change before opening the file.

Rule	Rows	What it means
names_recased	5,814	Rebuilt from all caps. Inc., LLC, initials, ordinals and Mc- names are handled as exceptions rather than title-cased into nonsense.
phones_reformatted	3,576	Brought to one format from the eight present in the file, including one number carrying an extension.
phones_missing	2,192	Genuinely absent in the source. Left empty on purpose — a missing value is not an error, and nothing was invented to fill it.
phones_invalid	13	Too few or too many digits to be a real number. Cleared and flagged rather than padded into looking valid.
duplicates_removed	158	The same business at the same address under 153 separate licence numbers. The most complete record was kept; the rest are listed in the flagged file.
whitespace_trimmed	285	Trailing spaces and doubled internal spaces. Invisible on screen, and the reason two identical rows fail to match.
zips_plus4_split	27	Nine-digit ZIPs run together, split into ZIP and +4.
zips_leading_zero_restored	2	Four-digit ZIP codes. A spreadsheet upstream read 01552 as the number 1552 and dropped the zero.
dates_placeholder	38	Dates of 1900-12-31 and 2100-11-27 — placeholders somebody typed to get past a required field. Cleared and flagged.
states_missing	3	No state recorded. Flagged; one of them turns out to be an address in the Philippines.

Judgement

What I did not do

Guess a missing phone number

2,192 rows have no phone in the source. They are delivered empty. Looking one up on the web is a different job with a different price, and doing it uninvited would hide how incomplete the source really is.

Repair an impossible value

13 phone numbers have the wrong digit count. Padding a four-digit number into a ten-digit one produces a plausible number that dials a stranger.

Delete the duplicates quietly

All 158 removed rows are in the flagged file with the licence number of the record that replaced them, so the decision can be reversed without re-running anything.

Deliverables

The files a client receives

The script is part of the delivery, not a secret. It uses the Python standard library only, so it runs on your machine with nothing to install — which matters when the next export lands next month and you would rather not pay for the same job twice.

Have a file like this?

Send a sample and what you need it to look like. You will get a quote and a turnaround, not a discovery call.

angga.dimassaputra@gmail.com WhatsApp