How to insert zero counts uniformly with Dataset Counts query

Question

Missing[] is generated when an association does not contain the specified key.

data = {<|"a" -> 1, "b" -> 2|>, <|"b" -> 3, "c" -> 4|>} // Dataset

This situation occurs when using Counts where there are zero instances of a value in some records, as illustrated in this result of a query that I won't bother copying here:

This is Not to be confused with counting missing values, eg, the 263 Missing[] "age" values in Titanic data, and the "" keys above).

How to insert key-value pairs representing zero counts to obtain uniform keys in the {Association__}?.

The inserted keys should ideally appear in the same order as they are by the built-in Datset Format functionality (not contained in the Normal form) .

Desired output for data is:

{<|"a" -> 1, "b" -> 2, "c" -> 0|>, <|"a" -> 0, "b" -> 3, 
   "c" -> 4|>} // Dataset

EDIT:

It seems KeyUnion performs exactly this function:

data[KeyUnion] // Normal

(*    {<|"a" -> 1, "b" -> 2, 
      "c" -> Missing["KeyAbsent", "c"]|>, <|"a" -> 
       Missing["KeyAbsent", "a"], "b" -> 3, "c" -> 4|>} *)

However, it seems immune to ReplaceAll:

data[KeyUnion, All, Replace[#, Missing[__] :> 0] &] // Normal 

(* {<|"a" -> 1, "b" -> 2, 
  "c" -> Missing["KeyAbsent", "c"]|>, <|"a" -> 
   Missing["KeyAbsent", "a"], "b" -> 3, "c" -> 4|>} *)

EDIT 2:

Elaborating on WReach's solution using KeyUnion composed with the subquery.

Since the example data is actually structured as key-value pairs, it seems appropriate to extend this question as opposed to asking a new one, though the focus now is on KeyUnion, not the replacement for the Missing data.

raw2 = <|a -> {"Copy", "Copy", "Copy", "", "", "", "Copy"}, 
    b -> { "Paste", "", "Paste", "Paste", "", "Paste", "Paste"}, 
    c -> {"Copy", "Paste", "Copy", "Copy", "", "", "Paste"}, 
    d -> {"Paste", "", "", "Paste", "", "Paste", "Paste"}|> // 
   Dataset;

Even though Dimensions are the same as raw in his answer, and also, raw2[All, Counts] // Normal gives:

<|a -> <|"Copy" -> 4, "" -> 3|>, b -> <|"Paste" -> 5, "" -> 2|>, 
 c -> <|"Copy" -> 3, "Paste" -> 2, "" -> 2|>, 
 d -> <|"Paste" -> 4, "" -> 3|>|>

It's not clear to me at what level to apply KeyUnion, eg:

raw2[All, KeyUnion, Counts] // Normal

(* <|a -> Missing["Failed"], b -> Missing["Failed"], 
 c -> Missing["Failed"], d -> Missing["Failed"]|> *)

Answer 1

One way to achieve this is to apply an ascending function that appends each association onto a suitably defaulted prototype association, like this:

data[All, <| "a" -> 0, "b" -> 0, "c" -> 0, # |> &]

Why didn't KeyUnion work?

The question observes that the following expression does not work:

data[KeyUnion, All, Replace[#, Missing[__] :> 0] &]

One reason for this is because both KeyUnion and the Replace[...]& function are ascending operators. Ascending operators are applied from the deepest level out. Thus, the Replace[...]& is not called on the absent keys because they have not yet been added the outer KeyUnion ascending function, which is called afterwards. To make this work, we would have to run successive queries:

data[KeyUnion][All, All, # /. _Missing -> 0 &]

This has the disadvantage that it constructs an intermediate Dataset. Note also that the new expression applies the replacement function to the individual association values, not to each association as a whole. This is necessary because associations, being atomic, are opaque to Replace et al. This is a second reason why the exhibited expression could not work.

The "replace" strategy can be made to work in a single query pass by explicitly specifying the key names:

data[All, {"a", "b", "c"}, # /. _Missing -> 0 &]

What if the set of keys is variable?

If we do not know the set of keys ahead of time, we can query for them using:

data[Union @@ # &, Keys]
(* {"a", "b", "c"} *)

or

data[Merge[First] /* Keys]
(* {"a", "b", "c"} *)

Of course, this will mean another pass through the dataset before the main query.

Working from the raw data

The question says to assume that the Counts query has already been run but, just for fun, here is an example of how the Counts could be corrected as part of that initial query:

raw = { {"Copy","Copy","Copy","","","","Copy"}
      , {"Paste","","Paste","Paste","","Paste","Paste"}
      , {"Copy","Paste","Copy","Copy","","","Paste"}
      , {"Paste","","","Paste","","Paste","Paste"}
      } // Dataset;

raw[KeyUnion /* Query[All, All, # /. _Missing -> 0 &], Counts]

Answer 2

I believe the problem with pattern matching has to do with the atomistic nature of an association.

data = {<|"a" -> 1, "b" -> 2|>, <|"b" -> 3, "c" -> 4|>};
Map[# /. _Missing -> 0 &, KeyUnion[data], {2}] // Dataset