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

Improve this question
$\endgroup$

2 Answers 2

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

Improve this answer
$\endgroup$
1
  • $\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
    Commented Dec 18, 2018 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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