I have a Dataset

Masterds =  Dataset@*Map[AssociationThread[{"ID", "Appearance"} -> #] &]@
{{"C01", 0}, {"C02", 0}, {"C03", 0}, {"C04", 0}, {"C05", 0},
{"C06",0}, {"C07", 0}, {"C08", 0}, {"C09", 0}, {"C10", 0}, 
{"C11",0}, {"C12", 0}}

where I want to fill the "Appearance" value with the number of occurrences of the "ID" in the following Datasets (the number in the Dataset-name indicates the number of occurrences):

ds1 = Dataset@*Map[AssociationThread[{"ID", "Val"} -> #] &]@
{{"C01", 11.2}, {"C05", 2.0}, {"C09", 22.3}, {"C04", 0.1}};
ds2 = Dataset@*Map[AssociationThread[{"ID", "Val"} -> #] &]@
{{"C02", 9.4}, {"C02",29.3}, {"C11", 2.7}, {"C11", 18.1},
{"C08", 0.}, {"C08", 13.2}, {"C06", 23.8}, {"C06", 1.2}};
ds3 = Dataset@*Map[AssociationThread[{"ID", "Val"} -> #] &]@
{{"C03", 2.5}, {"C03", 13.2}, {"C03", 23.0}, {"C10", 8.2},
{"C10", 3.1}, {"C10", 3.8}, {"C12", 11.1}, {"C12", 12.2}, {"C12", 3.4}};
ds4 = Dataset@*Map[AssociationThread[{"ID", "Val"} -> #] &]@
{{"C07", 12.5}, {"C07", 3.2}, {"C07", 3.0}, {"C07", 28.2}};

The "Masterds" should look in the end like this:

enter image description here

I tried approaches with MemberQ and StringMatchQ but I didnt't find a working solution. And I have no idea, what an elegant approach could look like, maybe it includes a boolean... Again, I just can hope for help from this very useful forum.

  • $\begingroup$ Do you need those Datasets? They always prompt errors I don't understand: counts = Counts[Normal@Join[ds1, ds2, ds3, ds4][;; , "ID"]]; Dataset@Normal@Masterds[;; , <|#, "Appearance" -> counts[#["ID"]]|> &] $\endgroup$
    – Kuba
    Mar 4 '15 at 12:56

As others have noted, there is no need to pre-allocate a dataset for Masterds. We can simply assign it the result of one of the following expressions.

The desired result can be generated like this:

Join[ds1, ds2, ds3, ds4][
  GroupBy["ID"] /* KeySortBy[Identity] /* Values 
, <| "ID" -> First /* "ID", "Appearance" -> Length |>

dataset screenshot

This expression performs the following steps:

  1. Joins all of the datasets into a single dataset (Join).
  2. Groups the associations together by their IDs (GroupBy).
  3. Sorts the groups (KeySortBy).
  4. Discards the grouping keys (Values).
  5. Generates the desired output associations by using the first (shared) "ID" in each group along with the group length (<| ... |>).

One might think to use KeySort in place of KeySortBy[Identity], but unfortunately KeySort is absent from the list of descending operators even though KeySortBy is present on that list.

Simpler Alternative?

If we are willing to compromise a little on the structure of the result, the data can be obtained by a simpler query:

Join[ds1, ds2, ds3, ds4][GroupBy["ID"] /* KeySort, Length]

dataset screenshot

  • $\begingroup$ I get the same result with KeySort. So is the consequence of descending only on performance? $\endgroup$ Mar 4 '15 at 18:49
  • $\begingroup$ @alancalvitti I am using 10.0.2. In my first alternative I get an invalid result (with Missing elements) if I use KeySort. The query plans are different, reflecting the fact that KeySort is ascending and KeySortBy is descending. In my second alternative, it does not matter whether the operator is ascending or descending, so I just use the shorter-named ascending KeySort. $\endgroup$
    – WReach
    Mar 4 '15 at 19:29

From a functional view, it's not desirable to prepopulate Masterds - the tally can be obtained by querying the input:

ds = <|ds1 -> {{"C01", 11.2}, {"C05", 2.}, {"C09", 22.3}, {"C04", 0.1}}, 
 ds2 -> {{"C02", 9.4}, {"C02", 29.3}, {"C11", 2.7}, {"C11", 
    18.1}, {"C08", 0.}, {"C08", 13.2}, {"C06", 23.8}, {"C06", 1.2}}, 
 ds3 -> {{"C03", 2.5}, {"C03", 13.2}, {"C03", 23.}, {"C10", 
    8.2}, {"C10", 3.1}, {"C10", 3.8}, {"C12", 11.1}, {"C12", 
    12.2}, {"C12", 3.4}}, 
 ds4 -> {{"C07", 12.5}, {"C07", 3.2}, {"C07", 3.}, {"C07", 28.2}}|> // Dataset // Query[All, All, AssociationThread[{"ID", "Val"} -> #] &];

enter image description here


ds[Catenate, Tally, "ID"][All,AssociationThread[{"ID", "Appearance"} -> #] &][SortBy["ID"]]

enter image description here


As per WReach's second example if only the ID x tally values are needed, the above reduces to:

ds[Catenate, Tally, "ID"][SortBy[First]]

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.