Retain column order when loading JSON to Pandas dataframe

Question

I'm trying to make sense of this.

I have this JSON file:

[
 {
   "id": 1,
   "Z": "a",
   "C": "z",
   "D": "text"
 },
 {
   "id": 2,
   "Z": "b",
   "C": "y",
   "D": "text"
 },
 {
   "id": 3,
   "Z": "c",
   "C": "x",
   "D": "text"
 }
]

The order of the data is meaningful and needs to be retained, both for rows and columns. I need to load it to a dataframe. So, here's what happens:

In [1]: import json

In [2]: from pandas.io.json import json_normalize

In [3]: with open('test.json') as f:
   ...:     json_data = json.load(f)
   ...:

In [4]: json_data
Out[4]:
[{'id': 1, 'Z': 'a', 'C': 'z', 'D': 'text'},
 {'id': 2, 'Z': 'b', 'C': 'y', 'D': 'text'},
 {'id': 3, 'Z': 'c', 'C': 'x', 'D': 'text'}]

In [5]: df = json_normalize(json_data)

In [6]: df
Out[6]:
   C     D  Z  id
0  z  text  a   1
1  y  text  b   2
2  x  text  c   3

As you can see, while rows are still in the right order, columns where sorted alphabetically. How can I keep my column order (or restore it)?

Thanks!

perl · Accepted Answer · 2019-04-03 09:22:35Z

5

You can reindex the columns with the list of keys of the first element of the dictionary (assuming the first element has its keys in the desired order):

df = df.reindex(columns=list(json_data[0].keys()))

print(df)

Output:

   id  Z  C     D
0   1  a  z  text
1   2  b  y  text
2   3  c  x  text

answered Apr 3, 2019 at 9:22

perl

9,9811 gold badge14 silver badges23 bronze badges

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

Comments

U13-Forward · Accepted Answer · 2019-04-03 09:22:55Z

4

Use this:

df = df[list(json_data[0].keys())]

answered Apr 3, 2019 at 9:22

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

Retain column order when loading JSON to Pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related