Kumar Deepak Kumar Deepak - 2 months ago 35
R Question

In SparkR 1.5.0, how do we specify a column unambiguously after a join on common column?

I joined two dataframes on a column with same name.

oe = join(orders, emp, orders$EmployeeID == emp$EmployeeID)


The resulting dataframe has two columns with same name
EmployeeID


Now a group by or even printing column name

peremp = groupBy(oe, 'EmployeeID', sales = n(oe$OrderID))
oe$EmployeeID


fails with the error


Error in invokeJava(isStatic = FALSE, objId$id, methodName, ...) :

org.apache.spark.sql.AnalysisException: Reference 'EmployeeID' is
ambiguous, could be: EmployeeID#36, EmployeeID#69.;

Answer

You can access columns through parent data frame. First lets create some example data:

df1 <- createDataFrame(sqlContext, data.frame(id=c(1, 2, 3), v=c("a", "b", "c")))
df2 <- createDataFrame(sqlContext, data.frame(id=c(2, 3), v=c("g", "z")))
df <- join(df1, df2, df1$id == df2$id)
head(df)
##   id v id v
## 1  3 c  3 z
## 2  2 b  2 g

And access v column:

select(df, "v")
## 15/09/30 17:47:13 ERROR RBackendHandler: select on 131 failed
## Error in invokeJava(isStatic = FALSE, objId$id, methodName, ...) : 
##   org.apache.spark.sql.AnalysisException: Reference 'v' is ambiguous, could be
## ....

select(df, df1$v) %>% head
##   v
## 1 c
## 2 b