Turn pandas columns into JSON string

user_11961869 0
Viewed 68

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!
pandas dataframe python json
5 Answers

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'}
user_126114090

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'}
user_128331660

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'}]}
user_5323120

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'}]}
user_34269270

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'}
]
user_130209500
Related