5 Best Ways to Convert Pandas DataFrames to Nested JSON

by

💡 Problem Formulation: Converting data structures between formats is a common task in data science. Given a DataFrame in pandas, the Python data analysis library, one might need to export it as a nested JSON object for web applications, APIs, or other purposes where JSON is the preferred format. This article demonstrates several ways to achieve this, with an example DataFrame containing user information that needs to be represented in JSON as {‘users’: [{‘name’: ‘John’, ‘age’: 30, …}, …]}.

Method 1: Using pandas to_json() with a Custom Function

This method involves using the pandas to_json() function and specifying a custom function to control the nesting. This allows for a high level of customization and can handle complex nesting requirements.

Here’s an example:

import pandas as pd

# Sample DataFrame
df = pd.DataFrame({
    'name': ['John', 'Jane'],
    'age': [30, 25],
    'city': ['New York', 'Los Angeles']
})

# Custom function to create nested JSON
def df_to_nested_json(df, key='users'):
    result = {key: df.to_dict(orient='records')}
    return result

nested_json = df_to_nested_json(df)
print(nested_json)

Output:

{
    "users": [
        {"name": "John", "age": 30, "city": "New York"},
        {"name": "Jane", "age": 25, "city": "Los Angeles"}
    ]
}

This code snippet defines a pandas DataFrame with user data and a custom function to nest the data under a specified key, resulting in a JSON object with the desired structure.

Method 2: Using the groupby() and apply() Methods

With this method, pandas’ groupby() can be used to group the data by a certain key, and then applying a function that will convert each group to a JSON format. This is useful when needing to nest JSON based on unique values.

Here’s an example:

import pandas as pd
import json

df = pd.DataFrame({
    'group': ['A', 'A', 'B', 'B'],
    'name': ['Alice', 'Bob', 'Charlie', 'David'],
    'age': [25, 30, 35, 40]
})

grouped = df.groupby('group')
nested_json = grouped.apply(lambda x: x.drop('group', axis=1).to_dict(orient='records')).to_json()

print(json.loads(nested_json))

Output:

{
    "A": [{"name": "Alice", "age": 25}, {"name": "Bob", "age": 30}],
    "B": [{"name": "Charlie", "age": 35}, {"name": "David", "age": 40}]
}

This code snippet groups rows by the ‘group’ column and then converts each group to a JSON list, resulting in a nested JSON structure keyed by unique group names.

Method 3: Using json_normalize()

The json_normalize() function in pandas flattens the structure of the JSON and can handle nested records, which can then be converted back into a JSON format with additional structures as required.

Here’s an example:

import pandas as pd
from pandas.io.json import json_normalize

data = [
    {'id': 1, 'name': {'first': 'John', 'last': 'Doe'}},
    {'id': 2, 'name': {'first': 'Anna', 'last': 'Smith'}}
]

df = json_normalize(data)

nested_json = df.to_json(orient='records')
print(nested_json)

Output:

[
    {"id": 1, "name.first": "John", "name.last": "Doe"},
    {"id": 2, "name.first": "Anna", "name.last": "Smith"}
]

This snippet demonstrates how to handle pre-nested structures within JSON data, flatten them with json_normalize(), and then convert the resulting DataFrame into a JSON format.

Method 4: Building Nested JSON Manually

Sometimes a dataset requires very specific nesting that isn’t directly supported by built-in functions. In this case, manually building the JSON using loops and the native Python JSON library can be the required solution.

Here’s an example:

import pandas as pd
import json

df = pd.DataFrame({
    'id': [1, 2],
    'name': ['John Doe', 'Anna Smith'],
    'age': [28, 34]
})

users_list = []
for _, row in df.iterrows():
    user_dict = {'id': row['id'], 'details': {'name': row['name'], 'age': row['age']}}
    users_list.append(user_dict)

nested_json = json.dumps({'users': users_list})
print(nested_json)

Output:

{
    "users": [
        {"id": 1, "details": {"name": "John Doe", "age": 28}},
        {"id": 2, "details": {"name": "Anna Smith", "age": 34}}
    ]
}

By iterating over DataFrame rows and building dictionaries in a specified structure, this code assembles a deeply nested JSON string, allowing for flexible and complex nesting.

Bonus One-Liner Method 5: Using to_json() for Simple Nesting

For situations where only simple JSON structures without advanced nesting are needed, pandas’ to_json() method can be utilized as a quick one-liner solution.

Here’s an example:

import pandas as pd

df = pd.DataFrame({
    'name': ['John Doe', 'Anna Smith'],
    'age': [28, 34]
})

nested_json = df.to_json(orient='records', indent=4)
print(nested_json)

Output:

[
    {
        "name": "John Doe",
        "age": 28
    },
    {
        "name": "Anna Smith",
        "age": 34
    }
]

This code snippet employs pandas’ to_json() function to convert a DataFrame into a minimally nested JSON array.

Summary/Discussion

  • Method 1: Custom Function. Highly customizable. Can require complex logic for more intricate nesting.
  • Method 2: groupby() and apply(). Good for grouping by column values. Not always intuitive for multi-level nesting.
  • Method 3: json_normalize(). Handles existing nested JSON. Might need additional steps to re-structure flattened data.
  • Method 4: Manual JSON Construction. Offers full control over the output structure. Can be verbose and error-prone for large datasets.
  • Bonus Method 5: One-Liner to_json(). Quick and simple for basic output. Lacks the flexibility for custom nested structures.