How to transpose a dataframe in pyspark?

Question

How do I transpose columns in Pyspark? I want to make columns become rows, and rows become the columns.

Here is the input:

+---- +------+-----+-----+
|idx  | vin  |cur  | mean|
+---- +------+-----+-----+
|Type1|     D| 5.0 |6.0  |
|Type2|     C| null| 7.0 |
+---- +------+-----+-----+

Expected Outcome:

+---- +------+-----+
|idx  |Type1 |Type2| 
+---- +------+-----+
|vin  |   D  | C   |
|cur  |  5.0 | null| 
|mean |  6.0 | 7.0 |     
+-----+------+-----+

Please repeat [on topic](https://stackoverflow.com/help/on-topic) and [how to ask](https://stackoverflow.com/help/how-to-ask) from the [intro tour](https://stackoverflow.com/tour). Asking a question that you haven't researched is not acceptable. — Prune, Feb 09 '21 at 22:26
@Prune lack of research is a downvote reason, sure, but not a close vote reason. OP wants to know how to complete this functionality in pyspark, not pandas. I've voted to reopen based on that discrepancy. — TylerH, Feb 09 '21 at 23:06
@inses0606 Have you made an attempt at this yourself and come across a problem? If not, that's your recommended first step. Then you can explain in the question exactly how your own attempt comes up short. — TylerH, Feb 09 '21 at 23:07

score 2 · Accepted Answer · answered Feb 09 '21 at 23:20

You can combine stack function to unpivot vin, mean and cur columns then pivot column idx:

from pyspark.sql import functions as F

df1 = df.selectExpr("idx", "stack(3, 'vin',vin, 'cur',cur, 'mean',mean)") \
    .select("idx", "col0", "col1") \
    .groupBy("col0") \
    .pivot("idx").agg(F.first("col1")) \
    .withColumnRenamed("col0", "idx")

df1.show(truncate=False)

#+----+-----+-----+
#|idx |Type1|Type2|
#+----+-----+-----+
#|vin |D    |C    |
#|mean|6.0  |7.0  |
#|cur |5.0  |null |
#+----+-----+-----+

You apply the transformation one by one to see how it works and what do each part.

How to transpose a dataframe in pyspark?

1 Answers1