I have a dataset as below.
+-------------------------------------------------------+-------------------------------------------------------------------+---------------------------------------------------------------------------------------+-------------+---------------------------------------------------------+-------------+
|emp_id |sik_id |modification_date |file_name |org_path |received_date|
+-------------------------------------------------------+-------------------------------------------------------------------+---------------------------------------------------------------------------------------+-------------+---------------------------------------------------------+-------------+
|[85627230-s387s09, 98722016-s015s05, 40022035-s008s21] |[f13c1320-5c8f3daas5cd, f13c1384-6659-4831, 4831-aaf1-5c8f3da] |[2021-04-19T11:43:32.617953Z, 2021-04-19T11:43:32.858290Z, 2021-04-19T11:43:34.027082Z]|test1.json |s3://my-bucket/test_prefix/vualt2/rcvd_dt=2022-01-24/ |2022-01-25 |
|[67dm34-4334, 8723gv6-2022, 6f7m99-2244-ki856] |[66d9-4888-aaf1, aaf1-5c8f3da1d5cd, f13c1884-66d9] |[2020-11-12T23:22:05.433107Z, 2020-11-12T20:16:51.339437Z, 2020-11-11T20:59:03.758126Z]|test2.json |s3://my-bucket/test_prefix/vualt2/rcvd_dt=2022-01-25/ |2022-01-25 |
+-------------------------------------------------------+-------------------------------------------------------------------+---------------------------------------------------------------------------------------+-------------+---------------------------------------------------------+-------------+
Whose schema contains array and string fields as below
>>> df.printSchema()
root
|-- emp_id: array (nullable = true)
| |-- element: string (containsNull = true)
|-- sik_id: array (nullable = true)
| |-- element: string (containsNull = true)
|-- modification_date: array (nullable = true)
| |-- element: string (containsNull = true)
|-- file_name: string (nullable = false)
|-- org_path: string (nullable = false)
|-- received_date: string (nullable = false)
I would like to get a result something like below where every emp_id, sik_id, modification_date gets the right file_name, org_path , received_date
+-----------------+--------------------------+-----------------------------+-------------+---------------------------------------------------------+-------------+
|emp_id |sik_id |modification_date |file_name |org_path |received_date|
+-----------------+--------------------------+-----------------------------+-------------+---------------------------------------------------------+-------------+
|85627230-s387s09 |f13c1320-5c8f3daas5cd |2021-04-19T11:43:32.617953Z |test1.json |s3://my-bucket/test_prefix/vualt2/rcvd_dt=2022-01-24/ |2022-01-25 |
|98722016-s015s05 |f13c1384-6659-4831 |2021-04-19T11:43:32.858290Z |test1.json |s3://my-bucket/test_prefix/vualt2/rcvd_dt=2022-01-24/ |2022-01-25 |
|40022035-s008s21 |4831-aaf1-5c8f3da |2021-04-19T11:43:34.027082Z |test1.json |s3://my-bucket/test_prefix/vualt2/rcvd_dt=2022-01-24/ |2022-01-25 |
|67dm34-4334 |66d9-4888-aaf1 |2020-11-12T23:22:05.433107Z |test2.json |s3://my-bucket/test_prefix/vualt2/rcvd_dt=2022-01-25/ |2022-01-25 |
|8723gv6-2022 |aaf1-5c8f3da1d5cd |2020-11-12T20:16:51.339437Z |test2.json |s3://my-bucket/test_prefix/vualt2/rcvd_dt=2022-01-25/ |2022-01-25 |
|6f7m99-2244-ki856|f13c1884-66d9 |2020-11-11T20:59:03.758126Z |test2.json |s3://my-bucket/test_prefix/vualt2/rcvd_dt=2022-01-25/ |2022-01-25 |
+-----------------+--------------------------+-----------------------------+-------------+---------------------------------------------------------+-------------+
I tried using zip() on these fields but looks like zip doesn’t work on array and string fields. As I was seeing a type mismatch exception.
Can someone please help me with the right solution.
Thanks in advance.
>Solution :
Combining the SQL functions arrays_zip and inline.
df = df.selectExpr('inline(arrays_zip(emp_id, sik_id, modification_date))', 'file_name', 'org_path', 'received_date')
df.show(truncate=False)