# Implementing nested subtotals

Inserting a nested subtotal in a spreadsheet is a facility that Excel performs very well.
But if a subset of this facility can be implemented in MMA for a table which needs it the hassle of its export/import will be avoided.
As an example I have the following 3-columns matrix, the first two columns of which I want subtotals values for. The matrix was sorted so that the rows I want subtotaled are grouped together:.

data = {{Pi, A, 15}, {Pi, B, 20}, {Pi, B, 10}, {Pi, C, 20}, {Pi/2, A,
1}, {Pi/2, B, 2}, {Pi/2, B, 3}, {Pi/3, B, 10}}


And I want this result in separate matrix from the input data.

result = {{π, A, 15}, {π, B, 30}, {π, C, 20}, {π,
"∑", 65}, {π/2, A, 1}, {π/2, B, 5}, {π/2,
"∑", 6}, {π/3, B, 10}, {π/3, "∑",
10}, {"∑", "∑", 81}} // MatrixForm


I came up with a partial result,and what remains is too challenging for me (it is wrong to group results only on breaks in column 2.)

{#[[1, 1]], "∑", Total[#[[All, 3]]]} & /@ SplitBy[data, First] (* subtotals col1 *)
{ "∑", "∑", Total[#[[All, 3]]]} & /@ {data} (*grand total *)


I would like to have a generalised answer, valid for more than two columns for subtotals as in the example I gave here.

-

I post this as a start (currently very time poor):

fun[u_] := Module[{g, s1, sub}, g = GatherBy[u, {#[[1]], #[[2]]} &];
s1 = GatherBy[{#[[1, 1]], #[[1, 2]], Total[#[[All, 3]]]} & /@
g, #[[1]] &];
sub = {Style[#, Red, Bold] & /@ {#[[1, 1]], "\[CapitalSigma]",
Total[#[[All, 3]]]}} & /@ s1;
(Join @@ Riffle[s1, sub])~
Join~{Style[#, Blue, Bold] & /@ {"\[CapitalSigma]",
"\[CapitalSigma]", Total[u[[All, 3]]]}}
]


Applying:

fun[data] // MatrixForm


An interactive approach:

opn[u_] :=
Module[{g, s1, sub, tot, sbt}, g = GatherBy[u, {#[[1]], #[[2]]} &];
s1 = GatherBy[{#[[1, 1]], #[[1, 2]], Total[#[[All, 3]]]} & /@
g, #[[1]] &];
FlipView[{Grid@#1, Grid@#2}] &, {s1, GatherBy[u, #[[1]] &]}];
tot = Style[#, Blue, Bold] & /@ {"\[CapitalSigma]",
"\[CapitalSigma]", Total[u[[All, 3]]]};
sbt = {Style[#, Red, Bold] & /@ {#[[1, 1]], "\[CapitalSigma]",
Total[#[[All, 3]]]}} & /@ s1;
Column[Join[Riffle[sub, Grid /@ sbt], {Grid[{tot}]}], Frame -> True]]


Applying:

opn[data]


I am not sure whether the generalization was meant to cover: more data columns (i.e. same criteria but more columns to add, if so relatively easy to adapt); or desire to choose to vary and increase the collapse/expand of groups (ala Excel pivot tables)...this would be more complex...beyond my time (and skill)...

-
thanks for your extended and adaptive answer which made discover the usefulness of FlipView. I thought about partly emulating the Excel pivot tables but it is a question too presumptuous to ask by a modest MMA user as myself who may not thoroughly understand any answer received. – Sigis K Mar 2 '14 at 17:26
@SigismondKmiecik thank you...I just wanted to post something hopefully useful or motivating. We are all learners...the fun is playing with new approaches...good luck – ubpdqn Mar 3 '14 at 10:08
 Reap[(
( Sow[Append[#[[1, 1 ;; 2]], Total@Flatten@Take[#, All, {3}]]] & /@
SplitBy[#, #[[2]] &] ;
Sow[{#[[1, 1]], "S", Total@Flatten@Take[#, All, {3}]}]) & /@
SplitBy[ data , #[[1]]  & ]);
Sow[{"Grand", "Tot", Total@Flatten@Take[data, All, {3}]}]][[2,1]]  // MatrixForm


With a little work work with Nest to generalize..

-

I think this does what you want, generalized to more "columns", so long as number of "columns" is consistent across lists:

nestedTotal[list_] := Module[{cnt = Length[list[[1]]]},
NestWhileList[(cnt--;
Map[Append[#[[1, ;; cnt]], Total[#[[All, -1]]]] &,
GatherBy[#, #[[;; cnt]] &]]) &, list, cnt > 0 &]]

data = {{Pi, A, 15}, {Pi, B, 20}, {Pi, B, 10}, {Pi, C, 20}, {Pi/2, A,
1}, {Pi/2, B, 2}, {Pi/2, B, 3}, {Pi/3, B, 10}};

result = nestedTotal[data]

data = {{Pi, A, B, 15}, {Pi, A, B, 20}, {Pi, B, B, 10}, {Pi, A, C,
20}, {Pi/2, A, A, 1}, {Pi/2, A, B, 2}, {Pi/2, B, B, 3}, {Pi/3, A,
B, 10}};

result = nestedTotal[data]


Outputs:

and

You can "decorate" the results as desired (e.g., sigma symbol, etc.), e.g. like:

Join @@ Function[arg,
Map[PadLeft[#, Length[arg[[1, 1]]], "\[Sum]"] &, Rest[arg], {2}]][
result] // MatrixForm


or this, which gives precisely the output of your OP (I have not extensively exercised this, can probably be simplified, in any case, massaging results is pretty trival):

Join @@ GatherBy[
Join @@ MapIndexed[
Function[{arg, idx},
Map[Flatten@
Insert[#, ConstantArray["\[Sum]", idx - 1], Length[#]] &,
arg]], Rest@result], First] // MatrixForm

-