使用pyspark按计数排序

Question

我试图打印前11个州，每个州最大的城市和每个州的商业数量。由于某种原因，我在打印州的商业统计数据时遇到了麻烦，只能得到城市的统计数据

这是我遇到麻烦的代码

dun=df_busSelected.groupBy("state","city").count().orderBy("count",ascending=False).limit(11).show(truncate=False)

 +-----+----------+-----+ 
|state|city |count| 
+-----+----------+-----+ 
|NV |Las Vegas |29361| 
|ON |Toronto |18904| 
|AZ |Phoenix |18764| 
|NC |Charlotte |9507 | 
|AZ |Scottsdale|8837 | 
|AB |Calgary |7735 | 
|PA |Pittsburgh|7016 | 
|QC |Montréal |6449 | 
|AZ |Mesa |6080 | 
|NV |Henderson |4892 | 
|AZ |Tempe |4550 | 
+-----+----------+-----+

Answer 1

如果我正确理解您需要做什么：

from pyspark.sql.functions import *
df_busSelected = spark.createDataFrame([("NV", "Las Vegas",29361),("ON", "Toronto" ,18904),("AZ", "Phoenix",18764),("NC", "Charlotte",9507),("AZ", "Scottsdale",8837),("AB", "Calgary",7735),("PA", "Pittsburgh",7016),("QC", "Montréal",6449),("AZ", "Mesa",6080),("NV", "Henderson",4892),("AZ", "Tempe",4550)]).toDF("state", "city", "count")

df_busSelected.withColumn("city_total_business", struct(col("count"), col("city")))\
     .groupBy("state")\
     .agg(sort_array(collect_set(col("city_total_business")), False)[0].name("top_city"))\
     .withColumn("city", col("top_city").getItem("city"))\
     .withColumn("count", col("top_city").getItem("count"))\
     .drop("top_city")\
     .show()

打印出来

+  -+     +  -+
|state|      city|count|
+  -+     +  -+
|   AZ|   Phoenix|18764|
|   QC|  Montréal| 6449|
|   NV| Las Vegas|29361|
|   NC| Charlotte| 9507|
|   PA|Pittsburgh| 7016|
|   ON|   Toronto|18904|
|   AB|   Calgary| 7735|
+  -+     +  -+

这将返回每个州计数最高的城市。现在可以很容易地对它们进行分类，并根据需要进行处理

如果你喜欢，请给我的答案打分