Pandas dataframe format for json

Question

I have a large pandas table framework for converting to JSON. The standard .to_json () functions do not make a compact format for JSON. How to get JSON output form like this using pandas only?

{"index": [ 0, 1 ,3 ],
 "col1": [ "250", "1" ,"3" ],
 "col2": [ "250", "1" ,"3" ]
}

This is a form of compact JSON format for tabular data. (I can do a loop over the lines .... but)

Answer 1

It seems you need to to_dictfirst and then dictto json:

df = pd.DataFrame({"index": [ 0, 1 ,3 ],
 "col1": [ "250", "1" ,"3" ],
 "col2": [ "250", "1" ,"3" ]
})
print (df)
  col1 col2  index
0  250  250      0
1    1    1      1
2    3    3      3


print (df.to_dict(orient='list'))
{'col1': ['250', '1', '3'], 'col2': ['250', '1', '3'], 'index': [0, 1, 3]}

---

import json

print (json.dumps(df.to_dict(orient='list')))
{"col1": ["250", "1", "3"], "col2": ["250", "1", "3"], "index": [0, 1, 3]}

Because it is not implemented yet :

print (df.to_json(orient='list'))

ValueError: invalid "list" value for "orient" option

EDIT:

If the index is not a column, add reset_index:

df = pd.DataFrame({"col1": [250, 1, 3],
                   "col2": [250, 1, 3]})
print (df)
   col1  col2
0   250   250
1     1     1
2     3     3

print (df.reset_index().to_dict(orient='list'))
{'col1': [250, 1, 3], 'index': [0, 1, 2], 'col2': [250, 1, 3]}

Answer 2

to_dict json ( index , assign):

import json

df = pd.DataFrame({"col1": [250, 1, 3],
                   "col2": [250, 1, 3]})

json_dict = df.assign(index=df.index).to_dict(orient="list")
print(json.dumps(json_dict))

>>> '{"index": [0, 1, 2], "col1": [250, 1, 3], "col2": [250, 1, 3]}'