4
$\begingroup$

I have two datasets

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}, 
 {"C11", SQLDateTime[{2013, 3, 2, 6, 40, 0.}], 128},
 {"C12", SQLDateTime[{2012, 10, 10, 0, 0, 0.}], 10}}

and I combine it via

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

Now, I want to replace the unmatched B values with the last (according to date) occurring B value of the matching ID. Eg. in lines 2 and 3: 315 for B, in line 5: 128 and lines 7-9: 10.

The result should look like this:

test

I tried something like this, where I first replaced the Missing["Unmatched"]->0 and then

Module[{prev = #"B" &}, ds4[All, {If[#"B" == 0, <|#, "B" -> #"B" + prev|>, #], 
     prev = #"B"}[[1]] &]]

But it doesn't yield the desired result. It only replaces the first occurrence. Any ideas?

$\endgroup$
  • $\begingroup$ Could you re-upload the image? Imgur reports broken file. $\endgroup$ – ercegovac Oct 11 '17 at 9:52
  • $\begingroup$ Thank you sebhofer, for the edit! $\endgroup$ – Lea Oct 11 '17 at 15:11
3
$\begingroup$

I like your idea! You can do it in the following way

Module[{prev = Missing["Unmatched"]}, 
 ds4[All, <|"ID" -> "ID", "Date" -> "Date", "A" -> "A", 
   "B" -> (If[#"B" === Missing["Unmatched"], prev, prev = #"B"] &)|>]]

or in a slightly modified version, which I personally like better (thanks to WReach to suggesting a simpler form of the query)

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

Also, I should point out that I'm on Mathematica version 10.0.2, that's why my query has the current form. From this answer I gather, that it could be rewritten in more convenient ways in other versions.

| improve this answer | |
$\endgroup$
  • 1
    $\begingroup$ +1 The query in your second alternative can be expressed in a simpler form: ds4[All, {"B" -> rfunc}]. $\endgroup$ – WReach Oct 11 '17 at 14:00
  • $\begingroup$ @WReach Great, thanks for this suggestion! This was bugging me for quite a while actually! $\endgroup$ – sebhofer Oct 11 '17 at 14:37

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.