Prevent Duplicated Columns when Joining Two DataFrames
If you perform a join in Spark and don’t specify your join correctly you’ll end up with duplicate column names. This makes it harder to select those columns. This topic and notebook demonstrate how to perform a join so that you don’t have duplicated columns.
Join on columns
If you join on columns, you get duplicated columns.
- Scala
%scala val llist = Seq(("bob", "2015-01-13", 4), ("alice", "2015-04-23",10)) val left = llist.toDF("name","date","duration") val right = Seq(("alice", 100),("bob", 23)).toDF("name","upload") val df = left.join(right, left.col("name") === right.col("name"))
- Python
%python llist = [('bob', '2015-01-13', 4), ('alice', '2015-04-23',10)] left = spark.createDataFrame(llist, ['name','date','duration']) right = spark.createDataFrame([('alice', 100),('bob', 23)],['name','upload']) df = left.join(right, left.name == right.name)
Solution
Specify the join column as an array type or string.
- Scala
%scala val df = left.join(right, Seq("name"))
%scala val df = left.join(right, "name")
- Python
%python df = left.join(right, ["name"])
%python df = left.join(right, "name")
- R
First register the DataFrames as tables.
%python left.createOrReplaceTempView("left_test_table") right.createOrReplaceTempView("right_test_table")
%r library(SparkR) sparkR.session() left <- sql("SELECT * FROM left_test_table") right <- sql("SELECT * FROM right_test_table")
The above code results in duplicate columns. The following code does not.
%r head(drop(join(left, right, left$name == right$name), left$name))