Parsing unstructured text file with Python

Question

I have a text file, a few snippets of which look like this:

Page 1 of 515                   
Closing Report for Company Name LLC                 

222 N 9th Street, #100 & 200, Las Vegas, NV, 89101                  

File number:    Jackie Grant    Status: Fell Thru   Primary closing party:  Seller
Acceptance: 01/01/2001  Closing date:   11/11/2011  Property type:  Commercial Lease
MLS number: Sale price: $200,000    Commission: $1,500.00   
Notes:  08/15/2000 02:30PM by Roger Lodge This property is a Commercial Lease handled by etc..  

Seller: Company Name LLC                    
Company name:   Company Name LLC                
Address:    222 N 9th Street, #100 & 200, Las Vegas, NV, 89101              
Home:   Pager:              
Business:   Fax:                
Mobile: Email:              
Buyer: Tomlinson, Ladainian                 
Address:    222 N 9th Street, #100 & 200, Las Vegas, NV, 89101              
Home:   Pager:              
Business:   555-555-5555    Fax:            
Mobile: Email:              
Lessee Agent: Blank, Arthur                 
Company name:   Sprockets Inc.              
Address:    5001 Old Man Dr, North Las Vegas, NV, 89002             
Home:   (575) 222-3455  Pager:          
Business:   Fax:    999-9990            
Mobile: (702) 600-3492  Email:  [email protected]        
Leasing Agent: Van Uytnyck, Chameleon                   
Company name:   Company Name LLC                
Address:                    
Home:   Pager:              
Business:   Fax:    909-222-2223            
Mobile: 595-595-5959    Email:          

(should be 2 spaces here.. this is not in normal text file)


Printed on Friday, June 12, 2015                    
Account owner: Roger Goodell                    
Page 2 of 515                   
Report for Adrian (Allday) Peterson                     

242 N 9th Street, #100 & 200                    

File number:    Soap    Status: Closed/Paid Primary closing party:  Buyer
Acceptance: 01/10/2010  Closing date:   01/10/2010  Property type:  RRR
MLS number: Sale price: $299,000    Commission: 33.00%  

Seller: SOS, Bank                   
Address:    242 N 9th Street, #100 & 200                
Home:   Pager:              
Business:   Fax:                
Mobile: Email:              
Buyer: Sabel, Aaron                 
Address:                    
Home:   Pager:              
Business:   Fax:                
Mobile: Email:  [email protected]          
Escrow Co: Schneider, Patty                 
Company name:   National Football League                
Address:    242 N 9th Street, #100 & 200                
Home:   Pager:              
Business:   800-2009    Fax:    800-1100        
Mobile: Email:              
Buyers Agent: Munchak, Mike                 
Company name:   Commission Group                
Address:                    
Home:   Pager:              
Business:   Fax:                
Mobile: 483374-3892 Email:  [email protected]     
Listing Agent: Ricci, Christina                 
Company name:   Other Guys              
Address:                    
Home:   Pager:              
Business:   Fax:                
Mobile: 888-333-3333    Email:  [email protected]      

Here's my code:

import re

file = open('file-path.txt','r')

# if there are more than two consecutive blank lines, then we start a new Entry
entries = []
curr = []
prev_blank = False
for line in file:
    line = line.rstrip('\n').strip()
    if (line == ''):
        if prev_blank == True:
            # end of the entry, create append the entry
            if(len(curr) > 0):
                entries.append(curr)
                print curr
                curr = []
                prev_blank = False
        else:
            prev_blank = True
    else:
        prev_blank = False
        # we need to parse the line
        line_list = line.split()
        str = ''
        start = False
        for item in line_list:
            if re.match('[a-zA-Z\s]+:.*',item):
                if len(str) > 0:
                    curr.append(str)
                str = item
                start = True
            elif start == True:
                str = str + ' ' + item

Here is the output:

['number: Jackie Grant', 'Status: Fell Thru Primary closing', 'Acceptance: 01/01/2001 Closing', 'date: 11/11/2011 Property', 'number: Sale', 'price: $200,000', 'Home:', 'Business:', 'Mobile:', 'Home:', 'Business: 555-555-5555', 'Mobile:', 'Home: (575) 222-3455', 'Business:', 'Mobile: (702) 600-3492', 'Home:', 'Business:', 'Mobile: 595-595-5959']

My issues are as follows:

1. First, there should be 2 records as output, and I'm only outputting one.
2. In the top block of text, my script has trouble knowing where the previous value ends, and the new one starts: 'Status: Fell Thru' should be one value, 'Primary closing party:', 'Buyer Acceptance: 01/10/2010', 'Closing date: 01/10/2010', 'Property type: RRR', 'MLS number:', 'Sale price: $299,000', 'Commission: 33.00%' should be caught.
3. Once this is parsed correctly, I will need to parse again to separate keys from values (ie. 'Closing date':01/10/2010), ideally in a list of dicts.

I can't think of a better way other than using regex to pick out keys, and then grabbing the snippets of text that follow.

When complete, I'd like a csv w/a header row filled with keys, that I can import into pandas w/read_csv. I've spent quite a few hours on this one..

Answer 1

(This isn't a complete answer, but it's too long for a comment).

• Field names can have spaces (e.g. MLS number)
• Several fields can appear on each line (e.g. Home: Pager:)
• The Notes field has the time in it, with a : in it

These mean you can't take your approach to identifying the fieldnames by regex. It's impossible for it to know whether "MLS" is part of the previous data value or the subsequent fieldname.

Some of the Home: Pager: lines refer to the Seller, some to the Buyer or the Lessee Agent or the Leasing Agent. This means the naive line-by-line approach I take below doesn't work either.

This is the code I was working on, it runs against your test data but gives incorrect output due to the above. It's here for a reference of the approach I was taking:

replaces = [
    ('Closing Report for', 'Report_for:')
    ,('Report for', 'Report_for:')
    ,('File number', 'File_number')
    ,('Primary closing party', 'Primary_closing_party')
    ,('MLS number', 'MLS_number')
    ,('Sale Price', 'Sale_Price')
    ,('Account owner', 'Account_owner')
    # ...
    # etc.
]

def fix_linemash(data):
    # splits many fields on one line into several lines

    results = []
    mini_collection = []
    for token in data.split(' '):
        if ':' not in token:
            mini_collection.append(token)
        else:
            results.append(' '.join(mini_collection))
            mini_collection = [token]

    return [line for line in results if line]

def process_record(data):
    # takes a collection of lines
    # fixes them, and builds a record dict
    record = {}

    for old, new in replaces:
        data = data.replace(old, new)

    for line in fix_linemash(data):
        print line
        name, value = line.split(':', 1)
        record[name.strip()] = value.strip()

    return record


records = []
collection = []
blank_flag = False

for line in open('d:/lol.txt'):
    # Read through the file collecting lines and
    # looking for double blank lines
    # every pair of blank lines, process the stored ones and reset

    line = line.strip()
    if line.startswith('Page '): continue
    if line.startswith('Printed on '): continue

    if not line and blank_flag:      # record finished
        records.append( process_record(' '.join(collection)) )
        blank_flag = False
        collection = []

    elif not line:  # maybe end of record?
        blank_flag = True

    else:   # false alarm, record continues
        blank_flag = False
        collection.append(line)

for record in records:
    print record

I'm now thinking it would be a much better idea to do some pre-processing tidyup steps over the data:

1. Strip out "Page n of n" and "Printed on ..." lines, and similar
2. Identify all valid field names, then break up the combined lines, meaning every line has one field only, fields start at the start of a line.
3. Run through and just process the Seller/Buyer/Agents blocks, replacing fieldnames with an identifying prefix, e.g. Email: -> Seller Email:.

Then write a record parser, which should be easy - check for two blank lines, split the lines at the first colon, use the left bit as the field name and the right bit as the value. Store however you want (nb. that dictionary keys are unordered).

Answer 2

I suppose it is easier to start a new record by hitting the word "Page".

Just share a little bit of my own experience - it just too difficult to write a generalized parser.

The situation isn't that bad given the data here. Instead of using a simple list to store an entry, use an object. Add all other fields as attributes/values to the object.