Mapping respective values to respective dates

Question

Input:

Id	FirstDate	LastDate	values
1	2023-12-10	2023-12-14	2,4,6,7,8
2	2024-01-12	2023-12-21	0,0,16,2,7,0,1,1,2,3

Expected OutPut:

Id	FirstDateAndLastDate	value
1	2023-12-10	2
1	2023-12-11	4
1	2023-12-12	6
1	2023-12-13	7
1	2023-12-14	8
2	2024-01-12	0
2	2024-01-13	0
2	2024-01-14	16
2	2024-01-15	2
2	2024-01-16	7
2	2024-01-17	0
2	2024-01-18	1
2	2024-01-19	1
2	2024-01-20	2
2	2024-01-21	3

I am able to get dates as per expected but I am not able to map respective values in front of respective dates.

df.withColumn("FirstDateAndLastDate", explode(expr("sequence(to_date(FirstDate), to_date(LastDate), interval 1 day)")))

When I explode it separates the data in rows but not for respective dates.

df.withColumn("new_value", explode(split(col("values"), ",")))

Last date for ID 2 should be 2024-12-21 right? So that each token in the delimited values corresponds to a single day in the interval? — Error_2646
– Error_2646, Commented Jan 19, 2024 at 17:02
stackoverflow.com/a/57769250/2956135 solution worked partially. problem statement is to assign values to respective dates. From this solution I am able to explode values but it is creating duplicate records. Every single date have combination of all values. — Sandesh
– Sandesh, Commented Jan 20, 2024 at 10:05

MUK · Accepted Answer · 2024-03-06 06:24:03Z

0

Given start_date and end_date are of type datetime.

def generate_dates(start_date, end_date, values):
    current_date = start_date
    date_values = []

    while current_date <= end_date:
        date_values.append((current_date, values[len(date_values) % len(values)]))
        current_date += relativedelta.relativedelta(days=1)

    return date_values

answered Mar 6, 2024 at 6:24

MUK

4216 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mapping respective values to respective dates

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related