Pandas DataFrame - Convert columns into JSON and add as a new column

Question

Consider having following DataFrame that I got from MySQL table of size of 11k rows:

col1 |  col2 | col3  | col4
-----------------------------
 cat | black | small | lovely
-----------------------------
 dog | white | medium| brave 
-----------------------------
mice | grey  | tinny | fast

...

I want to convert it dynamically to the following:

col1 |     newcol
------------------------------------------------------------
 cat | {"col2": "black", "col3": "small", "col4": "lovely"}
------------------------------------------------------------
 dog | {"col2": "white", "col3": "medium", "col4": "brave"}
------------------------------------------------------------
mice | {"col2": "grey", "col3": "tinny", "col4": "fast"}

...

Answer 1

You can do agg as dict on axis=1

For dictionary:

out = df[['col1']].assign(new_col=df.iloc[:,1:].agg(dict,1))

For json:

out = df[['col1']].assign(new_col=df.iloc[:,1:].agg(pd.Series.to_json,1))

---

print(out)

   col1                                            new_col
0   cat  {'col2': 'black', 'col3': 'small', 'col4': 'lo...
1   dog  {'col2': 'white', 'col3': 'medium', 'col4': 'b...
2  mice  {'col2': 'grey', 'col3': 'tinny', 'col4': 'fast'}