I am new to Spark and need a help with transposing the below input dataframe into the desired output df (Rows to Columns) using PySpark or Spark Sql.
Input Dataframe-
A B C D
1 2 3 4
10 11 12 13
......
........
Required Output (transposed) data
A 1
B 2
C 3
D 4
A 11
B 12
C 13
D 14
....
......
It is better if I can pivot the input data (columns) as per our requirement.
Use stack function in spark.
Example:
df.show()
#+---+---+---+---+
#| A| B| C| D|
#+---+---+---+---+
#| 1| 2| 3| 4|
#| 10| 11| 12| 13|
#+---+---+---+---+
from pyspark.sql.functions import *
df.selectExpr("stack(4,'A',A,'B',B,'C',C,'D',D) as (key,value)").show()
#+---+-----+
#|key|value|
#+---+-----+
#| A| 1|
#| B| 2|
#| C| 3|
#| D| 4|
#| A| 10|
#| B| 11|
#| C| 12|
#| D| 13|
#+---+-----+
You can make a generalized function like below (inspired from my previous answer here):
def stack_multiple(data,cols=None,output_columns=["col","values"]):
"""stacks multiple columns in a dataframe,
takes all columns by default unless passed a list of values"""
cols = data.columns if cols is None else cols
cols= [cols] if isinstance(cols,str) else cols
return data.selectExpr(f"""stack({len(cols)},{','.join(map(','.join,
(zip([f'"{i}"' for i in cols],[f"`{i}`" for i in cols]))))})
as ({','.join(output_columns)})""")
Sample Runs:
stack_multiple(df).show()
+---+------+
|col|values|
+---+------+
| A| 1|
| B| 2|
| C| 3|
| D| 4|
| A| 10|
| B| 11|
| C| 12|
| D| 13|
+---+------+
stack_multiple(df,['A','B'],output_columns=['A','B']).show()
+---+---+
| A| B|
+---+---+
| A| 1|
| B| 2|
| A| 10|
| B| 11|
+---+---+
df = spark.createDataFrame([[1,2,3,4],[10,11,12,13]]).toDF('A','B','C','D')
+---+---+---+---+
| A| B| C| D|
+---+---+---+---+
| 1| 2| 3| 4|
| 10| 11| 12| 13|
+---+---+---+---+
df.select(
F.explode(
F.create_map(
*reduce(lambda x, y: x+y,
[[F.lit(col), col] for col in df.columns]
)
)
)
)
+---+-----+
|key|value|
+---+-----+
| A| 1|
| B| 2|
| C| 3|
| D| 4|
| A| 10|
| B| 11|
| C| 12|
| D| 13|
+---+-----+