Merging rows from different dataframes together in Scala

Question

For example first I have a dataframe like this

+----+-----+-----+--------------------+-----+
|year| make|model|             comment|blank|
+----+-----+-----+--------------------+-----+
|2012|Tesla|    S|          No comment|     |
|1997| Ford| E350|Go get one now th...|     |
|2015|Chevy| Volt|                null| null|
+----+-----+-----+--------------------+-----+

we have years 2012, 1997 and 2015. And we have another Dataframe like this

+----+-----+-----+--------------------+-----+
|year| make|model|             comment|blank|
+----+-----+-----+--------------------+-----+
|2012|BMW  |    3|          No comment|     |
|1997|VW   | GTI |   get              |     |
|2015|MB   | C200|                good| null|
+----+-----+-----+--------------------+-----+

we also have year 2012, 1997, 2015. How can we merge the rows with same year together? Thanks

The output should be like this

+----+-----+-----+--------------------+-----++-----+-----+--------------------------+
|year| make|model|             comment|blank|| make|model|             comment|blank|
+----+-----+-----+--------------------+-----++-----+-----+-----+--------------------+
|2012|Tesla|    S|          No comment|     |BMW   | 3   |          no comment|
|1997| Ford| E350|Go get one now th...|     |VW    |GTI  |      get           |
|2015|Chevy| Volt|                null| null|MB    |C200 |             Good   |null
+----+-----+-----+--------------------+-----++----+-----+-----+---------------------+

Rohan Aletty · Accepted Answer · 2015-11-05 22:09:46Z

1

You can get what your desired table with a simple join. Something like:

val joined = df1.join(df2, df1("year") === df2("year"))

I loaded your inputs such that I see the following:

scala> df1.show
...
year make  model comment
2012 Tesla S     No comment
1997 Ford  E350  Go get one now
2015 Chevy Volt  null

scala> df2.show
...
year make model comment
2012 BMW  3     No comment
1997 VW   GTI   get
2015 MB   C200  good

When I run the join, I get:

scala> val joined = df1.join(df2, df1("year") === df2("year"))
joined: org.apache.spark.sql.DataFrame = [year: string, make: string, model: string, comment: string, year: string, make: string, model: string, comment: string]

scala> joined.show
...
year make  model comment        year make model comment
2012 Tesla S     No comment     2012 BMW  3     No comment
2015 Chevy Volt  null           2015 MB   C200  good
1997 Ford  E350  Go get one now 1997 VW   GTI   get

One thing to note is that your column names may be ambiguous as they are named the same across dataframes (so you could change their names to make operations on your resultant dataframe easier to write).

edited Nov 5, 2015 at 22:09

answered Nov 5, 2015 at 22:05

Rohan Aletty

2,4421 gold badge17 silver badges21 bronze badges

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

1 Comment

Gavin Niu Over a year ago

Is there inner join, left join, right join or full join in Spark? Thanks

Collectives™ on Stack Overflow

Merging rows from different dataframes together in Scala

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related