Updating a data table

Question

1. Example data

I have a data table organized as an Association of Associations:

table =
  <|"fra" -> <|"19" -> 2359, "20" -> 2181, "21" -> 2321, "22" -> 2378|>, 
   "gre" -> <|"19" -> 184, "20" -> 167, "21" -> 181, "22" -> 192|>, 
   "ita" -> <|"19" -> 1729, "20" -> 1574, "21" -> 1684, "22" -> 1745|>, 
   "por" -> <|"19" -> 200, "20" -> 184, "21" -> 194, "22" -> 207|>, 
   "spa" -> <|"19" -> 1193, "20" -> 1058, "21" -> 1117, "22" -> 1177|>|>;

table // Dataset

For this table I import monthly updates which have the following form:

update =
  {<|"land" -> "gre", "22" -> 192, "23" -> 200|>,
  <|"land" -> "alb", "22" -> Missing[_], "23" -> 55|>,
  <|"land" -> "por", "22" -> 211, "23" -> 210|>};

update // Dataset

Sometimes values of the preceding year are revised. This is the case with por. The old value of 207 must be overwritten with 211. It might also happen that new countries are added. This is the case with alb.

2. Current solution

My current solution looks like this:

lands = Union[Keys @ table, update[[All, 1]]];

years = Union[Keys @ table[[1]], Rest @ Keys @ update[[1]]];

missing = 
 Thread[lands -> Map[AssociationThread[years -> #] &, Table[Missing[], Length @ lands]]];

result = 
  Merge[{missing, Normal @ table, Normal @ GroupBy[update, First -> Rest, Last]}, Association];

3. Expected result:

result // Dataset

4. Question

The need for the extra missing table looks somehow amateurish to me, and I feel that a solution using f.e. KeyUnion or JoinAcross would be more appropriate. How can my code be improved?

Answer 1

Update: Using @eldo's suggestion in comments to define a function:

ClearAll[joinAssociations]
joinAssociations[tbl_, upd_] := Module[{key = Keys[upd][[1, 1]]},
  KeySort @ GroupBy[#, First -> Rest, Last] & @
   JoinAcross[KeyValueMap[<|Prepend[key -> #]@#2|> &]@tbl, upd, 
     key, "Outer", KeyCollisionFunction -> Right]]

Dataset @ joinAssociations[table, update]

---

Original answer:

Process table to give it the same shape as update:

table2 = KeyValueMap[<|Prepend["land" -> #]@#2|> &] @ table;

Use JoinAcross to combine update and table2:

ds = Dataset @ JoinAcross[update, table2, "land", "Outer"]

Re-shape ds into the desired form:

ds[SortBy[#] /* GroupBy[#] /* Transpose, Map[KeySort @* KeyDrop[#]]] & @ "land"

Note: We can replace JoinAcross[update,table2,"land","Outer"] with

JoinAcross[table2, update, "land", "Outer", KeyCollisionFunction -> Right]

to get the same result.