I have a dataframe like so:
df = sc.parallelize([("num1", "1"), ("num2", "5"), ("total", "10")]).toDF(("key", "val"))
key val
num1 1
num2 5
total 10
I want to pivot only the total row to a new column and keep its value for each row:
key val total
num1 1 10
num2 5 10
I've tried pivoting and aggregating but cannot get the one column with the same value.
You could join a dataframe with only the total to a dataframe without the total.
Another option would be to collect the total and add it as a literal.
from pyspark.sql import functions as f
# option 1
df1 = df.filter("key <> 'total'")
df2 = df.filter("key = 'total'").select(f.col('val').alias('total'))
df1.join(df2).show()
+----+---+-----+
| key|val|total|
+----+---+-----+
|num1| 1| 10|
|num2| 5| 10|
+----+---+-----+
# option 2
total = df.filter("key = 'total'").select('val').collect()[0][0]
df.filter("key <> 'total'").withColumn('total', f.lit(total)).show()
+----+---+-----+
| key|val|total|
+----+---+-----+
|num1| 1| 10|
|num2| 5| 10|
+----+---+-----+