PySpark: How to group a column as a list when joining two spark dataframes?
<p>I want to join the following spark dataframes on Name:</p>

<pre><code>df1 = spark.createDataFrame([("Mark", 68), ("John", 59), ("Mary", 49)], ['Name', 'Weight'])

df2 = spark.createDataFrame([(31, "Mark"), (32, "Mark"), (41, "John"), (42, "John"), (43, "John")],[ 'Age', 'Name'])
</code></pre>

<p>but I want the result to be the following dataframe:</p>

<pre><code>df3 = spark.createDataFrame([([31, 32], "Mark", 68), ([41, 42, 43], "John", 59), `(None, "Mary", 49)],[ 'Age', 'Name', 'Weight'])
</code></pre>

<p>A DataFrame is equivalent to a relational table in Spark SQL.  You can groupBy, join, then select.</p>

<pre><code>from pyspark import SparkContext
from pyspark.sql import SQLContext
from pyspark.sql.functions import *

sc = SparkContext()
sql = SQLContext(sc)

df1 = sql.createDataFrame([("Mark", 68), ("John", 59), ("Mary", 49)], ['Name', \
'Weight'])

df2 = sql.createDataFrame([(31, "Mark"), (32, "Mark"), (41, "John"), (42, "John\
"), (43, "John")],[ 'Age', 'Name'])

grouped = df2.groupBy(['Name']).agg(collect_list("Age").alias('age_list'))

joined_df = df1.join(grouped, df1.Name == grouped.Name, 'left_outer')
print(joined_df.select(grouped.age_list, df1.Name, df1.Weight).collect())
</code></pre>

<p>Result</p>

<pre><code>[Row(age_list=None, Name=u'Mary', Weight=49), Row(age_list=[31, 32], Name=u'Mark', Weight=68), Row(age_list=[41, 42, 43], Name=u'John', Weight=59)]
</code></pre>

python apache-spark pyspark