Pandas JSON

JSON（JavaScript Object Notation, JavaScript Object Notation), is a syntax for storing and exchanging text information, similar to XML.

JSON is smaller, faster, and easier to parse than XML. For more JSON content, refer toJSON Tutorial。

Pandas provides powerful methods to handle JSON format data, supporting reading data from JSON files or strings and converting it to a DataFrame, as well as converting a DataFrame back to JSON format.

Operation	Method	Description
Read data from JSON file/string	`pd.read_json()`	Read and load JSON data as a DataFrame
Convert DataFrame to JSON	`DataFrame.to_json()`	Convert DataFrame to JSON format data, you can specify the structuring method
Supported JSON structuring formats	`orient`Parameter	Supports multiple structuring formats, such as`split`、`records`、`columns`

Pandas can handle JSON data very conveniently. This article usessites.jsonas an example, with the following content:

Example

[ { "id": "A001", "name": "Example", "url": "www.example.com", "likes": 61 }, { "id": "A002", "name": "Google", "url": "www.google.com", "likes": 124 }, { "id": "A003", "name": "Taobao", "url": "www.taobao.com", "likes": 45 } ]

Load data from JSON file/string

pd.read_json() - Read JSON data

read_json() is used to read and load JSON format data as a DataFrame. It supports loading data from JSON files, JSON strings, or JSON URLs.

Syntax format:

import pandas as pd

df = pd.read_json(
    path_or_buffer,      # JSON 文件路径、JSON 字符串或 URL
    orient=None,         # JSON 数据的结构方式，默认是 'columns'
    dtype=None,          # 强制指定列的数据类型
    convert_axes=True,   # 是否转换行列索引
    convert_dates=True,  # 是否将日期解析为日期类型
    keep_default_na=True # 是否保留默认的缺失值标记
)

Parameter description:

Parameter	Description	Default value
`path_or_buffer`	The path to the JSON file, a JSON string, or a URL	Required parameter
`orient`	Defines the formatting method of JSON data. Common values are`split`、`records`、`index`、`columns`、`values`。	`None`(automatically inferred from the file)
`dtype`	Forcefully specify the data type of columns	`None`
`convert_axes`	Whether to convert axes to appropriate data types	`True`
`convert_dates`	Whether to parse dates as date types	`True`
`keep_default_na`	Whether to keep default missing value markers (such as`NaN`）	`True`

Common orient parameter options:

orient value	JSON format example	Description
`split`	`{"index":["a","b"],"columns":["A","B"],"data":[[1,2],[3,4]]}`	Use keys`index`、`columns`and`data`Structure
`records`	`[{"A":1,"B":2},{"A":3,"B":4}]`	Each record is a dictionary representing one row of data
`index`	`{"a":{"A":1,"B":2},"b":{"A":3,"B":4}}`	Uses index as keys, with values as dictionaries
`columns`	`{"A":{"a":1,"b":3},"B":{"a":2,"b":4}}`	Uses column names as keys, with values as dictionaries
`values`	`[[1,2],[3,4]]`	Only returns data, excluding index and column names

Load data from a JSON file:

Example

import pandas as pd

df = pd.read_json('sites.json')

print(df.to_string())

to_string()Used to return DataFrame-type data. We can also directly process JSON strings.

Example

import pandas as pd

data =[
{
"id": "A001",
"name": "Example",
"url": "www.example.com",
"likes": 61
},
{
"id": "A002",
"name": "Google",
"url": "www.google.com",
"likes": 124
},
{
"id": "A003",
"name": "Taobao",
"url": "www.taobao.com",
"likes": 45
}
]
df = pd.DataFrame(data)

print(df)

The output of the above example is:

     id    name             url  likes
0  A001    Example  www.example.com     61
1  A002  Google  www.google.com    124
2  A003      淘宝  www.taobao.com     45

JSON objects have the same format as Python dictionaries, so we can directly convert a Python dictionary into DataFrame data:

Example

import pandas as pd

# JSON in dictionary format
s = {
"col1":{"row1":1,"row2":2,"row3":3},
"col2":{"row1":"x","row2":"y","row3":"z"}
}

# Read JSON and convert to DataFrame
df = pd.DataFrame(s)
print(df)

The output of the above example is:

      col1 col2
row1     1    x
row2     2    y
row3     3    z

Read JSON data from a URL:

Example

import pandas as pd

URL = 'https://static.jyshare.com/download/sites.json'
df = pd.read_json(URL)
print(df)

The output of the above example is:

     id    name             url  likes
0  A001    Example  www.example.com     61
1  A002  Google  www.google.com    124
2  A003      淘宝  www.taobao.com     45

Load data from a JSON string:

Example

import pandas as pd

# JSON string
json_data = '''
[
{"Name": "Alice", "Age": 25, "City": "New York"},
{"Name": "Bob", "Age": 30, "City": "Los Angeles"},
{"Name": "Charlie", "Age": 35, "City": "Chicago"}
]
'''

# Read data from JSON string
df = pd.read_json(json_data)

print(df)

Output:

      Name  Age           City
0    Alice   25       New York
1      Bob   30    Los Angeles
2  Charlie   35        Chicago

Read from JSON data (specify orient as 'records'):

Example

import pandas as pd

# JSON data
json_data = '''
[
{"Name": "Alice", "Age": 25, "City": "New York"},
{"Name": "Bob", "Age": 30, "City": "Los Angeles"},
{"Name": "Charlie", "Age": 35, "City": "Chicago"}
]
'''

# Read data from JSON string, specify orient='records'
df = pd.read_json(json_data, orient='records')

print(df)

Output:

      Name  Age           City
0    Alice   25       New York
1      Bob   30    Los Angeles
2  Charlie   35        Chicago

Nested JSON data

Suppose there is a set of nested JSON data filesnested_list.json ：

Contents of nested_list.json file

{
"school_name": "ABC primary school",
"class": "Year 1",
"students": [
{
"id": "A001",
"name": "Tom",
"math": 60,
"physics": 66,
"chemistry": 61
},
{
"id": "A002",
"name": "James",
"math": 89,
"physics": 76,
"chemistry": 51
},
{
"id": "A003",
"name": "Jenny",
"math": 79,
"physics": 90,
"chemistry": 78
}]
}

Use the following code to format the complete content:

Example

import pandas as pd

df = pd.read_json('nested_list.json')

print(df)

The output of the above example is:

          school_name   class                                           students
0  ABC primary school  Year 1  {'id': 'A001', 'name': 'Tom', 'math': 60, 'phy...
1  ABC primary school  Year 1  {'id': 'A002', 'name': 'James', 'math': 89, 'p...
2  ABC primary school  Year 1  {'id': 'A003', 'name': 'Jenny', 'math': 79, 'p...

At this point we need to use thejson_normalize()method to fully parse the nested data:

Example

import pandas as pd
import json

# Load data using the Python JSON module
with open('nested_list.json','r') as f:
data = json.loads(f.read())

# Flatten data
df_nested_list = pd.json_normalize(data, record_path =['students'])
print(df_nested_list)

The output of the above example is:

     id   name  math  physics  chemistry
0  A001    Tom    60       66         61
1  A002  James    89       76         51
2  A003  Jenny    79       90         78

data = json.loads(f.read())Use the Python JSON module to load data.

json_normalize()Used the parameterrecord_pathand set it to['students']to flatten nested JSON datastudents。

The displayed result does not include the school_name and class elements. If you need to show them, you can use the meta parameter to display these metadata:

Example

import pandas as pd
import json

# Load data using the Python JSON module
with open('nested_list.json','r') as f:
data = json.loads(f.read())

# Flatten data
df_nested_list = pd.json_normalize(
data,
record_path =['students'],
meta=['school_name', 'class']
)
print(df_nested_list)

The output of the above example is:

     id   name  math  physics  chemistry         school_name   class
0  A001    Tom    60       66         61  ABC primary school  Year 1
1  A002  James    89       76         51  ABC primary school  Year 1
2  A003  Jenny    79       90         78  ABC primary school  Year 1

Next, let's try to read more complex JSON data that nests lists and dictionaries. The data filenested_mix.jsonis as follows:

Contents of nested_mix.json file

{
"school_name": "local primary school",
"class": "Year 1",
"info": {
"president": "John Kasich",
"address": "ABC road, London, UK",
"contacts": {
"email": "[email protected]",
"tel": "123456789"
}
},
"students": [
{
"id": "A001",
"name": "Tom",
"math": 60,
"physics": 66,
"chemistry": 61
},
{
"id": "A002",
"name": "James",
"math": 89,
"physics": 76,
"chemistry": 51
},
{
"id": "A003",
"name": "Jenny",
"math": 79,
"physics": 90,
"chemistry": 78
}]
}

Convert the nested_mix.json file to a DataFrame:

Example

import pandas as pd
import json

# Load data using the Python JSON module
with open('nested_mix.json','r') as f:
data = json.loads(f.read())

df = pd.json_normalize(
data,
record_path =['students'],
meta=[
'class',
['info', 'president'],
['info', 'contacts', 'tel']
]
)

print(df)

The output of the above example is:

     id   name  math  physics  chemistry   class info.president info.contacts.tel
0  A001    Tom    60       66         61  Year 1    John Kasich         123456789
1  A002  James    89       76         51  Year 1    John Kasich         123456789
2  A003  Jenny    79       90         78  Year 1    John Kasich         123456789

Read a set of data from nested data

The following is the example filenested_deep.json, we only read the nested data'smathfield

Contents of nested_deep.json file

{
"school_name": "local primary school",
"class": "Year 1",
"students": [
{
"id": "A001",
"name": "Tom",
"grade": {
"math": 60,
"physics": 66,
"chemistry": 61
}

},
{
"id": "A002",
"name": "James",
"grade": {
"math": 89,
"physics": 76,
"chemistry": 51
}

},
{
"id": "A003",
"name": "Jenny",
"grade": {
"math": 79,
"physics": 90,
"chemistry": 78
}
}]
}

Here we need to use theglommodule to handle data nesting,glomThe module allows us to use.to access the properties of nested objects.

Before using it for the first time, we need to install glom:

pip3 install glom

Example

import pandas as pd
from glom import glom

df = pd.read_json('nested_deep.json')

data = df['students'].apply(lambda row: glom(row, 'grade.math'))
print(data)

The output of the above example is:

0    60
1    89
2    79
Name: students, dtype: int64

Convert DataFrame to JSON

DataFrame.to_json() - Convert DataFrame to JSON data

The to_json() method is used to convert a DataFrame to JSON format data, and you can specify the structuring method of the JSON.

Syntax format:

df.to_json(
    path_or_buffer=None,    # 输出的文件路径或文件对象，如果是 None 则返回 JSON 字符串
    orient=None,            # JSON 格式方式，支持 'split', 'records', 'index', 'columns', 'values'
    date_format=None,       # 日期格式，支持 'epoch', 'iso'
    default_handler=None,   # 自定义非标准类型的处理函数
    lines=False,            # 是否将每行数据作为一行（适用于 'records' 或 'split'）
    encoding='utf-8'        # 编码格式
)

Parameter description:

Parameter	Description	Default value
`path_or_buffer`	The output file path or file object; if it is`None`, then returns a JSON string	`None`
`orient`	Specifies the JSON format structure; supports`split`、`records`、`index`、`columns`、`values`	`None`(default is`columns`）
`date_format`	date format, supports`'epoch'`or`'iso'`format	`None`
`default_handler`	Custom handling of non-standard types (such as`datetime`etc.) processing function	`None`
`lines`	Whether to output each row of data as one line (applicable to`records`or`split`）	`False`
`encoding`	Encoding format of the output file	`'utf-8'`

Example

Convert DataFrame to JSON file (specify orient='records'):

Example

import pandas as pd

# Create DataFrame
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['New York', 'Los Angeles', 'Chicago']
})

# Convert DataFrame to JSON file, specify orient='records'
df.to_json('data.json', orient='records', lines=True)

# Output the generated file content:
# [
# {"Name":"Alice","Age":25,"City":"New York"},
# {"Name":"Bob","Age":30,"City":"Los Angeles"},
# {"Name":"Charlie","Age":35,"City":"Chicago"}
# ]

Convert DataFrame to JSON and specify date format:

Example

import pandas as pd

# Create DataFrame containing date data
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie'],
'Date': pd.to_datetime(['2021-01-01', '2022-02-01', '2023-03-01']),
'Age': [25, 30, 35]
})

# Convert DataFrame to JSON, and specify date format as 'iso'
json_str = df.to_json(date_format='iso')

print(json_str)
<p>

Output:

{"Name":{"0":"Alice","1":"Bob","2":"Charlie"},"Date":{"0":"2021-01-01T00:00:00.000Z","1":"2022-02-01T00:00:00.000Z","2":"2023-03-01T00:00:00.000Z"},"Age":{"0":25,"1":30,"2":35}}

Other Extensions

Pandas CSV Files

Pandas Data Cleaning

Data Reading and Writing

Data Processing Core

Reference Manual

Statistics and Cases

Pandas JSON

Example

Load data from JSON file/string

pd.read_json() - Read JSON data

Example

Example

Example

Example

Example

Example

Nested JSON data

Contents of nested_list.json file

Example

Example

Example

Contents of nested_mix.json file

Example

Read a set of data from nested data

Contents of nested_deep.json file

Example

Convert DataFrame to JSON

DataFrame.to_json() - Convert DataFrame to JSON data

Example

Example

Example