I have a cost that varies by category and date. I also have the counts of each item type within each category and date. I want to distribute the cost to each of the item types. I'm trying to use GroupBy, and Normalize to get the percentage distribution by item type. My plan is to then multiply these percentages by the cost for each category and date.

x1 = {
     <|"cat" -> "a", "type" -> "abc1", "date" -> {2013, 1}, "count" -> 10|>,
     <|"cat" -> "a", "type" -> "abc2", "date" -> {2013, 1}, "count" -> 20|>,
     <|"cat" -> "a", "type" -> "abc3", "date" -> {2013, 1}, "count" -> 30|>,
     <|"cat" -> "a", "type" -> "abc1", "date" -> {2014, 1}, "count" -> 40|>,
     <|"cat" -> "a", "type" -> "abc2", "date" -> {2014, 1}, "count" -> 50|>,
     <|"cat" -> "a", "type" -> "abc1", "date" -> {2015, 1}, "count" -> 13|>,
     <|"cat" -> "b", "type" -> "abc1", "date" -> {2013, 1}, "count" -> 60|>,
     <|"cat" -> "b", "type" -> "abc1", "date" -> {2014, 1}, "count" -> 70|>,
     <|"cat" -> "b", "type" -> "abc2", "date" -> {2013, 1}, "count" -> 75|>
x2 = GroupBy[x1, {#cat, #date} &, Normalize[#, Total] &]

This results in the following:

<|{"a", {2013, 1}} -> 
 {<|"cat" -> 1/3, "type" -> ("abc1")/("abc1" + "abc2" + "abc3"), "date" -> {1/3, 1/3}, "count" -> 1/6|>, 
  <|"cat" -> 1/3, "type" -> ("abc2")/("abc1" + "abc2" + "abc3"), "date" -> {1/3, 1/3}, "count" -> 1/3|>, 
  <|"cat" -> 1/3, "type" -> ("abc3")/("abc1" + "abc2" + "abc3"), "date" -> {1/3, 1/3}, "count" -> 1/2|>}, 
 {"a", {2014, 1}} -> 
 {<|"cat" -> 1/2, "type" -> ("abc1")/("abc1" + "abc2"), "date" -> {1/2, 1/2}, "count" -> 4/9|>, 
  <|"cat" -> 1/2, "type" -> ("abc2")/("abc1" + "abc2"), "date" -> {1/2, 1/2}, "count" -> 5/9|>},
 {"a", {2015, 1}} -> 
 {<|"cat" -> 1, "type" -> 1, "date" -> {1, 1}, "count" -> 1|>}, 
 {"b", {2013, 1}} -> 
 {<|"cat" -> 1/2, "type" -> ("abc1")/("abc1" + "abc2"), "date" -> {1/2,1/2}, "count" -> 4/9|>, 
  <|"cat" -> 1/2, "type" -> ("abc2")/("abc1" + "abc2"), "date" -> {1/2,1/2}, "count" -> 5/9|>}, 
 {"b", {2014, 1}} -> {<|"cat" -> 1, "type" -> 1,"date" -> {1, 1}, "count" -> 1|>}|>

Which is a mess. It does calculate the correct share for each type (in the count key), but now I'd have to somehow pull this apart and put it back together properly.

Originally I didn't use associations, and did a lot of joining. Using associations seems like a better idea but this level of complication is daunting. I'm afraid that when I pull it apart and put it back together the pieces may not be in the same order.

Is there a better way to approach this? Or, is there a reasonable way to simplify the result?


Let's start with a function cost that will tell us the total cost for each category/date pair.

cost[{cat_, date_}] := 100.

For illustration purposes, this function has been defined trivially -- the real cost function will have to look up its values somewhere.

Using Query

Let's define total to be a map from a category/date pair to the total count for that pair:

total = x1 // Query[GroupBy[{#cat, #date}&], Total, "count"]

(* <| {a,{2013,1}} -> 60
    , {a,{2014,1}} -> 90
    , {a,{2015,1}} -> 13
    , {b,{2013,1}} -> 135
    , {b,{2014,1}} -> 70
    |> *)

Now we can generate adjusted source data so that each entry has a new cost property containing the prorated cost of that entry:

x1 // Query[All, <| #, "cost" -> cost[{#cat, #date}] #count / total[{#cat, #date}] |>&]

result screenshot

Without Using Query

The previous section uses Query, but it does not depend upon any particularly advanced functionality. We can perform the same operations without using Query at all:

total = GroupBy[x1, {#cat, #date}& -> (#count&), Total];

<| #, "cost" -> cost[{#cat, #date}] #count / total[{#cat, #date}] |>& /@ x1
(* same result as before *)

Sidebar: How does the Query version work?

Let's look at how Query is used above. First:

x1 // Query[GroupBy[{#cat, #date}&], Total, "count"]

Queries specify an operator to apply to each element of each level of an expression. The first operator is applied to level zero (the whole expression), the next to level one, etc. Each level is acted upon twice: once while descending into the expression and a second time when ascending back out to top-level. It is beyond the scope of this response to describe these phases in detail. However, here is the sequence of events for this particular query:

  1. Level 0, descending The GroupBy operator performs the necessary grouping and splits the original data into groups keyed by the "cat" and "date" keys. GroupBy is documented as being a special operator in that it inserts a new level into the data that did not exist before. While we started with a list of associations on level 0, after the grouping each level 1 element is a list of associations for a particular group.
  2. Level 1, descending The Total operator is an ascending operator, so in a descending context it is treated as All, i.e. Identity. This means that each group's list of associations will be left untouched as we descend.
  3. Level 2, descending The "count" operator will extract the count property from each of the associations. Thus, each association will be transformed into a count. This is the last operator, so now we switch to the ascending phase.
  4. Level 2, ascending The "count" operator is a descending operator, so it is treated as All in the ascending phase. Each count undergoes no further transformation.
  5. Level 1, ascending The Total operator now gets its chance to act since it is an ascending operator. It is presented with a list of the counts generated by step 4, returning a single number representing the sum of those counts.
  6. Level 0, ascending The GroupBy operator is a descending operator so it is treated as All in the ascending phase. This means that association containing counts keyed by groups will be returned unchanged. This association is the final result of the first query.

The second query is essentially of the form:

Query[All, f]

Short-cutting the description a bit, this applies the All operator to level 0 and the f operator to level 1. Since All essentially means Identity, the query is equivalent to saying Map[f]. In fact, we can see this by applying Normal to the query:

Query[All, f] // Normal
(* Map[f] *)

This Normal trick works for any query.

Tools described in Visualizing Type System Operations can be helpful in developing an intuition for dataset and query operations.

  • $\begingroup$ This works well. I had to make a small change since my "costs" are another association, so I had to do a preliminary JoinAccross to get them in a position for the mulitplication. Thanks. $\endgroup$ – Mitchell Kaplan Oct 28 '15 at 18:32
  • $\begingroup$ I'm very fuzzy on how Query works. Could you expand upon how it's used in you calculation of total? Maybe break it down into pieces. For example, could the same thing be accomplished without using Query? I have read through the documentation but it's not helping me a whole lot. I'm hoping for that "aha" moment. $\endgroup$ – Mitchell Kaplan Oct 28 '15 at 20:40
  • $\begingroup$ I have added a non-query variant and a section describing how the query-based version works. $\endgroup$ – WReach Oct 28 '15 at 22:17
  • 2
    $\begingroup$ Thanks for this! I'll mull over this the next time I have to deal with an Association[]. $\endgroup$ – J. M.'s ennui Oct 29 '15 at 0:03
  • $\begingroup$ All of that detail was extremely helpful - thanks! $\endgroup$ – Mitchell Kaplan Oct 29 '15 at 18:52

I am a relative novice with respect to manipulating Association[]/Dataset[] objects, so there might well be a better way than what I present below:

x1 = Dataset[x1];

treatData = Which[VectorQ[#, NumericQ], 100 Normalize[#, Total],
                  Length[#] == 1, First[#], True, #] &;

x1[GroupBy[{#cat &, #date &}], All][Values, Values,
   Merge[Composition[treatData, Union]]][Catenate,
   Map[KeyMap[# /. "count" -> "percentage" &]]]


  • $\begingroup$ Well, it works, but it's going to take me some time to understand it. Thanks! $\endgroup$ – Mitchell Kaplan Oct 27 '15 at 15:50
  • $\begingroup$ That you've said those words indicates to me that it can be expressed way better than I did. Wait for a bit, maybe someone more well-versed might come along. $\endgroup$ – J. M.'s ennui Oct 27 '15 at 15:51
  • $\begingroup$ I tried this on my actual data, which as you would imagine is much larger and not sorted the same way. I found that it sorts type and percentage both according to their own values. Thus the types no longer match the percentages. You can see this if you reverse abc1 and abc2 in rows 1 & 3. That is, abc1 will have count=30. Even so, I learned a number of things from your solution which will help me to put something together that will work for my more complicated data. Thanks very much. $\endgroup$ – Mitchell Kaplan Oct 28 '15 at 14:10
  • $\begingroup$ Yes, I had exploited the fact that the data was almost, but not quite sorted. I'll have to think about the case you mention. In any event, I'm glad this was of some use. $\endgroup$ – J. M.'s ennui Oct 28 '15 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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