Python PySpark DataFrame groupBy方法用法及代碼示例

例子

考慮以下PySpark DataFrame：

df = spark.createDataFrame([["Alex", "IT", 20, 500],\
                            ["Bob", "IT", 24, 400],\
                            ["Cathy", "HR", 22, 600]],\
                            ["name", "department", "age", "salary"])
df.show()



+-----+----------+---+------+
| name|department|age|salary|
+-----+----------+---+------+
| Alex|        IT| 20|   500|
|  Bob|        IT| 24|   400|
|Cathy|        HR| 22|   600|
+-----+----------+---+------+

基本用法

默認情況下，不帶任何參數的 groupBy() 會將所有行分組在一起，並將計算每個數字列的統計信息：

df.groupby().max().show()



+--------+-----------+
|max(age)|max(salary)|
+--------+-----------+
|      24|        600|
+--------+-----------+

按單列分組，計算每組所有列的統計值

要獲得每個部門中最高的 age 和 salary：

df.groupBy("department").max().show()



+----------+--------+-----------+
|department|max(age)|max(salary)|
+----------+--------+-----------+
|        IT|      24|        500|
|        HR|      22|        600|
+----------+--------+-----------+

我們還可以使用 SQL.functions.col(~) ，而不是通過標簽(string)引用列：

from pyspark.sql import functions as F
df.groupby(F.col("department")).max().show()



+----------+--------+-----------+
|department|max(age)|max(salary)|
+----------+--------+-----------+
|        IT|      24|        500|
|        HR|      22|        600|
+----------+--------+-----------+

按單列分組並計算每組特定列的統計數據

僅獲取最高的 age 而不是所有數字列：

df.groupby("department").max("age").show()



+----------+--------+
|department|max(age)|
+----------+--------+
|        IT|      24|
|        HR|      22|
+----------+--------+

同樣，我們可以使用 agg(~) 方法並使用 SQL.functions ' 聚合函數之一：

df.groupby("department").agg(F.max("age")).show()



+----------+--------+
|department|max(age)|
+----------+--------+
|        IT|      24|
|        HR|      22|
+----------+--------+

agg, avg, count, max, mean, min, pivot, sum

使用聚合列的別名進行分組

默認情況下，計算每個組的最大值 age 將得到列標簽 max(age) ：

df.groupby("department").max("age").show()



+----------+--------+
|department|max(age)|
+----------+--------+
|        IT|      24|
|        HR|      22|
+----------+--------+

要使用別名，我們需要使用函數agg(~)：

import pyspark.sql.functions as F
df.groupby("department").agg(F.max("age").alias("max_age")).show()



+----------+-------+
|department|max_age|
+----------+-------+
|        IT|     24|
|        HR|     22|
+----------+-------+

分組和計算多個統計數據

要一次計算多個統計數據：

import pyspark.sql.functions as F
df.groupby("department").agg(F.max("age").alias("max"), F.min("age"), F.avg("salary")).show()



+----------+--------+--------+-----------------+
|department|     max|min(age)|      avg(salary)|
+----------+--------+--------+-----------------+
|        IT|      26|      20|566.6666666666666|
|        HR|      22|      22|            600.0|
+----------+--------+--------+-----------------+

多列分組並計算統計

考慮以下PySpark DataFrame：

df = spark.createDataFrame([["Alex", "junior", "IT", 20, 500],\
                            ["Bob", "junior", "IT", 24, 400],\
                            ["Cathy", "junior", "HR", 22, 600],\
                            ["Doge", "senior", "IT", 26, 800]],\
                            ["name", "position", "department", "age", "salary"])
df.show()



+-----+--------+----------+---+------+
| name|position|department|age|salary|
+-----+--------+----------+---+------+
| Alex|  junior|        IT| 20|   500|
|  Bob|  junior|        IT| 24|   400|
|Cathy|  junior|        HR| 22|   600|
| Doge|  senior|        IT| 26|   800|
+-----+--------+----------+---+------+

按 position 和 department 進行分組，然後計算每個組的最大值 age：

df.groupby(["position", "department"]).max("age").show()



+--------+----------+--------+
|position|department|max(age)|
+--------+----------+--------+
|  junior|        IT|      24|
|  junior|        HR|      22|
|  senior|        IT|      26|
+--------+----------+--------+