How to obtain row percentages of crosstab from a spark dataframe using python?

user_14685323 0
Viewed 706

I used python code:

df.stat.crosstab("age", "y").orderBy("age_y").show()

to create a crosstab from a spark dataframe as follows:

enter image description here

However, I cannot find a code to obtain the row percentages. For example, age 18 row percentages should be 5/12 = 41.7% for 'no' and 7/12 = 58.3% for 'yes'. The sum of 2 percentages is 100%.

May someone advise me in this case? Many thanks in advance.

pyspark apache-spark-sql apache-spark python
1 Answers

Simply add 2 columns using using withColumn and your formula to calculate the percentages:

from pyspark.sql import functions as F

df1 = df.stat.crosstab("age", "y").orderBy("age_y")

result = df1.withColumn(
    "no_rp",
    F.round(F.col("no") / (F.col("no") + F.col("yes")) * 100, 2)
).withColumn(
    "yes_rp",
    F.round(F.col("yes") / (F.col("no") + F.col("yes")) * 100, 2)
)

result.show()

#+-----+---+---+-----+------+
#|age_y| no|yes|no_rp|yes_rp|
#+-----+---+---+-----+------+
#|   18|  5|  7|41.67| 58.33|
#|   19| 24| 11|68.57| 31.43|
#|   20| 35| 15| 70.0|  30.0|
#+-----+---+---+-----+------+
user_13865510
Related