How can I preserve DataFrame index names when using to_json in pandas?

Question

Right now I have a DataFrame df2 that looks like this:

       Kuwaiti  Non-Kuwaiti   Total
Age                                
0-4     164483       156459  320942
5-9     158377       136935  295312

When I do:

df2.to_json()

I get:

'{"Kuwaiti":{"0-4":164483,"5-9":158377},"Non-Kuwaiti":{"0-4":156459,"5-9":136935},"Total":{"0-4":320942,"5-9":295312}}'

As you can see, df2.index.name is not preserved anywhere.

What can I do to preserve the index name?

mdeff · Accepted Answer · 2017-03-23 22:40:11Z

7

Unfortunately none of the orient parameter we can pass to df2.to_json() will preserve the index name. A solution is to:

Re-index the DataFrame so that the index you want to preserve becomes a normal column:

df2.reset_index(inplace=True)

The DataFrame df2 is now:

    Age    Kuwaiti  Non-Kuwaiti   Total
0   0-4     164483       156459  320942
1   5-9     158377       136935  295312

Save it as a JSON (ideally with an orient that does not preserve the index to save space, 'split' is the most space efficient).
```
df2.to_json('file.json', orient='split')
```

Load and re-index.

df3 = pd.read_json('file.json', orient='split').set_index('Age')

And df3.index.name will be as expected:

       Kuwaiti  Non-Kuwaiti   Total
Age
0-4     164483       156459  320942
5-9     158377       136935  295312

answered Mar 23, 2017 at 22:40

mdeff

1,73318 silver badges21 bronze badges

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

1 Answer 1