I oftentimes encounter the difficulty of converting a garbled data file into a flat database version. More robust statistical foray into single datasets requires that they be in an organized state, thus the need for a flat-database format, indicating clearly separated columns or fields and corresponding rows or records. (Database experts also call them tuples.)
Perl has come to the rescue. I’ve used the cool practical extraction and reporting language to rearrange garbled data files I’ve copied from particular websites. The latest task I did was about the file containing number of Philippine barangays per year. For less than an hour (Of course, I could have just manually edited the file for 10 minutes. But I’m just preparing myself for similar problems involving thousands or even a million of rows or records.), I managed to create a script, which is shown in the slides below.
Note: The last slide does not clearly show the separated columns Count and Variance. But when you download the file, everything works out fine. Anyway, if you’re in the similar situation of wanting to clean up a large garbled data file, I may help you out.