Scala Spark - Map function referencing another dataframe

Question

I have two dataframes:

df1:

+---+------+----+
| id|weight|time|
+---+------+----+
|  A|   0.1|   1|
|  A|   0.2|   2|
|  A|   0.3|   4|
|  A|   0.4|   5|
|  B|   0.5|   1|
|  B|   0.7|   3|
|  B|   0.8|   6|
|  B|   0.9|   7|
|  B|   1.0|   8|
+---+------+----+

df2:

+---+---+-------+-----+
| id|  t|t_start|t_end|
+---+---+-------+-----+
|  A| t1|      0|    3|
|  A| t2|      4|    6|
|  A| t3|      7|    9|
|  B| t1|      0|    2|
|  B| t2|      3|    6|
|  B| t3|      7|    9|
+---+---+-------+-----+

My desired output is to identify the 't' for each time stamp in df1, where the ranges of 't' are in df2.

df_output:

+---+------+----+---+
| id|weight|time| t |
+---+------+----+---+
|  A|   0.1|   1| t1|
|  A|   0.2|   2| t1|
|  A|   0.3|   4| t2|
|  A|   0.4|   5| t2|
|  B|   0.5|   1| t1|
|  B|   0.7|   3| t2|
|  B|   0.8|   6| t2|
|  B|   0.9|   7| t3|
|  B|   1.0|   8| t3|
+---+------+----+---+

My understanding so far is that I must create an udf that takes the column 'id and 'time as inputs, map for each row, by refering to df2.filter(df2.id == df1.id, df1.time >= df2.t_start, df1.time <= df2.t_end), and get the correspondingdf2.t`

I'm very new to Scala and Spark, so I am wondering if this solution is even possible?

3 revs, 2 users 93% · Accepted Answer · 2016-07-07 20:01:37Z

2

You cannot use UDF for that but all you have to do is to reuse filter condition you already defined to join both frames:

df1.join(
  df2,
  df2("id") === df1("id") && df1("time").between(df2("t_start"), df2("t_end"))
)

edited Jul 7, 2016 at 20:01

community wiki

3 revs, 2 users 93%
zero323

Sign up to request clarification or add additional context in comments.

4 Comments

zero323 Over a year ago

Since you marked this question with scala === is a correct operator.

Béatrice M. Over a year ago

Yes, you are correct. I didn't know about === and thought it was a typo. I corrected it back as you had it, and it works now! - What is the difference between == and ===?

zero323 Over a year ago

=== is a SQL equality. == compares column objects which is not really meaningful.

Béatrice M. Over a year ago

The simplicity of your answer (and the expressive power of scala) really blows my mind :)

Collectives™ on Stack Overflow

Scala Spark - Map function referencing another dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related