Mark only first change in column values for a group PySpark

user_5073574 0
Viewed 25

I have a dataframe as follows:

+---------+-----+----------+
|  tx_grp |offer|old_offer |
+---------+-----+----------+
|Company_B|   10|      null| 
|Company_B|   10|      null| 
|Company_B|   12|        10|  
|Company_B|   12|        10|  
|Company_A|  101|      null| 
|Company_A|  101|      null|
|Company_A|  109|       101| 
|Company_A|  109|       101| 
+---------+-----+----------+

I tried: df = df.withColumn('isChanged',when(F.col('offer')!=F.col('old_offer'),'yes').otherwise(0))

but I get:

+---------+-----+----------+---------+
|  tx_grp |offer|old_offer |isChanged|
+---------+-----+----------+---------+
|Company_B|   10|      null|   0     |
|Company_B|   10|      null|   0     |
|Company_B|   12|        10| yes     |
|Company_B|   12|        10| yes     |
|Company_A|  101|      null|   0     |
|Company_A|  109|       101|  yes    |
|Company_A|  109|       101|  yes    |
|Company_A|  109|       101|  yes    |
+---------+-----+----------+---------+

I want to mark only first event of the change, how can I achieve that. what I want to have is:

+---------+-----+----------+---------+
|  tx_grp |offer|old_offer |isChanged|
+---------+-----+----------+---------+
|Company_B|   10|      null|   0     |
|Company_B|   10|      null|   0     |
|Company_B|   12|        10|  yes    |
|Company_B|   12|        10|   0     |
|Company_A|  101|      null|   0     |
|Company_A|  109|       101|  yes    |
|Company_A|  109|       101|   0     |
|Company_A|  109|       101|   0     |
+---------+-----+----------+---------+
pyspark group-by
1 Answers

Use window functions

 w=Window.partitionBy('tx_grp').orderBy(desc('tx_grp'))
    
(df.withColumn('ischanged', lag('old_offer').over(w)).na.fill(0).withColumn('ischanged', when(col('ischanged')==col('old_offer'),'0').otherwise('yes'))
).show()
user_89869750
Related