Pyspark - how to check which of the two records has the latest date and its column value?

user_7644168 0
Viewed 36

I have a DataFrame and want to check that a record with the latest date has a certain value. For example, for the below DF I need to check the records with Id 'B30' and get the record with the metric_1 == 0.25.

original_metrics_df = self.spark.createDataFrame(
    [('A10', -0.35, '2020-01-04'),
     ('A20', -0.20, '2017-05-01'),
     ('B30', 0.59, '2018-02-08'),
     ('B30', 0.25, '2019-05-01')
     ],
    ['id', 'metric_1', 'transaction_date']
)

First I did the filtering to retain only records with Id B30:

filtered_metrics_df = original_metrics_df.select('id','metric_1').filter(F.col('metric_1') == 'B30')

How should I apply filtering further to get only a metric_1 value of this record ('B30', 0.25, '2019-05-01')?

pyspark apache-spark-sql python-3.x python
1 Answers

You can try on a window :

If the transaction_date is not a date column convert to date:

original_metrics_df = original_metrics_df.withColumn("transaction_date",
                                F.to_date("transaction_date"))

Then we can get max of date over a window and select the desired condition:

w = Window.partitionBy("id")
cond = (F.col("id")=="B30") & (F.col("transaction_date")==F.col("Latest_date"))

(original_metrics_df.withColumn("Latest_date",F.max("transaction_date").over(w))
 .filter(cond).drop("Latest_date")).show()

+---+--------+----------------+
| id|metric_1|transaction_date|
+---+--------+----------------+
|B30|    0.25|      2019-05-01|
+---+--------+----------------+

Or , filter on the known condition first and then do the same operation:

w = Window.partitionBy("id")

(original_metrics_df.filter(F.col("id")=="B30")
  .withColumn("Latest_date",F.max("transaction_date").over(w))
  .filter(F.col("transaction_date")==F.col("Latest_date"))).show()
user_98406370
Related