If you've ever sat in on a session about data cleaning at one of the annual IRE and NICAR computer-assisted reporting conferences, you've heard one question that comes up again and again: "What about names?"
Many of us have gazed longingly at a huge table containing jumbled names, wondering what sort of havoc we could wreak if we could just break them up into more manageable fields.
A $50 program called ParseRat can do that by flashing through thousands of names in a few seconds to produce a nice, clean list parsed however you like.
While I've primarily used ParseRat (www.guysoftware.com/ParseRat.htm) to carve names, the program is capable of doing much more. The compact program file takes only a few minutes to download but can handle very large files in a variety of formats, from the antiquated EBCDIC data exported from mainframes to data-heavy Web pages using modern XML tagging.
ParseRat steps
For example, you can easily grab large tables from Web pages and, with the help of scripts included with the program, modify them for importing into a spreadsheet or database manager. Or you can use the program to parse data from a simple .dbf or complicated COBOL file. In fact, you can grab text in just about any format: delimited, fixed-width, "tagged" with HTML, XML or other coding, among others.
There are routines for converting unusual date and time formats. The program can even convert from one measurement system to another, such as metric to U.S. standard, and allows you to write new conversion rules for measurements not included.
ParseRat will even take a stab at determining gender from names, and will generate Soundex codes useful for searching for names phonetically. But it's obvious from the start that ParseRat was created with mailing lists in mind and there are a bunch of built-in options to parse names, addresses and phone numbers.
The program's major drawback: The interface is somewhat complicated, with tons of options available through small buttons in an often-jumbled package. The help files have a homemade feel that can be confusing at times. But after spending an hour or so with the documentation, I was able to navigate around the program with relative ease.
First, preparation
You should also be ready to do some prep work on your data before running it through ParseRat. I use a powerful but inexpensive text editor, UltraEdit, available for $35 at www.ultraedit.com There are a number of text editors out there and having one is a must, because they can crank through very large text files that would choke a conventional desktop word processor such as Microsoft Word or the trusty Windows Notepad.
Before using ParseRat, make sure all header information and extraneous material has been stripped from the top of the text because the program will use the first line of data to make judgment calls about parsing the entire file. The cleaner the file, the better the output, so take some time to ensure everything's as uniform and as clean as possible. I recommend isolating only those fields on which you want to work to keep things simple and speed up the process.
Here's a walk-through on parsing names, which I do regularly.
First, use your text editor to choose the most complicated name you can find and move it to the very top of the list. By first showing ParseRat a name with the most elements - "Mr. James E. Wilkerson III," for example - you prime the program to look for all of those elements in each record. And, because you only get to see the first record when making parsing decisions, it's easier to do with a name that contains all of the possible elements.
Sometimes it's easier to make up that first name than dig through your data to find the perfect model. If you make up a name just remember to delete it later.
Software interface
After that, fire up ParseRat and choose Open\Input file and browse for the text file. The next screen will ask you to confirm the type of data - delimited, fixed width, etc.
The interface gets pretty clunky here, but it's easy enough to navigate once you get the basics down. We will focus on the Input Type, Name and Output tabs.
After confirming the text type, click on the Name tab. This is where you specify the field (or fields) containing the name you want to clean, and tell ParseRat what you want the output to look like. To the left of the tabs, you'll see an input selection to the left and an output indicator to the right. (You should have at least one field visible in the input section - if not, go back to the Input Type and try another type until you do.)
Near the top of the dialog box, click on the first box available in the source fields input. Then, click on the appropriate source field to the left. The name of that field will appear in the source fields box, and the first name in your text will appear in the elements boxes underneath.
ParseRat takes a stab at determining the order in which the elements of the name appear, and ranks them from one to five using the radio buttons to the left of the sample name, starting with "Title" and ending with the full name. You can change that order by clicking on the buttons. You can change the output order by using the similar feature to the right of the sample name.
After setting up the input and output order on the name tab, you need to specify the fields to output. Click on the Output tab to get a lot of options. The most important is the parser-generated fields, which show you the output that ParseRat will generate. Scroll down and take a look at the results to make sure they match your expectations.
To the right of parser-generated fields is a small button labeled "Add Field." Clicking on that button will add a blank output field to the right of the dialog box. Count the number of fields you want to output and create that many blank fields with that button.
Choices, choices
Click on the first output field at the far right and choose the parser-generated fields name that you want to place there by clicking on it. Click on the next output field name and do the same, repeat until you have all of the output fields you need in the order you want.
If you want to keep the original record, place it in one of the output fields by clicking on it at the far right of the dialog box.
Make sure you have selected the appropriate output format - usually use comma-delimited text - and click the output button in the top tool bar. Then sit back and wait while ParseRat generates your cleaned names.
It should be obvious when you start poking around on it that the program is capable of much more than I touched on here. Take some time and read the documentation, and you'll soon be flying through parsing jobs you might have thought impossible.