title: Pyspark divide column by its subtotals grouped by another column
body: <p>My problem is similar to <a href="https://stackoverflow.com/questions/50493426/pyspark-divide-column-by-its-sum?rq=1">this</a> and <a href="https://stackoverflow.com/questions/48552672/how-to-divide-a-column-by-its-sum-in-a-spark-dataframe">this</a>. Both posts show how to divide a column value by the total sum of the same column. In my case I want to divide the values of a column by the sum of subtotals. Subtotal is calculated by grouping the column values depending on another column. I am slightly modifying the example mentioned in the links shared above.</p>
  
  <p>Here is my dataframe </p>
  
  <pre><code>df = [[1,'CAT1',10], [2, 'CAT1', 11], [3, 'CAT2', 20], [4, 'CAT2', 22], [5, 'CAT3', 30]]
  df = spark.createDataFrame(df, ['id', 'category', 'consumption'])
  df.show()
  +---+--------+-----------+
  | id|category|consumption|
  +---+--------+-----------+
  |  1|    CAT1|         10|
  |  2|    CAT1|         11|
  |  3|    CAT2|         20|
  |  4|    CAT2|         22|
  |  5|    CAT3|         30|
  +---+--------+-----------+
  </code></pre>
  
  <p>I want to divide "consumption" value by the total of grouped "category" and put the value in a column "normalized" as below.</p>
  
  <p>The subtotals doesn't need to be in the output(number 21, 42 and 30 in column consumption)
  <a href="https://i.stack.imgur.com/Qp2LE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Qp2LE.png" alt="enter image description here"></a></p>
  
  <p>What I've achieved so far
  df.crossJoin( </p>
  
  <pre><code>df.groupby('category').agg(F.sum('consumption').alias('sum_'))
  ).withColumn("normalized", F.col("consumption")/F.col("sum_"))\
  .show()
  
  +---+--------+-----------+--------+----+-------------------+
  | id|category|consumption|category|sum_|         normalized|
  +---+--------+-----------+--------+----+-------------------+
  |  1|    CAT1|         10|    CAT2|  42|0.23809523809523808|
  |  2|    CAT1|         11|    CAT2|  42| 0.2619047619047619|
  |  1|    CAT1|         10|    CAT1|  21|0.47619047619047616|
  |  2|    CAT1|         11|    CAT1|  21| 0.5238095238095238|
  |  1|    CAT1|         10|    CAT3|  30| 0.3333333333333333|
  |  2|    CAT1|         11|    CAT3|  30|0.36666666666666664|
  |  3|    CAT2|         20|    CAT2|  42|0.47619047619047616|
  |  4|    CAT2|         22|    CAT2|  42| 0.5238095238095238|
  |  5|    CAT3|         30|    CAT2|  42| 0.7142857142857143|
  |  3|    CAT2|         20|    CAT1|  21| 0.9523809523809523|
  |  4|    CAT2|         22|    CAT1|  21| 1.0476190476190477|
  |  5|    CAT3|         30|    CAT1|  21| 1.4285714285714286|
  |  3|    CAT2|         20|    CAT3|  30| 0.6666666666666666|
  |  4|    CAT2|         22|    CAT3|  30| 0.7333333333333333|
  |  5|    CAT3|         30|    CAT3|  30|                1.0|
  +---+--------+-----------+--------+----+-------------------+
  </code></pre>