Generating new column with list of other column values

Question

I have a Data Frame as below

+-----+---+------+-----+
| id |age|height| score
+-----+---+------+-----+
|1001|  5|    80| 12
|1002|  9|    95| 189
|1003| 10|    82| 345
+-----+---+------+-----+

and want to create new column which combines all other columns in a key value structure and few columns as it is something like below

+-----+----------------------------------------------------------+------+
| id  |property                                                  | score
+-----+----------------------------------------------------------+------+
|1001|  {'id': '1001', 'age': '5', 'height': '80', 'score': '12'} | 12
|1002|  {'id': '1002', 'age': '9', 'height': '95', 'score': '189'}|189
|1003| {'id': '1003', 'age': '10', 'height': '82', 'score':'345'}|345
+----------------------------------------------------------------+--------+

I tried with df.withColumn('property', map(lambda row: row.asDict(), df.collect())) but it is not producing results as I want. Anything wrong with my approach?

过过招 · Accepted Answer · 2022-05-19 10:11:23Z

1

You can do it using to_json and struct functions.

df = df.select(
    'id',
    F.to_json(F.struct('*')).alias('property'),
    'score'
)
df.show(truncate=False)

edited May 19, 2022 at 10:11

answered May 19, 2022 at 8:50

过过招

4,3372 gold badges7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

syv Over a year ago

It includes escape character additionally in String columns is there any way to avoid it? I think its because of to_json otherwise can I use to_dict or some other options?

过过招 Over a year ago

Can you provide some sample data?

syv Over a year ago

One of the column has json structure already like '{"mark": {"Sub1": {"type": "Taal"}, "Sub2": {"type": "WO"}, "Sub3": {"type": "Numbers"}' when I do to_json again it includes '\' in the place of "".

过过招 Over a year ago

Gotta get off work. Tomorrow, let's see how the json type field is concatenated with the to_json result.

syv Over a year ago

Solved !! df.withColumn('property', F.regexp_replace('property', '\\\\', ''))

Collectives™ on Stack Overflow

Generating new column with list of other column values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related