How to get week of month in Spark 3.0+?

user_9276587 0
Viewed 178

I cannot find any datetime formatting pattern to get the week of month in spark 3.0+

As use of 'W' is deprecated, is there a solution to get week of month without using legacy option?

The below code doesn't work for spark 3.2.1

df = df.withColumn("weekofmonth", f.date_format(f.col("Date"), "W"))
apache-spark-3.0 pyspark apache-spark-sql apache-spark datetime
3 Answers

For completeness, it's worth mentioning that one can set the configuration to "LEGACY".

spark.conf.set("spark.sql.legacy.timeParserPolicy", "LEGACY")

from pyspark.sql import functions as F

spark.conf.set("spark.sql.legacy.timeParserPolicy", "LEGACY")

df = spark.createDataFrame(
    [('2022-07-01',),
     ('2022-07-02',),
     ('2022-07-03',)],
    ['Date'])
df.withColumn("weekofmonth", F.date_format(F.col("Date"), "W")).show()
# +----------+-----------+
# |      Date|weekofmonth|
# +----------+-----------+
# |2022-07-01|          1|
# |2022-07-02|          1|
# |2022-07-03|          2|
# +----------+-----------+
user_27535010

you can try using udf:

from pyspark.sql.functions import col,year,month,dayofmonth

df = spark.createDataFrame(
    [(1, "2022-04-22"), (2, "2022-05-12")], ("id", "date"))

from calendar import monthcalendar
def get_week_of_month(year, month, day):
    return next(
        (
            week_number
            for week_number, days_of_week in enumerate(monthcalendar(year, month), start=1)
            if day in days_of_week
        ),
        None,
    )
fn1 = udf(get_week_of_month)
df =df.withColumn('week_of_mon',fn1(year(col('date')),month(col('date')),dayofmonth(col('date'))))
display(df)

enter image description here

user_155931180

If you have table with year, month and week numbers sorted by year and week, you may try my solution:

select 
      year_iso,
      month,
      posexplode(collect_list(week_iso)) as (week_of_month, week_iso)
from your_table_with_dates
group by year_iso, month

Here we just transform column week_iso into array grouped by year_iso and month, and then explodes it backward into two columns (position inside month and week_iso).

Note, positions starts in 0, but its not a real problem.

Screenshots of tables:

user_193815590
Related