Good tools to parse repetitive unstructured data

Question

I'm looking to parse a large number of lines of repetitive but unstructured data. This is a task that happens at least once every project, in my experience, so I'm looking for a tool to transform fairly standard text into structured data. Right now I just use a combination of regex find and replace and one-off python scripts.

Here's a clean example:

Distance: 25.903 miles*
Morgan Road Middle School Extension
    HEPHZIBAH GA, 30815
    Telephone: 706.504.4071
    A unit of: Boys & Girls Clubs of Augusta
        http://www.bgcaugusta.org

And here's a slightly messier example:

Maria Teresa’s Babies Early Enrichment Center/Daycare 
825 23rd Street South 
Arlington, VA 22202 
703-979-BABY (2229) 
22. 
Maria Teresa Desaba, Owner/Director; Tony Saba, Org. Director. 
Website: www.mariateresasbabies.com 
Serving children 6 wks to 5yrs full-time. 


National Science Foundation Child  Development Center  
23. 
4201 Wilson Blvd., Suite 180  22203 
703-292-4794 
Website:  www.brighthorizons.com 112 children, ages 6 wks - 5 yrs.   
7:00 a.m. – 6:00 p.m. Summer Camp for children 5 - 9 years.

These are only examples. The issue of parsing unstructured data that is nonetheless repetitive is something I come across fairly often, especially when receiving text or word documents in response to FOIA requests. Mostly I'm wondering if someone has written a tool or library that's good at converting these documents into structured data, or if I should be thinking about how to write something myself.

There has been a discussion if this question is off-topic. To be on the safe side, you might want to extend your question and briefly explain how it is relevant for the Open Data community. — Patrick Hoefler, May 12 '13 at 17:36
Edited to make the problem a bit clearer. I don't feel like this is a StackOverflow question because I'm not asking about how to code a solution - I've got a lot of those. I'm more wondering if anyone has any tools or libraries they know of that can generalize this problem. Obviously, I didn't ask that question very well. — RCA, May 12 '13 at 17:47
Technically they have a structure, just one that's not well defined. — Brad Gilbert, Aug 18 '13 at 18:20
Documents where the "structure" is provided by human editing conventions rather than an actual template are the worst, because they look regular, but are not -- which you don't discover until you start trying to process them. — Tom Morris, May 06 '15 at 22:27
What became of Monarch? Might there be a similar open source app? — Alan Hitchner, Jul 27 '17 at 21:25
I agree that this is not an answer. Please add more details, so other users may evaluate and upvote it. — Tasos, Jul 31 '17 at 21:10

score 23 · Accepted Answer · answered May 13 '13 at 08:26

23

OpenRefine can be used to parse semi-structured data into a table like structure, where it can be operated on in a manner similar to a spread sheet and exported.

The site features a tutorial on converting a list on wikipedia into a table which may be a good starting point.

The operations involved can also be exported incase you need to perform the same cleanup operation again.

answered May 13 '13 at 08:26

Daniel Knell

606
4
5

For irregular or semi-structured documents like this, I highly recommend leaning heavily on OpenRefine's facets to make sure that you're operating on a subset of rows that match your assumed preconditions and on the transformation preview (as well as operation undo) to work in small incremental steps that you can verify before proceeding to the next stage of processing. – Tom Morris May 06 '15 at 22:29

score 15 · Answer 2 · answered May 09 '13 at 01:18

Python is the tool I would use.

import csv
from itertools import izip_longest

def grouper(iterable, n, fillvalue=None):
    '''Collect data into fixed-length chunks or blocks
      grouper('ABCDEFG', 3, 'x') --> ABC DEF Gxx'''
    args = [iter(iterable)] * n
    return izip_longest(fillvalue=fillvalue, *args)

with open("raw_data.txt", 'r') as f:
    csv_file = open("out.csv", 'w')
    writer = csv.writer(csv_file)

    for record in grouper(f, 4):
        record = [item.strip() for item in record]
        city = record[-1:][0].split(',')[0]
        state = record[-1:][0].split(' ')[1]
        zip_code = record[-1:][0].split(' ')[2]

        sanitized_record = record[:-1] + [city, state, zip_code]

        writer.writerow(sanitized_record)

    csv_file.close()

the advantage this offers you over grep is that if later you decide to put the data in a database or perform some complex calculations on the data that will be a lot easier with python than grep.

Do you know of any libraries for this kind of work? – RCA May 09 '13 at 02:06 — RCA, May 09 '13 at 02:06
@rcackerman see Daniel Knell's answer. – John May 13 '13 at 08:29 — John, May 13 '13 at 08:29

score 4 · Answer 3 · answered Aug 18 '13 at 20:38

The problem with the second example you posted, is there is almost no structure to it. It doesn't even have a consistent ordering of rows for each record.

I think this is where Perl shines, so I went ahead and wrote up a prototype. ( took me about an hour )

I don't think there exists a tool that would be any simpler, that would get as decent a result. ( There might be a tool that would be easier to learn, but it still won't be any simpler to setup )

All it does is parse the input file, and give a computer readable output.

You would still have to do some hand editing of either the input or the output, but it does a fairly decent job on your sample.

The code was designed to not lose a single piece of information, so it would still need some processing in order to load it into a database.

It just throws whatever it can't decipher into comment.
If you knew a line should be tagged as hours (for example) you could just add "hours: " to the beginning of that line.

#! perl
use strict;
use warnings;

use 5.14.0;

# there was some wide chars in the input
use open ':encoding(utf8)';
use open (':std', ':encoding(utf8)');

$/ = "\n\n"; # input record separator

my @data;

RECORD: while( my $record = <> ){
  # split on newlines, ignore empty ones
  my @lines = grep { length } split /\r?\n/, $record;

  my %record = (
    name => shift @lines, # first line is the name
  );

  LINE: for( @lines ){ # for local $_ ( @lines ){...}
    s/^\s|\s+$//g; # remove leading and trailing spaces

    if( /^(\d+)\.$/a ){ # "22." "23." etc
      $record{number} = ''.$1;

    }elsif( /^\d{3}.\d{3}.[^\s]{4}/a ){ # loosely looks like a phone number
      push @{$record{phone}}, $_; # may be more than one phone number so put it in an array

    }elsif( /^(\w+):\s+(.*)/ ){
      push @{$record{lc $1}}, ''.$2; # handles website: ...

    }elsif( /\d+ .*? \b(?:st(?:reet)?|blvd|boul[ae]vard|ave(?:nue)?)\b/axi ){ # looks like a street address
      push @{$record{address}}, $_;

    }elsif( /,\s+\w\w\s+\d{5}/a ){ # looks like "city, state, zipcode"
      push @{$record{address}}, $_;

    }else{
      push @{ $record{comment} }, $_; # unknown line
    }
  }

  push @data, \%record;
}

use JSON;
# UTF8 is handled by "use open ..." above
# so we turn it off here
my $encode = JSON->new->utf8(0)->pretty;
print $encode->encode(\@data);

print "\n";

[
   {
      "website" : [
         "www.mariateresasbabies.com"
      ],
      "comment" : [
         "Maria Teresa Desaba, Owner/Director; Tony Saba, Org. Director.",
         "Serving children 6 wks to 5yrs full-time."
      ],
      "number" : "22",
      "name" : "Maria Teresa’s Babies Early Enrichment Center/Daycare ",
      "address" : [
         "825 23rd Street South",
         "Arlington, VA 22202"
      ],
      "phone" : [
         "703-979-BABY (2229)"
      ]
   },
   {
      "website" : [
         "www.brighthorizons.com 112 children, ages 6 wks - 5 yrs."
      ],
      "comment" : [
         "7:00 a.m. – 6:00 p.m. Summer Camp for children 5 - 9 years."
      ],
      "number" : "23",
      "name" : "National Science Foundation Child  Development Center  ",
      "address" : [
         "4201 Wilson Blvd., Suite 180  22203"
      ],
      "phone" : [
         "703-292-4794"
      ]
   }
]

score 3 · Answer 4 · edited May 23 '17 at 12:37

3

pyparsing is really a powerful tool. As the name implies, it's a module for Python. It's like zooming out one step from regexp. There are many examples at http://pyparsing.wikispaces.com/Examples

Here's another example which also makes use of regexp, in case you also want to use that for more exact pattern matching.

edited May 23 '17 at 12:37

Community

1

answered Dec 04 '13 at 18:52

Daniel F

131
3

score 3 · Answer 5 · answered May 13 '13 at 12:39

I've always done work like this in Perl -- my basic methodology goes something like this. (note, this is for dealing with multi-GB files ... it can be simplified if you can load the whole thing in memory)

I've denoted helper routines with &, although you probably don't want to use that old perl-4 calling style as it'll force it to ignore function prototyping.

my $record = '';
while ( my $line = <> ) {
  if ( &test_for_record_separator ) {
     &parse_record( $record );
     $record = '';
     next;
  }
  $record .= $line;
}
&parse_record( $record );

sub parse_record { 
  my $record = shift;
  my %record = ();
  # exact logic depends on how variable the record structure is.  To handle labeled lines:
  foreach my $line ( split /\n/, $record ) {
    if ( $line =~ m/Some Label:\s*(.*)$/  ) { $record{'Label'}  = $1; next } 
    if ( $line =~ m/Other Label:\s*(.*)$/ ) { $record{'Label2'} = $1; next }
    ...
    &flag_unknown_lines( $line );
  }
  # if the record structure is fixed:
  @record{ qw( company, street_num, street, city, state, zip, phone, index, description )} = (
    $record =~ m/(.+)\n(\d+)\s+(.+)\n(.+),\s+(\w[\w\s]+\w)\s+([-\d]+)\n(\d+)[.]\n([\s\S]+)/
  );
  # or some other more complicated process

  &throw_parse_error unless &validate(%record);
  &write_record_to_database_or_whatever(%record);
}

Obviously, knowledge of the input will allow you to select the best parsing routine -- if you're only going to have state abbreviations, you won't need (\w[\w\s]+\w) (which will match New York or West Virginia cleanly). Bits of this (like looking for 'Key: Value' stuff can be easily abstracted to handle whatever input, provided your writing routine can handle it.)

score 3 · Answer 6 · answered Apr 01 '16 at 22:29

3

Take a look at UTAH: https://github.com/sonalake/utah-parser

It's a good tool for handling files like this.

answered Apr 01 '16 at 22:29

Daniel Bray

31
1

score 2 · Answer 7 · answered May 09 '13 at 03:07

It entirely depends on how unstructured your data is. You'll be making a little "mini-domain language" to put the data into structured form.

The more unstructured it is, the more you going to have to get in the minds of everyone who made the records. Shoot for the most common and put the last 20% in the "complete-by-hand" file.

Python, Sed/awk, Perl are tools that are quite commonly used. I use Python myself and it's quite good.

score 2 · Answer 8 · answered Apr 04 '14 at 19:37

The Los Angeles Times did some very interesting work using natural language processing (NLP) to analyze their archival recipes and convert them into structured data.

An interview with the team ran on OpenNews Source, and Anthony Pesce wrote more (including some code samples) on the LAT Data Desk blog.

score 1 · Answer 9 · answered May 09 '13 at 09:22

1

Python and some XSLT wold do the trick

answered May 09 '13 at 09:22

Antonio Cunha Santos

139
1
3

score 1 · Answer 10 · answered May 09 '13 at 01:32

1

It does seem like a scripting language is the proper approach. I'd read line by line. Starting a new record at the top of the file or after an empty line. Use some pattern recognition and you'll be parsing away fairly quickly.

answered May 09 '13 at 01:32

David Medinets

291
2
4

1

Which language would you recommend? Do you know of any libraries to help with the pattern recognition? – Patrick Hoefler May 09 '13 at 01:55
The number of built-in commands in Ruby that deal with strings may still be larger than any other language (I haven't checked recently). But you ought to be able to do pretty similar work in Ruby, Perl or Python. – Roger_S May 18 '13 at 22:05

score 0 · Answer 11 · edited Sep 17 '13 at 10:44

I think the best thing would be to use IntelliGet for this. Its a smart tool and has got nice support. Its available at http://www.mountonetech.com/intelliget.asp for download and it has got plenty of examples, one of which is school-report, which I think is pretty close to what is being asked for here. You can define start and end of sections and sections within sections and how you want to pivot that information as fields of a line, etc.

Good tools to parse repetitive unstructured data

11 Answers11

Linked