Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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

Mathematica graphics

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.

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

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

enter image description here

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]] &];
  sub = MapThread[
    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]

enter image description here

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)...

share|improve this answer
    
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. –  Sigismond Kmiecik Mar 2 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 at 10:08
add comment
 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

enter image description here

With a little work work with Nest to generalize..

share|improve this answer
add comment

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:

enter image description here

and

enter image description here

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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