1
$\begingroup$

For example, I have file with data on 3 cars, 3 boats, 3 motorcycles:

type speed  power  capacity  price
car    120    100         4     10
       160    150         5     20
       180    200         7     25
boat    60    120         8     15
        50    150         10    25
        80    340         12    45
moto   140    150         2     10
       160    200         1     30
       100    120         3     8

For manipulating with this data in R, should I use this one file, or transform it to this:

type speed  power  capacity  price
car    120    100         4     10
car    160    150         5     20
car    180    200         7     25
boat    60    120         8     15
boat    50    150         10    25
boat    80    340         12    45
moto   140    150         2     10
moto   160    200         1     30
moto   100    120         3     8  

Or better to use 3 different files?

$\endgroup$
  • 1
    $\begingroup$ I tried to answer this in a platform-neutral way, as I'm pretty sure that the long, explicit format is preferred by most platforms, not just R. $\endgroup$ – Matt Krause Mar 31 '16 at 0:31
  • 3
    $\begingroup$ I think questions about how to store and process data can be considered on-topic here, so long as it isn't too software-dependent. I certainly feel that @MattKrause's answer should be considered on-topic even if the original question was asked in the context of R. Perhaps if people think the thread should be closed, they could suggest how the question should be amended for it to be a better fit here? For comparison, I think this Q can get a better and more general answer here than it would at Stack Overflow. $\endgroup$ – Silverfish Mar 31 '16 at 7:15
  • 1
    $\begingroup$ This question can have radically different sets of answers depending on what you mean by "manipulating." Do you intend this to refer to storing, managing, providing access to, or analyzing the data? For storage and management, very strong arguments can be advanced against any of the proposed solutions, whereas for certain kinds of analysis there are often good reasons to prefer the "long" (or "flat") form shown in the second example. Please edit the post to clarify what you mean. (BTW, I agree with @Silverfish that the subject is on topic.) $\endgroup$ – whuber Mar 31 '16 at 15:11
3
$\begingroup$

The bottom format is often called a "long format", and is often the best way to structure data for analysis because it makes everything explicit.

Both tables obviously contain the same data, but some of the type data only implicitly present in the top table. For example, we need to infer that row #2 describes a car from the total number of rows, the description ("I have file with data on 3 cars, 3 boats, 3 motorcycles"), and the parallel structure between rows #1, #4, and #7. This introduces an annoying dependency between the rows: to find the type of row $n$, you need to find the first non-empty type "above" it in the table and propagate it downwards. It also introduces the possibility for errors: if we weren't careful, we might conclude that the type information is missing for the remaining rows. Similarly, if the data were divided across three files, we'd have a similar implicit dependency on the file name

In contrast, everything is laid out explicitly in the long format. There is no need to deduce anything: if you need the type of the $n$th example, you just look at the $n$th entry of type. This layout also mirrors many of the things you're going to do with the data. For example, a regression expresses one column of the table (e.g., the price), as a combination of the remaining columns. It also allows you to extract subsets of the table, which you might do during cross validation.

The major disadvantage of the long format is that it contains more redundancy--you had to type "car" three times more often when producing the bottom table.

Most data analysis packages have functions for converting data to and from "long" format, which is often called "melting" the data. In R, you may want to look at tidyr or reshape2 packages. In python, pandas has similar functions, and stata has reshape long. You may need to write a little bit of custom code though, depending on exactly how the implicit data is structured.

| cite | improve this answer | |
$\endgroup$
  • $\begingroup$ +1 You could also point out explicitly that a simple sorting by, e.g., price would destroy the implicit information in table 1. $\endgroup$ – Roland Mar 31 '16 at 7:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.