Json data ordering PANDAS, python

Question

I have json like this:

json = {
 "b": 22,
 "x": 12,
 "a": 2,
 "c": 4
}

When i generate an Excel file from this json like this:

import pandas as pd

df = pd.read_json(json_text)
file_name = 'test.xls'
file_path = "/tmp/" + file_name
df.to_excel(file_path, index=False)

print("path to excel " + file_path)

Pandas does its own ordering in the Excel file like this:

pandas_json = {
"a": 2,
"b": 22,
"c": 4,
"x": 12
}

I don't want this. I need the ordering which exists in the json. Please give me some advice how to do this.

UPDATE: if i have json like this:

json = [
{"b": 22, "x":12, "a": 2, "c": 4},
{"b": 22, "x":12, "a": 2, "c": 2},
{"b": 22, "x":12, "a": 4, "c": 4},
]

pandas will generate its own ordering like this:

panas_json = [
{"a": 2, "b":22, "c": 4, "x": 12},
{"a": 2, "b":22, "c": 2, "x": 12},
{"a": 4, "b":22, "c": 4, "x": 12},
]

How can I make pandas preserve my own ordering?

anky · Accepted Answer · 2019-08-09 12:31:22Z

1

You can read the json as OrderedDict which will help to retain original order:

import json
from collections import OrderedDict

json_ = """{
"b": 22,
"x": 12,
"a": 2,
"c": 4
}"""

data = json.loads(json_, object_pairs_hook=OrderedDict)
pd.DataFrame.from_dict(data,orient='index')

Edit, updated json also works:

j="""[{"b": 22, "x":12, "a": 2, "c": 4},
{"b": 22, "x":12, "a": 2, "c": 2},{"b": 22, "x":12, "a": 4, "c": 4}]"""

data = json.loads(j, object_pairs_hook=OrderedDict)
pd.DataFrame.from_dict(data).to_json(orient='records')

'[{"b":22,"x":12,"a":2,"c":4},{"b":22,"x":12,"a":2,"c":2}, 
  {"b":22,"x":12,"a":4,"c":4}]'

edited Aug 9, 2019 at 12:31

answered Aug 9, 2019 at 12:03

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

1 Comment

nesalexy Over a year ago

Thanks! can you look at the more complex jason? I have updated my question

Collectives™ on Stack Overflow

Json data ordering PANDAS, python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related