using date_sub function dynamically to filter - PySpark

user_16798185 0
Viewed 360

I have a application which provides the application_run_date as string, which I was planning to convert to date format and use the same to apply the filter on dataframe. However I am running into an issue mentioned below.

Input Dataframe:

        raw_dataframe = spark.createDataFrame(
        [
         (999,"2021-09-01","Buy_A",10),
         (999,"2021-09-02","Buy_B",15),
         (999,"2021-09-03","Buy_C",20),
         (999,"2011-09-04","Buy_D",100)
        ],
        ("cust", "cust_date","purchase","value")
        )

Error:

Reason: [ cannot resolve '`2021-09-04`' given input columns: [cust, cust_date, purchase, value] ]

Code:

from pyspark.sql.functions import *
from pyspark.sql.functions import date_sub
from pyspark.sql import DataFrame

run_date = to_date(application_run_date,'yyyy-MM-dd')  # application_run_date is string, in this case provides a value 2021-09-04 as string
days_to_subtract =1 

final_data = raw_dataframe.filter(raw_dataframe["cust_date"].between(date_sub(run_date, days_to_subtract), run_date))

Can anyone please help?

pyspark python-3.x python
1 Answers

to_date takes as first parameter a column. If you provide a string (for example '2021-09-04') this string will be interpreted as column name, not as value.

If you want to string to be interpreted as value you have to convert it to a constant column using lit.

from pyspark.sql import functions as F
run_date = F.to_date(F.lit(application_run_date),'yyyy-MM-dd')

As a side note: you should avoid using from pyspark.sql.functions import * as it might hide standard functions like min or max. It is better to use from pyspark.sql import functions as F.

user_21298010
Related