1
$\begingroup$

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?

Improve this question
$\endgroup$
4
  • $\begingroup$ 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. $\endgroup$
    – mgamer
    Commented Oct 23, 2016 at 19:26
  • $\begingroup$ @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. $\endgroup$
    – user42582
    Commented Oct 23, 2016 at 19:39
  • $\begingroup$ o.k. I got your question, but do not have a simple answer yet... $\endgroup$
    – mgamer
    Commented Oct 23, 2016 at 19:54
  • 2
    $\begingroup$ You can certainly shorten your code to this: KeySort@Transpose@Transpose[d][All, Counts] $\endgroup$
    – dan7geo
    Commented Oct 23, 2016 at 23:14

1 Answer 1

Reset to default
3
$\begingroup$ 
KeySort[Transpose[d[Transpose /* Map[Counts], All]]]

Improve this answer
$\endgroup$

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.