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.
Python Program
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.
Python Program
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]}
.
Python Program
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}]
.
Python Program
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}}
.
Python Program
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}}
.
Python Program
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.
Python Program
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}}
.
Python Program
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.