Breaking down rows in Pyspark DataFrame
<p>I have a PySpark DataFrame in this format:</p>

<pre><code> dbn      |       bus      | subway   | score
----------|----------------|----------|--------
XYZ12     | B1, B44, B66   | A, C     | 59
ZYY3      | B8, B3, B7     | J, Z     | 66
</code></pre>

<p>What I want to do is be able to attach the score column to every individual bus and subway line, however I want to work on one column at a time so I'll start with bus. Ultimately what I want my DataFrame to look like is this (when I'm working with the bus column)</p>

<pre><code>dbn      | bus       | subway  | score
---------|-----------|---------|-------
XYZ12    | B1        | A, C    | 59
XYZ12    | B44       | A, C    | 59
XYZ12    | B66       | A, C    | 59
ZYY3     | B8        | J, Z    | 66
ZYY3     | B3        | J, Z    | 66
ZYY3     | B7        | J, Z    | 66
</code></pre>

<p>How would I go about doing this?</p>

<p>You can <code>explode</code> function which expects an <code>array</code> or a <code>map</code> column as an input. If <code>bus</code> is a string you can use string processing functions, like <code>split</code>, to break it into pieces first. Let's assume this scenario:</p>

<pre><code>df = sc.parallelize([
    ("XYZ12", "B1, B44, B66", "A, C", 59),
    ("ZYY3 ", "B8, B3, B7", "J, Z", 66)
]).toDF(["dbn", "bus", "subway", "score"])
</code></pre>

<p>First import required functions:</p>

<pre><code>from pyspark.sql.functions import col, explode, split, trim
</code></pre>

<p>add column:</p>

<pre><code>with_bus_exploded = df.withColumn("bus", explode(split("bus", ",")))
</code></pre>

<p>and <code>trim</code> leading / trailing spaces:</p>

<pre><code>with_bus_trimmed = with_bus_exploded.withColumn("bus", trim(col("bus")))
</code></pre>

<p>Finally the result is:</p>

<pre class="lang-none prettyprint-override"><code>+-----+---+------+-----+
|  dbn|bus|subway|score|
+-----+---+------+-----+
|XYZ12| B1|  A, C|   59|
|XYZ12|B44|  A, C|   59|
|XYZ12|B66|  A, C|   59|
|ZYY3 | B8|  J, Z|   66|
|ZYY3 | B3|  J, Z|   66|
|ZYY3 | B7|  J, Z|   66|
+-----+---+------+-----+
</code></pre>

python dataframe pyspark pyspark-sql