Json import sorting

[ghego]

Noticed that if export a df to json file and then reload the data, ordering is not kept.

import pandas as pd

df.to_json('test.json')
newdf = pd.read_json('test.json')

print df.index

gives:

1
10
100
....

instead of

1
2
3
....

it suffices to call newdf = pd.read_json('test.json').sort() to have the what I'd expect.

Is this intended behaviour or bug?

[jtratner]

The JSON emitted is a dictionary object, not an array, so it's inherently
unordered. It's not possible to maintain ordering (and it's outside of the
JSON spec to do so).

[cpcloud]

no it's sorting lexicographically, most likely because the index is sorted before being converted to a numeric dtype. I think this is a bug.

[jtratner]

@cpcloud sure, that may be, but fundamentally, you can't assure that whatever ordering was there initially should be in the final index. I.e.:

df = DataFrame([range(10)] * 10, index = range(10,0,-1))
df.from_json(df.to_json())

There's no way that you can ensure that the index falls out correctly. But yes, it's clearly lexicographically sorting.

[cpcloud]

hm....okay...but why is it sorting at all?

[Komnomnomnom]

The JSON code initially decodes to a dict with string keys which it then passes to the DataFrame constructor, I think the sorting might be happening after that point:

In [9]: d = {'foo': {'1': 1, '2': 2, '3': 3, '10': 10, '15': 15, '100': 100, '101': 101}}

In [10]: pd.DataFrame(d)
Out[10]: 
     foo
1      1
10    10
100  100
101  101
15    15
2      2
3      3

[cpcloud]

Yep, that'll do it! dicts are sorted in the DataFrame constructor.

[jreback]

passing a list-of-lists will not sort (though may be more expensive 2 create)

[Komnomnomnom]

BTW @ghego if you want to ensure that order is preserved during roudtrip JSONifying try using orient=split

n [27]: pd.read_json(df.to_json(orient='split'), orient='split')
[0]
Out[27]: 
     foo
1      1
2      2
3      3
4     10
10    11
11    20
100  100
101  101

[cpcloud]

Might be nice to have that in the docs/cookbook.

[Komnomnomnom]

@cpcloud agreed. There's a couple of things I'd like to add to the JSON docs (incl a couple of benchmarks and more about the numpy param and when it can be useful). I'll try and get a PR together for this stuff before the next release.

[jtratner]

So I think this can be closed because it's expected behavior?

[jreback]

I think @Komnomnomnom going to take a look....so let's leave open for a bit

[Komnomnomnom]

Just to clarify I'm going to update the docs to give more detail on the different orients, and make it clear when you can expect non order-preserving behaviour. Apart from that I wasn't going to change anything....

[jreback]

@Komnomnomnom changing this to docs only....when u have a chance

[jreback]

@Komnomnomnom how's docs coming on this?

[jreback]

@Komnomnomnom do we need anything in docs for this? (I believe you did cover it...)...lmk

[Komnomnomnom]

@jreback, sorry yeah still planning to get to the json docs at some point. Hopefully will have some time over the next few days.

[jreback]

gr8

[jreback]

@Komnomnomnom docs?

[Komnomnomnom]

I've done a rough pass but still struggling to find adequate time I'm afraid. Last chance this weekend? :)

[jreback]

docs are ok even after release-candidate....so ok

[Komnomnomnom]

@jreback #5287 eventually got there. Thanks for the reminders.