# Replacing “Unmatched” with value from other row - under condition

I have

ds = Dataset@*
Map[AssociationThread[{"ID", "Date", "A"} -> #] &]@{{"C12",
SQLDateTime[{2012, 12, 11, 0, 0, 0.}], 10}, {"C12",
SQLDateTime[{2015, 2, 19, 19, 30, 0.}], 9},
{"C12", SQLDateTime[{2013, 1, 22, 0, 0, 0.}], 12}, {"C10",
SQLDateTime[{2013, 1, 28, 9, 25, 0.}], 15}, {"C10",
SQLDateTime[{2014, 3, 21, 20, 0, 0.}], 12}, {"C11",
SQLDateTime[{2014, 8, 27, 6, 40, 0.}], 8}}


and

ds2 = Dataset@*
Map[AssociationThread[{"ID", "Date", "B"} -> #] &]@{{"C10",
SQLDateTime[{2013, 1, 28, 0, 25, 0.}], 315},
{"C12", SQLDateTime[{2012, 10, 10, 0, 0, 0.}], 10}}


I combine them via

ds3 = SortBy["ID"]@JoinAcross[ds, ds2, {"ID", "Date"}, "Outer"]


Now, I replace the Missing["Unmatched"] values B by the values from the lines above.

Module[{prev = Missing["Unmatched"]},
rfunc[Missing["Unmatched"]] := prev;
rfunc[b_] := prev = b;
ds3[All, {"B" -> rfunc}]]


But I have the problem, that I only want to do this in case, the ID is the same as in the line above (line with same ID but value for B existing). This means in line 4 (ID == C11) I'd like to have still an "Unmatched" instead of the 315, since the row above is corresponding to ID C10 instead of C11.

I would be very happy, if anybody could give me a hint. All I could think about so far didn't work and I have to present my data tomorrow ;-)

Here is an ugly approach i use sometimes ...

(and I reuse your rfunc idea)

rf[True, Missing["Unmatched"]] := prev;
rf[_, b_] := prev = b;

previd = "";
prev = -999;
ds3[All, (<|#, val = rf[#ID == previd, #B]; previd = #ID;
"B" -> val|>) &]


Or try this not much more satisfying approach (the idea is to extract/work on the column then replace it in the dataset) :

foo[b_, Missing["Unmatched"]] := b;
foo[_, c_] := c;

ds3[All, {"ID", "B"}] // Values // Normal //
GroupBy[#, First -> Last, FoldList[foo]] & // Values //
Flatten // Map[<|"B" -> #|> &, #] & //
Join @@@ Transpose[{Normal@ds3, #}] & // Dataset

• Thank you, I'm actually trying to implement your solution and will edit this comment, asap. – Lea Dec 19 '18 at 9:55
• The first solution works for me, the second I'll try after my talk this afternoon or maybe only in the new year. But this answer safed my day! – Lea Dec 19 '18 at 10:11
• You're welcome. Also I modified the second solution. – SquareOne Dec 19 '18 at 10:55

Define a function lookup to look up the B value from ds2 via the ID key:

lookup[x_] := Query[SelectFirst[#ID == x &], "B"]@ds2


then replace the missing values,

SortBy["ID"]@ds3[All, <|#,"B" -> Replace[#B, Missing["Unmatched"] -> lookup[#ID]]|> &]


or just lookup the B value in all rows of ds3 from ds2,

SortBy["ID"]@ds3[All, <|#, "B" -> lookup[#ID]|> &]


• Thank you so much! This is a new idea which helps. But I have a further problem, since for my real datasets the SelectFirst in the query does not work any time. Sometimes I have more than one case per ID - some with A missing, some with B missing. Maybe you have a further idea? – Lea Dec 18 '18 at 22:42