Pyspark - How to create a column replicating a specific number from a cell

user_10692899 0
Viewed 44

I need to convert the following dataframe:

Group - Risk Group - State - Value
A           yes        1      1000
A           no         2      100
A           no         3      100
A           no         4      100
A           no         5      100
B           yes        1      2000
B           no         2      200
B           no         3      100
B           no         4      100
B           no         5      200
C           yes        1      3000
C           no         2      100
C           no         3      100
C           no         4      200
C           no         5      200

Into this, where I'm transposing the risk group yes into a column:

Group - Risk Group - State - Value - Risk Group Value
A           no         2      100         1000
A           no         3      100         1000
A           no         4      100         1000
A           no         5      100         1000 
B           no         2      200         2000
B           no         3      100         2000
B           no         4      100         2000
B           no         5      200         2000
C           no         2      100         3000
C           no         3      100         3000
C           no         4      200         3000
C           no         5      200         3000

My final output would be to create a new column making the ratio between (risk group value / value) but this is easy to be done.

Thanks!

pyspark case-when transpose conditional-statements
1 Answers

You could sum only when Risk Group is yes, over a window partitioned by Group and then filter to remove yes rows to get desired output.

from pyspark.sql import functions as F
from pyspark.sql.window import Window

w=Window().partitionBy("Group")
df.withColumn("Risk Group Value", F.sum(F.when(F.col("Risk Group")=='yes',F.col("Value"))).over(w))\
  .filter(F.col("Risk Group")!='yes')\
  .orderBy("Group").show()

#+-----+----------+-----+-----+----------------+
#|Group|Risk Group|State|Value|Risk Group Value|
#+-----+----------+-----+-----+----------------+
#|    A|        no|    2|  100|            1000|
#|    A|        no|    3|  100|            1000|
#|    A|        no|    4|  100|            1000|
#|    A|        no|    5|  100|            1000|
#|    B|        no|    2|  200|            2000|
#|    B|        no|    3|  100|            2000|
#|    B|        no|    4|  100|            2000|
#|    B|        no|    5|  200|            2000|
#|    C|        no|    2|  100|            3000|
#|    C|        no|    5|  200|            3000|
#|    C|        no|    3|  100|            3000|
#|    C|        no|    4|  200|            3000|
#+-----+----------+-----+-----+----------------+
user_101073890
Related