Pandas df autosorts rows on construction with OrderedDict of OrderedDicts

from collections import OrderedDict
import pandas as pd
data = OrderedDict([('ele2', OrderedDict([('b', 1), ('a', 2)])),
                    ('ele1', OrderedDict([('b', 2), ('a', 5)]))])             
pd.DataFrame(data)

Problem description

The output shows the rows get sorted.

Expected Output

When constructing a df with OrderedDicts I would expect the order in columns and rows to stay the same.

Output of pd.show_versions()

In [6]: pd.show_versions() INSTALLED VERSIONS ------------------ commit: None python: 2.7.13.final.0 python-bits: 64 OS: Linux OS-release: 3.10.0-514.21.1.el7.x86_64 machine: x86_64 processor: byteorder: little LC_ALL: C LANG: en_US.UTF-8A LOCALE: None.None pandas: 0.21.0 pytest: None pip: 9.0.1 setuptools: 27.2.0 Cython: 0.25.2 numpy: 1.12.1 scipy: 0.19.0 pyarrow: None xarray: None IPython: 5.3.0 sphinx: 1.6.2 patsy: 0.4.1 dateutil: 2.6.0 pytz: 2017.2 blosc: None bottleneck: None tables: 3.3.0 numexpr: 2.6.2 feather: None matplotlib: 2.0.2 openpyxl: 2.4.7 xlrd: 1.0.0 xlwt: 1.2.0 xlsxwriter: 0.9.6 lxml: 3.8.0 bs4: None html5lib: 0.999 sqlalchemy: 1.1.10 pymysql: None psycopg2: None jinja2: 2.9.6 s3fs: None fastparquet: None pandas_gbq: None pandas_datareader: None

Comment From: jreback

you can have a look but i don’t think a nested dict that is ordered is respected. problem is you have have different keys in each row and so keys are accumulated via Index.union which sorts it would be non performant otherwise

but if u want to see pls do

Comment From: xuancong84

I agree with OP, there is some design flaw and bug in the low-level DataFrame construction code that needs to be fixed. My examples below illustrates the problem:

1. The following code shows the normal behaviour for using dict to construct row data, the result is correct and as expected: pd.DataFrame.from_dict(OrderedDict([(d, {0:0} if ii in [2,3,6] else {i:np.random.random() for i in range(5)}) for ii,d in enumerate(pd.date_range(start='2019-10-25', end='2019-11-05'))]), orient='index')

2. The 1st design flaw lies in when you pass in empty dict, the row gets omitted: pd.DataFrame.from_dict(OrderedDict([(d, {} if ii in [2,3,6] else {i:np.random.random() for i in range(5)}) for ii,d in enumerate(pd.date_range(start='2019-10-25', end='2019-11-05'))]), orient='index')

3. The 2nd design bug lies in that even when you use OrderedDict, you still cannot keep all the rows in the right order if the dictionary does not starts with the same key, pd.DataFrame.from_dict(OrderedDict([(d, {4:0} if ii in [2,3,6] else {i:np.random.random() for i in range(5)}) for ii,d in enumerate(pd.date_range(start='2019-10-25', end='2019-11-05'))]), orient='index')

Note the last 3 rows are out of order.

Comment From: mroeschke

Looks to work on master now. Could use a test

In [19]: from collections import OrderedDict
    ...: import pandas as pd
    ...: data = OrderedDict([('ele2', OrderedDict([('b', 1), ('a', 2)])),
    ...:                     ('ele1', OrderedDict([('b', 2), ('a', 5)]))])
    ...: pd.DataFrame(data)
Out[19]:
   ele2  ele1
b     1     2
a     2     5