How to return the json format of a dataframe? [duplicate]

Question

I have a dataframe df and I tried converting dataframe to json as below:

from flask import Flask
import pandas as pd

app=Flask(__name__)

@app.route('/graph')
def plot():
    df=pd.DataFrame([['madrid', 10], ['venice', 20], ['las vegas', 40], ['brighton', 35]],
                      columns=['city', 'temp'])
    jsondata=df.to_json(orient='records')
    return jsondata

if __name__=='__main__':
    app.run()

o/p as follows:

[{"city":"madrid","temp":10},{"city":"venice","temp":20},{"city":"las vegas","temp":40},{"city":"brighton","temp":35}]

and if I use orient='split' o/p is :

{"columns":["city","temp"],"index":[0,1,2,3],"data":[["madrid",10],["venice",20],["las vegas",40],["brighton",35]]}

I am looking for an output as:

{
"trace1":[{"city":["madrid",....],"temp":[10,20,...]}]
}

No idea to get for city and temp as above ,but tried for trace1 atleast as return {"trace1":jsondata} but this is giving me

{
    "trace1": "{\"columns\":[\"city\",\"temp\"],\"index\":[0,1,2,3],\"data\":[[\"madrid\",10],[\"venice\",20],[\"las vegas\",40],[\"brighton\",35]]}"
}

jezrael · Accepted Answer · 2019-10-10 09:11:05Z

2

Use DataFrame.to_dict with orient='list' for dictionary and then convert to json:

import json

jsondata= json.dumps({"trace1":[df.to_dict(orient='list')]})
print (jsondata)

{"trace1": [{"city": ["madrid", "venice", "las vegas", "brighton"],"temp": [10, 20, 40, 35]}]}

edited Oct 10, 2019 at 9:11

answered Oct 10, 2019 at 8:55

jezrael

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

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

7 Comments

KcH Over a year ago

Exactly what I'm looking for ...thanks jez

KcH Over a year ago

so couldn't we use df.to_json to achieve the same ??

jezrael Over a year ago

@Codenewbie - it is not implemented, test df.to_json(orient='list')

KcH Over a year ago

ValueError: Invalid value 'list' for option 'orient'

jezrael Over a year ago

@Codenewbie - I think there is double convert values to json, so second parsed json add `` for escape.

|

Collectives™ on Stack Overflow

How to return the json format of a dataframe? [duplicate]

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related