Combine multiple rows with the same timestamp and put values into new columns

user_11059429 0
Viewed 165

Assuming I'm having an input df which looks as follows:

Timestamp name value
14:00:00 A 25
14:00:00 B 24
15:00:00 A 20
15:00:00 C 21
16:00:00 A 20
16:00:00 B 22
16:00:00 C 23
16:00:00 D 24

What I'd like to do now is to merge the rows dependend on the Timestamps but when merging putting the names with the corresponding value into new columns to achieve the following:

Timestamp A B C D
14:00:00 25 24 nan nan
15:00:00 20 nan 21 nan
16:00:00 20 22 23 24

Are there some build in functions for Pyspark that I could use to achieve this? It would be great if the approach is as generic as possible.

pyspark databricks data-analysis python
1 Answers

All you need was to pivot in pyspark you can use pivot to achieve pivoting like below

df = spark.createDataFrame(
[('14:00:00','A',25),
('14:00:00','B',24),
('15:00:00','A',20),
('15:00:00','C',21),
('16:00:00','A',20),
('16:00:00','B',22),
('16:00:00','C',23),
('16:00:00','D',24)],("Timestamp", "name", "value"))


df1 = df.groupBy("Timestamp").pivot("name").sum("value")

df1.show() # this should display the expected results
user_62372410
Related