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?

Answer 1

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

1. 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
   

2. 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')
   

3. 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