5
$\begingroup$

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

enter image description here

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.

enter image description here

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 ;-)

$\endgroup$
2
$\begingroup$

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

enter image description here

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
$\endgroup$
  • $\begingroup$ Thank you, I'm actually trying to implement your solution and will edit this comment, asap. $\endgroup$ – Lea Dec 19 '18 at 9:55
  • $\begingroup$ 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! $\endgroup$ – Lea Dec 19 '18 at 10:11
  • $\begingroup$ You're welcome. Also I modified the second solution. $\endgroup$ – SquareOne Dec 19 '18 at 10:55
1
$\begingroup$

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

enter image description here

$\endgroup$
  • $\begingroup$ 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? $\endgroup$ – Lea Dec 18 '18 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.