Find duplicate addresses in a customer list
One customer typed the street in full, another abbreviated it, a third got the ZIP code wrong. To a spreadsheet they are three addresses; to the post they are one house.
Pro Clean and compare Command line Retail, e-commerce Logistics, field work
What you have
A CSV with an address column (named address, query or q; any other name with --column), and the country file. Eight rows, three houses:
id,name,address 1,Ana Ruiz,"233 S Wacker Dr, Chicago, IL 60606" 2,Ben Cole,"233 South Wacker Drive, Chicago IL" 3,Cara Diaz,"1600 Pennsylvania Ave NW, Washington, DC 20500" 4,Dan Ford,"350 Fifth Avenue, New York, NY 10118" 5,Eve Gray,"1600 Pennsylvania Avenue Northwest, Washington DC" 6,Finn Hale,"350 5th Avenue, New York, NY 10118" 7,Gil Ives,"233 S. Wacker Drive, Chicago, Illinois 60606" 8,Hana Jun,"233 S Wacker Dr, Chicago, IL 60607"
Steps
1. Group the rows that name one house:
$ gridpin-pro dedupe usa.bin customers.csv -o groups.csv 8 rows, 3 groups, 5 rows merged into another, 0 rows without an answer
2. Read the groups. Every input column stays; the new ones say which group a row joined and why:
$ cat groups.csv
What you get
id | address | gp_group | gp_canonical |
|---|---|---|---|
| 1 | 233 S Wacker Dr, Chicago, IL 60606 | 1 | 1 |
| 2 | 233 South Wacker Drive, Chicago IL | 1 | 1 |
| 3 | 1600 Pennsylvania Ave NW, Washington, DC 20500 | 2 | 3 |
| 4 | 350 Fifth Avenue, New York, NY 10118 | 3 | 4 |
| 5 | 1600 Pennsylvania Avenue Northwest, Washington DC | 2 | 3 |
| 6 | 350 5th Avenue, New York, NY 10118 | 3 | 4 |
| 7 | 233 S. Wacker Drive, Chicago, Illinois 60606 | 1 | 1 |
| 8 | 233 S Wacker Dr, Chicago, IL 60607 | 1 | 1 |
Four of thirteen columns. gp_canonical is the first row of the group: keep it and merge the others into it. gp_group_size counts the rows, gp_reason says why a row joined, and gp_street, gp_housenumber, gp_lat, gp_lon are the house they share.
Why it works
Rows are not compared as text. Each row is geocoded first, and two rows are one house when their answers have the same street, number and town and neither answer is marked weak. That is why 233 S. Wacker Drive, Chicago, Illinois and 233 S Wacker Dr with the wrong ZIP code 60607 land in one group: the file knows the house and puts both on it.
Limits
- A row the file cannot place, or places only on the street, stays in a group of its own: it is not merged on a guess.
--level streetgroups by street instead of house. - Flats and suites are not told apart: two flats in one building are one house here.
- One country file per run: a list with customers in two countries is split first.
All its keys: command reference. The function: dedupe. Part of gridpin-pro, which comes with a Starter or Pro subscription: Free, Starter and Pro.