Pandas dataframes are great for working with tabular data in python. Once you’re done with cleaning/manipulating/analyzing the data, you can save it to a range of different formats. For example, as a CSV or an Excel file. You can also save your dataframe as a JSON file. In this tutorial, we will look at how to write a pandas dataframe to a JSON file.
How to save dataframe to a JSON file?
To save a pandas dataframe as a JSON file, you can use the pandas to_json()
function. It also comes with a number of useful arguments to customize the JSON file. For example, you can use the orient
parameter to indicate the expected JSON string format. The following is the syntax:
# save dataframe to json file df.to_json("filename.json")
The to_json()
function saves the dataframe as a JSON file and returns the respective JSON string.
Examples
Let’s look at the different use-cases of the pandas to_json()
function with the help of examples. First, we will create a sample dataframe that we will be using throughout the tutorial.
import pandas as pd # create dataframe of Pokemons df = pd.DataFrame({ 'Name': ['Bulbasaur', 'Charmander', 'Squirtle'], 'Type': ['Grass', 'Fire', 'Water'], 'Height (m)': [0.7, 0.6, 0.4], 'Weight (Kg)': [6.9, 8.5, 9.0] }) # display the dataframe print(df)
Output:
Highlighted programs for you
Flatiron School
Flatiron School
University of Maryland Global Campus
University of Maryland Global Campus
Creighton University
Creighton University
Name Type Height (m) Weight (Kg) 0 Bulbasaur Grass 0.7 6.9 1 Charmander Fire 0.6 8.5 2 Squirtle Water 0.4 9.0
We now have a dataframe storing the Name, Type, Height, and Weight information on three Pokemons. Let’s now go ahead and save the dataframe as a JSON file.
# save dataframe to json file df.to_json("pokemon_info.json")
This is how the JSON file looks when viewed through a JSON viewer.
{ "Name": { "0": "Bulbasaur", "1": "Charmander", "2": "Squirtle" }, "Type": { "0": "Grass", "1": "Fire", "2": "Water" }, "Height (m)": { "0": 0.7, "1": 0.6, "2": 0.4 }, "Weight (Kg)": { "0": 6.9, "1": 8.5, "2": 9 } }
With the pandas to_json()
function, you can determine the orientation of the JSON string using the orient
parameters. Let’s look through the different values you can use for this parameter through examples.
1. Using orient='split'
The “split” orientation is used to group the column name, index, and data separately.
# use orient="split" df.to_json("pokemon_info.json", orient="split")
This is how the JSON file looks when viewed through a JSON viewer.
{ "columns": [ "Name", "Type", "Height (m)", "Weight (Kg)" ], "index": [ 0, 1, 2 ], "data": [ [ "Bulbasaur", "Grass", 0.7, 6.9 ], [ "Charmander", "Fire", 0.6, 8.5 ], [ "Squirtle", "Water", 0.4, 9 ] ] }
You can see that in the “split” orientation, the column name, index, and the data are split together.
2. Using orient='records'
The “records” orientation has groups on a record level.
# use orient="records" df.to_json("pokemon_info.json", orient="records")
This is how the JSON file looks when viewed through a JSON viewer.
[ { "Name": "Bulbasaur", "Type": "Grass", "Height (m)": 0.7, "Weight (Kg)": 6.9 }, { "Name": "Charmander", "Type": "Fire", "Height (m)": 0.6, "Weight (Kg)": 8.5 }, { "Name": "Squirtle", "Type": "Water", "Height (m)": 0.4, "Weight (Kg)": 9 } ]
You can see that in the “records” orientation, each record is present separately. Also, note that the index labels are not preserved in this format.
3. Using orient='index'
The “index” orientation has groups on a record level with the index information preserved.
# use orient="index" df.to_json("pokemon_info.json", orient="index")
This is how the JSON file looks when viewed through a JSON viewer.
{ "0": { "Name": "Bulbasaur", "Type": "Grass", "Height (m)": 0.7, "Weight (Kg)": 6.9 }, "1": { "Name": "Charmander", "Type": "Fire", "Height (m)": 0.6, "Weight (Kg)": 8.5 }, "2": { "Name": "Squirtle", "Type": "Water", "Height (m)": 0.4, "Weight (Kg)": 9 } }
You can see that in the “index” orientation, each record is present along with its index.
4. Using orient='columns'
The “columns” orientation has groups on a column level with the data pertaining to a column present together.
# use orient="columns" df.to_json("pokemon_info.json", orient="columns")
This is how the JSON file looks when viewed through a JSON viewer.
{ "Name": { "0": "Bulbasaur", "1": "Charmander", "2": "Squirtle" }, "Type": { "0": "Grass", "1": "Fire", "2": "Water" }, "Height (m)": { "0": 0.7, "1": 0.6, "2": 0.4 }, "Weight (Kg)": { "0": 6.9, "1": 8.5, "2": 9 } }
5. Using orient='values'
The “values” orientation stores just the values array.
# use orient="values" df.to_json("pokemon_info.json", orient="values")
This is how the JSON file looks when viewed through a JSON viewer.
[ [ "Bulbasaur", "Grass", 0.7, 6.9 ], [ "Charmander", "Fire", 0.6, 8.5 ], [ "Squirtle", "Water", 0.4, 9 ] ]
6. Using orient='table'
The “table” orientation stores a very detailed version of the dataframe including its schema, primary key, the pandas version, and the data.
# use orient="table" df.to_json("pokemon_info.json", orient="table")
This is how the JSON file looks when viewed through a JSON viewer.
{ "schema": { "fields": [ { "name": "index", "type": "integer" }, { "name": "Name", "type": "string" }, { "name": "Type", "type": "string" }, { "name": "Height (m)", "type": "number" }, { "name": "Weight (Kg)", "type": "number" } ], "primaryKey": [ "index" ], "pandas_version": "0.20.0" }, "data": [ { "index": 0, "Name": "Bulbasaur", "Type": "Grass", "Height (m)": 0.7, "Weight (Kg)": 6.9 }, { "index": 1, "Name": "Charmander", "Type": "Fire", "Height (m)": 0.6, "Weight (Kg)": 8.5 }, { "index": 2, "Name": "Squirtle", "Type": "Water", "Height (m)": 0.4, "Weight (Kg)": 9 } ] }
For more on the pandas to_json() function, refer to its documentation.
With this, we come to the end of this tutorial. The code examples and results presented in this tutorial have been implemented in a Jupyter Notebook with a python (version 3.8.3) kernel having pandas version 1.0.5
Subscribe to our newsletter for more informative guides and tutorials.
We do not spam and you can opt out any time.
Tutorials on performing read/write operations on pandas dataframes –
- Write a Pandas DataFrame to a JSON File
- Save Pandas DataFrame to a Pickle File
- Pandas – Append dataframe to existing CSV
- Read Pickle File as a Pandas DataFrame
- Copy Pandas DataFrame to the Clipboard
- Pandas – Read only the first n rows of a CSV file
- Pandas – Save DataFrame to an Excel file
- Read CSV files using Pandas – With Examples
- Save Pandas DataFrame to a CSV file