[Python] Unexpected behavior when chaining multiple .when() conditions

Context: I am building a date parser which will intake a column of values and attempt to match it to various date formats.

For example: 2025-04-01 or 2025-04-01T22:23:01.09123 or 20250401.

Problem: The current setup was working correctly, until I added a condition to parse unix epoch times. The error itself didn't show up when I added the condition, but when I attempted to parse the column which is of datatype Longtype. The value matches on patterns that it should not match on. It only works properly if then additional when clauses are not included.

Code: This works:


parsed_ts = (when(col(my_col).rlike("^(\\d{10,13})(?:\\.\\d+)?$"), from_unixtime(col(my_col)))
.otherwise(lit(None).cast("timestamp"))
)
df = df.withColumn("new_col", parsed_ts)


This does not work:

parsed_ts = (when(col(my_col).rlike("^(\\d{10,13})(?:\\.\\d+)?$"), from_unixtime(col(my_col)))
.when(col(my_col).rlike("^\\d{4}-\\d{2}-\\d{2}$"), to_timestamp(col(my_col), "yyyy-MM-dd"))
.when(col(my_col).rlike("^\\d{4}/\\d{2}/\\d{2}$"), to_timestamp(col(my_col), "yyyy/MM/dd"))
.when(col(my_col).rlike("^(\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2})$"), to_timestamp(col(my_col), "yyyy-MM-dd'T'HH:mm:ss"))
.when(col(my_col).rlike("^(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2})$"), to_timestamp(col(my_col), "yyyy-MM-dd HH:mm:ss"))
.when(col(my_col).rlike("^(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\.\\d{3})$"), to_timestamp(col(my_col), "yyyy-MM-dd HH:mm:ss.SSS"))
.when(col(my_col).rlike("^(\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{3}Z)$"), to_timestamp(regexp_replace(col(my_col), "Z", ""), "yyyy-MM-dd'T'HH:mm:ss.SSS"))
.when(col(my_col).rlike("^(\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{3})$"), to_timestamp(col(my_col), "yyyy-MM-dd'T'HH:mm:ss.SSS"))
.otherwise(lit(None).cast("timestamp"))
)
df = df.withColumn("new_col", parsed_ts)


Error Message:

Error Category: UNCLASSIFIED_ERROR; Failed Line Number: 73; An error occurred while calling o522.withColumn. [INTERNAL_ERROR] Cannot resolve the runtime replaceable expression 'to_timestamp(my_col, yyyy-MM-dd)'. The replacement has been unresolved: 'to_timestamp(my_col, yyyy-MM-dd)'


I removed some sensitive data from the error message.

I attempted to cast the column to a string instead of a longtype, and I moved the unix when condition to the top to attempt to catch it earlier. I am expecting the data to be matched on the first condition and changed into a timestamp.

Continue reading...