Clever Geek Handbook

Preserve column and row order when storing pandas dataframe in json

When storing data in a json object with to_json and reading it with read_json, the rows and columns are returned sorted alphabetically. Is there a way to save the order results or reorder them when searching?

+6


source to share


2 answers


You can use orient='split'

one that stores information about indexes and columns in lists that preserve order:

In [34]: df
Out[34]: 
   A  C  B
5  0  1  2
4  3  4  5
3  6  7  8

In [35]: df.to_json(orient='split')
Out[35]: '{"columns":["A","C","B"],"index":[5,4,3],"data":[[0,1,2],[3,4,5],[6,7,8]]}'

In [36]: pd.read_json(df.to_json(orient='split'), orient='split')
Out[36]: 
   A  C  B
5  0  1  2
4  3  4  5
3  6  7  8


Remember to also use orient='split'

when reading, or you will get

In [37]: pd.read_json(df.to_json(orient='split'))
Out[37]: 
  columns       data  index
0       A  [0, 1, 2]      5
1       C  [3, 4, 5]      4
2       B  [6, 7, 8]      3
+11


source


If you want to create a format with "orient = 'records'" and preserve the column order, try creating a function like this. I do not think this is a wise approach, and I do not recommend it because it does not guarantee its order.

def df_to_json(df):
    res_arr = []
    ldf = df.copy()
    ldf=ldf.fillna('')
    lcolumns = [ldf.index.name] + list(ldf.columns)
    for key, value in ldf.iterrows():
        lvalues = [key] + list(value)
        res_arr.append(dict(zip(lcolumns, lvalues)))
    return json.dumps(res_arr)


Also, for reading without a sorted column, please use the link [link] ( Python json.loads changes the order of the object )

Good luck

0


source






More articles:


All Articles