DataFrame左外连接遇多列重名报错，求问题排查及解决方法

问题场景 #

两个DataFrame（df和df_forecast）均包含D_ACCOUNTS_ID、D_APPS_ID、D_CONTENT_PAGE_ID三列，执行左外连接代码：

df = df.join(df_forecast, ["D_ACCOUNTS_ID", "D_APPS_ID", "D_CONTENT_PAGE_ID"], 'left')

运行后出现报错：

You're trying to access a column, but multiple columns have that name.

问题原因 #

用共同列作为连接键时，连接后的结果DataFrame会保留两个源DataFrame中的这些列（重名状态），后续操作无法直接区分重名列，因此触发报错。

解决方法 #

方法1：连接前重命名其中一个DataFrame的连接列 #

先修改df_forecast的连接列名称，避免重名后再执行连接：

# 重命名df_forecast的连接列
df_forecast_renamed = df_forecast.withColumnRenamed("D_ACCOUNTS_ID", "D_ACCOUNTS_ID_fc")\
                                 .withColumnRenamed("D_APPS_ID", "D_APPS_ID_fc")\
                                 .withColumnRenamed("D_CONTENT_PAGE_ID", "D_CONTENT_PAGE_ID_fc")

# 基于重命名后的列做连接
df = df.join(df_forecast_renamed, 
             (df.D_ACCOUNTS_ID == df_forecast_renamed.D_ACCOUNTS_ID_fc) & 
             (df.D_APPS_ID == df_forecast_renamed.D_APPS_ID_fc) & 
             (df.D_CONTENT_PAGE_ID == df_forecast_renamed.D_CONTENT_PAGE_ID_fc), 
             'left')

方法2：连接后删除重复的连接列 #

如果不需要保留df_forecast中的连接列，连接完成后直接删除重复列：

df = df.join(df_forecast, ["D_ACCOUNTS_ID", "D_APPS_ID", "D_CONTENT_PAGE_ID"], 'left')
# 删除来自df_forecast的重复连接列
df = df.drop(df_forecast.D_ACCOUNTS_ID, df_forecast.D_APPS_ID, df_forecast.D_CONTENT_PAGE_ID)

方法3：给DataFrame设置别名，明确区分列 #

通过别名标记两个DataFrame，连接和后续操作时用别名指定列：

# 给两个DataFrame设置别名
df = df.alias("a").join(df_forecast.alias("b"), 
                        (a.D_ACCOUNTS_ID == b.D_ACCOUNTS_ID) & 
                        (a.D_APPS_ID == b.D_APPS_ID) & 
                        (a.D_CONTENT_PAGE_ID == b.D_CONTENT_PAGE_ID), 
                        'left')

# 后续访问列时需通过别名区分，例如：
# df.select("a.D_ACCOUNTS_ID", "b.D_ACCOUNTS_ID")

内容的提问来源于stack exchange，提问作者x89