> 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 :)