CODEWITHSUNDEEP
jsonpython-2.7pandas
Flash
Flash

Reputation: 563

Pandas DataFrame to_json() results in index with dot notation

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"}}

Upvotes: 0

Views: 1149

Answers (2)

Flash
Flash

Reputation: 563

@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')

Upvotes: 1

shx2
shx2

Reputation: 64298

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"}}

Upvotes: 0

Related Questions