修复日期在Pyspark据框设定最低价值

Question 1

我有一个数据架与时间戳场RECEIPTDATEREQUESTED：时间戳由于某些原因，有日期都不到1900-01-01. 我不想要这些，我想要做的，是为每一个值中所列的数据框在RECEIPTDATEREQUESTED<'1900-01-01 00:00:00'然后设定的时间戳到1900-01-01或空。我已经尝试了一些方法可以做到这一点，但这似乎更多一些简单必须存在。我认为像这样的东西可能的工作，但是

import datetime
def testdate(date_value):
    oldest = datetime.datetime.strptime('1900-01-01 00:00:00', '%Y-%m-%d')
    try:
        if (date_value < oldest):
            return oldest
        else:
            return date_value
    except ValueError:
        return oldest
udf_testdate = udf(lambda x:testdate(x),TimestampType())
bdf = olddf.withColumn("RECEIPTDATEREQUESTED",udf_testdate(col("RECEIPTDATEREQUESTED")))

Question 2

你可以使用有条件评估使用 when and otherwise 设置 RECEIPTDATEREQUESTED 要么 null 或 1900-01-01 00:00:00 whenerver值 < '1900-01-01 00:00:00'.


from pyspark.sql import functions as F

data = [("1000-01-01 00:00:00",), 
        ("1899-12-31 23:59:59",),
        ("1900-01-01 00:00:00",), 
        ("1901-01-01 00:00:00",)]

df = spark.createDataFrame(data, ("RECEIPTDATEREQUESTED",))\
          .withColumn("RECEIPTDATEREQUESTED", F.to_timestamp(F.col("RECEIPTDATEREQUESTED")))


# Fill null

df.withColumn("RECEIPTDATEREQUESTED", 
              F.when(F.col("RECEIPTDATEREQUESTED") < "1900-01-01 00:00:00", F.lit(None))
               .otherwise(F.col("RECEIPTDATEREQUESTED")))\
  .show(200, False)

# Fill default value

df.withColumn("RECEIPTDATEREQUESTED", 
              F.when(F.col("RECEIPTDATEREQUESTED") < "1900-01-01 00:00:00", F.lit("1900-01-01 00:00:00").cast("timestamp"))
               .otherwise(F.col("RECEIPTDATEREQUESTED")))\
  .show(200, False)

输出

填充 null

+--------------------+
|RECEIPTDATEREQUESTED|
+--------------------+
|null                |
|null                |
|1900-01-01 00:00:00 |
|1901-01-01 00:00:00 |
+--------------------+

填充 1900-01-01 00:00:00

+--------------------+
|RECEIPTDATEREQUESTED|
+--------------------+
|1900-01-01 00:00:00 |
|1900-01-01 00:00:00 |
|1900-01-01 00:00:00 |
|1901-01-01 00:00:00 |
+--------------------+

Nithish · Answer 1 · 2021-11-23T20:53:37

你可以使用有条件评估使用 when and otherwise 设置 RECEIPTDATEREQUESTED 要么 null 或 1900-01-01 00:00:00 whenerver值 < '1900-01-01 00:00:00'.


from pyspark.sql import functions as F

data = [("1000-01-01 00:00:00",), 
        ("1899-12-31 23:59:59",),
        ("1900-01-01 00:00:00",), 
        ("1901-01-01 00:00:00",)]

df = spark.createDataFrame(data, ("RECEIPTDATEREQUESTED",))\
          .withColumn("RECEIPTDATEREQUESTED", F.to_timestamp(F.col("RECEIPTDATEREQUESTED")))


# Fill null

df.withColumn("RECEIPTDATEREQUESTED", 
              F.when(F.col("RECEIPTDATEREQUESTED") < "1900-01-01 00:00:00", F.lit(None))
               .otherwise(F.col("RECEIPTDATEREQUESTED")))\
  .show(200, False)

# Fill default value

df.withColumn("RECEIPTDATEREQUESTED", 
              F.when(F.col("RECEIPTDATEREQUESTED") < "1900-01-01 00:00:00", F.lit("1900-01-01 00:00:00").cast("timestamp"))
               .otherwise(F.col("RECEIPTDATEREQUESTED")))\
  .show(200, False)

输出

填充 null

+--------------------+
|RECEIPTDATEREQUESTED|
+--------------------+
|null                |
|null                |
|1900-01-01 00:00:00 |
|1901-01-01 00:00:00 |
+--------------------+

填充 1900-01-01 00:00:00

+--------------------+
|RECEIPTDATEREQUESTED|
+--------------------+
|1900-01-01 00:00:00 |
|1900-01-01 00:00:00 |
|1900-01-01 00:00:00 |
|1901-01-01 00:00:00 |
+--------------------+

是的。谢谢你。我不知道该怎样或为什么这一工作，所以我google这更多一些但是谢谢你。

修复日期在Pyspark据框设定最低价值

的问题

最好的答案

输出

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别