# How to store complex tables and structures?

I'm wondering about general approaches to storing complex tables and structures. For example, imagine I have a table like this:

A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 C1 ....Z5
individuals
1             .  .  .  .  . .  .  .  .  .
2             .  .  .  .  . .  .  .  .  .
3             .  .  .  .  . .  .  .  .  .
4             .  .  .  .  . .  .  .  .  .
5             .  .  .  .  . .  .  .  .  .
...           .  .  .  .  . .  .  .  .  .

(I'm just showing the row and column names (variables), but the table would be filled, or could be even much larger, with A1 to A999, B1 to B999 colums.)

I can want to perform some calculation on all $Ax$ columns, or on all $x3$ ones. Since it can be difficult to deal with so many columns, it may be better to write this as:

i  subcase  A B C ... Z
1  1      .  .  .  .
1  2      .  .  .  .
1  3      .  .  .  .
1  4      .  .  .  .
1  5      .  .  .  .
1  6      .  .  .  .
1  7      .  .  .  .
1  8      .  .  .  .
1  9      .  .  .  .
2  1      .  .  .  .
2  2      .  .  .  .
...       .  .  .  .

I remember seeing that kind of structure several times, sometimes referred to as long format. Later, one can perform arbitrary operations on all rows meeting some condition on "case". This has some advantages, but could be confusing.

One could average or crate the differences of all the $\text{subcase}==1$ rows, only from $\text{individual}==1$, or when $\text{individual}==subcase$, for example.

But what would be the best way to store the resulting numbers--columnwise or rowise? If I wanted to store the average of all $\text{subcase}==1$ rows, I would need to repeat the number on every row meeting that condition.

Another option would be to keep the wide format structure, but having 2 rows for the headers—one for the letters and a second one for the numbers. I think it could be a natural way, but I've never seen it before.

Are there other structures people can recommend for this problem, or for more complex situations, such as trees?

• Lists, as inspired by Lisp, are a very powerful data structure. They can store arbitrarily deep and ragged data structures of mixed types. Take a look at Mathematica or perhaps JSON for some inspiration. Commented Jul 21, 2015 at 22:23

IndvidualID
CatID
CaseID
Value

Primary Key: IndvidualID, CatID, CaseID

If you need to constrain CatID and/or CaseID then have a FK

From that structure you can easily loop and create long format or multidimensional array.
It is also space efficient as a null is simply no row.

In sql an avg

select CatID, avg(value)
from table
where CaseID = x
group by CatID

Or you can just loop on those same constraints
Or a tool like LINQ

It all depends on what you're trying to accomplish. For example, if you want to save space and you have sparse data, you want your table to grow "long" as you describe and let the absence of a row signify that you don't have data (rather than structuring your sparse field as a column, which would typically require space to store your empty data points).

If you need a data structure that makes it easiest to "process" the data, it depends on what your process is. Sometimes, a complex, nested data structure is best (e.g., JSON), other times, a flat table where some fields have repeating values is best.

• but how would you store intermediate calculations in long format? (such as averages, cumsums or lagged differencies)
– skan
Commented Jul 21, 2015 at 19:34
• Indeed. You have to provide some more information with what data you want to do which calculations and how many individuals you excpect as well as how sparse you except an individual's features to be. Commented Jul 21, 2015 at 20:52
• This week my data is a table with 2400 medical variables (such as diastolic blood pressure, results from biochemical analysis...) most of them real numbers, some missings, subdived in groups (and repeated several times through time), and almost 30000 individuals.
– skan
Commented Jul 22, 2015 at 9:20
• It sounds like space shouldn't be an issue - even in long form, we're talking max, 72 MM rows which a DB on a reasonably beefy server should handle. You probably want to draw timeseries for repeated measurements which should definitely be "long" form - a field for the timestamp and a field that tells you "what", e.g., "diastolic BP", and a field for the value. Not sure what other analysis you're going to want to do. If you want to do some kind of regression, you'll want to transform your data such that one row represents one patient. Commented Jul 22, 2015 at 18:56

To be frank, I really donot understand why this is an issue for you. If you want to a generic solution, I will say save it to a csv file. A file with 30,000 rows and 1,000 cols is NOT that big. Even a shell pipe with simple one-liners can do tasks you mentioned. For example, the following one-liner compute the mean of cells located at Ax rows and x3 cols of data.csv (I generate this data matrix of size 30,000x1,000 by filling random integers )

cat data.csv | perl -F',' -lane 'BEGIN{ $avg=0;$num=0; $row=0; }$row++; next if( $row%3!=0);foreach$i (0..$#F){ if($i%26 == 0){ $avg+=$F[$i];$num++;} }END{ print "avg=" . ($avg/$num); print "num=\$num"; }'
avg=0.1998
num=390000

It takes about 3 seconds on my laptop. You may put whatever interested conditions to filter data, collect filtered ones and compute stats.

Since for whatever complicate condition you AT MOST scan each element once, the complexity is thus at most linear. Even for a big data file, this will not be a big issue. By the way, adding extra auxiliary is simply to 'pre-compute' something in advance.

• In some months I'll have to apply survival and logistic regression analysis on a file with 12000 rows (people) and 45000 variables. before that I want to practice and plan a general strategy.
– skan
Commented Jul 24, 2015 at 9:41
• -1: Answers should aim to help the asker, not be a platform for comparing ideas about what should or shouldn't be too difficult! Try to rephrase in a more constructive way! Commented Oct 22, 2015 at 22:15