Pandas – Convert DataFrame to JSON

Pandas – Convert DataFrame to JSON

To convert a DataFrame to JSON string in Pandas, call DataFrame.to_json() function and pass the required arguments.

1. Convert DataFrame to JSON with Default Parameters

In this example, we take a DataFrame with five rows, and convert this DataFarme to JSON using DataFrame.to_json() function.

Since, no path is specified, to_json() returns the JSON as a string.

import pandas as pd

df = pd.DataFrame({'name':['a', 'b', 'c'],
                   'age' :[20, 21, 20]})
result = df.to_json()
print(result)

Output

{"name":{"0":"a","1":"b","2":"c"},"age":{"0":20,"1":21,"2":20}}

2. Convert DataFrame to JSON file

Pass the file name to to_json() function via path_or_buf parameter. The resulting JSON String is written to the specified file path.

import pandas as pd

df = pd.DataFrame({'name':['a', 'b', 'c'],
                   'age' :[20, 21, 20]})
df.to_json(path_or_buf='output.json')

output.json

{"name":{"0":"a","1":"b","2":"c"},"age":{"0":20,"1":21,"2":20}}

3. Convert DataFrame to JSON in split format

If parameter orient='split' is passed to to_json() function, then the format of the resulting JSON string is dictionary like {‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values]}.

import pandas as pd

df = pd.DataFrame({'name':['a', 'b', 'c'],
                   'age' :[20, 21, 20]})
result = df.to_json(orient='split')
print(result)

Output

{"columns":["name","age"],"index":[0,1,2],"data":[["a",20],["b",21],["c",20]]}

4. Convert DataFrame to JSON in records format

If parameter orient='records' is passed to to_json() function, then the format of the resulting JSON string is list like [{column -> value}, … , {column -> value}].

import pandas as pd

df = pd.DataFrame({'name':['a', 'b', 'c'],
                   'age' :[20, 21, 20]})
result = df.to_json(orient='records')
print(result)

Output

[{"name":"a","age":20},{"name":"b","age":21},{"name":"c","age":20}]

5. Convert DataFrame to JSON in index format

If parameter orient='index' is passed to to_json() function, then the format of the resulting JSON string is dictionary like {index -> {column -> value}}.

import pandas as pd

df = pd.DataFrame({'name':['a', 'b', 'c'],
                   'age' :[20, 21, 20]})
result = df.to_json(orient='index')
print(result)

Output

{"0":{"name":"a","age":20},"1":{"name":"b","age":21},"2":{"name":"c","age":20}}

6. Convert DataFrame to JSON in columns format

If parameter orient='columns' is passed to to_json() function, then the format of the resulting JSON string is dictionary like {column -> {index -> value}}.

import pandas as pd

df = pd.DataFrame({'name':['a', 'b', 'c'],
                   'age' :[20, 21, 20]})
result = df.to_json(orient='columns')
print(result)

Output

{"name":{"0":"a","1":"b","2":"c"},"age":{"0":20,"1":21,"2":20}}

7. Convert DataFrame to JSON in values format

If parameter orient='values' is passed to to_json() function, then the format of the resulting JSON string is like values array.

Only values in the columns shall be converted to JSON. Information about index and column names is lost.

import pandas as pd

df = pd.DataFrame({'name':['a', 'b', 'c'],
                   'age' :[20, 21, 20]})
result = df.to_json(orient='values')
print(result)

Output

[["a",20],["b",21],["c",20]]

8. Convert DataFrame to JSON in table format

If parameter orient='table' is passed to to_json() function, then the format of the resulting JSON string is dictionary like {‘schema’: {schema}, ‘data’: {data}}.

import pandas as pd

df = pd.DataFrame({'name':['a', 'b', 'c'],
                   'age' :[20, 21, 20]})
result = df.to_json(orient='table')
print(result)

Output

{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"name","type":"string"},{"name":"age","type":"integer"}],"primaryKey":["index"],"pandas_version":"0.20.0"},"data":[{"index":0,"name":"a","age":20},{"index":1,"name":"b","age":21},{"index":2,"name":"c","age":20}]}

Summary

In this Pandas Tutorial, we learned how to convert a DataFrame to JSON, in different formats.