JSON format for a dictionary with Pandas Dataframe as values

Question

I need to return, from a web framework (Flask for instance), a few dataframes and a string in a single Json object. My code looks something like this:

import pandas as pd
data1 = [['Alex',10],['Bob',12],['Clarke',13]]
df1 = pd.DataFrame(data1,columns=['Name','Age'])
data2 = [['Cycle',5],['Run',1],['Hike',7]]
df2 = pd.DataFrame(data1,columns=['Sport','Duration'])

test_value={}
test_value["df1"] = df1.to_json(orient='records')
test_value["df2"] = df2.to_json(orient='records')
print(json.dumps(test_value))

This outputs :

{"df1": "[{\"Name\":\"Alex\",\"Age\":10},{\"Name\":\"Bob\",\"Age\":12},{\"Name\":\"Clarke\",\"Age\":13}]", "df2": "[{\"Sport\":\"Alex\",\"Duration\":10},{\"Sport\":\"Bob\",\"Duration\":12},{\"Sport\":\"Clarke\",\"Duration\":13}]"}

So a number of escape characters in front of every key of the value of "df1" and "df2". If on the other hand, I look at test_value, I get:

{'df1': '[{"Name":"Alex","Age":10},{"Name":"Bob","Age":12},{"Name":"Clarke","Age":13}]', 'df2': '[{"Sport":"Alex","Duration":10},{"Sport":"Bob","Duration":12},{"Sport":"Clarke","Duration":13}]'}

Which is not quite right. What I need is 'df1' to be in double quotes "df1". Short of doing a search and replace in the string, what is the way to achieve that?

I've even tried to create the string myself, doing something like that :

print('\{"test": "{0:.2f}"\}'.format(123))

but I get this error:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-266-1fa35152436c> in <module>
----> 1 print('\{"test": "{0:.2f}"\}'.format(123))

KeyError: '"test"'

which I really don't get :). That said, there must be a better way then searching/replacing for the 'df1' for "df1".

Ideas?

Firstly, '\{"test": "{0:.2f}"\}'.format(123) is incorrect. It must be '{{"test": "{0:.2f}"}}.format(123)'. Format uses double braces, not backslashes. — DYZ
– DYZ, Commented Mar 15, 2020 at 4:33
Second, what is wrong with json.dumps(test_value)? Why do you not like it? — DYZ
– DYZ, Commented Mar 15, 2020 at 4:37
Because if escapes the " another time which makes not great. Answer below works perfectly! — omartin2010
– omartin2010, Commented Mar 15, 2020 at 11:54

jezrael · Accepted Answer · 2020-03-15 05:57:25Z

2

There is double converting to json, in to_json and in json.dumps functions. Solution is convert values to dictionaries by DataFrame.to_dict and then only once to json by json.dumps:

test_value={}
test_value["df1"] = df1.to_dict(orient='records')
test_value["df2"] = df2.to_dict(orient='records')
print(json.dumps(test_value))
{"df1": [{"Name": "Alex", "Age": 10}, 
         {"Name": "Bob", "Age": 12}, 
         {"Name": "Clarke", "Age": 13}], 
"df2": [{"Sport": "Alex", "Duration": 10}, 
        {"Sport": "Bob", "Duration": 12}, 
        {"Sport": "Clarke", "Duration": 13}]}

answered Mar 15, 2020 at 5:57

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

JSON format for a dictionary with Pandas Dataframe as values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related