Remove index from dataframe before converting to json with split orientation

Question

I am outputting a pandas dataframe to a json object using the following:

df_as_json = df.to_json(orient='split')

In the json object superfluous indexes are stored. I do no want to include these.

To remove them I tried

df_no_index = df.to_json(orient='records')
df_as_json = df_no_index.to_json(orient='split')

However I get a

AttributeError: 'str' object has no attribute 'to_json'

Is there a fast way to reorganize the dataframe so that is does not contain a separate index column during or prior to the .to_json(orient='split') call?

This doesn't work because to_json doesn't return a pandas data frame and therefore the next call of to_json cannot work. — languitar
– languitar, Commented Apr 25, 2017 at 13:17
@StevenG yes, this sends the column names as keys which increases file size by 30% (which I dont want to do). — proximacentauri
– proximacentauri, Commented Apr 25, 2017 at 13:29

Vineet Singh · Accepted Answer · 2021-10-14 11:59:39Z

8

import json module
Convert to json with to_json(orient='split')
Use the json module to load that string to a dictionary
Delete the index key with del json_dict['index']
Convert the dictionary back to json with json.dump or json.dumps

Demo

import json

df = pd.DataFrame([[1, 2], [3, 4]], ['x', 'y'], ['a', 'b'])

json_dict = json.loads(df.to_json(orient='split'))
del json_dict['index']
json.dumps(json_dict)

'{"columns": ["a", "b"], "data": [[1, 2], [3, 4]]}'

edited Oct 14, 2021 at 11:59

Vineet Singh

3477 silver badges14 bronze badges

answered Apr 25, 2017 at 13:23

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

1 Comment

hillmark Over a year ago

Alternatively, go via a dictionary to start with, i.e: data = df.to_dict(orient='split') followed by del data['index']...

MichaelChirico · Accepted Answer · 2019-12-21 18:16:38Z

5

Since two years back pandas (>= v0.23.0) offers an index argument (only valid for orient='split' and orient='table'):

df = pd.DataFrame([[1, 2], [3, 4]], ['x', 'y'], ['a', 'b'])
df.to_json(orient='split', index=True)
# '{"columns":["a","b"],"index":["x","y"],"data":[[1,2],[3,4]]}'
df.to_json(orient='split', index=False)
# '{"columns":["a","b"],"data":[[1,2],[3,4]]}'

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_json.html

edited Dec 21, 2019 at 18:16

answered Dec 21, 2019 at 18:09

MichaelChirico

34.9k17 gold badges122 silver badges209 bronze badges

Collectives™ on Stack Overflow

Remove index from dataframe before converting to json with split orientation

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related