<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/joining-tables-in-sparkr/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://statcompute.wordpress.com/2017/06/13/joining-tables-in-sparkr/"> S+/R – Yet Another Blog in Statistical Computing</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<pre>
library(SparkR, lib.loc = paste(Sys.getenv(&quot;SPARK_HOME&quot;), &quot;/R/lib&quot;, sep = &quot;&quot;))
sc &lt;- sparkR.session(master = &quot;local&quot;)
df1 &lt;- read.df(&quot;nycflights13.csv&quot;, source = &quot;csv&quot;, header = &quot;true&quot;, inferSchema = &quot;true&quot;)

grp1 &lt;- groupBy(filter(df1, &quot;month in (1, 2, 3)&quot;), &quot;month&quot;)
sum1 &lt;- withColumnRenamed(agg(grp1, min_dep = min(df1$dep_delay)), &quot;month&quot;, &quot;month1&quot;)

grp2 &lt;- groupBy(filter(df1, &quot;month in (2, 3, 4)&quot;), &quot;month&quot;)
sum2 &lt;- withColumnRenamed(agg(grp2, max_dep = max(df1$dep_delay)), &quot;month&quot;, &quot;month2&quot;)

# INNER JOIN
showDF(merge(sum1, sum2, by.x = &quot;month1&quot;, by.y = &quot;month2&quot;, all = FALSE))

showDF(join(sum1, sum2, sum1$month1 == sum2$month2, &quot;inner&quot;))

#+------+-------+------+-------+
#|month1|min_dep|month2|max_dep|
#+------+-------+------+-------+
#|     3|    -25|     3|    911|
#|     2|    -33|     2|    853|
#+------+-------+------+-------+

# LEFT JOIN
showDF(merge(sum1, sum2, by.x = &quot;month1&quot;, by.y = &quot;month2&quot;, all.x = TRUE))

showDF(join(sum1, sum2, sum1$month1 == sum2$month2, &quot;left&quot;))

#+------+-------+------+-------+
#|month1|min_dep|month2|max_dep|
#+------+-------+------+-------+
#|     1|    -30|  null|   null|
#|     3|    -25|     3|    911|
#|     2|    -33|     2|    853|
#+------+-------+------+-------+

# RIGHT JOIN
showDF(merge(sum1, sum2, by.x = &quot;month1&quot;, by.y = &quot;month2&quot;, all.y = TRUE))

showDF(join(sum1, sum2, sum1$month1 == sum2$month2, &quot;right&quot;))

#+------+-------+------+-------+
#|month1|min_dep|month2|max_dep|
#+------+-------+------+-------+
#|     3|    -25|     3|    911|
#|  null|   null|     4|    960|
#|     2|    -33|     2|    853|
#+------+-------+------+-------+

# FULL JOIN
showDF(merge(sum1, sum2, by.x = &quot;month1&quot;, by.y = &quot;month2&quot;, all = TRUE))

showDF(join(sum1, sum2, sum1$month1 == sum2$month2, &quot;full&quot;))

#+------+-------+------+-------+
#|month1|min_dep|month2|max_dep|
#+------+-------+------+-------+
#|     1|    -30|  null|   null|
#|     3|    -25|     3|    911|
#|  null|   null|     4|    960|
#|     2|    -33|     2|    853|
#+------+-------+------+-------+
</pre>
<p> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/statcompute.wordpress.com/2813/" target="_blank"><img border="0" alt="" src="http://feeds.wordpress.com/1.0/comments/statcompute.wordpress.com/2813/"></a> <img border="0" alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" data-recalc-dims="1"></p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://statcompute.wordpress.com/2017/06/13/joining-tables-in-sparkr/"> S+/R – Yet Another Blog in Statistical Computing</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...

	<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/FXzdIkgaYcc" height="1"></a></div>