Turn pandas columns into JSON string

Question

I have a pandas dataframe with columns col1, col2 and col3 and respective values. I would need to transform column names and values into a JSON string.

For instance, if the dataset is

data= pd.DataFrame({'col1': ['bravo', 'charlie','price'], 'col2': [1, 2, 3],'col3':['alpha','beta','gamma']})

I need to obtain an output like this

newdata= pd.DataFrame({'index': [0,1,2], 'payload': ['{"col1":"bravo", "col2":"1", "col3":"alpha"}', '{"col1":"charlie", "col2":"2", "col3":"beta"}', '{"col1":"price", "col2":"3", "col3":"gamma"}']})

I didn't find any function or iterative tool to perform this.

Thank you in advance!

Shubham Sharma · Accepted Answer · 2020-07-02 09:04:18Z

2

You can use:

df = data.agg(lambda s: dict(zip(s.index, s)), axis=1).rename('payload').to_frame()

Result:

# print(df)

                                          payload
0   {'col1': 'bravo', 'col2': 1, 'col3': 'alpha'}
1  {'col1': 'charlie', 'col2': 2, 'col3': 'beta'}
2   {'col1': 'price', 'col2': 3, 'col3': 'gamma'}

answered Jul 2, 2020 at 9:04

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

Comments

Pani · Accepted Answer · 2020-07-02 09:08:01Z

2

Here you go:

import pandas as pd

data= pd.DataFrame({'col1': ['bravo', 'charlie','price'], 'col2': [1, 2, 3],'col3':['alpha','beta','gamma']})
new_data = pd.DataFrame({
    'payload': data.to_dict(orient='records')
})
print(new_data)

## -- End pasted text --
                                          payload
0   {'col1': 'bravo', 'col2': 1, 'col3': 'alpha'}
1  {'col1': 'charlie', 'col2': 2, 'col3': 'beta'}
2   {'col1': 'price', 'col2': 3, 'col3': 'gamma'}

edited Jul 2, 2020 at 9:08

Pani

1,4071 gold badge17 silver badges23 bronze badges

answered Jul 2, 2020 at 9:00

Balaji Ambresh

5,0022 gold badges7 silver badges17 bronze badges

Comments

HK boy · Accepted Answer · 2020-07-02 11:07:24Z

1

If my understanding is correct, you want the index and the data records as a dict.

So:

dict(index=list(data.index), payload=data.to_dict(orient='records'))

For your example data:

>>> import pprint
>>> pprint.pprint(dict(index=list(data.index), payload=data.to_dict(orient='records')))
{'index': [0, 1, 2],
 'payload': [{'col1': 'bravo', 'col2': 1, 'col3': 'alpha'},
             {'col1': 'charlie', 'col2': 2, 'col3': 'beta'},
             {'col1': 'price', 'col2': 3, 'col3': 'gamma'}]}

edited Jul 2, 2020 at 11:07

HK boy

1,40411 gold badges21 silver badges27 bronze badges

answered Jul 2, 2020 at 9:18

Michael Hooreman

6021 gold badge7 silver badges17 bronze badges

Comments

Rakesh · Accepted Answer · 2020-07-02 09:04:16Z

1

This is one approach using .to_dict('index').

Ex:

import pandas as pd

data= pd.DataFrame({'col1': ['bravo', 'charlie','price'], 'col2': [1, 2, 3],'col3':['alpha','beta','gamma']})
newdata = data.to_dict('index')
print({'index': list(newdata.keys()), 'payload': list(newdata.values())})
#OR -->newdata= pd.DataFrame({'index': list(newdata.keys()), 'payload': list(newdata.values())})

Output:

{'index': [0, 1, 2],
 'payload': [{'col1': 'bravo', 'col2': 1, 'col3': 'alpha'},
             {'col1': 'charlie', 'col2': 2, 'col3': 'beta'},
             {'col1': 'price', 'col2': 3, 'col3': 'gamma'}]}

answered Jul 2, 2020 at 9:04

Rakesh

82.9k17 gold badges85 silver badges122 bronze badges

Comments

Gaëtan · Accepted Answer · 2020-07-02 09:17:02Z

0

Use to_dict: newdata = data.T.to_dict()

>>> print(newdata.values())
[
  {'col2': 1, 'col3': 'alpha', 'col1': 'bravo'},
  {'col2': 2, 'col3': 'beta', 'col1': 'charlie'},
  {'col2': 3, 'col3': 'gamma', 'col1': 'price'}
]

edited Jul 2, 2020 at 9:17

answered Jul 2, 2020 at 9:11

Gaëtan

3281 silver badge8 bronze badges

Collectives™ on Stack Overflow

Turn pandas columns into JSON string

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related