Q: How to join(Merge) two SparkDataFrame in SparkR and keep one of the common columns i have the following Spark DataFrame :
aps=data.frame(agent=c('a','b','c','d','a','a','a','b','c','a','b'),product=c('P1','P2','P3','P4','P1','P1','P2','P2','P2','P3','P3'),
      sale_amount=c(1000,2000,3000,4000,1000,1000,2000,2000,2000,3000,3000))

RDD_aps=createDataFrame(sqlContext,agent_product_sale)


   agent product sale_amount
1      a      P1        1000
2      b      P2        2000
3      c      P3        3000
4      d      P4        4000
5      a      P1        1000
6      a      P1        1000
7      a      P2        2000
8      b      P2        2000
9      c      P2        2000
10     a      P3        3000
11     b      P3        3000

and 
    percent=data.frame(agent=c('a','b','c'),percent=c(0.2 ,0.5,1.0))
agent  percent
  a      0.2
  b      0.5
  c      1.0

I need to join (merge) two data frame so that i can have a percent for each agent
something like this as output :
   agent product sale_amount     percent
1      d      P4        4000          NA
2      c      P3        3000         1.0
3      c      P2        2000         1.0
4      b      P2        2000         0.5
5      b      P2        2000         0.5
6      b      P3        3000         0.5
7      a      P1        1000         0.2
8      a      P1        1000         0.2
9      a      P1        1000         0.2
10     a      P2        2000         0.2
11     a      P3        3000         0.2

I have already tried :
     joined_aps=join(RDD_aps,percent,RDD_aps$agent==percent$agent,"left_outer")

but it adds an new  second "agent" column from percent dataframe and i don't want the duplicate column.
I have also tried:
merged=merge(RDD_aps,percent, by = "agent",all.x=TRUE)

This one also add "agent_y " column but i just want to have one agent column in (agent column from RDD_aps)

A: I think I saw someone prevent the '_x' and '_y' variables from being generated by using join somewhere on SO, but I am unable to find that post. I personally prefer merge in my operations...I think it's easier for me, plus I like being able to switch between a left/right/inner/outer/etc join using the all.x=TRUE/FALSE and all.y=TRUE/FALSE arguments.  I still get the annoying (but useful for verification purposes) _x and _y columns, but I fix those using code similar to the example below:
df1<- data.frame(person=c("Bob", "Jane", "John", "Liz"), favoriteColor=c("Blue", "Green", "Black", "White"))
df2<- data.frame(person=c("Bob", "Jane", "John", "Liz"), age=c(10,20,30,40))

sdf1<- SparkR::createDataFrame(df1)
sdf2<- SparkR::createDataFrame(df2)

sdf<- SparkR::merge(sdf1, sdf2, by.x="person", by.y="person", all.x=FALSE, all.y=FALSE) # Inner join...all.x/y not needed
colnames(sdf) # person_x and person_y are now present...to be fixed here

colnames(sdf)<- gsub(pattern= "_x",replacement = "", colnames(sdf))

col_names_sdf_subset<- colnames(sdf)[!(colnames(sdf) %in% colnames(sdf)[grep("_y", colnames(sdf))])]

sdf<- sdf %>% SparkR::select(col_names_sdf_subset) 

colnames(sdf)
View(head(sdf, num=20L))

