Label encoding of date and create new attribute based on quarter using pyspark

user_16154913 0
Viewed 108

I have a date and want to create a new attributes based on the date. For example.

 date                    quarter1    quarter2    quarter3   quarter4
2/3/2020(mm/dd/yyyy)        1            0.           0.        0
11/11/2020                  0            0            0         1
pyspark apache-spark
1 Answers

You can try to cast the date column and use quarter function, then apply an when + otherwise condition to create the columns:

from pyspark.sql import functions as F

qtrs = ['quarter1','quarter2','quarter3','quarter4']
df = df.select("*",F.concat(F.lit("quarter"),
                      F.quarter(F.to_date("date",'M/d/yyyy'))).alias("quarters"))\
.select("*",*[F.when(F.col("quarters")==col,1).otherwise(0).alias(col) for col in qtrs])\
.drop("quarters")

df.show()
+----------+--------+--------+--------+--------+
|      date|quarter1|quarter2|quarter3|quarter4|
+----------+--------+--------+--------+--------+
|  2/3/2020|       1|       0|       0|       0|
|11/11/2020|       0|       0|       0|       1|
+----------+--------+--------+--------+--------+

Per OP's request, adding approach with withColumn:

df = (df.withColumn("quarters",F.concat(F.lit("quarter"),
                      F.quarter(F.to_date("date",'M/d/yyyy'))))
   .withColumn("quarter1",F.when(F.col("quarters")=='quarter1',1).otherwise(0))
   .withColumn("quarter2",F.when(F.col("quarters")=='quarter2',1).otherwise(0))
   .withColumn("quarter3",F.when(F.col("quarters")=='quarter3',1).otherwise(0))
   .withColumn("quarter4",F.when(F.col("quarters")=='quarter4',1).otherwise(0))
   .drop("quarters")
)

df.show()
user_98406370
Related