I have two dataframes and what I would like to do is to join them per groups/partitions. How can I do it in PySpark?
The first df contains 3 time series identified by an id a timestamp and a value. Noticed that the time series contains some gap (missing days)
The second df contains a time series without gaps
The result I want to reach is
Left join on second df with coalesce will work for this case.
Example:
df.show()
#---+--------+-----+
#tag| ts|value|
#---+--------+-----+
# a|01-01-19| 45|
# a|03-01-19| 89|
# a|04-01-19| 24|
# a|05-01-19| 778|
#---+--------+-----+
df1.show()
#+--------+
#| ts|
#+--------+
#|01-01-19|
#|02-01-19|
#|03-01-19|
#|04-01-19|
#|05-01-19|
#+--------+
df1.alias("t1").join(df.alias("t2"),col("t1.ts")==col("t2.ts"),"left").\
selectExpr("coalesce(t1.ts,t2.ts) as ts","tag","value").\
orderBy("ts").\
show()
#+--------+----+-----+
#| ts| tag|value|
#+--------+----+-----+
#|01-01-19| a| 45|
#|02-01-19|null| null|
#|03-01-19| a| 89|
#|04-01-19| a| 24|
#|05-01-19| a| 778|
#+--------+----+-----+