Finding totals in a Dataset

Question

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]

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:

Executing

vals[All, Total]

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?

Answer 1

Here is a clean way to achieve this:

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

OR

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

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

Answer 2

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]

Answer 3

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"]

---

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]

Answer 4

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}]

All row totals

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

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"]

Columns

Both Columns

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

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