gridpin

What changed between two address files

Each month a new export of the same customers or service points arrives. The question is not which lines differ as text, but which ones now point somewhere else.

Pro Clean and compare Command line Utilities Public sector

What you have

Two exports with a common key column; in April one customer moved and one is new:

march.csvfile
id,address
1,"233 S Wacker Dr, Chicago, IL 60606"
2,"1600 Pennsylvania Ave NW, Washington, DC 20500"
3,"350 Fifth Avenue, New York, NY 10118"
april.csvfile
id,address
1,"233 S Wacker Dr, Chicago, IL 60606"
2,"1600 Pennsylvania Ave NW, Washington, DC 20500"
3,"600 N Michigan Ave, Chicago, IL 60611"
4,"1 Marina Park Dr, Boston, MA 02210"

Steps

1. Geocode both files:

terminalCLI
$ gridpin batch usa.bin march.csv march_g.csv
terminalCLI
$ gridpin batch usa.bin april.csv april_g.csv

2. Compare the answers row by row, matched by id; the summary is printed:

terminalCLI
$ gridpin-pro compare march_g.csv april_g.csv --key id -o changes.csv
{
  "answer_to_empty": 0,
  "changed": 1,
  "empty_to_answer": 0,
  "fields_only": 0,
  "match_level_changes": {},
  "moved": 1,
  "only_in_a": 0,
  "only_in_b": 1,
  "rows": 3,
  "unchanged": 2
}

3. Read the rows that changed:

terminalCLI
$ cat changes.csv

What you get

keychangedistance_ma_streetb_street
3moved1144830.95 AveNorth MICHIGAN Avenue

Five of sixteen columns. fields names the answer fields that differ; a_… and b_… also carry the point, the number, the precision and the match level before and after. A row only in the new file is counted as only_in_b.

Why it works

Rows are paired by the key and their answers compared, not their text. A point that moved farther than --threshold-m (150 m by default) is a move; any other difference in the answer is a field change; an answer that appeared or vanished is counted apart. So a reformatted line that still lands on the same house is not reported.

Limits

  • It compares answers, not the reasons: a changed line and a changed country file look the same.
  • Without --key the two files must have the same rows in the same order.
  • What changed in the country file itself between two releases is a different tool: sheet-diff.

All its keys: command reference. The function: compare. Part of gridpin-pro, which comes with a Starter or Pro subscription: Free, Starter and Pro.

More