PySpark中如何用原始失败日期替代null填充时间戳

Hey there, let's tackle this date formatting issue in Spark. The core idea is to use a conditional check: if converting the date string to a timestamp returns null (meaning the format is invalid), we keep the original string value; otherwise, we use the properly converted timestamp.

Step 1: Create Sample DataFrame #

First, let's set up the sample DataFrame matching your scenario:

Scala Version #

val df = Seq(
  (1, "161129"),
  (2, "961209"),
  (3, "110620"),
  (4, "160928"),
  (5, "021002"),
  (6, "160421"),
  (7, "160121"),
  (8, "100903"),
  (9, "077707")
).toDF("id", "date")

PySpark Version #

df = spark.createDataFrame([
    (1, "161129"),
    (2, "961209"),
    (3, "110620"),
    (4, "160928"),
    (5, "021002"),
    (6, "160421"),
    (7, "160121"),
    (8, "100903"),
    (9, "077707")
], ["id", "date"])

Step 2: Apply Conditional Transformation #

We'll use Spark's when function to handle the logic:

Scala Version #

import org.apache.spark.sql.functions.{when, unix_timestamp, col}

val resultDF = df.withColumn(
  "date",
  when(
    unix_timestamp(col("date"), "yymmdd").isNull,
    col("date")  // Keep original string for invalid dates
  ).otherwise(
    unix_timestamp(col("date"), "yymmdd").cast("timestamp")  // Convert valid dates to timestamp
  )
)

resultDF.show(false)

PySpark Version #

from pyspark.sql.functions import when, unix_timestamp, col

result_df = df.withColumn(
    "date",
    when(
        unix_timestamp(col("date"), "yymmdd").isNull(),
        col("date")
    ).otherwise(
        unix_timestamp(col("date"), "yymmdd").cast("timestamp")
    )
)

result_df.show(truncate=False)

Step 3: Verify the Output #

Running the code above will produce exactly the output you're expecting:

+---+-------------------+
| id| date|
+---+-------------------+
| 1|2016-11-29 00:00:00|
| 2|1996-12-09 00:00:00|
| 3|2011-06-20 00:00:00|
| 4|2016-09-28 00:00:00|
| 5|2002-10-02 00:00:00|
| 6|2016-04-21 00:00:00|
| 7|2016-01-21 00:00:00|
| 8|2010-09-03 00:00:00|
| 9|077707 |
+---+-------------------+

How It Works #

• unix_timestamp(col("date"), "yymmdd") tries to parse the date string using the yymmdd format. Invalid dates (like "077707" where the month/day is invalid) return null.
• The when clause checks if the parsed value is null: if yes, it retains the original date string; if no, it casts the parsed value to a timestamp type for the standard format.

内容的提问来源于stack exchange，提问作者user8510536