Q:

Pandas_UDF Concatenation loop with iloc

I'm relatively new in Pandas and recently stuck with a User Defined Function.
My Dataset is like :

|header|planned|
|  a   |   1   |
|  a   |   2   |
|  a   |   3   |
|  a   |   4   |
|  a   |   5   |
|  b   |   1   |
|  b   |   2   |
|  b   |   3   |
|  b   |   4   |
|  b   |   5   |

I have to concatenate values in column planned by groups of two rows to obtain something like this :

|header|planned|p_cat|
|  a   |   1   | 1_2 | 
|  a   |   2   | 2_3 |
|  a   |   3   | 3_4 |
|  a   |   4   | 4_5 |
|  a   |   5   |     |
|  b   |   1   | 1_2 |
|  b   |   2   | 2_3 |
|  b   |   3   | 3_4 |
|  b   |   4   | 4_5 |
|  b   |   5   |     |

Numbers in column planned are not in this specific order but will always be integers.
My UDF:

schema = ds_adh.schema

    @pandas_udf(schema, PandasUDFType.GROUPED_MAP)
    def concat_operations(ds_op):

        s = ds_op['planned']

        for index in range(ds_op['planned'].count()-1):
        
            # clearly working only for the last index
            couple = str([s.iloc[index]]) + '_' + str([s.iloc[index+1]])

        ds_op_new = ds_op

        ds_op_new ['p_cat'] = couple

        return ds_op_new

ds_adh = ds_adh.orderBy("time")
ds_adh = ds_adh.groupBy("header").apply(concat_operations)

My problems :

The concatenation itself doesn't work
I don't know how to push concatenations in couple for all the iterations of the loop

I also tried with pandaSeries without any success.
Here is the error I get with this code :

IndexError: single positional indexer is out-of-bounds

A:

Use inbuilt window lead function with partitionBy on header and orderBy on planned column, As udf will decrease the performance.
from pyspark.sql import *
from pyspark.sql.functions import *
w=Window.partitionBy("header").orderBy("planned")
df.withColumn("p_cat", when(lead(col("planned"),1).over(w).isNull(),lit("")).otherwise(concat_ws("_",col("planned"),lead(col("planned"),1).over(w)))).show()
#+------+-------+-----+
#|header|planned|p_cat|
#+------+-------+-----+
#|     a|      1|  1_2|
#|     a|      2|  2_3|
#|     a|      3|  3_4|
#|     a|      4|  4_5|
#|     a|      5|     |
#|     b|      1|  1_2|
#|     b|      2|  2_3|
#|     b|      3|  3_4|
#|     b|      4|  4_5|
#|     b|      5|     |
#+------+-------+-----+

