Adding data to an existing apache spark dataframe from a csv file
<p>i have a spark dataframe that has two columns: name, age as follows:</p>

<pre><code>[Row(name=u'Alice', age=2), Row(name=u'Bob', age=5)]
</code></pre>

<p>The dataframe was created using </p>

<pre><code>sqlContext.createDataFrame()
</code></pre>

<p>What i need to do next is to add a third column 'UserId' from an external 'csv' file. The external file has several columns but i need to include the first column only, which is the 'UserId':</p>

<p><a href="http://i.stack.imgur.com/udp5W.png" rel="nofollow"><img src="http://i.stack.imgur.com/udp5W.png" alt="enter image description here"></a></p>

<p>The number of records in both data sources is the same. I am using a standalone pyspark version on windows os. The final result should be a new dataframe with three columns: UserId, Name, Age. </p>

<p>Any suggestion? </p>

<p>I used pandas to make this work. It allows to join dataframes in many different ways.</p>

<p>1) We need first to import only that extra column (after we remove headers, although this can also be done after the import) and convert it into an RDD</p>

<pre><code>from pyspark.sql.types import StringType
from pyspark import SQLContext
sqlContext = SQLContext(sc)
userid_rdd = sc.textFile("C:â¦â¦/userid.csv").map(lambda line: line.split(","))
</code></pre>

<p>2) Convert the 'userid' RDD into a spark dataframe</p>

<pre><code>userid_df = userid_rdd.toDF(['userid'])
userid_df.show()
</code></pre>

<p>3) Convert the 'userid' dataframe into a pandas dataframe</p>

<pre><code>userid_toPandas = userid_df.toPandas()
userid_toPandas
</code></pre>

<p>4) Convert the âpredictionsâ dataframe (an existing dataframe) into a pandas dataframe</p>

<pre><code>predictions_toPandas = predictions.toPandas() 
predictions_toPandas
</code></pre>

<p>5) Combine the two pandas dataframes into one new dataframe using âconcatâ</p>

<pre><code>import pandas as pd
result = pd.concat([userid_toPandas, predictions_toPandas], axis = 1, ignore_index = True)
result
</code></pre>

python apache-spark pyspark spark-dataframe