如何过滤DataFrame中count列最小与最大值对应的行？

原始数据 #

原始DataFrame #

+---+-----------------------------------------+-----+
|eco|eco_name                                 |count|
+---+-----------------------------------------+-----+
|B63|Sicilian, Richter-Rauzer Attack          |5    |
|D86|Grunfeld, Exchange                       |3    |
|C99|Ruy Lopez, Closed, Chigorin, 12...cd     |5    |
|A44|Old Benoni Defense                       |3    |
|C46|Three Knights                            |1    |
|C08|French, Tarrasch, Open, 4.ed ed          |13   |
|E59|Nimzo-Indian, 4.e3, Main line            |2    |
|A20|English                                  |2    |
|B20|Sicilian                                 |4    |
|B37|Sicilian, Accelerated Fianchetto         |2    |
|A33|English, Symmetrical                     |8    |
|C77|Ruy Lopez                                |8    |
|B43|Sicilian, Kan, 5.Nc3                     |10   |
|A04|Reti Opening                             |6    |
|A59|Benko Gambit                             |1    |
|A54|Old Indian, Ukrainian Variation, 4.Nf3   |3    |
|D30|Queen's Gambit Declined                  |19   |
|C01|French, Exchange                         |3    |
|D75|Neo-Grunfeld, 6.cd Nxd5, 7.O-O c5, 8.dxc5|1    |
|E74|King's Indian, Averbakh, 6...c5          |2    |
+---+-----------------------------------------+-----+

数据Schema #

root
 |-- eco: string (nullable = true)
 |-- eco_name: string (nullable = true)
 |-- count: long (nullable = false)

需求说明 #

过滤上述DataFrame，仅保留count列取最大值和最小值对应的行，同时将count列重命名为number_of_occurences，预期输出如下：

+---+-----------------------------------------+--------------------+
|eco|eco_name                                 |number_of_occurences|
+---+-----------------------------------------+--------------------+
|D30|Queen's Gambit Declined                  |19                  |
|C46|Three Knights                            |1                   |
+---+-----------------------------------------+--------------------+

解决方法（PySpark实现） #

以下是适合初学者的分步实现代码：

1. 计算最值：先获取count列的最大值和最小值

# 假设df是你的原始DataFrame
max_count = df.select(max(col("count"))).first()[0]
min_count = df.select(min(col("count"))).first()[0]

2. 过滤并重命名列：

• 若要保留所有最值对应的行（比如所有count=1的行），用下面的代码：

from pyspark.sql.functions import col

result_df = df.filter(col("count").isin(max_count, min_count)) \
              .withColumnRenamed("count", "number_of_occurences")

• 若要和预期输出一致，仅保留一行最大值行+一行最小值行（取最小值的第一行），用下面的代码：

# 筛选最大值对应的行
max_row = df.filter(col("count") == max_count)
# 筛选最小值对应的第一行
min_row = df.filter(col("count") == min_count).limit(1)
# 合并两行并重命名列
result_df = max_row.union(min_row).withColumnRenamed("count", "number_of_occurences")

3. 查看结果：

result_df.show()

代码说明 #

• select(max(col("count"))).first()[0]：通过聚合函数max计算最大值，first()获取结果行，[0]提取具体数值。
• filter(col("count").isin(max_count, min_count))：筛选出count值等于最大值或最小值的行。
• withColumnRenamed：将原列名count修改为需求中的number_of_occurences。
• limit(1)：从多个最小值行中只取第一行，匹配预期输出。

内容的提问来源于stack exchange，提问作者AutumnRain