Fill Dataset value with result of queries on other Datasets

Question

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:

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.

Answer 1

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

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]

Answer 2

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"} -> #] &];

Then:

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

EDIT

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