Add new column based on existing column with concat values Spark dataframe

Question

I want to new column in my dataframe based on below condition.

My dataframe is like this:

my_string 

2020 test 

2020 prod 

2020 dev

My condition:

value1=subtract string after space from my_string

value2=subtract first four digit from my_string

If value 1 contains string 'test' then new_col=value2+"01"

If value 1 contains string 'prod' then new_col=value2+"kk"

If value 1 contains string 'dev' then new_col=value2+"ff"

I need result like this:

my_string       |  new_col

2020 test        | 202001

2020 prod        | 2020kk 

2020 dev        | 2020ff

Please can someone help me out?

can you please format your input & expected output something like tabular format ? so that it is readable & anyone can you give easy solution .. :) — s.polam
– s.polam, Commented May 15, 2020 at 15:07

notNull · Accepted Answer · 2020-05-15 15:24:55Z

1

Use row_number window function with monotonically_increasing_id()

from pyspark.sql import *
from pyspark.sql.functions import *
w = Window.orderBy(monotonically_increasing_id())
df.withColumn("new_col",concat(split(col("my_string")," ")[0], lpad(row_number().over(w),2,"0"))).show()

#+---------+-------+
#|my_string|new_col|
#+---------+-------+
#|2020 test| 202001|
#|2020 prod| 202002|
#| 2020 dev| 202003|
#+---------+-------+

UPDATE:

Use when+otherwise statement.

df.withColumn("dyn_col",when(lower(split(col("my_string")," ")[1]) =="prod","kk").\
when(lower(split(col("my_string")," ")[1]) =="dev","ff").\
when(lower(split(col("my_string")," ")[1]) =="test","01").\
otherwise("null")).\
withColumn("new_col",concat(split(col("my_string")," ")[0], col("dyn_col"))).\
drop("dyn_col").\
show()
#+---------+-------+
#|my_string|new_col|
#+---------+-------+
#|2020 test| 202001|
#|2020 prod| 2020kk|
#| 2020 dev| 2020ff|
#+---------+-------+

In Scala:

df.withColumn("dyn_col",when(lower(split(col("my_string")," ")(1)) ==="prod","kk").
when(lower(split(col("my_string")," ")(1)) ==="dev","ff").
when(lower(split(col("my_string")," ")(1)) ==="test","01").
otherwise("null")).
withColumn("new_col",concat(split(col("my_string")," ")(0), col("dyn_col"))).
drop("dyn_col").
show()

//+---------+-------+
//|my_string|new_col|
//+---------+-------+
//|2020 test| 202001|
//|2020 prod| 2020kk|
//| 2020 dev| 2020ff|
//+---------+-------+

edited May 15, 2020 at 15:24

answered May 15, 2020 at 15:13

notNull

31.8k4 gold badges41 silver badges58 bronze badges

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

2 Comments

User6006 Over a year ago

it's not row_number i just gave sample it may vary like any string edited again

User6006 Over a year ago

Thanks you so much i will try

Collectives™ on Stack Overflow

Add new column based on existing column with concat values Spark dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related