Hadley Wickham wrote a stellar article called "Tidy Data" (link) in JSS last year about data manipulation and getting the data into an "optimal" condition in order to perform analysis. However, I was wondering what were best practices in terms of presenting tabular data in a work setting? Let's say your coworker asks you to provide him with some data. What are some general rules you use when structuring that data? Are the guidelines in "Tidy Data" just as applicable in instances where you're sharing data with non-data professionals? Obviously, this is very context-specific but I'm asking about high level 'best practices'.
As can be expected from Hadley, his article contains a good definition of tidy data and I agree with almost everything in his article and believe it's not only valid to "data professionals". However, some of the points he makes are relatively easy to fix (e.g., with packages he has authored) if some more fundamental problems are avoided. Most of these problems are a result of the widespread use of Excel. Excel is a valuable tool and has its merits, but some of its facilities result in problems for data analysts.
Some points (from my experiences):
- Some people like colorful spreadsheets and make abundant use of formatting options. This is all fine, if it helps them organize their data and prepare tables for presentation. However, it's dangerous if a cell color actually encodes data. It's easy to lose this data and very difficult to get such data imported into statistical software (e.g., see this question on Stack Overflow).
- Sometimes I get some nicely formatted data (after I told people how to prepare it), but despite asking them to use a dedicated column or separate file for comments they decide to put a comment in a value column. Not only do I need to deal with this column in a special way when importing the data, but the main problem is that I would need to scroll through all the table to see such comments (which I usually wouldn't do). This gets even worse if they use Excel's commenting facilities.
- Spreadsheets with several tables in them, multiple header lines or connected cells result in manual work to prepare them for import in statistical software. Good data analysts usually don't enjoy this kind of manual work.
- Never, ever hide columns in Excel. If they are not needed, delete them. If they are needed, show them.
- xls and its descendants are not suitable file formats for exchanging data with others or archiving it. Formulas get updated when the file is opened and different Excel versions might handle the files differently. I recommend a simple CSV file instead, since almost all data-related software can import that (even Excel) and it can be expected that that won't change soon. However, be aware that Excel rounds to visible digits when saving to a CSV (thereby discarding precision).
- If you want to make life easy for others, adhere to the principles given in Hadley's article. Have a value column for each variable and factor columns defining strata.
There are probably several additional points that didn't come to my mind.
Firstly, I'm usually the one who gets the data. So this may read as my wish list.
My most important point is therefore: speak with the one who's going to analyse the data.
I had a quick glimpse over the paper: lots of what Hadley writes could be summarized by 'normalize your relational data base'.
But he also mentions that depending on what is actually going on, it can be sensible to have the same variable either in long or in wide form.
Here's an example: I deal with spectra. From a physical/spectroscopical point of view, the spectrum is e.g. an intensity $I$ as function of the wavelength $λ$: I = f (λ). For physical reasons, this function is continuous (and continuously differentiable). A discretization to particular $λ_i$s occurs just for practical reasons (e.g. digital computers, measurement instruments). This would clearly point to a long form. However, my instrument measures the different $λ_i$ in different channels (of a CCD / detector line or array). The data analysis also treats each $λ_i$ as a variate. That would be in favor of the wide form.
However, there are some practical advantages to non-normalized display/distribution of the data:
It may be much easier to check that the data is complete.
Connected tables as in a normalized relational data base are OK if the data actually is in a data base (in the software sense). There, you can put constraints that ensure completeness. If the data is exchanged in the form of several tables, in practice the links will be a mess.
Data base normalization removes redundancies. In real lab life, redundancies are used to double check integrity.
Thus redundant information should not be removed too early.
Memory / disk size seems to be less of a problem nowadays. But also the amount of data our instruments produce increases.
I'm working with an instrument that can easily produce 250 GB of high quality data within few hours. Those 250 GB are in an array format. Expanding this to long form would blow it up by a factor of at least 4: each of the array dimensions (lateral x and y, and wavelength λ) will become a column, plus one column for the intensity). In addition, my first step during data analysis would usually be to cast the normalized long form data back into spectra-wide form.
- Usually, the data analysis will need a particular form. This is why I advise to talk to the one who will analyse the data.
The tidying work that is addressed by these normalization points is tedious and not a nice job. However, in practice I usually spend much more time on other aspects of tidying
Ensuring the integrity and completeness of the data in practice is a large part of my tidying data work.
Data not being in an easily readable format / switching between slightly different formats:
I get lots of data in the form of many files, and usually some information is stored in the file name and/or path: the instrument software and/or the produced file formats do not allow to add information in a consistent manner, so we either have an additional table (like in a relational data base) that links the meta information to a file name or the file name encodes important information.
Typos or slight changes in the pattern of the file names causes lots of trouble here.
- Tidying from a measurement point of view: getting rid of false measurements (usually caused by known physical processes like someone accidentally switching on the light, cosmic rays hitting the detector, frame shifts of the camera, ...).