Styjun

Given two dataframes, which may have completely different schemas, except for a index column (timestamp in this case), such as df1 and df2 below:

df1:

timestamp | length | width
 1 | 10 | 20
 3 | 5 | 3

df2:

timestamp | name | length
 0 | "sample" | 3
 2 | "test" | 6

How can I combine these two dataframes into one that would look something like this:

df3:

timestamp | df1 | df2
 | length | width | name | length 
 0 | null | null | "sample" | 3
 1 | 10 | 20 | null | null
 2 | null | null | "test" | 6
 3 | 5 | 3 | null | null 

I am extremely new to spark, so this might not actually make a lot of sense. But the problem I am trying to solve is: I need to combine these dataframes so that later I can convert each row to a given object. However, they have to be ordered by timestamp, so when I write these objects out, they are in the correct order.

So for example, given the df3 above, I would be able to generate the following list of objects:

objs = [
 ObjectType1(timestamp=0, name="sample", length=3),
 ObjectType2(timestamp=1, length=10, width=20),
 ObjectType1(timestamp=2, name="test", length=6),
 ObjectType2(timestamp=3, length=5, width=3)
]

Perhaps combining the dataframes does not make sense, but how could I sort the dataframes individually and somehow grab the Rows from each one of them ordered by timestamp globally?

P.S.: Note that I repeated length in both dataframes. That was done on purpose to illustrate that they may have columns of same name and type, but represent completely different data, so merging schema is not a possibility.

what you need is a full outer join, possibly renaming one of the columns, something like df1.join(df2.withColumnRenamed("length","length2"), Seq("timestamp"),"full_outer")

See this example, built from yours (just less typing)

// data shaped as your example
case class t1(ts:Int, width:Int,l:Int)
case class t2(ts:Int, width:Int,l:Int)
// create data frames
val df1 = Seq(t1(1,10,20),t1(3,5,3)).toDF
val df2 = Seq(t2(0,"sample",3),t2(2,"test",6)).toDF
df1.join(df2.withColumnRenamed("l","l2"),Seq("ts"),"full_outer").sort("ts").show
+---+-----+----+------+----+ 
| ts|width| l| name| l2|
+---+-----+----+------+----+
| 0| null|null|sample| 3|
| 1| 10| 20| null|null|
| 2| null|null| test| 6|
| 3| 5| 3| null|null|
+---+-----+----+------+----+