返回dataframe中满足一个条件的行，同时修复另一列的值

Question

Genres | Year | Number_Movies
Drama  |2015  | 705
Romance|2015  | 203
Comedy |2015  | 586
Drama  |2014  | 605
Romance|2014  | 293
Comedy |2014  | 786

Genres | Year | Number_Movies
Drama  |2015  | 705
Comedy |2014  | 786

如果可能请帮忙。多谢.

Answer 1

这里有几个可以解决这个问题的选择-

df = spark.createDataFrame([('Drama',2015,705),('Romance',2015,203),('Comedy',2015,586),('Drama',2014,605),('Romance',2014,293),('Comedy ',2014,786)],['Genres','Year','Number_Movies'])

第一个选项：使用窗口函数定义一个等级（分区按年和顺序按Number_Movies desc）。每年数量最多的电影将获得“1”级。

from pyspark.sql.window import Window
from pyspark.sql.functions import row_number,desc

w = Window.partitionBy("Year").orderBy(desc("Number_Movies"))
rank = row_number().over(w).alias('rank')

df.withColumn("rank", rank)\
    .where("rank=1")\
    .drop("rank")\
    .show()

#+-------+----+-------------+
#| Genres|Year|Number_Movies|
#+-------+----+-------------+
#|Comedy |2014|          786|
#|  Drama|2015|          705|
#+-------+----+-------------+

第二个选项：获取每年Number_Movies的最大值，然后self加入dataframe以获取类型。

from pyspark.sql.functions import max,col

joining_condition = [col('a.Year') == col('b.Year'), col('a.max_Number_Movies') == col('b.Number_Movies')]

df.groupBy("Year").\
    agg(max("Number_Movies").alias("max_Number_Movies")).alias("a").\
    join(df.alias("b"),  joining_condition).\
    selectExpr("b.Genres","b.Year","b.Number_Movies").\
    show()

#+-------+----+-------------+
#| Genres|Year|Number_Movies|
#+-------+----+-------------+
#|Comedy |2014|          786|
#|  Drama|2015|          705|
#+-------+----+-------------+