12463_en-US

liliconcom

Help with Regex parsing of PDF turned text file<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

So, I am working on an awesome data set I found:

https://data.montgomerycountymd.gov/Public-Safety/Traffic-Violations/4mse-ku6q?

~ 600k traffic violation over a 3 year period. Lots of things that need re-coding, lots of text data and some interesting geospatial things.

One of the columns is "Charge" which is the actually charge that the driver was given. I found a PDF file of all the IDs, what they are and how much the ticket is for, and how many points the charge is worth. I could only find a PDF, which was converted to a text file (attached).

If anyone has a good idea on how to get this into a JMP table so I can join it against the violations table, that would be really helpful. I think regex parsing is what I will need to make this happen, and then conforming the X XYZ (X) structure of the .txt table into the X.XYZ(X) structure that the charge is.

I put the raw data here, too if anyone is interested: smb://JMPSMD/Users2/Valente-D/TrafficData

Thanks!

Re: Help with Regex parsing of PDF turned text file

Dan,

I might have some time to work on it at the end of the week. It would be helpful to get a slightly clearer version of the TXT file. In particular, some lines (like 266) were treated as if the columns were stacked in groups of two or three. Most of the entries with a length of greater than about 300 were treated this way (approx. 200/1376). The values in the column Text were created by parsing the original file at carriage returns and new lines (\!r, \!n) only.

Don

Re: Help with Regex parsing of PDF turned text file

Thanks, Don. I figured it was going to be a challenge. This is a good start. Let me join it against the master table and see what I get. It doesn't need to be perfect. I have 650k records, so even getting 20-30% of those would make for a reasonable demo data set. I appreciate the help! I'll let you know.

-Dan

Re: Help with Regex parsing of PDF turned text file

Dan,

I've worked on this for a few hours. There are a lot of mistakes in the pdf to text translation, about a third of the input has problems. It would take a considerable amount of time to clean it up. If you can get a cleaner translation I might be able to get something to work but unfortunately, I don't have the time to really go at it. I'm attaching a table of what I've done so far, if you want to take a look.

Don

Re: Help with Regex parsing of PDF turned text file

Dan,

This is very helpful. Let me see what I can do.

Don

Re: Help with Regex parsing of PDF turned text file

Hi Don --

I did not get a ticket. Just looking for interesting cross-industry data sets that are messy and large.

Here is the original PDF:

http://www.courts.state.md.us/district/forms/criminal/dccr090.pdf

See what you think. Thanks for your help.

Dan

Re: Help with Regex parsing of PDF turned text file

Dan,

I took a look at the text file and I think the conversion might have altered the format. If you can help with the structure, I can probably put something together with regex.

...unless, of course, you're trying to benchmark a ticket you recently received. In which case, my manager would strictly forbid me to do so.

Don

Re: Help with Regex parsing of PDF turned text file

Dan,

I hope this is not because you recently have received a ticket and were trying to benchmark it against other fines.

Lou