如何用PySpark将单VIN关联的双设备行列数据转置为单列两行？

现有数据中，一个VIN关联两台设备，数据以一行多列形式存储（如ID_1、ID_2，对应设备的序列号SN_1、SN_2）。需要将数据转换为单列多行结构：每个设备占一行，且设备的ID、SN需与原VIN保持关联。

曾尝试以下方法但均存在问题：

• union()：需手动拆分字段，代码冗余易出错
• 数组explode()：若同时处理ID和SN数组，会产生笛卡尔积（如生成8行），破坏ID与SN的对应关联
• stack unpivot：部分VIN丢失，且未同步处理ID与SN的关联

尝试过的代码如下：

数组Explode尝试 #

output1 = (df_test
           .withColumn('ID', array(col('ID_1'), col('ID_2')))
           .select('ID', 'VIN')
         )
output2 = (output1
            .withColumn('ID_1_x', explode('ID'))
            .select('ID_1_x', 'VIN')
            )

Unpivot尝试 #

unPivot_df = (df_test
             .select('VIN', expr("stack(3, 'ID_1', ID_1, 'ID_2', ID_2) as (other, ID)"))
              .where("ID is not null")
             ) 

针对ID与SN需关联的需求，正确的做法是用stack函数成对处理每台设备的ID和SN字段，确保每个设备的属性保持绑定，同时避免VIN丢失。

假设原数据结构 #

假设DataFrame包含字段：VIN、ID_1、SN_1、ID_2、SN_2

正确的Unpivot代码 #

from pyspark.sql.functions import expr

unpivoted_df = df_test.select(
    'VIN',
    expr("""
        stack(2, 
            '设备1', ID_1, SN_1,
            '设备2', ID_2, SN_2
        ) as (设备标识, 设备ID, 设备SN)
    """)
).where("设备ID is not null OR 设备SN is not null")  # 可根据需求调整：保留至少有一个属性非空的行

简化版（无需设备标识） #

如果不需要区分是第一台还是第二台设备，可简化为：

unpivoted_df = df_test.select(
    'VIN',
    expr("""
        stack(2, 
            ID_1, SN_1,
            ID_2, SN_2
        ) as (设备ID, 设备SN)
    """)
).where("设备ID is not null OR 设备SN is not null")

1. 数组Explode的问题：仅对ID数组拆分，未同步处理对应的SN字段，会导致SN与ID无法正确绑定；若同时对ID和SN数组执行explode，会触发笛卡尔积，生成错误的关联组合。
2. 原Unpivot代码的问题：
   • stack(3)多声明了一组空值，若某个VIN的两个设备ID均为null，where("ID is not null")会过滤掉整个VIN行
   • 仅处理了ID字段，未关联SN，丢失了设备属性的绑定关系

内容的提问来源于stack exchange，提问作者RobE