Pyspark Dataframe - Counting baklog each last day of the month

user_16439080 0
Viewed 150

I'm trying to looking for a solution to one problem. I've the following df:

task open_date close_date
t01 05/03/2021 16/03/2021
t02 07/03/2021 13/04/2021
t03 23/03/2021 04/04/2021

I need a new df with the backlog at the end of the month (opened that month and closed after it):

end_of_month backlog
31/03/2021 2

I'm playing with last_day() to get the EOM but I'm not reaching with the query/code to getting the right count. Could you help me? Thanks in advance!!

backlog pyspark dataframe count python
2 Answers
    df
    .withColumn('open_month_next_month', add_months('open_date', 1))
    .filter(last_day('open_month_next_month') == last_day('close_date'))
    .withColumn('end_of_month', last_day('open_date'))
    .groupBy('end_of_month')
    .agg(F.count(F.lit(1)).alias('backlog'))
user_73610070 
from pyspark.sql import functions as F
from pyspark.sql.window import Window

data = [("t01", "05/03/2021", "16/03/2021"),
        ("t02", "07/03/2021", "13/04/2021"),
        ("t03", "23/03/2021", "04/04/2021"),
        ("t04", "07/03/2021", "13/05/2021"),
        ("t05", "23/03/2021", "04/05/2021")]
schema = ["TaskID", "TaskstartDate", "TaskEndDate"]

df = spark.createDataFrame(data, schema)

df = df.withColumn("TaskstartDate",F.to_date(F.col("TaskstartDate"), "dd/MM/yyyy"))\
.withColumn("TaskEndDate",F.to_date(F.col("TaskEndDate"), "dd/MM/yyyy"))

df_grouped = df.withColumn("EndDay_fromStartDate", F.last_day(F.col("TaskstartDate")))\
.withColumn("EndDay_fromEndDate", F.last_day(F.col("TaskEndDate")))\
.filter((F.col("EndDay_fromStartDate") != F.col("EndDay_fromEndDate")) & (F.col("TaskstartDate") < F.col("TaskEndDate")))\
.groupBy('EndDay_fromStartDate')\
.agg(F.count(F.lit(1)).alias('backlog'))

df_grouped.show()
user_53578740
Related