Write a Pandas DataFrame to a JSON File

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:

         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 –

Piyush Raj

Piyush is a data professional passionate about using data to understand things better and make informed decisions. He has experience working as a Data Scientist in the consulting domain and holds an engineering degree from IIT Roorkee. His hobbies include watching cricket, reading, and working on side projects.

View all posts