Pandas DataFrame to_json() results in index with dot notation

Question

I have a Pandas DataFrame that I need to convert to JSON. The to_json() DataFrame method results in an acceptable format, but it converts my DataFrame index to strings (e.g. 0 becomes "0.0"). I need "0".

The DataFrame comes from JSON using the pd.io.json.read_json() method, which sets the index to float64.

Input JSON:

{"chemical": {"1": "chem2", "0": "chem1"}, 
"type": {"1": "pesticide", "0": "pesticide"}}

DataFrame (from read_json()):

  chemical  type
0 chem1    pesticide
1 chem2    pesticide

Produced JSON (from to_json()):

{"chemical": {"0.0": "chem1", "1.0": "chem2"},
"type": {"0.0": "pesticide", "1.0": "pesticide"}}

Needed JSON:

{"chemical": {"0": "chem1", "1": "chem2"},
"type": {"0": "pesticide", "1": "pesticide"}}

Flash · Accepted Answer · 2015-03-06 15:37:10Z

1

@shx2 pointed me in the right direction, but I changed my approach to creating the DataFrame from JSON.

Instead of using the to_json() method on a JSON string, I used the pd.DataFrame.from_dict() method on the JSON as a Python dictionary to create the DataFrame. This results in df.index.dtype == dtype('O')

I had to set dtype='float64' in the from_dict() method to set the correct dtype for the non-string entries.

pd_obj = pd.DataFrame.from_dict(request.json["inputs"], dtype='float64')

edited Mar 6, 2015 at 15:37

answered Mar 4, 2015 at 21:46

Flash

5632 gold badges8 silver badges21 bronze badges

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

Comments

shx2 · Accepted Answer · 2015-03-03 21:25:43Z

0

Seems like the dtype of the index is float (check df.index.dtype). You need to convert it to int:

df.index = df.index.astype(int)
df.to_json()
=> {"chemical": {"0": "chem1", "1": "chem2"}, "type": {"0": "pesticide", "1": "pesticide"}}

answered Mar 3, 2015 at 21:25

shx2

64.8k17 gold badges139 silver badges166 bronze badges

2 Comments

Flash Over a year ago

My index is type == float64, and trying to cast it to int throws the following:

TypeError: Setting <class 'pandas.core.index.Float64Index'> dtype to anything other than float64 or object is not supported

Flash Over a year ago

The pd.io.json.read_json() method sets the index to float64, which I didn't mention in my OP. This causes the to_json() method to represent my indexes as floats (e.g 0 -> "0.0").

Collectives™ on Stack Overflow

Pandas DataFrame to_json() results in index with dot notation

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related