Q: Join two dataframes without repeating values in spark I am creating a follow up question from this previous question I asked. For context, the solution proposed by blackbishop works, but i am realising now that it has a unexpected side-effect that i don't desire. (see the link to reproduce the problem and the solution).
problem statement: given several dataframes, how can i join them but avoid the cross join repetition of the columns?
solution proposed: join on id and row number.
When trying to reproduce the solution though, i realised that i do not get the same results as the solution proposed.
This is the table that I get
+---+-----------------+---------------------+-------------+
|id |name             |dob                  |country      |
+---+-----------------+---------------------+-------------+
|1  |{value -> Robert}|{value -> 21-04-1988}|{value -> IT}|
|1  |{value -> bob}   |null                 |{value -> DE}|
|2  |null             |null                 |{value -> ES}|
|2  |{value -> Mary}  |{value -> null}      |{value -> FR}|
+---+-----------------+---------------------+-------------+

The problem there is that, for id = 2 for example, I get the null values first for name and dob. What I would like, is a way to systematically get the first row of every id to always display non null values for each column (like shown by blackbishop).
#+---+-----------------+---------------------+-------------+
#|id |name             |dob                  |country      |
#+---+-----------------+---------------------+-------------+
#|1  |{value -> bob}   |{value -> 21-04-1988}|{value -> IT}|
#|1  |{value -> Robert}|null                 |{value -> DE}|
#|2  |{value -> Mary}  |{value -> null}      |{value -> FR}|
#|2  |null             |null                 |{value -> ES}|
#+---+-----------------+---------------------+-------------+

I tried to edit the windowing to the following but it doesn't change the result:
w = Window.partitionBy("id") # change this if you have some column to use for ordering

name = name.withColumn("id2", F.row_number().over(w.orderBy("name.value")))
dob = dob.withColumn("id2", F.row_number().over(w.orderBy("dob.value")))
country = country.withColumn("id2", F.row_number().over(w.orderBy("country.value")))

result = (name.join(dob, ["id", "id2"], "full")
          .join(country, ["id", "id2"], "full")
          .drop("id2")
          )


A: Silly me! It was much simpler than I thought. I just need to add an order by clause before dropping the id2.
Here's the solution:
name = name.withColumn("id2", F.row_number().over(w.orderBy("name.value")))
dob = dob.withColumn("id2", F.row_number().over(w.orderBy("dob.value")))
country = country.withColumn("id2", F.row_number().over(w.orderBy("country.value")))

result = (name.join(dob, ["id", "id2"], "full")
          .join(country, ["id", "id2"], "full")
          .orderBy("id", "id2")
).drop("id2")

