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, May 17 '18 at 06:42

score 28 · Accepted Answer · answered May 17 '18 at 06:41

28

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 '18 at 06:41

jezrael

822,522
95
1,334
1,252

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)** – jason May 17 '18 at 06:52
@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. – jezrael May 17 '18 at 06:54
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? – jason May 17 '18 at 07:00
this `'` are because `json` - `text`. so need `dictionary` for avoid it. if use `array.append(df1.to_dict(orient='records'))` it does not work? – jezrael May 17 '18 at 07:02
This is what I'm getting when I append it. **[[{'test': 0, 'params': 0, 'data': 'test'}]]** Error: **SyntaxError: JSON.parse: expected property name or '}' at line 1 column 4 of the JSON data** – jason May 17 '18 at 07:08
It should have double quotes instead of single quotes I guess! – jason May 17 '18 at 07:09
this is value in DataFrame? `df = pd.DataFrame({'col':[[{'test': 0, 'params': 0, 'data': 'test'}]]})` – jezrael May 17 '18 at 07:10
2

Cool ! I got it. **df1 = df1.fillna('')** FIxed it :) – jason May 17 '18 at 07:15
Hi Jezreal. PLease can you look into this - https://stackoverflow.com/questions/50859373/convert-all-data-within-the-json-to-csv-using-pandas-or-python Thanks in advance :) – jason Jun 15 '18 at 07:43

score 3 · Answer 2 · edited Dec 30 '22 at 10:16

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

Python - How To Convert Pandas Dataframe To JSON Object?

2 Answers2

Linked

Related