# Counting the occurences of entries in a dataset columnwise

Consider a dataset with some rows and considerably less columns (eg something like 10x3). Assume that the contents of the dataset cells are integers from some conveniently chosen range (eg 1 through 10).

The required task to perform, is to count the number of occurrences of each integer in the given range for every column, and return the results in ascending order.

For example, if the dataset is

d=Dataset[<|"r1" -> <|"c1" -> 8, "c2" -> 7, "c3" -> 9|>,
"r2" -> <|"c1" -> 4, "c2" -> 10, "c3" -> 5|>,
"r3" -> <|"c1" -> 2, "c2" -> 9, "c3" -> 6|>,
"r4" -> <|"c1" -> 9, "c2" -> 1, "c3" -> 1|>,
"r5" -> <|"c1" -> 9, "c2" -> 4, "c3" -> 4|>,
"r6" -> <|"c1" -> 9, "c2" -> 7, "c3" -> 2|>,
"r7" -> <|"c1" -> 2, "c2" -> 4, "c3" -> 2|>,
"r8" -> <|"c1" -> 9, "c2" -> 6, "c3" -> 4|>,
"r9" -> <|"c1" -> 2, "c2" -> 1, "c3" -> 6|>,
"r10" -> <|"c1" -> 9, "c2" -> 1, "c3" -> 2|>|>]


The following code produces the required output:

Transpose /* Query[All, Counts] /* Transpose /* SortBy[Keys]@d


(The output is:

<|1 -> <|"c1" -> Missing["KeyAbsent", 1], "c2" -> 3, "c3" -> 1|>,
2 -> <|"c1" -> 3, "c2" -> Missing["KeyAbsent", 2], "c3" -> 3|>,
4 -> <|"c1" -> 1, "c2" -> 2, "c3" -> 2|>,
5 -> <|"c1" -> Missing["KeyAbsent", 5],
"c2" -> Missing["KeyAbsent", 5], "c3" -> 1|>,
6 -> <|"c1" -> Missing["KeyAbsent", 6], "c2" -> 1, "c3" -> 2|>,
7 -> <|"c1" -> Missing["KeyAbsent", 7], "c2" -> 2,
"c3" -> Missing["KeyAbsent", 7]|>,
8 -> <|"c1" -> 1, "c2" -> Missing["KeyAbsent", 8],
"c3" -> Missing["KeyAbsent", 8]|>,
9 -> <|"c1" -> 5, "c2" -> 1, "c3" -> 1|>,
10 -> <|"c1" -> Missing["KeyAbsent", 10], "c2" -> 1,
"c3" -> Missing["KeyAbsent", 10]|>|>


)

The question is, is there a more compact (eg using Query differently) way to achieve the desired output? Also, are there efficiency considerations to take into account? Finally, if d is a subset of a bigger dataset, is there a way to achieve the same output, but this time by operating on the 'bigger' dataset?

• I´m not quite sure if I got your question right, but maybe this helps: d[All, #] [Counts] & /@ {"c1", "c2", "c3"} you get three Datasets, each of them counting the integers per column. Oct 23, 2016 at 19:26
• @mgamer: I am hoping there is a way NOT to have to deal with each column separately because that creates three (or more, depending on the number of columns in the dataset) intermediate datasets which along with the original one and the one which you combine the former three to create the output makes five datasets and I suspect that something like that is overkill for large datasets; please indicate any portion of the question that is unclear and I will be happy to try and clarify it as much as I can. Oct 23, 2016 at 19:39
• o.k. I got your question, but do not have a simple answer yet... Oct 23, 2016 at 19:54
• You can certainly shorten your code to this: KeySort@Transpose@Transpose[d][All, Counts] Oct 23, 2016 at 23:14

KeySort[Transpose[d[Transpose /* Map[Counts], All]]]