Comment by IanCal | Hacker News Reader

Comment by IanCal | Hacker News Reader

Parent Full thread

A couple of partially relevant things:

Something that drives me to really ask what the requirements are is building something for my Dad. It was a tool to do deconvolution of MS spectra, and he said it needed to run "quickly". I got versions down to an hour, then 15 minutes and bottomed out at about 5 minutes for a decent result. After a while I talked to him about the timings and he said that "quickly" meant "under a day". A failure on my part to clarify what is a really fuzzy term. Linked to that, at the time they had a process which involved someone frequently manually looking up an item in a ~3-5k list.

I'm a data scientist, and I think that the amazing tools available now can really cloud the problems that are faced by many organisations. Sure, we can use word-sense vectors to create a deep neural net to do a thing, but 4% of your data has a country of "NONE". Or you've got dates that don't make sense (1000 years into the future), or a suspicious amount at 1/1/1970, 1/1/1900 and 1/1/1904. I've seen important things with "ZZ TEST DO NOT USE" as a field, there's truncated data, broken encodings and more.

This isn't to make fun of the people with these errors, getting and keeping good data is hard and often overlooked. But unless you've got that, you're probably not going to be able to get anything useful from the fancy algorithms. And even then, there's a huge amount to be gained from improving simple interactions at the point humans and computers interface.

I can't express surprise at any of this.

A few responses:

I hope that list was sorted.

4% of Country = "NONE" sounds like they're running a tight ship compared to some places I've seen.

At least those date fields don't have "Unknown" as a value. Yes, I have seen the date field stored as text.

You are absolutely right. The ETL side, nailing down appropriate business logic, chasing down source errors and inconsistencies, altering processes to capture the data we need to give good answers; these are the challenges that take >80% of our time. If we do our job right in these pieces, the reporting can be done by an intern who learned how to use a pivot table yesterday.

> I hope that list was sorted.

Ish. If I remember rightly I think they were trying to compare multiple fields on those elements but it could be narrowed down.

> Yes, I have seen the date field stored as text.

Oh yes, that is always fun. Particularly when you see both 23/05/99 and 05/23/99 in the same column. Something I've ended up building bits and pieces of is work to try and find these kinds of inconsistencies. I'm slowly trying to automate a lot of the initial checks on a new dataset:

* Does it have a consistent number of columns in the CSV file? * Does it have fields with a surprising amount of question marks in? * Are the dates parseable with a single format? If you need to be precise, how many can be parsed by only one of the formats that's seen in the whole dataset? * How many things are blank? * How many things are blank-ish? NONE, FALSE, Empty, N/A, etc. * What does the encoding look like, are there any particularly weird characters? * What control characters can you see? (after hitting an enormous XML file which failed to parse half way though) * What does the type look like for each column? Currency (and then proportions), date, etc. * Are there number separators? Are they consistent? (1,000.00 vs 1.000,00)

Basically, what will trip me up later and leave me scratching my head before having to add yet another bit of code to ignore a field?

One of my side projects at the moment is to pull this stuff together from rag-tag bits of scripts and split up code to something I can just throw files at and get an initial report.

Also, something very important in this is how things overlap. 5% of each column being empty/broken might mean you have 7% of your data with almost no information or 90% of your data missing at least one thing. Depending on what you want to do, either might be OK or terrible.

> If we do our job right in these pieces, the reporting can be done by an intern who learned how to use a pivot table yesterday.

Yes, exactly! The best end point is where new questions and updates can be done either by someone else like this or by the experts who really know their field.

Ahh, thank you, I needed a bit of a data rant :)

What sizes of data are you talking about here? 1e5 or 1e8 (or higher)?

And people wonder why I call my job "Data Janitor".

Oddly these issues are predictable and I think there is a lot of potentials for tools to solve these exact problems.

I am skeptical about the possibility of throwing bodies at the problem because what you find is that everybody is excited about getting things done yesterday and not about getting things done correctly, consistently, or repeatably and without that mo' people, mo' data and mo' money just means mo' problems.

Reply on news.ycombinator.com