Replace column value based other column values pyspark data frame

Question

I have the following spark data frame.

Date_1         Value     Date_2 
20-10-2021       1        Date 
20-10-2021       2        Date 
21-10-2021       3        Date 
23-10-2021       4        Date

I would like to fill Date_2 values by adding Date_1 + (Value-1).

The output that I would like to see is the following.

Date_1         Value     Date_2 
20-10-2021       1        20-10-2021
20-10-2021       2        21-10-2021 
21-10-2021       3        23-10-2021
23-10-2021       4        26-10-2021

I have tried this using pyspark.

import pyspark.sql.functions as F

df = df.withColumn('Date_2', F.date_add(df['Date_1'], (df['Value'] -1)).show()

But I am getting TypeError: Column is not iterable.

Can anyone help with this?

Pav3k · Accepted Answer · 2021-10-20 07:46:49Z

1

You would need to parse SQL function DATE_ADD like this:

(
    df
    .withColumn("Value", F.col("Value").cast("int"))
    .withColumn("Date_2", 
                F.expr('DATE_ADD(Date_1, Value - 1)')
               )
)

DATE_ADD(Date_1, Value - 1) will add to each row in Date_1 column value from column Value -1 (counting in days).

Additionally (if you don't have it done yet) Value columns should be INT. If you would have there for example DOUBLE type, AnalysisException occur.

answered Oct 20, 2021 at 7:46

Pav3k

9196 silver badges11 bronze badges

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

4 Comments

Hiwot Over a year ago

This is perfect! Thanks.

Hiwot Over a year ago

when I take this solution to real one I am getting a strange error. Says AttributeError: '_io.TextIOWrapper' object has no attribute 'col' do you have any idea about this col?

Pav3k Over a year ago

Do you use F.col("Value") where F is from pyspark.sql import functions as F? I see similar error only if I replaced "F.col()" into "df.col()": AttributeError: 'DataFrame' object has no attribute 'col'

Pav3k Over a year ago

Don't you accidentally use f.col() instead F.col() where f in your case is something like this f = open(file_with_data)?

Steven · Accepted Answer · 2021-10-20 07:45:48Z

1

The signature of the function date_add is (col, int). Therefore, you cannot use directly df['Value'].

try this :

df = df.withColumn('Date_2', F.expr("date_add(Date_1, Value -1)")).show()

edited Oct 20, 2021 at 7:45

answered Oct 20, 2021 at 7:37

Steven

15.4k7 gold badges49 silver badges80 bronze badges

1 Comment

Hiwot Over a year ago

Can you make it a bit clear where the quotation which starts on date_add ends? AND Can it iterate on Value-1 as well?

Collectives™ on Stack Overflow

Replace column value based other column values pyspark data frame

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related