1
$\begingroup$

I have two data sets. I would like to join them left_join function of R this

d1={{1, 2.42}, {2, 1.58}, {3, 2.54}, {4, 1.48}, {5, 0.877}, {6, 
  2.65}, {7, 1.82}, {8, 2.22}, {9, 2.62}, {10, 1.4}, {11, 2.16}, {12, 
  1.91}, {13, 1.45}, {14, 2.02}, {15, 1.59}, {16, 1.5}, {17, 
  1.55}, {18, 1.7}, {19, 1.95}, {20, 1.53}, {21, 1.5}, {22, 
  1.94}, {23, 1.53}, {24, 1.11}, {25, 2.35}, {26, 1.35}, {27, 
  1.98}, {28, 1.81}, {30, 1.9}, {31, 1.81}, {32, 1.68}, {33, 
  1.54}, {34, 1.4}, {35, 2.2}, {36, 3.28}, {37, 2.05}, {61, 
  2.25}, {63, 3.83}, {64, 3.4}, {66, 2.41}, {69, 2.18}, {71, 
  3.64}, {75, 2.13}, {86, 4.53}, {87, 2.77}, {88, 2.97}, {89, 
  1.95}, {90, 0.51}, {91, 3.86}, {92, 3.96}, {93, 3.95}, {94, 
  2.38}, {95, 4.25}, {96, 1.48}, {97, 4.03}, {98, 4.45}, {99, 
  2.54}, {100, 2.56}, {101, 1.83}, {102, 1.69}, {103, 2.44}, {104, 
  2.65}, {105, 1.72}, {106, 1.49}, {107, 1.73}, {108, 1.84}, {109, 
  1.61}, {110, 1.42}, {111, 2.78}, {112, 1.36}, {113, 1.77}, {114, 
  1.22}, {115, 2.01}, {116, 1.65}, {117, 2.71}, {118, 1.72}, {119, 
  2.52}, {120, 1.87}, {121, 2.66}, {122, 2.66}, {123, 2.43}, {124, 
  2.24}, {125, 3.12}, {126, 2.64}, {127, 5.76}, {128, 1.87}, {129, 
  4.4}, {130, 4.73}, {131, 1.82}, {132, 2.65}, {133, 5.22}, {134, 
  2.6}, {135, 4}, {136, 3.42}, {137, 3.08}, {138, 1.84}, {139, 
  1.6}, {140, 2.12}, {141, 1.58}, {142, 1.98}, {143, 2.25}, {144, 
  3.2}, {145, 1.82}, {146, 2.16}, {147, 5.69}, {148, 2.32}};
d2={{1, 4.24}, {2, 3.16}, {3, 3.86}, {4, 2.67}, {5, 2.64}, {6, 2.82}, {7,
   2.38}, {8, 2.93}, {9, 2.38}, {10, 2.81}, {11, 4.21}, {12, 
  3.96}, {14, 3.27}, {15, 3.77}, {16, 3.86}, {17, 3.41}, {18, 
  3.4}, {19, 4.11}, {20, 2.73}, {21, 2.64}, {22, 4.05}, {23, 
  3.59}, {24, 2.25}, {25, 3.23}, {26, 2.96}, {27, 3.07}, {28, 
  2.81}, {30, 2.96}, {31, 2.8}, {32, 4.59}, {33, 2.9}, {34, 
  2.65}, {35, 3.51}, {36, 4.85}, {37, 3.58}, {61, 4.02}, {63, 
  6.39}, {64, 6}, {66, 4.25}, {69, 5.41}, {71, 4.74}, {75, 3.83}, {86,
   6.09}, {87, 6.66}, {88, 4.47}, {89, 3.07}, {90, 2.23}, {91, 
  7.31}, {92, 6.21}, {93, 7.7}, {94, 3.68}, {95, 6.45}, {96, 
  3.85}, {97, 6.3}, {98, 5.05}, {99, 3.9}, {100, 4.86}, {126, 
  5.89}, {127, 9.31}, {128, 3.8}, {129, 7.21}, {130, 7.42}, {131, 
  4.06}, {132, 4.48}, {133, 7.06}, {134, 5}, {135, 7.9}, {136, 
  6}, {137, 5.6}};

Here is my solution. It is a little bit ugly. Is there a nicer way to do this?

d22 = (Join @@ {d1, 
       Table[If[MemberQ[d1[[All, 1]], i] == True, 
         Nothing, {i, Missing}], {i, 148}]} // Sort)[[All, 2]];

d33 = (Join @@ {d2, 
       Table[If[MemberQ[d2[[All, 1]], i] == True, 
         Nothing, {i, Missing}], {i, 148}]} // Sort)[[All, 2]];

(mm = Transpose@{d22, d33})
$\endgroup$

2 Answers 2

5
$\begingroup$

You can use ResourceFunction["TableSet"].

With ds1 and ds2 has in OP then

tableset = ResourceFunction["TableSet"];
tsd1 = tableset[{"pos", "val1"}, d1]
tsd2 = tableset[{"pos", "val2"}, d2]

enter image description here

leftjoined = JoinAcross[tsd1, tsd2, "pos", "Left"]

enter image description here

Values with

leftjoined["Data"] // Short
{{1,2.42,4.24},{2,1.58,3.16},<<102>>,{147,5.69,Null},{148,2.32,Null}}

or

TableForm[leftjoined]

enter image description here

Hope this helps.

$\endgroup$
2
  • 1
    $\begingroup$ Is that like a dataframe but with better performance than Dataset? I've been looking for something like this for a while. $\endgroup$
    – Szabolcs
    Sep 16, 2022 at 16:43
  • $\begingroup$ @Szabolcs Yes. It has a much smaller memory footprint than Dataset. There is a comparison in the docs. $\endgroup$
    – Edmund
    Sep 17, 2022 at 0:51
3
$\begingroup$

I don't know R, but if this is a typical left join, you could use JoinAcross. First we need to add some structure to the data. It looks like the first element is being used as a key.

assoc1 = AssociationThread[{"key", "value1"} -> #] & /@ d1;
assoc2 = AssociationThread[{"key", "value2"} -> #] & /@ d2;

As an example, the first element of assoc1 is

<|"key" -> 1, "value1" -> 2.42|>

Now we can use JoinAcross:

joined = JoinAcross[assoc1, assoc2, "key", "Left"]

As an example, the first element of joined is

<|"key" -> 1, "value1" -> 2.42, "value2" -> 4.24|>

An example where the key was missint from assoc2 is

<|"key" -> 13, "value1" -> 1.45, "value2" -> Missing["Unmatched"]|>

If you want to destructure this to get back to simple lists without keys (like your mm), you could do this:

Values[joined][[All, 2 ;;]]
$\endgroup$

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.