Reputation: 31
The columns CGL, CPL, EO should become Coverage Type, the values for CGL, CPL, EO should go in column Premium, and values for CGLTria,CPLTria,EOTria should go in column Tria Premium.
declare @TestDate table (
QuoteGUID varchar(8000),
CGL money,
CGLTria money,
CPL money,
CPLTria money,
EO money,
EOTria money
)
INSERT INTO @TestDate (QuoteGUID, CGL, CGLTria, CPL, CPLTria, EO, EOTria)
VALUES ('2D62B895-92B7-4A76-86AF-00138C5C8540', 2000, 160, 674, 54, 341, 0),
('BE7F9483-174F-4238-8931-00D09F99F398', 0, 0, 3238, 259, 0, 0),
('BECFB9D8-D668-4C06-9971-0108A15E1EC2', 0, 0, 0, 0, 0, 0)
SELECT * FROM @TestDate
OUTPUT:
The result should be like that:
I have done it using cross apply with values in SQL, but I want to implement it using PySpark.
Upvotes: 3
Views: 6202
Reputation: 24508
Before usual unpivoting (using stack
), you could combine groups of columns to one column using array
.
Input:
from pyspark.sql import functions as F
df = spark.createDataFrame(
[('2D62B895-92B7-4A76-86AF-00138C5C8540', 2000, 160, 674, 54, 341, 0),
('BE7F9483-174F-4238-8931-00D09F99F398', 0, 0, 3238, 259, 0, 0),
('BECFB9D8-D668-4C06-9971-0108A15E1EC2', 0, 0, 0, 0, 0, 0)],
['QuoteGUID', 'CGL', 'CGLTria', 'CPL', 'CPLTria', 'EO', 'EOTria']
)
Script:
df2 = df.select(
'QuoteGUID',
F.expr("stack(3, 'CGL', array(CGL, CGLTria), 'CPL', array(CPL, CPLTria), 'EO', array(EO, EOTria)) as (CoverageType, _P)")
)
df3 = df2.select(
'QuoteGUID',
'CoverageType',
F.col('_P')[0].alias('Premium'),
F.col('_P')[1].alias('TriaPremium'),
)
Result:
df3.show(truncate=0)
# +------------------------------------+------------+-------+-----------+
# |QuoteGUID |CoverageType|Premium|TriaPremium|
# +------------------------------------+------------+-------+-----------+
# |2D62B895-92B7-4A76-86AF-00138C5C8540|CGL |2000 |160 |
# |2D62B895-92B7-4A76-86AF-00138C5C8540|CPL |674 |54 |
# |2D62B895-92B7-4A76-86AF-00138C5C8540|EO |341 |0 |
# |BE7F9483-174F-4238-8931-00D09F99F398|CGL |0 |0 |
# |BE7F9483-174F-4238-8931-00D09F99F398|CPL |3238 |259 |
# |BE7F9483-174F-4238-8931-00D09F99F398|EO |0 |0 |
# |BECFB9D8-D668-4C06-9971-0108A15E1EC2|CGL |0 |0 |
# |BECFB9D8-D668-4C06-9971-0108A15E1EC2|CPL |0 |0 |
# |BECFB9D8-D668-4C06-9971-0108A15E1EC2|EO |0 |0 |
# +------------------------------------+------------+-------+-----------+
Upvotes: 3