I have data farme inpyspark. I am trying to explode some array columns to create rows.
create data frame
df = spark.createDataFrame([('12345','0036',25),('12356','0036',24),('12347','0036',20)],("ext_contract", "legal_entity", "amount"))
>>> df.show()
+------------+------------+------+
|ext_contract|legal_entity|amount|
+------------+------------+------+
| 12345| 0036| 25|
| 12356| 0036| 24|
| 12347| 0036| 20|
+------------+------------+------+
First array columm
df.withColumn("array_source",array(f.lit('T2'),f.lit('T'), f.lit('S'))).withColumn("AFI_SOURCE",explode("array_source")).show()
+------------+------------+------+------------+----------+
|ext_contract|legal_entity|amount|array_source|AFI_SOURCE|
+------------+------------+------+------------+----------+
| 12345| 0036| 25| [T2, T, S]| T2|
| 12345| 0036| 25| [T2, T, S]| T|
| 12345| 0036| 25| [T2, T, S]| S|
| 12356| 0036| 24| [T2, T, S]| T2|
| 12356| 0036| 24| [T2, T, S]| T|
| 12356| 0036| 24| [T2, T, S]| S|
| 12347| 0036| 20| [T2, T, S]| T2|
| 12347| 0036| 20| [T2, T, S]| T|
| 12347| 0036| 20| [T2, T, S]| S|
+------------+------------+------+------------+----------+
Second array column
df.withColumn("INTRTP_ARRAY",array(f.lit('NWP'),f.lit(None), f.lit(None))).withColumn("INTRTP",explode("INTRTP_ARRAY")).show()
+------------+------------+------+------------+------+
|ext_contract|legal_entity|amount|INTRTP_ARRAY|INTRTP|
+------------+------------+------+------------+------+
| 12345| 0036| 25| [NWP,,]| NWP|
| 12345| 0036| 25| [NWP,,]| null|
| 12345| 0036| 25| [NWP,,]| null|
| 12356| 0036| 24| [NWP,,]| NWP|
| 12356| 0036| 24| [NWP,,]| null|
| 12356| 0036| 24| [NWP,,]| null|
| 12347| 0036| 20| [NWP,,]| NWP|
| 12347| 0036| 20| [NWP,,]| null|
| 12347| 0036| 20| [NWP,,]| null|
+------------+------------+------+------------+------+
As you see in both cases it is creating only 9 rows. But when I want try to cobine both the two array and explode it is creating many rows.
df.withColumn("array_source",array(f.lit('T2'),f.lit('T'), f.lit('S'))).withColumn("AFI_SOURCE",explode("array_source")).withColumn("INTRTP_ARRAY",array(f.lit('NWP'),f.lit(None), f.lit(None))).withColumn("INTRTP",explode("INTRTP_ARRAY")).show()
+------------+------------+------+------------+----------+------------+------+
|ext_contract|legal_entity|amount|array_source|AFI_SOURCE|INTRTP_ARRAY|INTRTP|
+------------+------------+------+------------+----------+------------+------+
| 12345| 0036| 25| [T2, T, S]| T2| [NWP,,]| NWP|
| 12345| 0036| 25| [T2, T, S]| T2| [NWP,,]| null|
| 12345| 0036| 25| [T2, T, S]| T2| [NWP,,]| null|
| 12345| 0036| 25| [T2, T, S]| T| [NWP,,]| NWP|
| 12345| 0036| 25| [T2, T, S]| T| [NWP,,]| null|
| 12345| 0036| 25| [T2, T, S]| T| [NWP,,]| null|
| 12345| 0036| 25| [T2, T, S]| S| [NWP,,]| NWP|
| 12345| 0036| 25| [T2, T, S]| S| [NWP,,]| null|
| 12345| 0036| 25| [T2, T, S]| S| [NWP,,]| null|
| 12356| 0036| 24| [T2, T, S]| T2| [NWP,,]| NWP|
| 12356| 0036| 24| [T2, T, S]| T2| [NWP,,]| null|
| 12356| 0036| 24| [T2, T, S]| T2| [NWP,,]| null|
| 12356| 0036| 24| [T2, T, S]| T| [NWP,,]| NWP|
| 12356| 0036| 24| [T2, T, S]| T| [NWP,,]| null|
| 12356| 0036| 24| [T2, T, S]| T| [NWP,,]| null|
| 12356| 0036| 24| [T2, T, S]| S| [NWP,,]| NWP|
| 12356| 0036| 24| [T2, T, S]| S| [NWP,,]| null|
| 12356| 0036| 24| [T2, T, S]| S| [NWP,,]| null|
| 12347| 0036| 20| [T2, T, S]| T2| [NWP,,]| NWP|
| 12347| 0036| 20| [T2, T, S]| T2| [NWP,,]| null|
| 12347| 0036| 20| [T2, T, S]| T2| [NWP,,]| null|
| 12347| 0036| 20| [T2, T, S]| T| [NWP,,]| NWP|
| 12347| 0036| 20| [T2, T, S]| T| [NWP,,]| null|
| 12347| 0036| 20| [T2, T, S]| T| [NWP,,]| null|
| 12347| 0036| 20| [T2, T, S]| S| [NWP,,]| NWP|
| 12347| 0036| 20| [T2, T, S]| S| [NWP,,]| null|
| 12347| 0036| 20| [T2, T, S]| S| [NWP,,]| null|
+------------+------------+------+------------+----------+------------+------+
expected result is below
+------------+------------+------+------------+------------+----------+------+
|ext_contract|legal_entity|amount|array_source|INTRTP_ARRAY|AFI_SOURCE|INTRTP|
+------------+------------+------+------------+------------+----------+------+
| 12345| 0036| 25| [T2, T, S]| [NWP,,]| T2| NWP|
| 12345| 0036| 25| [T2, T, S]| [NWP,,]| T| null|
| 12345| 0036| 25| [T2, T, S]| [NWP,,]| S| null|
| 12356| 0036| 24| [T2, T, S]| [NWP,,]| T2| NWP|
| 12356| 0036| 24| [T2, T, S]| [NWP,,]| T| null|
| 12356| 0036| 24| [T2, T, S]| [NWP,,]| S| null|
| 12347| 0036| 20| [T2, T, S]| [NWP,,]| T2| NWP|
| 12347| 0036| 20| [T2, T, S]| [NWP,,]| T| null|
| 12347| 0036| 20| [T2, T, S]| [NWP,,]| S| null|
+------------+------------+------+------------+------------+----------+------+
How can I achieve what I want