Simple, reliable, open, and interoperable plain text format for storing data

Question

In a previous question I asked about tools for editing CSV files.

Gavin linked to a comment on R Help by Duncan Murdoch suggesting that Data Interchange Format is a more reliable way to store data than CSV.

For some applications a dedicated database management system is what is needed. However, for small scale data analysis projects something more light weight seems more suitable.

Consider the following criteria for evaluating a file format:

• reliabile: the data entered should stay true to what has been entered; data should open consistently in different software;
• simple: it would be nice if the file format is easy to understand and ideally be readable with a simple text editor; it should be easy to write a simple program to read and write the format.
• open: the format should be open
• interoperable: the file format should be supported by many systems

I find tab and comma separated value formats fail on the reliability criterion. Although I suppose I could blame the importing and exporting programs rather than the file format. I often find myself having to make little adjustments to the options in read.table in order to prevent some strange character from breaking the loading of the data frame.

Questions

• Which file format best meets these needs?
• Is Data Interchange Format a better alternative? or does it have its own problems?
• Is there some other format that is preferable?
• Am I unfairly evaluating TSV and CSV? Is there a simple set of tips for working with such files that make the file format more reliable?

Answer 1

I wonder if there is a criterion collision going on here.

One complaint about file formats such as Excel, SQL, etc are that you have to define the datatypes in advance to have it behave well, which runs contrary to the "something more light weight" criterion (as I understand your restriction to be more time related than computationally related).

In contrast, the criteria that it not muck up the data, or allow the data to be mucked up, require some error checking. Unless you let the system auto-magically figure out the data types (which is essentially where Excel is failing you), there no way to have your cake and eat it too.

IMO, of the two, the second criterion is more important. Data integrity, once violated, makes analysis difficult or impossible. Lost observations or invalid values (if not properly checked) can mess up everything.

In regards to DIF, the actual raw text is not human readable and would be difficult (IMO) for humans to do data entry in.

IMO, you should give delimited files a fair shake. As mentioned above in the comments, the 'data mangling' is mostly the fault of a subset of tools you are using. Well behaved programs should not mangle delimited files. The greatest source of mangling is a poorly specified delimiter. For example, if your data might have commas, a CSV is inappropriate. If it might have tabs TSV is inappropriate. For many (but not all) programs you can specify an alternate delimiter. For example, I've used the tilde (~) in a couple difficult cases.

Answer 2

In all seriousness, I would consider RData files created by R itself as it fits

• reliable (check)
• simple (call it a draw--the format is binary)
• open (check: doesn't get more open than R source code)
• interoperable (check: works everywhere R works)

Close enough for me. If by systems you mean applications rather than operating system then the last point is a fail.

Oh, and RData is efficient as the files are now by default compressed (which used to be an option which was turned off by default).

Answer 3

In response to Dirk Eddelbuettel's answer, I suggest using the HDF5 file format. It is less simple than the RData format, or you might say, 'more rich', but certainly more interoperable (can be used in C, Java, Matlab, etc). I have found that I/O involving large HDF5 files is very fast.

Answer 4

I'm not quite sure why fixed text format with the appropriate meta data does not meet your criteria. It is not as simple to read as a delimiter but you need metadata to use the information anyway. The task of writing syntax to read the program simply depends on how large and complicated the structure of the dataset is. SPSS and Excel have a GUI to help with these tasks.

There are only two errors with CSV files I have come across:

1. Missing fields without delimiters (so every other field in that record is misplaced, I have also had this problem with missing tags in XML)
2. A comma within a text string

(if you have encountered other problems feel free to give examples)

Two is solved with a more irregular delimiter as drnexus suggested (a pipe (|) is one I have encountered before, but a tilde (~) works just as well in that neither is likely to be included in string fields.) One is a problem not easily solved by whatever software you are using, and both are problems with the way people wrote the files to begin with, not the software used to read the files.

I'd also like to say I agree with drnexus on both this thread and his response on your other recent thread about editing these files. You seem to be complaining about the software you use (particularly Excel) and asking to store data in a format that conforms to your ill behaved software. Maybe the question should be how to get Excel to stop auto-formatting plain text files. Your reliable criteria as it appears to me is a software problem with reading plain text files. I don't use R for data management, but I have not had that hard of a time reading delimited files in SPSS as you seem to be suggesting.

If the original files are not written properly what makes you expect any software to reliably read the file? And a specific file format will certainly not prevent you from incorrectly writing the data to whatever file type you choose to begin with.

Answer 5

The common problem with plain text format is that it cannot store metadata. How do you define missing data? How do you define 1=strongly disagree, 2=disagree, ... kinds of stuff in plain text format? With plain text format, you have to use another document to define those metadata. And it is not easy to do in XML.

Sometimes this issue can be very disturbing.

My solution is to use SPSS data format, which is self-contained and easy to edit in SPSS. I know this is not a right answer to your question, but I have been struggled on the same problem for a very long time and this is my current solution.