How do I convert pandas dataframe to nested JSON object?

Question

I have an SQL database that I need to fetch and convert to JSON. I am thinking that the first step to do that is to fetch the data from the database and load it as a dataframe, then convert the dataframe into JSON object.

Let's say I have the following dataframe.

df_school = pd.DataFrame({'id':[1,2,3,4], 'school_code': ['ABC', 'IJK', 'QRS', 'XYZ'], 'name': ['School A','School B', 'School C', 'School D'], 'type':['private', 'public', 'public', 'private']})
print(df_school)

I want to convert it to JSON with the following code.

import collections

object_list =[]
for idx, row in df_school.iterrows():
    d = collections.OrderedDict()
    d['id'] = row['id']
    d['school_code'] = row['school_code']
    d['name'] = row['name']
    d['type'] = row['type']
    object_list.append(d)

j = json.dumps(object_list)
object_list = 'school_objects.js'
f = open(object_list, 'w')
print(j)

But the result is string. It only looks like a JSON, but when I try to access the item inside the so-called JSON, like j[0] it prints [, not an item inside the JSON.

I also tried another approach, by converting the result from SQL directly to JSON.

query = "Select * from school;"
df_school = pd.read_sql_query(query, connection)
json_school = df_school.head(10).to_json(orient='records')

But I also still got string.

How do I convert it to real JSON object?

df_school.to_json()?

Mykola Zotko
– Mykola Zotko

2019-11-14 09:18:58 +00:00
Commented Nov 14, 2019 at 9:18 — Mykola Zotko
– Mykola Zotko, Commented Nov 14, 2019 at 9:18

Michielver · Accepted Answer · 2019-11-14 09:39:25Z

2

Given the provided df_school variable, we can just do j=df_school.to_json(orient='records') to turn it into a JSON formatted string.

Once we have j storing the JSON formatted string, if we want to do something with it, we first have to load the JSON into Python again using json.loads(j). So if we do:

j = df_school.to_json(orient='records')
# parse j into Python
loaded_json = json.loads(j)
print(loaded_json[0])
# print outputs: {'id': 1, 'name': 'School A', 'school_code': 'ABC', 'type': 'private'}

Hope this helps!

answered Nov 14, 2019 at 9:39

Michielver

1164 bronze badges

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

Comments

Jigy · Accepted Answer · 2019-11-14 09:48:17Z

1

import pandas as pd
import json
df_school = pd.DataFrame({'id':[1,2,3,4], 'school_code': ['ABC', 'IJK', 'QRS', 'XYZ'], 'name': ['School A','School B', 'School C', 'School D'], 'type':['private', 'public', 'public', 'private']})
str_school = df_school.to_json(orient='records')
json_school = json.loads(str_school)
json_school[0]

{'id': 1, 'school_code': 'ABC', 'name': 'School A', 'type': 'private'}

answered Nov 14, 2019 at 9:48

Jigy

114 bronze badges

Comments

AKX · Accepted Answer · 2019-11-14 09:23:01Z

0

JSON is a string encoding of objects.

Once you use json.dumps() or similar, you'll get a string.

answered Nov 14, 2019 at 9:23

AKX

171k17 gold badges148 silver badges230 bronze badges

Comments

Shishir Naresh · Accepted Answer · 2019-11-14 09:31:09Z

0

Try the below code, Hope this will help :

data = [{columns:df_school.iloc[i][columns] for columns in list(df_school.columns)  }  for i in range(df_school.shape[0])   ]

print(data)
print("***********************")
print(type(data[0]))

Ouput will be :

[{'id': 1, 'school_code': 'ABC', 'name': 'School A', 'type': 'private'},
 {'id': 2, 'school_code': 'IJK', 'name': 'School B', 'type': 'public'},
 {'id': 3, 'school_code': 'QRS', 'name': 'School C', 'type': 'public'},
 {'id': 4, 'school_code': 'XYZ', 'name': 'School D', 'type': 'private'}]

*************************
<class 'dict'>

answered Nov 14, 2019 at 9:31

Shishir Naresh

7634 silver badges10 bronze badges

Comments

vgp2018 · Accepted Answer · 2019-11-14 09:49:07Z

0

data={k:list(v.values()) for k,v in df_school.to_dict().items()}
{
'id': [1, 2, 3, 4],
'school_code': ['ABC', 'IJK', 'QRS', 'XYZ'],
'name': ['School A', 'School B', 'School C', 'School D'],
'type': ['private', 'public', 'public', 'private']
}

answered Nov 14, 2019 at 9:49

vgp2018

964 bronze badges

Collectives™ on Stack Overflow

How do I convert pandas dataframe to nested JSON object?

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