Creating a new column in pyspark dataframe using another column values from future

user_16093565 0
Viewed 334

I have a dataframe with 3-columns and I wanted to create a new column using values of column projection.
How the values should be picked from projection?--It should pick 3-consective values of projection year wise for ex - if you are creating a new column for year 2020 the value it should pick from 2021,2022,2023

I have tried below sql:

spark.sql(''' select serial_number,pit_pd_proj as proj1,year,lead(pit_pd_proj,3) over(partition by serial_number order by year) as proj2 from table where serial_number = 1 order by year''').show(50,truncate=False)

but this is not the complete answer

I wanted the projection2 column as the output

pyspark apache-spark-sql apache-spark
2 Answers

lag functions can be used to get previous values (before that proper sort / order by is needed). https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.sql.functions.lag.html

my_window = Window.partitionBy().orderBy("year")
df = df.withColumn("prev_value", F.lag(df.projection).over(my_window))

If you add to question example table with desired output (as there is only source) I can try to replicate it and update answer (above is not tested).

user_109729590

This can be accomplished by making use of window function and row frame.I am providing the answer in Scala ,but definitely the same can be implemented using Python as well.

import org.apache.spark.sql.expressions.Window

val df=Seq(("1","358934","2020"),("1","38639","2021"),("1","983434","2022"),("1","234","2023"),("1","2325","2024"),("1","4545","2025"),("1","7675","2026")).toDF("serial_number","projection","year")

val partitionExpr=Window.orderBy("year").rowsBetween(1, 3)

df.withColumn("total",sum("projection") over(partitionExpr)).show(false)

For additional information on Row and Range,please refer to https://databricks.com/blog/2015/07/15/introducing-window-functions-in-spark-sql.html

user_91016560
Related