I have a PySpark dataframe consists of three columns x, y, z.
X may have multiple rows in this dataframe. How can I compute the percentile of each key in x separately?
+------+---------+------+
| Name| Role|Salary|
+------+---------+------+
| bob|Developer|125000|
| mark|Developer|108000|
| carl| Tester| 70000|
| carl|Developer|185000|
| carl| Tester| 65000|
| roman| Tester| 82000|
| simon|Developer| 98000|
| eric|Developer|144000|
|carlos| Tester| 75000|
| henry|Developer|110000|
+------+---------+------+
The output needed:
+------+---------+------+---------+
| Name| Role|Salary| 50%|
+------+---------+------+---------+
| bob|Developer|125000|117500.0 |
| mark|Developer|108000|117500.0 |
| carl| Tester| 70000|72500.0 |
| carl|Developer|185000|117500.0 |
| carl| Tester| 65000|72500.0 |
| roman| Tester| 82000|72500.0 |
| simon|Developer| 98000|117500.0 |
| eric|Developer|144000|117500.0 |
|carlos| Tester| 75000|72500.0 |
| henry|Developer|110000|117500.0 |
+------+---------+------+---------+
Try groupby + F.expr:
import pyspark.sql.functions as F
df1 = df.groupby('Role').agg(F.expr('percentile(Salary, array(0.25))')[0].alias('%25'),
F.expr('percentile(Salary, array(0.50))')[0].alias('%50'),
F.expr('percentile(Salary, array(0.75))')[0].alias('%75'))
df1.show()
Output:
+---------+--------+--------+--------+
| Role| %25| %50| %75|
+---------+--------+--------+--------+
| Tester| 68750.0| 72500.0| 76750.0|
|Developer|108500.0|117500.0|139250.0|
+---------+--------+--------+--------+
Now you might join df1 with the original dataframe:
df.join(df1, on='Role', how='left').show()
Output:
+---------+------+------+--------+--------+--------+
| Role| Name|Salary| %25| %50| %75|
+---------+------+------+--------+--------+--------+
| Tester| carl| 70000| 68750.0| 72500.0| 76750.0|
| Tester| carl| 65000| 68750.0| 72500.0| 76750.0|
| Tester| roman| 82000| 68750.0| 72500.0| 76750.0|
| Tester|carlos| 75000| 68750.0| 72500.0| 76750.0|
|Developer| bob|125000|108500.0|117500.0|139250.0|
|Developer| mark|108000|108500.0|117500.0|139250.0|
|Developer| carl|185000|108500.0|117500.0|139250.0|
|Developer| simon| 98000|108500.0|117500.0|139250.0|
|Developer| eric|144000|108500.0|117500.0|139250.0|
|Developer| henry|110000|108500.0|117500.0|139250.0|
+---------+------+------+--------+--------+--------+