7
$\begingroup$

I have this Dataset:

data =
  {<|"Key" -> "h", "Values" -> {{10, 97}, {4, 59}, {2, 53}}|>,
   <|"Key" -> "c", "Values" -> {{1, 57}, {10, 34}, {2, 1}, {6, 74}, {9, 53}}|>,
   <|"Key" -> "e", "Values" -> {{6, 7}, {2, 61}, {1, 26}}|>,
   <|"Key" -> "g", "Values" -> {{2, 51}}|>,
   <|"Key" -> "b", "Values" -> {{1, 33}, {2, 13}, {2, 36}}|>,
   <|"Key" -> "d", "Values" -> {{2, 45}, {8, 85}, {4, 77}, {1, 88}}|>,
   <|"Key" -> "f", "Values" -> {{6, 55}}|>};

set = Dataset[data]

enter image description here

I want to find the row and column totals and the grand total. This is easy when I convert set with Normal. For example:

Find total of first column

Normal @ set[All, "Values"] /. {a_, _} :> a // Flatten // Total

81

Find all row totals

Total /@ (Flatten /@ Normal @ set[All, "Values"])

{225, 247, 103, 53, 87, 310, 61}

I would like to achieve results like the two examples above without going back to Normal. Executing

vals = set[All, "Values", Total]

I obtain the two row totals but loose the keys:

enter image description here

Executing

vals[All, Total]

enter image description here

I obtain the row totals. (I don' t know a similar form to get the column totals)

Excluding Normal - which alternatives do I have to compute the totals of my Dataset?

$\endgroup$

4 Answers 4

14
$\begingroup$

Here is a clean way to achieve this:

set[All, {"Values" -> (Total[#, 2] &)}]

OR

set[All, {"Values" -> Total /* Total}]

Mathematica graphics

One can also do:

set[All, {"Values" -> Apply[Total[{##}, 2] &]}]

OR

set[All, {"Values" -> Total@*Map[Total[#, 2] &]}]

For the other one:

set[All, 2, Total][Total, 1]

OR

set[All, "Values", Total][Total, 1]

81

$\endgroup$
3
  • 1
    $\begingroup$ Your ansatz is very interesting. With it I can even compute the "grand total": set[All, "Values", Total][Total][Total] (* 1086 *). $\endgroup$
    – eldo
    Commented Sep 30, 2014 at 19:03
  • $\begingroup$ @eldo Yes indeed. Dataset is interesting, many approach to achieve same thing. I'm still learning. $\endgroup$
    – RunnyKine
    Commented Sep 30, 2014 at 19:06
  • $\begingroup$ @eldo Why not set[Tr@*Flatten, "Values"] or set[Tr@*Flatten, 2] for that, depending on your preference? $\endgroup$
    – Mr.Wizard
    Commented Oct 1, 2014 at 4:27
8
$\begingroup$

If you want to keep the keys when doing the total by row you can also try

MyTotal[a_String] := a;
MyTotal[list_List] := Total[Flatten@list]

set[All, All, MyTotal]

enter image description here

$\endgroup$
3
  • $\begingroup$ @eldo Is this what you wanted? I didn't realize you wished to preserve the left column. $\endgroup$
    – Mr.Wizard
    Commented Sep 30, 2014 at 15:59
  • $\begingroup$ @Mr.Wizard My question is very general: "Which alternatives do I have..." So your answers are perfectly valid and I was just exploring them :) $\endgroup$
    – eldo
    Commented Sep 30, 2014 at 16:04
  • $\begingroup$ @eldo Okay; in any case I added my method for this operation to my answer. (mete, +1 from me, by the way.) $\endgroup$
    – Mr.Wizard
    Commented Sep 30, 2014 at 16:05
8
$\begingroup$

First of all your second result can be had more directly with:

set[All, "Values", Total@*Flatten]
{225, 247, 103, 53, 87, 310, 61}  (* as a Dataset *)

The other result can be had with:

set[Total@*Flatten, "Values", All, 1]
81

If you wish to preserve the first column of the Dataset then I would use:

set[All, Total@*Flatten ~MapAt~ "Values"]

enter image description here


I presumed from the use of "Values" in your own code that you wished to address items by key name, but if you prefer indexes as RunnyKine shows my methods work just as well:

set[All, 2, Tr@*Flatten]

set[Tr@*Flatten, 2, All, 1]

set[All, Tr@*Flatten ~MapAt~ 2]
$\endgroup$
3
$\begingroup$

Thanks to your distinguished answers I can now do everything I wanted to do.

Rows

Row subtotals over a range

set[1 ;; 3, {"Values" -> Total}]

enter image description here

All row totals

totals =
 SortBy[set[All, {"Values" -> Total /* Total}], "Key"]

enter image description here

totals[BarChart[Reverse @ #,
   BarOrigin -> Left,
   ChartElementFunction -> "GradientScaleRectangle",
   ChartLabels -> ToString /@ Reverse @ Normal @ totals[Identity, "Key"],
   GridLinesStyle -> Directive[Dotted, Thick],
   LabelingFunction -> (Placed[#, Right] &),
   Method -> {"GridLinesInFront" -> True},
   ImageSize -> 500,
   PlotTheme -> "Detailed"
   ] &, "Values"]

enter image description here

Columns

Both Columns

set[All, "Values", Total][Total]

enter image description here

Second column total

set[All, "Values", Total][Total][[2]]

1005

Column total over a range

set[1 ;; 3, "Values", Total][Total][Total]

575

Grand total

set[Total @* Flatten, "Values"]

1086

I'm beginning to like Dataset ...

$\endgroup$
1
  • $\begingroup$ +1. I'm beginning to like Dataset myself, it's super convenient. $\endgroup$
    – RunnyKine
    Commented Oct 1, 2014 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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