python - 通过PySpark中的几列从groupby获取具有最大值的行

我有一个类似于

from pyspark.sql.functions import avg, first

rdd = sc.parallelize(
[
(0, "A", 223,"201603", "PORT"),
(0, "A", 22,"201602", "PORT"),
(0, "A", 22,"201603", "PORT"),
(0, "C", 22,"201605", "PORT"),
(0, "D", 422,"201601", "DOCK"),
(0, "D", 422,"201602", "DOCK"),
(0, "C", 422,"201602", "DOCK"),
(1,"B", 3213,"201602", "DOCK"),
(1,"A", 3213,"201602", "DOCK"),
(1,"C", 3213,"201602", "PORT"),
(1,"B", 3213,"201601", "PORT"),
(1,"B", 3213,"201611", "PORT"),
(1,"B", 3213,"201604", "PORT"),
(3,"D", 3999,"201601", "PORT"),
(3,"C", 323,"201602", "PORT"),
(3,"C", 323,"201602", "PORT"),
(3,"C", 323,"201605", "DOCK"),
(3,"A", 323,"201602", "DOCK"),
(2,"C", 2321,"201601", "DOCK"),
(2,"A", 2321,"201602", "PORT")
]
)
df_data = sqlContext.createDataFrame(rdd, ["id","type", "cost", "date", "ship"])

我需要通过id和type进行聚合，得到每组ship的最高发生率例如，

grouped = df_data.groupby('id','type', 'ship').count()

有一列，其中包含每组的次数：

+---+----+----+-----+
| id|type|ship|count|
+---+----+----+-----+
|  3|   A|DOCK|    1|
|  0|   D|DOCK|    2|
|  3|   C|PORT|    2|
|  0|   A|PORT|    3|
|  1|   A|DOCK|    1|
|  1|   B|PORT|    3|
|  3|   C|DOCK|    1|
|  3|   D|PORT|    1|
|  1|   B|DOCK|    1|
|  1|   C|PORT|    1|
|  2|   C|DOCK|    1|
|  0|   C|PORT|    1|
|  0|   C|DOCK|    1|
|  2|   A|PORT|    1|
+---+----+----+-----+

我需要得到

+---+----+----+-----+
| id|type|ship|count|
+---+----+----+-----+
|  0|   D|DOCK|    2|
|  0|   A|PORT|    3|
|  1|   A|DOCK|    1|
|  1|   B|PORT|    3|
|  2|   C|DOCK|    1|
|  2|   A|PORT|    1|
|  3|   C|PORT|    2|
|  3|   A|DOCK|    1|
+---+----+----+-----+

我试着用

grouped.groupby('id', 'type', 'ship')\
.agg({'count':'max'}).orderBy('max(count)', ascending=False).\
groupby('id', 'type', 'ship').agg({'ship':'first'})

但是失败了。有没有方法从组的计数中获得最大行？
在熊猫身上，这艘独桅帆船的作用是：

df_pd = df_data.toPandas()
df_pd_t = df_pd[df_pd['count'] == df_pd.groupby(['id','type', ])['count'].transform(max)]

最佳答案

根据您的预期输出，您似乎只按id和ship分组，因为您在grouped中已经有不同的值，因此将根据id排序的列ship、count和type删除重复元素。
为此，我们可以使用Window函数：

from pyspark.sql.window import Window
from pyspark.sql.functions import rank, col

window = (Window
          .partitionBy(grouped['id'],
                       grouped['ship'])
          .orderBy(grouped['count'].desc(), grouped['type']))


(grouped
 .select('*', rank()
         .over(window)
         .alias('rank'))
  .filter(col('rank') == 1)
  .orderBy(col('id'))
  .dropDuplicates(['id', 'ship', 'count'])
  .drop('rank')
  .show())
+---+----+----+-----+
| id|type|ship|count|
+---+----+----+-----+
|  0|   D|DOCK|    2|
|  0|   A|PORT|    3|
|  1|   A|DOCK|    1|
|  1|   B|PORT|    3|
|  2|   C|DOCK|    1|
|  2|   A|PORT|    1|
|  3|   A|DOCK|    1|
|  3|   C|PORT|    2|
+---+----+----+-----+

关于python - 通过PySpark中的几列从groupby获取具有最大值的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40116117/