Pandas dataframe to json without index

Question

I'm trying to take a dataframe and transform it into a particular json format.

Here's my dataframe example:

DataFrame name: Stops
id    location
0     [50, 50]
1     [60, 60]
2     [70, 70]
3     [80, 80]

Here's the json format I'd like to transform into:

"stops":
[
{
    "id": 1,
    "location": [50, 50]
},
{
    "id": 2,
    "location": [60, 60]
},
... (and so on)
]

Notice it's a list of dicts. I have it nearly there with the following code:

df.reset_index().to_json(orient='index)

However, that line also includes the index like this:

"stops":
{
"0":
    {
        "id": 0,
        "location": [50, 50]
    },
"1":
    {
        "id": 1,
        "location": [60, 60]
    },
... (and so on)
}

Notice this is a dict of dicts and also includes the index twice (in the first dict and as the "id" in the second dict! Any help would be appreciated.

Maybe the behavior changed? I get ValueError: too many values to unpack (expected 2) — WestCoastProjects
– WestCoastProjects, Commented Mar 1, 2021 at 18:37

roman · Accepted Answer · 2015-02-18 18:13:49Z

90

You can use orient='records'

print df.reset_index().to_json(orient='records')

[
     {"id":0,"location":"[50, 50]"},
     {"id":1,"location":"[60, 60]"},
     {"id":2,"location":"[70, 70]"},
     {"id":3,"location":"[80, 80]"}
]

answered Feb 18, 2015 at 18:13

roman

118k30 gold badges205 silver badges209 bronze badges

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

3 Comments

Eric Miller Over a year ago

That is so simple. I love pandas ... and you! Thanks!

aerin Over a year ago

If you don't want index at all, just remove "reset_index()" part.

prosti Over a year ago

Found this in accident.

WestCoastProjects · Accepted Answer · 2021-03-01 18:44:18Z

6

Since 2017 there is an index=False option. Use it with orient='split' or orient='table'. Credit to this answer on a similar question: https://stackoverflow.com/a/59438648/1056563

    dfj = json.loads(df.to_json(orient='table',index=False))

answered Mar 1, 2021 at 18:44

WestCoastProjects

63.9k109 gold badges368 silver badges638 bronze badges

Comments

hzitoun · Accepted Answer · 2020-10-09 15:22:13Z

3

If you want a Python dict (the JSON-object equivalent in python) not a JSON string:

df.to_dict(orient='records')

answered Oct 9, 2020 at 15:22

hzitoun

5,8702 gold badges40 silver badges44 bronze badges

Comments

randomGeek4 · Accepted Answer · 2021-06-02 07:32:01Z

2

There is another way as well.

df_dict=df.reset_index().to_dict(orient='index')
df_vals=list(df_dict.values())

answered Jun 2, 2021 at 7:32

randomGeek4

3011 gold badge3 silver badges8 bronze badges

Collectives™ on Stack Overflow

Pandas dataframe to json without index

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related