如何将PySpark DataFrame的字符串日期列转为日期类型

核心问题 #

尝试将PySpark DataFrame中的字符串格式日期列转换为日期类型，参考多个已解决问题并尝试多种代码写法，转换后的日期列始终显示null值。

问题排查过程 #

• 更新1：发现部分数据使用M/dd/yyyy格式可正常解析，但后续出现Fail to parse '4/2/2012' in the new parser错误，该日期需使用M/d/yyyy格式，求兼容多格式的解析方法。
• 更新2：在Excel中为日期补前导零统一格式后，PySpark仍无法解析03/23/12，报错Fail to parse '03/23/12'。
• 更新3：尝试通过RDD映射解析日期，但无法将RDD转回DataFrame，toDF()方法无法直接使用，相关代码如下：

# 尝试用RDD解析日期字符串
def cleanDates(date_time_str):
      date_time_obj = datetime.datetime.strptime(date_time_str, '%m/%d/%Y')
      fields = date_time_obj.split('/')
      month = fields[0]
      day = fields[1]
      year = fields[2]
      return month, day, year

amazonDates = amazon_train.select("Date")
amazonDates.show()

rdd_amazon = amazonDates.rdd.map(lambda x: cleanDates(x))
# rdd_amazon.collect() # 无效
# rdd_amazon.sc.parallelize() # 无效
type(rdd_amazon) # 输出为pipeline rdd
sc.toDF(rdd_amazon) # sc为Spark上下文

# 以下尝试均无效：
# rdd_amazon = amazon_mapping.toDF() 
# rdd_amazon = rdd_amazon.flatMap(lambda x: cleanDates(x))
# amazon_train = amazon_train.withColumn('Date', rdd_amazon)
# amazon_train = amazon_train.withColumn("Date", to_date('Date', "MM/dd/yyyy"))
# amazon_train.show()
# amazon_train.printSchema()
# amazon_train.groupBy(year("date").alias('Year')).agg({'Close': 'mean'}).show()

已尝试的无效转换代码 #

以下多种转换方式均未成功：

# 这些方法都无效：

amazon_train.select(col("Date"),to_date(col("Date"),"MM-dd-yyyy").alias("date")).show()
# amazon_train.select(to_date(amazon_train.Date, 'yyyy-MM-dd HH:mm:ss').alias('date')).collect()
amazon_train.withColumn("New Date",expr("to_date(Date, yyyy-MM-dd)")).show()
amazon_train.withColumn(amazon_train.select(to_timestamp("Date", 'yyyy-MM-dd'))).alias('New Date').show()
amazon_train.select("Date").show()
amazon_train.Date = to_timestamp(amazon_train.Date, 'yyyy-MM-dd').alias('New Date').collect()
amazon_train = amazon_train.withColumn('New Date', to_date(unix_timestamp(col('Date'), 'MM-dd-yyyy').cast("timestamp")))
amazon_train = amazon_train.withColumn('col_with_date_format',sf.to_date(amazon_train.Date))
amazon_train = amazon_train.withColumn("Date", amazon_train["Date"].cast(DateType()))
amazon_train.select(date_format('Date', 'MM-dd-yyy').alias('newFormat')).show()
amazon_train.select(date_format(unix_timestamp("Date", "MM-dd-yyyy").cast("timestamp"), "MM-dd-yyyy")).show()
amazon_train.withColumn('New Date', F.date_format(F.to_date('Date', "MM/dd/yyyy"),'MM-dd-yyyy')).show()
F.date_format(F.to_date(amazon_train["Date"], "MM/dd/yyyy"), "MM-dd-yyyy")
amazon_train["Date"].cast(DateType())
amazon_train = amazon_train.withColumn("New Dates", date_format(to_date(col("Date"),"MM/dd/yyyy"),"MM-dd-yyyy"))
date_format(to_date(amazon_train.Date,"MM/dd/yyyy"),"MM/dd/yyyy")

示例错误输出 #

转换后的DataFrame日期列全部为null，示例输出如下：

+----+----------+----------+----------+----------+----------+-------+
|date|      Open|      High|       Low|     Close| Adj Close| Volume|
+----+----------+----------+----------+----------+----------+-------+
|null|192.009995|196.199997|191.800003|195.039993|195.039993|5984000|
|null|196.479996|202.970001|     195.5|202.869995|202.869995|7613700|
|null|203.589996|209.850006|202.880005|205.440002|205.440002|9600800|
|null|206.139999|     207.0|200.309998|201.160004|201.160004|6245000|
|null|201.279999|205.309998|200.630005|204.610001|204.610001|5711200|
+----+----------+----------+----------+----------+----------+-------+
only showing top 5 rows

root
 |-- date: date (nullable = true)
 |-- Open: double (nullable = true)
 |-- High: double (nullable = true)
 |-- Low: double (nullable = true)
 |-- Close: double (nullable = true)
 |-- Adj Close: double (nullable = true)
 |-- Volume: integer (nullable = true)

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