Pyspark : concat two spark df side ways without join efficiently

Junwoo Yun 0
Viewed 28

Hi I have sparse dataframe that was loaded by mergeschema option

DF              
name    A1  A2    B1   B2 .....  partitioned_name                                                    
A        1   1   null null          partition_a   
B        2   2   null null          partition_a  
A     null  null   3   4            partition_b 
B     null  null   3   4            partition_b

to

DF              
name    A1  A2    B1   B2 .....                                                      
A        1   1     3   4
B        2   2     3   4

Any Best ideas without joining for efficiency (nor rdd because data is huge)? I was thinking about solutions like pandas concat(axis=1) since all the tables are sorted

concatenation join pyspark apache-spark
1 Answers

If that pattern repeats and you don't mind hardcode the column names:

df = spark.createDataFrame(
    [
    ('A','1','1','null','null','partition_a'),
    ('B','2','2','null','null','partition_a'),
    ('A','null','null','3','4','partition_b'),
    ('B','null','null','3','4','partition_b')
    ],
    ['name','A1','A2','B1','B2','partitioned_name']
)\
.withColumn('A1', F.col('A1').cast('integer'))\
.withColumn('A2', F.col('A2').cast('integer'))\
.withColumn('B1', F.col('B1').cast('integer'))\
.withColumn('B2', F.col('B2').cast('integer'))\

df.show()

import pyspark.sql.functions as F

cols_to_agg = [col for col in df.columns if col not in ["name", "partitioned_name"]]

df\
    .groupby('name')\
    .agg(F.sum('A1').alias('A1'),
         F.sum('A2').alias('A2'),
         F.sum('B1').alias('B1'),
         F.sum('B2').alias('B2'))\
    .show()

+----+----+----+----+----+----------------+
# |name|  A1|  A2|  B1|  B2|partitioned_name|
# +----+----+----+----+----+----------------+
# |   A|   1|   1|null|null|     partition_a|
# |   B|   2|   2|null|null|     partition_a|
# |   A|null|null|   3|   4|     partition_b|
# |   B|null|null|   3|   4|     partition_b|
# +----+----+----+----+----+----------------+

# +----+---+---+---+---+
# |name| A1| A2| B1| B2|
# +----+---+---+---+---+
# |   A|  1|  1|  3|  4|
# |   B|  2|  2|  3|  4|
# +----+---+---+---+---+
Luiz Viola0
Related