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

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?
• I should add, R doesn't have a write.DIF() so it is a bit of a one-way street I am afraid. – Gavin Simpson Dec 8 '10 at 11:01
• I do not understand the issue of csv and reliability. Do you mean that csv is not strict enough ? Strict means that if it the regulations for csv were strict enough, every tool following these definitions could load a file without the need of extra parameters. – steffen Dec 8 '10 at 12:20
• @steffen I mean things like: loading and saving a csv file in some programs changes the csv file; loading csv files can result in inappropriate conversion unless you are careful; csv files sometimes break when strange combinations of characters are added without proper escaping. Perhaps I'm confusing use of csv with the format itself, although I have heard people comment about the lack of an official standard. Of course, I realise that in many cases it works just fine. – Jeromy Anglim Dec 8 '10 at 12:36
• @steffen: CSV doesn't store any information about the format or data-types of the data stored in the file. You can well open a CSV file in two different apps and have it interpret the data in the file in two different ways. – Gavin Simpson Dec 8 '10 at 13:49
• @JeromyAnglim, I think that changing of the csv file depends on your software, not the csv format per se. – Roman Luštrik Dec 8 '10 at 13:52

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.

• Thanks. It sounds like using a delimited file format with appropriate care may be the best option. – Jeromy Anglim Dec 9 '10 at 1:48

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

• RData certainly works well with R. It might be problematic with regards to version control. I suppose the R function dput() provides a plain text alternative that would work with version control. However, one of the appeals of csv/tsv is that when I share a repository with data (say for a journal article), people could take the data and reanalyse it easily using any software they like. – Jeromy Anglim Dec 9 '10 at 1:45
• Yes, it is a hugely complicated matter. I think people have discussed this since the dawn of computing. I had two more thoughts (and I could expand my answer): ProtocolBuffers are good for efficiently sharing with Python, Java, C++, ... and a host of other languages; Romain and I cover R. The the new-ish site mldata.org covers this for research in Machine Learning -- they even have tools they make available to convert. That may be worth a look. – Dirk Eddelbuettel Dec 9 '10 at 1:48
• Actually, SVN takes binary blobs such as pdf files etc without problem. I'd suspect git does too. – Dirk Eddelbuettel Dec 9 '10 at 3:08
• That's good to know about binary blobs. It would still be nice to be able to run diff on text files and get meaningful information about changes. Thanks also for link to mldata.org. That looks interesting. – Jeromy Anglim Dec 9 '10 at 3:27
• Pleasure. Sister site mloss.org is simply great, if hope they get traction for mldata.org. The time is right for that. – Dirk Eddelbuettel Dec 9 '10 at 3:32

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.

• (+1) Any thought about its performance compared to NetCDF? – chl Dec 9 '10 at 6:39
• IIRC that is also the internal format chosen at mldata.org -- with a suite of tools which convert. The converters may be worth a look. I always had the feeling that R support for HDF5 was less that perfect. – Dirk Eddelbuettel Dec 9 '10 at 17:07
• @chl I had vaguely thought that NetCDF used HDF5 internally, but that seems to be not quite accurate. – shabbychef Dec 9 '10 at 17:13

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.