> ## Content Index
> Fetch the complete content index at: https://datascientyst.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to Export DataFrame to JSON with Pandas
- URL: https://datascientyst.com/export-dataframe-to-json-pandas/
- Published: 2022-08-28T09:06:34.000Z
- Updated: 2023-03-18T09:50:39.000Z
- Author: John D K
- Tags: to_json()

In this quick tutorial, we'll show how to **export DataFrame to JSON format in Pandas**. We will cover different export options.

**(1) save DataFrame to a JSON file**

```python
df.to_json('file.json')

```

**(2) change JSON format and data**

```python
df.to_json('file.json', orient='split')

```

**Note:** Read also: [how to save Pandas DataFrame to JSON file without backslashes](https://datascientyst.com/convert-dataframe-to-json-without-backslash-in-pandas/)

There are multiple options for parameter `orient` of method - [to\_json](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to%5Fjson.html?ref=datascientyst.com):

- `split` \- dict  
  - `{‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values]}`
- `records` \- list  
  - `[{column -> value}, … , {column -> value}]`
- `index` \- dict  
  - `{column -> {index -> value}}`
- `columns` \- dict (default for DataFrame)  
  - `{column -> {index -> value}}`
- `values`  
  - just the values array
- `table` \- dict  
  - `{‘schema’: {schema}, ‘data’: {data}}`

**Pro Tip 1**  
For Pandas Series several options are available for export to JSON

{‘split’, ‘records’, ‘index’, ‘table’}

## Setup

Let's create a sample DataFrame which will be exported to a JSON file:

```python
import pandas as pd

data = {'day': [1, 2, 3, 4, 5, 6, 7, 8],
        'temp': [9, 8, 6, 13, 10, 15, 9, 10],
        'humidity': [0.89, 0.86, 0.54, 0.73, 0.45, 0.63, 0.95, 0.67]}

df = pd.DataFrame(data=data)

```

data looks like:

|   | day | temp | humidity |
| - | --- | ---- | -------- |
| 0 | 1   | 9    | 0.89     |
| 1 | 2   | 8    | 0.86     |
| 2 | 3   | 6    | 0.54     |
| 3 | 4   | 13   | 0.73     |
| 4 | 5   | 10   | 0.45     |

## 1: Export DataFrame as JSON file

So let's start with the most simple example - exporting DataFrame as JSON string or a JSON file:

```python
df.to_json('file.json')

```

If we provide the file name we will get a new JSON file created.

The output file path can be provided as relative path:

```python
df.to_json(r'/home/json/file.json')

```

**Pro Tip 1**  

NaN’s

 and 

None

 will be converted to 

null

 and datetime objects will be converted to UNIX timestamps. 

## 2: Export DataFrame as JSON string

Without file parameter - `path_or_buf` we will get JSON string:

```python
df.to_json()

```

which will result into:

```
'{"day":{"0":1,"1":2,"2":3,"3":4,"4":5,"5":6,"6":7,"7":8},"temp":{"0":9,"1":8,"2":6,"3":13,"4":10,"5":15,"6":9,"7":10},"humidity":{"0":0.89,"1":0.86,"2":0.54,"3":0.73,"4":0.45,"5":0.63,"6":0.95,"7":0.67}}'

```

## 3: Export DataFrame as pretty JSON

Using parameter `indent=True` will **prettify the JSON output of Pandas** method `to_json()`:

```python
print(df.to_json(indent=True))

```

result:

```
{
 "day":{
  "0":1,
  "1":2,
  "2":3,
  "3":4,
  "4":5,
  "5":6,
  "6":7,
  "7":8
 },
 "temp":{
  "0":9,
  "1":8,
  "2":6,
  "3":13,
  "4":10,
  "5":15,
  "6":9,
  "7":10
 },
 "humidity":{
  "0":0.89,
  "1":0.86,
  "2":0.54,
  "3":0.73,
  "4":0.45,
  "5":0.63,
  "6":0.95,
  "7":0.67
 }
}

```

## 4: Export DataFrame JSON formats - `orient`

![](https://datascientyst.com/content/images/2022/08/export-dataframe-to-json-pandas.png)

In this section we can see different JSON formats and data outputs.

### 4.1: columns - {column -> {index -> value}}

Let's start by the default export option for DataFrame - `columns`:

```python
df.to_json()

```

is equivalent to `df.to_json(orient='columns')`

```
{"day":{"0":1,"1":2,"2":3,"3":4,"4":5,"5":6,"6":7,"7":8},
"temp":{"0":9,"1":8,"2":6,"3":13,"4":10,"5":15,"6":9,"7":10},
"humidity":{"0":0.89,"1":0.86,"2":0.54,"3":0.73,"4":0.45,"5":0.63,"6":0.95,"7":0.67}}

```

### 4.2: split - {‘index’ -> \[index\], ‘columns’ -> \[columns\], ‘data’ -> \[values\]}

Using `orient` with option `split` will give us:

```python
df.to_json(orient='split')

```

JSON with keys - `columns, index and data`:

```
{"columns":["day","temp","humidity"],
"index":[0,1,2,3,4,5,6,7],
"data":[[1,9,0.89],[2,8,0.86],[3,6,0.54],[4,13,0.73]..]}

```

### 4.3: records - \[{column -> value}, … , {column -> value}\]

For `records` we get list of dictionaries - **each row as a new entry in the output JSON**:

```python
df.to_json(orient='records')

```

result:

```
[
{"day":1,"temp":9,"humidity":0.89},
{"day":2,"temp":8,"humidity":0.86},
{"day":3,"temp":6,"humidity":0.54},
{"day":4,"temp":13,"humidity":0.73},
{"day":5,"temp":10,"humidity":0.45}..]

```

### 4.4: index - {index -> {column -> value}}

With option `index` we got JSON file formatted as - index/data pairs:

```python
df.to_json(orient='index')

```

result:

```
{"0":{"day":1,"temp":9,"humidity":0.89},
"1":{"day":2,"temp":8,"humidity":0.86},
"2":{"day":3,"temp":6,"humidity":0.54},
"3":{"day":4,"temp":13,"humidity":0.73},
"4":{"day":5,"temp":10,"humidity":0.45}..}

```

### 4.5: orient='values' <-> df.values

The option `orient='values'` is similar to `df.values`:

```python
df.to_json(orient='values')

```

resulted JSON data is:

```
[[1,9,0.89],[2,8,0.86],[3,6,0.54],[4,13,0.73],[5,10,0.45],[6,15,0.63],[7,9,0.95],[8,10,0.67]]

```

### 4.6: table - {‘schema’: {schema}, ‘data’: {data}}

Finally we can use the option `table`. This give us **DB like and JSON schema like JSON export of a DataFrame**:

```python
df.to_json(orient='table')

```

which results in next JSON schema:

```
{
 "schema":
 {
  "fields":
  [
   {
    "name": "index",
    "type": "integer"
   },
   {
    "name": "day",
    "type": "integer"
   },
   {
    "name": "temp",
    "type": "integer"
   },
   {
    "name": "humidity",
    "type": "number"
   }
  ],
  "primaryKey":
  [
   "index"
  ],
  "pandas_version": "1.4.0"
 },
 "data":
 [
  {
   "index": 0,
   "day": 1,
   "temp": 9,
   "humidity": 0.89
  },
  {
   "index": 1,
   "day": 2,
   "temp": 8,
   "humidity": 0.86
  },
  {
   "index": 2,
   "day": 3,
   "temp": 6,
   "humidity": 0.54
  },
  {
   "index": 3,
   "day": 4,
   "temp": 13,
   "humidity": 0.73
  }
 ]
}

```

## 5: Export DataFrame as JSON lines

To save **Pandas DataFrame as JSON lines** we can use two parameters:

- `orient='records'`
- `lines=True`

```python
df.to_json(orient='records', lines=True)

```

this gives JSON line output:

```
{"day":1,"temp":9,"humidity":0.89}
{"day":2,"temp":8,"humidity":0.86}
{"day":3,"temp":6,"humidity":0.54}
{"day":4,"temp":13,"humidity":0.73}

```

## Conclusion

In this article we saw how to export DataFrame as a JSON string of files. We show how to pretty print the JSON output.

We show different JSON formats and export to JSON lines.