How to Distinguish Columns with Duplicated Names in Spark DataFrame?

When working with Spark DataFrames, it’s common to encounter situations where columns may have duplicated names, especially after performing joins or other operations. Distinguishing between these columns and renaming them can help while referencing and avoiding ambiguity. Here’s how you can do it:

Handling Duplicated Column Names in Spark DataFrame

Let’s assume that we have two DataFrames with a common column name, and we perform a join operation.

import pyspark
from pyspark.sql import SparkSession
from pyspark.sql.functions import col

# Create a Spark session
spark = SparkSession.builder.master("local").appName("Distinguish Columns").getOrCreate()

# Sample data
data1 = [("Alice", 34), ("Bob", 45)]
data2 = [("Alice", "Engineer"), ("Bob", "Manager")]

# Create DataFrames
df1 = spark.createDataFrame(data1, ["Name", "Age"])
df2 = spark.createDataFrame(data2, ["Name", "Job"])

# Perform a join
joined_df = df1.join(df2, on="Name")

# Show the DataFrame to see duplicated 'Name' column
joined_df.show()

+-----+---+--------+
| Name|Age|     Job|
+-----+---+--------+
|Alice| 34|Engineer|
|  Bob| 45| Manager|
+-----+---+--------+

Renaming Columns After a Join

We can rename the columns after performing the join to avoid confusion.

renamed_df = joined_df.withColumnRenamed("Name", "Person_Name")

# Show the DataFrame with renamed columns
renamed_df.show()

+-----------+---+--------+
|Person_Name|Age|     Job|
+-----------+---+--------+
|      Alice| 34|Engineer|
|        Bob| 45| Manager|
+-----------+---+--------+

Using Aliases

You can also use aliases during the join operation to avoid duplicating column names.

df1 = df1.alias("df1")
df2 = df2.alias("df2")

joined_df_with_aliases = df1.join(df2, col("df1.Name") == col("df2.Name")).select(
    col("df1.Name").alias("Person_Name"),
    col("df1.Age"),
    col("df2.Job")
)

# Show the DataFrame with aliases
joined_df_with_aliases.show()

+-----------+---+--------+
|Person_Name|Age|     Job|
+-----------+---+--------+
|      Alice| 34|Engineer|
|        Bob| 45| Manager|
+-----------+---+--------+

By following these approaches, you can distinguish columns with duplicated names in a Spark DataFrame and ensure clarity and ease of reference.