Need help to transform a column of a dataframe into multiple columns for timestamps

user_7800137 0
Viewed 52

Hello everyone need one help regarding the below question,

My dataframe looks like below and I am using pyspark,

enter image description here

the time column needs to be split into two columns 'start time' and 'end time' like below,

enter image description here

I tried couple of methods like the self joining the df on m_id but it looks very tedious and inefficient, I would appreciate if someone can help me on this

Thanks in advance

pyspark apache-spark-sql apache-spark python
1 Answers

Performing something based on row order in Spark is not a good idea. Row order is preserved while reading the file but it may get shuffled in between transformations and then there's no way to know which was the previous row(start time). You need to ensure that no shuffling happens in order to avoid this but that will lead to some other complexities.

my suggestion is to work on the file at source level and try to put row numbers column like

r_n  m_id    time
0      2     2022-01-01T12:12:12.789+000
1      2     2022-01-01T12:14:12.789+000
2      2     2022-01-01T12:16:12.789+000

later in spark you make a left self join with r_n like

df1=df.withColumn("next_r",col("r_n") + lit(1) )
df_final = df.join(df1,df1.next_r == df.r_n , "left" ).select(df("m_id"),df("time").as("start_time"),df1("time").as("end_time") )
user_193121080
Related