Python DataFrame df.to_json(orient='split') - Doesnt maintain Order of Column Labels

Question

Am using - Python 3.5.4 , pandas==0.23.0

The Pandas DataFrame created from Six Lists shown below - each for a Column.

    ls_col_1 = ["Col_1_Row_1","Col_1_Row_2","Col_1_Row_3"]
        ls_col_2 = ["Col_2_Row_1","Col_2_Row_2","Col_2_Row_3"]
        ls_col_3 = ["Col_3_Row_1","Col_3_Row_2","Col_3_Row_3"]
        ls_col_4 = ["Col_4_Row_1","Col_4_Row_2","Col_4_Row_3"]
        ls_col_5 = ["Col_5_Row_1","Col_5_Row_2","Col_5_Row_3"]
        ls_col_6 = ["Col_6_Row_1","Col_6_Row_2","Col_6_Row_3"]

df_xls = pd.DataFrame({'ZaaCol_A':ls_col_1,'MCol_B':ls_col_2,'BCol_C':ls_col_3,'XCol_3':ls_col_4,'Col_E':ls_col_5,'Col_F':ls_col_6})   

data = json.loads(df_xls.to_json(orient='split'))

dict_json['data_json'] = data

return JsonResponse(dict_json, safe= False)

The json i get to see in a Django template is as below :-

{"data_json": {"columns": ["BCol_C", "Col_E", "Col_F", "MCol_B", "XCol_3", "ZaaCol_A"], "index": [0, 1, 2], "data": [["Col_3_Row_1", "Col_5_Row_1", "Col_6_Row_1", "Col_2_Row_1", "Col_4_Row_1", "Col_1_Row_1"], ["Col_3_Row_2", "Col_5_Row_2", "Col_6_Row_2", "Col_2_Row_2", "Col_4_Row_2", "Col_1_Row_2"], ["Col_3_Row_3", "Col_5_Row_3", "Col_6_Row_3", "Col_2_Row_3", "Col_4_Row_3", "Col_1_Row_3"]]}}

The Question is - Why does the - "columns": within the -- {"data_json":, not maintain the given Order of Column Labels . Rather the Column Labels get Alphabetically sorted ?

Further i call this JsonResponse - dict_json , into DataTables.js and there the Columns maintain their Linkage with the Columnar Data - but the labels are Alphabetically sorted ?

This behaviour changes as i assign Column Labels to the Python DataFrame as seen below - but defeats the purpose of my code . I do not want to feed in the Column Labels after having created the DataFrame.

Below gives desired results :-

df_xls.columns = ['ZaaCol_A','MCol_B','BCol_C','XCol_3','Col_E','Col_F']

A related question would be here - Keep column and row order when storing pandas dataframe in json

but doesnt help me .

Also if it helps - the SO Question from where i took my down stream DataTables.js code . But that totally depends on what JSON im feeding the DataTable thus just for ref... How to display the column headers dynamically in jquery data table

Cannot reproduce. What versions of Python and pandas are you using? — Serge Ballesta, Feb 07 '19 at 15:36

Kyle · Accepted Answer · 2019-02-07T16:11:27.520

Your line:

df_xls = pd.DataFrame({'ZaaCol_A':ls_col_1,'MCol_B':ls_col_2,'BCol_C':ls_col_3,'XCol_3':ls_col_4,'Col_E':ls_col_5,'Col_F':ls_col_6})

is creating a python dict that is passed to the DataFrame constructor. In python versions < 3.7, dicts are unordered. You have two options, use an OrderedDict (from collections) or pass lists of data values and column names in the DataFrame constructor.

Ie:

from collections import OrderedDict
df = pd.DataFrame(OrderedDict([('ZaaCol_A', ls_col_1),('MCol_B',ls_col_2),
    ('BCol_C',ls_col_3),('XCol_3',ls_col_4),('Col_E'k,ls_col_5),('Col_F',ls_col_6)]))

Or:

df = pd.DataFrame(data=[ls_col_1, ls_col_2, ls_col_3, ls_col_4, ls_col_5, ls_col6],
                  columns=['Col_A', 'Col_B', 'Col_C', 'Col_D', 'Col_E', 'Col_F'])

Python DataFrame df.to_json(orient='split') - Doesnt maintain Order of Column Labels

1 Answers1