Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas dataframe to json list format

Tags:

json

pandas

I have large pandas tabular dataframe to convert into JSON. The standard .to_json() functions does not make a compact format for JSON. How to get JSON output forma like this, using pandas only ?

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

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

like image 479
tensor Avatar asked Mar 31 '17 07:03

tensor


People also ask

How do I turn a Pandas DataFrame into a list?

At times, you may need to convert your pandas dataframe to List. To accomplish this task, ' tolist() ' function can be used.

What is Orient in pandas?

orient: String value, ('dict', 'list', 'series', 'split', 'records', 'index') Defines which dtype to convert Columns(series into). For example, 'list' would return a dictionary of lists with Key=Column name and Value=List (Converted series). into: class, can pass an actual class or instance.

What is Orient in JSON?

orient : Indication of expected JSON string format. date_format : None, 'epoch', 'iso'} double_precision : The number of decimal places to use when encoding floating point values. force_ascii : Force encoded string to be ASCII. date_unit : string, default 'ms' (milliseconds)


1 Answers

It seems you need to_dict first and then dict to 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 value 'list' for option 'orient'

EDIT:

If index is not 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]}
like image 170
jezrael Avatar answered Sep 24 '22 07:09

jezrael