How to store complex tables and structures?

Question

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?

Answer 1

In third normal form

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

Answer 2

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.

Answer 3

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.