4

I am trying to clean up a large .txt dataset in Excel. I think that a macro or another application may be able to clean up the data they way I want but I have limited programming skills. I need to create fields from different sections in the .txt file so I can then geocode the points. I am conducting a crime analysis for my local police department and up until now I have been using wildcards and the Find & Replace tool to do this, but I have nearly 2 years worth of police calls. Fields needed:"Address", "City", "State", "Type", "Location", "Date", "Day of Week", "Time Received", "Time Dispatched", "Time Arrived", "Time Cleared". I put up the data from January 2012 on Github in repo called SLO-Crime under my name, chadbunn. I will be using CartoDB to geocode the data and then QGIS to perform the analysis. I will probably make some maps in TileMill as well if I have enough time to.

https://github.com/chadbunn/SLO-Crime/tree/master

Text sample below showing a single incident (plain text file, each section composed of a dispatch header summary followed by type / address / officer and other information):

===============================================================================                             
120101001 01/01/12 Received:00:05 Dispatched:00:06 Arrived:00:12 Cleared:00:53                              
===============================================================================                             
Type: MENTAL SUBJ                                             Location:LZ1                              
As Observed:                                
      Mental Health                             
                                
Addr: 1257 RICH, San Luis Obispo, CA             Clearance Code:Report Filed in                             
                                
Responsible Officer: Hyman, J                               
Units: 4235  ,4241                              
 Des: incid#=120101001 AP/KAVANAGH,KOLLEEN 060455 166 PC clr:RTF oc:MENH call=1l
0

3 Answers 3

5

One of the great things about structured text like this (generally fixed width data) is that it is pretty easy to parse out using a programming language. I used almost the same approach that @congrene used, but I wrote it with Python, which is widely used in the GIS community.

You'll note that in many cases the City and State are not fully populated. Therefore I (and @congrene) hard-coded the City and State attributes. If they were fully populated, you could extract the city with record["city"] = parseaddress[1] and state with record["state"] = parseaddress[2].

For explanatory purposes of how the code works, I created a Python dictionary (record) and then added new key:value pairs to hold each individual attribute. I then added the whole "record" as a Python List item, which could be used to loop through all records when it came time to write to CSV. That made it easier to build out the CSV by just calling 1) the next record and 2) the correct key:value for the record when writing the output to the CSV file.

import os, datetime, csv

infile = r'd:\path_to_your_data\January_Data.txt'
outfile = r'd:\path_to_your_data\January_Data.csv'

#Create an list aggregator to hold all csv values
csv_data = []

with open(infile) as f:
    for line in f.readlines():  ##read through each line in the file

        #Look for keywords in a line and extract the data that follows it
        if "Received:" in line:
            record = {}  ##create a dictionary to hold incident-level data
            record["incident"] = line[0:9].strip()
            record["date"] = line[10:18].strip()
            record["dayofweek"] = datetime.datetime.strptime(record["date"],'%m/%d/%y').strftime('%A')
            record["received"] = line[28:33].strip()
            record["dispatched"] = line[45:50].strip()
            record["arrived"] = line[59:64].strip()
            record["cleared"] = line[73:80].strip()

        elif "Type:" in line:
            record["type"] = line[6:20].strip()
            record["location"] = line[71:80].strip()

        elif "Addr:" in line:
            parseaddress = line[6:49].strip().split(",")
            record["addr"] = parseaddress[0]
            record["city"] = "San Luis Obispo"
            record["state"] = "CA"
            record["clearcode"] = line[64:80].strip()

        elif "Responsible Officer:" in line:
            record["officer"] = line[21:80].strip()

        elif "Units:" in line:
            record["units"] = line[6:80].strip()

        elif "Des:" in line:
            ##This line appears to terminate an incident record
            csv_data.append(record)


if os.path.exists(outfile):  ##check to see if the output exists already
    os.remove(outfile)

#Write the data to a CSV file
with open (outfile, 'wb') as csvfile:
    csvwriter = csv.writer(csvfile, delimiter=',', quotechar='"')

    #create the header row
    csvwriter.writerow(["Incident","Address", "City", "State", "Type", "Location", "Date", "Day of Week", "Time Received", "Time Dispatched", "Time Arrived", "Time Cleared"])

    #Add individual records to the CSV
    for record in csv_data:
        csvwriter.writerow([ record["incident"], record["addr"], record["city"], record["state"], record["type"], record["location"], record["date"], record["dayofweek"], record["received"], record["dispatched"], record["arrived"], record["cleared"] ])

print "Data converted to :" + str(outfile)
0
2

I know you asked for a solution using excel, but here is a short ruby script to parse this data. To use, you'll have to install ruby (https://www.ruby-lang.org/en/downloads/). Once set up, put the data and the script in the same folder and run: ruby munge.rb

My result from running this on a portion of the sample data provided: https://gist.github.com/tomay/ac536aad29cc50c7e06b
(note: this example is geocoded using the excellent ruby geocoder gem, see code sample on same gist page)

I haven't tested this extensively, so use with care. Ask if you run into issues.

require 'date'
# open files
infile = File.new("January_Data.txt","r")
outfile = File.new("result.csv","w")

# write header
header = "Address,City,State,Type,Location,Date,Day of Week,Time Received,Time Dispatched,Time Arrived,Time Cleared\n"
outfile << header

# read file lines
result = {}
infile.each do |line|
  values = line.split(" ")
  next if (values.length < 2 || line.chomp == "As Observed:")
  begin
    if values[0] == "Addr:"
      result["address"] = line.split(",")[0].gsub("Addr: ","")
    elsif values[0] == "Type:"
      result["location"] = values.pop.split(":").last
      values.shift
      result["type"] = values.join(" ")
    elsif values[2].split(":")[0] == "Received"
      result["date"] = values[1]
      result["day"] = Date.strptime(result['date'], '%m/%d/%y').strftime('%A')
      result["time_received"] = "#{values[2].split(":")[1]}:#{values[2].split(":")[2]}"
      result["time_dispatched"] = "#{values[3].split(":")[1]}:#{values[3].split(":")[2]}"
      result["time_arrived"] = "#{values[4].split(":")[1]}:#{values[4].split(":")[2]}"
      result["time_cleared"] = "#{values[5].split(":")[1]}:#{values[5].split(":")[2]}"
    elsif values[0] == "Des:"
      # write result
      outfile.write "#{result['address']},San Luis Obispo,CA,#{result['type']},#{result['location']},#{result['date']},#{result['day']},#{result["time_received"]},#{result["time_dispatched"]},#{result["time_arrived"]},#{result["time_cleared"]}\n"
      result = {}
    end
  rescue
    puts "Line was: #{line}"
  end
end

# close files
infile.close
outfile.close
0
2

Given the fields that are of interest to you are in 3 different typical lines, and given your lack of programming skills, I might suggest you make a first pass at removing useless lines.

You may be able to do that with a better text editor. As an example, the following command in Vim or GVim will delete all lines not containing either of these 3 conditions:

  • 3 or more digits at beginning of line (record numbers) (regex pattern: ^\d\d\d\+)
  • Lines beginning with Type (regex pattern: ^Type)
  • Lines beginning with Addr (regex pattern: ^Addr)

The magic command is :v/^\d\d\d\+\|^Type\|^Addr/d

will then transform your file in this form:

More notes on the above command at the end of the post

..
120131082 01/31/12 Received:20:48 Dispatched:20:51 etc. 
Type: COLL NON                    Location:LZ2                          
Addr: 297 MADONNA; PANERA BREAD, San Luis Obisp  Clearance Code:Report Filed in
...

From there you'll have to find a way to join the 3 recurring lines into a single one. This could be done very mechanically, search the VBA Excel threads for hints/solutions this one here about reshaping is directly applicable!. At least from there, you'll be closer to something you can parse within Excel.


Info complement on Vim command:

:v/ some_pattern /d

  1. That is a "reVerse grep then Delete" command. As such, it will delete all lines not matching the pattern between slashes.

  2. The patterns are in regular expression form regex which BTW are supported in most programming languages and good text editors. In Vim's case the "pipe" character meaning "or" needs to be escaped with a backslash: \|.

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.