Merging the similar column names while joining two dataframes using pyspark

Question

In the below program ,the duplicate columns are getting created while joining two dataframes in pyspark .

>>> spark = SparkSession.builder.appName("Join").getOrCreate()
>>> dict=[{"Emp_id" : 123 , "Emp_name" : "Raja" }, {"Emp_id" : 456 , "Emp_name" : "Ravi"}]
>>> dict1=[{"Emp_id" : 123 , "Dep_name" : "Computer" } , {"Emp_id" : 456 ,"Dep_name"  :"Economy"}]
>>> df=spark.createDataFrame(dict)
>>> df1=spark.createDataFrame(dict1)
>>> df2=df.join(df1,df.Emp_id == df1.Emp_id, how = 'inner')

>>> df.show()
    +------+--------+
    |Emp_id|Emp_name|
    +------+--------+
    |   123|    Raja|
    |   456|    Ravi|
    +------+--------+

>>> df1.show()
    +--------+------+
    |Dep_name|Emp_id|
    +--------+------+
    |Computer|   123|
    | Economy|   456|
    +--------+------+

>>> df2=df.join(df1,df.Emp_id == df1.Emp_id, how = 'inner')


>>> df2.show()
+------+--------+--------+------+
|Emp_id|Emp_name|Dep_name|Emp_id|
+------+--------+--------+------+
|   123|    Raja|Computer|   123|
|   456|    Ravi| Economy|   456|
+------+--------+--------+------+

Is there any other way to get the data like below as the result of join with overwriting the columns as similar as in SAS?

 +------+--------+--------+
|Emp_id|Emp_name|Dep_name|
+------+--------+--------+
|   123|    Raja|Computer|
|   456|    Ravi| Economy|
+------+--------+--------+

Answer 1

In your join condition replace df.Emp_id == df1.Emp_id with ['Emp_id']

---

df2=df.join(df1,['Emp_id'], how = 'inner')
df2.show()

#+------+--------+--------+
#|Emp_id|Emp_name|Dep_name|
#+------+--------+--------+
#|   123|    Raja|Computer|
#|   456|    Ravi| Economy|
#+------+--------+--------+

Answer 2

when joinin two dataframes on same column, explicitly specify join column on which you want to apply join in 'on' clouse.

df2=df.join(df1, on='Emp_id' how = 'inner')
df2.show()

#+------+--------+--------+
#|Emp_id|Emp_name|Dep_name|
#+------+--------+--------+
#|   123|    Raja|Computer|
#|   456|    Ravi| Economy|
#+------+--------+--------+