Convert dataframe schema In PySpark

Question

I've a DataFrame

+------------------+-------------------+--------------------+
|              name|                sku|         description|
+------------------+-------------------+--------------------+
|    Mary Rodriguez| hand-couple-manage|Senior word socia...|
|    Jose Henderson| together-table-oil|Apply girl treatm...|
|    Karen Villegas|     child-somebody|Every tell serve....|
|      Olivia Lynch|forget-matter-avoid|Perhaps environme...|
|     Whitney Wiley|    side-blue-dream|Quickly short soc...|
|  Brittany Johnson|        east-pretty|Indicate view sim...|
|       Paul Morris|    radio-window-us|Society month sho...|
|   Jason Patterson|   night-art-be-act|Entire around pla...|
|      Kiara Gentry|   compare-politics|Air my kind staff...|

Schema

root
 |-- sku: string (nullable = true)
 |-- name_description: array (nullable = true)
 |    |-- element: string (containsNull = true)

How can I groupby column sku and pusing the values from name and description to get a column name_description with values as an array of JSON in the format [{"name":..., "description":...}, {"name":..., "description":...}, ....] for each value in sku in PySpark?

Does these answers help? [pyspark create dictionary from data in two columns](https://stackoverflow.com/questions/52173037/pyspark-create-dictionary-from-data-in-two-columns/52188376) , [Spark scala dataframe: Merging multiple columns into single column](https://stackoverflow.com/questions/51875860/spark-scala-dataframe-merging-multiple-columns-into-single-column), and many more... — mazaneicha, Jun 13 '20 at 17:12

Srinivas · Accepted Answer · 2020-06-13T14:04:35.387

Check below code.


df.show(false)
+---------------+-------------------+-------------------+
|name           |sku                |description        |
+---------------+-------------------+-------------------+
|MaryRodriguez  |hand-couple-manage |Seniorwordsocia... |
|JoseHenderson  |together-table-oil |Applygirltreatm... |
|KarenVillegas  |child-somebody     |Everytellserve.... |
|OliviaLynch    |forget-matter-avoid|Perhapsenvironme...|
|WhitneyWiley   |side-blue-dream    |Quicklyshortsoc... |
|BrittanyJohnson|east-pretty        |Indicateviewsim... |
|PaulMorris     |radio-window-us    |Societymonthsho... |
|JasonPatterson |night-art-be-act   |Entirearoundpla... |
|KiaraGentry    |compare-politics   |Airmykindstaff...  |
+---------------+-------------------+-------------------+

df.groupBy(F.col("sku").agg(F.collect_list(F.struct(F.col("name"),F.col("description"))).alias("name_description")).toJSON.show(false)
+-------------------------------------------------------------------------------------------------------------+
|value                                                                                                        |
+-------------------------------------------------------------------------------------------------------------+
|{"sku":"hand-couple-manage","name_description":[{"name":"MaryRodriguez","description":"Seniorwordsocia..."}]}|
|{"sku":"night-art-be-act","name_description":[{"name":"JasonPatterson","description":"Entirearoundpla..."}]} |
|{"sku":"forget-matter-avoid","name_description":[{"name":"OliviaLynch","description":"Perhapsenvironme..."}]}|
|{"sku":"compare-politics","name_description":[{"name":"KiaraGentry","description":"Airmykindstaff..."}]}     |
|{"sku":"child-somebody","name_description":[{"name":"KarenVillegas","description":"Everytellserve...."}]}    |
|{"sku":"side-blue-dream","name_description":[{"name":"WhitneyWiley","description":"Quicklyshortsoc..."}]}    |
|{"sku":"radio-window-us","name_description":[{"name":"PaulMorris","description":"Societymonthsho..."}]}      |
|{"sku":"east-pretty","name_description":[{"name":"BrittanyJohnson","description":"Indicateviewsim..."}]}     |
|{"sku":"together-table-oil","name_description":[{"name":"JoseHenderson","description":"Applygirltreatm..."}]}|
+-------------------------------------------------------------------------------------------------------------+

Do you have an SQL way of doing the same? – rusty Mar 09 '21 at 10:04 — rusty, Mar 09 '21 at 10:04

Convert dataframe schema In PySpark

1 Answers1