CODEWITHSUNDEEP
t-sqlpysparkapache-spark-sqlcase-when.when
Katelyn Raphael
Katelyn Raphael

Reputation: 323

Multiple WHEN condition implementation in Pyspark

I've my T-SQL code below which I've converted in Pyspark but is giving me error

CASE
            WHEN time_on_site.eventaction = 'IN' AND time_on_site.next_action = 'OUT' AND time_on_site.timespent_sec < 72000 THEN 1  --  20 hours 
            WHEN time_on_site.eventaction = 'IN' AND time_on_site.next_action = 'OUT' AND time_on_site.timespent_sec >= 72000 THEN 0
            WHEN time_on_site.eventaction = 'IN' AND time_on_site.next_action = 'IN' AND time_on_site.timespent_sec <= 28800 THEN 2  -- 8 hours
            WHEN time_on_site.eventaction = 'IN' AND time_on_site.next_action = 'IN' AND time_on_site.timespent_sec > 28800 THEN 3
            WHEN time_on_site.type_flag = 'TYPE4' THEN 4
            ELSE NULL
         END AS "type"

Below is my Pyspark script which is throwing an error

from pyspark.sql.functions import when

TOS=TOS.withColumn('type', F.when( (col('eventaction') == 'IN') & (col('next_action') == 'OUT') & ("timespent_sec < 72000") , 1).
                            when( (col('eventaction') == 'IN') & (col('next_action') == 'OUT') & ("timespent_sec >= 72000") , 0).
                            when( (col('eventaction') == 'IN') & (col('next_action') == 'IN') & ("timespent_sec <= 28800") , 2).
                            when( (col('eventaction') == 'IN') & (col('next_action') == 'IN') & ("timespent_sec > 28800") , 3).
                            when(col('type_flag')=='TYPE4', 4).otherwise('NULL')
                            )

Where am I going wrong!?

Upvotes: 8

Views: 31392

Answers (2)

Katelyn Raphael
Katelyn Raphael

Reputation: 323

I've correctly implemented it as shown below

TOS=TOS.withColumn('type', F.when( (F.col("eventaction") == 'IN') & (F.col("next_action") == 'OUT') & (F.col("timespent_sec") < 72000) , 1).
  when( (F.col("eventaction") == 'IN') & (F.col("next_action") == 'OUT') & (F.col("timespent_sec") >= 72000) , 0).
  when( (F.col("eventaction") == 'IN') & (F.col("next_action") == 'IN') & (F.col("timespent_sec") <= 28800) , 2).
  when( (F.col("eventaction") == 'IN') & (F.col("next_action") == 'IN') & (F.col("timespent_sec") > 28800) , 3).
  when(F.col('type_flag')=='TYPE4', 4).otherwise('NULL'))

Upvotes: 10

cph_sto
cph_sto

Reputation: 7607

Nest your 'when' in otherwise(). My braces may not be entirely balanced, so do check them, but the idea is the same.

TOS=TOS.withColumn('type', F.when( (col('eventaction') == 'IN') & (col('next_action') == 'OUT') & ("timespent_sec < 72000") , 1).
                            otherwise( when(   (col('eventaction') == 'IN') & (col('next_action') == 'OUT') & ("timespent_sec >= 72000") , 0).
                            otherwise( when(   (col('eventaction') == 'IN') & (col('next_action') == 'IN') & ("timespent_sec <= 28800") , 2).
                            otherwise( when(   (col('eventaction') == 'IN') & (col('next_action') == 'IN') & ("timespent_sec > 28800") , 3).
                            otherwise( when(   col('type_flag')=='TYPE4', 4).otherwise('NULL'))))))

Upvotes: 15

Related Questions