Creating a new Dataset column using a map from a second Dataset
Here's a function that finds the last location according to datasetB
:
g[assoc_] := datasetB[Select[#Id <= assoc["Id"]&] /* Last, "Location"]
Using this function, you can augment datasetA
to include location information with:
datasetA[All, Association[#, "Location" -> g[#]]&]
Here's another method I hinted at in my earlier comment, but didn't have time for to post yet. It consists of 2 steps: first I use JoinAcross
to get the known locations across and then I use FoldList
to update the missing locations:
Dataset @ FoldList[
If[ MissingQ[#2["Location"]],
Append[#2, "Location" -> #Location],
#2
]&,
SortBy[#Id &] @ JoinAcross[Normal[datasetA], Normal[datasetB], Key["Id"], "Left"]
]