I would like to divide complete column in dataframe with one particular column value in same dataframe using pyspark

nainika George 0
Viewed 32

I have a pyspark dataframe as below. I want to obtain values in index column with pyspark functionalities.

Period cp1 cp2 index
2022-12-31 70.07 22.1
2021-09-19 70.38 Null
1992-01-03 80.7 Null
1975-02-05 55.23 Null
2000-08-08 22.2 Null

index = every value in cp1 column/with only value in cp2 i.e 22.10

Can someone help me out with this ?

pyspark apache-spark
1 Answers

index like this.

data= [
('2022-12-31', 70.07, 22.1), 
('2021-09-19', 70.38, None), 
('1992-01-03', 80.7, None), 
('1975-02-05', 55.23, None), 
('2000-08-08', 22.2, None),
]

df = spark.createDataFrame(data, ['Period', 'cp1', 'cp2'])

import sys
max_value = sys.maxsize
min_value = -sys.maxsize - 1
(
    df
        .withColumn("only_value", max("cp2").over(Window.orderBy(desc("Period")).rowsBetween(min_value, max_value)))
        .withColumn("index", col("cp1") / col("only_value"))
    .show(10, False)
)
# +----------+-----+----+----------+------------------+
# |Period    |cp1  |cp2 |only_value|index             |
# +----------+-----+----+----------+------------------+
# |2023-12-31|76.07|null|22.1      |3.4420814479638002|
# |2022-12-31|70.07|22.1|22.1      |3.170588235294117 |
# |2021-09-19|70.38|null|22.1      |3.1846153846153844|
# |2000-08-08|22.2 |null|22.1      |1.004524886877828 |
# |1992-01-03|80.7 |null|22.1      |3.6515837104072397|
# |1975-02-05|55.23|null|22.1      |2.499095022624434 |
# +----------+-----+----+----------+------------------+
userxU0k10
Related