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

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