How to determine how many variables and what kind of variables a table of data has?

Question

Here's an example regarding what I'm lost on. In the past I've just found myself counting columns to determine how many variables a table has, but I've realized that's totally insufficient.

For example, I look at the following table and would think: That has three variables because there are three columns. There are two categorical variables, Season and Type, and one numerical variable, Dollars.

    ['Season',  'Type',     'Dollars'],
    ['Winter',  'Sales',     1000],
    ['Winter',  'Expenses',  400],
    ['Winter',  'Profit',    250],
    ['Spring',  'Sales',     1170],
    ['Spring',  'Expenses',  460],
    ['Spring',  'Profit',    250],
    ['Summer',  'Sales',     660],
    ['Summer',  'Expenses',  1120],
    ['Summer',  'Profit',    300],
    ['Fall',    'Sales',     1030],
    ['Fall',    'Expenses',  540],
    ['Fall',    'Profit',    350]

However, I could then look at the following table, containing exactly the same information, and think: That has four variables because there are four columns: one categorical variable, Season, and three numerical variables, Sales, Expenses and Profit.

    ['Season',  'Sales', 'Expenses', 'Profit'], 
    ['Winter',  1000,    400,        200],
    ['Spring',  1170,    460,        250],
    ['Summer',  660,     1120,       300],
    ['Fall',    1030,    540,        350]

So what's true? And what is the correct method to determine how many, and what kind of variables a set of data has? Is there a proper way to structure the data to give the "right" answer?

Answer 1

There's most likely no single correct answer to the question "how many variables does this dataset have", one can structure the data in different ways as you've shown leading to different numbers of columns.

However, there's probably a good answer to "what structure would make this dataset most amenable for analysis", and that would probably be the first version you presented.

Hadley Wickham has done a bit of writing about this on what he calls "tidy data" (see this paper). When a dataset is tidy it's in its most ably-analyzed form. Ie. it's in its most basic form for an easy and consistent way for transformations to be applied on top, so that further analysis can be done. He argues that the best ways to structure a dataset for analysis are when:

1. Each variable forms a column.
2. Each observation forms a row.
3. Each type of observational unit forms a table.

The first dataset you presented, the one with 3 columns, would fit as tidy under these guidelines.

He also outlines 5 common ways datasets get untidy:

1. Column headers are values, not variable names.
2. Multiple variables are stored in one column.
3. Variables are stored in both rows and columns.
4. Multiple types of observational units are stored in the same table.
5. A single observational unit is stored in multiple tables.

The second version of your dataset, which has four columns, exhibits issue #1. This can be seen through Section 3.1 of his paper where he refers to the dataset about religion and income.

Again, there's probably no "correct" answer to the question of how many variables does this dataset have, but to the question how many columns should this dataset have, the right answer would be 3. Tidy data makes it easy and provides a consistent way to perform additional transformation to a dataset to get it into whatever future form is needed for analysis. Eg. Your second dataset with 4 columns is easily created with a Pandas group_by or an R aggregate from the first dataset.

Answer 2

@nick-eng pretty much answered it all (+1)! I just thought I could add some examples to illustrate his points and to show why the long-format (your first table) is more efficient to work with, especially when you are working with Hadley Wickham's R packages ggplot2 and plyr. But I do have to say that I often prefer to use the wide-format when reporting mean values in manuscripts.

-- Begin edit --

As @ttnphns rightly points out (see comment under OP's question), many analyses require the data to be in the long-format, whereas multivariate analyses usually need to have the dependent variables as individual columns. This also holds for repeated measures when analyzed with the Anova function of the car package.

-- End edit --

I used your first table and read it into R. With the dput() function, I can let R print the data into the console from where I can copy and paste it here so that other people can work with it easily:

d <- structure(list(Season = structure(c(4L, 4L, 4L, 2L, 2L, 2L, 3L, 
3L, 3L, 1L, 1L, 1L), .Label = c("Fall", "Spring", "Summer", "Winter"
), class = "factor"), Type = structure(c(3L, 1L, 2L, 3L, 1L, 
2L, 3L, 1L, 2L, 3L, 1L, 2L), .Label = c("Expenses", "Profit", 
"Sales"), class = "factor"), Dollars = c(1000L, 400L, 250L, 1170L, 
460L, 250L, 660L, 1120L, 300L, 1030L, 540L, 350L)), .Names = c("Season", 
"Type", "Dollars"), class = "data.frame", row.names = c(NA, -12L
))

Make a graph using ggplot2:

require(ggplot2)
ggplot(d, aes(x=Season, y=Dollars)) + geom_bar(stat="identity", fill="grey") +
# Especially for the next line you need the data in long format
facet_wrap(~Type)

Summarizing data and calculating mean and standard error:

require(plyr)
d.season <- ddply(d, .(Season), summarise, MEAN=mean(Dollars),
              ERROR=sd(Dollars)/sqrt(length(Dollars)))

Make another graph using ggplot2 using the summarized data d.season:

ggplot(d.season, aes(x = Season, y = MEAN)) + 
geom_bar(stat = "identity", fill = "grey") +
geom_errorbar(aes(ymax = MEAN + ERROR, ymin = MEAN - ERROR), width = 0.2) +
labs(y = "Dollars")

Now, switching back and forth between the wide and long format using the functions dcast() and melt() from the package reshape2. Note that the data will now be alphabetically ordered:

require(reshape2)

Long to wide format:

d.wide <- dcast(d, Season ~ Type, value.var = "Dollars")
> d.wide
  Season Expenses Profit Sales
1   Fall      540    350  1030
2 Spring      460    250  1170
3 Summer     1120    300   660
4 Winter      400    250  1000

Wide to long format:

d.long <- melt(d.wide, id.vars = "Season", variable.name = "Type", value.name = "Dollars")
> d.long
   Season     Type   Dollars
1    Fall Expenses   540
2  Spring Expenses   460
3  Summer Expenses  1120
4  Winter Expenses   400
5    Fall   Profit   350
6  Spring   Profit   250
7  Summer   Profit   300
8  Winter   Profit   250
9    Fall    Sales  1030
10 Spring    Sales  1170
11 Summer    Sales   660
12 Winter    Sales  1000

Compare to original data frame (not alphabetically ordered):

> d
   Season     Type Dollars
1  Winter    Sales    1000
2  Winter Expenses     400
3  Winter   Profit     250
4  Spring    Sales    1170
5  Spring Expenses     460
6  Spring   Profit     250
7  Summer    Sales     660
8  Summer Expenses    1120
9  Summer   Profit     300
10   Fall    Sales    1030
11   Fall Expenses     540
12   Fall   Profit     350