# Finding totals in a Dataset

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?

## 4 Answers

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

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

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]


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

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]


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

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