Spark Pivot rows to Columns with date

user_7668402 0
Viewed 364

I have a dataframe that looks like below

id  category    category_value  load_timestamp
            
1   Apple           Y            59:52.1
2   Apple           Y            33:18.0
3   Apple           N            08:13.4
4   Apple           Y            22:15.4
5   Tesla           Y            43:31.3
5   Nvidia          N            23:24.2

I was able to Pivot it to below :

id  Apple   Tesla   Nvidia
1   Y       
2   Y       
3   N       
4   Y       
5            Y       N

I was able to accomplish this using this code :

pivot_df = df.groupBy("id").pivot("category",[Apple, Tesla, Nvidia]).agg(first("category_value"))

However, I need to get the original dataframe in the following format:

id  Apple   Apple_load_timestamp    Tesla   Tesla_load_timestamp    Nvidia  Nvidia_load_timestamp
1   Y       59:52.1             
2   Y       33:18.0             
3   N       08:13.4             
4   Y       22:15.4             
5                                       Y   43:31.3                    N    23:24.2

Whats the best way of pivoting the dataframe in Spark/PySpark that also includes column for timestamp of each row?

pyspark apache-spark-sql apache-spark dataframe pivot
1 Answers

You can use:

out = df.groupBy("id").pivot("category").agg(F.first("category_value"),
                                 F.first("load_timestamp")).na.fill('')
import re
cols = [re.sub("\(category.*","",i.replace("_first",'')) for i in out.columns]
d = zip(out.columns,cols)
out.select(*[F.col(a).alias(b) for a,b in d]).show()

+---+-----+---------------------+------+----------------------+-----+---------------------+
| id|Apple|Apple(load_timestamp)|Nvidia|Nvidia(load_timestamp)|Tesla|Tesla(load_timestamp)|
+---+-----+---------------------+------+----------------------+-----+---------------------+
|  1|    Y|              59:52.1|      |                      |     |                     |
|  2|    Y|              33:18.0|      |                      |     |                     |
|  3|    N|              08:13.4|      |                      |     |                     |
|  4|    Y|              22:15.4|      |                      |     |                     |
|  5|     |                     |     N|               23:24.2|    Y|              43:31.3|
+---+-----+---------------------+------+----------------------+-----+---------------------+
user_98406370
Related