Python - How To Convert Pandas Dataframe To JSON Object?

Question

I'm using df.to_json() to convert dataframe to json. But it gives me a json string and not an object.

How can I get JSON object?

Also, when I'm appending this data to an array, it adds single quote before and after the json and it ruins the json structure.

How can I export to json object and append properly?

Code Used:

a=[]
     array.append(df1.to_json(orient='records', lines=True)) 
     array.append(df2.to_json(orient='records', lines=True))

Result:

['{"test:"w","param":1}','{"test:"w2","param":2}]']

Required Result:

[{"test":"w","param":1},{"test":"w2","param":2}]

Thank you!

what you ask for is not a "json object" but a dictionary, use df.to_dict() — ted
– ted, Commented May 17, 2018 at 6:42

jezrael · Accepted Answer · 2018-05-17 06:41:31Z

29

I believe need create dict and then convert to json:

import json
d = df1.to_dict(orient='records')
j = json.dumps(d)

Or if possible:

j = df1.to_json(orient='records')

answered May 17, 2018 at 6:41

jezrael

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

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

9 Comments

jason Over a year ago

WHen I'm trying to append this data in to an array. It throws out this error : AttributeError: 'dict' object has no attribute 'append' . COde: data.append(j)

jezrael Over a year ago

@jason - hmmm, orient='records' return list of dict, so append should working. Can you add data input sample and your code? Because I cannot simulate your problem. Thanks.

jason Over a year ago

I'm sorry it works but I get single quote before and after the json. Eg : ['[{"id":1,"param":"data"}]'] I don't need these single quotes when I append. How can I insert data without it?

jezrael Over a year ago

this ' are because json - text. so need dictionary for avoid it. if use array.append(df1.to_dict(orient='records')) it does not work?

jason Over a year ago

Cool ! I got it. df1 = df1.fillna('') FIxed it :)

|

Jerry Chong · Accepted Answer · 2022-12-30 10:16:16Z

5

Here's what worked for me:

import pandas as pd
import json

df = pd.DataFrame([{"test":"w","param":1},{"test":"w2","param":2}])
print(df)
    test  param
0     w      1
1    w2      2

So now we convert to a json string:

d = df.to_json(orient='records')
print(d)
'[{"test":"w","param":1},{"test":"w2","param":2}]'

And now we parse this string to a list of dicts:

data = json.loads(d)
print(data)
[{'test': 'w', 'param': 1}, {'test': 'w2', 'param': 2}]

edited Dec 30, 2022 at 10:16

Jerry Chong

9,4504 gold badges54 silver badges44 bronze badges

answered Jan 28, 2020 at 15:19

igorkf

3,6152 gold badges26 silver badges37 bronze badges

Collectives™ on Stack Overflow

Python - How To Convert Pandas Dataframe To JSON Object?

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related