One of the best examples of how regular expressions can be used to clean up data came when The Inquirer obtained a 700,000-record database of every reported crime in Philadelphia since the early 1990s. Unfortunately, police had been sloppy in entering addresses in a standard format. Here's just a few of the ways that a crime at 400 N. Broad St could have been recorded:
BROAD 400 N N BROAD 400 400 N BROAD BROAD 400N N400 BROAD
After looking at the data, we found that about 90 percent of the addresses fell into the first category - "BROAD 400 N." However, with a data set of 700,000 records, that still left about 70,000 records with bad addresses. After a little more playing with the data, we found about a dozen different styles for addresses.
The data gave the story real backbone, but the actual writing was filled with anecdotes and discussion of the issues.
To standardize the addresses, I wrote a Perl program that went through the database record-by-record-as part of a SQL Server DTS script - and used regular expressions to pick out the various parts of the street address.
For each record, the program checked to see if the address field matched this pattern: a word, a space, a number, another space, and a direction. The syntax Perl uses to express this is:
(\w+) (\d+) ([NSEW])
How this worked with an address of BROAD 400 N:
The \w indicates an alphanumeric character. The + says look for one or more of them. So \w+ means look for one or more alphanumeric characters in a row. So BROAD meets this condition. It's five alphanumeric characters in a row.
The \d+ tells Perl to look for one or more digits in a row; 400 meets this condition.
The brackets tell Perl to look for a single character. The letters in the brackets are the acceptable values. In this case, a street direction of N, S, E or W.
But where Perl outshines most other search-and-replace utilities is in taking pieces of the search term and rearranging them into the replacement. By placing parentheses around groups in the search term, the script remembered what it found. The value matched by the first set of parentheses was saved in the variable $1. The value matched by the second set was saved in the variable $2, and so on. So to turn BROAD 400 N into 400 N BROAD, the script looked for (\w+) (\d+) ([NSEW]) and replaced the address with $2 $3 $1.
Since the block number needed to be first, then the street direction, and then the street name, the program took the block numbers from the second set of parentheses - the (\d+), stored in $2 - and put it back as the first item in the replacement.
The street direction - stored in $3 - was the next item, and the street name - the first item searched for - became the last item in the replacement.
What if a particular record didn't match the pattern (\w+) (\d+) ([NSEW])? The program moved on to the next possible pattern - out of the dozen or so we were trying - and checked to see if it matched. If it did, it took the pieces, rearranged them in the proper order, and moved to the next record.
If it didn't, it tried yet another pattern. And if it exhausted all possible patterns, it flagged that record as an error and moved on to the next record.
Matthew Ericson can be reached by e-mail at mericson@phillynews.com