Pivot in pySpark

Question

I have a dataframe:

student_id class score
1 A 6
1 B 7
1 C 8

I would like to divide the class score into 3 columns so the above dataframe should become:

student_id class_A_score class_B_score class_C_score
1 6 7 8

The idea is to convert A B C into 3 columns.

@eliasah Can you help me see the similarity between OP's question and the link you provided. — Rahul Chawla, Dec 14 '18 at 09:28
Duplicate of this - https://stackoverflow.com/questions/53171023/pyspark-how-to-transpose-a-dataframe — cph_sto, Dec 14 '18 at 09:32
@eliasah I am skeptical about the method you mentioned, please see if the answers to this question are what you were pointing to. — Rahul Chawla, Dec 14 '18 at 09:32

Rahul Chawla · Answer 1 · 2018-12-14T09:30:53.597

1

This is a classic example of pivot. In pyspark, if df is your dataframe:

new_df = df.groupBy(['student_id']).pivot('class').sum(score)

Databricks has very nice illustration of this at https://databricks.com/blog/2016/02/09/reshaping-data-with-pivot-in-apache-spark.html

edited Dec 14 '18 at 09:30

answered Dec 14 '18 at 09:25

Rahul Chawla

1,048
10
15

score 1 · Accepted Answer · answered Dec 14 '18 at 09:29

values = [(1,'A',6),(1,'B',7),(1,'C',8)]
df = sqlContext.createDataFrame(values,['student_id','class','score'])
df.show()
+----------+-----+-----+
|student_id|class|score|
+----------+-----+-----+
|         1|    A|    6|
|         1|    B|    7|
|         1|    C|    8|
+----------+-----+-----+
df = df.groupBy(["student_id"]).pivot("class").sum("score")
df.show()
+----------+---+---+---+
|student_id|  A|  B|  C|
+----------+---+---+---+
|         1|  6|  7|  8|
+----------+---+---+---+

Pivot in pySpark

2 Answers2