Parsing badly formatted text files

Programming Questions

velvetkev..

Parsing badly formatted text files

in Programming Questions • 11 months ago

Hello! I'm working on a project based on live traffic speed data from New York (not really live at the moment for obvious hurricane related reasons, but the last updated data is still there). Problem is, there are a lot of extra line breaks inserted at different times (you can see where this happens about 75% of the way down the linked file), so the CSV style breaks down and using loadStrings() doesn't work the way it should.

At the moment I have a version working which is using brute force and ignorance to strip out any line which doesn't begin with a whole number (the ID attribute) and has at least 12 items, but this approach cuts out a significant portion of the useful data. Since the data is (theoretically) live I can't use Excel or whatever to clean it up, although opening it as a CSV in LibreOffice Calc and separating fields with " does a pretty good job.

So, tl;dr version: does anyone have any advice or strategies for dealing with irregular data with erroneous line breaks?

Thanks!

Stripped down version of what I'm doing at the moment, if you want to punish yourself:

String[] data;
String[][] splitData;
ArrayList<CamData> cams;
int activeCam= 0;
void setup() {
size(100, 100);
cams= new ArrayList<CamData>();
getData();
for (int i=0; i<cams.size(); i++) {
CamData c= cams.get(i);
if (c.parse() && c.fixCoords()) {
if (c.coords==null) {
cams.remove(i);
}
println("camera: "+ i+ ": ");
println(c.coords);
}
else {
cams.remove(i);
}
}
}
void draw() {
}
void getData() {
try {
data= loadStrings("http://207.251.86.229/nyc-links-cams/LinkSpeedQuery.txt");
}
catch(Exception e) {
println("unable to load data");
exit();
}
splitData= new String[data.length][13]; //each line of the input txt
for (int i=1; i<data.length; i++) {
splitData[i]= trim(splitData[i]);
splitData[i]= splitTokens(data[i], "\"");
try {
if (splitData[i][0]!=null && splitData[i].length>=12) {
cams.add(new CamData(splitData[i][0], splitData[i][2], splitData[i][6], splitData[i][12]));
}
}
catch(Exception e) {
//println(i+ ": " +e);
}
}
}
class CamData {
String initid;
String initspeed;
String initcamStatus;
String initcoords;
int camStatus;
int id;
float speed;
float[] coords;
CamData(String theId, String theSpeed, String theCamStatus, String theCoords) {
initid= theId;
initspeed= theSpeed;
initcamStatus= theCamStatus;
initcoords= theCoords;
}
boolean parse() {
try {
id= int(initid);
speed= float(initspeed);
camStatus= int(initcamStatus);
if (Float.isNaN(speed) ||camStatus==-101) {
return false;
}
else {
return true;
}
}
catch(Exception e) {
return false;
}
}
boolean fixCoords(){ //converts coordinate strings to lat/long floats
String[] splitCoords= splitTokens(initcoords, ",()/n ");
splitCoords= trim(splitCoords);
//println(splitCoords);
if(Float.isNaN(Float.parseFloat(splitCoords[0]))){
return false;
}
else{
coords= new float[splitCoords.length];
for(int i=0; i<splitCoords.length; i++){
coords[i]= float(splitCoords[i]);
}
if(coords==null){
return false;
}
else{
return true;
}
}
}
}//end of class

Replies(10)

PhiLho

Re: Parsing badly formatted text files

11 months ago

General note: loadStrings() doesn't throw any exception...
What you can do (without taking a look at the data) is to split the data: if the result hasn't the right number of fields, append the next line to the current one and try again. Until you get a full line. Then proceed to the next line.

v.k.

Re: Parsing badly formatted text files

11 months ago

Also did not look at data, but once i did replace all "\n" with space than loaded a biiiiig line and splited it... to reformat

PhiLho

Re: Parsing badly formatted text files

11 months ago

I had a look at the data. That's not CSV, but TSV: the separator is a simple tab.
I glanced at your code: you trim splitData before it is initialized...
And the try / catch statement in the loop is useless. See http://wiki.processing.org/w/What_is_an_exception%3F

velvetkev..

Re: Re: Parsing badly formatted text files

11 months ago

OK, thanks, the tab separator does make things simpler. The try/catch was actually catching exceptions but it was definitely not good practice, so I've replaced it with some more robust logic ;)

The main issue remains, though, that sometimes a carriage return is getting inserted into the middle of data items. E.g. when I split the input strings using tabs, I should get one of the items looking like this (it's a bunch of lat/long coords):

[6] ""40.74047, -74.00925000000001 40.74137, -74.00893 40.743170000000006, -74.00859000000001 40.746230000000004, -74.00797 40.74812, -74.00765000000001 40.74870000000001, -74.00769000000001 40.74971, -74.00819 40.75048, -74.00832000000001 40.75161000000001, -74.00789 40.753750000000004, -74.00704 40.75721, -74.00463 40.76003, -74.00263000000001 40.760740000000005, -74.00208""

What I get instead is this:

[0] "(40.78918, -73.78792)"

[0] "(40.788700000000006, -73.78952000000001)"

[0] "(40.788270000000004, -73.79063000000001)"

[0] "(40.787290000000006, -73.79263)"

[0] "(40.786680000000004, -73.7941)"

[0] "(40.7864, -73.79490000000001)"

[0] "(40.786120000000004, -73.79624000000001)"

[0] "(40.78602, -73.79693)"

because new lines have been inserted in the middle of some items.

I suspect that there's no easy solution here and I'll have to try and write something that tries to recognise the format of the lat/long strings etc and combine them into one item, but that sounds difficult and painful, so I thought I'd ask first :)

PhiLho

Re: Parsing badly formatted text files

11 months ago

Re-read my first answer: I don't think my solution is hard / painful...

I might try and implement my suggestion later, if I find time.

velvetkev..

Re: Re: Parsing badly formatted text files

11 months ago

Thanks, but don't feel that you have to trouble yourself on my behalf. I think it's slightly more complicated because there's not a consistent number of lat/long points per line but I'll see if I can implement what you suggest.

PhiLho

Re: Parsing badly formatted text files

11 months ago

" there's not a consistent number of lat/long points per line"
That's not a problem if there is a consistent number of tabs per line. That's the criterion to ensure you have a complete line.

velvetkev..

Re: Re: Parsing badly formatted text files

11 months ago

Aha... lightbulb moment here I think. Thanks! Will have a shot at it when I've had more than two hours of sleep.

PhiLho

Re: Parsing badly formatted text files

11 months ago

I just wanted to verify my idea worked... So here is my five-minute hack:

void setup()
{
// size(500, 300);
String[] lines = loadStrings("G:/Downloads/LinkSpeedQuery.txt");
// I suppose the first line is never splitted
String[] fields = split(lines[0], "\t");
int fieldNb = fields.length;
for (int i = 1; i < lines.length; i++)
{
print("(" + i + ")");
String line = lines[i];
String[] parts = split(line, "\t");
while (parts.length < fieldNb)
{
print("+");
// Append next line to this one
line += lines[++i];
parts = split(line, "\t");
}
// Example of usage
String id = getData(parts[0]);
String linkPoints = getData(parts[6]);
println(id + " @ " + linkPoints);
}
}
String getData(String field)
{
if (field.startsWith("\""))
{
field = field.substring(1, field.length() - 1);
}
return field;
}
void draw()
{
exit();
}

velvetkev..

Re: Parsing badly formatted text files

11 months ago

Thanks PhiLho, that pretty much does the trick I think. Still a couple of oddities sneaking through but it's light years better than it was.

Thanks again.

Top Reply